★ FEATURED ARTICLE 简介《数据库实验5嵌套查询.doc》是一份面向数据库课程学习者与实验课学生的单文件实验报告聚焦 SELECT 统计查询、连接查询与嵌套查询的语法和操作训练。文档围绕 CPXS 数据库展开包含客户数统计、库存求和、分组与 HAVING 筛选、城市与价格区间统计、LIKE 模糊匹配以及按上海客户、产品单价和未被订购产品构造子查询等典型题目便于对照实验目的梳理函数用法与谓词逻辑。资源包共 1 个 doc 文件大小约 642KB结构紧凑适合直接查阅或二次整理内容按实验目的、实验内容与题目示例组织可帮助读者理解统计函数、分组过滤和子查询的嵌套层级。已有 931 人学习可作为课堂实验参考、作业复盘和查询语句练习材料。1. 从一份 .doc 实验报告说起14 条 SQL 里藏着三类查询拿到这份数据库实验五的 .doc多数人的第一反应是照着题目把 SQL 抄进查询窗口能出结果就算过。真跑一遍就会发现实验内容里这十几条语句有一半在不同关系库上表现不一致第 8 条在严格分组模式下直接报错第 14 条只要销售表多于一行就崩第 12 条把甲公司自己也查进了结果。这份材料的价值不在题目本身而在于它把统计查询、连接查询、嵌套查询三块基础能力压进了同一个 CPXS 数据集里每条题都卡在一个真实写法边界上。适合刚学完 SELECT 语法、要在课程实验或手写 SQL 环节把底子打牢的人也适合已经工作、发现自己写子查询全靠试的开发者拿它当一次系统回炉。下面按建表造数、聚合分组、连接、嵌套、排查、验证的顺序拆开每步都能直接复制运行。2. 先把 CPXS 三张表建起来建表脚本与造数边界2.1 三张表的字段语义与主外键关系CPXS 是客户、产品、销售三张表组成的经典多对多模型。CUSTOMER 存客户主数据字段是 CNO客户编号、CNAME公司名、SITE所在城市、TELE联系电话CNO 做主键。PRODUCT 存产品主数据字段是 PCODE产品编号、PNAME产品名称、PRICE单价、STOCKS库存量PCODE 做主键。SALE 是中间表字段是 CNO、PCODE、OQUANTITY订购数量用 (CNO, PCODE) 做联合主键。这个模型之所以适合练嵌套查询原因在于 SALE 把客户和产品连成了多对多一个客户可以订多种产品一种产品也可以被多个客户订。凡是“没有被订购的产品”“和某公司同城的客户”“比某个产品单价高的产品”这类题本质都是在问一个集合与另一个集合的差、交或比较单表条件已经不够用必须落成子查询或者反连接。把这三张表的关系想清楚后面十几条 SQL 的题意就不会跑偏。2.2 建表脚本与初始化数据建表顺序不能乱CUSTOMER 和 PRODUCT 是被引用方必须先建SALE 带两个外键最后建。常见做法是在 MySQL 8.0 或 PostgreSQL 这类关系库上验证两者的严格分组检查行为略有差异后面会专门讲。-- 客户表CNO 主键CNAME 不允许为空 CREATE TABLE CUSTOMER ( CNO CHAR(4) PRIMARY KEY, CNAME VARCHAR(20) NOT NULL, SITE VARCHAR(20), TELE VARCHAR(20) ); -- 产品表PRICE 用两位小数STOCKS 为整数 CREATE TABLE PRODUCT ( PCODE CHAR(4) PRIMARY KEY, 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) );这段脚本有三个参数值得说清楚。CNO 和 PCODE 用 CHAR(4) 定长是为了让后面 LIKE B% 这类前缀匹配结果稳定也方便肉眼对齐。PRICE 用 DECIMAL(8,2) 而不是 FLOAT是因为价格比较出现在第 13 题的子查询里浮点误差会让“高于 A01 单价”这种判断出现意料之外的边界行。SALE 的联合主键是整套实验的隐含前提它保证同一客户对同一产品只有一条记录所以第 2 题里 COUNT(PCODE) 数出来的行数正好等于产品种类数如果业务允许重复下单这里就得换成 COUNT(DISTINCT PCODE)。造数要故意留边界。城市和公司名用 A 市、甲公司这类代称字段语义不变但数据分布要覆盖后面每道题INSERT INTO CUSTOMER VALUES (C001,甲公司,A市,0001-0001), (C002,乙公司,A市,0001-0002), (C003,丙公司,B市,0002-0003), (C004,丁公司,C市,0003-0004); INSERT INTO PRODUCT VALUES (A01,螺丝刀,15.00,800), (A02,扳手,25.00,300), (B01,电钻,180.00,120), (B02,砂纸,8.00,600), (C01,卷尺,12.00,450), (D01,水平仪,90.00,50); INSERT INTO SALE VALUES (C001,A01,300), (C001,A02,150), (C001,B01,50), (C002,A01,120), (C002,B02,220), (C003,A01,250), (C004,C01,80);七条销售记录是算过的C001 订了三种产品用来命中第 2 题的“至少两种”C002 订了两种正好卡在阈值上A01 被三个客户订购且每笔都超过 100命中第 8 题B02 数量 220让第 11 题有第二条结果D01 一条销售记录都不给专门留给第 14 题查“没被订购的产品”。2.3 造数时预留的边界值每道题都依赖某个特定数据分布造数时没铺好SQL 写得再对也跑不出预期结果。下面这张表是我每次重建实验库时对照用的题号依赖的数据特征造数要求第 1 题客户总数至少 4 条客户记录第 2 题订购两种以上产品的客户一个客户挂 3 条 SALE第 4 题A 市客户数A 市放 2 家其他城市各 1 家第 6 题库存超过 500 的产品放 2 个库存分别 800 和 600第 7 题单价 10 到 20 元的产品放 2 个单价 15 和 12第 8 题被两个以上客户订购的产品A01 挂 3 个客户每笔大于 100第 9 题B 开头产品放 2 个单价一高一低第 11 题A 市客户的大额订单至少 2 条数量大于 200第 12 题同城公司A 市放两家其中一家叫甲公司第 13 题高于 A01 单价的产品A02、B01、D01 单价均高于 15第 14 题无销售记录的产品D01 不插入 SALE提示造数完成后先跑一遍SELECT COUNT(*) FROM SALE确认是 7 行。行数不对后面所有统计题的对照都会错位。3. 聚合与分组COUNT/SUM/MAX 与 GROUP BY、HAVING 的执行顺序3.1 聚合函数写在 SELECT 列表里意味着什么COUNT、SUM、MAX、MIN 这类函数不是逐行取值而是把一组行压成一个值。写SELECT COUNT(*) FROM CUSTOMER时整张表被当成一组返回一个数字一旦加上 GROUP BY分组键的每个不同取值就各自成为一组聚合函数对每组分别算一次。这个“组”的概念是后面所有统计题的地基。COUNT 有两个容易混的形态。COUNT() 数的是行数不管列里是不是 NULLCOUNT(列名) 只数该列非 NULL 的行。实验第 1 题用 COUNT() 数客户没问题如果 CUSTOMER 表里 TELE 允许为空写 COUNT(TELE) 就会把没留电话的客户漏掉。第 6 题数产品个数用 COUNT(PCODE) 和 COUNT(*) 结果一样因为 PCODE 是主键、不可能为空但换成可能为空的列就要留心。SUM 和 MAX 遇到 NULL 会直接跳过不会把整组变成 NULL这一点和算术运算里的 NULL 传播不一样。3.2 GROUP BY 之后 SELECT 能带哪些列标准 SQL 的规则很硬SELECT 列表里要么是 GROUP BY 里出现过的列要么是聚合函数不能夹带其他列。原因在于分组之后每组只剩一行而那些没参与分组的列在一组里可能对应多个不同的值数据库不知道该拿哪一个。实验第 8 题的原写法正好踩在这条线上-- 原题写法OQUANTITY 既不在 GROUP BY 里也不是聚合函数 SELECT PCODE, OQUANTITY FROM SALE WHERE OQUANTITY 100 GROUP BY PCODE HAVING COUNT(PCODE) 2;在开启 ONLY_FULL_GROUP_BY 的库上这条直接报错在宽松模式下能出结果但 OQUANTITY 取的是组内任意一行的值换一台机器、换一个执行计划就可能变。题目本意是“订购数量总数”正确写法是把 OQUANTITY 包进聚合函数SELECT PCODE, SUM(OQUANTITY) AS 订购总数 FROM SALE WHERE OQUANTITY 100 GROUP BY PCODE HAVING COUNT(*) 2;改完之后结果稳定A01 命中三个客户订购总数是 300120250670。这里顺带说一下 HAVING COUNT(*) 2 的语义“两个以上”在中文里通常含 2但原题写的是大于 2实际筛出的是三个及以上如果按含 2 理解应改成 2。这种阈值边界在实验报告里不写清楚答辩时很容易被追问。3.3 WHERE 与 HAVING 的执行顺序与分工一条 SELECT 的逻辑执行顺序是 FROM → WHERE → GROUP BY → 聚合 → HAVING → SELECT → ORDER BY。WHERE 在分组之前执行过滤的是原始行HAVING 在分组之后执行过滤的是组。这个顺序决定了两者不能随便互换。实验第 6 题是个典型-- 原题写法先按库存分组再用 HAVING 过滤 SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS 500;这条 SQL 的结果不是“库存超过 500 的产品个数”而是“每个大于 500 的库存档位各有多少产品”返回的是多行。库存 800 和 600 各一行每行计数为 1。题目要的是单个数字应该把条件下推到 WHERESELECT COUNT(*) AS 产品数 FROM PRODUCT WHERE STOCKS 500;反过来第 2 题的HAVING COUNT(PCODE) 2就不能下推到 WHERE因为“订购种类数”是分组之后才算出来的WHERE 阶段这个值还不存在。判断标准很简单条件里用的是原始列放 WHERE用的是聚合结果放 HAVING。3.4 实验里统计题的逐条拆解把第 1 到第 9 题按语义归一下类能看出命题人想覆盖的几种聚合形态题号查询意图关键写法容易错在哪1客户总数COUNT(*)误用 COUNT(可空列)2订购两种以上产品的客户GROUP BY CNO HAVING COUNTHAVING 写成 WHERE3每个客户订购总数GROUP BY CNO SUM漏 GROUP BY4A 市客户公司数WHERE SITEA市 COUNT用 GROUP BY SITE 返回多行5最高单价MAX(PRICE)无6库存超 500 的产品数WHERE STOCKS500 COUNT用 GROUP BY STOCKS 返回多行7单价 10 到 20 的产品数WHERE PRICE BETWEEN COUNT按 PRICE 分组变成每个单价一行8大额订单的产品及总数WHERE GROUP BY HAVING SUMSELECT 夹带非分组列9B 开头产品最低单价WHERE LIKE B% MIN大小写与排序规则差异第 7 题和第 6 题是同一种错法的两个变体把本该放 WHERE 的范围条件写成了 GROUP BY 的分组键结果从“一个数”变成“多行”。第 9 题的 LIKE B% 在区分大小写的排序规则下只匹配大写 B如果产品编号里混入了小写 b结果会少稳妥写法是WHERE UPPER(PCODE) LIKE B%但这样会让索引失效数据量小的时候无所谓数据量大要另想办法。4. 多表连接从逗号连接改写到显式 JOIN4.1 笛卡尔积是怎么被连接条件消掉的两张表放在 FROM 后面用逗号隔开数据库先做笛卡尔积左表每一行和右表每一行配对行数是两表行数之积。WHERE 里的等值条件再把不匹配的组合剔掉剩下的就是连接结果。实验第 11 题就是这个套路SELECT SALE.CNO, PCODE, CNAME, OQUANTITY FROM SALE, CUSTOMER WHERE CUSTOMER.CNO SALE.CNO AND CUSTOMER.SITE A市 AND SALE.OQUANTITY 200;SALE 有 7 行、CUSTOMER 有 4 行笛卡尔积是 28 行CUSTOMER.CNO SALE.CNO把每行销售记录对到它的客户上还是 7 行再叠加城市和数量两个过滤条件剩下 2 行C001 的 A01 300 和 C002 的 B02 220 都满足注意 C002 的城市也是 A 市。这条 SQL 里 SELECT 列表的 CNO 必须带表前缀因为两张表都有 CNO 列不写前缀数据库无法判断取哪一列。4.2 隐式连接改写 INNER JOIN 的对照逗号连接和 INNER JOIN 在语义上等价区别在可读性和出错概率。把第 11 题改写成显式 JOINSELECT s.CNO, s.PCODE, c.CNAME, s.OQUANTITY FROM SALE AS s JOIN CUSTOMER AS c ON c.CNO s.CNO WHERE c.SITE A市 AND s.OQUANTITY 200;改写做了三件事连接条件从 WHERE 挪到 ON过滤条件留在 WHERE表名用短别名。这样一眼能看出哪两个条件负责“配对”、哪两个条件负责“筛行”。三表以上连接时这个优势更明显——实验第 13 题如果把 PRODUCT 也并进来用逗号写会变成一堆 AND漏一个条件就悄悄多出几倍行数。注意ON 和 WHERE 在 INNER JOIN 下可以互换但在 LEFT JOIN 下不能。ON 决定右表哪一行能配上WHERE 是在连接结果上再过滤把右表的过滤条件错写到 WHERELEFT JOIN 会退化成 INNER JOIN。4.3 连接条件漏写、错写的三种症状第一种症状是行数暴涨。漏掉连接条件时两表直接做笛卡尔积-- 漏写连接条件7 × 4 28 行 SELECT COUNT(*) FROM SALE, CUSTOMER; -- 连接条件写成不等值每行销售对到 3 个非本客户7 × 3 21 行 SELECT COUNT(*) FROM SALE, CUSTOMER WHERE CUSTOMER.CNO SALE.CNO;第二种症状是结果偏少。连接条件写错列比如把c.CNO s.CNO误写成c.CNO s.PCODE字符型比较虽然不报错但几乎匹配不上结果接近空集。第三种是重复计数。一对多连接后直接 COUNT(*)会把主表的一行数成多行第 11 题如果再加一个和 SALE 一对多的表统计客户数时就得用 COUNT(DISTINCT s.CNO)。判断连接写没写对最快的办法是先SELECT *看前 20 行再单独SELECT COUNT(*)和两表行数比一比。连接结果的行数一般介于“大表行数”和“两表行数之积”之间落在这个区间外就说明条件有问题。5. 嵌套查询从 到 EXISTS 的写法与五个排查记录5.1 单值子查询、、 后面接什么子查询放在比较运算符右边时数据库要求它最多返回一行一列。实验第 12 题和第 13 题都是这个形态-- 第 13 题先查出 A01 的单价再拿外层每行去比 SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE AS s JOIN PRODUCT AS p ON p.PCODE s.PCODE WHERE p.PRICE (SELECT PRICE FROM PRODUCT WHERE PCODE A01);子查询SELECT PRICE FROM PRODUCT WHERE PCODE A01返回 15.00 这一个值外层再逐行比较。这种写法叫不相关子查询子查询不依赖外层当前行理论上只需算一次。它成立的前提是 PCODE 是主键、条件唯一如果哪天基准条件换成 PNAME而产品名允许重复子查询返回多行整条语句立刻报“Subquery returns more than 1 row”。5.2 多值子查询IN、NOT IN 与 ALL/ANY子查询返回一列多行时比较运算符要换成 IN 或 ANY/ALL。实验第 14 题是反面教材-- 原题! 只接受单值SALE 有 7 行时直接报错 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE ! (SELECT PCODE FROM SALE); -- 修正一NOT IN SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE WHERE PCODE IS NOT NULL); -- 修正二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 );NOT IN 的语义是“不等于集合里的每一个值”只要子查询结果里混进一个 NULL整个条件对任何行都返回 UNKNOWN外层静默返回空集不报错。这是嵌套查询里最阴的一个坑因为 SQL 写得语法正确、执行也不报错只是结果不对。SALE.PCODE 在本实验里有外键约束、不会为 NULL但生产库里中间表字段可空的情况很常见所以我在子查询里习惯性加WHERE 列 IS NOT NULL或者直接改 NOT EXISTS。5.3 相关子查询与 EXISTS 的执行方式相关子查询的子查询体引用了外层的列不能只算一次要对候选行逐行求值。EXISTS 只关心子查询有没有返回行不关心返回什么所以子查询里写SELECT 1就够了。上面第 14 题的 NOT EXISTS 版本语义是“对 PRODUCT 的每一行去 SALE 里找有没有同 PCODE 的记录找不到就留下”和 NOT IN 结果一致但 NULL 安全。性能上现代优化器对 NOT IN 和 NOT EXISTS 常常生成相同的执行计划不必为了性能强行改写选择依据应该是语义清晰度和 NULL 安全性。数据量小的时候两者差别可以忽略真正要关注的是子查询列上有没有索引——SALE.PCODE 上有外键通常自带索引这也是实验环境跑起来不慢的原因。5.4 五个排查记录记录一第 14 条报“Subquery returns more than 1 row”。现象是语句直接失败提示子查询返回多行。原因是!和一样只接受单值而 SALE 表有 7 行。解决办法是把运算符换成 NOT IN或者改写成 NOT EXISTS如果业务上确实只想排除某一个产品那就把子查询条件补全成WHERE PCODE A01这类唯一条件。记录二NOT IN 写对了却查出空结果。现象是语法没问题、执行不报错但一行都没有。原因是子查询结果里含 NULLNULL 参与不等于比较得到 UNKNOWN整个 WHERE 过滤掉所有行。解决办法是在子查询里加WHERE 列 IS NOT NULL或者换 NOT EXISTS。验证方法是先单独跑一遍子查询看结果里有没有 NULL。记录三第 8 条在严格模式下报“not in GROUP BY”。现象是 SELECT 列表里的 OQUANTITY 不在 GROUP BY 中也不被聚合函数包住。原因是分组后该列在一组内有多个取值数据库无法确定返回哪个。解决办法是把 OQUANTITY 改成 SUM(OQUANTITY)或者从 SELECT 列表里删掉不要靠关闭 ONLY_FULL_GROUP_BY 来绕过那只是把错误藏起来。记录四第 12 条把甲公司自己也查了出来。现象是结果里出现了甲公司而题目问的是“与甲公司同城的其他公司”。原因是子查询只取了城市外层没有再排除本公司。解决办法是加AND CNAME 甲公司同时把改成 IN防止同名公司本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
