简介《数据库实验5嵌套查询》是一份面向数据库课程学习者的实验报告文档围绕数据统计查询与嵌套查询展开适合正在完成SQL实验、复习SELECT语句及子查询用法的学生对照使用。包内共1个doc文件约642KB内容以实验目的、实验内容、示例SQL与思考总结为主便于直接查阅和整理。文档覆盖COUNT、SUM、MAX、MIN等统计函数INNER JOIN、LEFT JOIN、RIGHT JOIN等连接方式以及子查询、派生表和相关谓词的应用结合CPXS数据库中的客户、产品、销售等表给出统计客户数、库存总量、上海客户订单、价格比较、未被订购产品等典型题目与语句。已有931人学习可作为课堂实验参考和期末复习材料帮助读者理解复杂查询的拆解思路与SQL书写规范。1. 从一份 .doc 实验报告说起CPXS 库里的统计与嵌套查询到底练什么数据库实验做到第五个很多人是在这一步第一次被 SQL 卡住的前面单表查询照着抄还能跑一进到统计函数配 GROUP BY、再把一条 SELECT 塞进另一条 SELECT 的 WHERE 里结果要么直接报错要么数字对不上还看不出来。这份《数据库实验5嵌套查询》的 doc 围绕 CPXS 库展开十四道题把 COUNT/SUM/MAX/MIN、GROUP BY 与 HAVING、隐式连接、单行子查询、IN 与 EXISTS 串了一遍本质是一次“从能跑到跑对”的集中训练。它适合正在做数据库实验、课程设计里要交查询报告的人也适合写了几年 CRUD、却很少认真想过 HAVING 和 WHERE 到底该谁上场的人。下面按真实执行顺序拆先建库灌数再逐题改写最后把几个反复翻车的点摊开讲。2. 搭好 CPXS 数据集三张表、样例数据与可复现建表脚本2.1 三张表的字段语义与类型选择实验里反复出现的表其实只有三张先把字段语义钉死后面十四道题才有共同的语言基础。表名字段含义建议类型customercno客户编号CHAR(4)customercname客户公司名VARCHAR(40)customersite所在城市VARCHAR(20)customertele联系电话VARCHAR(20)productpcode产品编号CHAR(6)productpname产品名称VARCHAR(40)productprice单价DECIMAL(10,2)productstocks库存量INTsalecno订购客户编号CHAR(4)salepcode订购产品编号CHAR(6)saleoquantity订购数量INT两个类型选择值得单独说。单价用DECIMAL(10,2)而不是FLOAT/DOUBLE是因为浮点数在做BETWEEN 10 AND 20、PRICE 15.00这类比较时会出幺蛾子——0.10.2 不等于 0.3 这种经典问题在金额字段上就是实打实的血泪经验。用定点小数等值比较和范围比较都是精确的索引也能正常走。编号用CHAR而不是VARCHAR是因为它们的长度是固定语义CHAR(4)在 InnoDB 里定长存储做主键和连接键时比较开销更可预期。很多同学图省事全用VARCHAR(50)在几千行数据上看不出差别等到连接三张表做嵌套查询时临时表体积和哈希键长度都会跟着涨。还有一个容易被忽略的点sale表的主键怎么定。如果按“客户 产品”建联合主键(cno, pcode)那一个客户对同一产品只能有一条订购记录重复下单必须累加进oquantity。这会直接影响第 8 题的统计口径——你要判断“被几个客户订购”就得用COUNT(DISTINCT cno)而不是COUNT(*)。建表时没想清楚这一点后面所有聚合数字都是错的。2.2 建库建表脚本与三个设计取舍下面这份脚本在 MySQL 8.x 上可以直接整段执行字符集显式指定为utf8mb4避免中文公司名和城市名出现乱码导致WHERE site A市匹配不上。-- 重建库避免上一次实验残留的表干扰结果 DROP DATABASE IF EXISTS cpxs; CREATE DATABASE cpxs DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE cpxs; -- 客户表cno 主键site 建普通索引第 4、11、12 题都按城市过滤 CREATE TABLE customer ( cno CHAR(4) NOT NULL, cname VARCHAR(40) NOT NULL, site VARCHAR(20) NOT NULL, tele VARCHAR(20), PRIMARY KEY (cno), KEY idx_site (site) ) ENGINEInnoDB; -- 产品表price 用定点小数stocks 建索引供库存统计走覆盖 CREATE TABLE product ( pcode CHAR(6) NOT NULL, pname VARCHAR(40) NOT NULL, price DECIMAL(10,2) NOT NULL, stocks INT NOT NULL DEFAULT 0, PRIMARY KEY (pcode), KEY idx_price (price) ) ENGINEInnoDB; -- 销售表联合主键保证“一客户一产品一行”外键约束保证连接不出现孤儿行 CREATE TABLE sale ( cno CHAR(4) NOT NULL, pcode CHAR(6) NOT NULL, oquantity INT NOT NULL, PRIMARY KEY (cno, pcode), KEY idx_pcode (pcode), CONSTRAINT fk_sale_customer FOREIGN KEY (cno) REFERENCES customer(cno), CONSTRAINT fk_sale_product FOREIGN KEY (pcode) REFERENCES product(pcode) ) ENGINEInnoDB;脚本里有三个取舍需要交代清楚。第一是排序规则选了utf8mb4_0900_ai_ci其中ci表示大小写不敏感这会影响第 9 题LIKE B%的匹配结果——b01和B01都会被命中。如果你的实验环境是 SQL Server 默认的Chinese_PRC_CI_AS行为是一致的但如果是 Linux 下 MySQL 常见的utf8mb4_binLIKE B%就只匹配大写题目会“莫名其妙”少一行。这不是玄学是排序规则在起作用。第二是外键约束。实验报告里通常不要求写外键但我习惯加上因为嵌套查询一旦查出孤儿行比如 sale 里有个不存在的 pcode你在排查时会以为是子查询写错了实际是数据脏了。第三是索引。idx_site、idx_price、idx_pcode这三个索引不是给这几百行数据准备的而是为了在第 6 章用EXPLAIN对比改写前后执行计划时有东西可看。没有索引所有查询都是全表扫描执行计划一模一样你就失去了判断改写是否有效的抓手。2.3 灌样例数据与三条自检数据要能让十四道题都查出非空结果同时保留一些边界情况——比如有产品从未被订购第 14 题需要、有客户只订了一种产品第 2 题需要排除、有城市只有一家公司第 12 题需要单行子查询。-- 客户A市两家、B市两家、C市一家 INSERT INTO customer (cno, cname, site, tele) VALUES (C001,A公司,A市,0000-0001), (C002,B公司,A市,0000-0002), (C003,C公司,B市,0000-0003), (C004,D公司,B市,0000-0004), (C005,E公司,C市,0000-0005); -- 产品B 开头两个第 9 题、单价跨 10~20 区间第 7 题、D01 从未被订购第 14 题 INSERT INTO product (pcode, pname, price, stocks) VALUES (A01,产品甲, 15.00, 300), (B01,产品乙, 18.50, 800), (B02,产品丙, 12.00, 120), (C01,产品丁, 25.00, 600), (D01,产品戊, 9.90, 50); -- 销售C001 和 C002 各订两种库存超 500 的产品有 B01、C01 INSERT INTO sale (cno, pcode, oquantity) VALUES (C001,A01,120), (C001,B01,260), (C002,A01, 90), (C002,C01,150), (C003,B01,210), (C004,B02, 80), (C005,A01,300);灌完之后不要急着写查询先跑三条自检把数据状态摸清楚否则后面数字对不上时你分不清是 SQL 写错还是数据本身就不符合预期。-- 自检 1三张表行数是否符合预期3 / 5 / 7 SELECT customer AS t, COUNT(*) AS n FROM customer UNION ALL SELECT product, COUNT(*) FROM product UNION ALL SELECT sale, COUNT(*) FROM sale; -- 自检 2每个客户的订购种类数第 2 题的答案就藏在这里 SELECT cno, COUNT(pcode) AS kinds FROM sale GROUP BY cno; -- 自检 3找出从未出现在 sale 里的产品第 14 题的标准答案 SELECT pcode, pname FROM product WHERE pcode NOT IN (SELECT pcode FROM sale);自检 1 里用了UNION ALL而不是UNION因为三行标签本来就不同去重只会多一次排序开销。自检 2 的结果应该是 C0012、C0022、C0031、C0041、C0051如果 C001 显示 1说明联合主键没生效、插入了重复行。自检 3 应当只返回 D01 一行如果返回空说明你灌数据时把所有产品都订了一遍第 14 题就永远查不出东西。3. 统计查询与分组聚合COUNT、SUM 和 HAVING 的落点在哪3.1 一条 SELECT 的真实执行顺序写聚合查询之前脑子里得有一条执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。这个顺序不是教科书摆设它直接决定了你能在哪个位置引用什么。WHERE在分组之前执行所以它只能看到原始行不能写聚合函数。HAVING在分组之后执行它看到的是每组压缩后的结果可以写COUNT(*)、SUM(oquantity)。SELECT在HAVING之后这就是为什么 MySQL 允许你在HAVING里用SELECT中定义的别名这是 MySQL 的扩展标准 SQL 不允许反过来在WHERE里用别名就会报Unknown column。实验报告里第 4 题写的是SELECT SITE, COUNT(CNO) FROM CUSTOMER GROUP BY SITE HAVING SITE A市;这句能跑但它是把本该在WHERE阶段完成的过滤拖到了HAVING阶段。执行时数据库要先把整张 customer 按城市分组、算出每组的计数再丢掉不匹配的组。数据量小的时候看不出差别几百万行时这一下就是几秒的差距。等价且更规范的写法是把条件下推-- 过滤下推到 WHERE分组前就把无关行砍掉 SELECT site, COUNT(cno) AS company_cnt FROM customer WHERE site A市 GROUP BY site;这里还有个细节COUNT(cno)和COUNT(*)在这一题结果一样因为cno是主键不可能为 NULL。但如果换成COUNT(tele)而tele允许为空结果就会少算那些没填电话的客户。COUNT(*)数的是行数COUNT(列)数的是该列非 NULL 的个数这个差别在第 1 题“统计客户数目”上是踩坑高发区——只要表里有空值字段两个写法就会给出不同的数字。3.2 前八题逐条改写第一组题1、2、3、5是标准聚合基本照写即可但要养成给结果列起别名的习惯不然导出的结果集列名会是一长串COUNT(*)写进实验报告很难看。-- 第1题客户总数。COUNT(*) 数行不受任何列 NULL 影响 SELECT COUNT(*) AS customer_total FROM customer; -- 第2题至少订购两种以上产品的客户编号与种类数 -- HAVING 里筛的是“分组后的组”不是原始行 SELECT cno, COUNT(pcode) AS kind_cnt FROM sale GROUP BY cno HAVING COUNT(pcode) 2; -- 第3题每个客户订购产品数量的总数 SELECT cno, SUM(oquantity) AS total_qty FROM sale GROUP BY cno; -- 第5题所有产品的最高单价 SELECT MAX(price) AS max_price FROM product;第 2 题原文写作HAVING COUNT(PCODE)2逻辑上没问题但注意题目说的是“至少订购两种以上”。“至少两种”就是 2加上“以上”容易让人犹豫要不要写 2。这类表述在实验报告里很常见判分标准通常是 2因为“两种以上”在中文语境里含本数。写 2的话C001、C002 这两条正好两种的客户会被漏掉结果集直接变空——这种“查出来是空表”的情况八成是边界条件写反了。第 3 题的SUM(oquantity)有一个隐含前提oquantity不能为 NULL。如果建表时没加NOT NULL某些客户的整组都是 NULLSUM会返回 NULL 而不是 0。要兼容这种情况写SUM(IFNULL(oquantity, 0))或者建表时就把约束加上。我倾向前者因为约束一旦上线就很难改。第二组题4、6、7、8全部涉及GROUP BY加HAVING也是错误最集中的地方。原文第 6 题是这样的SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS 500;这句的意图是“库存量超过 500 的产品个数”但实际执行结果是先按stocks的每个不同取值分组每组算一个计数再筛掉stocks 500的组。因为样例数据里 300、800、120、600、50 各不相同每组只有一行COUNT(PCODE)全是 1最后返回两行 1 和 1。学生看到结果会以为“超过 500 的有两个产品”数字碰巧对了但返回的是两行而不是一个总数换个数据集就露馅。第 7 题同理SELECT PRICE, COUNT(PCODE) FROM PRODUCT GROUP BY PRICE HAVING PRICE BETWEEN 10 AND 20;按单价分组每个单价一组返回的是“每个价格档位有几个产品”而不是“价格在 10~20 之间的产品总数”。正确的做法是把这两题看成“先过滤、再计数”根本不需要GROUP BY-- 第6题库存超过 500 的产品个数先 WHERE 过滤再 COUNT SELECT COUNT(*) AS cnt FROM product WHERE stocks 500; -- 第7题单价 10~20 元之间的产品个数 -- BETWEEN 是闭区间含 10.00 和 20.00 SELECT COUNT(*) AS cnt FROM product WHERE price BETWEEN 10 AND 20;判断一道题该不该GROUP BY有个简单标准看题目问的是“每个 X 的 Y”还是“整体的 Y”。问“每个客户的订购总数”才分组问“超过 500 的产品有几个”是整体口径分组只会把答案拆碎。第 8 题是前八题里最容易写错的-- 原写法SELECT 列表里的 oquantity 既不在 GROUP BY 里也没被聚合 SELECT PCODE, OQUANTITY FROM SALE WHERE OQUANTITY 100 GROUP BY PCODE HAVING COUNT(PCODE) 2;这句在 MySQL 5.7 以后、开了ONLY_FULL_GROUP_BY的默认配置下会直接报错提示oquantity不在GROUP BY中且包含非聚合列。就算关掉这个模式让它跑起来oquantity取的是组内随机一行的值结果不可复现。题目要的是“产品编号、订购数量总数”正确写法-- 第8题至少被两个客户订购、且每个客户订购量都超过 100 的产品 -- 用 COUNT(DISTINCT cno) 防重复计数用 SUM 求总量 SELECT pcode, SUM(oquantity) AS total_qty, COUNT(DISTINCT cno) AS customer_cnt FROM sale WHERE oquantity 100 GROUP BY pcode HAVING COUNT(DISTINCT cno) 2;COUNT(DISTINCT cno)里的DISTINCT不能省。前面sale表用了联合主键一个客户对一个产品只有一行看似不会重复但如果哪天业务改成允许多次下单、主键换成自增 IDCOUNT(cno)就会把同一客户的多笔订单算成多个客户结论从“两个客户”变成“三笔订单”。这种字段语义随表结构漂移的坑用DISTINCT兜一层最省心。3.3 HAVING 里写条件为什么常常是错的判断条件该放WHERE还是HAVING只看一条这个条件依赖不依赖聚合结果。stocks 500、price BETWEEN 10 AND 20、site A市都是单行属性跟分组无关一律进WHERE。COUNT(pcode) 2、SUM(oquantity) 1000依赖聚合值只能进HAVING。把单行条件写进HAVING的代价不只是慢。更麻烦的是语义偏差HAVING stocks 500在按stocks分组时筛的是“组的代表值”而当分组键不是stocks时MySQL 会从组内随便挑一行来判断结果取决于存储引擎返回行的顺序。同一个 SQL 在开发机和服务器上跑出不同数字十有八九就是这里出了问题。还有一个常被忽略的点HAVING里可以引用SELECT里定义的别名WHERE里不行。比如SELECT SUM(oquantity) AS total ... HAVING total 1000是合法的而WHERE total 1000会报未知列。但这属于 MySQL 的方言扩展换到 PostgreSQL 或标准 SQL 环境就跑不通。写实验报告时如果要求“通用 SQL”建议在HAVING里重写完整聚合表达式。4. 连接与嵌套从逗号连接改写到 EXISTS 的四种解法4.1 逗号连接与显式 JOIN 的等价改写原文第 11 题用的是老式逗号连接SELECT SALE.CNO, PCODE, CNAME, OQUANTITY FROM SALE, CUSTOMER WHERE CUSTOMER.CNO SALE.CNO AND CUSTOMER.SITE A市 AND SALE.OQUANTITY 200;这种写法在教科书里很常见因为它是 1992 标准语法任何数据库都认。但它有两个现实问题一是连接条件和过滤条件混在同一个WHERE里题目一复杂就看不出哪句是“把两张表缝起来”、哪句是“筛数据”二是漏写连接条件时会静默产生笛卡尔积几万行乘几万行直接把数据库拖死而且不报错——这就是最典型的黑匣子式翻车。改成显式JOIN之后两类条件在语法位置上就分开了-- 第11题A市客户中订购量大于 200 的订单明细 SELECT c.cno, s.pcode, c.cname, s.oquantity FROM sale s JOIN customer c ON c.cno s.cno WHERE c.site A市 AND s.oquantity 200;两种写法在 MySQL 8 上会被优化器改写成同一个执行计划性能上没有差别但可读性差很多。ON后面只放连接键WHERE后面只放过滤条件一眼能看出“谁和谁连、筛掉了什么”。同时给表起短别名s、c避免在SELECT里反复写全表名——第 11 题原文的SELECT SALE.CNO, PCODE, CNAME里PCODE没带表前缀如果sale和product都有pcode列就会报Column pcode in field list is ambiguous这类报错在四表连接时非常常见。关于连接类型的选择实验里基本只会用到INNER JOIN。LEFT JOIN的典型场景是“查所有产品及其订购量没被订购的显示 0”这个需求在第 14 题附近会碰到。这里有个细节LEFT JOIN之后再在WHERE里写s.pcode IS NULL才是“找出左表有右表没有”的标准写法如果把条件写在ON里就变成了另一种语义。这个区分是面试高频题也是实际写报表时的分水岭。4.2 单行子查询、IN、ANY/ALL、EXISTS 的选型第 12 题是单行子查询的典型-- 第12题与 A公司 同城的其他客户 SELECT tele, cname FROM customer WHERE site (SELECT site FROM customer WHERE cname A公司);这句的隐含假设是子查询只返回一行。样例数据里 A公司 只有一家所以不报错。但只要业务上出现两家同名公司现实里太正常了分公司重名、录入重复子查询返回两行MySQL 立刻抛Subquery returns more than 1 row。更稳的写法有两种取决于你想要什么语义。如果“同城”指任一 A公司 所在的城市用IN-- 用 IN 容错多行返回! 或 排除自身 SELECT cno, cname, tele FROM customer WHERE site IN (SELECT site FROM customer WHERE cname A公司) AND cname A公司;如果确实只想取一条比如取编号最小的那家用LIMIT 1或者加ORDER BY cno LIMIT 1把子查询锁定成单行让意图显式化。最怕的是既没IN也没LIMIT代码在测试库跑得好好的上线遇到重复数据就炸。第 13 题是连接加子查询的混合-- 第13题订购了单价比 A01 高的产品的订单 SELECT s.pcode, s.cno, s.oquantity FROM sale s JOIN product p ON p.pcode s.pcode WHERE p.price (SELECT price FROM product WHERE pcode A01);单行子查询在这里是安全的因为pcode是主键WHERE pcode A01最多返回一行这是比cname可靠得多的前提。判断单行子查询能不能省略IN就看子查询的过滤条件是不是命中主键或唯一键——是放心用不是一律用IN兜底。除了IN标准 SQL 还提供了ANY、SOME、ALL三个量词。第 13 题也可以写成-- 用 ANY 等价改写大于子查询结果中的任意一个即大于最小值 SELECT s.pcode, s.cno, s.oquantity FROM sale s JOIN product p ON p.pcode s.pcode WHERE p.price ANY (SELECT price FROM product WHERE pcode LIKE B%); ANY等价于 MIN(...) ALL等价于 MAX(...)。这两组等价关系在实验报告里经常被拿来出题但实际项目里我很少写ANY——不是因为它不好而是因为MIN/MAX写在WHERE里可读性更好优化器也更容易利用索引。ANY的价值在于当你需要 ANY等价于IN时能表达出“和集合中任意一个相等”的语义但既然有IN也就没必要绕。EXISTS是另一条路它不返回具体值只返回真或假-- 用 EXISTS 改写第13题只要存在比 A01 贵的产品被该订单订购 SELECT s.pcode, s.cno, s.oquantity FROM sale s WHERE EXISTS ( SELECT 1 FROM product p WHERE p.pcode s.pcode AND p.price (SELECT price FROM product WHERE pcode A01) );EXISTS的子查询SELECT列表写1、*还是具体列都一样因为数据库根本不关心返回什么只关心有没有行。这一点新手经常纠结其实写SELECT 1是最能表达“我不需要值”的方式。4.3 相关子查询与 NOT EXISTS 的正确写法第 14 题原文是全文最值得说的一句-- 原写法子查询返回多行必然报错 SELECT PRODUCT.PCODE, PNAME FROM PRODUCT WHERE PRODUCT.PCODE ! (SELECT SALE.PCODE FROM SALE);这行 SQL 在任何有两条以上销售记录的库上都跑不通因为!后面跟的是一个多行结果集。就算把!换成NOT IN也只是“能跑”还埋着 NULL 的雷——如果sale.pcode里有 NULLNOT IN的比较结果会变成UNKNOWN整个查询返回空集。这是个极其隐蔽的坑很多人在数据量小时根本发现不了。三种正确写法按推荐程度排序-- 写法一NOT EXISTS最稳NULL 安全且能走反连接anti-join SELECT p.pcode, p.pname FROM product p WHERE NOT EXISTS (SELECT 1 FROM sale s WHERE s.pcode p.pcode); -- 写法二NOT IN必须保证子查询列非空 SELECT pcode, pname FROM product WHERE pcode NOT IN (SELECT pcode FROM sale WHERE pcode IS NOT NULL); -- 写法三LEFT JOIN IS NULL可读性最好适合多表场景 SELECT p.pcode, p.pname FROM product p LEFT JOIN sale s ON s.pcode p.pcode WHERE s.pcode IS NULL;NOT EXISTS和LEFT JOIN ... IS NULL在 MySQL 8 上通常会被优化成同一种反连接执行计划性能接近NOT IN则可能被改写成半连接或物化子查询具体看版本和统计信息。我在生产环境里一律优先NOT EXISTS因为它的语义最贴近“不存在”不受 NULL 影响而且嵌套层级再深也不会因为语法嵌套而难以阅读。相关子查询的执行模型值得单独理解一下。NOT EXISTS里的子查询引用了外层的p.pcode所以它不是“执行一次拿到结果集”而是“外层每取一行内层就用这行的pcode去sale里探一次”。朴素实现是 O(n×m)但优化器会把它改写成反连接用哈希或索引查找实际接近 O(nm)。理解这一点你就知道为什么在sale.pcode上建索引很重要——没索引时每一次探测都是全表扫描几条数据看不出几万条就是分钟级。顺带说一个改写思路凡是能用“集合运算”表达的就不要用“逐行判断”。第 14 题的本质是product集合减去sale里出现过的pcode集合用NOT EXISTS或LEFT JOIN表达都比逐行子查询清晰。这个思维转换一旦建立后面写复杂报表会顺很多。5. 避坑排查六条实验语句跑不通或跑偏的真实原因5.1 报错Subquery returns more than 1 row现象执行第 12 题或第 14 题时MySQL 直接抛出这个错误语句中断。原因子查询用了或!这类标量比较符但子查询本身返回了多行。第 12 题里如果cname A公司命中两家公司或者第 14 题里sale表本来就有多条记录都会触发。很多人在测试库只插了一条数据换到实验验收库就报错还以为是环境问题。解决先单独跑一次子查询看行数。行数大于 1 就把外层改成IN/NOT IN/EXISTS或者给子查询加LIMIT 1把意图写死。更根本的做法是检查过滤条件是否命中了主键或唯一键——命中才允许用。5.2 统计结果少了几行COUNT数字对不上现象第 2 题查“至少订购两种产品的客户”明明有两个客户各订了两种结果只返回一个。原因sale表建表时主键定成了(cno, pcode)但灌数据时同一客户同一产品插了两行而且没报错——说明主键没建成功或者用了INSERT IGNORE把冲突行静默丢弃了。另一种可能是COUNT(pcode)里pcode存在 NULL。解决先跑SHOW CREATE TABLE sale确认主键在不在再用SELECT cno, pcode, COUNT(*) FROM sale GROUP BY cno, pcode HAVING COUNT(*) 1找重复行。字段可能为空的场景统一用COUNT(*)或COUNT(DISTINCT 列)。5.3 表名对不上SALEGROUP与SALE现象把实验报告里的 SQL 逐条粘进去前几题能跑第 2 题突然报Table cpxs.salegroup doesnt exist。原因这份 doc 里第 2 题写的是FROM SALEGROUP第 8、11、13、14 题写的是SALE。同一份实验里出现两个表名大概率是原始数据集里salegroup是sale的一个视图或者旧表名报告作者在不同版本间复制粘贴留下的不一致。解决以建表脚本为准统一成sale。如果你确实需要兼容报告原文可以建一个视图做别名CREATE VIEW salegroup AS SELECT * FROM sale;。但更推荐直接改 SQL因为对着视图调试执行计划会多一层黑箱排查问题时容易绕远。5.4LIKE B%查出来是空集现象第 9 题“以字母 B 开头的所有产品的最低单价”数据里明明有B01、B02MIN(PRICE)却返回 NULL。原因两种可能。一是排序规则是_bin或_cs而数据里存的是小写b01B%匹配不上。二是MIN只对非空值聚合如果price列允许 NULL 且所有匹配行都是 NULL结果就是 NULL 而不是 0。解决SHOW VARIABLES LIKE collation%确认排序规则临时验证可以写WHERE UPPER(pcode) LIKE B%但注意这会让索引失效。要兼顾索引和大小写规范做法是建表时选_ci排序规则或者用pcode LIKE B% COLLATE utf8mb4_0900_ai_ci显式指定。另外MIN返回 NULL 时用IFNULL(MIN(price), 0)兜底。5.5GROUP BY报only_full_group_by错误现象第 8 题原样执行报Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column cpxs.sale.oquantity。原因MySQL 5.7 起默认开启ONLY_FULL_GROUP_BY要求SELECT列表里的非聚合列必须出现在GROUP BY中。原题的oquantity既没分组也没聚合属于典型的“语义不明确”写法。解决不要用SET sql_mode 去关掉这个模式那只是把问题藏起来。正确做法是把oquantity改成SUM(oquantity)或MAX(oquantity)明确你到底要这一组的哪个统计值。关模式是后悔药吃一次就会一直吃。5.6 连接查询结果行数暴涨现象第 11 题本来预期返回一两条订单结果出来几十行数字全是重复的。原因FROM sale, customer WHERE customer.cno sale.cno里的连接条件写漏了或者写成了WHERE customer.cno customer.cno这种自我比较恒为真的条件产生笛卡尔积。另一种情况是sale表和product表连接时product表里同一pcode存在多条记录主键没建一对多放大。解决先用SELECT COUNT(*) FROM a JOIN b ON ...单独数连接后的行数和两表各自行数比一比。连接后的行数不应该超过“一对多”里多端的行数。养成写显式JOIN的习惯连接键写在ON后面能大幅降低漏写概率。6. 用 EXPLAIN 与结果集比对验证改写是否真的等价改写完十四道题最怕的是“看着对、其实不对”。我的习惯是两步验证先看执行计划再比结果集。执行计划用EXPLAIN重点看三列type、rows、Extra。type从好到坏大致是const eq_ref ref range index ALL出现ALL就是全表扫描。rows是优化器估算的扫描行数改写前后应该持平或下降。Extra里出现Using filesort、Using temporary要留意聚合查询里临时表很常见但如果改写后多出来一个说明你的写法反而更重了。-- 对比第 14 题两种写法的执行计划 EXPLAIN FORMATTRADITIONAL SELECT p.pcode, p.pname FROM product p WHERE NOT EXISTS (SELECT 1 FROM sale s WHERE s.pcode p.pcode); EXPLAIN FORMATTRADITIONAL SELECT p.pcode, p.pname FROM product p LEFT JOIN sale s ON s.pcode p.pcode WHERE s.pcode IS NULL;两条都应该在sale上出现ref或eq_ref访问Extra里带Using where; Not exists或Using where; Using index。如果某一条的rows是全表行数、type是ALL说明索引没被用上先回去检查sale.pcode上有没有索引、字段类型和排序规则是否一致——连接两边的字符集不同会导致索引失效这个坑很隐蔽。结果集比对更直接把原写法和改写各跑一遍做对称差行数为 0 才算等价。-- 对比两版查询的差集返回 0 行才说明结果完全一致 WITH a AS ( SELECT pcode FROM product WHERE pcode NOT IN (SELECT pcode FROM sale WHERE pcode IS NOT NULL) ), b AS ( SELECT p.pcode FROM product p LEFT JOIN sale s ON s.pcode p.pcode WHERE s.pcode IS NULL ) SELECT only_in_a AS side, pcode FROM a WHERE pcode NOT IN (SELECT pcode FROM b) UNION ALL SELECT only_in_b, pcode FROM b WHERE pcode NOT IN (SELECT pcode FROM a);这里用NOT IN而不是NOT EXISTS做差集是因为两边的pcode都已经过滤过 NULL可以放心用。WITH子句在 MySQL 8.0 才支持如果你的实验环境是 5.7把两个 CTE 改写成派生表FROM (SELECT ...) a即可逻辑不变。还有一个更省事的技巧给每条查询加一个COUNT(*)外壳先比总数再比明细。总数对不上一定是过滤条件写偏了总数一样但明细不同多半是排序或者重复行的问题。这个习惯让我在排查报表数字时少走了很多弯路。从那以后我每次改完一条嵌套查询都会强制走一遍「EXPLAIN 看计划 差集比结果」这两步哪怕只是把IN换成EXISTS这种看起来绝对等价的小改动。因为等价这件事靠眼睛看是靠不住的得让数据库自己说话。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/31 11:43:55 · 阅读 1.2k CPXS数据库实验5：SQL统计聚合与嵌套查询避坑指南
简介《数据库实验5嵌套查询》是一份面向数据库课程学习者的实验报告文档围绕数据统计查询与嵌套查询展开适合正在完成SQL实验、复习SELECT语句及子查询用法的学生对照使用。包内共1个doc文件约642KB内容以实验目的、实验内容、示例SQL与思考总结为主便于直接查阅和整理。文档覆盖COUNT、SUM、MAX、MIN等统计函数INNER JOIN、LEFT JOIN、RIGHT JOIN等连接方式以及子查询、派生表和相关谓词的应用结合CPXS数据库中的客户、产品、销售等表给出统计客户数、库存总量、上海客户订单、价格比较、未被订购产品等典型题目与语句。已有931人学习可作为课堂实验参考和期末复习材料帮助读者理解复杂查询的拆解思路与SQL书写规范。1. 从一份 .doc 实验报告说起CPXS 库里的统计与嵌套查询到底练什么数据库实验做到第五个很多人是在这一步第一次被 SQL 卡住的前面单表查询照着抄还能跑一进到统计函数配 GROUP BY、再把一条 SELECT 塞进另一条 SELECT 的 WHERE 里结果要么直接报错要么数字对不上还看不出来。这份《数据库实验5嵌套查询》的 doc 围绕 CPXS 库展开十四道题把 COUNT/SUM/MAX/MIN、GROUP BY 与 HAVING、隐式连接、单行子查询、IN 与 EXISTS 串了一遍本质是一次“从能跑到跑对”的集中训练。它适合正在做数据库实验、课程设计里要交查询报告的人也适合写了几年 CRUD、却很少认真想过 HAVING 和 WHERE 到底该谁上场的人。下面按真实执行顺序拆先建库灌数再逐题改写最后把几个反复翻车的点摊开讲。2. 搭好 CPXS 数据集三张表、样例数据与可复现建表脚本2.1 三张表的字段语义与类型选择实验里反复出现的表其实只有三张先把字段语义钉死后面十四道题才有共同的语言基础。表名字段含义建议类型customercno客户编号CHAR(4)customercname客户公司名VARCHAR(40)customersite所在城市VARCHAR(20)customertele联系电话VARCHAR(20)productpcode产品编号CHAR(6)productpname产品名称VARCHAR(40)productprice单价DECIMAL(10,2)productstocks库存量INTsalecno订购客户编号CHAR(4)salepcode订购产品编号CHAR(6)saleoquantity订购数量INT两个类型选择值得单独说。单价用DECIMAL(10,2)而不是FLOAT/DOUBLE是因为浮点数在做BETWEEN 10 AND 20、PRICE 15.00这类比较时会出幺蛾子——0.10.2 不等于 0.3 这种经典问题在金额字段上就是实打实的血泪经验。用定点小数等值比较和范围比较都是精确的索引也能正常走。编号用CHAR而不是VARCHAR是因为它们的长度是固定语义CHAR(4)在 InnoDB 里定长存储做主键和连接键时比较开销更可预期。很多同学图省事全用VARCHAR(50)在几千行数据上看不出差别等到连接三张表做嵌套查询时临时表体积和哈希键长度都会跟着涨。还有一个容易被忽略的点sale表的主键怎么定。如果按“客户 产品”建联合主键(cno, pcode)那一个客户对同一产品只能有一条订购记录重复下单必须累加进oquantity。这会直接影响第 8 题的统计口径——你要判断“被几个客户订购”就得用COUNT(DISTINCT cno)而不是COUNT(*)。建表时没想清楚这一点后面所有聚合数字都是错的。2.2 建库建表脚本与三个设计取舍下面这份脚本在 MySQL 8.x 上可以直接整段执行字符集显式指定为utf8mb4避免中文公司名和城市名出现乱码导致WHERE site A市匹配不上。-- 重建库避免上一次实验残留的表干扰结果 DROP DATABASE IF EXISTS cpxs; CREATE DATABASE cpxs DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; USE cpxs; -- 客户表cno 主键site 建普通索引第 4、11、12 题都按城市过滤 CREATE TABLE customer ( cno CHAR(4) NOT NULL, cname VARCHAR(40) NOT NULL, site VARCHAR(20) NOT NULL, tele VARCHAR(20), PRIMARY KEY (cno), KEY idx_site (site) ) ENGINEInnoDB; -- 产品表price 用定点小数stocks 建索引供库存统计走覆盖 CREATE TABLE product ( pcode CHAR(6) NOT NULL, pname VARCHAR(40) NOT NULL, price DECIMAL(10,2) NOT NULL, stocks INT NOT NULL DEFAULT 0, PRIMARY KEY (pcode), KEY idx_price (price) ) ENGINEInnoDB; -- 销售表联合主键保证“一客户一产品一行”外键约束保证连接不出现孤儿行 CREATE TABLE sale ( cno CHAR(4) NOT NULL, pcode CHAR(6) NOT NULL, oquantity INT NOT NULL, PRIMARY KEY (cno, pcode), KEY idx_pcode (pcode), CONSTRAINT fk_sale_customer FOREIGN KEY (cno) REFERENCES customer(cno), CONSTRAINT fk_sale_product FOREIGN KEY (pcode) REFERENCES product(pcode) ) ENGINEInnoDB;脚本里有三个取舍需要交代清楚。第一是排序规则选了utf8mb4_0900_ai_ci其中ci表示大小写不敏感这会影响第 9 题LIKE B%的匹配结果——b01和B01都会被命中。如果你的实验环境是 SQL Server 默认的Chinese_PRC_CI_AS行为是一致的但如果是 Linux 下 MySQL 常见的utf8mb4_binLIKE B%就只匹配大写题目会“莫名其妙”少一行。这不是玄学是排序规则在起作用。第二是外键约束。实验报告里通常不要求写外键但我习惯加上因为嵌套查询一旦查出孤儿行比如 sale 里有个不存在的 pcode你在排查时会以为是子查询写错了实际是数据脏了。第三是索引。idx_site、idx_price、idx_pcode这三个索引不是给这几百行数据准备的而是为了在第 6 章用EXPLAIN对比改写前后执行计划时有东西可看。没有索引所有查询都是全表扫描执行计划一模一样你就失去了判断改写是否有效的抓手。2.3 灌样例数据与三条自检数据要能让十四道题都查出非空结果同时保留一些边界情况——比如有产品从未被订购第 14 题需要、有客户只订了一种产品第 2 题需要排除、有城市只有一家公司第 12 题需要单行子查询。-- 客户A市两家、B市两家、C市一家 INSERT INTO customer (cno, cname, site, tele) VALUES (C001,A公司,A市,0000-0001), (C002,B公司,A市,0000-0002), (C003,C公司,B市,0000-0003), (C004,D公司,B市,0000-0004), (C005,E公司,C市,0000-0005); -- 产品B 开头两个第 9 题、单价跨 10~20 区间第 7 题、D01 从未被订购第 14 题 INSERT INTO product (pcode, pname, price, stocks) VALUES (A01,产品甲, 15.00, 300), (B01,产品乙, 18.50, 800), (B02,产品丙, 12.00, 120), (C01,产品丁, 25.00, 600), (D01,产品戊, 9.90, 50); -- 销售C001 和 C002 各订两种库存超 500 的产品有 B01、C01 INSERT INTO sale (cno, pcode, oquantity) VALUES (C001,A01,120), (C001,B01,260), (C002,A01, 90), (C002,C01,150), (C003,B01,210), (C004,B02, 80), (C005,A01,300);灌完之后不要急着写查询先跑三条自检把数据状态摸清楚否则后面数字对不上时你分不清是 SQL 写错还是数据本身就不符合预期。-- 自检 1三张表行数是否符合预期3 / 5 / 7 SELECT customer AS t, COUNT(*) AS n FROM customer UNION ALL SELECT product, COUNT(*) FROM product UNION ALL SELECT sale, COUNT(*) FROM sale; -- 自检 2每个客户的订购种类数第 2 题的答案就藏在这里 SELECT cno, COUNT(pcode) AS kinds FROM sale GROUP BY cno; -- 自检 3找出从未出现在 sale 里的产品第 14 题的标准答案 SELECT pcode, pname FROM product WHERE pcode NOT IN (SELECT pcode FROM sale);自检 1 里用了UNION ALL而不是UNION因为三行标签本来就不同去重只会多一次排序开销。自检 2 的结果应该是 C0012、C0022、C0031、C0041、C0051如果 C001 显示 1说明联合主键没生效、插入了重复行。自检 3 应当只返回 D01 一行如果返回空说明你灌数据时把所有产品都订了一遍第 14 题就永远查不出东西。3. 统计查询与分组聚合COUNT、SUM 和 HAVING 的落点在哪3.1 一条 SELECT 的真实执行顺序写聚合查询之前脑子里得有一条执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。这个顺序不是教科书摆设它直接决定了你能在哪个位置引用什么。WHERE在分组之前执行所以它只能看到原始行不能写聚合函数。HAVING在分组之后执行它看到的是每组压缩后的结果可以写COUNT(*)、SUM(oquantity)。SELECT在HAVING之后这就是为什么 MySQL 允许你在HAVING里用SELECT中定义的别名这是 MySQL 的扩展标准 SQL 不允许反过来在WHERE里用别名就会报Unknown column。实验报告里第 4 题写的是SELECT SITE, COUNT(CNO) FROM CUSTOMER GROUP BY SITE HAVING SITE A市;这句能跑但它是把本该在WHERE阶段完成的过滤拖到了HAVING阶段。执行时数据库要先把整张 customer 按城市分组、算出每组的计数再丢掉不匹配的组。数据量小的时候看不出差别几百万行时这一下就是几秒的差距。等价且更规范的写法是把条件下推-- 过滤下推到 WHERE分组前就把无关行砍掉 SELECT site, COUNT(cno) AS company_cnt FROM customer WHERE site A市 GROUP BY site;这里还有个细节COUNT(cno)和COUNT(*)在这一题结果一样因为cno是主键不可能为 NULL。但如果换成COUNT(tele)而tele允许为空结果就会少算那些没填电话的客户。COUNT(*)数的是行数COUNT(列)数的是该列非 NULL 的个数这个差别在第 1 题“统计客户数目”上是踩坑高发区——只要表里有空值字段两个写法就会给出不同的数字。3.2 前八题逐条改写第一组题1、2、3、5是标准聚合基本照写即可但要养成给结果列起别名的习惯不然导出的结果集列名会是一长串COUNT(*)写进实验报告很难看。-- 第1题客户总数。COUNT(*) 数行不受任何列 NULL 影响 SELECT COUNT(*) AS customer_total FROM customer; -- 第2题至少订购两种以上产品的客户编号与种类数 -- HAVING 里筛的是“分组后的组”不是原始行 SELECT cno, COUNT(pcode) AS kind_cnt FROM sale GROUP BY cno HAVING COUNT(pcode) 2; -- 第3题每个客户订购产品数量的总数 SELECT cno, SUM(oquantity) AS total_qty FROM sale GROUP BY cno; -- 第5题所有产品的最高单价 SELECT MAX(price) AS max_price FROM product;第 2 题原文写作HAVING COUNT(PCODE)2逻辑上没问题但注意题目说的是“至少订购两种以上”。“至少两种”就是 2加上“以上”容易让人犹豫要不要写 2。这类表述在实验报告里很常见判分标准通常是 2因为“两种以上”在中文语境里含本数。写 2的话C001、C002 这两条正好两种的客户会被漏掉结果集直接变空——这种“查出来是空表”的情况八成是边界条件写反了。第 3 题的SUM(oquantity)有一个隐含前提oquantity不能为 NULL。如果建表时没加NOT NULL某些客户的整组都是 NULLSUM会返回 NULL 而不是 0。要兼容这种情况写SUM(IFNULL(oquantity, 0))或者建表时就把约束加上。我倾向前者因为约束一旦上线就很难改。第二组题4、6、7、8全部涉及GROUP BY加HAVING也是错误最集中的地方。原文第 6 题是这样的SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS 500;这句的意图是“库存量超过 500 的产品个数”但实际执行结果是先按stocks的每个不同取值分组每组算一个计数再筛掉stocks 500的组。因为样例数据里 300、800、120、600、50 各不相同每组只有一行COUNT(PCODE)全是 1最后返回两行 1 和 1。学生看到结果会以为“超过 500 的有两个产品”数字碰巧对了但返回的是两行而不是一个总数换个数据集就露馅。第 7 题同理SELECT PRICE, COUNT(PCODE) FROM PRODUCT GROUP BY PRICE HAVING PRICE BETWEEN 10 AND 20;按单价分组每个单价一组返回的是“每个价格档位有几个产品”而不是“价格在 10~20 之间的产品总数”。正确的做法是把这两题看成“先过滤、再计数”根本不需要GROUP BY-- 第6题库存超过 500 的产品个数先 WHERE 过滤再 COUNT SELECT COUNT(*) AS cnt FROM product WHERE stocks 500; -- 第7题单价 10~20 元之间的产品个数 -- BETWEEN 是闭区间含 10.00 和 20.00 SELECT COUNT(*) AS cnt FROM product WHERE price BETWEEN 10 AND 20;判断一道题该不该GROUP BY有个简单标准看题目问的是“每个 X 的 Y”还是“整体的 Y”。问“每个客户的订购总数”才分组问“超过 500 的产品有几个”是整体口径分组只会把答案拆碎。第 8 题是前八题里最容易写错的-- 原写法SELECT 列表里的 oquantity 既不在 GROUP BY 里也没被聚合 SELECT PCODE, OQUANTITY FROM SALE WHERE OQUANTITY 100 GROUP BY PCODE HAVING COUNT(PCODE) 2;这句在 MySQL 5.7 以后、开了ONLY_FULL_GROUP_BY的默认配置下会直接报错提示oquantity不在GROUP BY中且包含非聚合列。就算关掉这个模式让它跑起来oquantity取的是组内随机一行的值结果不可复现。题目要的是“产品编号、订购数量总数”正确写法-- 第8题至少被两个客户订购、且每个客户订购量都超过 100 的产品 -- 用 COUNT(DISTINCT cno) 防重复计数用 SUM 求总量 SELECT pcode, SUM(oquantity) AS total_qty, COUNT(DISTINCT cno) AS customer_cnt FROM sale WHERE oquantity 100 GROUP BY pcode HAVING COUNT(DISTINCT cno) 2;COUNT(DISTINCT cno)里的DISTINCT不能省。前面sale表用了联合主键一个客户对一个产品只有一行看似不会重复但如果哪天业务改成允许多次下单、主键换成自增 IDCOUNT(cno)就会把同一客户的多笔订单算成多个客户结论从“两个客户”变成“三笔订单”。这种字段语义随表结构漂移的坑用DISTINCT兜一层最省心。3.3 HAVING 里写条件为什么常常是错的判断条件该放WHERE还是HAVING只看一条这个条件依赖不依赖聚合结果。stocks 500、price BETWEEN 10 AND 20、site A市都是单行属性跟分组无关一律进WHERE。COUNT(pcode) 2、SUM(oquantity) 1000依赖聚合值只能进HAVING。把单行条件写进HAVING的代价不只是慢。更麻烦的是语义偏差HAVING stocks 500在按stocks分组时筛的是“组的代表值”而当分组键不是stocks时MySQL 会从组内随便挑一行来判断结果取决于存储引擎返回行的顺序。同一个 SQL 在开发机和服务器上跑出不同数字十有八九就是这里出了问题。还有一个常被忽略的点HAVING里可以引用SELECT里定义的别名WHERE里不行。比如SELECT SUM(oquantity) AS total ... HAVING total 1000是合法的而WHERE total 1000会报未知列。但这属于 MySQL 的方言扩展换到 PostgreSQL 或标准 SQL 环境就跑不通。写实验报告时如果要求“通用 SQL”建议在HAVING里重写完整聚合表达式。4. 连接与嵌套从逗号连接改写到 EXISTS 的四种解法4.1 逗号连接与显式 JOIN 的等价改写原文第 11 题用的是老式逗号连接SELECT SALE.CNO, PCODE, CNAME, OQUANTITY FROM SALE, CUSTOMER WHERE CUSTOMER.CNO SALE.CNO AND CUSTOMER.SITE A市 AND SALE.OQUANTITY 200;这种写法在教科书里很常见因为它是 1992 标准语法任何数据库都认。但它有两个现实问题一是连接条件和过滤条件混在同一个WHERE里题目一复杂就看不出哪句是“把两张表缝起来”、哪句是“筛数据”二是漏写连接条件时会静默产生笛卡尔积几万行乘几万行直接把数据库拖死而且不报错——这就是最典型的黑匣子式翻车。改成显式JOIN之后两类条件在语法位置上就分开了-- 第11题A市客户中订购量大于 200 的订单明细 SELECT c.cno, s.pcode, c.cname, s.oquantity FROM sale s JOIN customer c ON c.cno s.cno WHERE c.site A市 AND s.oquantity 200;两种写法在 MySQL 8 上会被优化器改写成同一个执行计划性能上没有差别但可读性差很多。ON后面只放连接键WHERE后面只放过滤条件一眼能看出“谁和谁连、筛掉了什么”。同时给表起短别名s、c避免在SELECT里反复写全表名——第 11 题原文的SELECT SALE.CNO, PCODE, CNAME里PCODE没带表前缀如果sale和product都有pcode列就会报Column pcode in field list is ambiguous这类报错在四表连接时非常常见。关于连接类型的选择实验里基本只会用到INNER JOIN。LEFT JOIN的典型场景是“查所有产品及其订购量没被订购的显示 0”这个需求在第 14 题附近会碰到。这里有个细节LEFT JOIN之后再在WHERE里写s.pcode IS NULL才是“找出左表有右表没有”的标准写法如果把条件写在ON里就变成了另一种语义。这个区分是面试高频题也是实际写报表时的分水岭。4.2 单行子查询、IN、ANY/ALL、EXISTS 的选型第 12 题是单行子查询的典型-- 第12题与 A公司 同城的其他客户 SELECT tele, cname FROM customer WHERE site (SELECT site FROM customer WHERE cname A公司);这句的隐含假设是子查询只返回一行。样例数据里 A公司 只有一家所以不报错。但只要业务上出现两家同名公司现实里太正常了分公司重名、录入重复子查询返回两行MySQL 立刻抛Subquery returns more than 1 row。更稳的写法有两种取决于你想要什么语义。如果“同城”指任一 A公司 所在的城市用IN-- 用 IN 容错多行返回! 或 排除自身 SELECT cno, cname, tele FROM customer WHERE site IN (SELECT site FROM customer WHERE cname A公司) AND cname A公司;如果确实只想取一条比如取编号最小的那家用LIMIT 1或者加ORDER BY cno LIMIT 1把子查询锁定成单行让意图显式化。最怕的是既没IN也没LIMIT代码在测试库跑得好好的上线遇到重复数据就炸。第 13 题是连接加子查询的混合-- 第13题订购了单价比 A01 高的产品的订单 SELECT s.pcode, s.cno, s.oquantity FROM sale s JOIN product p ON p.pcode s.pcode WHERE p.price (SELECT price FROM product WHERE pcode A01);单行子查询在这里是安全的因为pcode是主键WHERE pcode A01最多返回一行这是比cname可靠得多的前提。判断单行子查询能不能省略IN就看子查询的过滤条件是不是命中主键或唯一键——是放心用不是一律用IN兜底。除了IN标准 SQL 还提供了ANY、SOME、ALL三个量词。第 13 题也可以写成-- 用 ANY 等价改写大于子查询结果中的任意一个即大于最小值 SELECT s.pcode, s.cno, s.oquantity FROM sale s JOIN product p ON p.pcode s.pcode WHERE p.price ANY (SELECT price FROM product WHERE pcode LIKE B%); ANY等价于 MIN(...) ALL等价于 MAX(...)。这两组等价关系在实验报告里经常被拿来出题但实际项目里我很少写ANY——不是因为它不好而是因为MIN/MAX写在WHERE里可读性更好优化器也更容易利用索引。ANY的价值在于当你需要 ANY等价于IN时能表达出“和集合中任意一个相等”的语义但既然有IN也就没必要绕。EXISTS是另一条路它不返回具体值只返回真或假-- 用 EXISTS 改写第13题只要存在比 A01 贵的产品被该订单订购 SELECT s.pcode, s.cno, s.oquantity FROM sale s WHERE EXISTS ( SELECT 1 FROM product p WHERE p.pcode s.pcode AND p.price (SELECT price FROM product WHERE pcode A01) );EXISTS的子查询SELECT列表写1、*还是具体列都一样因为数据库根本不关心返回什么只关心有没有行。这一点新手经常纠结其实写SELECT 1是最能表达“我不需要值”的方式。4.3 相关子查询与 NOT EXISTS 的正确写法第 14 题原文是全文最值得说的一句-- 原写法子查询返回多行必然报错 SELECT PRODUCT.PCODE, PNAME FROM PRODUCT WHERE PRODUCT.PCODE ! (SELECT SALE.PCODE FROM SALE);这行 SQL 在任何有两条以上销售记录的库上都跑不通因为!后面跟的是一个多行结果集。就算把!换成NOT IN也只是“能跑”还埋着 NULL 的雷——如果sale.pcode里有 NULLNOT IN的比较结果会变成UNKNOWN整个查询返回空集。这是个极其隐蔽的坑很多人在数据量小时根本发现不了。三种正确写法按推荐程度排序-- 写法一NOT EXISTS最稳NULL 安全且能走反连接anti-join SELECT p.pcode, p.pname FROM product p WHERE NOT EXISTS (SELECT 1 FROM sale s WHERE s.pcode p.pcode); -- 写法二NOT IN必须保证子查询列非空 SELECT pcode, pname FROM product WHERE pcode NOT IN (SELECT pcode FROM sale WHERE pcode IS NOT NULL); -- 写法三LEFT JOIN IS NULL可读性最好适合多表场景 SELECT p.pcode, p.pname FROM product p LEFT JOIN sale s ON s.pcode p.pcode WHERE s.pcode IS NULL;NOT EXISTS和LEFT JOIN ... IS NULL在 MySQL 8 上通常会被优化成同一种反连接执行计划性能接近NOT IN则可能被改写成半连接或物化子查询具体看版本和统计信息。我在生产环境里一律优先NOT EXISTS因为它的语义最贴近“不存在”不受 NULL 影响而且嵌套层级再深也不会因为语法嵌套而难以阅读。相关子查询的执行模型值得单独理解一下。NOT EXISTS里的子查询引用了外层的p.pcode所以它不是“执行一次拿到结果集”而是“外层每取一行内层就用这行的pcode去sale里探一次”。朴素实现是 O(n×m)但优化器会把它改写成反连接用哈希或索引查找实际接近 O(nm)。理解这一点你就知道为什么在sale.pcode上建索引很重要——没索引时每一次探测都是全表扫描几条数据看不出几万条就是分钟级。顺带说一个改写思路凡是能用“集合运算”表达的就不要用“逐行判断”。第 14 题的本质是product集合减去sale里出现过的pcode集合用NOT EXISTS或LEFT JOIN表达都比逐行子查询清晰。这个思维转换一旦建立后面写复杂报表会顺很多。5. 避坑排查六条实验语句跑不通或跑偏的真实原因5.1 报错Subquery returns more than 1 row现象执行第 12 题或第 14 题时MySQL 直接抛出这个错误语句中断。原因子查询用了或!这类标量比较符但子查询本身返回了多行。第 12 题里如果cname A公司命中两家公司或者第 14 题里sale表本来就有多条记录都会触发。很多人在测试库只插了一条数据换到实验验收库就报错还以为是环境问题。解决先单独跑一次子查询看行数。行数大于 1 就把外层改成IN/NOT IN/EXISTS或者给子查询加LIMIT 1把意图写死。更根本的做法是检查过滤条件是否命中了主键或唯一键——命中才允许用。5.2 统计结果少了几行COUNT数字对不上现象第 2 题查“至少订购两种产品的客户”明明有两个客户各订了两种结果只返回一个。原因sale表建表时主键定成了(cno, pcode)但灌数据时同一客户同一产品插了两行而且没报错——说明主键没建成功或者用了INSERT IGNORE把冲突行静默丢弃了。另一种可能是COUNT(pcode)里pcode存在 NULL。解决先跑SHOW CREATE TABLE sale确认主键在不在再用SELECT cno, pcode, COUNT(*) FROM sale GROUP BY cno, pcode HAVING COUNT(*) 1找重复行。字段可能为空的场景统一用COUNT(*)或COUNT(DISTINCT 列)。5.3 表名对不上SALEGROUP与SALE现象把实验报告里的 SQL 逐条粘进去前几题能跑第 2 题突然报Table cpxs.salegroup doesnt exist。原因这份 doc 里第 2 题写的是FROM SALEGROUP第 8、11、13、14 题写的是SALE。同一份实验里出现两个表名大概率是原始数据集里salegroup是sale的一个视图或者旧表名报告作者在不同版本间复制粘贴留下的不一致。解决以建表脚本为准统一成sale。如果你确实需要兼容报告原文可以建一个视图做别名CREATE VIEW salegroup AS SELECT * FROM sale;。但更推荐直接改 SQL因为对着视图调试执行计划会多一层黑箱排查问题时容易绕远。5.4LIKE B%查出来是空集现象第 9 题“以字母 B 开头的所有产品的最低单价”数据里明明有B01、B02MIN(PRICE)却返回 NULL。原因两种可能。一是排序规则是_bin或_cs而数据里存的是小写b01B%匹配不上。二是MIN只对非空值聚合如果price列允许 NULL 且所有匹配行都是 NULL结果就是 NULL 而不是 0。解决SHOW VARIABLES LIKE collation%确认排序规则临时验证可以写WHERE UPPER(pcode) LIKE B%但注意这会让索引失效。要兼顾索引和大小写规范做法是建表时选_ci排序规则或者用pcode LIKE B% COLLATE utf8mb4_0900_ai_ci显式指定。另外MIN返回 NULL 时用IFNULL(MIN(price), 0)兜底。5.5GROUP BY报only_full_group_by错误现象第 8 题原样执行报Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column cpxs.sale.oquantity。原因MySQL 5.7 起默认开启ONLY_FULL_GROUP_BY要求SELECT列表里的非聚合列必须出现在GROUP BY中。原题的oquantity既没分组也没聚合属于典型的“语义不明确”写法。解决不要用SET sql_mode 去关掉这个模式那只是把问题藏起来。正确做法是把oquantity改成SUM(oquantity)或MAX(oquantity)明确你到底要这一组的哪个统计值。关模式是后悔药吃一次就会一直吃。5.6 连接查询结果行数暴涨现象第 11 题本来预期返回一两条订单结果出来几十行数字全是重复的。原因FROM sale, customer WHERE customer.cno sale.cno里的连接条件写漏了或者写成了WHERE customer.cno customer.cno这种自我比较恒为真的条件产生笛卡尔积。另一种情况是sale表和product表连接时product表里同一pcode存在多条记录主键没建一对多放大。解决先用SELECT COUNT(*) FROM a JOIN b ON ...单独数连接后的行数和两表各自行数比一比。连接后的行数不应该超过“一对多”里多端的行数。养成写显式JOIN的习惯连接键写在ON后面能大幅降低漏写概率。6. 用 EXPLAIN 与结果集比对验证改写是否真的等价改写完十四道题最怕的是“看着对、其实不对”。我的习惯是两步验证先看执行计划再比结果集。执行计划用EXPLAIN重点看三列type、rows、Extra。type从好到坏大致是const eq_ref ref range index ALL出现ALL就是全表扫描。rows是优化器估算的扫描行数改写前后应该持平或下降。Extra里出现Using filesort、Using temporary要留意聚合查询里临时表很常见但如果改写后多出来一个说明你的写法反而更重了。-- 对比第 14 题两种写法的执行计划 EXPLAIN FORMATTRADITIONAL SELECT p.pcode, p.pname FROM product p WHERE NOT EXISTS (SELECT 1 FROM sale s WHERE s.pcode p.pcode); EXPLAIN FORMATTRADITIONAL SELECT p.pcode, p.pname FROM product p LEFT JOIN sale s ON s.pcode p.pcode WHERE s.pcode IS NULL;两条都应该在sale上出现ref或eq_ref访问Extra里带Using where; Not exists或Using where; Using index。如果某一条的rows是全表行数、type是ALL说明索引没被用上先回去检查sale.pcode上有没有索引、字段类型和排序规则是否一致——连接两边的字符集不同会导致索引失效这个坑很隐蔽。结果集比对更直接把原写法和改写各跑一遍做对称差行数为 0 才算等价。-- 对比两版查询的差集返回 0 行才说明结果完全一致 WITH a AS ( SELECT pcode FROM product WHERE pcode NOT IN (SELECT pcode FROM sale WHERE pcode IS NOT NULL) ), b AS ( SELECT p.pcode FROM product p LEFT JOIN sale s ON s.pcode p.pcode WHERE s.pcode IS NULL ) SELECT only_in_a AS side, pcode FROM a WHERE pcode NOT IN (SELECT pcode FROM b) UNION ALL SELECT only_in_b, pcode FROM b WHERE pcode NOT IN (SELECT pcode FROM a);这里用NOT IN而不是NOT EXISTS做差集是因为两边的pcode都已经过滤过 NULL可以放心用。WITH子句在 MySQL 8.0 才支持如果你的实验环境是 5.7把两个 CTE 改写成派生表FROM (SELECT ...) a即可逻辑不变。还有一个更省事的技巧给每条查询加一个COUNT(*)外壳先比总数再比明细。总数对不上一定是过滤条件写偏了总数一样但明细不同多半是排序或者重复行的问题。这个习惯让我在排查报表数字时少走了很多弯路。从那以后我每次改完一条嵌套查询都会强制走一遍「EXPLAIN 看计划 差集比结果」这两步哪怕只是把IN换成EXISTS这种看起来绝对等价的小改动。因为等价这件事靠眼睛看是靠不住的得让数据库自己说话。希望帮到你。本文还有配套的精品资源点击获取