简介这份资源是北邮研一数据库课程大作业的完整详解文档面向正在修读数据库课程、需要完成学生成绩管理系统设计的研究生及高年级本科生。内容围绕需求分析、数据库设计、ER图绘制与逻辑结构设计展开覆盖成绩信息维护、教师信息管理、不及格学生名单统计、无教学任务教师查询等核心功能并给出Course、Student、Sc、Teacher四张表的字段定义、主外键约束与一对多关系建模思路可直接作为课程作业的参考模板与设计范本。资源包共1个docx文件约503KB以文字与表格形式呈现数据字典、函数依赖集及建表SQL语句结构紧凑便于查阅。目前已有606人学习下载适合需要梳理数据库设计流程、对照检查ER图与关系模式、查漏补缺的同学参考使用。1. 从一份学生成绩管理系统拆开看数据库大作业到底在考什么如果你正在搜“学生成绩管理系统 数据库大作业”大概率不是想听数据库概论而是手里有一个 deadline需要一份能跑起来、能讲清楚、经得起老师追问的完整实现。这份资源就是围绕这个场景来的用 MySQL 建一个学生成绩管理库四张表course、student、sc、teacher撑起成绩录入、成绩查询、不及格统计、无教学任务教师查询这几件事再往上叠视图、存储过程、触发器、复杂查询。它适合两类人一类是刚学完 SQL 语法、还没独立做过完整库设计的新手照着能把建表到查询跑通另一类是做过多表查询、但没系统整理过约束和触发器的熟手可以拿它当一份结构参考重点看主外键怎么落、范式怎么对、哪些地方容易翻车。下面我不按论文目录讲按“拿到这份资源后怎么落地”的顺序拆。2. 四张表怎么落从 ER 到建表语句的完整推演2.1 先想清楚实体和联系再动手写 CREATE TABLE很多人一上来就敲 CREATE TABLE结果写到 sc 表发现主键不知道放谁回头改 student 表越改越乱。正确顺序是先抽象实体和联系。这个系统里有四个实体学生学号、姓名、性别、专业、班级、教师教师号、姓名、性别、院系、课程课程号、课程名、任课教师号、成绩学号、课程号、分数。其中成绩不是独立实体它是学生和课程之间“选修”联系上的属性所以单独抽成 sc 表用sno, cno做组合主键degree 作为联系属性。这里有个容易被忽略的点course 表里的 tno 是任课教师号它和 teacher 表的 tno 是同一个语义但原设计里 course 的 tno 没有显式声明外键。如果老师要求严格应该补上FOREIGN KEY (tno) REFERENCES teacher(tno)。不补也能跑但“没有教学任务的老师”这个查询就失去了约束层面的保障——你只能靠数据本身干净不能靠数据库帮你挡脏数据。关系模式确认后每个表都满足第三范式非主属性完全函数依赖于主键且不存在传递依赖。student 表里 sno 决定 sname、ssex、smajor、sclass没有哪个非主属性决定另一个非主属性sc 表里sno, cno决定 degree干净。这一点在答辩时经常被问建议自己先能口头说清楚。2.2 建库建表的可执行脚本与字段长度取舍下面这段脚本可以直接在 MySQL 客户端或 Navicat 查询窗口里跑。注意字符集和存储引擎MySQL 5.5 默认引擎是 InnoDB支持事务和外键别用 MyISAM否则外键约束形同虚设。-- 建库字符集用 utf8 避免中文乱码 CREATE DATABASE test DEFAULT CHARACTER SET utf8; USE test; -- 课程表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) );逻辑说明course 的 cno 用 CHAR(5)因为课程编号是定长编码用 VARCHAR 反而多一层长度字节student 的 sno 用 CHAR(9)和原设计一致但 sc 表里 sno 写成了 CHAR(10)这两个长度不一致是个隐患——如果 student 里存 9 位学号sc 里存 10 位JOIN 时 MySQL 会做隐式类型转换数据量小看不出来数据量一大索引直接失效。我一般会把 sc.sno 也改成 CHAR(9)保持两边一致。参数说明CHECK 约束在 MySQL 5.5 里是被解析但忽略的真正生效要 8.0.16 以后。如果你跑在 5.5 上degree 的范围校验得靠应用层或触发器兜底别以为写了 CHECK 就万事大吉。这是很多人答辩被问“你的约束真的生效了吗”时翻车的地方。2.3 插入测试数据别用中文全角标点原稿里的 INSERT 语句用了中文引号和全角逗号直接复制到 MySQL 里会报语法错误。这是血泪经验论文里贴的代码和能跑的代码经常是两回事。下面给一组干净的测试数据覆盖后续查询需要的场景。INSERT INTO teacher VALUES (101, 张老师, 男, 计算机系); INSERT INTO teacher VALUES (102, 李老师, 女, 数学系); INSERT INTO teacher VALUES (103, 王老师, 男, 物理系); INSERT INTO course VALUES (C01, 科学导论, 101); INSERT INTO course VALUES (C02, 高等数学, 102); INSERT INTO course VALUES (C03, 大学物理, 103); INSERT INTO student VALUES (120210332, 吴迪, 男, 计算机科学与技术, 4412); INSERT INTO student VALUES (120210455, 小明, 男, 软件工程, 4413); INSERT INTO student VALUES (120210466, 小红, 女, 计算机科学与技术, 4412); INSERT INTO sc VALUES (120210332, C01, 86.0); INSERT INTO sc VALUES (120210332, C02, 55.5); INSERT INTO sc VALUES (120210455, C01, 72.0); INSERT INTO sc VALUES (120210466, C03, 48.0);注意 teacher 表里 103 号王老师没有对应任何 course这就是后面“查询没有教学任务的老师”要用的数据。测试数据要故意留这种边界不然查询写完了没数据验证等于没写。3. 视图、存储过程、触发器三个加分项怎么用对3.1 视图不是语法练习是查询封装视图在这个作业里的价值是把多表 JOIN 封装成一个“虚拟表”让后续查询不用重复写连接条件。原稿里建了一个 v_student 查选修“科学导论”的学生这个思路对但写法可以更稳。-- 查询选修科学导论的学生学号、姓名、成绩 CREATE VIEW v_student AS SELECT A.sno, A.sname, C.degree FROM student A INNER JOIN sc C ON A.sno C.sno INNER JOIN course B ON C.cno B.cno WHERE B.cname 科学导论; -- 使用视图 SELECT * FROM v_student;逻辑说明这里用 INNER JOIN 而不是逗号连接可读性更好也不容易漏写 WHERE 条件导致笛卡尔积。参数上没什么可调的但要注意视图里的 WHERE 是硬编码课程名如果课程名改了视图结果就空了。更灵活的做法是建一个不带 WHERE 的视图查询时再过滤但作业场景下硬编码反而方便演示。还有一个坑原稿里试图通过视图 view_sc 向 sc 表插入数据。这在简单视图上可行但如果视图带了聚合、DISTINCT、GROUP BY插入就会失败。view_sc 只是SELECT sno, degree, cno FROM sc属于可更新视图插入能成功。但如果你把列顺序写成 sno, degree, cno插入时 VALUES 的顺序必须和视图列顺序一致否则成绩和课程号会串位。这种错误不报错但数据错了查的时候才发现。3.2 存储过程IN 和 OUT 参数别搞反存储过程是这份作业里区分“只会写查询”和“懂数据库编程”的分水岭。原稿给了两个一个按班级查学生一个统计某学生的选课数。第二个用了 IN 和 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 ; -- 调用先定义变量接收 OUT 值 CALL num_sc(120210332, cnt); SELECT cnt;逻辑说明IN 参数是传入的学号OUT 参数是返回的计数。调用时不能直接CALL num_sc(120210332, 0)必须传一个用户变量cnt。这个点新手经常卡住报错“OUT parameter is not a variable”。另外 DELIMITER 的作用是把语句结束符临时从分号改成//否则存储过程体内的分号会被客户端当成过程结束。跑完记得改回分号不然下一条普通 SQL 会一直等你输入。参数说明READS SQL DATA是告诉 MySQL 这个过程只读数据不修改属于权限声明不写也能建但写了更规范。如果过程里要改数据得写MODIFIES SQL DATA。3.3 触发器删除学生时留痕触发器考的是“能不能在数据变动时自动做一件事”。原稿的场景是删除学生时把学号姓名写入 delstudent 备份表这个设计合理但有几个细节要处理。-- 先建一张结构相同的空表用于接收删除记录 CREATE TABLE delstudent ( sno CHAR(9), sname CHAR(8) ); 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 ; -- 验证删除一个学生看备份表是否有记录 DELETE FROM student WHERE sname 小明; SELECT * FROM delstudent;逻辑说明AFTER DELETE 表示删除动作完成后触发OLD 代表被删除的那一行。如果用 BEFORE DELETEOLD 同样可用但插入备份表的时机不同。这里用 AFTER 更符合“删完了留个记录”的语义。坑在于如果 student 表被 sc 表的外键引用删除学生会因为外键约束失败触发器根本不会触发。原设计里 sc.sno 没有声明外键所以删除能成功但这也意味着删了学生之后 sc 里会留下孤儿记录。正确做法是给 sc 加外键并设置 ON DELETE CASCADE或者先在触发器里删 sc 再删 student。作业里为了演示触发器简化处理可以理解但答辩时被问到“删了学生他的成绩怎么办”得能答上来。4. 高频查询与统计把需求逐条翻译成 SQL4.1 不及格统计和无教学任务教师查询需求里明确要“查询和统计各科不及格学生名单”和“查询没有教学任务的老师名单”这两条是业务核心也是老师最容易挑毛病的。-- 各科不及格学生名单按课程分组列出不及格学生 SELECT C.cno, C.cname, A.sno, A.sname, B.degree FROM sc B INNER JOIN student A ON B.sno A.sno INNER JOIN course C ON B.cno C.cno WHERE B.degree 60 ORDER BY C.cno, B.degree; -- 没有教学任务的老师teacher 表中 tno 不在 course 表的 tno 集合里 SELECT tno, tname, tdept FROM teacher WHERE tno NOT IN (SELECT tno FROM course);逻辑说明第一条查询用 degree 60 过滤按课程号排序方便教务处按科目看。第二条用 NOT IN 子查询语义是“教师号没有出现在课程表的任课教师号里”。这里有个 NULL 陷阱如果 course.tno 允许 NULLNOT IN 遇到 NULL 会返回空结果。原设计里 course.tno 是 NOT NULL所以安全。如果哪天改成允许空这条查询就废了得换成 NOT EXISTS。参数说明degree 是 DECIMAL(4,1)比较时 60 会被隐式转成 60.0没问题。但如果 degree 存的是字符串比较就会按字典序100 60 会成立这是另一个翻车点。所以建表时类型选对比查询写得多漂亮都重要。4.2 平均分、多表连接和插入的边界-- 计算 C01 课程平均成绩 SELECT AVG(degree) AS avg_degree FROM sc WHERE cno C01; -- 查询选修高等数学的学生学号和姓名 SELECT A.sno, A.sname FROM student A INNER JOIN sc B ON A.sno B.sno INNER JOIN course C ON B.cno C.cno WHERE C.cname 高等数学; -- 插入一条学生记录只给必填字段 INSERT INTO student (sno, sname, ssex) VALUES (120210455, 小明, 男);逻辑说明AVG 会自动忽略 NULL如果某个学生选了 C01 但 degree 为空他不参与平均分计算。这是符合预期的但要在文档里说明否则老师问“为什么平均分和手算不一样”时能解释。多表连接查询里如果同一个学生在 sc 里有两条 C02 记录正常不该有因为组合主键挡着结果会重复但组合主键保证了不会。插入语句只给 sno、sname、ssex其余字段为 NULL。如果表定义里这些字段是 NOT NULL插入会失败。原设计里 sname、ssex 允许空所以能过。但实际业务里姓名不该为空这是设计上的宽松作业里可以接受生产环境要收紧。5. 避坑与排查这份作业里最容易翻车的五个点5.1 现象建表时报“Specified key was too long”原因CHAR 或 VARCHAR 字段长度加起来超过索引限制或者字符集用了 utf8mb4 导致每个字符占 4 字节组合主键超长。解决把字符集改成 utf8或者缩短字段长度。sc 表的组合主键 sno(10) cno(5) 在 utf8 下是 45 字节远没到限制但如果 sno 写成 VARCHAR(255) 就会炸。5.2 现象存储过程创建成功但 CALL 时报“Unknown procedure”原因DELIMITER 改回分号后存储过程名和库名没对上或者创建时没 USE 到目标库。解决SHOW PROCEDURE STATUS WHERE Db test;确认过程在哪个库调用时加库名前缀CALL test.num_sc(...)。5.3 现象触发器不触发原因删除操作因为外键约束失败或者触发器的 BEFORE/AFTER 时机和操作类型不匹配。解决先确认 DELETE 语句本身能执行成功再检查SHOW TRIGGERS;看触发器是否真的建上了。MySQL 里触发器名不能重复重复建会报错但不一定提示得明显。5.4 现象中文插入变成问号原因数据库、表、连接三处字符集不一致。解决建库时指定DEFAULT CHARACTER SET utf8连接串里加characterEncodingutf8Navicat 里右键数据库选“数据库属性”确认字符集。三处统一基本不会乱码。5.5 现象视图插入数据后查不到原因插入时列顺序和视图定义顺序不一致数据插到了别的列或者视图带了 WITH CHECK OPTION插入的数据不满足视图 WHERE 条件被拒绝。解决SELECT * FROM view_sc;和SELECT * FROM sc;对比看数据到底进了哪。建视图时养成写全列名的习惯别依赖SELECT *。6. 进阶技巧用 EXPLAIN 验证索引是否真的生效作业里提到“建立索引”但很多人建完索引就完事不知道有没有用。我一般会强制走一遍 EXPLAIN看 type 列是不是 ref 或 rangekey 列是不是用上了索引。下面以“按课程号查成绩”为例。-- 在 sc 表的 cno 上建索引 CREATE INDEX idx_sc_cno ON sc(cno); -- 用 EXPLAIN 看查询计划 EXPLAIN SELECT sno, degree FROM sc WHERE cno C01;逻辑说明EXPLAIN 输出里重点看三列。type 如果是 ALL说明全表扫描索引没生效key 如果是 idx_sc_cno说明用上了rows 是预估扫描行数越小越好。如果 type 是 index说明扫了整个索引树比 ALL 好但不如 ref。参数上没什么可调的但要注意如果查询条件里对 cno 做了函数操作比如WHERE LEFT(cno,1) C索引直接失效这是最常见的“建了索引没用上”的原因。再补一个验证数据一致性的习惯每次跑完删除或更新用SELECT COUNT(*)对一下前后行数。比如删了一个学生student 表少 1 行sc 表如果没级联删除应该还是原样这时候就要决定是手动清理还是加外键。我一般会在作业交付前跑一遍这套检查建库脚本从头执行一次、插入测试数据、跑所有查询、删一条数据验证触发器、EXPLAIN 看索引。从那以后我每次交数据库作业都强制走一遍这个流程比事后补文档管用。希望帮到你。本文还有配套的精品资源点击获取