数据库实验二SQL查询全解析：从连接子查询到避坑自查指南

数据库这门课实验一还风和日丽实验二就开始让人挠头了。我最近帮同学复盘某高校《数据库原理及应用》实验二个人版发现大多数人并不是不会写SQL而是不知道从哪儿下手、报错之后怎么排查。这份“个人版”的意思就是我自己补完整的一份带注释版本不是老师发的标准答案而是把每一句查询的来龙去脉都写清楚。内容适合两类人一类是正在做同样实验、卡在某个查询出不来结果的同学另一类是已经交了作业但还想搞明白“为什么这样写”的初学者。我会从基础查询讲起把连接、子查询和最后提交前的自查清单全部过一遍重点是那些我在实验里真实遇到、并且花了挺长时间才定位的坑。1. 实验二到底考什么从建表思维切换到查询思维1.1 为什么实验一轻松、实验二开始懵实验一通常是建库建表语句相对固定照着模板敲CREATE TABLE基本不会出错。实验二则完全不同它会直接扔给你一批业务问题比如“找出平均成绩高于80分的学生名单”“列出每个学生的选课数量”你得自己组织逻辑、选择语法。这种从“照着写”到“想清楚再写”的切换才是实验二真正的门槛。我见过不少同学在实验二里反复栽跟头表面上是语法不熟实际上是把中文问题翻译成SQL语句的过程卡住了。举个例子“列出没有选过任何课程的学生”这句话看着简单但“没选过”三个字在SQL里要做集合差运算要么用NOT EXISTS要么用NOT IN还要考虑空值问题。一个自然语言很容易表达的需求落到SQL里会有多种解法每种解法的边界条件还不一样这就是思维切换带来的落差。1.2 这次实验的具体目标与三张基础表参考不少高校的编排这类实验二一般围绕学生、课程、选课三张表展开要求掌握四类查询单表条件查询、聚合分组统计、多表连接查询、子查询与集合查询。先看表结构我在实验里用的结构是这样的基本是教材经典设计表名字段说明studentsno, sname, sex, age, dept学生表sno为主键coursecno, cname, credit课程表cno为主键scsno, cno, grade选课表(sno,cno)联合主键grade允许NULL我给这几张表塞了少量样例数据方便验证查询结果。学生表大概6行课程表4行选课表10行其中有一两个学生没选课有一两门课没有学生选还有一条选课记录的成绩是NULL。这样设计数据是故意的因为实验二最容易错的恰恰是空值和零行——数据全填得满满当当很多坑根本暴露不出来。后面讲到的几个踩坑案例都跟这组数据相关。2. 用三张表跑通基础SELECT我的第一版查询与注释2.1 单表查询的完整示例我从最简单的单表查询讲起。实验二的第一类题目通常让你练SELECT、WHERE、ORDER BY、DISTINCT。下面几个是我当时反复写过的典型问题。第一个查询所有学生的学号和姓名。这个很简单但我强烈建议不要写SELECT *显式列出字段能让读代码的人第一时间知道你关心哪些数据。SELECT sno, sname FROM student;第二个查询年龄在20到22岁之间的学生。这里要说清楚BETWEEN是闭区间包含20和22本身。有同学看到结果里包含了边界年龄觉得奇怪其实这是SQL标准的定义。SELECT sno, sname, age FROM student WHERE age BETWEEN 20 AND 22;第三个查询姓“张”的学生。如果数据库编码和字段设置正常用LIKE 张%就能搞定。这里要提醒的是LIKE和等值判断在性能上不一样等值判断能走索引前导通配符的LIKE基本不能走索引数据量小的时候无所谓数量一大就明显。SELECT sno, sname FROM student WHERE sname LIKE 张%;第四个查询有哪些学生选了课不重复列出学号。DISTINCT是很多人第一次接触的语法。需要提醒的是DISTINCT是对整个结果集去重不是只对某一列去重所以SELECT DISTINCT sno, cno和SELECT DISTINCT sno意义完全不同。SELECT DISTINCT sno FROM sc;2.2 聚合分组怎么用不犯错单表查询只是热身聚合分组才是实验二的第一道分水岭。COUNT、SUM、AVG、MAX、MIN配合GROUP BY使用容易踩的坑集中在两个地方WHERE与HAVING的区别、COUNT(*)与COUNT(列名)的区别。先看一道典型题统计每个系的学生人数。SELECT dept, COUNT(*) AS stu_cnt FROM student GROUP BY dept;GROUP BY的核心规则是SELECT后面出现的非聚合列必须出现在GROUP BY里。我用一个生活化的比喻解释GROUP BY就像是把一堆学生按系分到不同的房间COUNT(*)就是在每个房间点人数。你既然按系分房就不可能在一个房间里逐一问每个学生的姓名因为这个房间里有好几十个人。另一道题找出平均成绩大于80分的课程。SELECT cno, AVG(grade) AS avg_grade FROM sc GROUP BY cno HAVING AVG(grade) 80;WHERE和HAVING的分工很容易混淆其实只要记住一句话WHERE发生在分组之前HAVING发生在分组之后。WHERE不能写聚合函数因为分组之前还没有组的概念HAVING专门过滤分组后的结果。有同学在WHERE里写AVG(grade)80直接报错SQL的语法解释器不会允许聚合函数出现在WHERE里这不是数据库的bug而是逻辑顺序本身就不成立。再说COUNT。COUNT(*)统计的是行数COUNT(grade)统计的是grade非NULL的行数。如果某个学生缺考grade是NULLCOUNT(grade)不会把它算进去。实验题如果只问“选了课的人数”用COUNT(DISTINCT sno)更准确因为同一名学生选多门课会产生多条记录直接COUNT(sno)会把人数数重。这里藏着一个经典陷阱统计选课人数时有同学直接COUNT(sno)一个学生选了三门课就被数了三次结果自然不对。3. 多表连接最容易翻车的地方三个真实报错复盘3.1 笛卡尔积膨胀的排查过程多表连接是实验二的重头戏也是出错重灾区。我第一个有印象的现象是查询结果的行数莫名其妙爆炸。题目要求“列出所有选了课的学生姓名、课程名和成绩”我第一版写的SQL长这样SELECT student.sname, course.cname, sc.grade FROM student, course, sc WHERE student.sno sc.sno;这条SQL运行不报错但结果里每个学生和每门课程都组合了一遍课程名全是重复的。原因很直接我漏掉了sc.cno course.cno这个条件。三张表连接至少需要两个连接条件缺失一个就会有一对表产生笛卡尔积。当时的排查过程是先单独执行SELECT COUNT(*) FROM sc得到10条再跑错误SQL发现结果有40条明显是10乘4的产物。于是立刻检查连接条件一眼就看到了漏掉的部分。补上之后结果回到正常的10条。SELECT student.sname, course.cname, sc.grade FROM student, course, sc WHERE student.sno sc.sno AND sc.cno course.cno;这个经验可以总结成一句口诀n张表做连接至少要写n-1个连接条件写完数一下条件个数能少一半低级错误。3.2 外连接方向搞反另一道题是“列出所有课程以及它们的选课人数没有学生选的课程也要保留”。一开始我用内连接SELECT course.cname, COUNT(sc.sno) FROM course JOIN sc ON course.cno sc.cno GROUP BY course.cname;结果只有3行而课程表里有4门课。空课的选课人数是0但在INNER JOIN里因为选课表sc没有对应记录整行直接消失了。正确的做法是让course表当左表用LEFT JOINSELECT course.cname, COUNT(sc.sno) FROM course LEFT JOIN sc ON course.cno sc.cno GROUP BY course.cname;这里还有个细节外连接里ON和WHERE的语义差异非常隐蔽。LEFT JOIN的结果集先按ON条件匹配已经匹配的行不会因为ON条件不满足而被删除但WHERE是在连接结果生成之后过滤。举例来说LEFT JOIN后如果WHERE里包含“右表某字段 某值”的条件NULL行会被过滤掉LEFT JOIN就退化成INNER JOIN的效果。所以想让所有左表行都保留过滤条件尽量放在ON里想让最终结果只保留满足某个条件的行才放在WHERE里。3.3 自连接查“和A同学同系的其他同学”自连接是很多人第一次觉得SQL“绕”的地方。题目是“找出与A同学同系的其他学生”如果不用子查询可以用自连接SELECT a.sname AS student_name, b.sname AS classmate_name FROM student a JOIN student b ON a.dept b.dept AND a.sno ! b.sno WHERE a.sname A同学;自连接的核心是同一张表出现两次起两个不同的别名然后像两张普通表一样做连接。我把这个过程类比成“把一张表复印成两份再在复印件之间连线”。写的时候最容易忘的是a.sno ! b.sno忘了就去掉了“其他”两个字把自己也算进来了。另外要说一句实际查询里如果数据量大自连接最好配合索引和过滤条件否则一张几万行的表自己和自己连接计算量会非常可观。4. 子查询和集合逻辑为什么我的结果总是多一行少一行4.1 IN与EXISTS的选择一个NULL引发的诡异空结果子查询最典型的题目是“没有选任何课程的学生名单”。我第一反应是写NOT IN版本SELECT sno, sname FROM student WHERE sno NOT IN (SELECT sno FROM sc);在实验数据正常情况下这个写法运行没问题。但后来我为了复现一个经典坑专门构造了一个包含NULL的临时查询把学生学号和NULL做UNION再拿NOT IN去筛结果整个查询返回空——这就是SQL三值逻辑的典型表现。SQL里NULL与任何值比较结果都是“未知”NOT IN需要所有比较都为“真”才返回TRUE只要子查询结果里混入一行NULL整个判断就会变成“未知”行就不会被选中。如果数据是从Excel导入的某行学号缺失就会变成NULL你在线上系统里遇到莫名其妙的空结果很多时候就是这类问题。稳妥的写法是NOT EXISTSSELECT s.sno, s.sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno );NOT EXISTS走的是“逐行检查是否存在”的逻辑不会因为NULL产生整片空白。这个案例是我觉得实验二里最有价值的一个坑它说明了一个问题SQL的“正常”和“符合直觉”之间是有差距的你必须把空值当成一等公民来对待。4.2 ANY/ALL与聚合函数的等价改写再来看一组查询找出比计科系所有学生年龄都小的学生。这种题目初学者容易写出复杂逻辑但实际上用ALL就能解决SELECT sno, sname, age FROM student WHERE age ALL ( SELECT age FROM student WHERE dept 计科 );ALL的意思是“小于其中每一个”等价于小于计科系里最小的年龄。所以也可以写成SELECT sno, sname, age FROM student WHERE age ( SELECT MIN(age) FROM student WHERE dept 计科 );两种写法结果一样但用MIN的写法更容易被索引优化可读性也更好。同理 ANY等价于 MIN ALL等价于 MAX。把这些等价关系背下来很多看着复杂的题目可以瞬间简化。说到多一行少一行的问题有个常见错误是忘了加“其他学生”的排除条件。比如题目要求“找出比计科系所有学生都小的非计科系学生”如果不在WHERE里加dept ! 计科自己系的学生也会被选进去行数就多了。返回行数不对的时候先检查是不是过滤条件写少了这是最快的排查方向。5. 提交前一定要过一遍的自查清单与书写习惯5.1 功能正确之外的效率细节实验二交上去之前我通常会做一轮检查重点不是语法对不对而是逻辑合不合理、结果会不会被扣分。第一能不写SELECT *就不要写。实验数据量小看不出差别但写SELECT会让结果集里出现一堆无关字段不管是给别人看还是自己排查都很干扰。第二注意字符串比较和排序规则。不同数据库对中文排序不一样有的按拼音有的按汉字编码实验里如果要求按姓名排序最好先确认数据库的排序规则不然结果可能和预期不一致。第三留意常用函数在不同数据库里的差异。比如取当前时间MySQL用NOW()有些数据库用GETDATE()实验环境如果是另一种数据库函数要跟着改。第四养成给列起别名的习惯。聚合结果列叫COUNT()在结果集里显示不友好用AS取一个可读的名字报告截图也好看。提示能用普通查询解决的问题不要急着套窗口函数或者复杂子查询。实验二阶段老师更看重你能不能把基础概念讲清楚而不是炫技。5.2 个人版实验报告怎么写最省事既然是“个人版”实验我自己的整理方式是这样的每一道题按四个小节组织——题目原文、我的SQL、运行结果、关键解释。题目原文是老师的原始需求SQL是最终版本运行结果贴关键几行截图关键解释用一两句话说明“这里为什么用GROUP BY而不写WHERE”之类的理由。这四个小节写下来不仅复习方便老师看的时候也能快速理解你的思路。我还习惯把踩过的错题单独列一个表格放在报告最后格式就三列报错现象、可能原因、解决办法。比如笛卡尔积、NOT IN空值、外连接方向搞反这些坑全部记下来。这份错题表看起来没什么技术含量却是最实用的东西期末复习的时候翻一遍比重新翻教材高效得多。最后再分享一个小技巧我每次写完一段SQL都会先把表里的样例数据手算一遍预期结果再跑代码。数据库不会骗人但它只会执行你写出来的逻辑而不是你脑子里想的逻辑。手算一遍很多答案对不对你自己心里就有数了。这个习惯帮我少跑了很多冤枉路实验二里那些看起来莫名其妙的报错多数时候都是逻辑边界问题早发现早解决。