MySQL学生成绩管理系统：四表设计、视图、存储过程与触发器实战

简介这份资源是北邮研一数据库课程大作业的完整详解文档面向正在修读数据库课程、需要完成学生成绩管理系统设计的研究生及高年级本科生。内容围绕需求分析、数据库设计、ER图绘制与逻辑结构设计展开覆盖成绩信息维护、教师信息管理、不及格学生名单统计以及无教学任务教师查询等核心功能并给出Course、Student、Sc、Teacher四张表的主外键约束与一对多关系设计。资源包内含1个docx文档约503KB集中呈现数据字典、函数依赖集与建表SQL语句便于对照课程要求梳理设计思路。目前已有606人学习下载适合需要参考完整大作业方案、理解关系模型规范化过程或查漏补缺的读者可帮助快速掌握从需求到建表的完整设计链路。1. 从一份学生成绩管理系统说起四个表撑起选课、成绩与教师管理如果你手头正好有一份数据库课程大作业题目是学生成绩管理系统要求用 MySQL 建库建表、写视图、写存储过程、写触发器还要能查不及格名单和没排课的老师那这份资源基本就是照着这个场景做的。它把需求分析、ER 图、逻辑结构、建表 SQL、视图、存储过程、触发器、复杂查询全部串了一遍最后落在 Navicat 里跑通。适合两类人一类是刚学完 SQL 语法、第一次做大作业不知道从哪下手的同学另一类是想拿一个完整案例复习关系模型、函数依赖和约束设计的开发者。整个系统围绕 course、student、sc、teacher 四张表展开麻雀虽小但主外键、组合主键、CHECK 约束、视图更新、触发器回写这些数据库课的核心考点一个没落。2. 需求到表结构四张表怎么装下选课、成绩和教师2.1 功能需求拆成三条业务线原题给的功能看着零散其实可以归成三条线。第一条是基础信息维护学生、教师、课程都要能增删改查这是最常规的 CRUD。第二条是成绩链路授课教师录入成绩学生查自己的成绩教务处按课程统计不及格名单。第三条是教学任务核查找出没有教学任务的老师这条最容易被忽略但它决定了 course 表和 teacher 表之间必须能反查。把这三条线落到表上就得到四个实体学生、教师、课程、成绩。成绩不是独立实体它是学生和课程之间的多对多联系所以单独抽成 sc 表用 sno 和 cno 做组合主键。这个设计判断是整个作业的地基后面所有查询都建立在它上面。2.2 四张表的字段与约束设计先看建库建表。原稿里数据库名用的是 test实际交作业时建议改成有含义的名字比如 score_mgmt避免和别的库撞名。下面这段 SQL 把四张表一次建出来字段长度和可空性都按需求文档对齐。-- 创建数据库字符集用 utf8mb4 避免中文乱码 CREATE DATABASE score_mgmt DEFAULT CHARACTER SET utf8mb4; USE score_mgmt; -- 课程表cno 主键tno 记录任课教师 CREATE TABLE course ( cno CHAR(5) NOT NULL, cname VARCHAR(20) NOT NULL, tno CHAR(3) NOT NULL, CONSTRAINT pk_course PRIMARY KEY (cno) ); -- 学生表sno 主键其余字段允许为空 CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname CHAR(8), ssex CHAR(2), smajor CHAR(20), sclass CHAR(10) ); -- 成绩表sno cno 组合主键degree 限制 0 到 100 CREATE TABLE sc ( sno CHAR(10) NOT NULL, cno CHAR(5) NOT NULL, degree DECIMAL(4,1), CONSTRAINT pk_sc PRIMARY KEY (sno, cno), CONSTRAINT ck_degree CHECK (degree 0 AND degree 100) ); -- 教师表tno 主键 CREATE TABLE teacher ( tno CHAR(3) NOT NULL, tname VARCHAR(8), tsex CHAR(2), tdept CHAR(16), CONSTRAINT pk_teacher PRIMARY KEY (tno) );几个参数值得单独说。sc 表的 degree 用 DECIMAL(4,1)表示总共 4 位、小数点后 1 位最大能存 999.9配合 CHECK 约束卡在 0 到 100 之间比用 FLOAT 更稳不会出现 59.99999 这种浮点误差导致判断不及格时翻车。sno 在 student 表里是 CHAR(9)在 sc 表里写成了 CHAR(10)这是原稿里的一处不一致实际建表时两边统一成 CHAR(9) 更规范否则插入时可能因为长度不匹配报错。提示CHECK 约束在 MySQL 8.0.16 之前是被解析但忽略的如果你的环境是 5.7degree 的范围校验不会真正生效需要在应用层或触发器里补。2.3 主外键与函数依赖四张表的主键定下来之后函数依赖关系就清楚了。student 表里 sno 决定 sname、ssex、smajor、sclass满足第三范式。course 表里 cno 决定 cname 和 tno。teacher 表里 tno 决定 tname、tsex、tdept。sc 表里 sno 和 cno 联合决定 degreedegree 不依赖任何单个属性所以也满足 3NF。原稿里 course 表的 tno 字段注释写的是“选课人数”但从 ER 图和后续“没有教学任务的老师”这个需求看tno 实际是任课教师编号不是人数。这个字段语义要在文档里写清楚否则后面写查询时会把自己绕进去。如果要严格做外键约束可以在 sc 表上加两条 FOREIGN KEY分别指向 student 和 course这样删除学生时要么级联删成绩要么报错阻止行为更可控。-- 补上外键约束让 sc 表的 sno 和 cno 真正依赖主表 ALTER TABLE sc ADD CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student(sno) ON DELETE CASCADE, ADD CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course(cno) ON DELETE CASCADE;加上 ON DELETE CASCADE 之后删学生会自动清掉他的成绩记录避免留下孤儿数据。但这也意味着删学生要谨慎后面讲触发器时还会碰到这个点。3. 视图、存储过程与触发器把业务逻辑写进数据库3.1 视图封装高频查询视图的作用是把复杂的多表连接藏起来让上层查询变简单。原稿里定义了 v_student查选修“科学导论”的学生学号、姓名和成绩。这个视图把 student、course、sc 三张表连在一起以后只要 SELECT * FROM v_student 就能拿到结果不用每次重写 JOIN。-- 视图查选修指定课程的学生成绩 CREATE VIEW v_student AS SELECT A.sno, A.sname, C.degree FROM student A JOIN sc C ON A.sno C.sno JOIN course B ON B.cno C.cno WHERE B.cname 科学导论;这里用 JOIN 而不是逗号连接可读性更好也不容易漏掉连接条件导致笛卡尔积。视图本身不存数据每次查询都会重新执行底层 SQL所以底层表加了索引之后视图查询也会跟着快。原稿还演示了通过视图 view_sc 向 sc 表插入数据。简单视图只来自一张表、不含聚合和 DISTINCT是可以更新的插入会直接落到基表。但要注意如果视图里带了 JOIN 或者聚合函数INSERT 就会失败这是 MySQL 的硬限制不是配置问题。-- 单表视图可插入 CREATE VIEW view_sc AS SELECT sno, cno, degree FROM sc; INSERT INTO view_sc VALUES (140210321, C01, 88);3.2 存储过程处理统计类需求存储过程适合把一段固定逻辑封装在数据库端调用时只传参数。原稿里有两个典型例子一个是查某班级的学生一个是统计某学生的选课门数。第二个用到了 OUT 参数这是存储过程里比较实用的写法。DELIMITER // -- 统计指定学号的学生选了几门课结果通过 OUT 参数返回 CREATE PROCEDURE num_sc(IN tmp_sno CHAR(9), OUT count_num INT) READS SQL DATA BEGIN SELECT COUNT(*) INTO count_num FROM sc WHERE sno tmp_sno; END // DELIMITER ; -- 调用先定义变量接收结果 CALL num_sc(120210332, cnt); SELECT cnt;IN 参数是传入的学号OUT 参数 count_num 用来把统计结果带出来。DELIMITER 改成 // 是因为存储过程体里用了分号不换分隔符的话 MySQL 会在第一个分号处就认为语句结束了这是新手最常踩的坑之一。READS SQL DATA 声明这个存储过程只读不写属于权限声明生产环境里有助于审计。3.3 触发器做删除备份触发器解决的是“当某件事发生时自动做另一件事”。原稿的场景是删除一个学生时把他的学号和姓名写进 delstudent 备份表。这个需求在教务系统里很真实学生毕业或退学后记录不能直接丢。-- 备份表结构复制 student 但不要数据 CREATE TABLE delstudent LIKE student; -- 如果只要 sno 和 sname 两列可以这样建 -- CREATE TABLE delstudent SELECT sno, sname FROM student WHERE 10; DELIMITER // CREATE TRIGGER trig_student AFTER DELETE ON student FOR EACH ROW BEGIN INSERT INTO delstudent(sno, sname) VALUES (OLD.sno, OLD.sname); END // DELIMITER ;AFTER DELETE 表示删除动作完成之后触发FOR EACH ROW 表示每删一行就执行一次。OLD 是触发器里的关键字代表被删除的那行旧数据对应的还有 NEW用在 INSERT 和 UPDATE 触发器里。这里有个顺序问题如果 sc 表上加了 ON DELETE CASCADE 外键删学生时成绩先被级联删掉然后触发器才把学生信息写进备份表两者不冲突但备份表里不会记录成绩如果需要完整归档得在触发器里同时把 sc 的记录也存一份。注意触发器里的逻辑越简单越好不要在触发器里做复杂查询或调用其他存储过程否则排查问题时很难定位容易变成黑匣子。4. 复杂查询与统计不及格名单、无课教师怎么查4.1 多表连接查不及格学生教务处要的“各科不及格学生名单”是典型的三表连接。sc 表里有成绩student 表里有学生信息course 表里有课程名三个条件一卡就出来了。-- 查 C03 课程不及格的学生 SELECT A.sno, A.sname, A.ssex, A.smajor, A.sclass, B.degree FROM student A JOIN sc B ON A.sno B.sno JOIN course C ON B.cno C.cno WHERE C.cno C03 AND B.degree 60;如果要做成“所有课程的不及格名单”把 WHERE 里的 C.cno 条件去掉改成 ORDER BY C.cno, A.sno结果按课程分组排列教务处一眼就能看出哪门课挂的人多。这里 degree 60 的判断依赖 DECIMAL 类型如果用 FLOAT 存成绩可能出现 59.999999 被判定为不及格但显示是 60.0 的玄学情况所以前面强调用 DECIMAL 是有原因的。4.2 反连接查没有教学任务的老师“没有教学任务的老师”这个需求本质是找 teacher 表里那些 tno 不在 course 表 tno 集合中的记录。有两种写法NOT IN 和 LEFT JOIN ... IS NULL后者在数据量大时通常更快也不怕子查询返回 NULL 值。-- 方法一LEFT JOIN 反连接 SELECT T.tno, T.tname, T.tdept FROM teacher T LEFT JOIN course C ON T.tno C.tno WHERE C.tno IS NULL; -- 方法二NOT EXISTS SELECT tno, tname, tdept FROM teacher T WHERE NOT EXISTS ( SELECT 1 FROM course C WHERE C.tno T.tno );LEFT JOIN 的思路是先把所有老师和课程左连接有课的老师会匹配到 course 记录没课的老师右边全是 NULL再用 WHERE C.tno IS NULL 筛出来。NOT EXISTS 则是逐行判断子查询有没有结果语义更直白。两种写法结果一样面试里经常被拿来对比实际写作业用哪种都行。4.3 聚合统计与分组平均分、选课人数、每门课的最高最低分这些都要靠 GROUP BY 和聚合函数。原稿里算了 C01 的平均成绩实际作业里更常见的是按课程分组统计。-- 每门课程的平均分、最高分、最低分和选课人数 SELECT C.cno, C.cname, AVG(S.degree) AS avg_degree, MAX(S.degree) AS max_degree, MIN(S.degree) AS min_degree, COUNT(*) AS student_count FROM course C JOIN sc S ON C.cno S.cno GROUP BY C.cno, C.cname HAVING COUNT(*) 1 ORDER BY avg_degree DESC;GROUP BY 后面要带上 cname因为 cname 不是聚合列在 ONLY_FULL_GROUP_BY 模式下不写会报错。HAVING 用来过滤分组后的结果和 WHERE 的区别是 WHERE 在分组前过滤行HAVING 在分组后过滤组。这个查询直接对应教务处“统计各科成绩分布”的需求稍微改一下就能输出不及格率。5. 避坑与排查建表、触发器、字符集里最容易翻车的地方5.1 中文乱码字符集没对齐现象是插入“吴迪”“科学导论”之后SELECT 出来变成问号或者乱码。原因通常是建库时没指定字符集默认用了 latin1而连接层又是 utf8。解决办法是建库时就写 DEFAULT CHARACTER SET utf8mb4建表时也可以显式指定连接字符串里加上 characterEncodingutf8。Navicat 里要在连接属性里把编码设成 utf8mb4否则图形界面看着正常命令行查出来还是乱的。5.2 触发器不生效分隔符和权限现象是建完触发器删了一条学生记录delstudent 表里没数据。先检查 DELIMITER 有没有改如果没改MySQL 遇到触发器体里的第一个分号就截断了触发器根本没建完整。再检查当前用户有没有 TRIGGER 权限普通账号可能被限制。最后确认触发时机AFTER DELETE 要配合 DELETE 语句才会触发TRUNCATE TABLE 不会触发 DELETE 触发器这是很多人不知道的坑。5.3 视图插入失败视图不可更新现象是 INSERT INTO view_sc 报错提示视图不可更新。原因是视图定义里包含了 JOIN、GROUP BY、DISTINCT、聚合函数或者 UNION 中的任意一种MySQL 就无法把插入映射回基表。解决方法是插入操作直接走基表视图只用来查询或者把视图拆成单表简单视图就像原稿里的 view_sc 那样。5.4 外键约束导致删不掉数据现象是 DELETE FROM student WHERE snoxxx 报外键约束错误。原因是 sc 表里有这个学生的成绩记录外键挡住了删除。解决办法有两种先删 sc 里的关联记录再删学生或者建外键时加 ON DELETE CASCADE 让数据库自动处理。但级联删除要小心删一个学生连带删掉他所有成绩如果误操作就是连锁反应建议在应用层做二次确认。5.5 存储过程调用报参数错误现象是 CALL num_sc(120210332) 报参数数量不对。原因是定义时写了 OUT 参数调用时也必须传一个变量接收不能只传 IN 参数。正确写法是 CALL num_sc(120210332, cnt)然后 SELECT cnt 看结果。另外 IN 参数的类型要和表字段匹配CHAR(9) 的学号传了超过 9 位会被截断查不到数据还以为是 SQL 写错了。6. 二次开发从作业到能用的成绩管理后端把这份作业改成一个真正能跑的小系统第一步是补外键和索引。sc 表的 sno 和 cno 上各建一个索引查询不及格名单和统计平均分时会快很多尤其是数据量上了几万条之后。CREATE INDEX idx_sc_sno ON sc(sno); CREATE INDEX idx_sc_cno ON sc(cno); CREATE INDEX idx_course_tno ON course(tno);第二步是把存储过程改造成带分页的查询。原稿的 proc_stud 一次返回全班数据多了前端会卡加两个参数 page_no 和 page_size用 LIMIT 做分页。DELIMITER // CREATE PROCEDURE proc_stud_paged(IN p_class VARCHAR(10), IN p_page INT, IN p_size INT) READS SQL DATA BEGIN DECLARE v_offset INT; SET v_offset (p_page - 1) * p_size; SELECT sno, sname, smajor FROM student WHERE sclass LIKE CONCAT(%, p_class, %) ORDER BY sno LIMIT v_offset, p_size; END // DELIMITER ;第三步是加一张操作日志表把成绩修改记录留下来。教务系统里成绩改过一次就得有痕迹这是刚需。可以在 sc 表上再建一个 AFTER UPDATE 触发器把旧成绩和新成绩都写进日志表。CREATE TABLE sc_log ( id INT AUTO_INCREMENT PRIMARY KEY, sno CHAR(9), cno CHAR(5), old_degree DECIMAL(4,1), new_degree DECIMAL(4,1), changed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); DELIMITER // CREATE TRIGGER trig_sc_update AFTER UPDATE ON sc FOR EACH ROW BEGIN IF OLD.degree NEW.degree THEN INSERT INTO sc_log(sno, cno, old_degree, new_degree) VALUES (OLD.sno, OLD.cno, OLD.degree, NEW.degree); END IF; END // DELIMITER ;验证改造是否成功我一般会走一遍完整链路插入一个学生给他选两门课改一次成绩查日志表看有没有记录再删掉这个学生确认 sc 和 sc_log 里的关联数据行为符合预期。这套流程走下来基本能覆盖主外键、触发器、事务边界这几个最容易出问题的地方。从那以后我每次拿到这种课程设计类的数据库作业都会先把四张表的建表语句单独拎出来跑一遍确认字符集、约束和默认值都对再往上叠视图和存储过程。因为地基没打好后面写再多 SQL 都是白费。希望这份拆解能帮你少走点弯路顺利把作业跑起来。本文还有配套的精品资源点击获取