MySql-学生成绩管理系统

前言

暑假的时候学习过这个 MySql 数据库，然后学校这个学期居然开了这门课程，那就做一个学生成绩管理系统来练一练手吧。在这里做一个简单的练习记录。

需求分析

SQL 编写

新建数据库

为该数据库建表

INSERT INTO 数据

创建索引

创建视图

创建触发器

创建存储过程

SQL 查询进行调试

结语

完整代码如下

-- 1. 新建数据库drop database studentManage;CREATE DATABASE studentManage;use studentManage;-- 2. 创建表-- 创建班级表CREATE TABLE classes (class_id INT PRIMARY KEY,  -- 班级ID，作为主键class_name VARCHAR(50)  -- 班级名称，最大长度为50个字符);-- 插入班级数据INSERT INTO classes (class_id, class_name) VALUES (0, '尖子班');INSERT INTO classes (class_id, class_name) VALUES (1, '垃圾班');INSERT INTO classes (class_id, class_name) VALUES (2, '垃圾班');INSERT INTO classes (class_id, class_name) VALUES (3, '废物班');INSERT INTO classes (class_id, class_name) VALUES (4, '废物班');INSERT INTO classes (class_id, class_name) VALUES (5, '脑残班');-- 创建学生表CREATE TABLE students (student_id INT PRIMARY KEY NOT NULL,  -- 学生ID，作为主键，不允许为空name VARCHAR(50),  -- 姓名，最大长度为50个字符class_id INT,  -- 班级IDage INT,  -- 年龄FOREIGN KEY (class_id) REFERENCES classes(class_id)  -- 外键关联到班级表的班级ID);-- 插入学生数据INSERT INTO students (student_id, name, class_id, age) VALUES (1, '王八', 5, 19);INSERT INTO students (student_id, name, class_id, age) VALUES (2, '王一', 1, 20);INSERT INTO students (student_id, name, class_id, age) VALUES (3, '小六', 0, 18);INSERT INTO students (student_id, name, class_id, age) VALUES (4, '王五', 2, 19);INSERT INTO students (student_id, name, class_id, age) VALUES (5, '赵六', 3, 20);INSERT INTO students (student_id, name, class_id, age) VALUES (6, '小七', 4, 18);-- 创建教师表CREATE TABLE teachers (teacher_id INT PRIMARY KEY NOT NULL,  -- 教师ID，作为主键，不允许为空teacher_name VARCHAR(50)  -- 教师姓名，最大长度为50个字符);-- 插入教师数据INSERT INTO teachers (teacher_id, teacher_name) VALUES (1, '陈老师');INSERT INTO teachers (teacher_id, teacher_name) VALUES (2, '杨老师');INSERT INTO teachers (teacher_id, teacher_name) VALUES (4, '何老师');INSERT INTO teachers (teacher_id, teacher_name) VALUES (4, '刘老师');INSERT INTO teachers (teacher_id, teacher_name) VALUES (5, '陈老师');INSERT INTO teachers (teacher_id, teacher_name) VALUES (6, '周老师');-- 创建课程表CREATE TABLE courses (course_id INT PRIMARY KEY NOT NULL,  -- 课程ID，作为主键，不允许为空course_name VARCHAR(50),  -- 课程名称，最大长度为50个字符teacher_id INT,  -- 教师IDFOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)  -- 外键关联到教师表的教师ID);-- 插入课程数据INSERT INTO courses (course_id, course_name, teacher_id) VALUES (100, '英语', 1);INSERT INTO courses (course_id, course_name, teacher_id) VALUES (101, '语文', 2);INSERT INTO courses (course_id, course_name, teacher_id) VALUES (102, '数学', 3);INSERT INTO courses (course_id, course_name, teacher_id) VALUES (103, '生物', 4);INSERT INTO courses (course_id, course_name, teacher_id) VALUES (104, '物理', 5);INSERT INTO courses (course_id, course_name, teacher_id) VALUES (105, '化学', 6);-- 创建成绩表CREATE TABLE scores (student_id INT,  -- 学生IDcourse_id INT,  -- 课程IDscore INT,  -- 分数FOREIGN KEY (student_id) REFERENCES students(student_id),  -- 外键关联到学生表的学生IDFOREIGN KEY (course_id) REFERENCES courses(course_id)  -- 外键关联到课程表的课程ID);-- 插入成绩数据INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 92);INSERT INTO scores (student_id, course_id, score) VALUES (2, 1, 8);INSERT INTO scores (student_id, course_id, score) VALUES (3, 1, 5);INSERT INTO scores (student_id, course_id, score) VALUES (4, 1, 0);INSERT INTO scores (student_id, course_id, score) VALUES (5, 1, 8);INSERT INTO scores (student_id, course_id, score) VALUES (6, 1, 7);INSERT INTO scores (student_id, course_id, score) VALUES (1, 2, 100);INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 8);INSERT INTO scores (student_id, course_id, score) VALUES (3, 2, 5);INSERT INTO scores (student_id, course_id, score) VALUES (4, 2, 2);INSERT INTO scores (student_id, course_id, score) VALUES (5, 2, 8);INSERT INTO scores (student_id, course_id, score) VALUES (6, 2, 7);INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 1);INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 7);INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 10);INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 10);INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 60);INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 22);INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 71);INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 20);INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 30);INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 40);INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 14);INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 72);INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 11);INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 11);INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 62);INSERT INTO scores (student_id, course_id, score) VALUES (1, 1, 100);INSERT INTO scores (student_id, course_id, score) VALUES (2, 2, 12);INSERT INTO scores (student_id, course_id, score) VALUES (3, 3, 73);INSERT INTO scores (student_id, course_id, score) VALUES (4, 4, 14);INSERT INTO scores (student_id, course_id, score) VALUES (5, 5, 15);INSERT INTO scores (student_id, course_id, score) VALUES (6, 6, 66);-- 3. 创建索引-- 为学生表的学生ID创建索引CREATE INDEX idx_student_id ON students (student_id);-- 为课程表的课程ID创建索引CREATE INDEX idx_course_id ON courses (course_id);-- 为成绩表的学生ID和课程ID创建联合索引CREATE INDEX idx_student_course ON scores (student_id, course_id);-- 4. 创建视图CREATE VIEW class_scores AS    SELECT         c.class_id,         c.class_name,         COUNT(s.student_id) AS student_count,         AVG(sc.score) AS average_score    FROM         classes c    JOIN         students s ON c.class_id = s.class_id    JOIN    scores sc ON s.student_id = sc.student_idGROUP BY         c.class_id, c.class_name;CREATE VIEW teacher_courses AS  SELECT       t.teacher_id,       t.teacher_name,       COUNT(c.course_id) AS course_count  FROM       teachers t  JOIN       courses c ON t.teacher_id = c.teacher_id  GROUP BY       t.teacher_id, t.teacher_name;-- 5. 创建触发器DELIMITER //  CREATE TRIGGER check_student_class_id_after_insert  AFTER INSERT ON students  FOR EACH ROW  BEGIN      IF NEW.class_id IS NULL OR NEW.class_id = 0 THEN          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '必须为新学生分配班级ID';      END IF;  END;  //  DELIMITER ;DELIMITER //  CREATE TRIGGER check_course_name_length_before_update  BEFORE UPDATE ON courses  FOR EACH ROW  BEGIN      IF NEW.course_name IS NOT NULL AND LENGTH(NEW.course_name) > 50 THEN          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '课程名称长度不能超过50个字符';      END IF;  END;  //  DELIMITER ;DELIMITER //  CREATE TRIGGER check_student_scores_after_delete  AFTER DELETE ON students  FOR EACH ROW  BEGIN      DECLARE score_count INT;      SELECT COUNT(*) INTO score_count FROM scores WHERE student_id = OLD.student_id;      IF score_count > 0 THEN          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能删除有分数记录的学生';      END IF;  END;  //  DELIMITER ;-- 6. 创建存储函数-- 存储过程1 - 添加学生DELIMITER //CREATE PROCEDURE add_student (    IN p_student_id INT,    IN p_name VARCHAR(50),    IN p_class_id INT,    IN p_age INT)BEGIN    INSERT INTO students (student_id, name, class_id, age) VALUES (p_student_id, p_name, p_class_id, p_age);END //DELIMITER ;-- 存储过程2 - 添加课程DELIMITER //CREATE PROCEDURE add_course (    IN p_course_id INT,    IN p_course_name VARCHAR(50),    IN p_teacher_id INT)BEGIN    INSERT INTO courses (course_id, course_name, teacher_id) VALUES (p_course_id, p_course_name, p_teacher_id);END //DELIMITER ;-- 存储过程3 - 添加成绩DELIMITER //CREATE PROCEDURE add_score (    IN p_student_id INT,    IN p_course_id INT,    IN p_score INT)BEGIN    INSERT INTO scores (student_id, course_id, score) VALUES (p_student_id, p_course_id, p_score);END //DELIMITER ;-- 数据更新-- 更新学生的姓名UPDATE students SET name = '新名字' WHERE student_id = 1;-- 更新学生的班级UPDATE students SET class_id = 1 WHERE student_id = 2;-- 更新学生的年龄UPDATE students SET age = 21 WHERE student_id = 3;-- 更新课程的名称UPDATE courses SET course_name = '历史' WHERE course_id = 100;-- 更新课程的教师UPDATE courses SET teacher_id = 2 WHERE course_id = 101;-- 更新成绩UPDATE scores SET score = 90 WHERE student_id = 1 AND course_id = 100;-- 查询特定学生的信息：SELECT * FROM students WHERE student_id = 1;--  查询特定课程的信息：SELECT * FROM courses WHERE course_id = 5;-- 查询特定学生在特定课程上的成绩：SELECT scores.score   FROM scores   JOIN students ON scores.student_id = students.student_id   JOIN courses ON scores.course_id = courses.course_id   WHERE students.student_id = 1 AND courses.course_id = 5;-- 查询某个班级的所有学生信息：SELECT * FROM students WHERE class_id = 1;-- 查询某个教师的所有课程信息：SELECT courses.* FROM courses JOIN teachers ON courses.teacher_id = teachers.teacher_id WHERE teachers.teacher_name = '刘老师';