简介南华大学《数据库原理》课程实验报告面向大数据技术等专业学生及数据库入门学习者围绕SQL Server环境下的数据库设计与SQL应用展开可用于课程实验复盘、期末复习和上机操作对照。压缩包共1个doc文件约5.38MB以图文实验报告形式组织配合截图与SQL代码完整记录实验题目、要求、执行结果和分析过程。报告按模块推进实验1认识DBMS涵盖图形界面与SQL语句创建“学生选课”数据库、学生/课程/选修三表及主外码和默认值实验2简单SQL查询包含单表筛选、排序、分组及多表连接实验3高级SQL查询、实验4数据更新与实验总结便于理解实体完整性与参照完整性并掌握插入、更新、删除等基本操作。已有377人学习适合希望对照完整实验过程、补做上机练习或整理课程报告的学习者参考。1. 从一份实验报告里拆出能跑的 T-SQL它解决的是“写不出”而不是“看不懂”数据库原理实验报告这类资料网上一搜一大把 PDF但真能照着跑通的不到一半——多数只剩截图和题目SQL 语句要么被截断要么缺建表脚本拿到手第一件事反而是猜表结构。我手上这份是某高校大数据技术专业 2022 年春季学期的完整版四个实验从 SQL Server Management Studio 建库建表一路做到单表查询、多表连接、嵌套查询和集合运算题目、要求、代码四件套齐全数据库名、表名、字段名前后一致不用自己补设计。它解决的是一件事把手写 T-SQL 从“看得懂”推到“写得出、跑得通、改得动”。适合三类人——正在赶数据库原理实验的学生准备后端面试想捡回 SQL 基本功的求职者以及太久没碰 SQL Server、连GROUP BY和HAVING的先后都开始犹豫的开发者。下面按建表、单表、多表、子查询、排错、验证六段拆脚本都能直接粘进 SSMS 跑。2. 建库建表与三张表的关系主码、外码、默认值到底怎么落2.1 先想清楚实体和联系再动手写 CREATE实验 1 的题目本身给得很清楚学生、课程、选修三张表。但很多人拿到就直接开写写到一半发现选修表不知道主码放什么。这里的关键是先把联系类型定下来——一个学生能选多门课一门课能被多个学生选学生和课程之间是多对多多对多必须落一张中间表也就是选修表。选修表的主码不能是单一字段得是学号课程号的联合主码因为同一个学生选同一门课只应该有一条记录用联合主码天然把这条规则约束住。有人喜欢给选修表加一个自增 ID 当主码把学号课程号做成唯一索引。这不算错但在教学和考试语境下联合主码更贴近教材对“主码”的定义也少一个跟业务无关的字段。我一般会跟学生强调主码选错后面所有连接查询的写法都会跟着别扭。学生表的主码是学号课程表的主码是课程号选修表引用这两个外码关系自然就出来了。属性类型也要在这一步定。学号固定长度用CHAR(8)姓名长度不定用VARCHAR(20)性别只有两个字CHAR(2)就够出生日期用DATE别用DATETIME否则查询 1999—2000 年出生的学生时会多出一堆时间部分的麻烦。成绩用DECIMAL(5,1)一位小数够表示 0 到 100 分。这些选择单看都是小事凑在一起决定了后面查询能不能少写一堆CAST。提示先定主码和外码再定字段类型最后才考虑默认值和可空性。顺序反过来改表结构时会被外码约束卡住。2.2 一段能直接跑的建库建表脚本图形界面点得再熟最后还是要落到脚本。下面这段按“建库 → 建表 → 加外码 → 灌数据”的顺序写可以直接整段粘进 SSMS 执行。注意GO是 SSMS 的批处理分隔符不是 T-SQL 语句它让CREATE DATABASE和后面的USE分批提交否则同一批里引用刚建的库会报错。-- 若已存在同名库则先删除方便反复重跑 IF DB_ID(StuCou) IS NOT NULL DROP DATABASE StuCou; GO CREATE DATABASE StuCou; GO USE StuCou; GO -- 学生表学号为主码性别给默认值备注允许为空 CREATE TABLE Stu ( Sno CHAR(8) NOT NULL, Sname VARCHAR(20) NOT NULL, Ssex CHAR(2) NOT NULL CONSTRAINT DF_Stu_Ssex DEFAULT 男, Sbirth DATE NULL, Sdept VARCHAR(30) NULL, Smemo VARCHAR(100) NULL, CONSTRAINT PK_Stu PRIMARY KEY (Sno) ); -- 课程表Cpno 是先行课自引用外码必须允许为空 CREATE TABLE Cou ( Cno CHAR(4) NOT NULL, Cname VARCHAR(40) NOT NULL, Cpno CHAR(4) NULL, Ccredit SMALLINT NOT NULL CONSTRAINT DF_Cou_Ccredit DEFAULT 4, CONSTRAINT PK_Cou PRIMARY KEY (Cno), CONSTRAINT FK_Cou_Cpno FOREIGN KEY (Cpno) REFERENCES Cou(Cno) ); -- 选修表联合主码两个外码分别指向学生和课程 CREATE TABLE SC ( Sno CHAR(8) NOT NULL, Cno CHAR(4) NOT NULL, Grade DECIMAL(5,1) NULL, CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno), CONSTRAINT FK_SC_Sno FOREIGN KEY (Sno) REFERENCES Stu(Sno), CONSTRAINT FK_SC_Cno FOREIGN KEY (Cno) REFERENCES Cou(Cno) ); GO这段脚本里有几处值得单独说。CONSTRAINT DF_Stu_Ssex DEFAULT 男是给默认值显式命名不命名 SQL Server 会生成DF__Stu__Ssex__1A14E395这类随机名后面想删默认值就得先去系统视图里查名字实验报告里写出来也不好看。Cpno必须允许为空因为第一门课没有先行课写成NOT NULL之后那条记录永远插不进去。FK_Cou_Cpno是自引用外码引用的是同一张表的Cno这在 SQL Server 里允许但插入顺序有讲究下一节细说。SC表的Grade允许为空是为了表示“选了但还没出成绩”。这个可空性后面会直接影响COUNT(*)和COUNT(Grade)的差别是实验 2 里最容易算错的地方之一。2.3 三个容易写错的约束细节第一个是默认值到底什么时候生效。DEFAULT 男只在INSERT语句完全省略这一列时才起作用如果写成INSERT INTO Stu (Sno, Sname, Ssex) VALUES (0602005,陈明, NULL)显式传了NULL默认值不会兜底插入的就是空值。想让它兜底要么省略列要么在列上加NOT NULL把空值挡回去。这个行为和很多人直觉里的“默认值等于自动填充”不一样。第二个是自引用外码的插入顺序。课程表里 C02 的先行课是 C01C03 的先行课是 C02如果按 C01、C02、C03 的顺序插没有问题一旦顺序打乱先插 C02会因为 C01 还不存在而报外码冲突。稳妥的写法是先把所有课程的Cpno插成NULL再统一UPDATE补先行课关系。-- 先插入不带先行课的课程记录 INSERT INTO Cou (Cno, Cname, Cpno, Ccredit) VALUES (C01,数据库原理, NULL, 4), (C02,数据结构, NULL, 4), (C03,操作系统, NULL, 4), (C04,离散数学, NULL, 3); -- 再补先行课关系此时被引用的课程都已存在 UPDATE Cou SET Cpno C01 WHERE Cno C02; UPDATE Cou SET Cpno C02 WHERE Cno C03;第三个是可空性和查询结果的隐性耦合。Sdept允许为空WHERE Sdept 计算机系会把这些空值行过滤掉看起来正常但如果写成WHERE Sdept 计算机系空值行同样不会出现因为NULL参与比较得到的是UNKNOWN而不是TRUE。实验 3 里“查询其他系中比计算机系学生年龄都小的学生”就踩在这个点上后面第 5 章展开。2.4 图形界面和脚本两条路结果差在哪实验 1 要求两种方式各做一遍先用 SSMS 图形界面建库建表建关系再用 SQL 语句建一套。很多人做完觉得两边一样其实差异藏在细节里。图形界面建表时SSMS 设计器默认把表建成“保存时重建”如果表里已经有数据改一个字段类型可能触发删表重建数据直接没了而且它不会明显提醒你。脚本改表虽然也有限制但至少你能看见自己写的是ALTER还是DROP。再就是约束命名。图形界面加的默认值、检查约束名字都是系统生成的脚本里引用不到。实验报告如果要求写“删除某某约束”图形界面那条路基本交不出可复现的语句。所以我的习惯是图形界面只用来快速看一眼表结构最终交付一律用脚本命名规则统一成PK_表名、FK_子表_列、DF_表名_列谁看都知道指什么。数据灌入也是一样。下面这段是学生表、课程表、选修表的最小数据集学号用 0602 开头课程用 C01 到 C04成绩里故意留一条空值方便后面验证聚合函数的差异。INSERT INTO Stu (Sno, Sname, Ssex, Sbirth, Sdept, Smemo) VALUES (0602001,李勇,男,1999-03-12,计算机系, NULL), (0602002,刘晨,女,2000-07-25,计算机系, NULL), (0602003,王敏,女,1999-11-02,计算机系, NULL), (0602004,张立,男,1998-05-18,数学系, NULL), (0602005,陈明,男,2000-01-30,数学系, NULL); INSERT INTO SC (Sno, Cno, Grade) VALUES (0602001,C01, 88.0), (0602001,C02, 76.5), (0602001,C03, 91.0), (0602002,C01, 59.0), (0602002,C02, 82.0), (0602003,C01, 95.0), (0602003,C04, NULL);数据量不大但对验证查询结果足够了。特别是0602003选 C04 没成绩这一条后面统计平均分和门数时会立刻暴露写法的差别。3. 单表查询那十道题WHERE、聚合与排序的真实写法3.1 条件筛选比较、逻辑与模糊匹配实验 2 的第一组题全部落在单表上看似简单但十道题基本把WHERE的常用形态过了一遍。最基础的一道是查计算机系全体学生写法就是把系名当等值条件。这类查询的坑不在语法在于字符串里的空格——计算机系 和计算机系在比较下不相等从 Excel 粘数据进来时尾部空格很常见查不出结果先怀疑这个。-- 查询计算机系全体学生的完整信息 SELECT Sno, Sname, Ssex, Sbirth, Sdept, Smemo FROM Stu WHERE Sdept 计算机系;查姓“李”的学生用LIKE 李%百分号在后表示前缀匹配。这里要注意排序规则的影响默认排序规则下LIKE 李%能命中“李勇”但如果库的排序规则是二进制或某些区分大小写的配置中文匹配行为会跟着变。实验环境一般用默认的Chinese_PRC_CI_AS问题不大换到别的库上跑不通先查这一项。查询没有先行课的课程考的是IS NULL而不是 NULL。Cpno NULL永远返回空结果因为NULL表示未知未知和任何值比较都是未知。这条是 SQL 入门最经典的翻车点实验报告里专门列出来是有道理的。-- 先行课为空说明这门课没有先修要求 SELECT Cname FROM Cou WHERE Cpno IS NULL;查有不及格成绩的学生学号要加DISTINCT。一个学生可能挂了两门不去重会拿到重复学号和题目要的“学生的学号”对不上。这一步很多人漏掉因为样例数据里恰好只有一个学生挂科跑出来看不出问题换成真实数据就露馅。-- DISTINCT 去掉同一学生多门挂科造成的重复 SELECT DISTINCT Sno FROM SC WHERE Grade 60;选 C01 或 C02 这道题IN和OR等价但IN更好读字段多的时候不用重复写列名。要注意的是如果成绩为空Grade列会带出NULL题目只要求学号和成绩空值照常显示这不算错但如果后续要对成绩做运算就得先处理。3.2 日期与年龄DATEDIFF 的两个边界“查询全体计算机系学生的姓名及其年龄”这道题最常见的写法是YEAR(GETDATE()) - YEAR(Sbirth)。这个写法在大多数情况下对但有两个边界一是生日还没到的学生会被多算一岁二是Sbirth为空时整个表达式返回NULL。实验数据里恰好没有这两种情况所以跑出来一切正常换一批数据就开始对不上。更稳的写法是用DATEDIFF加一次修正把“今年生日是否已过”判断进去-- 精确到岁的年龄先算年份差生日未到则减 1 SELECT Sname, DATEDIFF(YEAR, Sbirth, GETDATE()) - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, Sbirth, GETDATE()), Sbirth) GETDATE() THEN 1 ELSE 0 END AS Age FROM Stu WHERE Sdept 计算机系;DATEDIFF(YEAR, ...)只比较年份部分不看到底过没过生日DATEADD把出生日期加上同样的年数得到“今年的生日”再和今天比。这个套路在处理工龄、账龄、会员年限时通用值得记住。如果只是想粗略估计用YEAR相减也无可厚非但实验报告里最好注明这是近似值别让人拿去当考勤依据。1999—2000 年出生的学生那道题坑在区间边界。写成BETWEEN 1999-01-01 AND 2000-12-31在DATE类型下没问题但如果字段是DATETIME且存了2000-12-31 10:30:00BETWEEN的右端就到不了这条记录会被漏掉。半开区间写法 1999-01-01 AND 2001-01-01能避开这个问题也是我在生产环境里一律采用的写法。-- 半开区间避免右端点的时间部分把当天数据漏掉 SELECT Sname FROM Stu WHERE Sdept 计算机系 AND Sbirth 1999-01-01 AND Sbirth 2001-01-01;3.3 分组聚合COUNT 数谁、HAVING 放哪“查询选修了两门以上课程的学生学号与课程数”和“查询选修课程数大于等于 2 的学生的学号、平均成绩和选课门数并按平均成绩降序排列”这两道题是实验 2 里唯一需要分组的两道也是错得最多的两道。错误集中在三个地方COUNT(*)和COUNT(列)混用、WHERE和HAVING放错位置、ORDER BY里直接用聚合函数还是别名。-- 选修两门以上严格大于 2的学生及课程数 SELECT Sno, COUNT(*) AS CouCnt FROM SC GROUP BY Sno HAVING COUNT(*) 2; -- 选修两门及以上带平均分按平均分降序 SELECT Sno, AVG(Grade) AS AvgGrade, COUNT(*) AS CouCnt FROM SC GROUP BY Sno HAVING COUNT(*) 2 ORDER BY AVG(Grade) DESC;COUNT(*)数的是行数COUNT(Grade)数的是成绩非空的行数。样例数据里0602003选了 C04 但成绩为空如果第二道题写成COUNT(Grade)这个学生的选课门数会被算成 1直接被HAVING 2过滤掉结果里少一个人。题目问的是“选课门数”选课行为已经发生成绩有没有出是另一回事所以应该用COUNT(*)。这个差别在成绩表、订单表、日志表上完全一样是很通用的一条经验。WHERE在分组前过滤行HAVING在分组后过滤组两者不能互换。想筛“计算机系的学生选课门数”系名条件放WHERE门数条件放HAVING要是把Sdept 计算机系写进HAVINGSQL Server 会直接报错说该列不在聚合或分组里。另外HAVING COUNT(*) 2和HAVING CouCnt 2在 SQL Server 里后者不行因为SELECT里的别名在这一步还不能引用ORDER BY才可以。至于“两门以上”到底含不含两门这是中文表述的歧义不是 SQL 的问题。题目第 9 题写“两门以上”第 10 题写“大于等于 2”出题人显然把前者当成严格大于。实验报告里把和各写一遍、注明理由比纠结哪个对更有价值。3.4 排序与前 N 行TOP 与字典序“查询姓李的学生中姓名按字典顺序排序的前两个”这道题考的是TOP加ORDER BY。写法很短但顺序不能反TOP必须和ORDER BY一起用才有确定结果否则拿到的是哪两行全看存储引擎心情这在 SQL Server 里属于“能跑但不可复现”的典型。-- TOP 必须配合 ORDER BY否则结果不保证 SELECT TOP 2 Sno, Sname FROM Stu WHERE Sname LIKE 李% ORDER BY Sname;字典序在这里按数据库的排序规则来中文一般按拼音所以“李”姓学生里“李勇”排在“李娜”后面取决于具体规则。如果实验要求严格按拼音可以在ORDER BY后面加COLLATE Chinese_PRC_CI_AS显式指定如果只是交实验报告默认规则足够。这一点在跨库迁移时会被放大——同一句 SQL 在两个排序规则不同的库上跑TOP 2拿到的可能是两个人。分页场景下TOP的现代替代是OFFSET ... FETCHSQL Server 2012 之后都支持。实验报告里没要求但既然要练手顺手写一遍对比不难-- 等价的分页写法跳过前 2 行取 2 行 SELECT Sno, Sname FROM Stu WHERE Sname LIKE 李% ORDER BY Sname OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;单表这十道题看着零散其实覆盖了WHERE的比较与逻辑、LIKE模糊匹配、IS NULL判断、DISTINCT去重、日期函数、GROUP BY与HAVING、ORDER BY与TOP八个点。把这八个点各自写对一遍再组合起来多表查询的难度就只剩连接条件了。4. 多表连接查询内连接、外连接与自连接的八个场景4.1 内连接JOIN 写法和 WHERE 隐式连接等价吗多表查询的第一组题是“查询选修了数据库原理的计算机系学生学号和姓名”需要三张表串起来学生表拿学号和姓名选修表拿选课记录课程表拿课程名做筛选。写法有两种显式JOIN和WHERE里堆连接条件结果等价但可读性差很多。-- 显式内连接连接条件和过滤条件分开写 SELECT S.Sno, S.Sname FROM Stu AS S JOIN SC AS SC ON S.Sno SC.Sno JOIN Cou AS C ON C.Cno SC.Cno WHERE S.Sdept 计算机系 AND C.Cname 数据库原理; -- 隐式连接连接条件混在 WHERE 里漏写一个就变成笛卡尔积 SELECT S.Sno, S.Sname FROM Stu AS S, SC AS SC, Cou AS C WHERE S.Sno SC.Sno AND C.Cno SC.Cno AND S.Sdept 计算机系 AND C.Cname 数据库原理;两种写法在 SQL Server 里都会被查询优化器改写成同样的执行计划性能上没有差别。真正的差别在维护三张表还好五张表以上的隐式连接只要漏写一个连接条件结果集行数立刻爆炸而且不会报错你只会觉得“怎么查出来这么多”。显式JOIN把连接条件锁在ON里漏写会直接语法错误或者结果明显不对容易发现得多。所以我带人写 SQL 时一律要求用显式JOINWHERE里只放过滤条件。给表起别名这件事也值得说一句。Stu AS S里的AS在 SQL Server 中写不写都行但列名有歧义时别名是必须的——三张表都有Sno或Cno不写别名就报“列名不明确”。别名起得短一点S、SC、C能让长查询保持可读起成a、b、c则过两周自己都看不懂。4.2 外连接把“没选课的学生”捞出来内连接只保留两边都匹配上的行这个特性在“查询所有学生的信息和他们的选课课程号”这类题上就不够用了——没选课的学生会被直接丢掉而题目要的是“所有学生”。这时候得用左外连接以学生表为左表保证每个学生至少出现一次没选课的课程号显示为NULL。-- 左外连接左表全保留右表没匹配上就补 NULL SELECT S.Sno, S.Sname, S.Ssex, S.Sbirth, S.Sdept, S.Smemo, SC.Cno FROM Stu AS S LEFT JOIN SC AS SC ON S.Sno SC.Sno;判断“哪些学生一门课都没选”可以直接在左外连接后面加WHERE SC.Sno IS NULL。这个套路在排查孤儿数据时非常常用找出没有订单的用户、没有登录记录的账号、没有关联明细的主表行写法都一样。反过来如果题目问“所有课程的被选情况”左表就要换成课程表因为要保留的是课程而不是学生。-- 找出没有任何选课记录的学生 SELECT S.Sno, S.Sname FROM Stu AS S LEFT JOIN SC AS SC ON S.Sno SC.Sno WHERE SC.Sno IS NULL; -- 所有课程及其被选情况课程表做左表 SELECT C.Cname, SC.Sno, SC.Cno, SC.Grade FROM Cou AS C LEFT JOIN SC AS SC ON C.Cno SC.Cno;一个常见误用是把左外连接写成LEFT JOIN之后又在WHERE里加右表的过滤条件比如WHERE SC.Grade 80。这个条件会把Grade为NULL的行全部干掉左外连接退化成内连接结果和预期差很远。如果确实要过滤右表又保留左表全部行条件得挪到ON里或者用OR SC.Grade IS NULL兜一下。这个区别在实验报告里不一定考但工作中天天遇到。还有一道“列出学生所有可能的选修情况”考的是笛卡尔积用CROSS JOIN或者省略连接条件都行。行数等于学生数乘课程数五乘四得二十行看着像错误结果其实题目要的就是这个。写的时候心里要有数别看到行数变多就以为写错了。4.3 自连接一门课的间接先行课怎么找“查询每一门课的间接先行课”是实验 2 里最绕的一道绕在它需要把课程表和自己连接一次。所谓间接先行课就是先行课的先行课C03 的直接先行课是 C02C02 的直接先行课是 C01那么 C03 的间接先行课就是 C01。用一次自连接就能拿到。-- cur 是当前课程pre 是它的直接先行课 -- pre 自己还有先行课那个才是间接先行课 SELECT cur.Cno AS CourseNo, pre.Cpno AS IndirectPre FROM Cou AS cur JOIN Cou AS pre ON cur.Cpno pre.Cno WHERE pre.Cpno IS NOT NULL;理解这段的关键是分清两个别名各自的角色。cur代表“我要查的那门课”cur.Cpno是它的直接先行课编号拿这个编号去匹配pre.Cnopre就是那门直接先行课最后取pre.Cpno也就是直接先行课自己的先行课。WHERE pre.Cpno IS NOT NULL把没有间接先行课的课程过滤掉符合题目“有间接先行课才算”的要求。自连接最容易翻车的地方是别名写混比如ON cur.Cpno pre.Cpno语义变成“两门课有同一个先行课”结果完全不对而且不报错。判断自连接写对没写对最快的办法是拿两三行已知数据手推一遍看输出是不是预期的那几条。用样例数据推C01 无先行课不出现C02 的先行课是 C01C01 无先行课不出现C03 的先行课是 C02C02 的先行课是 C01所以输出 C03 对应 C01。对上了就说明逻辑没问题。4.4 三表连接的中间结果与行数核对三表连接写多了会发现一个规律结果行数主要由最细粒度的那张表决定。学号、课程名、成绩这道题粒度是“某个学生选某门课”也就是选修表的行数连接只是把姓名和课程名补上去不会增加行数。反过来如果连接条件写错导致一对多变成多对多行数会翻倍而 SQL 不会给你任何提示。-- 学号、姓名、课程名、成绩行数应与 SC 表一致 SELECT S.Sno, S.Sname, C.Cname, SC.Grade FROM Stu AS S JOIN SC AS SC ON S.Sno SC.Sno JOIN Cou AS C ON C.Cno SC.Cno; -- 核对两条语句的行数应该相等 SELECT COUNT(*) FROM SC;我一般会在写完一条多表查询后立刻用COUNT(*)和基准表对一下行数。对不上就是连接条件有问题比一行行看结果快得多。如果查询里带了WHERE过滤就先去掉过滤条件对总数再加回过滤条件看比例是否合理。“查询选修了课程的学生姓名”这道题还有个细节同一个学生选了三门课连接之后会出现三行题目要的是姓名列表所以要么加DISTINCT要么用EXISTS半连接。两种写法的结果一样执行计划通常也一样选哪个看个人习惯。-- 写法一DISTINCT 去重 SELECT DISTINCT S.Sname FROM Stu AS S JOIN SC AS SC ON S.Sno SC.Sno; -- 写法二EXISTS 半连接不产生重复行 SELECT S.Sname FROM Stu AS S WHERE EXISTS (SELECT 1 FROM SC WHERE SC.Sno S.Sno);后者在数据量大时通常更稳因为它一旦匹配到就停不需要把选修表的所有行都连出来再去重。实验数据量小两种写法看不出差别但习惯从实验阶段就养好后面处理大表能省不少事。5. 嵌套查询与集合运算排查五类报错的现象、成因与改法5.1 相关子查询与非相关子查询执行顺序差在哪实验 3 的六道题全部落在子查询和集合运算上难度比实验 2 高一档。先分清两类子查询非相关子查询可以单独拿出来跑结果是一个固定集合外层查询拿它当条件用相关子查询引用了外层的列必须跟着外层每一行跑一次。“统计选修了数据库原理课程的学生人数”是非相关子查询的典型甚至可以直接写成连接加聚合不需要子查询。 “查询没有选修数据库原理课程的学生信息”就必须用相关子查询或者集合差因为要针对每个学生判断“他有没有选”外层每一行都要跑一次内层。-- 非相关子查询内层先算出课程号外层拿来做条件 SELECT COUNT(*) AS StuCnt FROM SC WHERE Cno (SELECT Cno FROM Cou WHERE Cname 数据库原理); -- 相关子查询内层引用外层的 S.Sno逐行判断 SELECT S.Sno, S.Sname, S.Ssex, S.Sbirth, S.Sdept, S.Smemo FROM Stu AS S WHERE NOT EXISTS ( SELECT 1 FROM SC JOIN Cou AS C ON C.Cno SC.Cno WHERE SC.Sno S.Sno AND C.Cname 数据库原理 );第一段里内层子查询只返回一个值所以可以用如果课程表里有同名课程内层返回多行就会报“子查询返回的值不止一个”。这种报错在实际项目里很常见原因是写的时候假设了唯一性数据一变就炸。稳妥做法是用IN或者加上TOP 1并明确排序规则。“查询其他系中比计算机系学生年龄都小的学生”考的是ALL或者MAX。年龄小意味着出生日期晚所以条件应该是出生日期大于计算机系所有学生的最大出生日期。写成 ALL (...)也能得到同样结果但可读性差一些而且ALL遇到空集合时返回真这个语义边界要心里有数。-- 用 MAX 表达“比计算机系所有学生都晚出生” SELECT Sno, Sname, Ssex, Sbirth, Sdept, Smemo FROM Stu WHERE Sdept 计算机系 AND Sbirth (SELECT MAX(Sbirth) FROM Stu WHERE Sdept 计算机系);这里Sdept 计算机系会把系名为空的学生过滤掉如果数据里有这种记录且业务上算“其他系”条件得改成Sdept 计算机系 OR Sdept IS NULL。实验数据里没有空系名所以看不出来真实数据里这属于必须处理的一类边界。5.2 UNION、INTERSECT、EXCEPT语义、去重与列顺序三道集合运算的题目各要求两种写法一种用集合运算符一种用IN或EXISTS子查询摆在一起对比着看最清楚。UNION是并集默认去重UNION ALL保留重复。查询“被 0602001 或 0602002 选修的课程号”用UNION正好两个学生都选的课只出现一次符合“或”的语义。-- 并集两个学生选过的课重复的只留一条 SELECT Cno FROM SC WHERE Sno 0602001 UNION SELECT Cno FROM SC WHERE Sno 0602002; -- 等价写法IN 加 DISTINCT SELECT DISTINCT Cno FROM SC WHERE Sno IN (0602001, 0602002);INTERSECT是交集“两个学生同时选修的课程号”。EXCEPT是差集“0602001 选了但 0602002 没选的课程号”。这两个运算符都隐含去重结果里不会有重复行所以外层不用再加DISTINCT。-- 交集两人都选的课 SELECT Cno FROM SC WHERE Sno 0602001 INTERSECT SELECT Cno FROM SC WHERE Sno 0602002; -- 差集前者选了、后者没选的课 SELECT Cno FROM SC WHERE Sno 0602001 EXCEPT SELECT Cno FROM SC WHERE Sno 0602002;用集合运算符写语句短、语义直白但有两个硬约束两边的列数必须一致对应列的数据类型必须兼容。列数不一致时报“使用 UNION、INTERSECT 和 EXCEPT 运算符组合的所有查询都必须具有相同数量的表达式”这个报错信息很长但问题往往就是某个SELECT多写了一列。另外EXCEPT和INTERSECT的优先级高于UNION混用的时候不加括号容易算错顺序。对应的子查询写法分别是IN、EXISTS和NOT EXISTS。以交集为例用EXISTS写出来是“0602001 选的每一门课去选修表里找有没有 0602002 选它的记录”语义和INTERSECT一致但执行计划可能不同数据量大时通常EXISTS更早停止。-- 交集等价写法对 0602001 的每门课判断 0602002 是否也选了 SELECT DISTINCT A.Cno FROM SC AS A WHERE A.Sno 0602001 AND EXISTS (SELECT 1 FROM SC AS B WHERE B.Sno 0602002 AND B.Cno A.Cno);5.3 五个高频报错现象、原因、改法现象一NOT IN的查询返回空结果而数据明明存在。原因是被比较的子查询结果里含NULL。x NOT IN (1, 2, NULL)等价于x 1 AND x 2 AND x NULL最后一项是UNKNOWN整个条件永远不成立。改法有两种子查询里加WHERE 列 IS NOT NULL或者干脆换成NOT EXISTS后者对NULL天然免疫。我现在的习惯是只要子查询可能返回空值一律用NOT EXISTS。现象二先插课程 C02 再插 C01报外码冲突 547。原因是课程表的Cpno是自引用外码插入 C02 时它引用的 C01 还不存在。改法是先把Cpno插成NULL全部课程插完后再用UPDATE补关系或者临时ALTER TABLE Cou NOCHECK CONSTRAINT FK_Cou_Cpno插完再启用但这种方式在生产环境要慎重容易留下脏数据。现象三统计选课门数时结果比预期少人。原因是用了COUNT(Grade)成绩为空的行没被计入。改法是换COUNT(*)它数的是行不是非空值。这个坑在订单金额、评论数、登录次数上完全一样凡是“统计发生了多少次”都应该用COUNT(*)只有“统计有多少个非空值”才用COUNT(列)。现象四算出来的年龄偶尔多一岁。原因是DATEDIFF(YEAR, Sbirth, GETDATE())只比较年份部分不管今年生日过没过。改法是加一次DATEADD修正把“今年的生日”和今天比较没到就减一。实验数据里生日分散很容易撞上这个情况看着像随机错误其实有确定规律。现象五三表连接后行数暴涨。原因是漏写连接条件或者连接条件写错产生了笛卡尔积。这种现象不会报错只会让结果看起来“多了很多重复行”。改法是先单独COUNT(*)每张基表再COUNT(*)连接结果行数应该等于最细粒度那张表的行数对不上就从连接条件逐个排查。多表查询写完先对行数这个习惯能挡掉大部分低级错误。6. 用双向 EXCEPT 验证两种写法等价再谈索引实验 3 的每道题都要求两种实现最容易出问题的地方不是写不出来而是写完不知道两种写法结果到底一不一致。肉眼看几十行数据不现实我一般用双向EXCEPT做等价验证把写法 A 的结果和写法 B 的结果互相做差集两边都为空就说明结果集完全一致。-- 验证 UNION 写法与 IN 写法结果等价 -- 两个查询都只返回 Cno 一列才能做集合运算 SELECT Cno FROM SC WHERE Sno 0602001 UNION SELECT Cno FROM SC WHERE Sno 0602002 EXCEPT SELECT DISTINCT Cno FROM SC WHERE Sno IN (0602001, 0602002); -- 反向再差一次 SELECT DISTINCT Cno FROM SC WHERE Sno IN (0602001, 0602002) EXCEPT SELECT Cno FROM SC WHERE Sno 0602001 UNION SELECT Cno FROM SC WHERE Sno 0602002;两段都返回空结果才敢在实验报告里写“两种方法结果相同”。只做单向差集是不够的A 减 B 为空只能说明 A 是 B 的子集反过来可能漏行。这个手法在迁移 SQL、改写查询、对比新旧存储过程结果时都通用比人工对眼睛可靠得多。第二个值得顺手做的事是看执行计划。实验数据量小加不加索引都是毫秒级看不出差别但把选修表的数据复制到几万行再跑一次差异就出来了。选修表上按学号查是最常见的访问路径PK_SC的联合主码已经把Sno放在第一列等于自带一个可用索引如果查询条件经常是单独按课程号过滤那就需要额外建一个Cno上的索引。验证手法适用场景判断标准双向 EXCEPT两种写法结果比对两个方向都返回 0 行COUNT 对行数多表连接是否产生笛卡尔积连接结果行数等于最细粒度基表行数执行计划判断是否走索引出现 Index Seek 而非 Table Scan加索引前后计时验证索引收益同一查询耗时明显下降建索引的语句本身很简单关键是别乱建。SC表上已经有联合主码再给Sno单独建索引就是冗余给Grade建索引收益也很低因为成绩的选择性差查 60 分以下和查 90 分以上可能各占一半数据优化器宁愿全表扫。真正值得建的是外码列和高选择性过滤列比如经常按课程号统计选课人数Cno上加索引就有意义。-- 按课程号统计时给外码列补索引IF NOT EXISTS 避免重复建 IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name IX_SC_Cno AND object_id OBJECT_ID(SC)) CREATE NONCLUSTERED INDEX IX_SC_Cno ON SC(Cno);验证索引有没有被用上不要只看“变快了”要看执行计划里是不是出现了Index Seek。如果还是Table Scan可能是统计信息没更新跑一句UPDATE STATISTICS SC再看也可能是查询本身写成了SELECT *或者对列做了函数运算导致索引失效比如WHERE YEAR(Sbirth) 1999就走不了Sbirth上的索引得改成范围比较。写完这套东西之后我养成了一个习惯任何一条多表查询或者子查询在交给别人之前先用COUNT(*)对一遍行数再用双向EXCEPT和自己想当然的另一种写法比一遍两个都过了才敢说“这条 SQL 是对的”。有次偷懒跳过这一步交上去的统计结果里少了一个没出成绩的学生被追问了半天才定位到COUNT(Grade)上。从那以后我每次写聚合查询都强制走一遍行数核对宁可多花两分钟也不想再解释一遍为什么数字对不上。希望帮到你。本文还有配套的精品资源点击获取