简介：这份资源是北邮研一数据库课程大作业的完整详解文档，面向正在修读数据库课程、需要完成学生成绩管理系统设计的研究生及高年级本科生。内容围绕需求分析、数据库设计、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.7，degree 的范围校验不会真正生效，需要在应用层或触发器里补。
2.3 主外键与函数依赖
四张表的主键定下来之后，函数依赖关系就清楚了。student 表里 sno 决定 sname、ssex、smajor、sclass，满足第三范式。course 表里 cno 决定 cname 和 tno。teacher 表里 tno 决定 tname、tsex、tdept。sc 表里 sno 和 cno 联合决定 degree，degree 不依赖任何单个属性，所以也满足 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 1=0; 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，建表时也可以显式指定，连接字符串里加上 characterEncoding=utf8。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 sno='xxx' 报外键约束错误。原因是 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 都是白费。希望这份拆解能帮你少走点弯路，顺利把作业跑起来。
本文还有配套的精品资源，点击获取
