★ FEATURED ARTICLE 《数据库系统概论》学到第三章基本就进入了SQL的主场。前两章还在讲关系模型、关系代数这些偏理论的东西第三章开始动真格地写SQL语句。课后题4和5看起来只是几道写查询、写更新的小题实际上把这一章最核心的内容全串起来了连接查询、分组统计、嵌套子查询、除法运算、数据更新、视图建立和使用。如果你正在赶这门课的作业或者准备考研复试这篇文章可以直接当作一份参考答案避坑手册。我这里用的是高校教学中流传最广的版本题4是四小问查询题5是更新加视图操作都跑在经典的学生-课程数据库上。我先把完整建表语句、样例数据和每一条SQL的执行逻辑都写出来再补充一些课本不会细讲的细节。同一道题我会给多种写法因为考试和工作里同一个查询本来就不止一条路。1. 先别急着写SQL把知识地图和数据库环境搭好1.1 两道题到底考了什么先看考点。题4是四小问查询难度阶梯式上升第一问考分组统计第二问考多表连接或嵌套子查询第三问考自连接或标量子查询第四问考除法运算。第四问是教材里公认的难点——查询选修了全部课程的学生SQL里没有直接的除法命令要用双重NOT EXISTS实现。题5考的是数据操作和视图包括UPDATE条件更新、INSERT插入、DELETE删除、CREATE VIEW建立视图再基于视图做分组查询。把知识点摊开看就是这样题目考点核心SQL组件难度题4-1分组统计GROUP BY / COUNT / ORDER BY低题4-2多表连接JOIN或IN子查询中题4-3自连接表别名 / 关联子查询中题4-4除法运算双重NOT EXISTS高题5-1条件更新UPDATE 子查询中题5-2插入记录INSERT低题5-3删除记录DELETE低题5-4视图查询CREATE VIEW / GROUP BY / HAVING中为什么要先梳理考点因为写SQL和写作文一样得先审题再动笔。大部分同学作业出错不是语法不会而是没看懂题目在问什么。选修了全部课程这句话如果不转化成不存在任何一门课他没有选SQL根本无从下手。这个转化过程就是审题。1.2 学生-课程库建表和样例数据后面所有SQL都跑在三张表上Student、Course、SC。建表语句如下。CREATE TABLE Student ( Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) ); CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL, Cpno CHAR(4), Ccredit SMALLINT ); CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade SMALLINT, PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );注意SC表的主键是(Sno, Cno)联合主键这保证了一个学生一门课最多只有一条成绩记录。这个细节后面讲COUNT(DISTINCT Cno)时会用到。样例数据我用教材里最经典的那批学生分布在CS、MA、IS三个系INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES (201215121, 李勇, 男, 20, CS), (201215122, 刘晨, 女, 19, CS), (201215125, 张立, 男, 19, IS), (201215126, 李一, 女, 18, MA); INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES (1, 数据库, 5, 4), (2, 数学, NULL, 2), (3, 信息系统, 1, 4), (4, 操作系统, 6, 3), (5, 数据结构, 7, 4), (6, 数据处理, NULL, 2), (7, PASCAL语言, 6, 4); INSERT INTO SC (Sno, Cno, Grade) VALUES (201215121, 1, 92), (201215121, 2, 85), (201215121, 3, 88), (201215121, 4, 90), (201215121, 5, 86), (201215121, 6, 79), (201215121, 7, 91), (201215122, 1, 76), (201215122, 2, 90), (201215122, 3, 80), (201215125, 2, 95), (201215125, 3, 66);这批数据特意让李勇选了全部7门课这样第四问选修了全部课程的学生能查出真实结果不然空结果集很难验证SQL对不对。2. 课后题4拆解四小问把SQL查询讲了八成2.1 第一问每门课的选修人数题目查询每门课程的课程名和选修人数结果按人数降序排列。SELECT Cname, COUNT(*) AS 选修人数 FROM Course, SC WHERE Course.Cno SC.Cno GROUP BY Course.Cno, Cname ORDER BY 选修人数 DESC;这是标准的先连接、再分组、最后排序三步走。很多同学会问既然表里已经有Cname了GROUP BY为什么不能只写Cno从SQL标准来说SELECT子句里出现的非聚合列必须出现在GROUP BY子句中。分组之后同一组内Cname的值虽然确定但数据库引擎需要你明确告诉它按什么分组。MySQL 8.0开始默认开启ONLY_FULL_GROUP_BY只按Cno分组的老写法在严格模式下直接报错。把Cno和Cname都写进GROUP BY语义更严谨以后课程改名也不会影响分组逻辑。这里还藏着一个性能理解点执行顺序是先WHERE连接两表过滤出匹配行再按课程分组最后统计每组行数。连接是第一步所以SC表里每一条选课记录都会成为一行COUNT(*)自然就是选课人数。如果想加分可以补一个没有学生选的课程也要显示选修人数为0的版本。这必须用LEFT JOINSELECT Course.Cname, COUNT(SC.Cno) AS 选修人数 FROM Course LEFT JOIN SC ON Course.Cno SC.Cno GROUP BY Course.Cno, Course.Cname ORDER BY 选修人数 DESC;注意这里用了COUNT(SC.Cno)而不是COUNT()。LEFT JOIN会让没被选过的课程产生一行SC字段全为NULL的记录COUNT()会把这一行也数进去结果变成1而COUNT(SC.Cno)只统计非NULL值就能正确显示0。这是一个非常经典的坑第4部分还要单独讲。2.2 第二问三表连接还是子查询题目查询选修了数据库课程且成绩在90分以上的学生姓名。最直观的写法是三表连接SELECT Sname FROM Student, SC, Course WHERE Student.Sno SC.Sno AND SC.Cno Course.Cno AND Cname 数据库 AND Grade 90;题目要求学生姓名所以从Student出发成绩在SC表课程名在Course表。三张表通过Sno和Cno两个外键串起来过滤条件写在最后。等价的嵌套子查询写法SELECT Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname 数据库 ) AND Grade 90 );连接查询是一次把需要的列拉出来再过滤子查询是层层缩小范围。实际工作中如果只查Student表的字段连接写法通常更高效优化器容易做成索引连接而IN子查询逻辑接近自然语言读起来不容易错。我的习惯是两表以上查询先用连接写法把结果集框出来如果表太多、条件太绕再拆子查询。考试时两种都要会写因为有的题目明确要求用嵌套查询实现。还有个细节如果数据库里有两门课都叫数据库按Cname过滤会重复计算。实际系统里更推荐按Cno1关联这也解释了为什么真实业务中习惯用ID关联而不是名称关联。2.3 第三问自连接差点被绕晕的一题题目查询与刘晨在同一个系学习的学生的学号和姓名。注意要排除刘晨本人。写法一自连接。把Student表复制成两份S1负责定位刘晨所在的系S2负责找出这个系的所有学生SELECT S2.Sno, S2.Sname FROM Student S1, Student S2 WHERE S1.Sname 刘晨 AND S1.Sdept S2.Sdept AND S2.Sname 刘晨;同一个物理表在逻辑上当成两个表用这就是自连接。第一次接触时容易懵想通表和自己连接后很多在同一个组里找同类的问题都能用这个套路解。写法二标量子查询SELECT Sno, Sname FROM Student WHERE Sdept ( SELECT Sdept FROM Student WHERE Sname 刘晨 ) AND Sname 刘晨;不少同学在这里丢分因为漏掉最后的AND Sname 刘晨。题目问与刘晨在同一系的学生自然语言里不含刘晨本人必须显式排除。如果系里有两个叫刘晨的人等号子查询会报子查询返回了多行要改成INSELECT Sno, Sname FROM Student WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname 刘晨 ) AND Sname 刘晨;教学环境假设姓名唯一但真实项目中姓名从来不是唯一键。这类题目搬到生产环境正确做法是先取学生ID再关联。这也再次印证了上一节的观点关联条件要用主键而不是业务字段。2.4 第四问双重NOT EXISTS经典中的经典题目查询选修了全部课程的学生姓名。这是教材里公认的难点也是考研复试高频题。第一次见到的人大概率会懵SQL里没有全部这个关键词怎么表达核心思路是逻辑转化学生X选修了全部课程等价于不存在一门课程Y使得学生X没有选修Y。写成SQL就是双层NOT EXISTSSELECT Sname FROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SC WHERE SC.Sno Student.Sno AND SC.Cno Course.Cno ) );理解方式最内层检查这个学生是否选了这门课第二层遍历所有课程找出这个学生没选的课程如果第二层查出来的结果为空说明一门没漏最外层NOT EXISTS成立学生被选中。整个查询是关联子查询每层内层的Student.Sno和外层Student一一对应每个学生都会被过一遍。如果觉得双重否定绕还有一招基于集合计数的等价写法SELECT Student.Sname FROM Student JOIN ( SELECT Sno, COUNT(*) AS cnt FROM SC GROUP BY Sno ) AS SC_CNT ON Student.Sno SC_CNT.Sno WHERE SC_CNT.cnt (SELECT COUNT(*) FROM Course);思路是统计每个学生选课数再和课程总数比。前提是SC表没有重复选课记录。我们表的主键正好是(Sno, Cno)所以用COUNT(*)没问题如果表结构没这层约束就得改成COUNT(DISTINCT Cno)。哪种更好教学角度NOT EXISTS是标准解法完美对应集合除法性能角度如果Course和SC有索引关联子查询通常也不错。但NOT EXISTS可读性差初学容易把NOT写错位置。我改作业时见过最多的错误就是把NOT漏掉或者把两层写反。练习时建议两种都写一遍互相验证结果。3. 课后题5拆解更新和视图SQL的另一半3.1 条件更新的正确姿势UPDATE 子查询题目将CS系学生选修的数据库课程成绩下调5%。一种稳妥写法UPDATE SC SET Grade Grade * 0.95 WHERE Sno IN ( SELECT Sno FROM Student WHERE Sdept CS ) AND Cno IN ( SELECT Cno FROM Course WHERE Cname 数据库 );WHERE里两条IN子查询逻辑清楚同时满足学生是CS系和课程是数据库。SET子句用表达式Grade Grade * 0.95原地乘0.95不用先取出来再放回去。往深了说UPDATE前务必想清楚影响范围。最稳妥的做法是先在SELECT里验证SELECT SC.* FROM SC, Student, Course WHERE SC.Sno Student.Sno AND SC.Cno Course.Cno AND Student.Sdept CS AND Course.Cname 数据库;确认结果集正是要改的记录再改写成UPDATE。别嫌多这一步生产环境里一条UPDATE误伤全表的事故九成是直接写UPDATE而没先跑SELECT造成的。再提醒一个方言差异MySQL支持UPDATE ... JOIN ... SET的多表更新语法但SQL Server和标准SQL不支持。为了教材通用建议以子查询过滤写法为准。3.2 INSERT和DELETE先搞清楚你是要删数据还是删表题目里还有两个操作向Student表插入一名新生学号201215123姓名王敏18岁MA系删除SC表中成绩低于60分的记录。INSERT标准写法INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept) VALUES (201215123, 王敏, 女, 18, MA);如果省略列名写成INSERT INTO Student VALUES(...)要求值的顺序、个数和表定义完全一致一旦改表结构就崩。列出列名是好习惯可读性和健壮性都更高。DELETE写法DELETE FROM SC WHERE Grade 60;这里值得展开的是DELETE和DROP的区别。DELETE是删行表结构、索引、约束都还在DROP是删整张表包括结构连后悔的机会都没有。还有一个TRUNCATE清空表但保留结构速度快但无法加WHERE条件。一句话总结DROP是拆房子TRUNCATE是搬家清空DELETE是挑几件不要的家具扔掉。再提醒如果SC表的外键约束是ON DELETE CASCADE删除Student某条记录会连带删掉SC里相关选课记录。教学库里经常忽略这个行为做题时发现只删了一条学生记录选课表却少了好几行先检查外键级联设置。3.3 视图操作建立视图再查询题目建立CS系学生的选课视图包含学号、姓名、课程名、成绩再通过该视图查询平均成绩大于85分的学生学号和平均成绩。建视图CREATE VIEW CS_Student_Score AS SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course WHERE Student.Sno SC.Sno AND SC.Cno Course.Cno AND Sdept CS;基于视图查询SELECT Sno, AVG(Grade) AS avg_grade FROM CS_Student_Score GROUP BY Sno HAVING AVG(Grade) 85;这里要讲明白视图的两个本质特点。第一视图不存数据只存一条SELECT语句每次查询视图时数据库重新执行这段语句所以视图也叫虚拟表。第二视图限制数据可见范围。用户通过CS_Student_Score只能看到CS系数据其他系记录对他透明。权限管控场景里非常实用比如外包人员只能查白名单视图。如果用MySQLSHOW CREATE VIEW可以查看视图背后存的SELECT语句可以自己验证只存语句不存数据这个说法。再补充WITH CHECK OPTIONCREATE VIEW CS_Student_Score AS SELECT ... WITH CHECK OPTION;之后通过视图插入或修改数据时数据库会检查数据是否满足视图定义条件。往视图里插一条MA系学生的记录会被直接拒绝防止绕过视图产生看不见的数据。这是常考但容易被忽略的细节。3.4 通过视图更新数据不是想改就能改承接上面的视图假设执行UPDATE CS_Student_Score SET Sname 李勇勇 WHERE Sno 201215121;多数数据库会报错或拒绝。原因很简单这个视图是三张基表连接出来的Sname来自StudentCname和Grade来自别的表。修改Sname时数据库不知道要不要连带处理其他表标准实现通常直接拒绝多表连接视图的DML操作。但简单视图就不一样。比如CREATE VIEW CS_Student AS SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept CS;这是典型的行列子集视图列是Student子集行是单表WHERE过滤。对它执行UPDATE是允许的UPDATE CS_Student SET Sage Sage 1 WHERE Sno 201215121;数据库会把这条语句翻译成对Student表的更新。所以复习时要记住视图更新能力取决于视图复杂程度。分组、聚合、DISTINCT、多表连接视图一般不可更新单表行列子集视图可更新。4. 作业里最常踩的坑每条都是我批改时见过的4.1 坑一COUNT(*) 和 COUNT(列) 在LEFT JOIN里结果不一样前面埋的伏笔在这里收。很多同学写每门课选修人数时遇到没有学生选的课程会得到人数为1SELECT Cname, COUNT(*) AS 选课人数 FROM Course LEFT JOIN SC ON Course.Cno SC.Cno GROUP BY Cname;当某课程没有选课记录时LEFT JOIN产生一行SC字段全为NULL的记录COUNT()把这行也数进去。改成COUNT(SC.Cno)只统计非NULL值才能得到0。这个坑在JOIN查询里非常常见面试官尤其爱问。记住一句话COUNT()数行数COUNT(列)数非NULL值。4.2 坑二NOT IN 遇到 NULL 直接翻车题目变形查询没有选修任何课程的学生。很多同学的直觉写法SELECT Sno, Sname FROM Student WHERE Sno NOT IN (SELECT Sno FROM SC);数据干净时没问题但一旦SC表存在Sno为NULL的记录比如外键约束没建好或者手工插入脏数据查询返回空集。原因SQL三值逻辑里NULL参与比较的结果不是TRUE也不是FALSE而是UNKNOWN。NOT IN本质是对所有子查询结果做不等于比较只要有一个NULL整个结果就变UNKNOWN什么都查不出来。解决方法是换成NOT EXISTSSELECT Sno, Sname FROM Student WHERE NOT EXISTS ( SELECT 1 FROM SC WHERE SC.Sno Student.Sno );NOT EXISTS只关心是否存在完全不涉及NULL值的等值比较天然避开这个坑。这也是为什么教材讲没有B的A这类问题时直接推荐NOT EXISTS。4.3 坑三HAVING和WHERE傻傻分不清在查平均成绩大于85分的CS系学生这类题目里正确写法SELECT SC.Sno, AVG(Grade) FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept CS) GROUP BY SC.Sno HAVING AVG(Grade) 85;WHERE在分组前过滤行HAVING在分组后过滤组。不能在WHERE里写AVG(Grade)85因为执行到WHERE时还没分组。也不能把SdeptCS放进HAVING除非SELECT了Sdept并按它分组。很多同学把两个条件写反要么报错要么结果错误。再提醒HAVING里能不能用别名比如HAVING avg_grade 85不同数据库行为不一致标准SQL不允许MySQL部分版本允许。为了兼容性最好在HAVING里写原始表达式AVG(Grade)而不是别名。4.4 坑四UPDATE/DELETE前不检查事故就是这么来的这个谈不上技术难度纯粹是习惯问题。反复强调一个动作写任何UPDATE或DELETE先把同样的WHERE放进SELECT里跑一遍。比如要删SC里成绩低于60的记录先跑SELECT * FROM SC WHERE Grade 60;确认条数符合预期再执行DELETE。成功后看一眼受影响行数和刚才SELECT结果数是否一致。生产环境里这个习惯能救命。另一个习惯是更新或删除前开事务BEGIN; DELETE FROM SC WHERE Grade 60; -- 确认无误后 COMMIT; -- 发现问题 ROLLBACK;教学里不强调但真实项目中是标配。一旦误操作ROLLBACK还能把数据救回来。5. 把课后题变成真正能打的技能三个延伸方向5.1 用窗口函数重新做第四问第四问如果用现代SQL的窗口函数也能做思路是算每个学生的选课数再和总课程数比较SELECT Student.Sname FROM Student LEFT JOIN ( SELECT Sno, COUNT(DISTINCT Cno) AS course_cnt FROM SC GROUP BY Sno ) AS t ON Student.Sno t.Sno WHERE COALESCE(t.course_cnt, 0) (SELECT COUNT(*) FROM Course);这个写法少点逻辑绕弯但同样有NULL问题没选课的学生LEFT JOIN后course_cnt为NULL要用COALESCE兜底为0。类似地RANK()、DENSE_RANK()这些窗口函数可以用来处理每个系成绩排名前三的学生是面试中的加分项。5.2 换个数据库验证一遍你会看到方言的威力同一套逻辑在不同数据库里写法差异很大。MySQL第二问可以直接JOIN ... ONSQL Server取前N条用TOPOracle用ROWNUM或FETCH FIRST。字符串拼接更是各搞各的MySQL用CONCATSQL Server用Oracle用||。课内作业用标准SQL没问题出去工作后第一件事就是搞清楚公司用的数据库方言。推荐学习路径本地装一个MySQL或SQLite把课后题从头到尾跑一遍。SQLite尤其轻量一条命令启动适合快速验证。跑通了再换PostgreSQL试试语法越严格SQL基础就越扎实。5.3 从写对到写快看执行计划题目写对了只是第一步数据量大时性能问题才显现。用EXPLAINMySQL可以看查询走全表扫描还是索引查找。比如第四问的NOT EXISTS版本如果SC表有(Sno, Cno)联合索引内层查询能快速定位没有索引就会对每个学生扫描整个SC表复杂度直接翻倍。所以建表时为什么强调主键和外键除了完整性约束它们天然就是索引连接查询靠的就是这些索引。课后题做完了顺手用EXPLAIN看看自己写的每条查询这一步会让对SQL的理解上一个台阶。我自己学第三章时课后题4和5做了三遍。第一遍照着答案抄第二遍合上书自己写第三遍故意不看标准答案用不同写法实现同样结果。第三遍收获最大同一个查询至少有连接、子查询、EXISTS、窗口函数四条路各有各的适用场景。后来处理真实业务数据这些基础SQL几乎每天都在用。把这两道题吃透SQL地基就算打牢了。
阅读完成 · 觉得有帮助？
