简介这是一份数据库课程“实验五 数据统计查询和嵌套查询”实验报告面向高校数据库课程学生、自学者及备考实验考试的用户。文档以 CPXS 数据库为背景通过具体 SQL 语句演示客户数目统计、库存量求和、上海客户订购数量大于 200 套的信息查询、与“美美”公司同城市的客户查询以及未被订购产品筛选等典型操作同时覆盖 SELECT 语句、SUM/COUNT/MAX/MIN 聚合函数、GROUP BY 与 HAVING 分组过滤、INNER/LEFT/RIGHT 连接查询以及子查询、派生表和多种操作符的用法。压缩包内含 1 个 doc 文件整体大小 642KB报告按实验目的、实验内容、SQL 示例和结果说明组织可配合数据库环境边读边练。目前已有 930 人学习下载适合用于数据库实验课复习、期末备考或课程设计时对照练习提升 SQL 查询与数据分析的实际能力。1. 数据库实验5嵌套查询一次实验看清 SQL 子查询的三种形态打开实验文档时很多人会对着“嵌套查询”四个字犯怵明明连接查询已经能查出结果为什么还要把一个 SELECT 塞进另一个 SELECT 里这个实验真正要解决的是让你接受一种新的思维——查询的结果不再只是“最终答案”它可以被当作条件、当作值、甚至当作一张临时表继续参与下一次查询。做完这条线你会发现自己终于能读懂那些带子查询的数据库 SQL而不是只会对着教程增删改查。它适合正在修数据库课程的学生也适合面试前补 SQL 基础的开发者。嵌套查询的写法不难难的是知道什么时候该用、在哪里容易翻车。2. 嵌套查询的执行顺序与三形态先弄清查询怎么跑起来的嵌套查询在实验文档里的定义很朴素把一条 SELECT 查询块放进另一条查询的 WHERE 或 HAVING 子句中。内层那条 SELECT 叫子查询外层叫主查询。子查询的结果可能是单个值、一行值也可能是一张多行多列的表。不同的返回形态决定它能被放在什么位置、能和什么运算符搭配也决定了数据库优化器会怎么执行它。这一章先把这两件事讲透后面写实验 SQL 时你才不会靠猜。2.1 三种子查询形态按返回结果区分不按位置区分判断一条子查询属于哪种形态不看它写在 WHERE 还是 FROM而看它执行完可能返回几行几列。这是整个嵌套查询实验里最核心的一个判断标准。形态返回内容典型位置示例标量子查询单行单列就是一个值SELECT 子句、WHERE 比较运算符右侧SELECT AVG(grade) FROM score行子查询单行多列一整行字段WHERE 后与行构造器比较SELECT sdept, sage FROM student WHERE sno 2019001表子查询多行多列像一张临时表FROM 子句派生表、IN、EXISTSSELECT sno FROM score标量子查询最直观。比如要查询成绩高于平均分的学生平均分是一个数字所以(SELECT AVG(grade) FROM score)是标量外层用grade 比较就合语法。行子查询稍微绕一点它把多个字段打包成一个值组比如WHERE (sdept, sage) (SELECT sdept, sage FROM student WHERE sno 2019001)要求左右两边字段数一致且类型兼容。表子查询是实验中出镜率最高的IN、EXISTS、FROM子句里的派生表全都走这个形态。做实验时判断标准就一条如果一条子查询可能返回多行就别把它放在、、右边否则数据库会直接报错如果它可能返回多列就别放在IN左边。你会发现很多改错题本质都是没先想清楚子查询的返回形态。2.2 非相关与相关子查询两种完全不同的执行顺序实验报告里常让你写“分析该查询的执行过程”这个过程的本质就是区分子查询是否相关。非相关子查询不依赖外层任何字段它的执行顺序是确定的先跑内层得到结果集再拿这个结果集去执行外层。比如查选修了“数据库系统”课程的学生内层先查出这门课的课程号外层再用sno IN (...)去匹配内层只执行一次。相关子查询正好反过来。外层先取出一行把这一行某个字段的值带进内层内层针对这一行算一次外层再取第二行内层再算一次。外层有多少行内层就要跑多少遍。比如查询每门课中成绩低于该门课平均分的学生内层子查询要拿外层的cno去限定范围内外层就绑定了。在 MySQL 的 EXPLAIN 输出里这两种形态会直接体现出来。非相关子查询的select_type常见值是SUBQUERY表示它独立执行一次相关子查询则是DEPENDENT SUBQUERY表示它依赖外层查询每行都可能触发一次执行。实验文档里让你“写出执行顺序”你只要抓住这两类就能答对一半先判断内外层是否有字段关联有关联就是相关子查询没关联就是非相关子查询。注意DEPENDENT SUBQUERY不代表一定慢但它是性能分析的第一信号。看到这个标记先确认内层关联字段有没有索引后面第 5 章会具体讲。3. 建表、造数、跑通最小嵌套查询从零复现实验的完整步骤这个实验常见的落点是一套学生选课数据库三张表学生表 student、课程表 course、选课成绩表 score。我不太喜欢直接拿现成数据库跑建议自己建一个几 MB 的库把表结构和数据都写清楚这样你能看到一条嵌套查询每一步到底在查什么后面改写成连接查询验证时也有据可查。3.1 准备最小数据集三张表十行数据足够跑完实验先建库和表。MySQL 8.0 语法如下SQL Server 或 PostgreSQL 做实验时把数据类型稍微调整即可查询逻辑完全一样。CREATE DATABASE expr5 CHARACTER SET utf8mb4; USE expr5; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, ssex CHAR(2), sage SMALLINT, sdept VARCHAR(20) ); CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(20) NOT NULL, cpno CHAR(4), credit SMALLINT ); CREATE TABLE score ( sno CHAR(9), cno CHAR(4), grade DECIMAL(5,2), PRIMARY KEY (sno, cno) );这段代码里sno用了定长CHAR(9)因为学号是固定长度定长比变长检索更快grade用了DECIMAL(5,2)能存 999.99适合百分制成绩避免浮点误差。score表的主键是(sno, cno)联合主键这同时也给后续按学号关联查询建好了索引。插入数据时注意课程号的引用关系cpno是先行课号允许为 NULL。少量数据就够重点是把嵌套查询的几种形态都覆盖到。INSERT INTO student VALUES (2019001, 张明, 男, 20, 计算机系), (2019002, 李丽, 女, 19, 计算机系), (2019003, 王强, 男, 21, 数学系), (2019004, 赵敏, 女, 20, 数学系), (2019005, 孙浩, 男, 22, 信息系); INSERT INTO course VALUES (C001, 数据库系统, NULL, 4), (C002, 数据结构, C001, 4), (C003, 操作系统, C002, 4), (C004, 计算机网络, C002, 3); INSERT INTO score VALUES (2019001, C001, 92), (2019001, C002, 85), (2019002, C001, 78), (2019002, C003, 65), (2019003, C002, 88), (2019004, C004, 73), (2019005, C001, 91), (2019005, C002, 82);插入顺序是先课程后成绩因为score里的cno是逻辑外键插入成绩时课程必须已经存在。这套数据里没有学生选 C003 和 C004 的全部分支目的就是让“没选课的学生”“没被选的课程”这些边界情况也能被实验查询命中。3.2 三条必写嵌套查询IN、标量比较、EXISTS第一条是经典的两层嵌套用IN查选修了“数据库系统”课程的学生姓名。SELECT sname FROM student WHERE sno IN ( SELECT sno FROM score WHERE cno IN ( SELECT cno FROM course WHERE cname 数据库系统 ) );执行顺序是最内层先查出cname 数据库系统的课程号得到C001第二层在score里按cno C001查出所有选课学号最外层再拿这批学号去student表换姓名。整个链条清晰体现了“查询结果作为下一个查询条件”的核心思想。如果结果里出现重复姓名说明一个学生选了多次这门课但在这套数据里不会发生因为(sno, cno)是联合主键。第二条用标量子查询查“成绩高于全校平均分”的学号和成绩。SELECT sno, cno, grade FROM score WHERE grade ( SELECT AVG(grade) FROM score );这里AVG(grade)在 MySQL 中返回单行单列也就是一个标量。AVG会忽略 NULL所以即使成绩表里有缺失值平均分也不会被 NULL 干扰。这条查询最能体现标量子查询的用法内层不和外层做任何关联执行一次拿到一个数直接参与外层比较。第三条用EXISTS查“没有选任何课的学生”。SELECT sno, sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.sno s.sno );EXISTS只判断内层是否返回了行返回了就为真没返回就为假。子查询里写SELECT 1是习惯做法因为 EXISTS 不关心选了哪些列只关心有没有行。这条是相关子查询外层student每取一行内层就用这一行的s.sno去score里查一次。执行下来2019004赵敏没有选课记录会被查出来。3.3 验证结果把嵌套查询改写成连接查询写实验 SQL 最怕的是“结果不对”但更怕的是“结果看起来对实际少了行”。一个稳妥的验证方法是把嵌套查询改写成等价的连接查询然后对比两个结果集。SELECT DISTINCT s.sname FROM student s JOIN score sc ON s.sno sc.sno JOIN course c ON sc.cno c.cno WHERE c.cname 数据库系统;这条连接查询与前面的IN版本在语义上等价但有一个关键差异连接查询不会自动去重。如果同一个学生选了同一门课两次连接版本会返回重复行嵌套查询不会。所以这里必须加DISTINCT。验证时只看行数不够还要看具体数据最保险的做法是把两个结果都按学号排序后逐行对比。数据量小的时候肉眼能看出来数据量大了就要用集合差来做这个技巧留在第 6 章。4. 相关子查询与 EXISTS/IN 选择从能跑通到跑得快实验文档通常会要求你“用三种方式实现同一个查询”这个练习的真正价值不在抄三条 SQL而在于让你看到不同写法背后是数据库完全不同的执行策略。第 4 章进入嵌套查询里最容易拉开分差的部分——相关子查询的性能行为以及EXISTS与IN该怎么选。4.1 相关子查询的运行模型外层一行内层跑一次先看一条最常见的相关子查询查询每门课中成绩低于该门课平均分的学生。SELECT s1.sno, s1.cno, s1.grade FROM score s1 WHERE s1.grade ( SELECT AVG(s2.grade) FROM score s2 WHERE s2.cno s1.cno );这条查询的执行模型和第 3 章的NOT EXISTS一样。外层score s1每取一行内层就拿这行的cno去算对应课程的平均分然后比较。假设score表有 1 万行非相关子查询只执行一次而这条相关子查询理论上可能要执行 1 万次内层查询。从复杂度看外层 N 行、内层每次 M 行整体可能是 O(N×M)这也是实验里“数据一多就卡死”的主要来源。优化办法很直接给score.cno建索引。内层每次查询都按cno做等值过滤索引让它从全表扫描变成索引查找复杂度降为 O(N×logM)。所以遇到相关子查询慢第一反应不是换写法而是先EXPLAIN看内层走了什么访问路径。ALTER TABLE score ADD INDEX idx_cno (cno); EXPLAIN SELECT s1.sno, s1.cno, s1.grade FROM score s1 WHERE s1.grade ( SELECT AVG(s2.grade) FROM score s2 WHERE s2.cno s1.cno );建索引前后对比 EXPLAIN 输出你会看到s2的type从ALL变成refkey列出现idx_cnorows估算值大幅下降。这就是数据库性能调优里最典型的一课子查询本身不是罪魁祸首缺失索引才是。4.2 EXISTS 与 IN 的等价改写不要背“一定快”的结论同一个需求用IN和用EXISTS都能写。比如查选修了“数据库系统”课程的学生-- 写法 AIN SELECT sno, sname FROM student WHERE sno IN ( SELECT sc.sno FROM score sc JOIN course c ON sc.cno c.cno WHERE c.cname 数据库系统 ); -- 写法 BEXISTS SELECT s.sno, s.sname FROM student s WHERE EXISTS ( SELECT 1 FROM score sc JOIN course c ON sc.cno c.cno WHERE sc.sno s.sno AND c.cname 数据库系统 );两种写法在多数数据库优化器下最终会变成类似的执行计划尤其 MySQL 8 会把IN和EXISTS都改写成半连接SEMI JOIN。但它们在语义和边界行为上有差异实验报告里常被拿出来对比。对比维度IN 子查询EXISTS 相关子查询执行时机子查询先物化结果外层再匹配外层逐行触发内层命中即停NULL 处理子查询结果含 NULL 时IN 基本不受影响但NOT IN会翻车EXISTS 只看是否返回行不受 NULL 影响优化器改写可能物化也可能半连接可能半连接也可能保持相关执行适用直觉子查询结果集小、且外层数据量很大时常见外层数据量小、内层有高效索引时常见我的经验是小数据量上这两种写法没有肉眼可见的差异别花时间纠结。真到了几百万行的表用EXPLAIN看select_type和rows哪个执行计划低就用哪个很多网上所谓的“EXISTS 一定比 IN 快”的说法在现在的优化器面前已经过时了。4.3 派生表与数据量放大嵌套查询的性能边界嵌套查询还有一个重要位置是FROM子句这种写法得到的是一张派生表。比如查每门课最高分对应的学生记录可以先按课程分组取最高分再把它当临时表和原表连接SELECT s.sno, s.cno, s.grade FROM score s JOIN ( SELECT cno, MAX(grade) AS max_grade FROM score GROUP BY cno ) t ON s.cno t.cno AND s.grade t.max_grade;这里FROM (SELECT ... GROUP BY cno) t就是派生表。数据库通常会先把这段子查询的结果物化成一张临时表再与score做连接。MySQL 8 对派生表有derived_merge优化如果子查询可以被合并进外层它就不会物化但如果子查询里有GROUP BY、聚合函数这类操作通常只能物化。派生表的最大隐患是物化后的临时表没有合适的索引时外层每一次连接都要遍历它。数据量从几千行放大到几十万行时这条查询可能从毫秒级变成秒级。所以做实验时我会刻意把数据量翻倍再看执行计划一旦发现派生表成了瓶颈优先把FROM子查询改写成普通JOIN或直接写成两条 SQL 分步查。嵌套查询不是写得越少越好而是要让每一步的访问路径都可控。5. 嵌套查询避坑记录五个让实验翻车的问题嵌套查询的语法不难但坑不少而且很多坑不是报错是“结果错了你都不知道”。这一章挑五条最常见的记录都是实验课上真实翻车的例子按“现象 → 原因 → 解决”写清楚。5.1 单行子查询返回多行错误 1242 的成因与修复现象执行SELECT sno, grade FROM score WHERE grade (SELECT grade FROM score WHERE cno C001)时MySQL 报错ERROR 1242 (21000): Subquery returns more than 1 row整条 SQL 直接报废。原因右边要求的是一个标量值但cno C001对应了多个学生的成绩子查询返回了多行文法就过不了。解决先想清楚子查询的返回形态。如果只想比“课程 C001 的最高分”用MAX(grade)如果想比“C001 的所有成绩”那语义上就是“高于最大值”等价于grade ALL (SELECT grade FROM score WHERE cno C001)。别急着加LIMIT 1加上 LIMIT 后结果是“任意一行”不是“最大那一行”逻辑就错了。5.2 多行结果用错比较符ANY 与 ALL 的边界现象同学想查“成绩高于课程 C001 任一学生成绩”的记录写了WHERE grade ANY (SELECT grade FROM score WHERE cno C001)结果发现返回了几乎全部成绩完全不符合预期。原因 ANY表示“大于其中一个即可”等价于grade (SELECT MIN(grade) ...)它筛选出的范围非常大。很多人记成“大于任一”就是“压过所有人”这恰好和 ALL搞反了。解决如果目标是“比 C001 所有人都高”应该用grade ALL (SELECT grade ...)它等价于grade (SELECT MAX(grade) ...)如果目标是“只要高过一个人就行”才用 ANY。这个地方最好的习惯是看到ANY/ALL立刻用MIN/MAX做等价替换帮助自己确认语义后再写。5.3 相关子查询慢到像死锁先查索引再怀疑机器现象一条“查低于系平均分”的相关子查询在几百条测试数据上秒回导入全量数据后跑了十几分钟没出来体验上跟数据库死锁了一样。原因外层每取一行内层都要对关联字段做一次全表扫描。数据量从几百放大到几万复杂度从几百乘几百变成几万乘几万指数级上涨。这是相关子查询最常见的性能坑问题不在写法和机器在内层关联字段上没有索引。解决先用EXPLAIN看执行计划。如果看到内层表type是ALL、key是 NULL基本就能锁定问题。然后在score表的cno或sno上建索引慢查询会在毫秒级内恢复。记住一点相关子查询不是不能用是必须保证内层查询的驱动字段有索引可走。这个排查顺序比换整条 SQL 的写法重要得多。5.4 子查询里的 ORDER BY 不生效排序位置写错了现象想查出“每个系里年龄最小的学生”有人写成SELECT sdept, sno, sname FROM (SELECT * FROM student ORDER BY sage ASC) t GROUP BY sdept结果返回的并不是最小年龄那行。原因SQL 标准不保证子查询的排序结果能保留到外层。外层GROUP BY在处理时可能直接丢弃子查询里的ORDER BY所以“先排好序再分组”的思路本身就站不住。解决用聚合函数确定目标再关联回原表。比如先GROUP BY sdept取MIN(sage)再用这个值去匹配对应学生或者用窗口函数ROW_NUMBER() OVER (PARTITION BY sdept ORDER BY sage ASC)取序号为 1 的行。记住子查询里的ORDER BY只有在配合LIMIT时才有明确意义别把它当全局排序来用。5.5 NOT IN 遇上 NULL结果少到令人怀疑人生现象用SELECT sno, sname FROM student WHERE sno NOT IN (SELECT sno FROM score)查“没选课的学生”结果返回空集合但表里明明有没选课的人。原因这是嵌套查询里最隐蔽的逻辑坑。如果score表的sno字段里有 NULLNOT IN在语义上等价于“不等于任何一项”而sno NULL的结果是 UNKNOWNWHERE只会保留结果为 TRUE 的行UNKNOWN 全部被过滤掉最终一个都不剩。解决优先用NOT EXISTS改写因为它只关心子查询有没有返回行不参与 NULL 比较。SELECT sno, sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.sno s.sno );如果想保留NOT IN也可以先排除掉 NULLWHERE sno NOT IN (SELECT sno FROM score WHERE sno IS NOT NULL)但在实验场景里写NOT EXISTS更干净。这个坑我栽过不止一次现在看到NOT IN的第一反应就是检查子查询结果集里有没有 NULL。6. 用执行计划验证嵌套查询实验报告里的加分技巧6.1 学会看 EXPLAIN 里的 select_type 和 rows嵌套查询写得对不对最终要靠执行计划来验证。给任何一条嵌套查询前面加EXPLAINMySQL 会在不真正执行查询的情况下返回执行方案。重点看三列select_type告诉你子查询是怎么被处理的type告诉你访问路径rows告诉你数据库估算要扫多少行。EXPLAIN SELECT s.sno, s.sname FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.sno s.sno );输出里student的select_type是PRIMARYscore的select_type是DEPENDENT SUBQUERY。看到DEPENDENT就要警惕这是相关子查询确认关联字段score.sno是否有索引。再看type从system、const、eq_ref、ref一路到range、index、ALL越靠右说明扫的数据越多ALL就是全表扫描。6.2 一个验证技巧用差集检查嵌套查询是否少行嵌套查询最怕“结果看着对其实少了行”。我的习惯是写完嵌套查询后用NOT EXISTS做一次差集验证如果 A 结果里的每条记录都不在 B 结果里那说明结果集一致。-- 例子验证“选了数据库系统课程的学生”两个版本结果是否一致 SELECT s.sno FROM student s WHERE s.sno IN (SELECT sno FROM score WHERE cno C001) AND NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.sno s.sno AND sc.cno C001 );这条查询如果返回空集说明两个版本没有差异。我把这个差集思路当成做嵌套查询实验的固定收尾动作每次都会把嵌套版和连接版分别跑一遍再用差集检查。第一遍做实验时我栽在NOT IN和 NULL 上面全表少了两个没选课的人查了一晚上才反应过来。后来每写一条嵌套查询我都习惯性地先问一句“子查询返回什么形态、有没有 NULL、能不能连接改写”再用 EXPLAIN 看一遍访问路径。这个习惯帮我省下的排查时间远超多写几行 SQL 的功夫。希望帮到你。本文还有配套的精品资源点击获取