MySQL实训项目——学生成绩录入与分析系统

项目简述：在校园中，除了上课之外，我们会有许多大大小小的考试，本项目将实现对学生数据的增添，删除，查询与修改，能让教育者更好的了解学生情况，进而优化教学方法和管理策略。

1.建表操作（DDL）

 
-- 创建学生信息表
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='系统配置表';

2.插入表格数据（DML）

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', '考试安排网址');

效果图

各表格内数据：

对上面表单做出相应的E-R图：

3.对学生信息的基础增删查改语句：

 -- 学生信息管理：管理学生的基本信息，如姓名、性别、班级等。
select name,gender,class from students;
-- 成绩录入：支持教师录入学生的各科成绩。

-- 插入学生成绩
INSERT INTO scores (student_id, subject_id, score, exam_date)
VALUES (3, 3, 80.00, '2023-10-16'); -- 假设王五的物理成绩

-- 成绩查询与修改：提供学生、教师或管理员查询学生成绩的功能，并允许对成绩进行必要的修改。

-- 查询学生成绩
SELECT s.name AS 学生姓名, sb.subject_name AS 科目名称, sc.score AS 成绩, sc.exam_date AS 考试日期
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN subjects sb ON sc.subject_id = sb.subject_id
WHERE s.name = '张三'; -- 假设查询张三的成绩

-- 修改学生成绩
UPDATE scores
SET score = 95.00
WHERE student_id = 1 AND subject_id = 1; -- 假设修改张三的数学成绩为95.00

效果图:

4.触发器

DELIMITER //
CREATE TRIGGER update_score_analysis_after_insert_or_update
AFTER INSERT ON scores
FOR EACH ROW
BEGIN
    DECLARE subject_id_var INT;
    DECLARE avg_score_var DECIMAL(5, 2);
    DECLARE max_score_var DECIMAL(5, 2);
    DECLARE min_score_var DECIMAL(5, 2);

    SET subject_id_var = NEW.subject_id;

    -- 计算平均分、最高分、最低分
    SELECT AVG(score), MAX(score), MIN(score)
    INTO avg_score_var, max_score_var, min_score_var
    FROM scores
    WHERE subject_id = subject_id_var;

    -- 更新score_analysis表
    UPDATE score_analysis
    SET avg_score = avg_score_var,
        max_score = max_score_var,
        min_score = min_score_var,
        analysis_date = NOW()
    WHERE subject_id = subject_id_var;

    -- 如果不存在则插入
    IF ROW_COUNT() = 0 THEN
        INSERT INTO score_analysis (subject_id, avg_score, max_score, min_score, analysis_date)
        VALUES (subject_id_var, avg_score_var, max_score_var, min_score_var, NOW());
    END IF;
END;
//
DELIMITER ;


-- 同样，为了完整性，也可以为UPDATE操作创建触发器
DELIMITER //
CREATE TRIGGER update_score_analysis_after_update
AFTER UPDATE ON scores
FOR EACH ROW
BEGIN
    -- 这里可以复制上面的触发器逻辑，或者只更新发生变化的科目分析
    -- ...（与上面的逻辑类似，但只针对被更新的科目）
END;
//
DELIMITER ;

-- 成绩分析：基于录入的成绩数据，进行统计分析，如平均分、最高分、最低分、成绩分布等。

-- 报表生成：自动生成成绩报表，方便打印或导出。

-- 生成所有科目的成绩报表
SELECT s.name AS 学生姓名, sb.subject_name AS 科目名称, sc.score AS 成绩, sc.exam_date AS 考试日期
FROM students s
JOIN scores sc ON s.student_id = sc.student_id
JOIN subjects sb ON sc.subject_id = sb.subject_id
ORDER BY s.name, sb.subject_name;

效果图：

5.存储过程

CALL UpdateStudentScore(1, 1, 95.00); --调用

DELIMITER // --插入时的存储过程
CREATE PROCEDURE InsertStudentScore(IN p_student_id INT, IN p_subject_id INT, IN p_score DECIMAL(5,2), IN p_exam_date DATE)
BEGIN
    INSERT INTO scores (student_id, subject_id, score, exam_date)
    VALUES (p_student_id, p_subject_id, p_score, p_exam_date);
END;
//
DELIMITER ;
CALL UpdateStudentScore(1, 1, 95.00);
DELIMITER // --查询时的存储过程
CREATE PROCEDURE QueryStudentScore(IN p_student_name VARCHAR(255))
BEGIN
    SELECT s.name AS 学生姓名, sb.subject_name AS 科目名称, sc.score AS 成绩, sc.exam_date AS 考试日期
    FROM students s
    JOIN scores sc ON s.student_id = sc.student_id
    JOIN subjects sb ON sc.subject_id = sb.subject_id
    WHERE s.name = p_student_name;
END;
//
DELIMITER ;

DELIMITER // --修改时的存储过程
CREATE PROCEDURE UpdateStudentScore(IN p_student_id INT, IN p_subject_id INT, IN p_new_score DECIMAL(5,2))
BEGIN
    UPDATE scores
    SET score = p_new_score
    WHERE student_id = p_student_id AND subject_id = p_subject_id;
END;
//
DELIMITER ;

效果图：