前面整理电脑的时候翻到了刚提交的《数据库系统原理》第三章作业。第三章讲的是 SQL 语言题目不算多但每一道都扎在容易想当然的地方。当时花了一个周末才把全部代码调通过程中踩了触发器递归、NULL 比较、GROUP BY 语义这些坑。今天把这套从读题到提交的完整过程写下来给正在做同类作业或者刚入门 SQL 的人一个参考。我会把作业里没明说但必须懂的知识点也一并拆开讲。1. 作业还没读题我先做完了这两件事拿到作业别急着敲代码。我第三次做这种大作业才总结出教训读题之前的准备工作往往决定后面会不会返工。1.1 先搞清第三章在整门课里的位置我们的教材第三章是「关系数据库标准语言 SQL」前两章分别是数据库系统概论和关系数据库。这意味着第三章的作业默认你已经掌握了两样东西关系模型的三要素关系结构、关系操作、完整性约束。实体完整性、参照完整性、用户定义完整性分别对应 SQL 里的主键、外键、CHECK 约束。第三章作业里大部分题目不会把「这里要用主键」写在你脸上它只给一张表的需求描述比如每个学生有唯一学号课程名不允许重复你需要自己把这些自然语言翻译成约束。我处理这种题目的习惯是先通读一遍全部大题把每道题涉及的实体、属性和关系画成一张草稿表。注意不画复杂的实体关系图只做一个三列清单实体名、主键候选、外键候选。比如作业里有一道学生选课表的设计题我先把「学生」「课程」「选课记录」三个实体列出来选课记录的主键是 (学号, 课程号) 联合主键学号和课程号同时是外键。这一步做完后面建表语句基本就是照着清单翻译。1.2 把题目里的自然语言翻译成主键/外键/约束三个清单这里分享一个非常笨但非常有效的方法。作业题目里通常会出现这些句式题目原话翻译成 SQL 语义唯一标识主键或 UNIQUE 约束必须存在NOT NULL参照某表的某列FOREIGN KEY取值在某个范围CHECK 约束删除时级联/置空ON DELETE CASCADE / SET NULL我们第三章作业第一大题是设计一个简单的图书管理系统数据库其中有一条要求是出版社名称唯一且出版社编号为主键。这句话里「出版社名称唯一」我最初没管直接建了张只有主键的表。后来检查时才意识到题目既然单独强调唯一就是要求出版社名称列加 UNIQUE 约束否则会有两条重名出版社记录违反业务语义。还有一个高频坑作业题目里说删除作者时其图书信息自动删除这就是 ON DELETE CASCADE。如果只写外键不写级联删除作者时会因为存在参照记录而报错题目要求的功能就实现不了。这类隐含条件特别多我建议逐字读题不要放过任何自动必须唯一这种副词。2. 建表环节题目没说的隐性要求都在这里建表是第三章作业的第一步但大部分人的建表语句只能做到能运行距离符合题目全部意图还有距离。这一节我把建表过程的隐性要求逐条讲清楚。2.1 为什么字符集和排序规则不能乱选如果你用的是 MySQL建表语句如果不显式指定字符集默认继承数据库配置。很多学校的实验环境默认是 utf8mb4 或 latin1。第三章作业经常要存中文数据比如书名、作者名、出版社名如果用 latin1 字符集插入中文会变成乱码甚至直接报错。我当时建的book表是这样写的CREATE TABLE book ( book_id CHAR(8) PRIMARY KEY, book_name VARCHAR(100) NOT NULL, publisher_id CHAR(4) NOT NULL, author VARCHAR(50) NOT NULL, price DECIMAL(6,2) CHECK (price 0), publish_date DATE, CONSTRAINT fk_publisher FOREIGN KEY (publisher_id) REFERENCES publisher(publisher_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意几个细节book_id用 CHAR(8) 而不是 INT因为学号、书号这类编号通常有固定长度且有前导零用 INT 会丢掉前导零。价格用DECIMAL(6,2)而不是 FLOAT浮点数存金额会有精度误差。指定ENGINEInnoDB才能用外键约束MyISAM 引擎不检查外键。DEFAULT CHARSETutf8mb4保证中文正常存储。2.2 外键约束写在表里还是写在表外外键约束可以写在列定义内联也可以在表定义末尾单独用CONSTRAINT声明。两者区别在于内联写法适合单列外键命名由系统自动生成表级写法可以自定义约束名方便以后删除约束。我建议作业全部用表级写法原因有二。第一第三章作业经常涉及联合主键和联合外键比如选课表CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(4,1), PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_s FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT fk_sc_c FOREIGN KEY (cno) REFERENCES course(cno) );联合外键这种场景内联写法根本做不了。第二老师批改作业时会看约束命名是否规范fk_sc_s这种命名一眼能看出是从选课表到学生表的外键比系统自动生成的sc_ibfk_1专业得多。2.3 测试数据怎么构造才不容易漏出问题建完表之后要做的事情不是直接写查询题而是往表里插入测试数据。测试数据的质量直接影响你对查询结果正确性的判断。我见过不少同学只插三四行数据而且故意避开边界情况比如没有空值。没有重复值。没有同一作者出版多本书的情况。没有未选课的学生。结果就是查询语句跑出来的结果碰巧是对的但换一批数据就出错。我构造测试数据会故意设计这些场景至少一个学生没有选任何课程。用来看 INNER JOIN 会不会把他漏掉LEFT JOIN 的结果是否正确。至少一门课程没有人选。用来看统计每门课选课人数时是否出现 NULL 值。至少一个学生选了 3 门及以上课程。用来看带 HAVING 的 GROUP BY 分组是否正确。至少两条记录的某个非唯一字段值为空。用来看 WHERE 条件对 NULL 的处理。当时我的student表插入了 8 条数据course表 5 条sc表 12 条。有一道题要求查询没有选修任何课程的学生姓名如果测试数据里恰好每个学生都选了课这条查询根本测不出来。我看到好多同学的作业里这道题的查询结果为空但他们的答案却被判定为错误原因就是测试数据没覆盖这种情况。3. 核心查询题四种最容易丢分的写法和正确思路第三章作业的大头是查询题。这里我不逐题贴代码而是挑出四类高频丢分点每一类都说说为什么这样写会错以及正确的思考路径是什么。3.1 带 EXISTS 的子查询到底怎么读作业里有一道老题「查询选修了全部课程的学生姓名」。很多人的第一反应是用COUNT(*)统计学生选课数然后跟课程总数比较。这个思路对但实现的写法很容易出错。错误的常见写法是这样的SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) (SELECT COUNT(*) FROM course);乍看没问题但如果课程表里有一门课没人选或者选课表里存在重复记录这个统计口径就乱了。更稳妥的做法是用NOT EXISTS双重否定来实现没有一门课该学生没选SELECT sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM course c WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno AND sc.cno c.cno ) );理解这段代码的关键在于从外到内一层一层读最外层是遍历每个学生中间层是遍历每门课程最内层是检查这个学生是否选了这门课。如果存在某门课这个学生没选中间层的查询就会返回一行最外层的NOT EXISTS就为假该学生被过滤掉。平时写代码嵌套循环会让人头疼SQL 里这种关联子查询本质也是嵌套循环。你自己手动模拟一遍执行过程比死记硬背三重 NOT EXISTS 表示全部要有效得多。3.2 GROUP BY 与 HAVING 的过滤顺序还有一道题是「查询平均成绩大于 80 分的学生学号和平均成绩」。大部分同学的初版写法是SELECT sno, AVG(grade) AS avg_grade FROM sc WHERE AVG(grade) 80 GROUP BY sno;这条语句一执行就会报错因为WHERE 子句不能使用聚合函数。正确的做法是用 HAVINGSELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno HAVING AVG(grade) 80;这里我吃了不少苦头专门梳理了 WHERE 和 HAVING 的执行顺序FROM 先取表。WHERE 对每一行原始数据进行过滤此时还没分组所以不能用聚合函数。GROUP BY 按列分组。HAVING 对分组后的结果进行过滤此时可以使用聚合函数。SELECT 最终投影。记住一句话WHERE 过滤的是行HAVING 过滤的是组。第三章作业里好几道题就是考这个区别比如查询选修课程数量大于 3 的学生你必须用 HAVING COUNT(*) 3而不是在 WHERE 里写。3.3 连接查询里的 NULL 陷阱题目「查询所有学生的选课情况包括没有选课的学生」。这个包括没有选课的学生意味着必须用外连接而且要注意连接字段和过滤条件的放置位置。错误写法SELECT student.sno, sname, cno, grade FROM student LEFT JOIN sc ON student.sno sc.sno WHERE sc.grade 60;这个写法的问题在于WHERE 里的grade 60会把没有选课的学生过滤掉因为 NULL 与任何值比较的结果都是 UNKNOWNWHERE 只保留 TRUE 的行。题目要求没有选课的学生也要显示结果被 WHERE 一过滤全没了。正确做法是把过滤条件放进 ON 子句SELECT student.sno, sname, sc.cno, sc.grade FROM student LEFT JOIN sc ON student.sno sc.sno AND sc.grade 60;这样左连接会保留 student 表中所有学生只有选课成绩大于等于 60 的记录才与左表匹配未选课或成绩不达标的行保留 NULL。记住一个定律外连接中对右表列的任何 WHERE 过滤都会使外连接退化为内连接。这是第三章作业里最隐蔽的陷阱之一。3.4 窗口函数能不能用取决于作业允许的 MySQL 版本作业里有一道「查询每门课程成绩排名第一的学生」可以用窗口函数非常优雅地解决SELECT cno, sno, grade FROM ( SELECT cno, sno, grade, ROW_NUMBER() OVER (PARTITION BY cno ORDER BY grade DESC) AS rn FROM sc ) t WHERE rn 1;但这里有个大坑MySQL 8.0 以下版本不支持窗口函数。如果你连接的实验环境是 5.7这条语句直接语法错误。交作业之前务必确认环境版本或者老老实实用分组嵌套查询写出等效代码SELECT sc.cno, sc.sno, sc.grade FROM sc JOIN ( SELECT cno, MAX(grade) AS max_grade FROM sc GROUP BY cno ) t ON sc.cno t.cno AND sc.grade t.max_grade;这种写法的缺点是如果同一门课有多个相同最高分会产生多条记录。作业题如果要求排名第一且允许并列这一版本反而更合适。4. 触发器那道题我调了一下午才明白第三章作业里有一道触发器题目要求是选课表 sc 插入记录时自动将 student 表中对应学生的选课门数加 1。听起来很简单真写起来才发现到处都是坑。4.1 题目的真实考查点不是语法而是新旧值先看学生表需要加一个字段表示选课门数。我们的表结构里有这一列course_count INT DEFAULT 0触发器写法如下DELIMITER // CREATE TRIGGER trg_sc_insert AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count 1 WHERE sno NEW.sno; END // DELIMITER ;这个触发器本身没什么难度真正的考点在于是否理解NEW和OLD两个关键字。AFTER INSERT触发器里只有NEW没有OLDDELETE触发器里只有OLDUPDATE触发器里两者都有。我一开始把NEW.sno写成了sc.sno系统直接报错。因为在触发器内部你不能直接用表名引用当前操作的记录必须通过NEW或OLD去拿当前行的数据。这个认知不转变触发器永远是写一个错一个。4.2 递归触发的坑与解决方案这道题要求删课同时删选课记录且学生选课门数减一。我写了 DELETE 触发器CREATE TRIGGER trg_sc_delete AFTER DELETE ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count - 1 WHERE sno OLD.sno; END;如果仅仅如此没问题。但我当时为了偷懒在删除课程表 course 的时候想通过外键级联删除 sc 的记录再把题目要求的学生选课门数减一也交给触发器处理DELETE FROM course WHERE cno C001;这条语句会触发外键的 ON DELETE CASCADE级联删除 sc 表中该课程的所有选课记录。每删除一条 sc 记录trg_sc_delete触发器都会执行一次confirmed 这不算递归触发因为触发器更新的是 student 表不是再次触发删除 sc但会造成一个严重问题批量删除时触发器逐行执行效率极低而且在某些数据库环境中可能因为锁等待而失败。更关键的是如果学生选了 3 门课其中 2 门是这门被删的课程正常情况下不会出现但测试数据可能不规范级联删除会触发两次触发器course_count 会减 2。逻辑上没问题但你要确保自己的数据模型能处理这种语义。4.3 如何用一张日志表验证触发器的正确性触发器最坑的地方在于它执行成功了但你看不到它干了什么。我调试了一个下午最后是加了一张日志表来追踪CREATE TABLE trigger_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, log_time DATETIME DEFAULT CURRENT_TIMESTAMP, log_type VARCHAR(20), log_sno CHAR(9), log_course_count INT );然后在触发器里加一行日志插入CREATE TRIGGER trg_sc_insert_log AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count 1 WHERE sno NEW.sno; INSERT INTO trigger_log(log_type, log_sno, log_course_count) SELECT INSERT, NEW.sno, course_count FROM student WHERE sno NEW.sno; END;这样每次插入选课记录日志表都会记下当前学生的选课门数。我通过日志表发现了一个意想不到的问题第一次插入选课记录时course_count 从 0 变成 1日志正确。但第二次插入时日志查出来的是 1而该学生实际已经选了 2 门。为什么因为日志表的SELECT查询发生在 UPDATE 语句之后但此时读到的 course_count 是 UPDATE 之后的值所以第二次日志显示 1 是错的——不对让我重新梳理一下。真实的坑是这样的MySQL 中AFTER INSERT触发器里UPDATE student执行完毕之后student 表里的 course_count 已经更新。日志表再插入时SELECT course_count读到的是更新后的值。所以日志显示 1 说明第一次插入后选课门数是 12 说明第二次后是 2完全正确。我之所以懵住是因为日志表里只有当前值没有历史值没法直观看到累加过程。解决方案是在日志表里存更新前后的值CREATE TRIGGER trg_sc_insert_log AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count 1 WHERE sno NEW.sno; INSERT INTO trigger_log(log_type, log_sno, log_course_count) VALUES (INSERT, NEW.sno, (SELECT course_count FROM student WHERE sno NEW.sno)); END;验证完成后把日志相关代码删掉只保留正式触发器。生产环境切忌把调试代码留在作业里。5. 提交前用这组自测用例兜底避免低级错误代码全部写完不等于作业完成。我见过太多人包括我自己因为低级失误被扣分交作业前花半小时做一次系统自测比反复改代码有价值得多。5.1 用一份独立的小数据集做回归作业给的测试数据往往比较温和你自己构造的测试数据又容易写错。最稳妥的方式是另外准备一份最小数据集专门用来验证每道题的结果。比如查询平均成绩大于 80 分的学生我在完整数据集上跑出来的结果里有 3 个学生但我不确定这三个是否真的都是平均分超过 80。于是我单独构造了 2 个学生的数据学生 A选了 2 门课成绩分别是 100 和 60平均分 80。学生 B选了 2 门课成绩分别是 100 和 100平均分 100。用这份数据跑查询如果结果返回 B 而不返回 A说明题目理解正确如果返回了 A说明你的查询条件是 80而不是 80边界条件有偏差。独立小数据集的好处是你可以手工算出预期结果再和 SQL 实际输出对照。我的自查表格大概长这样题号题目要求手工预期结果SQL 实际结果是否一致3.1查询选修全部课程的学生2 人2 人是3.2查询平均成绩 80 的学生3 人3 人是3.3查询所有学生选课情况8 人含 1 人无选课8 人含 1 人无选课是这道工序不复杂但能滤掉至少 80% 的逻辑错误。5.2 格式化与注释的真实作用作业是要被老师批改的代码格式直接决定老师愿不愿意仔细看你的答案。我见过不少同学交上来的 SQL 密得跟压缩文件似的全部挤在一行老师瞄一眼就失去耐心。我提交前会把每一条语句重新排版列名一列一个子查询缩进对齐并在关键子句旁边写注释。比如-- 查询平均成绩大于 80 分的学生学号和平均成绩 SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno HAVING AVG(grade) 80; -- 分组后过滤不能用 WHERE注释不是写给老师看的花架子而是逼自己重新理一遍逻辑。如果某条注释你写不出来说明这个查询你自己也没真正理解。我自己就经历过写注释时发现这里为什么用 INNER JOIN 而不是 LEFT JOIN根本说不清楚重新分析之后才发现漏掉了空值处理的情况。5.3 交作业前晚发现的三个低级错误这一节是我个人的血泪清单每次都核对一下能省好多分分号缺失。一个 SQL 脚本里多个语句之间必须有分号最后一条可以省略但建议都写上。MySQL 命令行下漏分号会一直等输入很容易在最后执行时报错。表名大小写混乱。Linux 环境下 MySQL 表名区分大小写Windows 下不区分。我在本地 Windows 写好的脚本上传到学校 Linux 服务器执行SELECT * FROM Student直接报错说表不存在因为表名建的是student。统一用小写是最稳妥的做法。外键约束导致插入失败。插入测试数据时有先后顺序。先插入父表student、course再插入子表sc否则外键校验不通过。我的脚本里插入数据的顺序一开始是乱的执行时报外键冲突排查半天才发现是顺序问题。把插入语句按依赖关系排序是从第三章作业开始就应该养成的习惯。自测完毕之后我会再从头到尾跑一遍整个脚本建库、建表、插入数据、每个查询题依次执行。确保从零环境可以一键复现全部结果。这一步做完作业才算真正完成。从读懂题意到建表再到查询调通第三章作业真正锻炼的其实不是 SQL 语法而是把模糊的业务需求翻译成精确的数据操作的能力。建表时的每个约束、查询里的每个 JOIN、触发器里的每个 NEW 和 OLD背后都是对数据关系的理解。我在做这份作业时的体会是不怕写错就怕不知道为什么错。用日志表追踪触发器执行、用独立小数据集核对查询结果、用注释逼自己重新讲一遍逻辑这三件事让我在作业之外收获最大。如果你也正在为第三章作业头疼不妨按这个思路重新过一遍先把题目里的每个自然语言要求找出来再动手写 SQL最后一定要用边界数据自测。这几个环节走下来作业拿高分是水到渠成的事。
前面整理电脑的时候翻到了刚提交的《数据库系统原理》第三章作业。第三章讲的是 SQL 语言题目不算多但每一道都扎在容易想当然的地方。当时花了一个周末才把全部代码调通过程中踩了触发器递归、NULL 比较、GROUP BY 语义这些坑。今天把这套从读题到提交的完整过程写下来给正在做同类作业或者刚入门 SQL 的人一个参考。我会把作业里没明说但必须懂的知识点也一并拆开讲。1. 作业还没读题我先做完了这两件事拿到作业别急着敲代码。我第三次做这种大作业才总结出教训读题之前的准备工作往往决定后面会不会返工。1.1 先搞清第三章在整门课里的位置我们的教材第三章是「关系数据库标准语言 SQL」前两章分别是数据库系统概论和关系数据库。这意味着第三章的作业默认你已经掌握了两样东西关系模型的三要素关系结构、关系操作、完整性约束。实体完整性、参照完整性、用户定义完整性分别对应 SQL 里的主键、外键、CHECK 约束。第三章作业里大部分题目不会把「这里要用主键」写在你脸上它只给一张表的需求描述比如每个学生有唯一学号课程名不允许重复你需要自己把这些自然语言翻译成约束。我处理这种题目的习惯是先通读一遍全部大题把每道题涉及的实体、属性和关系画成一张草稿表。注意不画复杂的实体关系图只做一个三列清单实体名、主键候选、外键候选。比如作业里有一道学生选课表的设计题我先把「学生」「课程」「选课记录」三个实体列出来选课记录的主键是 (学号, 课程号) 联合主键学号和课程号同时是外键。这一步做完后面建表语句基本就是照着清单翻译。1.2 把题目里的自然语言翻译成主键/外键/约束三个清单这里分享一个非常笨但非常有效的方法。作业题目里通常会出现这些句式题目原话翻译成 SQL 语义唯一标识主键或 UNIQUE 约束必须存在NOT NULL参照某表的某列FOREIGN KEY取值在某个范围CHECK 约束删除时级联/置空ON DELETE CASCADE / SET NULL我们第三章作业第一大题是设计一个简单的图书管理系统数据库其中有一条要求是出版社名称唯一且出版社编号为主键。这句话里「出版社名称唯一」我最初没管直接建了张只有主键的表。后来检查时才意识到题目既然单独强调唯一就是要求出版社名称列加 UNIQUE 约束否则会有两条重名出版社记录违反业务语义。还有一个高频坑作业题目里说删除作者时其图书信息自动删除这就是 ON DELETE CASCADE。如果只写外键不写级联删除作者时会因为存在参照记录而报错题目要求的功能就实现不了。这类隐含条件特别多我建议逐字读题不要放过任何自动必须唯一这种副词。2. 建表环节题目没说的隐性要求都在这里建表是第三章作业的第一步但大部分人的建表语句只能做到能运行距离符合题目全部意图还有距离。这一节我把建表过程的隐性要求逐条讲清楚。2.1 为什么字符集和排序规则不能乱选如果你用的是 MySQL建表语句如果不显式指定字符集默认继承数据库配置。很多学校的实验环境默认是 utf8mb4 或 latin1。第三章作业经常要存中文数据比如书名、作者名、出版社名如果用 latin1 字符集插入中文会变成乱码甚至直接报错。我当时建的book表是这样写的CREATE TABLE book ( book_id CHAR(8) PRIMARY KEY, book_name VARCHAR(100) NOT NULL, publisher_id CHAR(4) NOT NULL, author VARCHAR(50) NOT NULL, price DECIMAL(6,2) CHECK (price 0), publish_date DATE, CONSTRAINT fk_publisher FOREIGN KEY (publisher_id) REFERENCES publisher(publisher_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意几个细节book_id用 CHAR(8) 而不是 INT因为学号、书号这类编号通常有固定长度且有前导零用 INT 会丢掉前导零。价格用DECIMAL(6,2)而不是 FLOAT浮点数存金额会有精度误差。指定ENGINEInnoDB才能用外键约束MyISAM 引擎不检查外键。DEFAULT CHARSETutf8mb4保证中文正常存储。2.2 外键约束写在表里还是写在表外外键约束可以写在列定义内联也可以在表定义末尾单独用CONSTRAINT声明。两者区别在于内联写法适合单列外键命名由系统自动生成表级写法可以自定义约束名方便以后删除约束。我建议作业全部用表级写法原因有二。第一第三章作业经常涉及联合主键和联合外键比如选课表CREATE TABLE sc ( sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, grade DECIMAL(4,1), PRIMARY KEY (sno, cno), CONSTRAINT fk_sc_s FOREIGN KEY (sno) REFERENCES student(sno), CONSTRAINT fk_sc_c FOREIGN KEY (cno) REFERENCES course(cno) );联合外键这种场景内联写法根本做不了。第二老师批改作业时会看约束命名是否规范fk_sc_s这种命名一眼能看出是从选课表到学生表的外键比系统自动生成的sc_ibfk_1专业得多。2.3 测试数据怎么构造才不容易漏出问题建完表之后要做的事情不是直接写查询题而是往表里插入测试数据。测试数据的质量直接影响你对查询结果正确性的判断。我见过不少同学只插三四行数据而且故意避开边界情况比如没有空值。没有重复值。没有同一作者出版多本书的情况。没有未选课的学生。结果就是查询语句跑出来的结果碰巧是对的但换一批数据就出错。我构造测试数据会故意设计这些场景至少一个学生没有选任何课程。用来看 INNER JOIN 会不会把他漏掉LEFT JOIN 的结果是否正确。至少一门课程没有人选。用来看统计每门课选课人数时是否出现 NULL 值。至少一个学生选了 3 门及以上课程。用来看带 HAVING 的 GROUP BY 分组是否正确。至少两条记录的某个非唯一字段值为空。用来看 WHERE 条件对 NULL 的处理。当时我的student表插入了 8 条数据course表 5 条sc表 12 条。有一道题要求查询没有选修任何课程的学生姓名如果测试数据里恰好每个学生都选了课这条查询根本测不出来。我看到好多同学的作业里这道题的查询结果为空但他们的答案却被判定为错误原因就是测试数据没覆盖这种情况。3. 核心查询题四种最容易丢分的写法和正确思路第三章作业的大头是查询题。这里我不逐题贴代码而是挑出四类高频丢分点每一类都说说为什么这样写会错以及正确的思考路径是什么。3.1 带 EXISTS 的子查询到底怎么读作业里有一道老题「查询选修了全部课程的学生姓名」。很多人的第一反应是用COUNT(*)统计学生选课数然后跟课程总数比较。这个思路对但实现的写法很容易出错。错误的常见写法是这样的SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) (SELECT COUNT(*) FROM course);乍看没问题但如果课程表里有一门课没人选或者选课表里存在重复记录这个统计口径就乱了。更稳妥的做法是用NOT EXISTS双重否定来实现没有一门课该学生没选SELECT sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM course c WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno AND sc.cno c.cno ) );理解这段代码的关键在于从外到内一层一层读最外层是遍历每个学生中间层是遍历每门课程最内层是检查这个学生是否选了这门课。如果存在某门课这个学生没选中间层的查询就会返回一行最外层的NOT EXISTS就为假该学生被过滤掉。平时写代码嵌套循环会让人头疼SQL 里这种关联子查询本质也是嵌套循环。你自己手动模拟一遍执行过程比死记硬背三重 NOT EXISTS 表示全部要有效得多。3.2 GROUP BY 与 HAVING 的过滤顺序还有一道题是「查询平均成绩大于 80 分的学生学号和平均成绩」。大部分同学的初版写法是SELECT sno, AVG(grade) AS avg_grade FROM sc WHERE AVG(grade) 80 GROUP BY sno;这条语句一执行就会报错因为WHERE 子句不能使用聚合函数。正确的做法是用 HAVINGSELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno HAVING AVG(grade) 80;这里我吃了不少苦头专门梳理了 WHERE 和 HAVING 的执行顺序FROM 先取表。WHERE 对每一行原始数据进行过滤此时还没分组所以不能用聚合函数。GROUP BY 按列分组。HAVING 对分组后的结果进行过滤此时可以使用聚合函数。SELECT 最终投影。记住一句话WHERE 过滤的是行HAVING 过滤的是组。第三章作业里好几道题就是考这个区别比如查询选修课程数量大于 3 的学生你必须用 HAVING COUNT(*) 3而不是在 WHERE 里写。3.3 连接查询里的 NULL 陷阱题目「查询所有学生的选课情况包括没有选课的学生」。这个包括没有选课的学生意味着必须用外连接而且要注意连接字段和过滤条件的放置位置。错误写法SELECT student.sno, sname, cno, grade FROM student LEFT JOIN sc ON student.sno sc.sno WHERE sc.grade 60;这个写法的问题在于WHERE 里的grade 60会把没有选课的学生过滤掉因为 NULL 与任何值比较的结果都是 UNKNOWNWHERE 只保留 TRUE 的行。题目要求没有选课的学生也要显示结果被 WHERE 一过滤全没了。正确做法是把过滤条件放进 ON 子句SELECT student.sno, sname, sc.cno, sc.grade FROM student LEFT JOIN sc ON student.sno sc.sno AND sc.grade 60;这样左连接会保留 student 表中所有学生只有选课成绩大于等于 60 的记录才与左表匹配未选课或成绩不达标的行保留 NULL。记住一个定律外连接中对右表列的任何 WHERE 过滤都会使外连接退化为内连接。这是第三章作业里最隐蔽的陷阱之一。3.4 窗口函数能不能用取决于作业允许的 MySQL 版本作业里有一道「查询每门课程成绩排名第一的学生」可以用窗口函数非常优雅地解决SELECT cno, sno, grade FROM ( SELECT cno, sno, grade, ROW_NUMBER() OVER (PARTITION BY cno ORDER BY grade DESC) AS rn FROM sc ) t WHERE rn 1;但这里有个大坑MySQL 8.0 以下版本不支持窗口函数。如果你连接的实验环境是 5.7这条语句直接语法错误。交作业之前务必确认环境版本或者老老实实用分组嵌套查询写出等效代码SELECT sc.cno, sc.sno, sc.grade FROM sc JOIN ( SELECT cno, MAX(grade) AS max_grade FROM sc GROUP BY cno ) t ON sc.cno t.cno AND sc.grade t.max_grade;这种写法的缺点是如果同一门课有多个相同最高分会产生多条记录。作业题如果要求排名第一且允许并列这一版本反而更合适。4. 触发器那道题我调了一下午才明白第三章作业里有一道触发器题目要求是选课表 sc 插入记录时自动将 student 表中对应学生的选课门数加 1。听起来很简单真写起来才发现到处都是坑。4.1 题目的真实考查点不是语法而是新旧值先看学生表需要加一个字段表示选课门数。我们的表结构里有这一列course_count INT DEFAULT 0触发器写法如下DELIMITER // CREATE TRIGGER trg_sc_insert AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count 1 WHERE sno NEW.sno; END // DELIMITER ;这个触发器本身没什么难度真正的考点在于是否理解NEW和OLD两个关键字。AFTER INSERT触发器里只有NEW没有OLDDELETE触发器里只有OLDUPDATE触发器里两者都有。我一开始把NEW.sno写成了sc.sno系统直接报错。因为在触发器内部你不能直接用表名引用当前操作的记录必须通过NEW或OLD去拿当前行的数据。这个认知不转变触发器永远是写一个错一个。4.2 递归触发的坑与解决方案这道题要求删课同时删选课记录且学生选课门数减一。我写了 DELETE 触发器CREATE TRIGGER trg_sc_delete AFTER DELETE ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count - 1 WHERE sno OLD.sno; END;如果仅仅如此没问题。但我当时为了偷懒在删除课程表 course 的时候想通过外键级联删除 sc 的记录再把题目要求的学生选课门数减一也交给触发器处理DELETE FROM course WHERE cno C001;这条语句会触发外键的 ON DELETE CASCADE级联删除 sc 表中该课程的所有选课记录。每删除一条 sc 记录trg_sc_delete触发器都会执行一次confirmed 这不算递归触发因为触发器更新的是 student 表不是再次触发删除 sc但会造成一个严重问题批量删除时触发器逐行执行效率极低而且在某些数据库环境中可能因为锁等待而失败。更关键的是如果学生选了 3 门课其中 2 门是这门被删的课程正常情况下不会出现但测试数据可能不规范级联删除会触发两次触发器course_count 会减 2。逻辑上没问题但你要确保自己的数据模型能处理这种语义。4.3 如何用一张日志表验证触发器的正确性触发器最坑的地方在于它执行成功了但你看不到它干了什么。我调试了一个下午最后是加了一张日志表来追踪CREATE TABLE trigger_log ( log_id INT AUTO_INCREMENT PRIMARY KEY, log_time DATETIME DEFAULT CURRENT_TIMESTAMP, log_type VARCHAR(20), log_sno CHAR(9), log_course_count INT );然后在触发器里加一行日志插入CREATE TRIGGER trg_sc_insert_log AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count 1 WHERE sno NEW.sno; INSERT INTO trigger_log(log_type, log_sno, log_course_count) SELECT INSERT, NEW.sno, course_count FROM student WHERE sno NEW.sno; END;这样每次插入选课记录日志表都会记下当前学生的选课门数。我通过日志表发现了一个意想不到的问题第一次插入选课记录时course_count 从 0 变成 1日志正确。但第二次插入时日志查出来的是 1而该学生实际已经选了 2 门。为什么因为日志表的SELECT查询发生在 UPDATE 语句之后但此时读到的 course_count 是 UPDATE 之后的值所以第二次日志显示 1 是错的——不对让我重新梳理一下。真实的坑是这样的MySQL 中AFTER INSERT触发器里UPDATE student执行完毕之后student 表里的 course_count 已经更新。日志表再插入时SELECT course_count读到的是更新后的值。所以日志显示 1 说明第一次插入后选课门数是 12 说明第二次后是 2完全正确。我之所以懵住是因为日志表里只有当前值没有历史值没法直观看到累加过程。解决方案是在日志表里存更新前后的值CREATE TRIGGER trg_sc_insert_log AFTER INSERT ON sc FOR EACH ROW BEGIN UPDATE student SET course_count course_count 1 WHERE sno NEW.sno; INSERT INTO trigger_log(log_type, log_sno, log_course_count) VALUES (INSERT, NEW.sno, (SELECT course_count FROM student WHERE sno NEW.sno)); END;验证完成后把日志相关代码删掉只保留正式触发器。生产环境切忌把调试代码留在作业里。5. 提交前用这组自测用例兜底避免低级错误代码全部写完不等于作业完成。我见过太多人包括我自己因为低级失误被扣分交作业前花半小时做一次系统自测比反复改代码有价值得多。5.1 用一份独立的小数据集做回归作业给的测试数据往往比较温和你自己构造的测试数据又容易写错。最稳妥的方式是另外准备一份最小数据集专门用来验证每道题的结果。比如查询平均成绩大于 80 分的学生我在完整数据集上跑出来的结果里有 3 个学生但我不确定这三个是否真的都是平均分超过 80。于是我单独构造了 2 个学生的数据学生 A选了 2 门课成绩分别是 100 和 60平均分 80。学生 B选了 2 门课成绩分别是 100 和 100平均分 100。用这份数据跑查询如果结果返回 B 而不返回 A说明题目理解正确如果返回了 A说明你的查询条件是 80而不是 80边界条件有偏差。独立小数据集的好处是你可以手工算出预期结果再和 SQL 实际输出对照。我的自查表格大概长这样题号题目要求手工预期结果SQL 实际结果是否一致3.1查询选修全部课程的学生2 人2 人是3.2查询平均成绩 80 的学生3 人3 人是3.3查询所有学生选课情况8 人含 1 人无选课8 人含 1 人无选课是这道工序不复杂但能滤掉至少 80% 的逻辑错误。5.2 格式化与注释的真实作用作业是要被老师批改的代码格式直接决定老师愿不愿意仔细看你的答案。我见过不少同学交上来的 SQL 密得跟压缩文件似的全部挤在一行老师瞄一眼就失去耐心。我提交前会把每一条语句重新排版列名一列一个子查询缩进对齐并在关键子句旁边写注释。比如-- 查询平均成绩大于 80 分的学生学号和平均成绩 SELECT sno, AVG(grade) AS avg_grade FROM sc GROUP BY sno HAVING AVG(grade) 80; -- 分组后过滤不能用 WHERE注释不是写给老师看的花架子而是逼自己重新理一遍逻辑。如果某条注释你写不出来说明这个查询你自己也没真正理解。我自己就经历过写注释时发现这里为什么用 INNER JOIN 而不是 LEFT JOIN根本说不清楚重新分析之后才发现漏掉了空值处理的情况。5.3 交作业前晚发现的三个低级错误这一节是我个人的血泪清单每次都核对一下能省好多分分号缺失。一个 SQL 脚本里多个语句之间必须有分号最后一条可以省略但建议都写上。MySQL 命令行下漏分号会一直等输入很容易在最后执行时报错。表名大小写混乱。Linux 环境下 MySQL 表名区分大小写Windows 下不区分。我在本地 Windows 写好的脚本上传到学校 Linux 服务器执行SELECT * FROM Student直接报错说表不存在因为表名建的是student。统一用小写是最稳妥的做法。外键约束导致插入失败。插入测试数据时有先后顺序。先插入父表student、course再插入子表sc否则外键校验不通过。我的脚本里插入数据的顺序一开始是乱的执行时报外键冲突排查半天才发现是顺序问题。把插入语句按依赖关系排序是从第三章作业开始就应该养成的习惯。自测完毕之后我会再从头到尾跑一遍整个脚本建库、建表、插入数据、每个查询题依次执行。确保从零环境可以一键复现全部结果。这一步做完作业才算真正完成。从读懂题意到建表再到查询调通第三章作业真正锻炼的其实不是 SQL 语法而是把模糊的业务需求翻译成精确的数据操作的能力。建表时的每个约束、查询里的每个 JOIN、触发器里的每个 NEW 和 OLD背后都是对数据关系的理解。我在做这份作业时的体会是不怕写错就怕不知道为什么错。用日志表追踪触发器执行、用独立小数据集核对查询结果、用注释逼自己重新讲一遍逻辑这三件事让我在作业之外收获最大。如果你也正在为第三章作业头疼不妨按这个思路重新过一遍先把题目里的每个自然语言要求找出来再动手写 SQL最后一定要用边界数据自测。这几个环节走下来作业拿高分是水到渠成的事。
