简介《数据库实验5嵌套查询》是一份面向数据库课程学习者与实验课教师的实验报告文档围绕 SELECT 统计查询、常用统计函数、连接查询与嵌套查询展开帮助读者理解多表检索、子查询、派生表及相关谓词的使用场景。文档以 CPXS 数据库为操作对象结合客户、产品、销售等表给出统计客户数量、求库存总量、按客户分组汇总、筛选上海客户订单、比较产品单价、查找同城客户、查询未被订购产品等 SQL 实例并配有实验目的、内容、结论与思考便于对照课堂进度完成实验和复盘语法。资源包共 1 个 doc 文件大小约 642KB结构完整、可直接查阅。目前已有 931 人学习适合需要巩固数据库查询基础、练习嵌套查询写法与理解连接查询差异的读者参考。1. 从一次结果对不上的统计说起这套查询方案要解决什么实验报告里 14 条查询跑完第 12 题返回了 3 行第 14 题返回 0 行第 8 题的数字和手工算的对不上。这类统计查询与嵌套查询的实验丢分点几乎从不在语法报错上——数据库会把错的结果安安静静地返回给你一个字都不提醒。这套方案要解决的就是这件事把 CPXS 库的客户、产品、订购三张表按「单表聚合 → 分组过滤 → 多表连接 → 嵌套子查询」四层拆开每条实验要求先定位到层再落 SQL最后用反例自证结果。适合正在交数据库实验报告、课程设计里要写查询模块或者要给一组人统一 SQL 口径的读者。反直觉的地方在于14 道题里真正需要嵌套的只有 4 道剩下的大半用 GROUP BY 加 HAVING 就能收工上来就套子查询反而容易把统计口径写歪。2. 数据模型与查询分层设计三张表、四类查询的职责划分动手写 SQL 之前先把表之间的关系画清楚比急着敲 SELECT 划算得多。CPXS 这个库的结构不复杂但三张表之间的连接路径有两条客户到订购是一对多产品到订购也是一对多客户和产品之间没有直接外键必须经过 SALE 这张中间表。搞不清这一点后面写「上海客户订购了哪些产品」这种题就会条件写漏。2.1 CPXS 库的三张表与字段约定先把字段语义固定下来后面所有查询的口径都以这张表为准。实验报告里最容易出问题的地方往往不是语法而是对字段含义的理解偏差比如 STOCKS 到底是「库存件数」还是「库存批次」直接决定 COUNT 里该写星号还是写列名。表名字段含义约束CUSTOMERCNO客户编号主键CUSTOMERCNAME客户公司名称非空CUSTOMERSITE所在城市用于同城查询CUSTOMERTELE联系电话可为空PRODUCTPCODE产品编号主键字母开头PRODUCTPNAME产品名称非空PRODUCTPRICE单价数值型PRODUCTSTOCKS库存量数值型SALECNO客户编号外键指向 CUSTOMERSALEPCODE产品编号外键指向 PRODUCTSALEOQUANTITY订购数量数值型三张表的基数关系是这套方案的骨架一个客户可以有多条订购记录一个产品也可以被多个客户订购SALE 是典型的多对多桥接表。凡是题目里出现「每个客户」「每种产品」这类措辞基本就落在分组查询上出现「和……在同一城市」「比……单价高」这类措辞基本就落在嵌套查询上。这个判断习惯一旦建立选题型的时间能从几分钟压到几秒。2.2 把 14 条实验要求拆成四层查询能力实验指导书给的是 14 条零散要求直接一条条写会写成流水账报告里也看不出设计思路。我一般会先做一次归类把它们映射到四层能力上每一层对应一组语法点这样报告的结构和代码的组织能对齐。层级能力对应题目核心语法第一层单表聚合客户数目、库存总量、最高单价、B 开头最低单价COUNT / SUM / MAX / MIN第二层分组过滤订购两种以上的客户、每客户订购总量、上海客户数、库存超 500 的产品数GROUP BY HAVING第三层多表连接上海客户订购明细、高于 A01 单价的产品订购等值连接、三表连接第四层嵌套子查询同城客户、未被订购的产品标量子查询、IN、NOT EXISTS这份映射表是整套方案的核心设计产物它同时解决三个问题报告里「实验设计」一节有东西可写写代码时知道该用哪组语法不会拿连接查询硬凑子查询的题验收时能按层逐个对答案出错范围立刻缩小到某一层。我在带课程设计时通常要求先交这张表再交 SQL返工率能降一半。有一点需要说明层级不是难度的严格排序而是依赖关系的排序。第一层的聚合函数会出现在第二层的 HAVING 里第二层的结果又常被第四层拿来当条件。所以写的时候按层推进别跳着写。2.3 建表与样例数据让每条查询都能复现实验环境里库是现成的但报告要能复现最好把建表和最小样例数据一起写进去。数据量不用大每张表五六行足够暴露问题——尤其是同城客户和未被订购产品这两类题数据太少会得到「恰好也对」的假象。-- CPXS 实验库三张表 最小可复现数据集 CREATE TABLE CUSTOMER ( CNO CHAR(4) PRIMARY KEY, -- 客户编号固定 4 位 CNAME VARCHAR(30) NOT NULL, -- 公司名称同城判断靠 SITE SITE VARCHAR(20), -- 所在城市 TELE VARCHAR(20) -- 联系电话允许为空 ); CREATE TABLE PRODUCT ( PCODE CHAR(4) PRIMARY KEY, -- 产品编号B 开头用于 LIKE 测试 PNAME VARCHAR(30) NOT NULL, PRICE DECIMAL(8,2), -- 单价嵌套比较的对象 STOCKS INT -- 库存量统计题的分组/过滤字段 ); CREATE TABLE SALE ( CNO CHAR(4), -- 外键客户 PCODE CHAR(4), -- 外键产品 OQUANTITY INT, -- 订购数量 PRIMARY KEY (CNO, PCODE), -- 联合主键同一客户同一产品只一条 FOREIGN KEY (CNO) REFERENCES CUSTOMER(CNO), FOREIGN KEY (PCODE) REFERENCES PRODUCT(PCODE) ); -- 样例数据刻意构造同城、未订购、单价交叉三种情形 INSERT INTO CUSTOMER VALUES (C01,美美公司,上海,021-1111), (C02,东方商行,上海,021-2222), (C03,北方物资,北京,010-3333); INSERT INTO PRODUCT VALUES (A01,打印机, 1200.00, 300), (B01,硒鼓, 180.00, 620), (B02,墨盒, 15.00, 480), (C01,扫描仪, 900.00, 120); INSERT INTO SALE VALUES (C01,A01, 150), (C01,B01, 260), (C02,A01, 220), (C02,B02, 400), (C03,B01, 130);建表脚本里有两个设计取舍值得在报告里点一句。第一SALE 用了 (CNO, PCODE) 联合主键这意味着同一客户对同一产品只有一条汇总记录所以后面统计「订购种类数」时 COUNT(PCODE) 就是种类数不用加 DISTINCT如果实际业务允许多批次下单主键要加流水号那时 COUNT 就得改成 COUNT(DISTINCT PCODE)。第二样例数据里刻意让 C01 和 C02 都在上海这样第 12 题的同城查询会返回多行正好能暴露「用 接子查询」的隐患。外键约束在这里不只是规范问题。少了外键SALE 里可以插入不存在的客户编号连接查询就会静默丢行结果少了几条你根本发现不了。这类问题在小数据集上尤其隐蔽因为你怎么看都觉得「数字挺合理的」。3. 统计查询与分组聚合函数、GROUP BY 与 HAVING 的落地写法统计类题目看着最没技术含量实际上丢分最多。原因很统一聚合函数把多行压成一行你没法像普通查询那样一眼看出哪行错了只能靠口径推演。这一章把 14 条要求里属于第一层和第二层的部分拆开写每条给出可执行 SQL 和一句口径说明。3.1 五个聚合函数在题目里的分工COUNT、SUM、MAX、MIN、AVG 这五个函数实验里真正用到的场景可以归成三类计数类、求和类、极值类。分工搞清楚了题目读完就知道该拿哪个。计数类用的是 COUNT。它的坑在参数上COUNT() 统计行数COUNT(列名) 统计该列非空的行数两者在有 NULL 的列上结果不同。实验里「统计客户的数目」这种题标准写法就是 COUNT()语义是「有多少条客户记录」不关心哪个字段是否为空。求和类用的是 SUM只对数值列有效且自动忽略 NULL。求「库存量总和」「每客户订购总量」都属于这一类。极值类用 MAX 和 MIN除了数值列也能用在字符列上——按字符序比较所以「B 开头产品的最低单价」是先 LIKE 过滤再取 MIN不是反过来。AVG 在这 14 条里没有直接出现但答辩时经常被追问「平均订购量怎么算」标准答案要补一句「AVG 会忽略 NULL如果某条记录数量为空分母会变小」这是它和 SUM/COUNT 手动相除的差别。3.2 GROUP BY 与 HAVING 的执行顺序为什么 WHERE 里不能写 COUNT这是整个实验里最值得讲清楚的一个知识点也是报告「实验原理」一节最容易写空的地方。SQL 的逻辑执行顺序大致是FROM → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → ORDER BY。放到分组查询里含义很明确WHERE 在分组之前生效过滤的是原始行所以它不能引用聚合函数HAVING 在分组之后生效过滤的是分组结果所以它可以引用 COUNT、SUM 这类聚合值。同理SELECT 里出现的非聚合列必须出现在 GROUP BY 里否则这一列取的是组内哪一行的值取决于具体实现。-- 正确分层先 WHERE 过滤原始行再 GROUP BY 分组最后 HAVING 过滤组 SELECT CNO, COUNT(PCODE) AS 产品种类数 FROM SALE WHERE OQUANTITY 0 -- 第一道闸过滤无效订购行 GROUP BY CNO -- 按客户分组 HAVING COUNT(PCODE) 2; -- 第二道闸过滤组聚合函数可出现在这里这段 SQL 的价值不在语法而在顺序感。很多人写分组查询时把条件一股脑塞进 HAVING虽然结果常常也对但等于让数据库先分组再丢弃整组效率差且语义含混。判断标准很简单条件针对「单行」就放 WHERE针对「一组」就放 HAVING。OQUANTITY 0是单行条件COUNT(PCODE) 2是组条件各归各位。参数上要注意的是GROUP BY 后面可以跟多列形成多级分组HAVING 里可以出现 SELECT 中没写出来的聚合函数ORDER BY 里的别名在大多数数据库里可用但标准只保证 SELECT 列表中的列别名在 ORDER BY 可见跨库移植时建议直接重写表达式。3.3 逐题实现从客户数目到每客户订购总量下面这组 SQL 对应第一层和第二层的前半部分每条后面跟一句口径说明。写法上我倾向显式写 AS 别名报告里结果集有列名答辩时对着结果讲比对着代码讲快。-- 1. 统计客户数目COUNT(*) 统计行数不受任何列 NULL 影响 SELECT COUNT(*) AS 客户总数 FROM CUSTOMER; -- 2. 求库存量总和SUM 自动忽略 NULL结果是一行一列 SELECT SUM(STOCKS) AS 库存总量 FROM PRODUCT; -- 3. 至少订购两种产品的客户编号与种类数 SELECT CNO, COUNT(PCODE) AS 种类数 FROM SALE GROUP BY CNO HAVING COUNT(PCODE) 2; -- 4. 每个客户订购产品数量的总数 SELECT CNO, SUM(OQUANTITY) AS 订购总量 FROM SALE GROUP BY CNO; -- 5. 所在城市为上海的客户公司数 SELECT SITE, COUNT(CNO) AS 客户数 FROM CUSTOMER GROUP BY SITE HAVING SITE 上海;第 3 条和第 4 条是一对同样的 GROUP BY一个数种类、一个数总量。这里用 COUNT(PCODE) 而不是 COUNT(*)是因为 SALE 的联合主键保证 PCODE 非空两者结果相同但写列名更能表达「数产品种类」的意图。第 5 条值得单独说HAVING SITE 上海在功能上等价于WHERE SITE 上海因为 SITE 是分组列。更规范的写法是放 WHERE因为它在分组前就把非上海的行排除了分组规模更小。报告里可以把两种写法都列出来说明结果一致但执行路径不同这是加分项。如果题目要的是「每个城市的客户数」而不限定上海那就去掉 HAVING直接 GROUP BY SITE结果会返回北京和上海两行。这个改写练习建议做一遍能立刻分清「过滤组」和「过滤行」的区别。3.4 范围条件与模糊匹配BETWEEN、LIKE 与聚合前过滤剩下几条统计题的核心不是聚合而是聚合之前的行过滤。单价在 10 到 20 元之间的产品个数、以 B 开头的产品最低单价、库存超过 500 的产品个数都属于这一类。这类题的通用形态是「先缩小行集再统计」所以过滤条件应该落在 WHERE 上不是 HAVING也不是 GROUP BY。-- 6. 单价在 10~20 元之间的产品个数闭区间含两端 SELECT COUNT(*) AS 产品个数 FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20; -- 7. 以字母 B 开头的所有产品的最低单价 SELECT MIN(PRICE) AS 最低单价 FROM PRODUCT WHERE PCODE LIKE B%; -- 8. 库存量超过 500 的产品个数 SELECT COUNT(*) AS 产品个数 FROM PRODUCT WHERE STOCKS 500; -- 9. 所有产品的最高单价 SELECT MAX(PRICE) AS 最高单价 FROM PRODUCT;第 6 条的 BETWEEN 是闭区间等价于PRICE 10 AND PRICE 20边界值会算进去。如果题目要求开区间必须手写成和别指望 BETWEEN 帮你排除端点。第 7 条的 LIKE B% 里百分号匹配任意长度字符下划线匹配单个字符这两个通配符的区别建议在报告里写一句答辩常问。第 8 条是最容易写歪的一条。我见过不少报告写成GROUP BY STOCKS HAVING STOCKS 500再 COUNT那样得到的是「每种库存值各有多少个产品」是一堆行不是总数。只要题目问的是「个数」而不是「每种多少个」就用 WHERE 过滤加 COUNT(*)不需要 GROUP BY。判断依据还是那句题目要一行结果就别分组。4. 连接查询与嵌套查询从等值连接到 EXISTS 的选型第三层和第四层是这份实验的重点也是标题里「嵌套查询」四个字的落点。但嵌套不是越多越好很多题写成连接反而更清楚。这一章先把连接和子查询的适用边界划出来再逐题落地。4.1 三表连接的写法与笛卡尔积风险CPXS 里最典型的三表题是「查询由上海客户订购的、且订购数量大于 200 的所有客户编号、产品编号、联系人和订购数量」。这条要求横跨 CUSTOMER 和 SALE 两张表条件分属两表城市在 CUSTOMER数量在 SALE。-- 10. 上海客户订购且数量 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; -- 过滤条件数量 -- 11. 高于 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);第 10 条的关键是连接条件必须写全。漏掉C.CNO S.CNO的后果不是报错而是笛卡尔积——3 个客户乘 5 条订购返回 15 行其中大部分是客户和订购记录错配出来的假数据。更麻烦的是这种错误在小数据集上返回的行数看着也「不算离谱」很容易被当成正确答案交上去。第 11 条演示了连接和子查询的组合用法外层用连接把订购明细和产品价格拉到一起内层用标量子查询取出 A01 的单价当比较基准。这里子查询返回单值所以能用直接比。如果 A01 在产品表里有两条记录这条 SQL 会直接报「子查询返回多于一行」这也是为什么 PCODE 必须是主键。写连接时我一般的习惯是先写 FROM 和 JOIN把连接条件补齐跑一次看行数对不对再加 WHERE 条件逐条筛。这样出错时能立刻定位是连接错了还是过滤错了。4.2 标量子查询、IN、ANY/ALL、EXISTS 的适用边界嵌套查询这一块的语法点密集实验要求里提到的操作符和谓词基本都要覆盖到。按返回形态可以分成四类选型逻辑很清楚子查询形态返回值配套运算符典型场景标量子查询单行单列 和 A01 单价比较多行单列一列多行IN / NOT IN同城客户、未被订购产品多行单列 量词一列多行ANY / ALL / SOME「高于任意一个」这类模糊比较相关子查询布尔EXISTS / NOT EXISTS存在性判断逐行执行标量子查询最容易出的问题是「以为返回一行实际返回多行」。同城查询就是典型WHERE SITE (SELECT SITE FROM CUSTOMER WHERE CNAME美美)在「美美」只有一条记录时能用一旦同城有多家公司且名称重复立刻报错。稳妥写法是把换成 IN语义从「等于那个值」变成「属于那个集合」。ANY 和 ALL 用得少但必须会。 ANY (子查询)等价于「大于最小值」 ALL (子查询)等价于「大于最大值」。这两个谓词在报告里写一句等价转换能说明你真的理解了量词而不只是背了语法。EXISTS 和前三种的根本区别在于它是相关子查询子查询里引用了外层的列所以对外层的每一行都要执行一次。它的优势在于语义直白——「存在满足条件的记录」——而且不关心子查询选出什么列SELECT 1和SELECT *效果一样。判断「没有被订购的产品」这类否定题NOT EXISTS 是首选。4.3 逐题实现同城客户、高于 A01 单价、未被订购的产品-- 12. 与美美同城的客户公司名称及电话用 IN 防子查询多行 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE IN (SELECT SITE FROM CUSTOMER WHERE CNAME 美美) AND CNAME 美美; -- 通常要排除自己 -- 13. 单价高于 A01 的产品用 EXISTS 改写避免连接重复 SELECT PCODE, PNAME, PRICE FROM PRODUCT P WHERE EXISTS ( SELECT 1 FROM PRODUCT X WHERE X.PCODE A01 AND P.PRICE X.PRICE ); -- 14. 没有被订购的产品编号和名称NOT EXISTS 标准写法 SELECT P.PCODE, P.PNAME FROM PRODUCT P WHERE NOT EXISTS ( SELECT 1 FROM SALE S WHERE S.PCODE P.PCODE );第 12 条加了CNAME 美美因为题目问的是「与美美同城的客户」逻辑上不含它自己。这一句是我在批改时常见的遗漏点不加也能跑但语义多算了一家。第 13 条用 EXISTS 改写标量子查询好处是把「A01 的单价」这个基准也变成存在性判断即使产品表里有多个编号相似的产品也不会报多行错误。代价是可读性下降所以报告里建议两种写法并列说明各自适用条件这比只写一种更有说服力。第 14 条是整套实验里错误率最高的一题。很多人写成WHERE PCODE ! (SELECT PCODE FROM SALE)一旦 SALE 表里有两条以上记录子查询返回多行语句直接报错即使只有一条记录!的语义也是「不等于那一个产品编号」而不是「没被任何客户订购过」。正确做法只有两条路NOT EXISTS或者NOT IN (SELECT PCODE FROM SALE)。两者在 SALE.PCODE 允许 NULL 时行为不同——NOT IN 遇到 NULL 会整体返回空结果NOT EXISTS 不受影响所以更推荐前者。4.4 相关子查询与不相关子查询执行次数决定性能这个区分在实验数据量下看不出差别但答辩和后续课程设计一定会问。不相关子查询独立于外层只算一次把结果当常量用比如第 11 条里的 A01 单价。相关子查询引用了外层列外层每行都要跑一次比如第 14 条的 NOT EXISTS产品表有几行就执行几次。-- 不相关子查询先算出上海客户集合再一次性比对 SELECT CNO, PCODE FROM SALE WHERE CNO IN (SELECT CNO FROM CUSTOMER WHERE SITE 上海); -- 相关子查询外层每行都要回查一次 SALE SELECT CNO, PCODE FROM SALE S WHERE EXISTS (SELECT 1 FROM CUSTOMER C WHERE C.CNO S.CNO AND C.SITE 上海);两条 SQL 结果一样执行路径不同。前者子查询只跑一次后者跑 N 次。数据量小的时候后者甚至可能更快因为能走索引但报告里应当写明这个差别说明你知道自己在选什么。我一般的做法是能用不相关子查询就用不相关只有在语义天然是「逐行判断存在性」时才用相关子查询。5. 避坑与排查五个最容易翻车的点这一章是从实际批改和自查里攒出来的。每一条都按「现象 → 原因 → 解决」写对着自己的 SQL 逐条过一遍能挡掉大部分返工。5.1 坑一SELECT 里的列没进 GROUP BY现象查询能跑通但同一客户在不同数据库上返回的订购数量不一样或者报告里的数字和同学的对不上。原因SELECT 列表里出现了既不是聚合函数、也不在 GROUP BY 中的列比如SELECT CNO, OQUANTITY FROM SALE GROUP BY CNO。此时 OQUANTITY 取的是组内哪一行的值标准没有规定取决于具体实现属于典型的未定义行为。解决要么把该列加进 GROUP BY要么用聚合函数包起来。业务含义上一个客户对应多条订购记录时想拿数量就应该写SUM(OQUANTITY)而不是随便抓一行。写完分组查询后扫一眼 SELECT 列表逐个确认「这列是分组列还是聚合列」不属于任何一类的直接删掉或改写。5.2 坑二把 HAVING 当 WHERE 用或者反过来现象库存超 500 的产品个数查出来是一堆行每行一个数字加起来才对或者按城市统计时用 HAVING 过滤城市结果虽然对但执行计划显示扫描了全表。原因过滤条件的作用对象搞混了。HAVING STOCKS 500是在分组之后过滤组如果 GROUP BY 的是 STOCKS每个库存值自成一组返回的自然是「每种库存值有多少产品」。而HAVING SITE 上海虽然结果正确但它在分组后才丢弃非上海组做了无用功。解决判断标准只有一条——条件针对单行就放 WHERE针对整组就放 HAVING。凡是列名直接和常量比较的城市、单价区间、编号前缀一律 WHERE凡是包了聚合函数的COUNT、SUM一律 HAVING。写完检查一遍两边的条件看有没有放错位置的。5.3 坑三用 ! 或 NOT IN 判断「没有被订购」现象第 14 题报错「子查询返回多于一行」或者返回 0 行明明产品表里有未被订购的记录。原因WHERE PCODE ! (SELECT PCODE FROM SALE)有两个问题。一是子查询返回多行时标量比较直接报错二是即使只返回一行语义也是「不等于这一个编号」而不是「没被任何客户订购」。换成NOT IN后又踩第二个坑如果 SALE.PCODE 里存在 NULL整个 NOT IN 比较结果变成未知查询返回空集。解决否定类存在性判断统一用 NOT EXISTS它不受 NULL 影响语义也最贴近题目原话。如果非要用 NOT IN先确认子查询列上有没有 NULL没有才用。-- 推荐NOT EXISTS逐行判断是否存在订购记录 SELECT PCODE, PNAME FROM PRODUCT P WHERE NOT EXISTS (SELECT 1 FROM SALE S WHERE S.PCODE P.PCODE);5.4 坑四多表连接漏写连接条件现象查询返回的行数明显偏多明细里出现「上海客户订购了北京才有的产品」这种不可能的组合但语句不报任何错。原因FROM 后面跟了两张表却只写了过滤条件没写连接条件数据库按笛卡尔积组合。3 行乘 5 行得 15 行其中 12 行是假的。数据集小的时候假数据看着也像真的这是最危险的地方。解决养成「先连接、后过滤」的书写顺序JOIN 后面立刻跟 ON 条件跑一次看行数是否符合预期再加 WHERE。另外可以在报告里写一句行数校验预期行数等于满足条件的订购记录数实际返回对不上就是连接写错了。用显式 JOIN ... ON 语法而不是逗号加 WHERE也是降低这类错误的手段。5.5 坑五标量子查询在多行数据上直接崩现象同城查询在测试数据上跑得好好的换一套数据就报「子查询返回多于一行」报告里的 SQL 直接失效。原因用了 (子查询)这种标量比较前提是子查询严格返回一行一列。只要同城有两家客户、或者产品表里有两个 B 开头的编号前提就不成立。解决按语义选运算符。确定返回单值时比如按主键查单价才用可能返回多值时一律用 IN或者改成 EXISTS。写完子查询后先单独跑一遍看它到底返回几行这个习惯能省掉大量排查时间。6. 验收与答辩用对照表和反例自证结果这套实验的验收核心不是「SQL 能跑」而是「结果能自证」。我在带课程设计时通常要求交三样东西一份逐题结果对照表、一组故意构造的反例、一段口述的执行顺序说明。三样齐了答辩基本不会被动。对照表的做法是把每条查询的预期结果先手算出来再和 SQL 实际返回比。手算不用算全表抽两三行样本就行。比如第 10 题先确认上海有 C01、C02 两家再数它们在 SALE 里数量大于 200 的记录有几条预期值写死跑完对不上就说明条件写漏了。这张表长这样题号检查点预期值样例数据判定标准3订购两种以上的客户C01、C02行数与客户数一致10上海客户大额订购2 行不含北京客户记录12与美美同城1 行C02不含美美自身14未被订购产品C01 扫描仪不含已订购产品反例的设计思路是「故意让错写法暴露」。比如验证第 14 题可以临时往 SALE 插一条 PCODE 为空或者重复的记录看 NOT EXISTS 是否仍然稳定返回验证第 12 题可以再插一家同城的公司看写法是否报错、IN 写法是否正常。这一组反例写进报告比任何文字说明都有说服力。答辩时被问得最多的是执行顺序和子查询类型准备两句话就够一句讲 WHERE 在分组前、HAVING 在分组后所以聚合函数不能进 WHERE一句讲相关子查询逐行执行、不相关子查询只执行一次所以能用前者改写的地方优先改写。被问到「为什么这题不用嵌套」时答「分组能解决就不引入子查询可读性和执行计划都更好」这是标准答案。从那以后我每次交这类实验报告都强制走一遍「手算小样本 → 跑 SQL → 差异定位」的流程哪怕只查三行数据也不省这一步。希望帮到你。本文还有配套的精品资源点击获取
简介《数据库实验5嵌套查询》是一份面向数据库课程学习者与实验课教师的实验报告文档围绕 SELECT 统计查询、常用统计函数、连接查询与嵌套查询展开帮助读者理解多表检索、子查询、派生表及相关谓词的使用场景。文档以 CPXS 数据库为操作对象结合客户、产品、销售等表给出统计客户数量、求库存总量、按客户分组汇总、筛选上海客户订单、比较产品单价、查找同城客户、查询未被订购产品等 SQL 实例并配有实验目的、内容、结论与思考便于对照课堂进度完成实验和复盘语法。资源包共 1 个 doc 文件大小约 642KB结构完整、可直接查阅。目前已有 931 人学习适合需要巩固数据库查询基础、练习嵌套查询写法与理解连接查询差异的读者参考。1. 从一次结果对不上的统计说起这套查询方案要解决什么实验报告里 14 条查询跑完第 12 题返回了 3 行第 14 题返回 0 行第 8 题的数字和手工算的对不上。这类统计查询与嵌套查询的实验丢分点几乎从不在语法报错上——数据库会把错的结果安安静静地返回给你一个字都不提醒。这套方案要解决的就是这件事把 CPXS 库的客户、产品、订购三张表按「单表聚合 → 分组过滤 → 多表连接 → 嵌套子查询」四层拆开每条实验要求先定位到层再落 SQL最后用反例自证结果。适合正在交数据库实验报告、课程设计里要写查询模块或者要给一组人统一 SQL 口径的读者。反直觉的地方在于14 道题里真正需要嵌套的只有 4 道剩下的大半用 GROUP BY 加 HAVING 就能收工上来就套子查询反而容易把统计口径写歪。2. 数据模型与查询分层设计三张表、四类查询的职责划分动手写 SQL 之前先把表之间的关系画清楚比急着敲 SELECT 划算得多。CPXS 这个库的结构不复杂但三张表之间的连接路径有两条客户到订购是一对多产品到订购也是一对多客户和产品之间没有直接外键必须经过 SALE 这张中间表。搞不清这一点后面写「上海客户订购了哪些产品」这种题就会条件写漏。2.1 CPXS 库的三张表与字段约定先把字段语义固定下来后面所有查询的口径都以这张表为准。实验报告里最容易出问题的地方往往不是语法而是对字段含义的理解偏差比如 STOCKS 到底是「库存件数」还是「库存批次」直接决定 COUNT 里该写星号还是写列名。表名字段含义约束CUSTOMERCNO客户编号主键CUSTOMERCNAME客户公司名称非空CUSTOMERSITE所在城市用于同城查询CUSTOMERTELE联系电话可为空PRODUCTPCODE产品编号主键字母开头PRODUCTPNAME产品名称非空PRODUCTPRICE单价数值型PRODUCTSTOCKS库存量数值型SALECNO客户编号外键指向 CUSTOMERSALEPCODE产品编号外键指向 PRODUCTSALEOQUANTITY订购数量数值型三张表的基数关系是这套方案的骨架一个客户可以有多条订购记录一个产品也可以被多个客户订购SALE 是典型的多对多桥接表。凡是题目里出现「每个客户」「每种产品」这类措辞基本就落在分组查询上出现「和……在同一城市」「比……单价高」这类措辞基本就落在嵌套查询上。这个判断习惯一旦建立选题型的时间能从几分钟压到几秒。2.2 把 14 条实验要求拆成四层查询能力实验指导书给的是 14 条零散要求直接一条条写会写成流水账报告里也看不出设计思路。我一般会先做一次归类把它们映射到四层能力上每一层对应一组语法点这样报告的结构和代码的组织能对齐。层级能力对应题目核心语法第一层单表聚合客户数目、库存总量、最高单价、B 开头最低单价COUNT / SUM / MAX / MIN第二层分组过滤订购两种以上的客户、每客户订购总量、上海客户数、库存超 500 的产品数GROUP BY HAVING第三层多表连接上海客户订购明细、高于 A01 单价的产品订购等值连接、三表连接第四层嵌套子查询同城客户、未被订购的产品标量子查询、IN、NOT EXISTS这份映射表是整套方案的核心设计产物它同时解决三个问题报告里「实验设计」一节有东西可写写代码时知道该用哪组语法不会拿连接查询硬凑子查询的题验收时能按层逐个对答案出错范围立刻缩小到某一层。我在带课程设计时通常要求先交这张表再交 SQL返工率能降一半。有一点需要说明层级不是难度的严格排序而是依赖关系的排序。第一层的聚合函数会出现在第二层的 HAVING 里第二层的结果又常被第四层拿来当条件。所以写的时候按层推进别跳着写。2.3 建表与样例数据让每条查询都能复现实验环境里库是现成的但报告要能复现最好把建表和最小样例数据一起写进去。数据量不用大每张表五六行足够暴露问题——尤其是同城客户和未被订购产品这两类题数据太少会得到「恰好也对」的假象。-- CPXS 实验库三张表 最小可复现数据集 CREATE TABLE CUSTOMER ( CNO CHAR(4) PRIMARY KEY, -- 客户编号固定 4 位 CNAME VARCHAR(30) NOT NULL, -- 公司名称同城判断靠 SITE SITE VARCHAR(20), -- 所在城市 TELE VARCHAR(20) -- 联系电话允许为空 ); CREATE TABLE PRODUCT ( PCODE CHAR(4) PRIMARY KEY, -- 产品编号B 开头用于 LIKE 测试 PNAME VARCHAR(30) NOT NULL, PRICE DECIMAL(8,2), -- 单价嵌套比较的对象 STOCKS INT -- 库存量统计题的分组/过滤字段 ); CREATE TABLE SALE ( CNO CHAR(4), -- 外键客户 PCODE CHAR(4), -- 外键产品 OQUANTITY INT, -- 订购数量 PRIMARY KEY (CNO, PCODE), -- 联合主键同一客户同一产品只一条 FOREIGN KEY (CNO) REFERENCES CUSTOMER(CNO), FOREIGN KEY (PCODE) REFERENCES PRODUCT(PCODE) ); -- 样例数据刻意构造同城、未订购、单价交叉三种情形 INSERT INTO CUSTOMER VALUES (C01,美美公司,上海,021-1111), (C02,东方商行,上海,021-2222), (C03,北方物资,北京,010-3333); INSERT INTO PRODUCT VALUES (A01,打印机, 1200.00, 300), (B01,硒鼓, 180.00, 620), (B02,墨盒, 15.00, 480), (C01,扫描仪, 900.00, 120); INSERT INTO SALE VALUES (C01,A01, 150), (C01,B01, 260), (C02,A01, 220), (C02,B02, 400), (C03,B01, 130);建表脚本里有两个设计取舍值得在报告里点一句。第一SALE 用了 (CNO, PCODE) 联合主键这意味着同一客户对同一产品只有一条汇总记录所以后面统计「订购种类数」时 COUNT(PCODE) 就是种类数不用加 DISTINCT如果实际业务允许多批次下单主键要加流水号那时 COUNT 就得改成 COUNT(DISTINCT PCODE)。第二样例数据里刻意让 C01 和 C02 都在上海这样第 12 题的同城查询会返回多行正好能暴露「用 接子查询」的隐患。外键约束在这里不只是规范问题。少了外键SALE 里可以插入不存在的客户编号连接查询就会静默丢行结果少了几条你根本发现不了。这类问题在小数据集上尤其隐蔽因为你怎么看都觉得「数字挺合理的」。3. 统计查询与分组聚合函数、GROUP BY 与 HAVING 的落地写法统计类题目看着最没技术含量实际上丢分最多。原因很统一聚合函数把多行压成一行你没法像普通查询那样一眼看出哪行错了只能靠口径推演。这一章把 14 条要求里属于第一层和第二层的部分拆开写每条给出可执行 SQL 和一句口径说明。3.1 五个聚合函数在题目里的分工COUNT、SUM、MAX、MIN、AVG 这五个函数实验里真正用到的场景可以归成三类计数类、求和类、极值类。分工搞清楚了题目读完就知道该拿哪个。计数类用的是 COUNT。它的坑在参数上COUNT() 统计行数COUNT(列名) 统计该列非空的行数两者在有 NULL 的列上结果不同。实验里「统计客户的数目」这种题标准写法就是 COUNT()语义是「有多少条客户记录」不关心哪个字段是否为空。求和类用的是 SUM只对数值列有效且自动忽略 NULL。求「库存量总和」「每客户订购总量」都属于这一类。极值类用 MAX 和 MIN除了数值列也能用在字符列上——按字符序比较所以「B 开头产品的最低单价」是先 LIKE 过滤再取 MIN不是反过来。AVG 在这 14 条里没有直接出现但答辩时经常被追问「平均订购量怎么算」标准答案要补一句「AVG 会忽略 NULL如果某条记录数量为空分母会变小」这是它和 SUM/COUNT 手动相除的差别。3.2 GROUP BY 与 HAVING 的执行顺序为什么 WHERE 里不能写 COUNT这是整个实验里最值得讲清楚的一个知识点也是报告「实验原理」一节最容易写空的地方。SQL 的逻辑执行顺序大致是FROM → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → ORDER BY。放到分组查询里含义很明确WHERE 在分组之前生效过滤的是原始行所以它不能引用聚合函数HAVING 在分组之后生效过滤的是分组结果所以它可以引用 COUNT、SUM 这类聚合值。同理SELECT 里出现的非聚合列必须出现在 GROUP BY 里否则这一列取的是组内哪一行的值取决于具体实现。-- 正确分层先 WHERE 过滤原始行再 GROUP BY 分组最后 HAVING 过滤组 SELECT CNO, COUNT(PCODE) AS 产品种类数 FROM SALE WHERE OQUANTITY 0 -- 第一道闸过滤无效订购行 GROUP BY CNO -- 按客户分组 HAVING COUNT(PCODE) 2; -- 第二道闸过滤组聚合函数可出现在这里这段 SQL 的价值不在语法而在顺序感。很多人写分组查询时把条件一股脑塞进 HAVING虽然结果常常也对但等于让数据库先分组再丢弃整组效率差且语义含混。判断标准很简单条件针对「单行」就放 WHERE针对「一组」就放 HAVING。OQUANTITY 0是单行条件COUNT(PCODE) 2是组条件各归各位。参数上要注意的是GROUP BY 后面可以跟多列形成多级分组HAVING 里可以出现 SELECT 中没写出来的聚合函数ORDER BY 里的别名在大多数数据库里可用但标准只保证 SELECT 列表中的列别名在 ORDER BY 可见跨库移植时建议直接重写表达式。3.3 逐题实现从客户数目到每客户订购总量下面这组 SQL 对应第一层和第二层的前半部分每条后面跟一句口径说明。写法上我倾向显式写 AS 别名报告里结果集有列名答辩时对着结果讲比对着代码讲快。-- 1. 统计客户数目COUNT(*) 统计行数不受任何列 NULL 影响 SELECT COUNT(*) AS 客户总数 FROM CUSTOMER; -- 2. 求库存量总和SUM 自动忽略 NULL结果是一行一列 SELECT SUM(STOCKS) AS 库存总量 FROM PRODUCT; -- 3. 至少订购两种产品的客户编号与种类数 SELECT CNO, COUNT(PCODE) AS 种类数 FROM SALE GROUP BY CNO HAVING COUNT(PCODE) 2; -- 4. 每个客户订购产品数量的总数 SELECT CNO, SUM(OQUANTITY) AS 订购总量 FROM SALE GROUP BY CNO; -- 5. 所在城市为上海的客户公司数 SELECT SITE, COUNT(CNO) AS 客户数 FROM CUSTOMER GROUP BY SITE HAVING SITE 上海;第 3 条和第 4 条是一对同样的 GROUP BY一个数种类、一个数总量。这里用 COUNT(PCODE) 而不是 COUNT(*)是因为 SALE 的联合主键保证 PCODE 非空两者结果相同但写列名更能表达「数产品种类」的意图。第 5 条值得单独说HAVING SITE 上海在功能上等价于WHERE SITE 上海因为 SITE 是分组列。更规范的写法是放 WHERE因为它在分组前就把非上海的行排除了分组规模更小。报告里可以把两种写法都列出来说明结果一致但执行路径不同这是加分项。如果题目要的是「每个城市的客户数」而不限定上海那就去掉 HAVING直接 GROUP BY SITE结果会返回北京和上海两行。这个改写练习建议做一遍能立刻分清「过滤组」和「过滤行」的区别。3.4 范围条件与模糊匹配BETWEEN、LIKE 与聚合前过滤剩下几条统计题的核心不是聚合而是聚合之前的行过滤。单价在 10 到 20 元之间的产品个数、以 B 开头的产品最低单价、库存超过 500 的产品个数都属于这一类。这类题的通用形态是「先缩小行集再统计」所以过滤条件应该落在 WHERE 上不是 HAVING也不是 GROUP BY。-- 6. 单价在 10~20 元之间的产品个数闭区间含两端 SELECT COUNT(*) AS 产品个数 FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20; -- 7. 以字母 B 开头的所有产品的最低单价 SELECT MIN(PRICE) AS 最低单价 FROM PRODUCT WHERE PCODE LIKE B%; -- 8. 库存量超过 500 的产品个数 SELECT COUNT(*) AS 产品个数 FROM PRODUCT WHERE STOCKS 500; -- 9. 所有产品的最高单价 SELECT MAX(PRICE) AS 最高单价 FROM PRODUCT;第 6 条的 BETWEEN 是闭区间等价于PRICE 10 AND PRICE 20边界值会算进去。如果题目要求开区间必须手写成和别指望 BETWEEN 帮你排除端点。第 7 条的 LIKE B% 里百分号匹配任意长度字符下划线匹配单个字符这两个通配符的区别建议在报告里写一句答辩常问。第 8 条是最容易写歪的一条。我见过不少报告写成GROUP BY STOCKS HAVING STOCKS 500再 COUNT那样得到的是「每种库存值各有多少个产品」是一堆行不是总数。只要题目问的是「个数」而不是「每种多少个」就用 WHERE 过滤加 COUNT(*)不需要 GROUP BY。判断依据还是那句题目要一行结果就别分组。4. 连接查询与嵌套查询从等值连接到 EXISTS 的选型第三层和第四层是这份实验的重点也是标题里「嵌套查询」四个字的落点。但嵌套不是越多越好很多题写成连接反而更清楚。这一章先把连接和子查询的适用边界划出来再逐题落地。4.1 三表连接的写法与笛卡尔积风险CPXS 里最典型的三表题是「查询由上海客户订购的、且订购数量大于 200 的所有客户编号、产品编号、联系人和订购数量」。这条要求横跨 CUSTOMER 和 SALE 两张表条件分属两表城市在 CUSTOMER数量在 SALE。-- 10. 上海客户订购且数量 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; -- 过滤条件数量 -- 11. 高于 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);第 10 条的关键是连接条件必须写全。漏掉C.CNO S.CNO的后果不是报错而是笛卡尔积——3 个客户乘 5 条订购返回 15 行其中大部分是客户和订购记录错配出来的假数据。更麻烦的是这种错误在小数据集上返回的行数看着也「不算离谱」很容易被当成正确答案交上去。第 11 条演示了连接和子查询的组合用法外层用连接把订购明细和产品价格拉到一起内层用标量子查询取出 A01 的单价当比较基准。这里子查询返回单值所以能用直接比。如果 A01 在产品表里有两条记录这条 SQL 会直接报「子查询返回多于一行」这也是为什么 PCODE 必须是主键。写连接时我一般的习惯是先写 FROM 和 JOIN把连接条件补齐跑一次看行数对不对再加 WHERE 条件逐条筛。这样出错时能立刻定位是连接错了还是过滤错了。4.2 标量子查询、IN、ANY/ALL、EXISTS 的适用边界嵌套查询这一块的语法点密集实验要求里提到的操作符和谓词基本都要覆盖到。按返回形态可以分成四类选型逻辑很清楚子查询形态返回值配套运算符典型场景标量子查询单行单列 和 A01 单价比较多行单列一列多行IN / NOT IN同城客户、未被订购产品多行单列 量词一列多行ANY / ALL / SOME「高于任意一个」这类模糊比较相关子查询布尔EXISTS / NOT EXISTS存在性判断逐行执行标量子查询最容易出的问题是「以为返回一行实际返回多行」。同城查询就是典型WHERE SITE (SELECT SITE FROM CUSTOMER WHERE CNAME美美)在「美美」只有一条记录时能用一旦同城有多家公司且名称重复立刻报错。稳妥写法是把换成 IN语义从「等于那个值」变成「属于那个集合」。ANY 和 ALL 用得少但必须会。 ANY (子查询)等价于「大于最小值」 ALL (子查询)等价于「大于最大值」。这两个谓词在报告里写一句等价转换能说明你真的理解了量词而不只是背了语法。EXISTS 和前三种的根本区别在于它是相关子查询子查询里引用了外层的列所以对外层的每一行都要执行一次。它的优势在于语义直白——「存在满足条件的记录」——而且不关心子查询选出什么列SELECT 1和SELECT *效果一样。判断「没有被订购的产品」这类否定题NOT EXISTS 是首选。4.3 逐题实现同城客户、高于 A01 单价、未被订购的产品-- 12. 与美美同城的客户公司名称及电话用 IN 防子查询多行 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE IN (SELECT SITE FROM CUSTOMER WHERE CNAME 美美) AND CNAME 美美; -- 通常要排除自己 -- 13. 单价高于 A01 的产品用 EXISTS 改写避免连接重复 SELECT PCODE, PNAME, PRICE FROM PRODUCT P WHERE EXISTS ( SELECT 1 FROM PRODUCT X WHERE X.PCODE A01 AND P.PRICE X.PRICE ); -- 14. 没有被订购的产品编号和名称NOT EXISTS 标准写法 SELECT P.PCODE, P.PNAME FROM PRODUCT P WHERE NOT EXISTS ( SELECT 1 FROM SALE S WHERE S.PCODE P.PCODE );第 12 条加了CNAME 美美因为题目问的是「与美美同城的客户」逻辑上不含它自己。这一句是我在批改时常见的遗漏点不加也能跑但语义多算了一家。第 13 条用 EXISTS 改写标量子查询好处是把「A01 的单价」这个基准也变成存在性判断即使产品表里有多个编号相似的产品也不会报多行错误。代价是可读性下降所以报告里建议两种写法并列说明各自适用条件这比只写一种更有说服力。第 14 条是整套实验里错误率最高的一题。很多人写成WHERE PCODE ! (SELECT PCODE FROM SALE)一旦 SALE 表里有两条以上记录子查询返回多行语句直接报错即使只有一条记录!的语义也是「不等于那一个产品编号」而不是「没被任何客户订购过」。正确做法只有两条路NOT EXISTS或者NOT IN (SELECT PCODE FROM SALE)。两者在 SALE.PCODE 允许 NULL 时行为不同——NOT IN 遇到 NULL 会整体返回空结果NOT EXISTS 不受影响所以更推荐前者。4.4 相关子查询与不相关子查询执行次数决定性能这个区分在实验数据量下看不出差别但答辩和后续课程设计一定会问。不相关子查询独立于外层只算一次把结果当常量用比如第 11 条里的 A01 单价。相关子查询引用了外层列外层每行都要跑一次比如第 14 条的 NOT EXISTS产品表有几行就执行几次。-- 不相关子查询先算出上海客户集合再一次性比对 SELECT CNO, PCODE FROM SALE WHERE CNO IN (SELECT CNO FROM CUSTOMER WHERE SITE 上海); -- 相关子查询外层每行都要回查一次 SALE SELECT CNO, PCODE FROM SALE S WHERE EXISTS (SELECT 1 FROM CUSTOMER C WHERE C.CNO S.CNO AND C.SITE 上海);两条 SQL 结果一样执行路径不同。前者子查询只跑一次后者跑 N 次。数据量小的时候后者甚至可能更快因为能走索引但报告里应当写明这个差别说明你知道自己在选什么。我一般的做法是能用不相关子查询就用不相关只有在语义天然是「逐行判断存在性」时才用相关子查询。5. 避坑与排查五个最容易翻车的点这一章是从实际批改和自查里攒出来的。每一条都按「现象 → 原因 → 解决」写对着自己的 SQL 逐条过一遍能挡掉大部分返工。5.1 坑一SELECT 里的列没进 GROUP BY现象查询能跑通但同一客户在不同数据库上返回的订购数量不一样或者报告里的数字和同学的对不上。原因SELECT 列表里出现了既不是聚合函数、也不在 GROUP BY 中的列比如SELECT CNO, OQUANTITY FROM SALE GROUP BY CNO。此时 OQUANTITY 取的是组内哪一行的值标准没有规定取决于具体实现属于典型的未定义行为。解决要么把该列加进 GROUP BY要么用聚合函数包起来。业务含义上一个客户对应多条订购记录时想拿数量就应该写SUM(OQUANTITY)而不是随便抓一行。写完分组查询后扫一眼 SELECT 列表逐个确认「这列是分组列还是聚合列」不属于任何一类的直接删掉或改写。5.2 坑二把 HAVING 当 WHERE 用或者反过来现象库存超 500 的产品个数查出来是一堆行每行一个数字加起来才对或者按城市统计时用 HAVING 过滤城市结果虽然对但执行计划显示扫描了全表。原因过滤条件的作用对象搞混了。HAVING STOCKS 500是在分组之后过滤组如果 GROUP BY 的是 STOCKS每个库存值自成一组返回的自然是「每种库存值有多少产品」。而HAVING SITE 上海虽然结果正确但它在分组后才丢弃非上海组做了无用功。解决判断标准只有一条——条件针对单行就放 WHERE针对整组就放 HAVING。凡是列名直接和常量比较的城市、单价区间、编号前缀一律 WHERE凡是包了聚合函数的COUNT、SUM一律 HAVING。写完检查一遍两边的条件看有没有放错位置的。5.3 坑三用 ! 或 NOT IN 判断「没有被订购」现象第 14 题报错「子查询返回多于一行」或者返回 0 行明明产品表里有未被订购的记录。原因WHERE PCODE ! (SELECT PCODE FROM SALE)有两个问题。一是子查询返回多行时标量比较直接报错二是即使只返回一行语义也是「不等于这一个编号」而不是「没被任何客户订购」。换成NOT IN后又踩第二个坑如果 SALE.PCODE 里存在 NULL整个 NOT IN 比较结果变成未知查询返回空集。解决否定类存在性判断统一用 NOT EXISTS它不受 NULL 影响语义也最贴近题目原话。如果非要用 NOT IN先确认子查询列上有没有 NULL没有才用。-- 推荐NOT EXISTS逐行判断是否存在订购记录 SELECT PCODE, PNAME FROM PRODUCT P WHERE NOT EXISTS (SELECT 1 FROM SALE S WHERE S.PCODE P.PCODE);5.4 坑四多表连接漏写连接条件现象查询返回的行数明显偏多明细里出现「上海客户订购了北京才有的产品」这种不可能的组合但语句不报任何错。原因FROM 后面跟了两张表却只写了过滤条件没写连接条件数据库按笛卡尔积组合。3 行乘 5 行得 15 行其中 12 行是假的。数据集小的时候假数据看着也像真的这是最危险的地方。解决养成「先连接、后过滤」的书写顺序JOIN 后面立刻跟 ON 条件跑一次看行数是否符合预期再加 WHERE。另外可以在报告里写一句行数校验预期行数等于满足条件的订购记录数实际返回对不上就是连接写错了。用显式 JOIN ... ON 语法而不是逗号加 WHERE也是降低这类错误的手段。5.5 坑五标量子查询在多行数据上直接崩现象同城查询在测试数据上跑得好好的换一套数据就报「子查询返回多于一行」报告里的 SQL 直接失效。原因用了 (子查询)这种标量比较前提是子查询严格返回一行一列。只要同城有两家客户、或者产品表里有两个 B 开头的编号前提就不成立。解决按语义选运算符。确定返回单值时比如按主键查单价才用可能返回多值时一律用 IN或者改成 EXISTS。写完子查询后先单独跑一遍看它到底返回几行这个习惯能省掉大量排查时间。6. 验收与答辩用对照表和反例自证结果这套实验的验收核心不是「SQL 能跑」而是「结果能自证」。我在带课程设计时通常要求交三样东西一份逐题结果对照表、一组故意构造的反例、一段口述的执行顺序说明。三样齐了答辩基本不会被动。对照表的做法是把每条查询的预期结果先手算出来再和 SQL 实际返回比。手算不用算全表抽两三行样本就行。比如第 10 题先确认上海有 C01、C02 两家再数它们在 SALE 里数量大于 200 的记录有几条预期值写死跑完对不上就说明条件写漏了。这张表长这样题号检查点预期值样例数据判定标准3订购两种以上的客户C01、C02行数与客户数一致10上海客户大额订购2 行不含北京客户记录12与美美同城1 行C02不含美美自身14未被订购产品C01 扫描仪不含已订购产品反例的设计思路是「故意让错写法暴露」。比如验证第 14 题可以临时往 SALE 插一条 PCODE 为空或者重复的记录看 NOT EXISTS 是否仍然稳定返回验证第 12 题可以再插一家同城的公司看写法是否报错、IN 写法是否正常。这一组反例写进报告比任何文字说明都有说服力。答辩时被问得最多的是执行顺序和子查询类型准备两句话就够一句讲 WHERE 在分组前、HAVING 在分组后所以聚合函数不能进 WHERE一句讲相关子查询逐行执行、不相关子查询只执行一次所以能用前者改写的地方优先改写。被问到「为什么这题不用嵌套」时答「分组能解决就不引入子查询可读性和执行计划都更好」这是标准答案。从那以后我每次交这类实验报告都强制走一遍「手算小样本 → 跑 SQL → 差异定位」的流程哪怕只查三行数据也不省这一步。希望帮到你。本文还有配套的精品资源点击获取
