MySql-学生成绩管理系统

前言

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

需求分析

SQL 编写

新建数据库

为该数据库建表

INSERT INTO 数据

创建索引

创建视图

创建触发器

创建存储过程

SQL 查询进行调试

结语

完整代码如下

-- 1. 新建数据库dropdatabase studentManage;CREATEDATABASE studentManage;use studentManage;-- 2. 创建表-- 创建班级表CREATETABLE classes (
class_id INTPRIMARYKEY,-- 班级ID，作为主键
class_name VARCHAR(50)-- 班级名称，最大长度为50个字符);-- 插入班级数据INSERTINTO classes (class_id, class_name)VALUES(0,'尖子班');INSERTINTO classes (class_id, class_name)VALUES(1,'垃圾班');INSERTINTO classes (class_id, class_name)VALUES(2,'垃圾班');INSERTINTO classes (class_id, class_name)VALUES(3,'废物班');INSERTINTO classes (class_id, class_name)VALUES(4,'废物班');INSERTINTO classes (class_id, class_name)VALUES(5,'脑残班');-- 创建学生表CREATETABLE students (
student_id INTPRIMARYKEYNOTNULL,-- 学生ID，作为主键，不允许为空
name VARCHAR(50),-- 姓名，最大长度为50个字符
class_id INT,-- 班级ID
age INT,-- 年龄FOREIGNKEY(class_id)REFERENCES classes(class_id)-- 外键关联到班级表的班级ID);-- 插入学生数据INSERTINTO students (student_id, name, class_id, age)VALUES(1,'王八',5,19);INSERTINTO students (student_id, name, class_id, age)VALUES(2,'王一',1,20);INSERTINTO students (student_id, name, class_id, age)VALUES(3,'小六',0,18);INSERTINTO students (student_id, name, class_id, age)VALUES(4,'王五',2,19);INSERTINTO students (student_id, name, class_id, age)VALUES(5,'赵六',3,20);INSERTINTO students (student_id, name, class_id, age)VALUES(6,'小七',4,18);-- 创建教师表CREATETABLE teachers (
teacher_id INTPRIMARYKEYNOTNULL,-- 教师ID，作为主键，不允许为空
teacher_name VARCHAR(50)-- 教师姓名，最大长度为50个字符);-- 插入教师数据INSERTINTO teachers (teacher_id, teacher_name)VALUES(1,'陈老师');INSERTINTO teachers (teacher_id, teacher_name)VALUES(2,'杨老师');INSERTINTO teachers (teacher_id, teacher_name)VALUES(4,'何老师');INSERTINTO teachers (teacher_id, teacher_name)VALUES(4,'刘老师');INSERTINTO teachers (teacher_id, teacher_name)VALUES(5,'陈老师');INSERTINTO teachers (teacher_id, teacher_name)VALUES(6,'周老师');-- 创建课程表CREATETABLE courses (
course_id INTPRIMARYKEYNOTNULL,-- 课程ID，作为主键，不允许为空
course_name VARCHAR(50),-- 课程名称，最大长度为50个字符
teacher_id INT,-- 教师IDFOREIGNKEY(teacher_id)REFERENCES teachers(teacher_id)-- 外键关联到教师表的教师ID);-- 插入课程数据INSERTINTO courses (course_id, course_name, teacher_id)VALUES(100,'英语',1);INSERTINTO courses (course_id, course_name, teacher_id)VALUES(101,'语文',2);INSERTINTO courses (course_id, course_name, teacher_id)VALUES(102,'数学',3);INSERTINTO courses (course_id, course_name, teacher_id)VALUES(103,'生物',4);INSERTINTO courses (course_id, course_name, teacher_id)VALUES(104,'物理',5);INSERTINTO courses (course_id, course_name, teacher_id)VALUES(105,'化学',6);-- 创建成绩表CREATETABLE scores (
student_id INT,-- 学生ID
course_id INT,-- 课程ID
score INT,-- 分数FOREIGNKEY(student_id)REFERENCES students(student_id),-- 外键关联到学生表的学生IDFOREIGNKEY(course_id)REFERENCES courses(course_id)-- 外键关联到课程表的课程ID);-- 插入成绩数据INSERTINTO scores (student_id, course_id, score)VALUES(1,1,92);INSERTINTO scores (student_id, course_id, score)VALUES(2,1,8);INSERTINTO scores (student_id, course_id, score)VALUES(3,1,5);INSERTINTO scores (student_id, course_id, score)VALUES(4,1,0);INSERTINTO scores (student_id, course_id, score)VALUES(5,1,8);INSERTINTO scores (student_id, course_id, score)VALUES(6,1,7);INSERTINTO scores (student_id, course_id, score)VALUES(1,2,100);INSERTINTO scores (student_id, course_id, score)VALUES(2,2,8);INSERTINTO scores (student_id, course_id, score)VALUES(3,2,5);INSERTINTO scores (student_id, course_id, score)VALUES(4,2,2);INSERTINTO scores (student_id, course_id, score)VALUES(5,2,8);INSERTINTO scores (student_id, course_id, score)VALUES(6,2,7);INSERTINTO scores (student_id, course_id, score)VALUES(1,1,100);INSERTINTO scores (student_id, course_id, score)VALUES(2,2,1);INSERTINTO scores (student_id, course_id, score)VALUES(3,3,7);INSERTINTO scores (student_id, course_id, score)VALUES(4,4,10);INSERTINTO scores (student_id, course_id, score)VALUES(5,5,10);INSERTINTO scores (student_id, course_id, score)VALUES(6,6,60);INSERTINTO scores (student_id, course_id, score)VALUES(1,1,100);INSERTINTO scores (student_id, course_id, score)VALUES(2,2,22);INSERTINTO scores (student_id, course_id, score)VALUES(3,3,71);INSERTINTO scores (student_id, course_id, score)VALUES(4,4,20);INSERTINTO scores (student_id, course_id, score)VALUES(5,5,30);INSERTINTO scores (student_id, course_id, score)VALUES(6,6,40);INSERTINTO scores (student_id, course_id, score)VALUES(1,1,100);INSERTINTO scores (student_id, course_id, score)VALUES(2,2,14);INSERTINTO scores (student_id, course_id, score)VALUES(3,3,72);INSERTINTO scores (student_id, course_id, score)VALUES(4,4,11);INSERTINTO scores (student_id, course_id, score)VALUES(5,5,11);INSERTINTO scores (student_id, course_id, score)VALUES(6,6,62);INSERTINTO scores (student_id, course_id, score)VALUES(1,1,100);INSERTINTO scores (student_id, course_id, score)VALUES(2,2,12);INSERTINTO scores (student_id, course_id, score)VALUES(3,3,73);INSERTINTO scores (student_id, course_id, score)VALUES(4,4,14);INSERTINTO scores (student_id, course_id, score)VALUES(5,5,15);INSERTINTO scores (student_id, course_id, score)VALUES(6,6,66);-- 3. 创建索引-- 为学生表的学生ID创建索引CREATEINDEX idx_student_id ON students (student_id);-- 为课程表的课程ID创建索引CREATEINDEX idx_course_id ON courses (course_id);-- 为成绩表的学生ID和课程ID创建联合索引CREATEINDEX idx_student_course ON scores (student_id, course_id);-- 4. 创建视图CREATEVIEW class_scores ASSELECT     
    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_id
