简介数据库实验5嵌套查询.doc是一份面向数据库初学者的完整实验报告围绕CPXS数据库演示统计查询、连接查询与嵌套查询的典型操作重点帮助学生掌握SELECT语句、COUNT/SUM/MAX/MIN等统计函数以及GROUP BY、HAVING、BETWEEN、LIKE、IN、子查询等常用语法适合数据库原理课程同步练习、上机实验和考前复习。压缩包内仅有1个doc文档大小642KB内容包含实验目的、实验步骤、多条可运行的SQL语句示例、实践结论及相关知识点整理可直接对照数据库环境验证运行结果。这些示例覆盖求客户数目、库存量总和、最高单价、至少订购两种产品的客户编号以及查询上海客户订购数量大于200套的联系方式、与“美美”同城客户、未被订购产品等典型场景能够帮助读者理清分组统计、多表连接和嵌套子查询的组合逻辑。目前已有930人学习下载对于需要撰写数据库实验报告或强化SQL查询能力的初学者来说是一份结构清晰、即拿即用的参考资料。1. 嵌套查询从一条报表SQL说起如果你做过业务报表大概率遇到过这类需求查出“选了某门课并且成绩高于该课平均分”的学生名单。第一次写的人会本能地先把平均分查出来再拿着这个数去写第二条查询。两条SQL能跑通但换个班级、换门课就要改代码维护成本很高。数据库实验5嵌套查询这份实验核心就是让你摆脱这种“先查一次、再查一次”的笨办法把两条SQL合成一条让数据库自己完成中间结果的传递。嵌套查询也叫子查询本质是“一条SELECT语句的查询结果作为另一条SELECT语句的输入条件或数据源”。它解决的最大痛点是查询条件本身依赖另一个查询结果而这个结果无法用简单的JOIN直接表达或者用JOIN写出来逻辑特别绕。适合的读者有两类一类是正在做数据库课程实验、需要在Word里写实验报告的同学另一类是工作中写SQL常遇到“多层条件过滤”但只会拆成多条语句的开发者。这篇笔记会把嵌套查询从执行原理、建表环境、六种典型写法到性能坑位一次讲透。2. 嵌套查询的执行顺序与基本形态先让原理立住再动手写嵌套查询的第一课不是背语法而是建立执行顺序的直觉。很多人在写嵌套查询时翻车不是因为不会写关键字而是不知道数据库到底先跑哪一段。搞清楚执行顺序你才能在脑子里面“预编译”每条SQL而不是靠反复执行去试错。2.1 非相关子查询先算内层再算外层非相关子查询是最简单也最常用的一种形态内层子查询不依赖外层查询的任何列可以独立运行。它的执行顺序很直观——先执行内层查询拿到结果集再把这个结果集当作外层查询的过滤条件来用。SELECT student_name, course_id, score FROM score WHERE course_id CS101 AND score ( SELECT AVG(score) FROM score WHERE course_id CS101 );这段SQL的逻辑是先在内层算出CS101课程的平均分得到一个单值然后外层查询把这个值当作比较的右值。内层AVG(score)只执行一次外层每一行都拿自己的score去和这个固定值比较。这类子查询相对容易理解也方便排查——你可以单独把内层SELECT拎出来跑一遍看看平均分是否合理。参数层面内层子查询的WHERE条件决定了平均分的统计范围如果在内层漏写course_id平均分就会变成全表平均外层结果立刻失真。还有一点容易被忽略内层返回多行时比较运算符会直接报错。写实验报告时遇到“Subquery returns more than 1 row”这个报错十有八九是内层查询忘记加聚合或忘记加唯一性约束。2.2 相关子查询外层驱动内层逐行执行相关子查询的复杂度上了一个台阶。它的内层查询引用了外层查询的列导致内层不能独立执行——数据库会从外层取一行带着这一行的列值去执行内层查询如此反复直到外层所有行处理完毕。行数越多内层执行的次数就越多。SELECT s.student_name, sc.course_id, sc.score FROM student s JOIN score sc ON s.student_id sc.student_id WHERE sc.score ( SELECT AVG(score) FROM score sc2 WHERE sc2.course_id sc.course_id );这条SQL的意图是找出每位学生在其所选课程中成绩高于该课程平均分的学生记录。关键差异在于内层子查询里的sc2.course_id sc.course_id这个关联条件让内层查询依赖外层当前行的course_id。对于外层查出来的每一条选课记录数据库都要重新算一次对应课程的平均分因此性能敏感度明显高于非相关子查询。从实验报告的角度建议把执行顺序用手写表格的方式呈现第一列写外层当前行的学号和课程号第二列写内层这次查出来的平均分第三列写比较结果。这张表能让阅卷人一眼看出你理解了相关子查询的“逐行执行”特性。调优时优先考虑是否能用窗口函数替代后面章节会展开讲。2.3 三种返回值形态单值、集合、表嵌套查询按返回值可以拆成三种形态写法和使用位置完全不同。第一种是返回单值的子查询配合、、这类比较运算符第二种是返回一列多行的集合配合IN、ANY、ALL使用第三种是返回一张临时表的子查询放在FROM子句后面当作派生表使用。-- 形态一单值配合比较运算符 SELECT name FROM student WHERE student_id (SELECT MAX(student_id) FROM student); -- 形态二集合配合IN SELECT name FROM student WHERE student_id IN (SELECT student_id FROM score WHERE score 60); -- 形态三派生表放在FROM后面 SELECT dept_id, avg_score FROM ( SELECT dept_id, AVG(score) AS avg_score FROM student s JOIN score sc ON s.student_id sc.student_id GROUP BY dept_id ) AS dept_avg WHERE avg_score 75;三种形态里形态三的改造成本最高因为它把分组聚合的结果当作一张新表继续过滤直接写成单层查询会非常别扭。理解这三种形态后再去看后续章节的六种典型写法就有了分类的依据。遇到一条复杂SQL时先判断内层返回的是单值、集合还是表再决定用哪个关键字这是写嵌套查询的核心决策路径。3. 搭一套能复现的练习环境建表、造数、准备实验底稿嵌套查询的学习不能只看不练。实验文档里给的通常是表结构和样例数据但很多人拿到手直接往MySQL里敲发现字段对不上、数据量太小看不出性能差异。我的做法是先搭一套标准的学生-课程-选课模型数据量控制在能看出问题、又不至于等太久的量级。3.1 学生-课程-选课三张表的建表SQLCREATE DATABASE IF NOT EXISTS nested_query_lab DEFAULT CHARACTER SET utf8mb4; USE nested_query_lab; CREATE TABLE student ( student_id INT PRIMARY KEY COMMENT 学号, student_name VARCHAR(50) NOT NULL COMMENT 姓名, dept_id VARCHAR(10) COMMENT 院系编号, enroll_year INT COMMENT 入学年份 ); CREATE TABLE course ( course_id VARCHAR(10) PRIMARY KEY COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) COMMENT 学分, teacher VARCHAR(50) COMMENT 授课教师 ); CREATE TABLE score ( student_id INT NOT NULL, course_id VARCHAR(10) NOT NULL, score DECIMAL(5,1) COMMENT 成绩0-100, term VARCHAR(20) COMMENT 开课学期, PRIMARY KEY (student_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(course_id) ); CREATE INDEX idx_score_course ON score(course_id); CREATE INDEX idx_score_score ON score(score);三张表的选型理由是贴合大多数实验文档的场景同时让嵌套查询有足够的用武之地。score表采用复合主键(student_id, course_id)这符合选课记录的业务语义一个学生一门课只有一条成绩记录。外键约束在实验环境下建议保留它能防止你造数据时不小心造出“选了不存在的课”这种脏数据省下后面排查的时间。索引设计上score表对course_id单独建索引是因为嵌套查询里大量出现“按课程查平均分”“按课程查最高分”这类条件course_id是高频过滤字段。对score字段建索引是因为很多比较型子查询会拿score做范围过滤。如果实验数据量不超过几千行这两个索引对性能的影响看不出来但养成建索引的习惯对后续大表实验有好处。3.2 造一份带边界情况的样本数据样本数据不能只造“正常数据”要有意识地加入边界情况。比如有空成绩的记录吗有缺考的学生吗有从未选课的学生吗这些边界数据是嵌套查询的试金石——很多SQL写法在“全正常数据”下怎么跑都对一遇到NULL就翻车。INSERT INTO student (student_id, student_name, dept_id, enroll_year) VALUES (101, A同学, CS, 2023), (102, B同学, CS, 2023), (103, C同学, EE, 2022), (104, D同学, EE, 2022), (105, E同学, MA, 2023); INSERT INTO course (course_id, course_name, credit, teacher) VALUES (CS101, 数据库原理, 3.0, 某教师), (CS102, 数据结构, 4.0, 某教师), (EE201, 电路分析, 3.5, 某教师), (MA301, 高等数学, 5.0, 某教师); INSERT INTO score (student_id, course_id, score, term) VALUES (101, CS101, 85, 2024春), (101, CS102, 78, 2024春), (102, CS101, 62, 2024春), (103, CS101, 91, 2024春), (104, EE201, 74, 2024秋), (105, CS101, NULL, 2024春), (105, MA301, 88, 2024秋);这份数据里埋了几个有意为之的坑E同学在CS101上成绩为NULL代表缺考或缓考105号学生在MA301有成绩说明他不是完全没上课D同学只选了一门课。这些数据足够支撑后续章节的绝大多数实验点。数据量的问题也要说一句验证语法用这几行就够了但验证“相关子查询会不会慢”这几行完全不够。我的习惯是写一个存储过程循环插入把score表撑到1万行以上再测性能后面避坑章节会讲具体的触发场景。提示MySQL客户端执行建表SQL时注意先执行CREATE DATABASE否则后续语句会报“No database selected”。如果用的是Navicat或DataGrip直接在图形界面里跑完整脚本即可。4. 嵌套查询的单层与多层从IN到EXISTS再到FROM子句的六种写法这一章是实验报告的主体内容。嵌套查询的写法看似很多核心就六种IN子查询、比较运算符子查询、EXISTS子查询、NOT EXISTS子查询、FROM子句派生表、以及多层嵌套的逐层拆解。每一类都有明确的适用场景写错了不会报错但会绕远路。4.1 IN子查询集合归属判断的标准答案IN子查询用于“外层记录的值是否落在内层结果集里”的场景。它语义直白适合过滤条件来自另一张表的去重集合。比如“查询选了CS101课程的学生姓名”。SELECT student_name FROM student WHERE student_id IN ( SELECT student_id FROM score WHERE course_id CS101 );内层查询先执行得到选了CS101的学生ID集合例如{101, 102, 103, 105}然后外层拿每个学生的student_id去这个集合里做匹配。这里有两个容易被忽略的点第一IN子查询会自动去重内层返回[101, 101, 102]和返回[101, 102]效果一样第二如果内层集合里包含NULLIN不会出问题但NOT IN会这个坑在下一章单独讲。参数上需要注意内层SELECT的字段个数IN左侧写了一个列右侧子查询就只能返回一个列返回两列会直接报“Operand should contain 1 column”错误。很多新手在这里犯迷糊以为是数据问题其实是列数不匹配。4.2 比较运算符子查询搭配聚合函数查询极值比较运算符子查询用来表达“比某个统计值高/低/等于”的条件。最常见的写法是配合聚合函数MAX、MIN、AVG查询高于平均分、低于最低分之类的记录。它要求内层必须返回单行单值多一行都报错。SELECT student_id, course_id, score FROM score WHERE course_id CS101 AND score ( SELECT MAX(score) FROM score WHERE course_id CS101 );这条SQL查CS101课程的最高分记录。内层先算出最高分91外层用score 91过滤。注意一个问题如果最高分对应的学生不止一个这条SQL会把所有最高分记录都列出来这符合“最大值”的语义但很多人在实验报告里会写成“查最高分学生”那就需要配合LIMIT 1或者改用其他写法。从执行效率看这个子查询的内层和外层都用了course_id CS101的过滤条件前面建的idx_score_course索引会生效。内层聚合走索引扫描外层走索引查找整体效率在万级数据量下没问题。如果内层不加course_id过滤MAX会变成全表最高分业务语义就错了。4.3 EXISTS子查询存在性判断比IN更扛造EXISTS子查询只关心“内层是否有记录返回”不关心返回什么列。它的执行逻辑和相关子查询一致外层取一行内层判断一次有返回就保留这行。由于不关心具体值内层SELECT后面写什么都不影响结果惯例写SELECT 1。SELECT student_name FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id AND sc.score 60 );这条SQL查“有任意一门课不及格”的学生。执行过程是外层拿到A同学内层带着A同学的student_id去score表找低于60分的记录找到就保留找不到就丢弃然后处理下一个学生。相关子查询的典型特征是内层引用了外层别名s.student_id。EXISTS和IN最大的区别是语义边界IN先算出完整集合再比对EXISTS是逐行判断。在score表数据量大、外层student表数据量小的场景下EXISTS通常比IN表现好因为它可以“提前终止”——内层一旦找到一条记录就不继续扫描。反过来如果内层结果集很小且稳定IN的多次执行开销反而更低。实验报告里如果能写清楚什么时候优先用EXISTS会明显加分。4.4 NOT EXISTS子查询补集场景的可靠选择NOT EXISTS用于“排除存在某种关联记录”的情况。比如“查没选过任何课的学生”用NOT EXISTS比用NOT IN更安全原因还是NULL的坑——NOT IN遇到NULL会返回空结果而NOT EXISTS不会。SELECT student_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id );这条SQL查“从未出现在score表里”的学生。前面造的样本数据里只有D同学的情况比较特殊——D同学选过EE201所以这条SQL在样本数据上应该返回空集。想验证NOT EXISTS的有效性可以再插入一条没有任何选课记录的学生。这里有一个常见的误用用NOT IN (SELECT student_id FROM score)代替NOT EXISTS。当score表的student_id列存在NULL时NOT IN的匹配结果不是“排除不存在的记录”而是整个查询返回空。原理是SQL的三值逻辑NULL与任何值的比较结果是UNKNOWNUNKNOWN取反还是UNKNOWN所以所有行都被过滤掉了。这个坑在下一章会详细展开。4.5 FROM子句派生表先聚合再过滤的标准解法当过滤条件基于聚合结果时子查询放在FROM后面最合适。前面2.3节提到的“按院系统计平均分并过滤平均分大于75的院系”就是这一类。SELECT dept_id, avg_score FROM ( SELECT s.dept_id, AVG(sc.score) AS avg_score FROM student s JOIN score sc ON s.student_id sc.student_id GROUP BY s.dept_id ) AS dept_avg WHERE avg_score 75;执行逻辑是先执行内层把按院系分组后的平均分算出来形成一张派生表dept_avg再对这张表做WHERE过滤。注意MySQL要求派生表必须起别名AS dept_avg不写会报错。PostgreSQL允许不写但实验环境以MySQL居多养成写别名的习惯最稳妥。派生表的优势是逻辑清晰先算好中间结果再基于中间结果做二次筛选。劣势是MySQL 5.7以下版本对派生表的优化不充分会物化成临时表导致外层过滤无法下推。如果实验环境是MySQL 8.0以上优化器会做派生表合并性能问题小很多。判断方法很简单对这条SQL执行EXPLAIN看派生表这一行Using temporary的标记。4.6 多层嵌套从内往外拆解三步法三层以上嵌套的SQL新手容易一上来就写完整条然后报错后完全不知道错在哪一层。我的习惯是三步拆解第一步单独运行最内层子查询确认返回结果第二步把内层结果当作固定值运行第二层查询第三步逐层往外套直到完整SQL跑通。这套方法能快速定位语法错误和逻辑错误配合SQL注释能大大提升实验报告的完成质量。-- 查询选过CS101且成绩高于CS102最高分的学生姓名 -- 第一步单独查CS102最高分 -- SELECT MAX(score) FROM score WHERE course_id CS102; SELECT s.student_name FROM student s WHERE s.student_id IN ( SELECT sc.student_id FROM score sc WHERE sc.course_id CS101 AND sc.score ( SELECT MAX(score) FROM score WHERE course_id CS102 ) );这段SQL是两层IN加一层比较运算符子查询一共三层。先算出CS102的最高分内层最深处然后在外层用这个值过滤CS101的成绩最后在student表里匹配学生姓名。每一步单独执行的结果都应该是可预期的第一层算出78分CS102最高分第二层选出CS101成绩大于78的记录第三层输出对应的学生姓名。在实验报告里写下“我先把最内层跑了一遍结果是78然后带入第二层确认筛选出3条记录”这样的调试过程比直接贴最终SQL有价值得多。阅卷人看到的不只是语法而是调试能力。5. 避坑指南嵌套查询最容易翻车的五个场景嵌套查询的坑不会写在语法文档里都是在实打实写SQL时踩出来的。这一章列五个最高频的翻车现场每条都按“现象→原因→解决”的顺序来方便你直接对照排查。5.1 NOT IN遇到NULL整个查询返回空集现象SELECT ... WHERE student_id NOT IN (SELECT student_id FROM score)明明有学生没选课查询结果却一行都不返回。把子查询单独跑一遍发现结果里确实有没选课的学生但合并起来就是查不出来。原因SQL的三值逻辑。如果score表的student_id列存在NULLNOT IN的语义变成“不等于集合中的每一个值”。NULL与任何值比较的结果是UNKNOWNNOT IN(NULL)的结果也是UNKNOWNUNKNOWN在WHERE条件里视为不成立所以所有行都被排除。解决优先用NOT EXISTS替换。或者先排除NULLWHERE student_id NOT IN (SELECT student_id FROM score WHERE student_id IS NOT NULL)。两种方案里NOT EXISTS写法更通用不仅规避NULL还能利用相关子查询的提前终止优势。5.2 相关子查询性能突然恶化现象同一条SQL在小数据量下跑得飞快数据量涨到几万行后突然慢了几十倍。EXPLAIN看到内层子查询的type是ALL意味着每次外层取一行内层都要全表扫描一次。原因相关子查询是外层行数乘以内层扫描开销。外层1万行内层全表扫1万次就是亿级扫描。这是相关子查询的固有代价和数据量强相关。MySQL优化器对相关子查询的改写能力有限往往不会自动把它转成JOIN。解决改写为JOIN GROUP BY或者用窗口函数消除逐行计算。以“查成绩高于本课程平均分”为例可以先用派生表或窗口函数把平均分算好再JOIN回来避免逐行执行内层查询。-- 窗口函数写法避免相关子查询逐行执行 SELECT student_id, course_id, score FROM ( SELECT student_id, course_id, score, AVG(score) OVER (PARTITION BY course_id) AS avg_score FROM score ) t WHERE score avg_score;这段SQL先用窗口函数按course_id分区算平均值每个分区计算一次然后外层过滤。相比相关子查询它的扫描次数从“外层行数内层全表”降为“单次扫描一次排序”数据量越大优势越明显。5.3 子查询里ORDER BY不生效现象内层写了ORDER BY score DESC LIMIT 3子查询单独执行的结果顺序正确但外层查询的结果顺序乱了或者子查询在MySQL里直接报错。原因MySQL对子查询的优化会忽略部分子查询的ORDER BY尤其是IN子查询。WHERE id IN (SELECT id FROM t ORDER BY score DESC LIMIT 3)在MySQL 5.7里ORDER BY可能被优化器直接丢弃LIMIT和ORDER BY的组合有时只在单独执行时有效。此外子查询的排序结果对外层本身没有语义意义外层有自己的ORDER BY才会最终排序。解决如果子查询依赖LIMIT取前几条用派生表包一层让最内层的SELECT作为派生表被物化后再取数。MySQL 8.0对派生表的优化更激进但也可能改变执行顺序必要时候先在派生表上强制物化。5.4 列数不匹配与聚合函数位置错误现象报错信息是“Operand should contain 1 column”或者“Column score cannot be used in the SELECT list because it is not contained in either an aggregate function or the GROUP BY clause”。原因第一种情况是IN左侧写了两个列右侧子查询只返回一个列第二种是GROUP BY只按course_id分组但SELECT里同时选了student_id或score非聚合列没有出现在GROUP BY里。解决IN的子查询SELECT后面只保留和左侧列对应的单个字段。聚合查询里非聚合列必须同时出现在GROUP BY中或者改成子查询方式把细节字段排除在外。MySQL的ONLY_FULL_GROUP_BY模式会严格执行这个规则关闭模式虽然不报错但结果不可预期不建议关。5.5 多层嵌套的可读性崩溃现象三层以上的SQL写完自己回头都看不懂改一个条件要全文搜索找半天。放了两周再回头改完全不知道当初为什么这样写。这是嵌套查询的隐性坑——语法没错、结果也对但维护成本极高。原因没有给表起有意义的别名没有加注释嵌套层数超过三到四层时人的短期记忆已经无法同时追踪所有关联条件。解决固定三招。第一每张表加语义化别名比如student用s、score用sc、course用c关联条件一眼能对上。第二在最内层和每一层WHERE上写注释说明这一层的业务语义。第三一旦嵌套超过三层优先考虑拆成CTE公共表表达式大幅提升可读性。WITH cs101_high AS ( SELECT student_id FROM score WHERE course_id CS101 AND score 80 ), cs102_max AS ( SELECT MAX(score) AS max_score FROM score WHERE course_id CS102 ) SELECT s.student_name FROM student s WHERE s.student_id IN (SELECT student_id FROM cs101_high) AND s.student_id NOT IN (SELECT student_id FROM score WHERE score (SELECT max_score FROM cs102_max));这段CTE写法把三层嵌套拆成两个独立命名的中间集合再在外层做最终过滤。MySQL 8.0、PostgreSQL、SQL Server都支持CTE实验环境的兼容性没问题。CTE比派生表更直观的优势是每个中间结果都有名字可以在报告里单独解释每一步的用途。6. 嵌套查询会写还要会调EXPLAIN验证与改写取舍嵌套查询的实验报告写到“能跑通”只是及格线能讲清楚“为什么这样写、性能怎么样、有没有更优写法”才是加分项。我的习惯是每条嵌套查询都跑一遍EXPLAIN记录下内层和外层的访问类型再决定是否值得改写。EXPLAIN输出里最关键的是type列从好到差依次是system、const、eq_ref、ref、range、index、ALL。嵌套查询的内层如果出现ALL意味着全表扫描数据量小没事数据量大就要警觉。前面第2章那条“查高于本课程平均分”的相关子查询在1万行score数据下内层type是ALL外层是ALL整体就是1万次全表扫描。改成窗口函数写法后再跑EXPLAINtype变成range或index扫描次数降了一个量级。改写取舍我一般遵循一条原则能改写为JOIN或窗口函数时优先改写嵌套层数在两层以内、数据量小或内层有索引支撑时保留嵌套写法反而更直观。业务系统里见过太多为了炫技写五层嵌套的SQL性能差且没人能维护。实验报告里可以加一小节对比同一需求分别用相关子查询、派生表JOIN、窗口函数三种方案实现记录执行时间。这样做能证明你不只是会写语法而是真的理解每种写法的代价。收尾再说一个习惯写嵌套查询时永远先问自己一句“内层结果集大概有多少行”。如果是几十行IN和EXISTS区别不大如果是几十万行写之前就必须考虑索引和改写方案。做实验时吃过这个亏——拿一套干净的小数据集验证完就交作业结果业务环境数据量上来立刻超时数据库的连接都被拖垮。那次之后每条嵌套查询都会用EXPLAIN过一遍这个习惯一直到今天都没断过。希望这篇笔记能帮你少走一半弯路嵌套查询写起来更快坑位都提前避开。本文还有配套的精品资源点击获取 
长河君 · 2026/10/17 15:46:44 · 阅读 1.2k 数据库嵌套查询从原理到调优：六种写法与避坑指南
简介数据库实验5嵌套查询.doc是一份面向数据库初学者的完整实验报告围绕CPXS数据库演示统计查询、连接查询与嵌套查询的典型操作重点帮助学生掌握SELECT语句、COUNT/SUM/MAX/MIN等统计函数以及GROUP BY、HAVING、BETWEEN、LIKE、IN、子查询等常用语法适合数据库原理课程同步练习、上机实验和考前复习。压缩包内仅有1个doc文档大小642KB内容包含实验目的、实验步骤、多条可运行的SQL语句示例、实践结论及相关知识点整理可直接对照数据库环境验证运行结果。这些示例覆盖求客户数目、库存量总和、最高单价、至少订购两种产品的客户编号以及查询上海客户订购数量大于200套的联系方式、与“美美”同城客户、未被订购产品等典型场景能够帮助读者理清分组统计、多表连接和嵌套子查询的组合逻辑。目前已有930人学习下载对于需要撰写数据库实验报告或强化SQL查询能力的初学者来说是一份结构清晰、即拿即用的参考资料。1. 嵌套查询从一条报表SQL说起如果你做过业务报表大概率遇到过这类需求查出“选了某门课并且成绩高于该课平均分”的学生名单。第一次写的人会本能地先把平均分查出来再拿着这个数去写第二条查询。两条SQL能跑通但换个班级、换门课就要改代码维护成本很高。数据库实验5嵌套查询这份实验核心就是让你摆脱这种“先查一次、再查一次”的笨办法把两条SQL合成一条让数据库自己完成中间结果的传递。嵌套查询也叫子查询本质是“一条SELECT语句的查询结果作为另一条SELECT语句的输入条件或数据源”。它解决的最大痛点是查询条件本身依赖另一个查询结果而这个结果无法用简单的JOIN直接表达或者用JOIN写出来逻辑特别绕。适合的读者有两类一类是正在做数据库课程实验、需要在Word里写实验报告的同学另一类是工作中写SQL常遇到“多层条件过滤”但只会拆成多条语句的开发者。这篇笔记会把嵌套查询从执行原理、建表环境、六种典型写法到性能坑位一次讲透。2. 嵌套查询的执行顺序与基本形态先让原理立住再动手写嵌套查询的第一课不是背语法而是建立执行顺序的直觉。很多人在写嵌套查询时翻车不是因为不会写关键字而是不知道数据库到底先跑哪一段。搞清楚执行顺序你才能在脑子里面“预编译”每条SQL而不是靠反复执行去试错。2.1 非相关子查询先算内层再算外层非相关子查询是最简单也最常用的一种形态内层子查询不依赖外层查询的任何列可以独立运行。它的执行顺序很直观——先执行内层查询拿到结果集再把这个结果集当作外层查询的过滤条件来用。SELECT student_name, course_id, score FROM score WHERE course_id CS101 AND score ( SELECT AVG(score) FROM score WHERE course_id CS101 );这段SQL的逻辑是先在内层算出CS101课程的平均分得到一个单值然后外层查询把这个值当作比较的右值。内层AVG(score)只执行一次外层每一行都拿自己的score去和这个固定值比较。这类子查询相对容易理解也方便排查——你可以单独把内层SELECT拎出来跑一遍看看平均分是否合理。参数层面内层子查询的WHERE条件决定了平均分的统计范围如果在内层漏写course_id平均分就会变成全表平均外层结果立刻失真。还有一点容易被忽略内层返回多行时比较运算符会直接报错。写实验报告时遇到“Subquery returns more than 1 row”这个报错十有八九是内层查询忘记加聚合或忘记加唯一性约束。2.2 相关子查询外层驱动内层逐行执行相关子查询的复杂度上了一个台阶。它的内层查询引用了外层查询的列导致内层不能独立执行——数据库会从外层取一行带着这一行的列值去执行内层查询如此反复直到外层所有行处理完毕。行数越多内层执行的次数就越多。SELECT s.student_name, sc.course_id, sc.score FROM student s JOIN score sc ON s.student_id sc.student_id WHERE sc.score ( SELECT AVG(score) FROM score sc2 WHERE sc2.course_id sc.course_id );这条SQL的意图是找出每位学生在其所选课程中成绩高于该课程平均分的学生记录。关键差异在于内层子查询里的sc2.course_id sc.course_id这个关联条件让内层查询依赖外层当前行的course_id。对于外层查出来的每一条选课记录数据库都要重新算一次对应课程的平均分因此性能敏感度明显高于非相关子查询。从实验报告的角度建议把执行顺序用手写表格的方式呈现第一列写外层当前行的学号和课程号第二列写内层这次查出来的平均分第三列写比较结果。这张表能让阅卷人一眼看出你理解了相关子查询的“逐行执行”特性。调优时优先考虑是否能用窗口函数替代后面章节会展开讲。2.3 三种返回值形态单值、集合、表嵌套查询按返回值可以拆成三种形态写法和使用位置完全不同。第一种是返回单值的子查询配合、、这类比较运算符第二种是返回一列多行的集合配合IN、ANY、ALL使用第三种是返回一张临时表的子查询放在FROM子句后面当作派生表使用。-- 形态一单值配合比较运算符 SELECT name FROM student WHERE student_id (SELECT MAX(student_id) FROM student); -- 形态二集合配合IN SELECT name FROM student WHERE student_id IN (SELECT student_id FROM score WHERE score 60); -- 形态三派生表放在FROM后面 SELECT dept_id, avg_score FROM ( SELECT dept_id, AVG(score) AS avg_score FROM student s JOIN score sc ON s.student_id sc.student_id GROUP BY dept_id ) AS dept_avg WHERE avg_score 75;三种形态里形态三的改造成本最高因为它把分组聚合的结果当作一张新表继续过滤直接写成单层查询会非常别扭。理解这三种形态后再去看后续章节的六种典型写法就有了分类的依据。遇到一条复杂SQL时先判断内层返回的是单值、集合还是表再决定用哪个关键字这是写嵌套查询的核心决策路径。3. 搭一套能复现的练习环境建表、造数、准备实验底稿嵌套查询的学习不能只看不练。实验文档里给的通常是表结构和样例数据但很多人拿到手直接往MySQL里敲发现字段对不上、数据量太小看不出性能差异。我的做法是先搭一套标准的学生-课程-选课模型数据量控制在能看出问题、又不至于等太久的量级。3.1 学生-课程-选课三张表的建表SQLCREATE DATABASE IF NOT EXISTS nested_query_lab DEFAULT CHARACTER SET utf8mb4; USE nested_query_lab; CREATE TABLE student ( student_id INT PRIMARY KEY COMMENT 学号, student_name VARCHAR(50) NOT NULL COMMENT 姓名, dept_id VARCHAR(10) COMMENT 院系编号, enroll_year INT COMMENT 入学年份 ); CREATE TABLE course ( course_id VARCHAR(10) PRIMARY KEY COMMENT 课程编号, course_name VARCHAR(100) NOT NULL COMMENT 课程名称, credit DECIMAL(3,1) COMMENT 学分, teacher VARCHAR(50) COMMENT 授课教师 ); CREATE TABLE score ( student_id INT NOT NULL, course_id VARCHAR(10) NOT NULL, score DECIMAL(5,1) COMMENT 成绩0-100, term VARCHAR(20) COMMENT 开课学期, PRIMARY KEY (student_id, course_id), CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(student_id), CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(course_id) ); CREATE INDEX idx_score_course ON score(course_id); CREATE INDEX idx_score_score ON score(score);三张表的选型理由是贴合大多数实验文档的场景同时让嵌套查询有足够的用武之地。score表采用复合主键(student_id, course_id)这符合选课记录的业务语义一个学生一门课只有一条成绩记录。外键约束在实验环境下建议保留它能防止你造数据时不小心造出“选了不存在的课”这种脏数据省下后面排查的时间。索引设计上score表对course_id单独建索引是因为嵌套查询里大量出现“按课程查平均分”“按课程查最高分”这类条件course_id是高频过滤字段。对score字段建索引是因为很多比较型子查询会拿score做范围过滤。如果实验数据量不超过几千行这两个索引对性能的影响看不出来但养成建索引的习惯对后续大表实验有好处。3.2 造一份带边界情况的样本数据样本数据不能只造“正常数据”要有意识地加入边界情况。比如有空成绩的记录吗有缺考的学生吗有从未选课的学生吗这些边界数据是嵌套查询的试金石——很多SQL写法在“全正常数据”下怎么跑都对一遇到NULL就翻车。INSERT INTO student (student_id, student_name, dept_id, enroll_year) VALUES (101, A同学, CS, 2023), (102, B同学, CS, 2023), (103, C同学, EE, 2022), (104, D同学, EE, 2022), (105, E同学, MA, 2023); INSERT INTO course (course_id, course_name, credit, teacher) VALUES (CS101, 数据库原理, 3.0, 某教师), (CS102, 数据结构, 4.0, 某教师), (EE201, 电路分析, 3.5, 某教师), (MA301, 高等数学, 5.0, 某教师); INSERT INTO score (student_id, course_id, score, term) VALUES (101, CS101, 85, 2024春), (101, CS102, 78, 2024春), (102, CS101, 62, 2024春), (103, CS101, 91, 2024春), (104, EE201, 74, 2024秋), (105, CS101, NULL, 2024春), (105, MA301, 88, 2024秋);这份数据里埋了几个有意为之的坑E同学在CS101上成绩为NULL代表缺考或缓考105号学生在MA301有成绩说明他不是完全没上课D同学只选了一门课。这些数据足够支撑后续章节的绝大多数实验点。数据量的问题也要说一句验证语法用这几行就够了但验证“相关子查询会不会慢”这几行完全不够。我的习惯是写一个存储过程循环插入把score表撑到1万行以上再测性能后面避坑章节会讲具体的触发场景。提示MySQL客户端执行建表SQL时注意先执行CREATE DATABASE否则后续语句会报“No database selected”。如果用的是Navicat或DataGrip直接在图形界面里跑完整脚本即可。4. 嵌套查询的单层与多层从IN到EXISTS再到FROM子句的六种写法这一章是实验报告的主体内容。嵌套查询的写法看似很多核心就六种IN子查询、比较运算符子查询、EXISTS子查询、NOT EXISTS子查询、FROM子句派生表、以及多层嵌套的逐层拆解。每一类都有明确的适用场景写错了不会报错但会绕远路。4.1 IN子查询集合归属判断的标准答案IN子查询用于“外层记录的值是否落在内层结果集里”的场景。它语义直白适合过滤条件来自另一张表的去重集合。比如“查询选了CS101课程的学生姓名”。SELECT student_name FROM student WHERE student_id IN ( SELECT student_id FROM score WHERE course_id CS101 );内层查询先执行得到选了CS101的学生ID集合例如{101, 102, 103, 105}然后外层拿每个学生的student_id去这个集合里做匹配。这里有两个容易被忽略的点第一IN子查询会自动去重内层返回[101, 101, 102]和返回[101, 102]效果一样第二如果内层集合里包含NULLIN不会出问题但NOT IN会这个坑在下一章单独讲。参数上需要注意内层SELECT的字段个数IN左侧写了一个列右侧子查询就只能返回一个列返回两列会直接报“Operand should contain 1 column”错误。很多新手在这里犯迷糊以为是数据问题其实是列数不匹配。4.2 比较运算符子查询搭配聚合函数查询极值比较运算符子查询用来表达“比某个统计值高/低/等于”的条件。最常见的写法是配合聚合函数MAX、MIN、AVG查询高于平均分、低于最低分之类的记录。它要求内层必须返回单行单值多一行都报错。SELECT student_id, course_id, score FROM score WHERE course_id CS101 AND score ( SELECT MAX(score) FROM score WHERE course_id CS101 );这条SQL查CS101课程的最高分记录。内层先算出最高分91外层用score 91过滤。注意一个问题如果最高分对应的学生不止一个这条SQL会把所有最高分记录都列出来这符合“最大值”的语义但很多人在实验报告里会写成“查最高分学生”那就需要配合LIMIT 1或者改用其他写法。从执行效率看这个子查询的内层和外层都用了course_id CS101的过滤条件前面建的idx_score_course索引会生效。内层聚合走索引扫描外层走索引查找整体效率在万级数据量下没问题。如果内层不加course_id过滤MAX会变成全表最高分业务语义就错了。4.3 EXISTS子查询存在性判断比IN更扛造EXISTS子查询只关心“内层是否有记录返回”不关心返回什么列。它的执行逻辑和相关子查询一致外层取一行内层判断一次有返回就保留这行。由于不关心具体值内层SELECT后面写什么都不影响结果惯例写SELECT 1。SELECT student_name FROM student s WHERE EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id AND sc.score 60 );这条SQL查“有任意一门课不及格”的学生。执行过程是外层拿到A同学内层带着A同学的student_id去score表找低于60分的记录找到就保留找不到就丢弃然后处理下一个学生。相关子查询的典型特征是内层引用了外层别名s.student_id。EXISTS和IN最大的区别是语义边界IN先算出完整集合再比对EXISTS是逐行判断。在score表数据量大、外层student表数据量小的场景下EXISTS通常比IN表现好因为它可以“提前终止”——内层一旦找到一条记录就不继续扫描。反过来如果内层结果集很小且稳定IN的多次执行开销反而更低。实验报告里如果能写清楚什么时候优先用EXISTS会明显加分。4.4 NOT EXISTS子查询补集场景的可靠选择NOT EXISTS用于“排除存在某种关联记录”的情况。比如“查没选过任何课的学生”用NOT EXISTS比用NOT IN更安全原因还是NULL的坑——NOT IN遇到NULL会返回空结果而NOT EXISTS不会。SELECT student_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.student_id s.student_id );这条SQL查“从未出现在score表里”的学生。前面造的样本数据里只有D同学的情况比较特殊——D同学选过EE201所以这条SQL在样本数据上应该返回空集。想验证NOT EXISTS的有效性可以再插入一条没有任何选课记录的学生。这里有一个常见的误用用NOT IN (SELECT student_id FROM score)代替NOT EXISTS。当score表的student_id列存在NULL时NOT IN的匹配结果不是“排除不存在的记录”而是整个查询返回空。原理是SQL的三值逻辑NULL与任何值的比较结果是UNKNOWNUNKNOWN取反还是UNKNOWN所以所有行都被过滤掉了。这个坑在下一章会详细展开。4.5 FROM子句派生表先聚合再过滤的标准解法当过滤条件基于聚合结果时子查询放在FROM后面最合适。前面2.3节提到的“按院系统计平均分并过滤平均分大于75的院系”就是这一类。SELECT dept_id, avg_score FROM ( SELECT s.dept_id, AVG(sc.score) AS avg_score FROM student s JOIN score sc ON s.student_id sc.student_id GROUP BY s.dept_id ) AS dept_avg WHERE avg_score 75;执行逻辑是先执行内层把按院系分组后的平均分算出来形成一张派生表dept_avg再对这张表做WHERE过滤。注意MySQL要求派生表必须起别名AS dept_avg不写会报错。PostgreSQL允许不写但实验环境以MySQL居多养成写别名的习惯最稳妥。派生表的优势是逻辑清晰先算好中间结果再基于中间结果做二次筛选。劣势是MySQL 5.7以下版本对派生表的优化不充分会物化成临时表导致外层过滤无法下推。如果实验环境是MySQL 8.0以上优化器会做派生表合并性能问题小很多。判断方法很简单对这条SQL执行EXPLAIN看派生表这一行Using temporary的标记。4.6 多层嵌套从内往外拆解三步法三层以上嵌套的SQL新手容易一上来就写完整条然后报错后完全不知道错在哪一层。我的习惯是三步拆解第一步单独运行最内层子查询确认返回结果第二步把内层结果当作固定值运行第二层查询第三步逐层往外套直到完整SQL跑通。这套方法能快速定位语法错误和逻辑错误配合SQL注释能大大提升实验报告的完成质量。-- 查询选过CS101且成绩高于CS102最高分的学生姓名 -- 第一步单独查CS102最高分 -- SELECT MAX(score) FROM score WHERE course_id CS102; SELECT s.student_name FROM student s WHERE s.student_id IN ( SELECT sc.student_id FROM score sc WHERE sc.course_id CS101 AND sc.score ( SELECT MAX(score) FROM score WHERE course_id CS102 ) );这段SQL是两层IN加一层比较运算符子查询一共三层。先算出CS102的最高分内层最深处然后在外层用这个值过滤CS101的成绩最后在student表里匹配学生姓名。每一步单独执行的结果都应该是可预期的第一层算出78分CS102最高分第二层选出CS101成绩大于78的记录第三层输出对应的学生姓名。在实验报告里写下“我先把最内层跑了一遍结果是78然后带入第二层确认筛选出3条记录”这样的调试过程比直接贴最终SQL有价值得多。阅卷人看到的不只是语法而是调试能力。5. 避坑指南嵌套查询最容易翻车的五个场景嵌套查询的坑不会写在语法文档里都是在实打实写SQL时踩出来的。这一章列五个最高频的翻车现场每条都按“现象→原因→解决”的顺序来方便你直接对照排查。5.1 NOT IN遇到NULL整个查询返回空集现象SELECT ... WHERE student_id NOT IN (SELECT student_id FROM score)明明有学生没选课查询结果却一行都不返回。把子查询单独跑一遍发现结果里确实有没选课的学生但合并起来就是查不出来。原因SQL的三值逻辑。如果score表的student_id列存在NULLNOT IN的语义变成“不等于集合中的每一个值”。NULL与任何值比较的结果是UNKNOWNNOT IN(NULL)的结果也是UNKNOWNUNKNOWN在WHERE条件里视为不成立所以所有行都被排除。解决优先用NOT EXISTS替换。或者先排除NULLWHERE student_id NOT IN (SELECT student_id FROM score WHERE student_id IS NOT NULL)。两种方案里NOT EXISTS写法更通用不仅规避NULL还能利用相关子查询的提前终止优势。5.2 相关子查询性能突然恶化现象同一条SQL在小数据量下跑得飞快数据量涨到几万行后突然慢了几十倍。EXPLAIN看到内层子查询的type是ALL意味着每次外层取一行内层都要全表扫描一次。原因相关子查询是外层行数乘以内层扫描开销。外层1万行内层全表扫1万次就是亿级扫描。这是相关子查询的固有代价和数据量强相关。MySQL优化器对相关子查询的改写能力有限往往不会自动把它转成JOIN。解决改写为JOIN GROUP BY或者用窗口函数消除逐行计算。以“查成绩高于本课程平均分”为例可以先用派生表或窗口函数把平均分算好再JOIN回来避免逐行执行内层查询。-- 窗口函数写法避免相关子查询逐行执行 SELECT student_id, course_id, score FROM ( SELECT student_id, course_id, score, AVG(score) OVER (PARTITION BY course_id) AS avg_score FROM score ) t WHERE score avg_score;这段SQL先用窗口函数按course_id分区算平均值每个分区计算一次然后外层过滤。相比相关子查询它的扫描次数从“外层行数内层全表”降为“单次扫描一次排序”数据量越大优势越明显。5.3 子查询里ORDER BY不生效现象内层写了ORDER BY score DESC LIMIT 3子查询单独执行的结果顺序正确但外层查询的结果顺序乱了或者子查询在MySQL里直接报错。原因MySQL对子查询的优化会忽略部分子查询的ORDER BY尤其是IN子查询。WHERE id IN (SELECT id FROM t ORDER BY score DESC LIMIT 3)在MySQL 5.7里ORDER BY可能被优化器直接丢弃LIMIT和ORDER BY的组合有时只在单独执行时有效。此外子查询的排序结果对外层本身没有语义意义外层有自己的ORDER BY才会最终排序。解决如果子查询依赖LIMIT取前几条用派生表包一层让最内层的SELECT作为派生表被物化后再取数。MySQL 8.0对派生表的优化更激进但也可能改变执行顺序必要时候先在派生表上强制物化。5.4 列数不匹配与聚合函数位置错误现象报错信息是“Operand should contain 1 column”或者“Column score cannot be used in the SELECT list because it is not contained in either an aggregate function or the GROUP BY clause”。原因第一种情况是IN左侧写了两个列右侧子查询只返回一个列第二种是GROUP BY只按course_id分组但SELECT里同时选了student_id或score非聚合列没有出现在GROUP BY里。解决IN的子查询SELECT后面只保留和左侧列对应的单个字段。聚合查询里非聚合列必须同时出现在GROUP BY中或者改成子查询方式把细节字段排除在外。MySQL的ONLY_FULL_GROUP_BY模式会严格执行这个规则关闭模式虽然不报错但结果不可预期不建议关。5.5 多层嵌套的可读性崩溃现象三层以上的SQL写完自己回头都看不懂改一个条件要全文搜索找半天。放了两周再回头改完全不知道当初为什么这样写。这是嵌套查询的隐性坑——语法没错、结果也对但维护成本极高。原因没有给表起有意义的别名没有加注释嵌套层数超过三到四层时人的短期记忆已经无法同时追踪所有关联条件。解决固定三招。第一每张表加语义化别名比如student用s、score用sc、course用c关联条件一眼能对上。第二在最内层和每一层WHERE上写注释说明这一层的业务语义。第三一旦嵌套超过三层优先考虑拆成CTE公共表表达式大幅提升可读性。WITH cs101_high AS ( SELECT student_id FROM score WHERE course_id CS101 AND score 80 ), cs102_max AS ( SELECT MAX(score) AS max_score FROM score WHERE course_id CS102 ) SELECT s.student_name FROM student s WHERE s.student_id IN (SELECT student_id FROM cs101_high) AND s.student_id NOT IN (SELECT student_id FROM score WHERE score (SELECT max_score FROM cs102_max));这段CTE写法把三层嵌套拆成两个独立命名的中间集合再在外层做最终过滤。MySQL 8.0、PostgreSQL、SQL Server都支持CTE实验环境的兼容性没问题。CTE比派生表更直观的优势是每个中间结果都有名字可以在报告里单独解释每一步的用途。6. 嵌套查询会写还要会调EXPLAIN验证与改写取舍嵌套查询的实验报告写到“能跑通”只是及格线能讲清楚“为什么这样写、性能怎么样、有没有更优写法”才是加分项。我的习惯是每条嵌套查询都跑一遍EXPLAIN记录下内层和外层的访问类型再决定是否值得改写。EXPLAIN输出里最关键的是type列从好到差依次是system、const、eq_ref、ref、range、index、ALL。嵌套查询的内层如果出现ALL意味着全表扫描数据量小没事数据量大就要警觉。前面第2章那条“查高于本课程平均分”的相关子查询在1万行score数据下内层type是ALL外层是ALL整体就是1万次全表扫描。改成窗口函数写法后再跑EXPLAINtype变成range或index扫描次数降了一个量级。改写取舍我一般遵循一条原则能改写为JOIN或窗口函数时优先改写嵌套层数在两层以内、数据量小或内层有索引支撑时保留嵌套写法反而更直观。业务系统里见过太多为了炫技写五层嵌套的SQL性能差且没人能维护。实验报告里可以加一小节对比同一需求分别用相关子查询、派生表JOIN、窗口函数三种方案实现记录执行时间。这样做能证明你不只是会写语法而是真的理解每种写法的代价。收尾再说一个习惯写嵌套查询时永远先问自己一句“内层结果集大概有多少行”。如果是几十行IN和EXISTS区别不大如果是几十万行写之前就必须考虑索引和改写方案。做实验时吃过这个亏——拿一套干净的小数据集验证完就交作业结果业务环境数据量上来立刻超时数据库的连接都被拖垮。那次之后每条嵌套查询都会用EXPLAIN过一遍这个习惯一直到今天都没断过。希望这篇笔记能帮你少走一半弯路嵌套查询写起来更快坑位都提前避开。本文还有配套的精品资源点击获取