简介面向数据库课程学习者与实验教学场景的《数据库实验5嵌套查询》实验报告文档围绕SELECT统计查询、聚合函数、连接查询与嵌套查询四类核心技能展开适合在完成基础SQL语法后集中训练复杂查询思路与实验报告撰写。压缩包内共1个doc文件约642KB内容以实验目的、实验内容、操作示例和实验思考组织包含统计客户数量、库存总量、按客户分组计数、筛选上海客户、最高单价、LIKE前缀匹配、同城客户子查询、高于A01单价的产品以及未被订购产品等练习读者可据此对照CPXS数据库逐步理解GROUP BY、HAVING、谓词与子查询的配合方式。已有931人学习可作为课程实验参考、课后练习与查询语句自查材料。1. 从一份“数据库实验5嵌套查询.doc”说起它到底能解决什么翻开这份文档第一眼看到的是十几条针对同一套业务库的查询题统计客户数、按城市分组计数、求 B 开头产品的最低价、找和某公司在同一城市的客户、找“没被订购过”的产品。题目不难但每一道都精准踩在 SQL 学习者最容易含糊的地方——分组后过滤该写 WHERE 还是 HAVING、相关子查询和外层表怎么关联、!接子查询为什么会翻车。这份实验文档的价值就在于它把统计查询、连接查询、嵌套查询三条线拧在同一套 CPXS 数据上反复练而不是各讲各的。它适合两类人一是正在跟数据库实验课、需要交实验报告但 SQL 写不动的学生二是工作里天天写 CRUD、一遇到“查没有下单的客户”“查高于均价的商品”就要翻旧代码的开发者。下面按“先把表结构立住、再逐类拆查询、最后避坑”的顺序走一遍照着敲能复现参数改动能套到自己库上。2. 先把 CPXS 四张表立住字段、关系与建库脚本嵌套查询写不出来八成不是语法问题是没记住表和表之间靠谁连。这份实验从头到尾只用了四张表客户表 CUSTOMER、产品表 PRODUCT、销售表 SALE题目里还隐含一个“订购明细”的语义。先把结构固化成可执行的建库脚本后面每条查询才有落点。2.1 四张表的字段语义与关联键从实验里的 SQL 反推字段职责大致是这样表名关键字段含义与其他表的关系CUSTOMERCNO、CNAME、SITE、TELE客户编号、公司名、所在城市、联系电话CNO 被 SALE 引用PRODUCTPCODE、PNAME、PRICE、STOCKS产品编号、名称、单价、库存量PCODE 被 SALE 引用SALECNO、PCODE、OQUANTITY哪个客户订了哪个产品、订了多少同时引用 CUSTOMER 和 PRODUCT关联键只有两个CUSTOMER.CNO SALE.CNO和PRODUCT.PCODE SALE.PCODE。实验里第 11 题写的是FROM SALE,CUSTOMER WHERE CUSTOMER.CNOSALE.CNO这就是最朴素的隐式连接第 13 题写的是FROM SALE,PRODUCT WHERE SALE.PCODEPRODUCT.PCODE同一个套路。把这两条等值条件记住多表查询就不会写出笛卡尔积。提示原文档第 12 题的 SELECT 列表写成SELECT TELE CNAME少了个逗号实际应为SELECT TELE, CNAME。这类排版错误在 .doc 转出来的 SQL 里很常见抄之前先扫一眼逗号。2.2 建库建表与样例数据不同数据库对自增、字符类型的要求不同下面用通用性最好的写法MySQL、openGauss、人大金仓这类都能直接跑SQL Server 只需把VARCHAR换成NVARCHAR即可。-- 客户表CNO 为主键SITE 用于按城市分组 CREATE TABLE CUSTOMER ( CNO VARCHAR(10) PRIMARY KEY, CNAME VARCHAR(50) NOT NULL, SITE VARCHAR(30), TELE VARCHAR(20) ); -- 产品表STOCKS 库存量PRICE 单价后面的聚合查询都围绕它们 CREATE TABLE PRODUCT ( PCODE VARCHAR(10) PRIMARY KEY, PNAME VARCHAR(50) NOT NULL, PRICE DECIMAL(10,2), STOCKS INT ); -- 销售表CNOPCODE 联合主键表示同一客户对同一产品只留一条汇总记录 CREATE TABLE SALE ( CNO VARCHAR(10), PCODE VARCHAR(10), OQUANTITY INT, PRIMARY KEY (CNO, PCODE), FOREIGN KEY (CNO) REFERENCES CUSTOMER(CNO), FOREIGN KEY (PCODE) REFERENCES PRODUCT(PCODE) ); -- 样例数据要覆盖实验里的边界上海/非上海、B 开头产品、单价 10~20 之间、库存超 500 INSERT INTO CUSTOMER VALUES (C01,美美,上海,021-1111), (C02,华信,上海,021-2222), (C03,北方商贸,北京,010-3333), (C04,西域商行,西安,029-4444); INSERT INTO PRODUCT VALUES (A01,打印机,1200.00,80), (A02,硒鼓, 18.50, 600), (B01,网线, 12.00, 900), (B02,交换机,980.00,300), (C01,鼠标, 15.00, 520); INSERT INTO SALE VALUES (C01,A01,150), (C01,A02,300), (C02,A01,220), (C02,B01,180), (C03,A01,90);联合主键(CNO, PCODE)是个关键设计实验第 2 题GROUP BY CNO HAVING COUNT(PCODE)2求“订购两种以上产品”的客户前提就是同一客户对同一产品只有一行。如果表里允许重复行COUNT 数的是订单条数而不是产品种类数结果直接失真。这一点在换到真实业务表时必须先确认否则聚合口径全是错的。数据插入顺序也有讲究SALE 有外键指向另外两张表必须先插 CUSTOMER 和 PRODUCT否则会报外键约束错误。库存量和单价特意造了几条边界值——A02 库存 600、B01 库存 900 覆盖“库存超 500”A02 单价 18.50、C01 单价 15.00 覆盖“10~20 之间”B01、B02 覆盖LIKE B%。样例数据不是随手编的是照着后文的每道题反推出来的。2.3 先验证关联是否成立建完表别急着写复杂查询先用一条两表连接看看数据能不能对上-- 验证 SALE 与 CUSTOMER、PRODUCT 的关联是否完整 SELECT s.CNO, c.CNAME, s.PCODE, p.PNAME, s.OQUANTITY FROM SALE s JOIN CUSTOMER c ON c.CNO s.CNO JOIN PRODUCT p ON p.PCODE s.PCODE ORDER BY s.CNO, s.PCODE;这条语句跑出来应该有 5 行和 SALE 的行数一致。如果行数变多说明某张表主键不唯一连接被放大了如果变少说明有孤儿记录外键没生效。我一般会把这个检查当成“后悔药”先跑一遍后面嵌套查询结果可疑时回到这一步比对原始明细能立刻判断是子查询写错还是数据本身有问题。3. 统计查询与分组过滤COUNT/SUM/MAX 的落地写法实验前半段第 1 到第 9 题全是聚合函数加分组看似简单但每道题都在考同一个判断过滤条件该放在分组前还是分组后。3.1 聚合函数与 GROUP BY 的执行顺序理解顺序比背语法有用。一条带分组的查询逻辑执行顺序是FROM → WHERE → GROUP BY → 聚合函数 → HAVING → SELECT → ORDER BY。WHERE 在分组之前逐行过滤所以它后面不能跟聚合函数HAVING 在分组之后过滤所以它可以直接用COUNT(*)、SUM(...)。实验第 6 题写的是-- 求库存量超过 500 的产品个数按库存量分组后过滤 SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS 500;这条 SQL 能跑但语义值得推敲它先按库存量把产品分成若干组再筛出库存量大于 500 的组最后每组各返回一个计数。如果两个产品库存都恰好是 600它们会被分到同一组结果是 2 而不是两条 1。想表达“库存超 500 的产品有多少个”更准确的写法是-- 更贴合题意的写法先过滤再计数 SELECT COUNT(PCODE) AS over_500_cnt FROM PRODUCT WHERE STOCKS 500;WHERE STOCKS 500是行级过滤不涉及分组语义就是“库存大于 500 的行有几条”。原文那种写法在结果上可能碰巧一致但换个数据分布就会露馅。这类“能跑但语义偏”的语句是实验文档里最值得拿来当反面教材的部分。3.2 HAVING 的正确用法按分组结果筛真正的 HAVING 场景是第 2 题和第 8 题——条件本身就是聚合值。-- 至少订购两种以上产品的客户编号和产品种类数 SELECT CNO, COUNT(PCODE) AS prod_kinds FROM SALE GROUP BY CNO HAVING COUNT(PCODE) 2;这里的HAVING COUNT(PCODE) 2没法挪到 WHERE因为 WHERE 执行时还没分组拿不到“每组的种类数”。同理第 8 题-- 至少被两个以上客户订购、且每个客户订购数量超过 100 的产品 SELECT PCODE, SUM(OQUANTITY) AS total_qty FROM SALE WHERE OQUANTITY 100 GROUP BY PCODE HAVING COUNT(PCODE) 2;这条的层次要看清WHERE OQUANTITY 100先剔掉小单剩下的大单再按产品分组HAVING COUNT(PCODE) 2才筛“被两个以上客户订购”。原文这里写的是SELECT PCODE, OQUANTITY而 OQUANTITY 既不在 GROUP BY 里也没被聚合在开启ONLY_FULL_GROUP_BY的 MySQL 上会直接报错。改成SUM(OQUANTITY)才是稳定可移植的写法。注意MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BYSELECT 列表里出现的非聚合列必须出现在 GROUP BY 中。实验文档里有多条语句违反这条规则比如第 7 题的SELECT PRICE, COUNT(PCODE) ... GROUP BY PRICE——这条其实是合规的因为 PRICE 在 GROUP BY 里但第 12 题、第 8 题的原始写法就未必。抄作业前先看自己库的sql_mode。3.3 字符串匹配与区间统计第 9 题和第 7 题是两类常用过滤的模板。LIKE B%里的%匹配任意长度字符性能上要注意前缀匹配能用上索引%B这种前后都带通配的用不上。区间统计优先用BETWEEN ... AND ...它等价于 AND 但可读性更好-- 以字母 B 开头的所有产品的最低单价 SELECT MIN(PRICE) AS min_price FROM PRODUCT WHERE PCODE LIKE B%; -- 单价在 10~20 元之间产品的个数过滤在分组前用 WHERE 更直接 SELECT COUNT(PCODE) AS price_band_cnt FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20;第二条如果真要做成分组统计比如按价格档看分布才需要GROUP BY PRICE HAVING PRICE BETWEEN 10 AND 20但那样返回的是“每个具体价格各有多少个产品”不是“区间内总共有多少个”。题目问的是后者所以 WHERE 版本才对。3.4 多表连接统计的写法取舍第 11 题开始把连接和统计混在一起-- 上海客户订购且数量大于 200 的客户编号、产品编号、联系人和数量 SELECT s.CNO, s.PCODE, c.CNAME, s.OQUANTITY FROM SALE s JOIN CUSTOMER c ON c.CNO s.CNO WHERE c.SITE 上海 AND s.OQUANTITY 200;原文档用的是逗号连接加 WHERE 等值条件功能等价但JOIN ... ON把连接条件和过滤条件分开写读起来层次清楚也不会漏掉连接条件导致笛卡尔积。工程里我一般统一用显式 JOIN团队协作时少一类低级错误。这条语句返回的是明细行不需要 GROUP BY如果题目改成“上海客户订购总量”才需要加SUM(OQUANTITY)和GROUP BY s.CNO。三个统计函数的适用边界也顺带说清COUNT(*) 数行数COUNT(列) 跳过该列为 NULL 的行两者在有空值时结果不同SUM 和 AVG 忽略 NULL如果整组全为 NULL 则返回 NULL 而非 0报表里做除法时要留意MAX/MIN 对字符串是按排序规则比较对日期是取最晚/最早别想当然。4. 嵌套查询三类写法标量子查询、IN 与 NOT EXISTS实验后半段第 12 到第 14 题是嵌套查询的主场。三道的难度是递进的等值比较的标量子查询、多值比较的 IN、以及取反的“没有被订购”。4.1 标量子查询返回单值的子查询第 12 题是最好懂的一种-- 查询与美美公司在同一城市的客户公司名称及联系电话 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE (SELECT SITE FROM CUSTOMER WHERE CNAME 美美);子查询(SELECT SITE FROM CUSTOMER WHERE CNAME 美美)必须只返回一个值否则外层会报“子查询返回多于一行”。这里能成立是因为 CNAME 在业务上唯一如果表里存在两家同名的“美美”这条 SQL 立刻翻车。稳妥做法是加LIMIT 1或者改用 IN-- 防御写法同名公司不唯一时也不会报错只是结果可能变多 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE IN (SELECT SITE FROM CUSTOMER WHERE CNAME 美美);另外别忘了结果里会包含“美美”自己如果要排除加AND CNAME 美美。实验文档没提这茬但真实报表里“找出同城同行”通常是要排除自己的。第 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);子查询先算出 A01 的单价 1200.00外层再筛出单价高于它的销售明细。注意外层必须连 PRODUCT 表因为单价在 PRODUCT 上、订购量在 SALE 上。原文档写成FROM SALE,PRODUCT WHERE SALE.PCODEPRODUCT.PCODE AND PRICE(...)注意外层PRICE没加表前缀——只要 PRODUCT 是唯一带 PRICE 列的表就不报错但一旦子查询里也涉及 PRICE解析器可能产生歧义。加表别名是最省心的习惯。4.2 IN 与 NOT IN多值比较和它的 NULL 陷阱第 14 题表面简单坑最深-- 查询没有被订购的产品的编号和名称原文写法有隐患 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE ! (SELECT PCODE FROM SALE);这条 SQL 在 SALE 有多行时必然报错!是标量比较右侧子查询返回多行直接失败。实验文档里这条大概率跑不通属于典型的教学材料笔误。正确的两种写法-- 写法一NOT IN语义直观 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE); -- 写法二NOT EXISTS空值安全大表上通常也更稳 SELECT p.PCODE, p.PNAME FROM PRODUCT p WHERE NOT EXISTS ( SELECT 1 FROM SALE s WHERE s.PCODE p.PCODE );NOT IN 的致命陷阱是 NULL如果子查询结果里含 NULLx NOT IN (..., NULL)的三值逻辑会让整个条件既不真也不假最终一行都不返回。SALE.PCODE 因为有外键和非空约束一般不会有 NULL但只要换成一张可能为空的表这就是血泪坑。NOT EXISTS 不受影响因为它判断的是“关联行存在与否”NULL 不参与比较。我现在的习惯是子查询列可能为空时一律用 NOT EXISTS。4.3 相关子查询子查询引用外层列相关子查询是嵌套查询里最需要建立直觉的一类子查询不是先算一次而是外层每行都带着自己的值进去算一次。第 13 题其实已经隐含这种结构更典型的场景是“查出每个客户订购量高于该客户平均订购量的记录”-- 相关子查询外层每一行都把 c.CNO 传给子查询 SELECT s.CNO, s.PCODE, s.OQUANTITY FROM SALE s WHERE s.OQUANTITY ( SELECT AVG(s2.OQUANTITY) FROM SALE s2 WHERE s2.CNO s.CNO -- 这一句把内外层绑定起来 );WHERE s2.CNO s.CNO就是“相关条件”去掉它子查询就退化成全局平均语义完全变了。相关子查询在逻辑上清晰但执行时可能对外层每行都跑一遍子查询数据量大时要看执行计划常见做法是改写成 JOIN 加派生表或者在支持的库上用窗口函数AVG(...) OVER (PARTITION BY CNO)一次算完。4.4 三值逻辑与 EXISTS 的取舍SQL 的逻辑不是二值是 TRUE / FALSE / UNKNOWN 三值。NULL NULL结果是 UNKNOWNWHERE只放行 TRUE所以NOT IN遇 NULL 全灭而NOT EXISTS只关心“有没有行”。这个差异在实验第 14 题上体现得最典型用!写法是语法/语义双重错误用NOT IN能跑但怕 NULL用NOT EXISTS最稳。写法适用场景主要风险 (子查询)子查询确定返回单值多行报错、多值为 NULL 时结果为 UNKNOWNIN (子查询)子查询返回一列多值子查询含 NULL 时匹配不到该行NOT IN排除一列多值子查询含 NULL 时整体返回空集NOT EXISTS判断关联行是否存在写法略长需正确写关联条件改写成 EXISTS 时最常见的错误是漏写关联条件变成SELECT 1 FROM SALE这种与外表无关的子查询——只要 SALE 非空结果就恒真整条查询退化成全表扫描。写完先拿小数据集对一遍行数能省不少排查时间。5. 避坑与排查这份实验里最容易翻车的五处实验文档本身有笔误不同数据库行为又有差异照抄跑不通是常态。下面五条是被问得最多的按“现象 → 原因 → 解决”写。5.1 第 14 题报“子查询返回多于一行”现象执行WHERE PCODE ! (SELECT PCODE FROM SALE)直接报错提示子查询返回多行。原因!后面只能接标量而 SALE 里每个产品可能对应多条销售记录子查询天然返回多行。这是实验文档的写法问题不是环境问题。解决改用NOT IN或NOT EXISTS如 4.2 节所示。若坚持用比较运算符必须给子查询加聚合或LIMIT 1但语义就不是“所有未订购产品”了。5.2 GROUP BY 报“不是 GROUP BY 表达式”现象SELECT CNO, COUNT(PCODE) FROM SALE GROUP BY CNO能跑但把 COUNT 换成 OQUANTITY 就报错。原因MySQL 5.7 默认ONLY_FULL_GROUP_BYSELECT 里的非聚合列必须出现在 GROUP BY 中。实验第 8 题原始写法正好踩线。解决把非聚合列改成聚合如SUM(OQUANTITY)或把它加进 GROUP BY。不建议为了跑通去关sql_mode那只是把错误藏起来。查当前设置SELECT sql_mode;。5.3 中文条件查不出数据现象WHERE SITE 上海明明表里有“上海”结果为空或乱码。原因客户端、连接、表三处字符集不一致。常见是表建成了 latin1或用\G输出时客户端编码不对。解决建库时统一CHARACTER SET utf8mb4连接串显式指定字符集排查用SHOW VARIABLES LIKE character_set%;对比服务端和客户端。SQL Server 里则注意NVARCHAR加N上海前缀。5.4 连接后行数变多现象两表连接查出来的记录比 SALE 表行数还多聚合结果跟着虚高。原因连接键不唯一比如 CUSTOMER 中 CNO 有重复或 SALE 中同一客户同一产品有多条记录JOIN 时一对多被放大。解决先跑SELECT CNO, COUNT(*) FROM CUSTOMER GROUP BY CNO HAVING COUNT(*)1;找出重复键确认业务上是否真需要去重。聚合前先看清基数是避免统计失真的第一步。5.5 子查询能跑但结果对不上现象单独跑子查询有结果套进外层查询却是空集。原因相关子查询的关联条件写错内外层列绑错表或者NOT IN子查询列里有 NULL整条条件变 UNKNOWN。解决把子查询单独拎出来看返回值特别是有没有 NULL相关子查询把关联条件注释掉对比结果差异能快速定位是绑定问题还是数据问题。这一步花两分钟比盯着外层 SQL 猜半小时有效。6. 从实验题到生产查询改写与验证的两个习惯这份文档练的是查询本身但真正把 SQL 写稳靠的是两条习惯先验证基数再做等价改写。第一条是验证基数。任何带 JOIN 或子查询的语句动手前先确认参与连接的键唯一性。我的固定动作是三条检查SELECT COUNT(*) FROM 主表;、对连接键跑GROUP BY ... HAVING COUNT(*)1、把连接结果和主表行数对比。实验里第 11 题那种“SALE 连 CUSTOMER 再连 PRODUCT”的查询只要 CNO 和 PCODE 都是主键行数就恒等于 SALE 行数结果可信一旦换成真实业务表这两条唯一性往往不成立不先查就会得到看似合理实则翻倍的统计结果。第二条是等价改写。同一道题至少有三种写法性能差异可能很大。以“没被订购的产品”为例-- 写法 ANOT IN遇 NULL 会静默返回空集 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE); -- 写法 BNOT EXISTS空值安全 SELECT p.PCODE, p.PNAME FROM PRODUCT p WHERE NOT EXISTS (SELECT 1 FROM SALE s WHERE s.PCODE p.PCODE); -- 写法 CLEFT 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; -- 三者在同一份数据上应该返回完全相同的行先对行数再谈性能三种写法在当前样例数据上都应返回 1 行C01 鼠标未被任何客户订购。改写的验证方法是先在小数据集上对齐结果行数和内容再拿EXPLAIN看执行计划。MySQL 里NOT EXISTS和LEFT JOIN ... IS NULL常被优化成同类计划而NOT IN在子查询列可空时不仅结果错还可能走不上索引。再说一个实验文档没覆盖但生产里必踩的点嵌套层数。三层以上的嵌套查询可读性急剧下降维护成本高。我的经验法则是超过两层就把内层抽成 CTEWITH ... AS或者临时表一是好调试每层单独跑二是优化器有时能对 CTE 做更好的物化决策。openGauss、PostgreSQL、SQL Server 都支持 CTEMySQL 8.0 也支持写起来比嵌套清爽得多-- 用 CTE 把订购量高于客户平均的逻辑拆开便于逐层验证 WITH cust_avg AS ( SELECT CNO, AVG(OQUANTITY) AS avg_qty FROM SALE GROUP BY CNO ) SELECT s.CNO, s.PCODE, s.OQUANTITY FROM SALE s JOIN cust_avg a ON a.CNO s.CNO WHERE s.OQUANTITY a.avg_qty;这条和 4.3 节的相关子查询版本结果一致但拆成两步后cust_avg可以单独跑出来核对每个客户的平均值出问题一眼能看出是哪一层。从那以后我每次拿到一份实验文档或旧 SQL都会先把建表脚本和数据跑一遍、确认基数再把嵌套改成可分层验证的写法最后才谈优化。这套动作看着笨但能挡住九成的“结果看着对其实错”的坑。这份嵌套查询实验的价值也正在这里题目小、边界清正好用来把这套习惯练成肌肉记忆。希望帮到你。本文还有配套的精品资源点击获取