GROUPBY     
    c.class_id, c.class_name;CREATEVIEW teacher_courses ASSELECT   
    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  
GROUPBY   
    t.teacher_id, t.teacher_name;-- 5. 创建触发器DELIMITER//  CREATETRIGGER check_student_class_id_after_insert  
AFTERINSERTON students  
FOR EACH ROWBEGINIF NEW.class_id ISNULLOR NEW.class_id =0THEN  
        SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='必须为新学生分配班级ID';ENDIF;END;//  DELIMITER;DELIMITER//  CREATETRIGGER check_course_name_length_before_update  
BEFORE UPDATEON courses  
FOR EACH ROWBEGINIF NEW.course_name ISNOTNULLAND LENGTH(NEW.course_name)>50THEN  
        SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='课程名称长度不能超过50个字符';ENDIF;END;//  DELIMITER;DELIMITER//  CREATETRIGGER check_student_scores_after_delete  
AFTERDELETEON students  
FOR EACH ROWBEGINDECLARE score_count INT;SELECTCOUNT(*)INTO score_count FROM scores WHERE student_id = OLD.student_id;IF score_count >0THEN  
        SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='不能删除有分数记录的学生';ENDIF;END;//  DELIMITER;-- 6. 创建存储函数-- 存储过程1 - 添加学生DELIMITER//CREATEPROCEDURE add_student (IN p_student_id INT,IN p_name VARCHAR(50),IN p_class_id INT,IN p_age INT)BEGININSERTINTO students (student_id, name, class_id, age)VALUES(p_student_id, p_name, p_class_id, p_age);END//DELIMITER;-- 存储过程2 - 添加课程DELIMITER//CREATEPROCEDURE add_course (IN p_course_id INT,IN p_course_name VARCHAR(50),IN p_teacher_id INT)BEGININSERTINTO courses (course_id, course_name, teacher_id)VALUES(p_course_id, p_course_name, p_teacher_id);END//DELIMITER;-- 存储过程3 - 添加成绩DELIMITER//CREATEPROCEDURE add_score (IN p_student_id INT,IN p_course_id INT,IN p_score INT)BEGININSERTINTO 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 =1WHERE student_id =2;-- 更新学生的年龄UPDATE students SET age =21WHERE student_id =3;-- 更新课程的名称UPDATE courses SET course_name ='历史'WHERE course_id =100;-- 更新课程的教师UPDATE courses SET teacher_id =2WHERE course_id =101;-- 更新成绩UPDATE scores SET score =90WHERE student_id =1AND 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 =1AND 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 ='刘老师';