★ FEATURED ARTICLE 简介围绕北邮研一数据库课程大作业这份文档以学生成绩管理系统为案例面向需要完成数据库课程设计、课程作业或入门关系数据库建模的读者重点解决从需求分析、概念设计到逻辑结构设计与建表的完整方案问题。压缩包共1个docx文件约503KB内容以文字、表格和示例结构组织便于直接查阅和整理课程报告。资料覆盖成绩与教师信息维护、按条件查询、不及格学生统计、无教学任务教师查询等功能需求并给出Course、Student、Sc、Teacher四表的数据需求、主外键约束、局部与全局ER图、数据字典及建表SQL能帮助读者理解一对多联系、组合主键、成绩取值范围校验等设计要点也可据此复盘数据库建模从需求到实现的完整链路。目前已有608人学习下载适合用作课程设计参考与数据库建模练习。1. 一份数据库大作业真正难的地方不在写 SQL拿到这份学生成绩管理系统的时候我以为两小时能收工四个实体、四张表、十几条增删改查课程大作业的标配。真正卡住我的是需求文档本身——文档里 course 表的“选课人数”字段名写的是 tno长度为 3可往下翻到逻辑结构设计course 的关系模式又变成cno, tno, cnametno 明明指教师号student 表的 sno 定义成 char(9)sc 表里的 sno 却是 char(10)。这种前后不一致的字段漂移是数据库大作业里最常见也最难发现的隐性问题外键建不上、数据插不进去报错还只给你一个 1215。这套系统要落地的功能其实很清楚教师录成绩、学生查成绩、教务处统计各科不及格名单、查出没有教学任务的教师。技术栈就是 MySQL 加一个图形化客户端纯 SQL 实现没有后端代码。它适合两类人研一刚接触数据库课程设计、需要一份能跑通的完整参考的以及已经写完 SQL 但被约束、视图、触发器这些对象折腾得怀疑人生的。下面按我实际复现的顺序拆。2. 需求到表结构四个关系、三种主键和一条外键链2.1 四张表各自守着什么数据这套系统的实体划分是教科书式的四个学生、教师、课程、成绩。关键在成绩。成绩既不属于学生也不属于课程它是两者发生关系时产生的数据所以必须单独抽一张表出来这就是 scstudent-course表存在的理由。很多同学图省事把 degree 直接塞进 student 表结果一个学生只能存一门课的成绩选第二门就得加字段这条路走两步就死了。四张表的职责边界我梳理成这样表名存的什么主键关键约束teacher教师基本信息与所属系别tno性别只能取男/女student学生基本信息、专业、班级sno学号唯一且非空course课程名与授课教师cnotno 指向 teachersc谁选了哪门课、考了多少分(sno, cno)成绩 0–100两个外键这里有个必须提前定死的事course 表里的 tno 到底代表什么。文档开头写“选课人数tno”但长度只有 3选课人数不可能这么短逻辑结构设计里 course 的关系模式是cno, tno, cnametno 和 teacher 的主键同名同长度。按教师号处理是唯一自洽的解释。选课人数这种派生数据也不该落表要用的时候COUNT(*)从 sc 里算就行存进去反而要维护一致性纯属给自己找事。同理teacher 表的 tdept 在数据需求里被描述成“教师地址”长度 16而逻辑结构里对应的属性是系别/专业。char(16) 装一个系名刚好装地址明显不够。按系别建模。2.2 主键怎么选单列主键与 sc 表的组合主键student、teacher、course 三张表都是单列主键没什么争议。真正需要想清楚的是 sc 表。一个学生可以选多门课一门课可以被多个学生选这是典型的 m:n 关系落到关系模型上必须用组合主键 (sno, cno) ——单拿 sno 做主键会导致一个学生只能有一条成绩记录单拿 cno 做主键会导致一门课只能有一个学生。组合主键顺带解决了一个业务约束同一个学生不能重复选同一门课。数据库层面直接把这件事故杀在插入阶段应用层就不用再写一遍“你已选过该课程”的判断。这也是我后来做二次开发时最庆幸的一个决定——不用在代码里补去重逻辑。第三范式在这个设计里是自然满足的student 表里没有课程信息course 表里没有学生信息sc 表里只有两个外键和成绩本身没有任何非主属性依赖非主键属性。原文说四张表“满足第三范式”这个结论是对的但推导过程值得自己走一遍答辩时被问到“为什么满足 3NF”能答上来比背结论有用。2.3 外键、CHECK 与第三范式约束写进 DDL 才算数约束这件事写在文档里只是承诺写进 DDL 才是强制执行。我见过太多作业文档里写着“成绩取值 1-100”建表时忘了 CHECK最后数据库里躺着 120 分的记录统计平均分直接失真。外键链的依赖顺序也得排好teacher 和 student 先建course 依赖 teachersc 依赖 student 和 course。建表顺序错了会直接报 1215Cannot add foreign key constraint。-- 建库时把字符集写死别指望服务器默认值是 utf8mb4 CREATE DATABASE score_mgmt DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE score_mgmt; -- 教师表先建因为 course 要引用它 CREATE TABLE teacher ( tno CHAR(3) NOT NULL COMMENT 教师号, tname VARCHAR(8) NULL COMMENT 姓名, tsex CHAR(2) NULL COMMENT 性别男/女, tdept VARCHAR(16) NULL COMMENT 所属系别, PRIMARY KEY (tno), CONSTRAINT ck_teacher_sex CHECK (tsex IN (男, 女)) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;这段 DDL 里有三个点值得说。第一ENGINEInnoDB必须显式指定MyISAM 不支持外键和事务用它建出来的表外键会被静默忽略你以为约束生效了其实根本没有。第二CHECK 约束在 MySQL 8.0.16 之前是被解析但忽略的5.5、5.7 上写了也白写这点后面避坑章节会展开。第三VARCHAR 而不是 CHAR 存姓名和系别中文在不同编码下占的字节数不一样CHAR 定长容易截断。课程表和学生表按同样思路建重点是外键的类型必须和被引用列完全一致 —— 长度、字符集、有无符号都要对齐这是 1215 最常见的原因。CREATE TABLE student ( sno CHAR(9) NOT NULL COMMENT 学号, sname VARCHAR(8) NULL COMMENT 姓名, ssex CHAR(2) NULL COMMENT 性别, smajor VARCHAR(20) NULL COMMENT 专业, sclass VARCHAR(10) NULL COMMENT 班级, PRIMARY KEY (sno) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; CREATE TABLE course ( cno CHAR(5) NOT NULL COMMENT 课程号, cname VARCHAR(20) NOT NULL COMMENT 课程名, tno CHAR(3) NULL COMMENT 授课教师号, PRIMARY KEY (cno), CONSTRAINT fk_course_teacher FOREIGN KEY (tno) REFERENCES teacher (tno) ON UPDATE CASCADE ON DELETE SET NULL ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; -- 成绩表组合主键挡住重复选课两个外键保证引用完整性 CREATE TABLE sc ( sno CHAR(9) NOT NULL COMMENT 学号, cno CHAR(5) NOT NULL COMMENT 课程号, degree DECIMAL(4,1) NULL COMMENT 成绩 0-100, PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_student FOREIGN KEY (sno) REFERENCES student (sno) ON DELETE CASCADE, CONSTRAINT fk_sc_course FOREIGN KEY (cno) REFERENCES course (cno) ON DELETE CASCADE, CONSTRAINT ck_sc_degree CHECK (degree 0 AND degree 100) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4;ON DELETE CASCADE这个选择要交代一下学生退学或者课程取消时sc 里的关联记录没有独立存在的意义级联删除是合理的。反过来 course 表的ON DELETE SET NULL意思是教师离职了课程本身还在只是暂时没有授课人——这条正好呼应需求里的“查询没有教学任务的教师”如果一个教师被删了课程也跟着没了那条统计就没意义了。degree DECIMAL(4,1)里 4 是总位数、1 是小数位能表示的最大值是 999.9放 0–100 绰绰有余。用 FLOAT 存成绩会撞上二进制浮点的精度问题平均分算出来 79.66666666导出报表时被质疑。3. 把库跑起来从空 MySQL 到能查出不及格名单3.1 环境准备MySQL 版本和客户端怎么选原项目用的是 MySQL 5.5 加图形化客户端。我的建议是别守着 5.5直接上 8.0窗口函数、CTE、真正生效的 CHECK 约束这些在写统计查询时省事太多而且 8.0 的报错信息比 5.5 详细一个量级。如果你的学校机房锁死了版本那就得知道哪些语法不能用后面避坑章节会列。客户端方面图形化工具确实能把建表、插数据这种重复劳动提速原文作者也提到了从命令行转图形界面后的效率提升。但我自己的习惯是建表、建约束、建对象一律用脚本跑图形界面只用来临时看数据。理由是脚本能进版本控制、能一键重建、出问题能 diff图形界面点出来的表结构过两周你自己都想不起来当时点了什么。所以下面的流程全部给可复现的 SQL。初始化就三步# 登录不同安装方式命令略有差异Windows 下走客户端自带的命令行即可 mysql -u root -p # 验证版本决定后面能不能用窗口函数 SELECT VERSION(); # 看默认字符集如果是 latin1建库时必须显式指定 SHOW VARIABLES LIKE character_set_%;版本号决定语法边界8.0 以上可以用ROW_NUMBER()排名、用WITH写 CTE5.7 及以下这些全没有只能靠自连接或者变量模拟。字符集那一行更关键如果character_set_server是 latin1不显式指定的话中文姓名插进去就是问号而且这个错误不报异常你只有查出来才知道数据已经毁了。3.2 一次建库建表的完整脚本把上一章的 DDL 按 teacher → student → course → sc 的顺序存成一个schema.sql然后一次性执行mysql -u root -p schema.sql # 执行完立刻验证四张表都在且引擎是 InnoDB mysql -u root -p -e USE score_mgmt; SHOW TABLES; SHOW TABLE STATUS\G | grep -E Name|Engine用文件重定向执行而不是粘贴到客户端里好处是出错会中断在具体某一行你能立刻定位是第几条语句挂了。如果中途失败别在残留的半成品库上继续跑直接DROP DATABASE score_mgmt;重新来一遍——这是我最常犯的低级错误在脏库上反复修修到最后自己也搞不清哪张表是新的哪张是旧的。建完立刻做一次结构自检把外键和约束真实落库的情况查出来-- 确认外键都建上了别只看建表语句没报错就以为成了 SELECT TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA score_mgmt AND REFERENCED_TABLE_NAME IS NOT NULL; -- 确认 CHECK 约束在 8.0 上确实生效5.7 上这里会是空的 SELECT CONSTRAINT_NAME, CHECK_CLAUSE FROM information_schema.CHECK_CONSTRAINTS WHERE CONSTRAINT_SCHEMA score_mgmt;第一条查询返回四行才算外键齐了course→teacher 一条sc→student、sc→course 两条正好三条外键对应四条记录sc 有两条外键。少了哪条就说明对应的语句被跳过了通常是类型不匹配导致建表失败但脚本继续往下跑。3.3 造数据三条 INSERT 和一轮自检数据量不用大但类型要全。至少要覆盖一个学生选多门课、一门课多个学生、有成绩为 NULL 的选了没考、有不及格的、有教师没排课的。INSERT INTO teacher VALUES (T01, 周老师, 男, 计算机系), (T02, 吴老师, 女, 数学系), (T03, 郑老师, 男, 外语系); -- 这位不排课用来验证无教学任务查询 INSERT INTO student VALUES (202101001, 张同学, 男, 计算机科学与技术, 4412), (202101002, 李同学, 女, 软件工程, 4412), (202101003, 王同学, 男, 数学与应用数学, 4413); INSERT INTO course VALUES (C01, 科学导论, T01), (C02, 高等数学, T02), (C03, 数据库原理, T01); -- 故意留一条 degree 为 NULL代表选课但缺考 INSERT INTO sc VALUES (202101001, C01, 86.0), (202101001, C02, 55.5), (202101002, C01, 92.0), (202101002, C03, NULL), (202101003, C02, 78.0);插完立刻做三个方向的校验这一步别省-- 1. 行数对不对 SELECT teacher AS t, COUNT(*) FROM teacher UNION ALL SELECT student, COUNT(*) FROM student UNION ALL SELECT course, COUNT(*) FROM course UNION ALL SELECT sc, COUNT(*) FROM sc; -- 2. 有没有脏数据成绩越界或外键悬空 SELECT * FROM sc WHERE degree 0 OR degree 100; SELECT * FROM sc WHERE sno NOT IN (SELECT sno FROM student); -- 3. 重复选课能不能被主键挡住这条应该报错 INSERT INTO sc VALUES (202101001, C01, 60.0);第三条应该报Duplicate entry如果它插成功了说明你的组合主键没建对赶紧回去查建表语句。这种“故意插非法数据看它会不会报错”的验证方式比看建表语句有没有执行成功可靠得多——数据库不会骗你报错信息会。3.4 索引加上去再看执行计划四张表的主键自带索引组合主键 (sno, cno) 的索引能加速“查某学生的所有成绩”因为最左前缀是 sno。但反过来“查某门课的所有成绩”用不上这个索引得单独给 cno 建一个。-- 按课程统计成绩时会用到不及格名单、平均分都走这条路 CREATE INDEX idx_sc_cno ON sc (cno); -- 按班级筛学生教务处的查询基本都带这个条件 CREATE INDEX idx_student_class ON student (sclass); -- 查某教师的授课课程 CREATE INDEX idx_course_tno ON course (tno);建完用EXPLAIN确认索引真被用上了而不是建了放着EXPLAIN SELECT sno, degree FROM sc WHERE cno C01; -- 关注 type 列ref 或 range 说明走索引了ALL 就是全表扫描数据量只有几十条的时候走不走索引根本无所谓优化器甚至可能因为数据太少直接选全表扫描——这不是索引失效是它算出来全扫更便宜。判断索引有没有用得把数据造到几千行再看EXPLAIN或者看key列有没有值。这是我当初误判过一次的地方白删了一个好索引。4. 视图、存储过程、触发器把重复查询固化成数据库对象4.1 视图把三表连接封成一张可以查的“表”系统里高频出现的查询是“某学生的成绩单”要 join student、course、sc 三张表。每写一次都要重复同样的连接条件写错一个少条记录排查半天。视图就是干这个的把连接逻辑固定下来外面当一张表用。-- 成绩明细视图学生 课程 分数一次定义反复用 CREATE OR REPLACE VIEW v_score_detail AS SELECT s.sno, s.sname, s.sclass, c.cno, c.cname, sc.degree FROM sc JOIN student s ON s.sno sc.sno JOIN course c ON c.cno sc.cno; -- 查某个学生的全部成绩一行就够 SELECT cname, degree FROM v_score_detail WHERE sno 202101001;注意这里用的是JOIN而不是逗号加 WHERE。原文档里的写法是FROM student A, course B, sc C WHERE A.snoC.sno AND B.cnoC.cno结果一样但连接条件和过滤条件混在一起后面加一个AND cname科学导论很容易分不清哪句是连接哪句是筛选。显式 JOIN 把两件事分开写出错概率低得多。视图还有个容易踩的边界不是所有视图都能改。上面这种多表 JOIN 出来的视图在 MySQL 里是只读的对它执行 INSERT 会直接报The target table of the INSERT statement is not updatable。单表视图哪怕带 WHERE 过滤才可能可更新。所以原文档里那个view_sc只从 sc 单表取三列就能往里插数据——不是视图有什么特殊写法而是它恰好满足可更新视图的条件。-- 单表视图可更新改这里等于改 sc CREATE OR REPLACE VIEW v_sc_simple AS SELECT sno, cno, degree FROM sc WHERE degree IS NOT NULL; -- 这两条都能成功 INSERT INTO v_sc_simple VALUES (202101003, C01, 88.0); UPDATE v_sc_simple SET degree 90.0 WHERE sno 202101003 AND cno C01;可更新视图的限制条件在 MySQL 文档里有完整列表不能有聚合函数、不能有 DISTINCT、不能有 GROUP BY、不能有 UNION、不能引用多个表。二次开发时如果前端直接对着视图写增删改这些限制会变成很难解释的报错稳妥做法是读写分离——查询走视图写入走基表。4.2 存储过程带 IN / OUT 参数的统计接口存储过程的价值在于把“一组 SQL 流程控制”封成一个可调用的单元。这套系统里最典型的是两个按班级查学生、统计某学生的选课门数。DELIMITER $$ -- IN 参数调用时传班级号进去 CREATE PROCEDURE proc_class_students(IN p_class VARCHAR(10)) READS SQL DATA BEGIN SELECT sno, sname, smajor FROM student WHERE sclass p_class ORDER BY sno; END $$ DELIMITER ;DELIMITER $$这段是必须的因为存储过程体内部有分号客户端默认把分号当语句结束符不加分隔符的话会把过程体从中间截断报一个莫名其妙的语法错误。READS SQL DATA是声明这个过程只读不写让优化器和权限系统知道它的行为写漏了不报错但语义上不准确。调用就是CALL proc_class_students(4412);。第二个带 OUT 参数这个更实用因为它能返回一个标量值给应用层DELIMITER $$ -- IN 传学号OUT 把门数带出来 CREATE PROCEDURE num_sc(IN p_sno CHAR(9), OUT p_count INT) READS SQL DATA BEGIN SELECT COUNT(*) INTO p_count FROM sc WHERE sno p_sno; END $$ DELIMITER ; -- 调用方式OUT 参数要用会话变量接 CALL num_sc(202101001, cnt); SELECT cnt AS course_count;参数方向是新手最容易搞混的地方。IN是单向传入过程里改了外面的变量不变OUT是单向带出进过程时初值被丢弃INOUT才是双向。上面这个num_sc如果写成IN p_count INT调用时传个数字进去过程执行完外面拿不到结果你会以为统计失败其实只是方向写错了。另外注意WHERE sclass p_class是精确匹配。原文档用的是LIKE %4412%看起来也能查出来但%前后都通配意味着任何包含 4412 的字符串都会被命中比如44120、X4412Y。班级号是定长编码精确匹配才是对的。4.3 触发器删学生时自动留档需求里没明说但做数据管理绕不开一个问题删掉的学生怎么追溯直接DELETE之后这个人的成绩记录因为级联删除也一并没了出了纠纷连原始数据都找不回。触发器在这里就是后悔药。-- 先建一张留档表结构跟 student 对齐多一个删除时间 CREATE TABLE delstudent ( sno CHAR(9) NOT NULL, sname VARCHAR(8) NULL, del_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE InnoDB DEFAULT CHARSET utf8mb4; DELIMITER $$ CREATE TRIGGER trig_student_del AFTER DELETE ON student FOR EACH ROW BEGIN INSERT INTO delstudent (sno, sname) VALUES (OLD.sno, OLD.sname); END $$ DELIMITER ;FOR EACH ROW是行级触发器的标志一条 DELETE 删了 10 行就会触发 10 次每次OLD指向当前这一行的旧值。AFTER而不是BEFORE是因为删除动作确认成功之后再留档更合理如果删除因为外键约束失败BEFORE 触发器可能已经写了一笔不该存在的记录。验证的时候一定要用真实删除别用TRUNCATEDELETE FROM student WHERE sno 202101002; SELECT * FROM delstudent; -- 应该有一条对应记录这两句的差别是血泪经验TRUNCATE TABLE不走触发器因为它本质是重建表而不是逐行删除。我当初用 TRUNCATE 清测试数据然后纳闷为什么留档表一直是空的查了半小时才想起来这回事。还有一点如果 sc 表的外键是ON DELETE CASCADE删除学生时会先级联删掉 sc 的记录再触发 student 的 AFTER DELETE。顺序没问题但如果留档需要连成绩一起存就得在留档表里多存几列靠触发器里再查一次 sc——不过那时候 sc 的记录已经没了得改用 BEFORE DELETE。这种先后顺序的坑不实际跑一遍根本想不到。4.4 几条高频统计 SQL不及格名单与无课教师需求文档里明确要求的两条统计原文只给了其中一条的实现另一条我按常见做法补上。各科不及格学生名单按课程分组输出SELECT c.cno, c.cname, s.sno, s.sname, s.sclass, sc.degree FROM sc JOIN student s ON s.sno sc.sno JOIN course c ON c.cno sc.cno WHERE sc.degree 60 ORDER BY c.cno, sc.degree;WHERE sc.degree 60会把 NULL 排除掉这正好符合业务语义——缺考不算不及格它是另一个类别要单独统计的话得用degree IS NULL。这两个条件混在一起写是常见错误统计出来的挂科率会虚高。没有教学任务的教师名单用 NOT EXISTSSELECT t.tno, t.tname, t.tdept FROM teacher t WHERE NOT EXISTS ( SELECT 1 FROM course c WHERE c.tno t.tno );NOT EXISTS比NOT IN稳因为NOT IN遇到子查询结果里有 NULL 会整体返回空集——如果 course 表允许 tno 为 NULL我上面的 DDL 里确实允许因为 ON DELETE SET NULLNOT IN就废了。这是 SQL 里最阴的一个坑反直觉且不报错只是结果莫名其妙变空。再补一条教务处常用的合并统计每门课的平均分、最高分、不及格人数一次算出来SELECT c.cno, c.cname, COUNT(sc.sno) AS 选课人数, ROUND(AVG(sc.degree), 1) AS 平均分, MAX(sc.degree) AS 最高分, SUM(CASE WHEN sc.degree 60 THEN 1 ELSE 0 END) AS 不及格人数 FROM course c LEFT JOIN sc ON sc.cno c.cno GROUP BY c.cno, c.cname ORDER BY 平均分 DESC;两处细节。第一LEFT JOIN而不是JOIN否则一门没人选的课会直接从结果里消失教务处看到的课程列表就少了几行。第二AVG会自动忽略 NULL所以缺考的学生不会拉低平均分这是想要的行为但如果要按“选课人数”算挂科率分母得用COUNT(sc.sno)而不是COUNT(*)后者会把 LEFT JOIN 产生的空行也算进去。5. 避坑与排查外键建不上、CHECK 失效这些坑5.1 建表阶段的三个硬坑现象一建 sc 表时报 1215Cannot add foreign key constraint但语句看起来完全正确。原因基本只有一个被引用列和引用列的类型不一致。原文档里 student.sno 定义成 char(9)sc.sno 定义成 char(10)长度差一位MySQL 就拒绝建立外键。字符集不同一张表 utf8mb4、另一张 latin1、有无符号不同INT 对 INT UNSIGNED报的都是同一个 1215错误信息里不会告诉你具体哪里不匹配。解决方式是建表前把所有外键列的类型对齐一条条核。核完还报错用这条查询看实际落库的类型SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, CHARACTER_SET_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA score_mgmt AND COLUMN_NAME IN (sno, cno, tno);还有一种情况父表根本没建成或者引擎是 MyISAM。MyISAM 不支持外键建表时不报错外键被静默丢弃你在KEY_COLUMN_USAGE里查不到任何记录但表看起来一切正常。现象二写了 CHECK (degree BETWEEN 0 AND 100)插 200 分照样成功。MySQL 8.0.16 之前的版本会解析 CHECK 语法但不执行5.5、5.7 上全是这个行为。解决方式有两条升级到 8.0.16或者在应用层加校验再或者在 sc 表上挂一个 BEFORE INSERT / BEFORE UPDATE 触发器用SIGNAL SQLSTATE 45000抛异常。第三条是兼容老版本的通用做法代价是每次写入多一次触发器开销。现象三中文姓名插进去变成一串问号或者报 Incorrect string value。建库没指定字符集服务器默认是 latin1。这个问题最恶劣的地方是不报错你只有查出来才发现数据已经毁了而且毁了之后改字符集也救不回来——存进去的就是问号。解决方式是从建库开始一路显式指定utf8mb4库、表、连接三处都要。连接那一层容易被忽略客户端建立连接时不带字符集参数插进去照样乱码需要在连接串上补?characterEncodingutf8或者在会话里执行SET NAMES utf8mb4;。5.2 数据写入与触发器阶段的坑现象四批量删了 20 个学生留档表里一条记录都没有。用了TRUNCATE TABLE student。TRUNCATE 是 DDL 操作做法是丢弃数据文件重建表不逐行删除所以不触发任何触发器也不写入 binlog 的行事件。解决方式是老老实实用DELETE FROM student WHERE ...或者接受 TRUNCATE 不触发触发器这个事实在应用层单独记录操作日志。这个坑我踩过一次之后再写清表脚本都会先问自己一句这张表上有触发器吗。现象五删除学生时报 1451Cannot delete or update a parent row。sc 表的外键没写ON DELETE CASCADE默认行为是 RESTRICT父表记录被引用了就不许删。解决方式是先删子表记录再删父表或者把外键改成级联删除。改外键的语法是ALTER TABLE sc DROP FOREIGN KEY fk_sc_student再重新 ADD注意 DROP 的时候用约束名而不是列名。这里还有个顺序问题值得记一笔如果留档触发器是 BEFORE DELETE而此时 sc 的记录还没被级联删掉触发器里能查到成绩如果改成 AFTER DELETE级联已经执行完sc 里查不到了。要留成绩就得用 BEFORE要保证删除成功才留档就用 AFTER二选一没有两全的方案。5.3 查询与对象维护阶段的坑现象六往多表 JOIN 的视图里插数据报错 the target table is not updatable。视图可更新是有条件的不能有聚合、DISTINCT、GROUP BY、UNION不能引用多个表不能有子查询在 SELECT 列表里。上面那个v_score_detail三表 JOIN天生只读。解决方式是写入走基表视图只负责查询。如果前端图省事想直接对视图做增删改那得为每种写操作单独建单表视图或者干脆加一层后端接口。现象七存储过程建到一半报语法错误光标指着过程体中间某一行。DELIMITER忘了改或者改完忘了改回来。客户端看到过程体里的第一个分号就把语句截断了。解决方式是DELIMITER $$开头、DELIMITER ;结尾成对出现。改回来那一句漏了的话后续所有普通 SQL 都会因为找不到$$结束符而卡住表现是敲了回车没反应很容易误以为是客户端卡死。现象八NOT IN子查询返回空结果但数据明明存在。子查询结果集里含 NULL。NOT IN (1, 2, NULL)的语义是“不等于 1 且不等于 2 且不等于 NULL”而任何值和 NULL 比较的结果都是 UNKNOWN整个条件永远不成立。解决方式是改用NOT EXISTS或者在子查询里加WHERE tno IS NOT NULL。这条坑没有任何报错只有结果不对排查时优先怀疑它。6. 二次开发从课程作业到能挂出去的教务小工具6.1 加一层薄接口把 SQL 收进后端纯 SQL 的作业交上去没问题但真要给教务用学生不可能直连数据库。加一层最薄的后端把这几个查询暴露成 HTTP 接口工作量其实很小。用 Node Express 举例连接池加三个路由就够跑起来// db.js连接池避免每次请求都新建连接 const mysql require(mysql2/promise); const pool mysql.createPool({ host: 127.0.0.1, user: score_app, // 别用 root权限收窄到这张库 password: process.env.DB_PWD, database: score_mgmt, charset: utf8mb4, // 连接层也要指定否则中文乱码 connectionLimit: 10, waitForConnections: true }); module.exports pool;// routes.js三个只读接口参数全部走占位符 const express require(express); const pool require(./db); const router express.Router(); // 学生查自己的成绩 router.get(/score/:sno, async (req, res) { const [rows] await pool.query( SELECT cname, degree FROM v_score_detail WHERE sno ?, [req.params.sno] // 占位符绝不拼字符串 ); res.json({ code: 0, data: rows }); }); // 教务处查某门课的不及格名单 router.get(/fail/:cno, async (req, res) { const [rows] await pool.query( SELECT s.sno, s.sname, s.sclass, sc.degree FROM sc JOIN student s ON s.sno sc.sno WHERE sc.cno ? AND sc.degree 60 ORDER BY sc.degree, [req.params.cno] ); res.json({ code: 0, data: rows }); }); // 无教学任务的教师 router.get(/teacher/idle, async (req, res) { const [rows] await pool.query( SELECT t.tno, t.tname, t.tdept FROM teacher t WHERE NOT EXISTS (SELECT 1 FROM course c WHERE c.tno t.tno) ); res.json({ code: 0, data: rows }); }); module.exports router;参数必须用?占位符这是硬性要求。字符串拼接出来的 SQL 遇到sno 1 OR 11直接把全表数据吐出去学生查成绩的接口变成数据泄露入口。连接池这一层也别省每个请求新建连接在高并发下会把数据库的连接数打满报Too many connections。数据库账号要单独建只给这张库的 SELECT / INSERT / UPDATE不给 DROP 和 GRANTCREATE USER score_app127.0.0.1 IDENTIFIED BY 换成强密码; GRANT SELECT, INSERT, UPDATE ON score_mgmt.* TO score_app127.0.0.1; FLUSH PRIVILEGES;6.2 权限、分页与导出三个最先要补的点真要用起来这三件事一定会被提出来。第一是权限教师只能录自己课的成绩。这条在数据库层可以靠视图 应用层过滤实现接口里强制带上tno 当前登录教师的条件不要指望前端传什么就信什么。第二是分页。学生表几千行的时候SELECT * FROM student一次返回全部前端直接卡死。改成LIMIT ? OFFSET ?注意深分页OFFSET 很大会越翻越慢常见做法是改成基于主键的游标分页WHERE sno ? ORDER BY sno LIMIT ?。第三是导出。教务处要 Excel最简单的做法是接口直接返回 CSV注意加 BOM 头否则 Excel 打开中文全是乱码。改造点涉及表/对象改动量优先级接口层 参数化查询全部中高教师维度权限过滤course、sc小高分页与游标翻页student、sc小中CSV 导出带 BOM视图 v_score_detail小中成绩变更审计表新增 audit 表 触发器中低选课人数冗余字段course大要维护一致性不建议最后一行是我特意列的选课人数千万别加冗余字段。需求文档里那个“选课人数tno”的表述很容易诱导人去 course 表加一列存人数一旦加上每次 sc 增删都要同步更新漏一次数据就不一致还得写定时任务对账。用COUNT(*)实时算几千行的量级下根本不是瓶颈。6.3 改完怎么验证边界数据清单改造之后最容易出问题的地方不是主流程是边界。我一般会准备一份固定清单每次改完都跑一遍-- 成绩边界0 分、100 分、小数、NULL INSERT INTO sc VALUES (202101003, C03, 0.0); INSERT INTO sc VALUES (202101001, C03, 100.0); -- 下面这条应该失败如果 CHECK 生效 INSERT INTO sc VALUES (202101002, C02, 101.0); -- 重复选课应失败 INSERT INTO sc VALUES (202101001, C01, 60.0); -- 统计口径NULL 成绩不该计入平均分但应计入选课人数 SELECT cno, COUNT(*) AS 选课数, COUNT(degree) AS 有成绩数, AVG(degree) AS 均分 FROM sc GROUP BY cno;重点看最后那条COUNT(*)和COUNT(degree)的差值就是缺考人数。如果两个数相等说明你的测试数据里没有 NULL 成绩覆盖不全得补一条。还有一个容易被忽略的验证角度把sql_mode设成严格模式再跑一次全部脚本。严格模式下插入超长字符串会报错而不是静默截断很多在宽松模式下“正常”的代码会立刻暴露问题SET SESSION sql_mode STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE;我自己有个习惯每次交付带数据库的项目都会在项目根目录放一个rebuild.sh内容就是删库、建库、跑 schema、灌测试数据、跑一遍验证 SQL最后打印“OK”或者具体的失败行。这脚本平时看着多余等到某天你改了一个字段长度、忘了同步改测试数据一跑就停在正确的行号上——比在生产库上手动改字段然后第二天早上被电话叫醒强太多。从那以后我每次改表结构都强制走一遍这个脚本哪怕只改了一个注释。这套东西的骨架就这么多四张表、一条外键链、几个数据库对象剩下的都是往里填业务。希望帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
