数据库系统概论第三章课后题4和5精解：SQL查询与视图操作

《数据库系统概论》学到第三章，基本就进入了SQL的主场。前两章还在讲关系模型、关系代数这些偏理论的东西，第三章开始动真格地写SQL语句。课后题4和5，看起来只是几道"写查询、写更新"的小题，实际上把这一章最核心的内容全串起来了：连接查询、分组统计、嵌套子查询、除法运算、数据更新、视图建立和使用。如果你正在赶这门课的作业，或者准备考研复试，这篇文章可以直接当作一份"参考答案+避坑手册"。

我这里用的是高校教学中流传最广的版本，题4是四小问查询，题5是更新加视图操作，都跑在经典的"学生-课程"数据库上。我先把完整建表语句、样例数据和每一条SQL的执行逻辑都写出来，再补充一些课本不会细讲的细节。同一道题我会给多种写法，因为考试和工作里，同一个查询本来就不止一条路。

1. 先别急着写SQL：把知识地图和数据库环境搭好

1.1 两道题到底考了什么

先看考点。题4是四小问查询，难度阶梯式上升：第一问考分组统计，第二问考多表连接或嵌套子查询，第三问考自连接或标量子查询，第四问考除法运算。第四问是教材里公认的难点——"查询选修了全部课程的学生"，SQL里没有直接的除法命令，要用双重NOT EXISTS实现。

题5考的是数据操作和视图，包括UPDATE条件更新、INSERT插入、DELETE删除、CREATE VIEW建立视图，再基于视图做分组查询。

把知识点摊开看就是这样：

为什么要先梳理考点？因为写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 JOIN：

SELECT 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过滤会重复计算。实际系统里更推荐按Cno='1'关联，这也解释了为什么真实业务中习惯用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 <> '刘晨'。题目问"与刘晨在同一系的学生"，自然语言里不含刘晨本人，必须显式排除。

如果系里有两个叫"刘晨"的人，等号子查询会报"子查询返回了多行"，要改成IN：

SELECT 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 EXISTS：

SELECT 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系数据，其他系记录对他透明。权限管控场景里非常实用，比如外包人员只能查白名单视图。

如果用MySQL，SHOW CREATE VIEW可以查看视图背后存的SELECT语句，可以自己验证"只存语句不存数据"这个说法。

再补充WITH CHECK OPTION：

CREATE VIEW CS_Student_Score AS SELECT ... WITH CHECK OPTION;

之后通过视图插入或修改数据时，数据库会检查数据是否满足视图定义条件。往视图里插一条MA系学生的记录会被直接拒绝，防止绕过视图产生"看不见的数据"。这是常考但容易被忽略的细节。

3.4 通过视图更新数据：不是想改就能改

承接上面的视图，假设执行：

UPDATE CS_Student_Score SET Sname = '李勇勇' WHERE Sno = '201215121';

多数数据库会报错或拒绝。原因很简单：这个视图是三张基表连接出来的，Sname来自Student，Cname和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里结果不一样

前面埋的伏笔在这里收。很多同学写"每门课选修人数"时，遇到没有学生选的课程，会得到人数为1：

SELECT 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 EXISTS：

SELECT 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时还没分组。也不能把Sdept='CS'放进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 ... ON，SQL Server取前N条用TOP，Oracle用ROWNUM或FETCH FIRST。字符串拼接更是各搞各的：MySQL用CONCAT，SQL Server用+，Oracle用||。课内作业用标准SQL没问题，出去工作后第一件事就是搞清楚公司用的数据库方言。

推荐学习路径：本地装一个MySQL或SQLite，把课后题从头到尾跑一遍。SQLite尤其轻量，一条命令启动，适合快速验证。跑通了再换PostgreSQL试试，语法越严格，SQL基础就越扎实。

5.3 从"写对"到"写快"：看执行计划

题目写对了只是第一步，数据量大时性能问题才显现。用EXPLAIN（MySQL）可以看查询走全表扫描还是索引查找。比如第四问的NOT EXISTS版本，如果SC表有(Sno, Cno)联合索引，内层查询能快速定位；没有索引，就会对每个学生扫描整个SC表，复杂度直接翻倍。

所以建表时为什么强调主键和外键？除了完整性约束，它们天然就是索引，连接查询靠的就是这些索引。课后题做完了，顺手用EXPLAIN看看自己写的每条查询，这一步会让对SQL的理解上一个台阶。

我自己学第三章时，课后题4和5做了三遍。第一遍照着答案抄，第二遍合上书自己写，第三遍故意不看标准答案，用不同写法实现同样结果。第三遍收获最大，同一个查询至少有连接、子查询、EXISTS、窗口函数四条路，各有各的适用场景。后来处理真实业务数据，这些基础SQL几乎每天都在用。把这两道题吃透，SQL地基就算打牢了。