★ FEATURED ARTICLE 简介《数据库实验5嵌套查询》是一份面向数据库课程学习者与实验教学场景的文档型实验报告围绕CPXS数据库展开统计查询、连接查询与嵌套查询的训练适合正在学习SQL查询、准备课程实验或需要巩固查询语法的高校学生与自学者参考。资源包共1个doc文件约642KB内容以实验目的、实验内容、SQL示例与知识点归纳为主集中呈现SELECT统计查询、COUNT/SUM/MAX/MIN等聚合函数、GROUP BY与HAVING分组筛选、多表连接以及子查询谓词等核心内容。该文档已有931人学习说明其在数据库实验学习中具有一定参考价值。读者可借助其中的示例语句理解客户数量统计、库存汇总、上海客户订购筛选、同城客户查询、高于指定单价产品查询及未被订购产品查询等典型题目并从中梳理嵌套查询的写法与排错思路用于实验报告整理和期末复习。1. 从一份实验报告说起三张表撑起的十四道查询题数据库实验做到第五个很多人会卡在同一处前面单表查询还能照着 SELECT 抄一到嵌套查询、子查询、NOT EXISTS写出来要么报错要么结果对不上却又看不出哪里错。这份《数据库实验5嵌套查询》的 .doc围着 CPXS 数据库里的 CUSTOMER、PRODUCT、SALE 三张表把统计查询、连接查询、嵌套查询串成十四个小题从COUNT(*)数客户一路写到查询没有被订购的产品这种典型反连接。它适合刚学完 SELECT 语法、需要把聚合函数和子查询真正写一遍的人也适合带实验课、想找一套小而全的题面做课堂练习的人。表少、题密、每道题都对应一个具体语法点是它比大部头教材更值得动手的原因。2. 三表模型与可运行环境先把 CPXS 库搭出来2.1 三张表的字段与关联关系这套实验的骨架非常干净只有客户、产品、销售三张表字段名在十四道题里反复出现先把它们认全后面读 SQL 才不会被CNO、PCODE绕晕。表名字段含义说明CUSTOMERCNO客户编号主键如 C01CUSTOMERCNAME客户公司名称第 12 题按名称找同城客户CUSTOMERSITE所在城市第 4、11、12 题都用到CUSTOMERTELE联系电话第 12 题要输出PRODUCTPCODE产品编号主键A/B 开头第 9 题用 LIKE B%PRODUCTPNAME产品名称第 14 题要输出PRODUCTPRICE单价第 5、7、13 题的比较对象PRODUCTSTOCKS库存量第 2、6 题的统计对象SALECNO客户编号外键指向 CUSTOMERSALEPCODE产品编号外键指向 PRODUCTSALEOQUANTITY订购数量第 8、11 题的过滤条件关系是典型的两条一对多一个客户可以有多条订购记录一个产品也可以被多个客户订购SALE 就是那张把两边连起来的中间表。实验题里所有连接查询本质都在这张表上做文章所有嵌套查询本质都是先在一张表里算出中间结果再拿它去过滤另一张表。提示教材里的 CPXS 库通常还带供应商、仓库之类的表这份实验只用到了三张。少几张表不影响语法练习反而让每道题的意图更清楚——你能一眼看出这题考的是聚合还是子查询。2.2 一份能直接跑起来的建表与造数脚本文档里只给了查询语句没给建表脚本直接抄过去是没有数据可查的。我按题面用到的字段反推出了一份 MySQL 8 的建表脚本跑完这十几行就能把环境备齐。CREATE DATABASE IF NOT EXISTS CPXS DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci; USE CPXS; CREATE TABLE CUSTOMER ( CNO CHAR(4) NOT NULL, -- 客户编号如 C01 CNAME VARCHAR(40) NOT NULL, -- 客户公司名称 SITE VARCHAR(20) NOT NULL, -- 所在城市第 4/11/12 题依赖 TELE VARCHAR(20) DEFAULT NULL, -- 联系电话允许为空 PRIMARY KEY (CNO), KEY idx_site (SITE) -- 按城市过滤时用得上 ); CREATE TABLE PRODUCT ( PCODE CHAR(4) NOT NULL, -- 产品编号如 A01 / B02 PNAME VARCHAR(40) NOT NULL, -- 产品名称 PRICE DECIMAL(10,2) NOT NULL, -- 单价定点数比 FLOAT 稳 STOCKS INT NOT NULL DEFAULT 0,-- 库存量第 2/6 题统计对象 PRIMARY KEY (PCODE), KEY idx_price (PRICE) -- 第 7/13 题按价格比较 ); CREATE TABLE SALE ( CNO CHAR(4) NOT NULL, PCODE CHAR(4) 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) );这段脚本里有三个决定后面会不会翻车的地方。PRICE用DECIMAL(10,2)而不是FLOAT因为第 13 题要做单价比 A01 高的严格比较浮点误差会让边界值忽高忽低SALE的主键设成(CNO, PCODE)联合主键意味着一对客户与产品只保留一行汇总记录第 2 题COUNT(PCODE)的语义才成立两个外键约束保证不会插进不存在的客户或产品第 11 题的连接结果不会凭空多出行。造数据的时候要照顾到十四道题的结果都不能是空集否则跑出来一片空白你根本分不清是写错了还是本来就没数据。INSERT INTO CUSTOMER (CNO, CNAME, SITE, TELE) VALUES (C01,美美公司,上海,021-0001), (C02,华联商厦,上海,021-0002), (C03,北方商贸,北京,010-0003), (C04,南方百货,广州,020-0004); INSERT INTO PRODUCT (PCODE, PNAME, PRICE, STOCKS) VALUES (A01,台式电扇, 12.50, 800), -- 第 13 题的比较基准 (A02,落地电扇, 19.00, 320), (B01,电饭煲, 15.00, 600), (B02,电水壶, 11.00, 120), (B03,空气净化器, 260.00, 40); -- 故意不插销售记录留给第 14 题 INSERT INTO SALE (CNO, PCODE, OQUANTITY) VALUES (C01,A01,150), (C01,B01,210), -- C01 订了两种产品命中第 2 题 (C01,A02, 80), (C02,A01,300), -- 上海客户 数量 200命中第 11 题 (C02,B02,120), (C03,A01,260), (C04,B03, 10);注意B03那行被我留空了这是刻意的第 14 题问没有被订购的产品如果所有产品都订过一遍这题的结果就是空集你验证不了自己写的NOT EXISTS到底对不对。同理C01和C02都在上海第 12 题与美美公司同城的客户才有第二条记录可查。造测试数据的血泪经验就一句每条查询都要能返回至少一行不然调试时你连自己错在哪都不知道。2.3 字符集、字段类型与外键几个容易被忽略的设置utf8mb4那行不是摆设。实验第 4、11、12 题都带中文字面量上海如果库建成latin1查询不会报错只会安静地返回空集排查起来非常费劲。建库时把字符集和排序规则一次性定死比事后ALTER省事得多。字段类型的选择也直接影响结果。CNO、PCODE用CHAR(4)定长因为它们在连接条件里要频繁比较定长比较比变长快也不会因为尾部空格导致A01 和A01匹配不上。OQUANTITY用INT第 8 题要做 100的数值比较存成字符串的话80 100会返回真这种隐式转换的坑一旦踩上结果错得毫无规律。外键要不要加团队里一直有争议。做实验库我一般会加因为它能在造数据阶段就把脏数据挡在门外。如果后面要批量灌历史数据、追求导入速度可以先SET FOREIGN_KEY_CHECKS0导完再打开别为了图快直接把约束删掉。3. 单表统计与多表连接十一道题共用的一套骨架3.1 聚合函数COUNT 的三种写法和 SUM/MAX/MIN 的返回值实验前半段几乎全是聚合函数的练习。第 1 题数客户、第 2 题求库存总量、第 5 题求最高单价、第 9 题求 B 开头产品的最低单价四个函数各出现一次。看起来简单但COUNT有三种写法差别不小。-- 实验第 1 题客户总数COUNT(*) 数的是行数 SELECT COUNT(*) AS customer_cnt FROM CUSTOMER; -- COUNT(列) 会跳过 NULLTELE 允许为空时两者结果可能不同 SELECT COUNT(*) AS all_rows, COUNT(TELE) AS has_tele FROM CUSTOMER; -- 实验第 2 题库存总量 SELECT SUM(STOCKS) AS total_stocks FROM PRODUCT; -- 实验第 5 题所有产品的最高单价 SELECT MAX(PRICE) AS max_price FROM PRODUCT; -- 实验第 9 题以字母 B 开头产品的最低单价 SELECT MIN(PRICE) AS min_price FROM PRODUCT WHERE PCODE LIKE B%;COUNT(*)统计的是结果集行数不管某列是不是 NULLCOUNT(TELE)只统计TELE非空的行。当前这份造数里两个值恰好相等但真实数据里联系电话经常缺失两者就会拉开差距。参数上要注意LIKE B%的百分号B%表示以 B 开头%B%表示包含 B%B表示以 B 结尾第 9 题要的是前者写成后者结果会把中间带 B 的产品也算进来。SUM、MAX、MIN在没有匹配行时的返回值也不一样SUM返回NULL而不是 0MAX/MIN同样返回NULL。第 9 题如果你的数据里一个 B 开头的产品都没有结果就是NULL而不是 0前端拿到这个值直接显示会很难看常见做法是在外层套COALESCE(MIN(PRICE), 0)。3.2 WHERE 与 HAVING 的分工过滤发生在分组前还是分组后第 3、4 题是分组查询的入门题第 2 题的第 2 小问则引入了HAVING。这三道题放一起看能把WHERE和HAVING的分界线讲清楚。-- 实验第 3 题每个客户订购产品数量的总数 SELECT CNO, SUM(OQUANTITY) AS total_qty FROM SALE GROUP BY CNO; -- 实验第 2 题第 2 小问至少订购两种以上产品的客户编号与产品种类数 SELECT CNO, COUNT(DISTINCT PCODE) AS kind_cnt FROM SALE GROUP BY CNO HAVING COUNT(DISTINCT PCODE) 2; -- 实验第 4 题所在城市为上海的客户公司数 SELECT SITE, COUNT(CNO) AS customer_cnt FROM CUSTOMER WHERE SITE 上海 GROUP BY SITE;第 3 题没什么争议GROUP BY CNO把同一个客户的多条订购记录压成一行SUM(OQUANTITY)累加数量。第 2 题我加了DISTINCT这是原题写法里容易出问题的一处如果SALE允许同一客户对同一产品留多行COUNT(PCODE)会把重复订购的次数也算成产品种类客户只买了一种产品但分三次下单也能凑出COUNT 3误判成订购两种以上。用COUNT(DISTINCT PCODE)才对应产品种类数这个语义。第 4 题的原写法是GROUP BY SITE HAVING SITE上海。语法上跑得通但这是把行级过滤条件塞进了分组后过滤等于先按所有城市分组、再扔掉其他组白白多做一轮分组。写成WHERE SITE 上海更符合习惯读者一眼就能看出这是过滤而不是聚合条件。判断标准很简单条件里出现聚合函数COUNT、SUM、AVG就用HAVING只涉及原始列就用WHERE。3.3 实验第 6、7 题用 HAVING 做行过滤会踩的两个坑这两道题是整份实验里最容易照抄出错的地方原写法语法合法但语义跑偏了。先把原样贴出来看。-- 实验第 6 题原写法按 STOCKS 分组结果是一堆行不是一个数 SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS 500; -- 实验第 7 题原写法按 PRICE 分组同理 SELECT PRICE, COUNT(PCODE) FROM PRODUCT GROUP BY PRICE HAVING PRICE BETWEEN 10 AND 20;题目问的是库存量超过 500 的产品个数和单价在 10~20 元之间产品的个数两个都是标量答案应该是一个数字。但这两条 SQL 的GROUP BY把STOCKS、PRICE当成分组键返回的是每个库存值对应几种产品、每个单价对应几种产品的多行结果。数据量一大你可能拿到几十行然后误以为自己写对了因为确实有输出。正确的写法是把过滤条件放回WHERE聚合函数在过滤之后才作用于剩下的行。-- 改法一条件放进 WHERE聚合函数直接出单值 SELECT COUNT(*) AS cnt FROM PRODUCT WHERE STOCKS 500; -- 改法二单价区间同理BETWEEN 是闭区间含 10 和 20 SELECT COUNT(*) AS cnt FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20; -- 如果确实想看分组明细才保留 GROUP BY但 SELECT 列必须和分组键对齐 SELECT STOCKS, COUNT(*) AS cnt FROM PRODUCT WHERE STOCKS 500 GROUP BY STOCKS;改法二里BETWEEN 10 AND 20是闭区间PRICE 10和PRICE 20都会被算进去。如果题目要求开区间得写成PRICE 10 AND PRICE 20。这种边界在实验数据里不一定能暴露出来等到真实业务对账差一分钱的时候才发现那就是另一回事了。第 8 题还有一处类似的隐患。原写法SELECT PCODE, OQUANTITY ... GROUP BY PCODE里OQUANTITY没出现在GROUP BY中MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BY这条语句会直接报错Expression #2 of SELECT list is not in GROUP BY clause。就算关掉这个模式让它跑过去返回的OQUANTITY也是该组里随机一行的值没有业务含义。按题目本意改写成下面这样才站得住。-- 第 8 题改写先过滤掉数量不足 100 的记录再按产品汇总 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;3.4 多表连接逗号连接、显式 INNER JOIN 与 LEFT JOIN 的差别第 11 题是这份实验里唯一的显式多表查询原写法用的是逗号连接加WHERE关联。这种写法在老教材里很常见语法没错但表一多就容易漏写关联条件退化成笛卡尔积。-- 实验第 11 题原写法逗号连接 WHERE 关联 SELECT SALE.CNO, SALE.PCODE, CUSTOMER.CNAME, SALE.OQUANTITY FROM SALE, CUSTOMER WHERE SALE.CNO CUSTOMER.CNO -- 关联条件漏了就是笛卡尔积 AND CUSTOMER.SITE 上海 -- 过滤条件 AND SALE.OQUANTITY 200; -- 过滤条件 -- 显式 INNER JOIN关联与过滤分开写漏条件的概率低很多 SELECT s.CNO, s.PCODE, c.CNAME, s.OQUANTITY FROM SALE AS s INNER JOIN CUSTOMER AS c ON s.CNO c.CNO WHERE c.SITE 上海 AND s.OQUANTITY 200;两条语句结果完全一致区别在可维护性。ON后面只放关联条件WHERE后面只放过滤条件读代码的人扫一眼就知道这两张表是怎么连的。表超过三张之后逗号连接里的关联条件和过滤条件混在一起改一处条件很容易牵动另一处。LEFT JOIN在这份实验里没有直接出现但第 14 题没有被订购的产品用它写比子查询更直观。-- 反连接写法左表保留全部产品右表匹配不上的行 s.PCODE 为 NULL SELECT p.PCODE, p.PNAME FROM PRODUCT AS p LEFT JOIN SALE AS s ON p.PCODE s.PCODE WHERE s.PCODE IS NULL;这里WHERE s.PCODE IS NULL是关键。LEFT JOIN保证PRODUCT每一行都出现在结果里匹配不到销售记录的右表所有列补NULL用这个特征就能筛出从没被订过的产品。有个前提SALE.PCODE必须允许为NULL才能这么判断如果它被设成NOT NULL判断条件要改成s.CNO IS NULL——CNO是SALE里同样参与连接的列匹配不上时一样是NULL。4. 嵌套查询标量子查询、IN 与 NOT EXISTS 的取舍4.1 标量子查询返回一行一列时的比较运算符第 13 题是嵌套查询的入门形态。子查询(SELECT PRICE FROM PRODUCT WHERE PCODEA01)只返回一行一列这种叫标量子查询可以直接参与、、这类比较运算。-- 实验第 13 题单价比 A01 高的产品对应的订购记录 SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE AS s INNER JOIN PRODUCT AS p ON s.PCODE p.PCODE WHERE p.PRICE (SELECT PRICE FROM PRODUCT WHERE PCODE A01);执行顺序是从内到外先跑子查询拿到 A01 的单价 12.50再拿这个值去过滤外层。参数上要注意PCODE A01这个条件必须保证唯一性PCODE是PRODUCT的主键所以没问题。如果子查询写在非唯一列上比如WHERE PNAME 台式电扇一旦有两条同名产品子查询返回两行外层就会报Subquery returns more than 1 row。这是标量子查询最典型的翻车现场下面第 3 小节还会细说。这道题还有另一种写法把子查询直接放进FROM-- 派生表写法先算出基准价再和 PRODUCT 连接 SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE AS s INNER JOIN PRODUCT AS p ON s.PCODE p.PCODE INNER JOIN (SELECT PRICE AS base_price FROM PRODUCT WHERE PCODE A01) AS b ON p.PRICE b.base_price;结果一样但派生表在数据量大时更容易被优化器合并执行计划。日常写查询不用纠结这个可读性优先标量子查询更贴近题面。4.2 IN 与多值子查询第 12 题为什么要用 IN 而不是 第 12 题问与美美公司在同一城市的客户公司名称及联系电话思路是先用子查询查出美美所在的城市再拿这个城市去过滤客户表。原写法用的是-- 原写法用 接子查询同城有多个美美时会报错 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE (SELECT SITE FROM CUSTOMER WHERE CNAME 美美);当前数据里只有一家美美公司子查询返回单值语句能跑通。但这是一个依赖数据巧合才能成立的写法只要哪天录入了第二家同名公司比如连锁店在两地都有分公司子查询立刻返回两行直接报错。生产环境里这种今天能跑、明天报错的 SQL 最难排查因为代码一行没改。-- 推荐写法IN 天然接受多值不会因为子查询多返回一行而崩 SELECT CNAME, TELE FROM CUSTOMER WHERE SITE IN (SELECT SITE FROM CUSTOMER WHERE CNAME 美美) AND CNAME 美美; -- 结果里通常要排除自己 -- 等价的连接写法某些优化器对 JOIN 的执行计划更友好 SELECT DISTINCT c2.CNAME, c2.TELE FROM CUSTOMER AS c1 INNER JOIN CUSTOMER AS c2 ON c1.SITE c2.SITE WHERE c1.CNAME 美美 AND c2.CNAME 美美;IN的语义是等于子查询结果集中的任意一个值多返回几行不会出错。后面那句CNAME 美美是我自己加的题目字面没要求排除美美公司本身但与美美同城的其他客户才是这次查询真正的业务目的实际交付时通常会把自身排除掉。这就属于题面之外要自己想清楚的一层。IN有一个隐藏陷阱如果子查询结果里含NULLSITE IN (..., NULL)对不匹配的行返回的是UNKNOWN而不是FALSEWHERE只保留TRUE的行所以不匹配的行会被一起过滤掉结果可能少了。当前SITE是NOT NULL暂时安全但换成可空列就得留心。4.3 第 14 题! 子查询为什么会报错NOT EXISTS 怎么改写这道题是整份实验里最能体现同一需求有四种写法、只有两种稳的地方。原写法-- 原写法SALE 里有多条记录子查询返回多行 / ! 直接报错 SELECT PRODUCT.PCODE, PNAME FROM PRODUCT WHERE PRODUCT.PCODE ! (SELECT SALE.PCODE FROM SALE);错误信息是Subquery returns more than 1 row。原因很直白SALE表里有 7 条记录子查询返回 7 个PCODE!是二元比较运算符只能拿一个值去比7 个值它不知道该怎么处理。这条语句在SALE只有一行时能跑通数据一多就崩属于典型的测试环境能过、上线就炸。改成NOT IN语法上就对了但还有一个更隐蔽的坑-- 语法正确但 SALE.PCODE 一旦含 NULL整条查询返回空集 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE);NOT IN的展开逻辑是PCODE v1 AND PCODE v2 AND ...只要集合里有一个NULLPCODE NULL求值为UNKNOWN整个AND链就变成UNKNOWNWHERE把所有行都过滤掉查询安静地返回零行。你现在这份数据里SALE.PCODE是主键的一部分不会为NULL所以跑得出结果换成SELECT CNO FROM SALE这种可空列当子查询问题立刻显现。-- 推荐写法NOT EXISTS 是逐个产品去试不依赖集合语义NULL 也不影响 SELECT p.PCODE, p.PNAME FROM PRODUCT AS p WHERE NOT EXISTS ( SELECT 1 FROM SALE AS s WHERE s.PCODE p.PCODE );SELECT 1里的 1 只是个占位EXISTS只关心子查询有没有返回行不关心返回什么列写SELECT *、SELECT NULL效果一样写1是行业习惯省得优化器真去取列。WHERE s.PCODE p.PCODE这行把内外两层关联起来p.PCODE引用外层当前行的值这就是相关子查询。4.4 相关子查询与 EXISTS 的执行逻辑EXISTS和IN的区别本质是执行模型不同。IN是先把子查询整体算出来再拿外层每一行去这个集合里找EXISTS是外层每读一行就把当前行的值带进子查询里跑一次只看有没有结果。-- 找出至少被上海客户订购过的产品相关子查询 SELECT p.PCODE, p.PNAME FROM PRODUCT AS p WHERE EXISTS ( SELECT 1 FROM SALE AS s INNER JOIN CUSTOMER AS c ON s.CNO c.CNO WHERE s.PCODE p.PCODE AND c.SITE 上海 );这条语句里p.PCODE是相关条件c.SITE 上海是常量过滤。执行时外层每读一个产品内层就用它的编号去SALE里找一遍找到第一条就短路返回真不再往下扫。子查询内部还能再连一张CUSTOMER这就是嵌套查询里套连接实验题里没有直接出但实际业务中很常见。性能上有个粗略经验外层表小、子查询能用索引命中的EXISTS通常更快因为它找到第一条就停子查询结果集本身很小、而且能被缓存复用的IN更划算。这个结论不是绝对的EXPLAIN看一眼执行计划比背结论靠谱。带NOT的时候我一般直接写NOT EXISTS绕开NOT IN的NULL语义后悔药少吃几回。5. 避坑与排查跑这份实验最容易卡住的 5 个点5.1 中文字面量查询返回空集现象第 4、11、12 题里带上海的条件语句不报错但结果永远是 0 行。原因建库时字符集用了latin1或者连接层没设utf8mb4客户端发过去的中文在服务端被解成乱码跟表里的数据对不上。另一种情况是从 .doc 复制的单引号被 Word 转成了中文全角引号‘上海’MySQL 把它当成标识符而不是字符串。解决建库时显式指定DEFAULT CHARACTER SET utf8mb4连接串里加charsetutf8mb4粘贴 SQL 后先把全角引号替换成半角。排查时先跑SELECT SITE FROM CUSTOMER;看数据能不能正常显示能显示说明存没问题再跑SELECT HEX(SITE) FROM CUSTOMER WHERE CNOC01;看编码对不对。5.2 GROUP BY 下 SELECT 了未聚合的列现象第 8 题原写法在 MySQL 8 上报Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column。原因ONLY_FULL_GROUP_BY是 MySQL 5.7 之后的默认sql_mode它要求SELECT里的每一列要么出现在GROUP BY中要么被聚合函数包住。原写法SELECT PCODE, OQUANTITY ... GROUP BY PCODE里的OQUANTITY两条都不满足。解决优先改写语义把OQUANTITY换成SUM(OQUANTITY)或MAX(OQUANTITY)明确你想要这一组的哪个值不要图省事去SET sql_mode关掉检查那样只是把错误藏起来结果仍然是随机的。真要兼容旧代码可以用ANY_VALUE(OQUANTITY)显式声明随便取一个至少读代码的人知道这是有意为之。5.3 子查询返回多行比较直接报错现象第 12、14 题跑出Subquery returns more than 1 row但换一份数据又能跑。原因、!、、都是标量比较运算符右边只能接一行一列的结果。子查询写在没有唯一约束的列上CNAME、SITE返回行数取决于数据量今天一行明天三行。解决确认业务上要不要多值。要就换IN/NOT IN或ANY/ALL不要就给子查询加LIMIT 1或者补上唯一键条件。LIMIT 1只是止血不解决语义问题——如果真有多行你取哪一行都是错的。我一般会在子查询外面套一层SELECT COUNT(*)先看看它到底会返回几行确认清楚再决定改法。5.4 COUNT(*) 和 COUNT(列) 结果对不上现象同一张表COUNT(*)返回 4、COUNT(TELE)返回 3以为语句写错了。原因COUNT(列)跳过NULLCOUNT(*)数行数。TELE允许为空某条客户记录没填电话就会差一行。解决先明确题目问的是有多少条记录还是有多少条填了电话的记录。前者用COUNT(*)后者用COUNT(TELE)。如果要按有电话的客户数统计又得保留没电话的客户行用COUNT(DISTINCT TELE)会更准确它能同时处理重复和空值。5.5 造的数据太少HAVING 过滤完一片空白现象第 2 题HAVING COUNT(PCODE) 2、第 8 题HAVING COUNT(DISTINCT CNO) 2跑出来 0 行怀疑自己语法写错了。原因数据量不够。每个客户只订了一种产品HAVING的条件永远不成立。这跟 SQL 写得对不对没有关系。解决先去查明细SELECT CNO, COUNT(DISTINCT PCODE) FROM SALE GROUP BY CNO;看看分组后的计数分布再决定是补数据还是降条件。造测试数据时我习惯让每道题的边界值都落在数据里第 2 题至少有一个客户订两种以上第 8 题至少有一个产品被两个客户订过第 14 题至少有一个产品没被订过。数据备齐了后面调试就只剩语法问题排查范围小一半。6. 二次开发把这份 .doc 做成可回归的查询练习库这份实验报告最大的价值不是那十几条 SQL而是它定义了一组有明确输入输出的查询题面。把它改造成一个能反复跑的练习库你带新人或者自己复习都有现成的靶子。我的做法是每题一个.sql文件配一份期望结果用脚本批量执行比对。# check_queries.py —— 逐题执行 SQL把结果和期望值比对 import sqlite3, pathlib, sys def run_case(conn, sql_file): sql pathlib.Path(sql_file).read_text(encodingutf-8) cur conn.execute(sql) # 一次只跑一条语句 cols [d[0] for d in cur.description] # 列名 rows cur.fetchall() return cols, rows EXPECT { q01_customer_count.sql: ([customer_cnt], [(4,)]), q05_max_price.sql: ([max_price], [(260.0,)]), q14_not_ordered.sql: ([PCODE, PNAME], [(B03, 空气净化器)]), } conn sqlite3.connect(cpxs.db) fail 0 for f, (want_cols, want_rows) in EXPECT.items(): cols, rows run_case(conn, fcases/{f}) if cols ! want_cols or rows ! want_rows: fail 1 print(f[FAIL] {f}\n got {cols} {rows}\n want {want_cols} {want_rows}) print(fdone, {fail} failed) sys.exit(1 if fail else 0)这段脚本用sqlite3是为了免装服务、pip完就能跑代价是建表脚本要做几处方言替换DECIMAL(10,2)改成REAL、AUTO_INCREMENT去掉、utf8mb4相关语句删掉。表结构和数据与第 2 章那份保持一致SQL 逻辑一字不改。EXPECT字典是我手算出来的期望值写完先故意把q01的期望改成 5看脚本能不能报出[FAIL]确认这套断言真的有效——不然你有可能写了个永远通过的检查器比没有还危险。改造思路上有三点值得动手。第一给每道题补一份错误写法用例比如第 14 题把NOT EXISTS换成NOT IN在SALE.PCODE里插一条NULL看脚本能不能抓出结果差异这比看十遍书都记得牢。第二把第 6、7 题这类原写法有语义偏差的题目单独标出配一份改写前后结果对比的说明文档这是教材里最缺的部分。第三如果要做成给别人的练习包把.sql文件里的答案清空、只留注释形式的题面期望结果放进单独的expected.json避免答案和题目放在一起被一眼看穿。我踩过的坑是一开始把十几道题全写在一个all.sql里用分号切开顺序执行结果中间某一条报错后面全被跳过脚本还显示完成。从那以后我每次拆题都强制一题一文件、一文件一次执行、失败立刻退出宁可多写几个文件也不让一条错误把整批结果带偏。希望这套做法能帮到你。本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
