MySQL数据库基础练习系列8、成绩录入与分析系统

MySQL数据库基础练习系列目标

很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。

数据库环境

MySQL版本：5.7.31-log

数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。

字符集：utf8 排序规则：utf8_general_ci

使用工具：Navicat Premium 15，可以在下面的连接中下载

https://download.csdn.net/download/feng8403000/89403778

项目名称与项目简介

成绩录入与分析系统是一个用于管理学生成绩、提供成绩分析功能的软件系统。该系统能够支持成绩的录入、查询、修改以及基于成绩的各项统计分析功能，旨在帮助教育者、管理者更好地掌握学生的学习状况，进而优化教学方法和管理策略。

主要功能包括：

学生信息管理：管理学生的基本信息，如姓名、性别、班级等。
成绩录入：支持教师录入学生的各科成绩。
成绩查询与修改：提供学生、教师或管理员查询学生成绩的功能，并允许对成绩进行必要的修改。
成绩分析：基于录入的成绩数据，进行统计分析，如平均分、最高分、最低分、成绩分布等。
报表生成：自动生成成绩报表，方便打印或导出。

数据库DDL（注意创建顺序）

为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。

代码语言：javascript复制

-- 创建学生信息表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    class VARCHAR(50) NOT NULL COMMENT '班级',
    registration_date DATE COMMENT '注册日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';


-- 创建科目表
CREATE TABLE subjects (
    subject_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '科目ID',
    subject_name VARCHAR(100) NOT NULL UNIQUE COMMENT '科目名称',
    teacher_name VARCHAR(100) COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='科目信息表';


-- 创建成绩表
CREATE TABLE scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成绩ID',
    student_id INT NOT NULL COMMENT '学生ID',
    subject_id INT NOT NULL COMMENT '科目ID',
    score DECIMAL(5, 2) NOT NULL COMMENT '成绩',
    exam_date DATE COMMENT '考试日期',
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';


-- 创建成绩分析表（可选，用于存储分析结果）
CREATE TABLE score_analysis (
    analysis_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分析ID',
    subject_id INT NOT NULL COMMENT '科目ID',
    avg_score DECIMAL(5, 2) NOT NULL COMMENT '平均分',
    max_score DECIMAL(5, 2) NOT NULL COMMENT '最高分',
    min_score DECIMAL(5, 2) NOT NULL COMMENT '最低分',
    analysis_date DATE COMMENT '分析日期',
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩分析表';


-- 创建系统配置表（可选，用于存储系统相关配置信息）
CREATE TABLE system_config (
    config_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '配置ID',
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value VARCHAR(255) COMMENT '配置值',
    description TEXT COMMENT '配置描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

插入数据DML（注意插入数据顺序）

插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。

代码语言：javascript复制

INSERT INTO students (name, gender, class, registration_date) VALUES
('张三', '男', '一班', '2023-09-01'),
('李四', '女', '二班', '2023-09-02'),
('王五', '男', '三班', '2023-09-03'),
('赵六', '女', '四班', '2023-09-04'),
('孙七', '男', '五班', '2023-09-05');
INSERT INTO subjects (subject_name, teacher_name) VALUES
('数学', '张老师'),
('英语', '李老师'),
('物理', '王老师'),
('化学', '赵老师'),
('生物', '孙老师');
INSERT INTO scores (student_id, subject_id, score, exam_date) VALUES
(1, 1, 85.50, '2023-10-15'), -- 张三的数学成绩
(1, 2, 90.25, '2023-10-15'), -- 张三的英语成绩
(2, 1, 78.75, '2023-10-15'), -- 李四的数学成绩
(2, 2, 88.00, '2023-10-15'), -- 李四的英语成绩
(5, 5, 92.00, '2023-10-15'); -- 孙七的生物成绩
INSERT INTO score_analysis (subject_id, avg_score, max_score, min_score, analysis_date) VALUES
(1, 82.38, 90.50, 75.00, '2023-10-17'), -- 数学成绩分析
(2, 86.75, 92.00, 80.00, '2023-10-17'), -- 英语成绩分析
(5, 89.25, 95.00, 85.00, '2023-10-17'); -- 生物成绩分析
INSERT INTO system_config (config_key, config_value, description) VALUES
('school_name', '阳光中学', '学校名称'),
('admin_email', 'admin@example.com', '管理员电子邮箱'),
('exam_schedule_url', 'https://example.com/exam-schedule', '考试安排网址');

遵循的数据库三范式

数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：

第一范式（1NF, First Normal Form）

定义：

列不可分割，即数据库表的每一列都是不可分割的原子数据项。
每一列都是不可再分的最小数据单元（也称为最小的原子单元）。

解释：

在第一范式中，主要关注的是列的原子性。也就是说，表中的每一列都应该只包含一个值，而不能包含集合、数组或其他复合数据类型。
例如，如果有一个“地址”列，它包含了街道、城市、省份和国家等信息，那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列，如“街道”、“城市”、“省份”和“国家”。

第二范式（2NF, Second Normal Form）

定义：

满足1NF。
非主键列必须完全依赖于主键，而不能只依赖于主键的一部分（针对复合主键而言）。

解释：

第二范式建立在第一范式的基础上，主要关注于主键与非主键列之间的依赖关系。
在第二范式中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
如果表中的某一列只与复合主键的一部分有关，那么它就不应该存在于这个表中，而应该被分离出去形成另外一张新表。

第三范式（3NF, Third Normal Form）

定义：

满足2NF。
非主键列必须直接依赖于主键，不能存在传递依赖。即非主键列必须直接依赖于整个主键，而不能依赖于主键的一部分。

解释：

第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖，即非主键列不应该依赖于主键的某一部分，而应该直接依赖于整个主键。
如果存在传递依赖，那么应该考虑将这个非主键列分离出去，形成新的表，并通过主键或外键与原表进行关联。

数据库 mysql 基础数据系统

0 人点赞