简介《数据库实验5嵌套查询》实验报告面向正在学习数据库查询语言的学生或数据库初学者解决SELECT统计查询、连接查询与嵌套查询理解难的问题。报告以CPXS数据库为实验载体逐项演示COUNT、SUM、MAX、MIN等统计函数的使用方法覆盖INNER JOIN、LEFT JOIN、RIGHT JOIN等连接查询语法并通过子查询、派生表等嵌套操作完成复杂检索例如统计客户数目、求库存量总和、筛选订购两种以上产品的客户、查询由上海客户订购且数量大于200套的产品信息等每个案例均附完整SQL语句可帮助读者理解各类查询的语法格式与实际应用场景。压缩包仅1个doc格式文件大小642KB内容精炼、结构清晰便于打印复习或对照练习。目前已有930人学习下载适合需要快速掌握SQL查询技巧的课程实验者使用。1. 数据库实验5嵌套查询先搞清楚实验在考什么拿到数据库实验5嵌套查询这个题目时很多人以为它只是一份需要填写的作业文档直到真正在数据库客户端里跑SQL才发现题目看着都会一提交就报错。我这些年带过不少写实验的人掉进坑里的基本都不是语法记不住而是没弄清楚子查询到底返回来的是什么是单个值、一列多行还是一个能当表用的结果集。这个实验通常放在关系数据库课程的中段考察“先查谁、再按什么条件过滤”这一整条思考链能不能平移成嵌套SQL。适合正在赶实验报告的学生、准备面试刷题的新手以及想把子查询用明白的同事。读完这篇文章你会知道它考什么、怎么写以及最容易翻车的四个地方。2. 嵌套查询的分类与执行逻辑为什么子查询比多表连接更容易翻车嵌套查询的底层逻辑不复杂就是“查询里再套查询”。可一旦套起来执行顺序、结果形态、空值参与全都开始变得反直觉。我习惯把子查询先按“是否依赖外层”分成两类再按“返回几行几列”去判断能放在哪个位置。2.1 三类嵌套查询无关子查询、相关子查询、集合量词改写第一类是无关子查询也叫非相关子查询。子查询可以完全独立运行不引用外层查询的任何列。比如WHERE dept IN (SELECT dept FROM student WHERE sname 张三)内层先单独查出张三的系再拿这个结果和外层的dept比较。这类查询好处是好调试我会先把内层SQL单独执行一遍确认它返回的是预期集合再套到外层去。第二类是相关子查询也叫关联子查询。子查询里引用了外层表的别名比如WHERE EXISTS (SELECT 1 FROM sc WHERE sc.sno student.sno)。这里的student.sno来自外层理论上外层每一行都要带着自己的值去执行一次内层查询。相关子查询最难的点在于别名作用域外层表必须起别名而且别名要在内层能被看见。如果写错了数据库会直接报“Unknown column”如果没写错但漏了索引性能会肉眼可见地变慢。第三类其实不算严格意义上的独立类别而是为了写出“比所有都大”“比任一个都大”这类语义而生的集合量词ANY、ALL、SOME。配合单列多行子查询使用效果是age ANY(...)等价于“大于结果集里的最小值”age ALL(...)等价于“大于结果集里的最大值”。这些量词在实验题里出现频率不低而且经常被拿来和MIN、MAX的标量写法对比。理解这三种类型后嵌套查询在你眼里就不再是“一层套一层”的壳而是“内层先算什么、外层要拿它当什么”的执行模型。无关子查询先跑内层相关子查询外层驱动内层集合量词是内层返回一个集合供外层做范围比较。这个模型是后面所有排查动作的基础。2.2 子查询的三种返回形态标量、单列多行、多列成对子查询写出来之后数据库先算出一个结果这个结果必然落在这几种形态里。判断形态比背语法更重要因为很多报错都来源于“形态和上层运算符不匹配”。第一种形态是标量也就是单个值。比如SELECT AVG(age) FROM student WHERE dept 数学返回一个数字。标量子查询能放在、、这些比较运算符右边。一旦内层真实数据有多行数据库就不知道拿哪一行去比较直接报“Subquery returns more than 1 row”。我在实验课上见过太多次这种报错都是没先把内层跑一遍。第二种形态是单列多行也就是一列但有多行。典型用途是IN、NOT IN、ANY、ALL。比如查“和某个系学生同龄的人”用age IN (SELECT age FROM student WHERE dept 计算机)。这种形态好处理但要警惕NULL如果子查询结果里混入了NULLNOT IN整个条件会变成UNKNOWN导致最后一行也查不出来。这是实验中常见的“结果无端少行”的原因。第三种形态是多列多行也就是一个临时表。它不能放在或IN后面直接比较除非用成对的行构造器比如(sno, cno) IN (SELECT ...)最自然的去处是FROM子句里当派生表。比如FROM (SELECT dept, AVG(age) AS avg_age FROM student GROUP BY dept) t这个t必须起别名而且外层可以访问t.dept、t.avg_age。多列形态还常见于HAVING分组后和另一个子查询的聚合结果比较。所以拿到一个嵌套查询题目我第一件事不是写SQL而是先问自己这个子查询我要的是单个值、一个集合还是一张临时表这个问题答对了写出的SQL基本能跑通答错了后面全在调试报错。2.3 嵌套查询与连接查询的等价改写选型理由同一个业务问题用连接查询和用嵌套查询往往都能做出来。比如“查没有选过课的学生”可以是LEFT JOIN sc ... WHERE sc.sno IS NULL也可以是NOT EXISTS (SELECT 1 FROM sc WHERE sc.sno student.sno)。既然结果一样为什么实验里还要专门练嵌套查询我的理解是嵌套查询更贴近人脑的过滤思路——“先找出一个集合再按这个集合过滤”而不需要关心连接后行数会不会膨胀。选型上可以有一条简单原则如果查询最终只需要外层表的字段优先用嵌套查询省去DISTINCT如果还需要输出子查询里的某个字段比如要输出每个系的平均年龄那就必须把子查询放到FROM里当派生表或者直接用 JOIN。举个例子查“各系年龄最小的学生”用FROM子查询先算每个系的最小年龄再 JOIN 回学生表取详细信息这个写法比纯IN写起来更顺也比直接 JOIN 更容易拆开调试。性能上教科书常说相关子查询很慢但在新版本数据库里并不绝对。MySQL 会试图把IN改写成 semi-join把EXISTS改写成依赖子查询或物化真正决定速度的往往是有没有索引。所以我不建议无脑绕开嵌套查询而是用EXPLAIN看执行计划。如果计划里出现了DEPENDENT SUBQUERY并且子查询走的全表扫描那才是需要改写或加索引的信号。3. 实验文档里常见的要求拆解从where到having再到from数据库实验5嵌套查询的文档形式通常是给三张表定义好主外键然后列七八道查询题。题目看起来都在问“哪些人/哪些课”但真正拉开差距的是你能不能把“先算什么、后过滤什么”拆清楚。3.1 实验步骤的典型顺序建库→验证数据→逐题写查询我一般不会直接上手写题而是先按文档里的表结构在本地建一个库。很多写不出来或结果不对的情况不是因为嵌套查询难而是没看清表里的外键和空值。比如学生和选课之间是一对多课程和选课也是一对多如果漏了选课表中的某条记录后面所有“没选课的人”都会算错。建库之后不要急着跑查询先验证数据。用几条简单的SELECT * FROM student和SELECT COUNT(*) FROM sc确认每张表的行数、主键是否唯一、外键是否都能对应上。这一步很枯燥但能避免后面“查询结果看起来好怪”的玄学问题。数据确认后我会把实验文档里的每一道题先抄成注释再把题目拆成两层外层要什么行、内层先过滤出什么集合。拆完再写SQL写错的概率小一半。拆题的时候要养成给每道题单独保存成.sql文件的习惯文件里用注释标好题号。比如q5_has_all_courses.sql。这样实验报告要贴代码时不用临时翻历史记录调错时也只影响单个文件不用在一个大脚本里找半天。3.2 用IN/EXISTS/ANY/ALL写出五类必考查询实验题虽然有各种变体但嵌套查询的高频考法基本集中在五类。第一类是用IN做等价集合过滤适合“和某个人/某些人同属性”的题目。比如查和“张三”同一个系的学生-- Q1: 用 IN 做等价集合过滤 SELECT sno, sname, dept FROM student WHERE dept IN ( SELECT dept FROM student WHERE sname 张三 );内层先查出张三的系名返回一行或零行外层再筛选。如果表里有重名的张三且他们系不同这个查询会返回多个系的学生这不一定错但要先看看题目到底问的是“一个张三”还是“所有张三”。所以每次写之前我会确认主键和业务含义。第二类是用EXISTS和NOT EXISTS表达“存在/不存在”。这是相关子查询的经典场景比如查没有选过任何课程的学生-- Q2: 用 NOT EXISTS 表达“不存在” SELECT sno, sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno );这里的SELECT 1不是真的想返回1只是告诉数据库“只要存在一行就算匹配”。EXISTS关心的是行数是否大于0不关心具体查出了什么列所以写1或*结果一样。我用1是为了让执行计划更清爽。第三类是用ANY和ALL做范围比较。比如查比“计算机”系任意一个学生年龄都大的学生-- Q3: 用 ANY 表达“大于任意一个” SELECT sno, sname, age FROM student WHERE age ANY ( SELECT age FROM student WHERE dept 计算机 ); ANY在实际语义上等于“大于计算机系最小年龄”。如果计算机系没有学生内层集合为空age ANY(空集)返回FALSE整个查询一行都不会出。这里很容易踩坑以为空集不影响。第四类是用HAVING过滤分组条件子查询放在聚合之后。比如查平均成绩高于全体学生平均分的系-- Q4: 用 HAVING 对分组结果做嵌套过滤 SELECT dept, AVG(score) AS avg_score FROM student s JOIN sc ON s.sno sc.sno GROUP BY dept HAVING AVG(score) ( SELECT AVG(score) FROM student JOIN sc ON student.sno sc.sno );这里的HAVING子查询先算一次全表平均分然后对每个系的平均分做比较。要注意子查询里不能直接用外层s的别名因为它在分组后才执行和每次分组关联的s已经不是一个确定的行了。写这种题目时子查询往往和JOIN混在一起别被表面结构吓住核心还是“先聚合、再过滤”。第五类是把子查询放进FROM当派生表用。比如查每个系年龄最小的学生-- Q5: 用 FROM 子查询做分组预聚合 SELECT t.dept, s.sname, s.age FROM ( SELECT dept, MIN(age) AS min_age FROM student GROUP BY dept ) t JOIN student s ON s.dept t.dept AND s.age t.min_age;这个写法的好处是内层先算出“每个系最小年龄”外层再拿这个结果去匹配完整学生信息。它把复杂问题拆成了“聚合”和“关联”两步每一步都能单独验证。如果某个系同时有两个年龄相同的最小学生这个查询会把两个人都列出来而如果用GROUP BY直接查通常只会显示一个这正好体现嵌套查询在信息完整性上的优势。3.3 把嵌套查询落到实验报告的SQL注释与结果核对实验报告要你写“SQL语句”和“执行结果”但直接贴一段SQL并不够。我教学生的方法是在每个SQL文件顶部用多行注释写清楚题号、拆解思路、最终查询字段这样报告里的代码即使被压缩读代码的人也能一眼看懂。比如-- Q5: 查每个系年龄最小的学生 -- 思路: -- 1. 子查询按系分组取 MIN(age) -- 2. 外层 JOIN student按系和年龄匹配 -- 边界: 若一个系有多个同龄最小者会全部列出结果核对这一步不要靠肉眼数行数。数据库客户端不会告诉你“少了哪一行”但对拍可以先用SELECT COUNT(*)拿到当前查询的行数再用一个直觉上更可靠的方式重写一遍比较两个结果的行数。比如用窗口函数写“各系最小年龄”的版本与上面FROM子查询版本对比。行数一致不代表一定对但行数不一致一定有问题。4. 用 MySQL 8 跑通嵌套查询最小命令与参数说明选择 MySQL 8 的原因是它免费、容易装而且支持窗口函数和公共表表达式方便做验证。如果你所在实验课用的是 SQL Server嵌套查询语法基本一致差别主要在分号、LIMIT或TOP这类地方。这一章我用一个最常用的教学库结构把从建库到验证的完整路径跑一遍。4.1 准备实验环境的四行命令我习惯把实验环境建在独立的库里这样不会污染其他数据。创建库和表的命令如下CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4; USE school; CREATE TABLE student ( sno CHAR(8) PRIMARY KEY, sname VARCHAR(20) NOT NULL, dept VARCHAR(40), age INT ); CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1) ); CREATE TABLE sc ( sno CHAR(8), cno CHAR(4), score DECIMAL(5,2), PRIMARY KEY (sno, cno), KEY idx_cno (cno) );这里有几个细节值得说明。sno用CHAR(8)是因为学号通常是等长数字串比VARCHAR少一点存储开销。sc表的主键是(sno, cno)复合主键能同时保证同一学生对同一课程只有一条选课记录。idx_cno是给cno单独建索引因为很多查询会从课程号关联选课表没有这个索引相关子查询执行时会慢很多。建表后插入几行测试数据INSERT INTO student (sno, sname, dept, age) VALUES (2023001, 张三, 计算机, 20), (2023002, 李四, 计算机, 21), (2023003, 王五, 数学, 19), (2023004, 赵六, 数学, 22), (2023005, 钱七, 物理, 20); INSERT INTO course (cno, cname, credit) VALUES (C001, 数据库原理, 4), (C002, 操作系统, 3), (C003, 数据结构, 4); INSERT INTO sc (sno, cno, score) VALUES (2023001, C001, 85), (2023001, C002, 92), (2023002, C001, 78), (2023003, C002, 88), (2023003, C003, 95), (2023004, C001, 59);我故意不把所有学生都插入选课记录这样后面“没选课的学生”“有不及格记录”的查询才有意义。插入完成后用SELECT COUNT(*)分别核对三张表确认学生5人、课程3门、选课6条。这一步是自检很多调试花掉的时间都省在这儿。4.2 经典实验的SQL模板子查询在where、from、having里怎么放嵌套查询最容易被卡住的点是不知道子查询能放在哪个位置。实际上只有三个高频位置WHERE、FROM、HAVING。放在WHERE里最常见比如查询年龄大于“数学系平均年龄”的学生-- WHERE 中的标量子查询 SELECT sno, sname, age FROM student WHERE age ( SELECT AVG(age) FROM student WHERE dept 数学 );AVG(age)返回的是单个数值所以这里用是合法的。但有个隐藏问题如果数学系没有学生AVG返回NULL外层所有age NULL都会变成UNKNOWN结果就是查询一行都查不到。这种“查不到不是报错”的情况恰恰是嵌套查询最阴险的地方。要规避可以在子查询里先加HAVING COUNT(*) 0或者用COALESCE把NULL转成默认值。放在FROM里的子查询叫派生表它必须起别名。比如查 C001 课程成绩高于该课程平均分的学生-- FROM 中的派生表 WHERE 中的标量子查询 SELECT t.sno, t.sname, t.score FROM ( SELECT s.sno, s.sname, sc.score FROM student s JOIN sc ON s.sno sc.sno WHERE sc.cno C001 ) t WHERE t.score ( SELECT AVG(score) FROM sc WHERE cno C001 );这里t是整个派生表的别名外层访问它的字段时必须带t.前缀。如果不写别名MySQL 直接报“Every derived table must have its own alias”。子查询里先做连接目的是把学生姓名带进来同时只保留 C001 的选课记录。外层的WHERE再算一次平均分作比较。这种写法把“取数据”和“过滤数据”分开调试时可以先跑内层立刻看到是不是多表连接出了问题。放在HAVING里的子查询常用于聚合条件。比如查“平均成绩高于全体学生平均分的系”-- HAVING 中的子查询 SELECT s.dept, AVG(sc.score) AS avg_score FROM student s JOIN sc ON s.sno sc.sno GROUP BY s.dept HAVING AVG(sc.score) ( SELECT AVG(score) FROM student JOIN sc ON student.sno sc.sno );子查询在HAVING里的执行时机是分组已经完成后。它先算一次全体平均分然后对每个系的平均分做过滤。要注意这里不能在子查询里用s.dept因为子查询不是针对某一个系运行的它是全局一次。如果某系没有选课记录JOIN会把它的行直接过滤掉所以HAVING结果里不会看到没选课的学生所属系。4.3 可复现的验证SQL比较嵌套查询与连表结果写完嵌套查询后最推荐的验证方式是同时写一个连接查询版本然后比较两个版本的结果行数或差集。比如“没选课的学生”嵌套写法用NOT EXISTS连接写法用LEFT JOIN-- 方法ANOT EXISTS SELECT s.sno, s.sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno ); -- 方法BLEFT JOIN SELECT s.sno, s.sname FROM student s LEFT JOIN sc ON s.sno sc.sno WHERE sc.sno IS NULL;两个查询在语义上期望返回相同行。如果结果不一致多半是数据里有重复sno但主键保证了不会或某条sc记录的sno为 NULL。比较时不要只看“好像一样”我一般会把两个结果分别存成临时表再去查差集-- 找A有B没有的行 SELECT a.sno FROM result_a a WHERE NOT EXISTS ( SELECT 1 FROM result_b b WHERE b.sno a.sno );如果差集查询返回空说明两个方法结果一致。再把临时表删掉。这个方法能用在几乎所有嵌套查询题上尤其是用GROUP BY容易去重场景差集检查比肉眼靠谱得多。5. 嵌套查询的避坑与常见问题从结果错误到玄学慢查询嵌套查询的错误往往不是“语法错误”而是“逻辑错误”。数据库不会告诉你结果少了一行是因为NULL也不会告诉你慢是因为索引缺失。这一章把我见过最多的四类问题整理出来按现象、原因、解决的顺序写。5.1 子查询返回多行却用了等号的报错现象执行SELECT * FROM student WHERE age (SELECT age ...)时MySQL 直接报错Subquery returns more than 1 row。原因右边期望一个标量值但你子查询返回了多行。数据库不知道该拿哪一行的值来比较干脆拒绝执行。解决先单独跑子查询确认它到底返回几行。如果你确实想在“等于其中任意一个”的语义下查询把改成IN如果你只想要其中某一个可以在子查询里加LIMIT 1但要知道这会让结果带上随机性。实验里碰到这个问题我会直接问自己一句这个条件到底要不要唯一比如查“和计算机系某学生同龄的人”应该用IN而查“和计算机系年龄最小的人同龄”子查询里要写MIN(age)而不是直接返回多行。-- 错误写法 SELECT * FROM student WHERE age (SELECT age FROM student WHERE dept 计算机); -- 正确写法匹配任意一个 SELECT * FROM student WHERE age IN (SELECT age FROM student WHERE dept 计算机);这一条是嵌套查询入门最经典的报错也是面试里最喜欢问的“为什么加IN就对了”。理解了标量和多行的区别以后看到报错就能立刻定位。5.2 EXISTS与IN在NULL上的差异结果莫名少行现象用NOT IN查“没有选课的学生”结果一行都没有但用NOT EXISTS能查出一堆。原因这属于 SQL 三值逻辑的老问题。NOT IN相当于“不等于列表里任何一行”但列表中如果含NULLx ! NULL的结果是UNKNOWN不是真也不是假。一个条件里只要有一个UNKNOWN最终结果就是UNKNOWN被当成“不满足条件”于是所有行都被过滤掉。解决优先用NOT EXISTS它在语义上更贴近“不存在”。如果一定要保留NOT IN可以在子查询里加WHERE col IS NOT NULL把NULL剔除。例如-- 慎用子查询结果含NULL时结果异常 SELECT sno, sname FROM student WHERE sno NOT IN ( SELECT sno FROM sc ); -- 推荐NOT EXISTS 不关心NULL SELECT sno, sname FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.sno s.sno );这一条在实验数据不干净时特别容易踩中。很多同学看到NOT IN返回空以为是数据问题实际上是非空约束没做好。5.3 相关子查询在大表上慢执行计划与索引现象同一道题用两个表连接跑得很快换成相关子查询后要等十几秒甚至更久。原因相关子查询对每一行外层记录都可能执行一次内层查询。如果内层查询的关联字段没有索引比如sc.sno没有索引那么外层每遍历一条学生记录都要对sc做一次全表扫。数据量翻倍耗时可能翻几倍。解决先加EXPLAIN看执行计划重点看type列是不是ALL、Extra里有没有Using where加Using join bufferselect_type里有没有DEPENDENT SUBQUERY。如果相关子查询走了全表扫我会先给关联字段建索引CREATE INDEX idx_sno ON sc(sno);建立索引后通常就能看到type从ALL变成ref。如果加了索引还是慢再考虑把相关子查询改写成LEFT JOIN加IS NULL的形式。现代数据库优化器不一定把所有EXISTS都转成连接但你手动改写后执行计划里至少能清楚看到是不是走了索引。5.4 别名作用域的坑最容易在from子查询上翻车现象在FROM子查询里引用外层查询的列名MySQL 直接报Unknown column s.dept in where clause。原因普通派生表是“先计算出整个内层结果再给外层用”内层执行时并不存在外层那行数据。想在派生表里动态引用外层列需要横向派生表语法LATERAL不是所有版本都默认支持。我在实验里遇到过不少同学把相关子查询的概念硬套到FROM子查询里结果就撞上这个报错。解决把需要引用外层列的逻辑拆出去。比如你想给每个学生带上自己系的平均年龄可以这样写-- 正确先分组算出各系平均年龄再JOIN学生表 SELECT s.sno, s.sname, t.avg_age FROM student s JOIN ( SELECT dept, AVG(age) AS avg_age FROM student GROUP BY dept ) t ON s.dept t.dept;如果业务上必须用“每条学生记录动态计算”那就回到相关子查询的写法把逻辑放在WHERE或SELECT里而不是塞进FROM。记住一条FROM里的子查询是“静态的”WHERE里的相关子查询才是“动态的”。6. 一个实用技巧把嵌套查询改写成窗口函数做对照验证嵌套查询写多了最怕的不是写不出来而是“写出来还觉得自己对”。我养成了一个习惯凡是涉及“最大”“最小”“排名”“分组前N”的实验题我会同时用窗口函数写一个标准答案再拿嵌套查询的结果和它对拍。这样做不是为了炫技而是窗口函数和嵌套查询是两条完全不同的计算路径两边结果一致时才敢把SQL交上去。举个例子要查“每门课成绩最高的学生”。嵌套查询的写法是相关子查询按课程号把内层MAX(score)算出来再在外层匹配窗口函数版本则先用RANK()或DENSE_RANK()给每门课的成绩排序再取排名为1的行-- 嵌套查询版 SELECT sno, cno, score FROM sc a WHERE score ( SELECT MAX(score) FROM sc b WHERE b.cno a.cno ); -- 窗口函数版 SELECT sno, cno, score FROM ( SELECT sno, cno, score, RANK() OVER (PARTITION BY cno ORDER BY score DESC) AS rk FROM sc ) t WHERE rk 1;两个版本的行数如果一致基本可以判定嵌套查询没有少写条件。如果结果不一样我通常先检查窗口函数版里有没有把RANK()和DENSE_RANK()用混再用差集查询定位哪些行对不上。这个对照方法把这个实验最容易丢分的“边界情况”直接暴露出来比如同一课程有两个并列最高分的情况嵌套查询会把两个人都查出来窗口函数用RANK()也会查出来用ROW_NUMBER()则只会保留一个。理解这个差异比反复读教材有用得多。我在自己写实验和帮别人调实验时都坚持“每题至少准备两种写法”。第一次做嵌套查询会觉得多花时间但提交之后不用因为一小处 NULL 或去重逻辑被扣分省下的返工时间远多于当初对拍的那几分钟。这个习惯我从数据库实验一路带到工作里每次遇到复杂 SQL 都先用简单方式确认边界再套正式逻辑。希望帮到你。本文还有配套的精品资源点击获取