数据库实验5嵌套查询：SELECT里套SELECT的语法、选型与统计实战

简介这份数据库实验报告面向高校计算机及相关专业学生聚焦数据统计查询与嵌套查询的实操训练帮助读者掌握SELECT语句、统计函数、连接查询及子查询的综合运用。资源包内含1个doc文档大小约642KB内容围绕CPXS数据库展开涵盖COUNT、SUM、MAX、MIN等统计函数的使用INNER JOIN、LEFT JOIN、RIGHT JOIN等连接查询的语法以及子查询、派生表等嵌套查询操作符与谓词的实践。文档以实验目的、实验内容、实践结论、相关知识点和实验思考为脉络通过十余道典型查询题目如统计客户数目、求库存总和、查询上海客户订购数量大于200套的记录、比较产品单价等帮助读者理解多表关联与条件筛选的解题思路。目前已有930人学习下载适合正在学习数据库课程或准备实验考核的学生参考也可作为复习SQL查询语法的练习材料。1. 数据库实验5嵌套查询从一条 SELECT 里再塞一条 SELECT 说起很多人第一次写嵌套查询是因为一条 SQL 死活查不出想要的结果。比如要查“选修了数据库这门课的学生”用连接查询能写但写完之后发现去重、聚合、条件叠加全搅在一起越写越乱。这时候把一条 SELECT 塞进另一条 SELECT 的 WHERE 或 FROM 里问题突然就清晰了——这就是嵌套查询也叫子查询。数据库实验5嵌套查询这个标题核心就三件事SELECT 里套 SELECT 的语法结构、子查询和连接查询的选型边界、以及统计查询场景下怎么用嵌套把聚合结果当条件用。适合正在做数据库课程实验的学生也适合写了几年 CRUD 但一遇到多层聚合就绕不清楚的开发者。下面按“先能跑通、再知道为什么、最后知道什么时候别用”的顺序拆开讲。2. 嵌套查询的三种形态WHERE、FROM、SELECT 里各放什么2.1 WHERE 子句子查询最常用也最容易踩空值坑WHERE 里嵌子查询是最常见的形态分两类标量子查询返回单个值和 IN/EXISTS 子查询返回集合。标量子查询的例子——查成绩高于平均分的学生-- 子查询返回单个值外层用比较运算符 SELECT student_id, course_id, score FROM score WHERE score ( SELECT AVG(score) FROM score WHERE course_id CS101 ) AND course_id CS101;逻辑说明内层先算出 CS101 这门课的平均分外层拿每行分数去比。注意内层和外层都限定了 course_id如果不加平均分是全课程的平均分结果就不对了。参数说明AVG 返回 NULL 时比如该课程没有任何成绩记录外层score NULL永远为 UNKNOWN查不出任何行。稳妥做法是加AND (SELECT AVG(score) ...) IS NOT NULL或者用 COALESCE 兜底。IN 子查询——查选修了“数据库”课程的学生SELECT student_id, student_name FROM student WHERE student_id IN ( SELECT student_id FROM score WHERE course_id ( SELECT course_id FROM course WHERE course_name 数据库 ) );这是两层嵌套最内层拿到课程编号中间层拿到选修该课的学生编号集合外层拿学生信息。IN 的坑在于子查询结果里如果有 NULLIN (NULL)不会匹配任何行整个查询可能返回空集。用 EXISTS 可以规避这个问题。EXISTS 子查询——同样查选修数据库的学生SELECT s.student_id, s.student_name FROM student s WHERE EXISTS ( SELECT 1 FROM score sc JOIN course c ON sc.course_id c.course_id WHERE c.course_name 数据库 AND sc.student_id s.student_id );EXISTS 只关心子查询有没有返回行不关心返回什么所以 SELECT 1 就够了。它的执行逻辑是对 student 每一行拿 student_id 去子查询里试命中就保留。关联子查询的特点是内层引用了外层的列s.student_id这也是它和 IN 子查询最大的区别。提示IN 适合子查询结果集小且无 NULL 的场景EXISTS 适合外层表大、子查询能走索引的场景。两者在多数数据库里优化器会做等价改写但写法影响可读性。2.2 FROM 子句子查询把聚合结果当临时表用FROM 里嵌子查询本质是构造一个派生表。统计查询里特别常见——先分组算聚合再对聚合结果做二次筛选。查每门课平均分只要平均分大于 80 的-- 派生表必须有别名MySQL 里没别名直接报错 SELECT t.course_id, t.avg_score FROM ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) AS t WHERE t.avg_score 80;逻辑说明内层先按课程分组算平均分外层把结果当一张表来查。为什么不能直接WHERE AVG(score) 80因为 WHERE 在 GROUP BY 之前执行聚合函数不能出现在 WHERE 里。必须先用子查询算出聚合值外层再过滤。参数说明派生表别名AS t在 MySQL、PostgreSQL 里是强制的Oracle 里不允许加 AS 关键字直接写别名。这是跨数据库移植时的高频翻车点。再进一步——查平均分最高的那门课SELECT t.course_id, t.avg_score FROM ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ) AS t WHERE t.avg_score ( SELECT MAX(avg_score) FROM ( SELECT AVG(score) AS avg_score FROM score GROUP BY course_id ) AS t2 );这里出现了嵌套里再嵌套外层派生表算每课平均分子查询里再算一次平均分的最大值。两次聚合逻辑一样但目的不同写的时候容易把别名搞混。2.3 SELECT 子句子查询标量值直接拼进结果列SELECT 列表里嵌子查询要求子查询必须返回单行单列否则报错。适合给每行附加一个统计值。查每个学生的姓名和总选课数SELECT s.student_name, (SELECT COUNT(*) FROM score sc WHERE sc.student_id s.student_id) AS course_count FROM student s;逻辑说明对 student 每一行子查询算这个学生选了几门课。这是关联子查询外层每行都会触发一次内层查询。数据量大时性能很差因为执行次数等于外层行数。参数说明如果某个学生没有选课记录COUNT(*) 返回 0不会返回 NULL所以结果是 0 而不是空。但如果换成 SUM(score)没选课的学生会返回 NULL需要用 COALESCE 包一层。注意SELECT 子句子查询在结果集大时是性能杀手。常见优化是改成 LEFT JOIN GROUP BY让优化器一次性算完而不是逐行触发。3. 嵌套查询和连接查询怎么选三个判断维度3.1 从语义清晰度判断连接查询把多张表平铺在 FROM 里适合“我要同时看到多张表的列”。嵌套查询把逻辑分层适合“我要用一张表的查询结果去筛另一张表”。比如查“没选任何课的学生”-- 嵌套写法语义是“不存在选课记录的学生” SELECT student_id, student_name FROM student WHERE NOT EXISTS ( SELECT 1 FROM score WHERE score.student_id student.student_id ); -- 连接写法语义是“左连接后右表为空的那些行” SELECT s.student_id, s.student_name FROM student s LEFT JOIN score sc ON s.student_id sc.student_id WHERE sc.student_id IS NULL;两种写法结果一样但 NOT EXISTS 读起来更接近自然语言。连接写法需要理解 LEFT JOIN 产生 NULL 行的机制新手容易在 WHERE 条件上写错。3.2 从性能判断现代数据库优化器对 IN、EXISTS、JOIN 的改写能力很强多数情况下执行计划趋同。但有几个场景差异明显场景推荐写法原因子查询结果集小外层表大IN 或 EXISTS优化器可能物化子查询结果再哈希匹配外层表小子查询能走索引EXISTS逐行探测索引外层行数少则总代价低需要子查询的列出现在结果里JOIN嵌套在 SELECT 里逐行触发JOIN 一次算完多层聚合后再筛选FROM 子查询WHERE 不能直接过滤聚合值3.3 从可维护性判断嵌套超过三层可读性急剧下降。我一般会遵守一个习惯WHERE 里的嵌套不超过两层FROM 里的派生表不超过两层超了就拆成 CTEWITH 子句。-- 用 CTE 改写多层嵌套逻辑从上到下读 WITH course_avg AS ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ), max_avg AS ( SELECT MAX(avg_score) AS max_score FROM course_avg ) SELECT ca.course_id, ca.avg_score FROM course_avg ca, max_avg ma WHERE ca.avg_score ma.max_score;CTE 不是所有数据库都支持MySQL 8.0 之前不支持但支持的地方优先用。它把嵌套变成了顺序叙述调试时也可以单独跑每一个 CTE 看中间结果。4. 统计查询里的嵌套实战从分组到同比4.1 分组聚合后筛选HAVING 和子查询的分工统计查询绕不开 GROUP BY HAVING。HAVING 能过滤聚合值但只能过滤当前分组算出来的值。如果要拿这个聚合值和另一个查询的结果比就得嵌套。查平均分高于全校平均分的课程SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id HAVING AVG(score) ( SELECT AVG(score) FROM score );逻辑说明HAVING 里可以直接嵌子查询。内层算全校平均分外层对每个课程分组算平均分后比较。这里不能用 WHERE因为 WHERE 在分组前执行。参数说明如果 score 表里有 NULL 值AVG 会自动忽略 NULL。但 COUNT(*) 会计入 NULL 行COUNT(score) 不会。统计口径要提前确认。4.2 相关子查询做逐行统计查每个学生的最高分和该分数对应的课程SELECT s.student_id, 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 MAX(score) FROM score WHERE student_id s.student_id );逻辑说明对每一行 score子查询算这个学生的最高分如果当前行分数等于最高分就保留。一个学生如果有两门课同分且都是最高分会返回两行。参数说明这种写法在数据量大时慢因为每行都要跑一次子查询。优化方式是先用派生表算出每个学生的最高分再 JOIN 回去。-- 优化版一次算出所有学生的最高分 SELECT s.student_id, s.student_name, sc.course_id, sc.score FROM student s JOIN score sc ON s.student_id sc.student_id JOIN ( SELECT student_id, MAX(score) AS max_score FROM score GROUP BY student_id ) AS m ON sc.student_id m.student_id AND sc.score m.max_score;4.3 嵌套查询做同比环比假设有月度销售表 sales(month, amount)查每个月和上个月的差额SELECT s1.month, s1.amount, s1.amount - ( SELECT s2.amount FROM sales s2 WHERE s2.month s1.month - 1 ) AS diff FROM sales s1 ORDER BY s1.month;逻辑说明对每个月子查询拿上个月的金额外层做减法。如果月份不连续比如 1 月没有上个月子查询返回 NULLdiff 也是 NULL。参数说明月份是数字才能做减法。如果是日期类型要用日期函数算上个月。这种逐行关联子查询在数据量大时慢可以用窗口函数 LAG 替代-- 窗口函数写法一次扫描完成 SELECT month, amount, amount - LAG(amount) OVER (ORDER BY month) AS diff FROM sales ORDER BY month;窗口函数不是所有数据库都支持MySQL 8.0、PostgreSQL、SQL Server 2012 可以老版本 MySQL 只能用嵌套子查询。5. 嵌套查询避坑五条血泪经验5.1 子查询返回多行却用了比较运算符现象WHERE score (SELECT score FROM score WHERE course_id CS101)报错“子查询返回多于一行”。原因标量子查询要求返回单值但 CS101 有多条成绩记录。解决要么加聚合函数(SELECT AVG(score) ...)要么改用 ALL/ ANY要么改成 JOIN。5.2 NOT IN 遇到 NULL 返回空集现象WHERE student_id NOT IN (SELECT student_id FROM score)返回 0 行但明明有学生没选课。原因score 表里如果有 student_id 为 NULL 的行NOT IN (..., NULL)的结果永远是 UNKNOWN不返回任何行。解决改用 NOT EXISTS或者在子查询里加WHERE student_id IS NOT NULL。5.3 派生表没写别名现象MySQL 报 “Every derived table must have its own alias”。原因FROM 子句里的子查询结果是一张临时表数据库要求必须有名字。解决加AS t。Oracle 里不能加 AS直接写) t。5.4 关联子查询在 SELECT 列表里拖慢查询现象查 1000 个学生每个学生附带选课数跑了十几秒。原因SELECT 里的关联子查询逐行执行1000 行就是 1000 次子查询。解决改成 LEFT JOIN GROUP BY一次算完。SELECT s.student_id, s.student_name, COUNT(sc.student_id) AS course_count FROM student s LEFT JOIN score sc ON s.student_id sc.student_id GROUP BY s.student_id, s.student_name;5.5 嵌套层次太深导致优化器选错计划现象三层嵌套的查询明明子查询结果集很小却走了全表扫描。原因部分数据库对深层嵌套的优化能力有限尤其是老版本 MySQL。解决用 CTE 拆开或者把子查询结果物化成临时表加索引后再 JOIN。6. 把嵌套查询写成可调试的 CTE一个习惯嵌套查询写完之后最痛苦的事情是调不对。一条五层嵌套的 SQL报错只告诉你语法没问题但结果不对你根本不知道哪一层出了岔子。我现在的习惯是只要嵌套超过两层先写成 CTE每一层单独跑一遍看结果确认无误后再决定要不要合并回嵌套写法。-- 分步调试先跑这一层 WITH course_avg AS ( SELECT course_id, AVG(score) AS avg_score, COUNT(*) AS cnt FROM score GROUP BY course_id ) SELECT * FROM course_avg ORDER BY avg_score DESC; -- 确认没问题后再套下一层 WITH course_avg AS ( SELECT course_id, AVG(score) AS avg_score FROM score GROUP BY course_id ), ranked AS ( SELECT course_id, avg_score, RANK() OVER (ORDER BY avg_score DESC) AS rk FROM course_avg ) SELECT * FROM ranked WHERE rk 3;这个习惯帮我省了很多后悔药。嵌套查询本身不难难的是出问题时你不知道该看哪一层。CTE 把黑匣子拆成了透明管道每一段都能单独验证。还有一个参数层面的习惯写完嵌套查询后一定用 EXPLAIN 看一眼执行计划。重点看子查询是被物化了还是逐行关联有没有走索引有没有出现 DEPENDENT SUBQUERY。如果看到 DEPENDENT SUBQUERY 且外层行数大基本可以判定需要改写。最后说一个我踩过的坑早期做统计查询时喜欢把所有逻辑塞进一条 SQL觉得这样“高效”。后来发现一条 200 行的嵌套 SQL 维护成本远高于拆成三条简单 SQL 加一张临时表。数据库不是越少交互越好可读性和可调试性在实验和项目里同样重要。希望帮到你。本文还有配套的精品资源点击获取