MySQL商城库查询实验：单表、聚合、连接与子查询

发布时间：2026/10/27 2:32:11

简介：面向国家开放大学 MySQL 数据库应用课程学习者的实验训练 2 配套材料，聚焦数据查询这一核心技能模块，可服务于课程实验报告撰写、上机练习对照与期末复习梳理。内容按实验 2.1 至 2.16 顺序组织，逐题给出需求描述与分析思路：从单表字段查询、AND 多条件筛选，到 DISTINCT 去重、ORDER BY 升降序排列、GROUP BY 多列分组，再到 COUNT、SUM、AVG、MAX、MIN 等聚合函数的典型用法，并延伸至内连接、左右外连接、复合条件连接及 IN、比较运算符、EXISTS 嵌套子查询，均以汽车用品网上商城为统一业务场景，便于连贯理解表间关系与语句组合逻辑。资源包内含 1 个 PDF 文件，约 1.75MB，排版完整、便于打印与批注。目前已有 3338 人学习下载，适合需要按实验进度逐条对照语句写法、理解分析要点并查漏补缺的学习者使用。

1. 一套商城库的 19 个实验：查询训练到底练的是什么

MySQL 数据库应用这门课里，数据查询是最容易「看懂了但写不出」的一段。教材上每个语法点只有两三行示例，真到业务里让你查「每个用户今年的消费总额并按金额倒序」，很多人第一反应还是去搜索。这份课程实验训练的文档资料把 19 个实验排成了一条线：单表字段查询、多条件、DISTINCT、ORDER BY、GROUP BY、五个聚合函数、内连接外连接、复合条件连接、IN/EXISTS/ANY/ALL、UNION。它不教你装环境，也不讲事务和锁，就集中练一件事——给定一套商城库的表结构，把一句业务说法翻译成一条能跑出正确结果的 SQL。适合三类人：刚学完 SELECT 语法但写不出业务查询的、要做实验报告但想真弄明白的、工作里临时被拉去写报表 SQL 的。下面按这条线把每个实验的写法、参数和翻车点拆开。

2. 单表查询：字段筛选、多条件与 DISTINCT 的三层边界

2.1 六张表先立起来：建库脚本与字段类型选择

实验题里的商品表、订单表、用户表、评论表、购物车表、类别表，我按最小可用原则重建了一份，表名和字段含义对齐实验描述，方便对着题目改。金额一律用 DECIMAL，促销标志用 TINYINT，日期用 DATE——这三条选择在后面几个实验里会直接影响结果对不对。

-- 商城库：字符集统一 utf8mb4，金额统一 DECIMAL，避免后期对账误差 CREATE DATABASE IF NOT EXISTS shop_demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE shop_demo; CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(32) NOT NULL, create_date DATE NOT NULL, -- 注册日期，后面按年去重、按年过滤都靠它 KEY idx_create_date (create_date) ) ENGINE=InnoDB; CREATE TABLE category ( cate_id INT PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(32) NOT NULL ) ENGINE=InnoDB; CREATE TABLE goods ( goods_id INT PRIMARY KEY AUTO_INCREMENT, goods_name VARCHAR(64) NOT NULL, cate_id INT NOT NULL, price DECIMAL(10,2) NOT NULL, -- 金额不用 FLOAT，避免比较和求和失真 stock INT NOT NULL DEFAULT 0, is_promotion TINYINT(1) NOT NULL DEFAULT 0, -- 0 不促销 1 促销 KEY idx_cate (cate_id), KEY idx_price (price) ) ENGINE=InnoDB; CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_total DECIMAL(10,2) NOT NULL, order_date DATE NOT NULL, KEY idx_user (user_id), KEY idx_date (order_date) ) ENGINE=InnoDB; CREATE TABLE order_detail ( detail_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, goods_id INT NOT NULL, quantity INT NOT NULL, KEY idx_order (order_id), KEY idx_goods (goods_id) ) ENGINE=InnoDB; CREATE TABLE comments ( comment_id INT PRIMARY KEY AUTO_INCREMENT, goods_id INT NOT NULL, user_id INT NOT NULL, content VARCHAR(255) DEFAULT NULL, KEY idx_goods (goods_id) ) ENGINE=InnoDB; CREATE TABLE cart ( cart_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, goods_id INT NOT NULL, quantity INT NOT NULL DEFAULT 1, KEY idx_user (user_id) ) ENGINE=InnoDB;

逻辑说明：六张表之间只靠业务字段关联，没有建外键约束。实验阶段数据量小，外键反而会在插数据时增加顺序负担；真到生产环境再加约束也不迟，索引先建上就够用。is_promotion用 TINYINT(1) 而不是 CHAR(1)，是为了让= 1这种写法在类型上完全对齐，避免隐式转换把索引废掉。索引只加在会被 WHERE 和 JOIN 命中的列上，goods_name故意没建，因为实验里它是等值查询，几十行数据全表扫一遍更快。

灌几条能覆盖所有实验的数据，后面每段 SQL 都能直接跑：

INSERT INTO category (cate_id, cate_name) VALUES (1, '汽车玻璃'), (2, '汽车内饰'), (3, '汽车电子'); INSERT INTO goods (goods_id, goods_name, cate_id, price, stock, is_promotion) VALUES (1, '挡风玻璃', 1, 880.00, 30, 1), (2, '侧窗玻璃', 1, 420.00, 60, 0), (3, '坐垫', 2, 199.00, 120, 1), (4, '行车记录仪', 3, 599.00, 45, 1), (5, '脚垫', 2, 3.50, 500, 0); INSERT INTO users (user_id, user_name, create_date) VALUES (1, 'A同学', '2023-03-11'), (2, 'B同学', '2024-01-05'), (3, 'C同学', '2024-07-19'); INSERT INTO orders (order_id, user_id, order_total, order_date) VALUES (1, 1, 880.00, '2024-03-01'), (2, 1, 99.50, '2024-03-02'), (3, 2, 599.00, '2024-03-02'), (4, 3, 23.00, '2024-03-03'); INSERT INTO order_detail (order_id, goods_id, quantity) VALUES (1, 1, 1), (2, 3, 1), (3, 4, 1), (4, 5, 2); INSERT INTO comments (goods_id, user_id, content) VALUES (1, 1, '包装完好'), (1, 2, '尺寸合适'), (1, 1, '第二次购买'), (3, 3, NULL); INSERT INTO cart (user_id, goods_id, quantity) VALUES (1, 2, 2), (1, 4, 1), (2, 5, 3);

这批数据的用处在于「脏」得恰到好处：同一个用户对同一件商品评论了两次，正好测 DISTINCT；脚垫单价 3.50，正好卡在「价格小于 5」的边界上；评论表里留了一条 content 为 NULL，后面讲聚合函数和 NOT IN 时会用到。

2.2 字段查询与多条件：WHERE 里 AND 和 OR 的优先级

实验 2.1 的两个查询都是纯单表，判断依据很简单——需要的列是不是都在同一张表里。查询商品名称为「挡风玻璃」的商品信息，商品名和商品信息都在商品表，直接查；查询 ID 为 1 的订单，订单信息全在订单表，也是单表。

-- 实验 2.1-1：按商品名称精确匹配，只取需要的列 SELECT goods_id, goods_name, price, stock FROM goods WHERE goods_name = '挡风玻璃'; -- 实验 2.1-2：订单主键查询，主键等值是最快的访问路径 SELECT order_id, user_id, order_total, order_date FROM orders WHERE order_id = 1;

逻辑说明：SELECT后面显式列出字段而不是SELECT *，实验阶段看不出差别，但养成习惯之后，接生产库时就不会把几十个字段连同大文本列一起拉回来。参数上唯一要注意的是字符串比较的规则：goods_name用 utf8mb4_general_ci 排序规则时大小写不敏感，'挡风玻璃'和全角半角混写可能匹配不上，实验里统一用中文全角最稳。

实验 2.2 的多条件查询是「是否促销」加「价格小于 1000」，两个条件都在商品表，没有跨表问题，难点在条件的组合方式。

-- 实验 2.2：两个条件同时满足用 AND，促销标记和价格都是商品表字段 SELECT goods_id, goods_name, price, is_promotion FROM goods WHERE is_promotion = 1 AND price < 1000;

逻辑说明：AND要求两侧同时为真，结果里只剩促销且价格低于 1000 的商品。参数含义：is_promotion = 1是 TINYINT 的等值判断；price < 1000用的是严格小于，恰好 1000 的商品不会出现。如果业务口径是「不超过 1000」，就得改成<=，这类边界差异在实验报告里最好写清楚，不然对账时会差一条记录。

真正容易翻车的是AND和OR混用。比如「促销商品或者价格低于 1000 的商品里，只要类别为 1 的」，写成WHERE is_promotion = 1 OR price < 1000 AND cate_id = 1就错了——AND优先级高于OR，等价于促销 OR (价格<1000 AND 类别=1)，促销但类别为 2 的商品也会被带出来。正确写法是加括号：

-- AND 优先级高于 OR，混用时必须显式加括号锁定语义 SELECT goods_id, goods_name, price FROM goods WHERE (is_promotion = 1 OR price < 1000) AND cate_id = 1;

这条括号规则我见过太多人栽在上面，实验数据少的时候结果看着「差不多对」，量一上来就是成片的脏数据。

2.3 DISTINCT：去的是整行组合，不是某一列

实验 2.3 的两个小问看着不同，本质是同一件事：结果集里出现了重复行，需要压缩。查询对商品 ID 为 1 发表过评论的用户 ID，同一个用户可以评论多次，直接查会重复；查会员的创建时间段，一年里注册了多个会员，年份也会重复。

-- 实验 2.3-1：同一用户对同一商品多次评论，DISTINCT 压掉重复 user_id SELECT DISTINCT user_id FROM comments WHERE goods_id = 1; -- 实验 2.3-2：按年份去重，YEAR() 把日期截断成整数年 SELECT DISTINCT YEAR(create_date) AS create_year FROM users ORDER BY create_year;

逻辑说明：DISTINCT作用在SELECT列表的整体上，不是只作用于紧跟其后的那一列。单列查询时它等价于「这一列去重」，一旦写成SELECT DISTINCT user_id, goods_id，去重依据就变成了这两列的组合，只要组合不同就算不同行。参数上YEAR(create_date)是对列做函数运算，会导致idx_create_date索引失效，实验数据无所谓，真表上千万级数据要改成范围查询create_date >= '2024-01-01' AND create_date < '2025-01-01'。

还有一个常被忽略的点：DISTINCT会把多个 NULL 视为同一个值，只保留一个。评论表里 content 为 NULL 的那条记录，用SELECT DISTINCT content FROM comments查出来只会有一个 NULL 行。如果业务上要统计「有多少条评论内容为空」，得用COUNT(*) - COUNT(content)或者SUM(content IS NULL)，不能靠 DISTINCT 数行数。

统计去重后的数量时，COUNT(DISTINCT col)比「先 DISTINCT 再套一层子查询 COUNT」更直接：

-- 统计对商品 1 发表过评论的去重用户数 SELECT COUNT(DISTINCT user_id) AS user_cnt FROM comments WHERE goods_id = 1;

参数说明：COUNT(DISTINCT user_id)会忽略 NULL，如果 user_id 允许为空，统计结果会比实际出现的用户数少。这类细节在实验报告里写一句，比只贴一条 SQL 有价值得多。

3. 排序、分组与聚合：ORDER BY、GROUP BY 和五个聚合函数怎么配

3.1 ORDER BY：DESC 只作用于紧挨着的那一列

实验 2.4 的第一个查询是「类别 ID 为 1 的所有商品，按商品 ID 降序」，单列排序，写法直接。

-- 实验 2.4-1：单列降序，DESC 紧跟排序键 SELECT goods_id, goods_name, price FROM goods WHERE cate_id = 1 ORDER BY goods_id DESC;

逻辑说明：ORDER BY goods_id DESC表示按商品 ID 从大到小排，DESC只修饰goods_id这一列。如果写成ORDER BY goods_id, price DESC，含义就变成「先按 goods_id 升序，goods_id 相同时再按 price 降序」——第一个列没写方向，默认是 ASC。这个规则在实验里不明显，一旦遇到多列排序，方向写错就会得到一份「局部有序、整体乱序」的结果。

实验 2.4 的第二个查询是「今年新增的会员，按用户名排序」，这里的关键不是 ORDER BY，而是「今年」怎么表达。

-- 实验 2.4-2：用固定年份便于复现，生产环境换成 YEAR(CURDATE()) SELECT user_id, user_name, create_date FROM users WHERE create_date >= '2024-01-01' AND create_date < '2025-01-01' ORDER BY user_name ASC;

逻辑说明：用范围条件而不是YEAR(create_date) = 2024，是为了让idx_create_date能用上；>= 下一年1月1日这种半开区间写法，能完整覆盖 12 月 31 日当天带时分秒的时间戳，不会像<= '2024-12-31'那样漏掉当天下午注册的用户。ORDER BY user_name ASC里的 ASC 可以省略，但中文用户名的排序结果依赖排序规则，utf8mb4_general_ci 下大致按 Unicode 码点排，和拼音顺序不完全一致。要做拼音排序得在建表时指定utf8mb4_zh_0900_as_cs之类的规则，实验阶段知道这个边界就行。

NULL 在排序里的位置也值得记一笔：MySQL 把 NULL 当成最小值，ASC时排在最前，DESC时排在最后。如果想让 NULL 固定排末尾，可以用ORDER BY col IS NULL, col，先按「是否为空」排一轮再按值排。

3.2 GROUP BY：分组键决定了结果集有几行

实验 2.5 的第一个查询是「每个用户的消费总金额」，这是分组聚合最典型的形态：按 user_id 分组，每组内部对 order_total 求和。

-- 实验 2.5-1：一个用户一行，SUM 汇总该用户所有订单 SELECT user_id, SUM(order_total) AS total_amount FROM orders GROUP BY user_id;

逻辑说明：GROUP BY user_id把订单表按用户切成若干组，SUM(order_total)在每组内独立计算，最终每个 user_id 输出一行。SELECT列表里出现的非聚合列必须出现在GROUP BY中，否则在ONLY_FULL_GROUP_BY模式下会直接报错——MySQL 5.7 之后的默认 sql_mode 就带这个开关，很多人从旧教程抄来的写法一跑就挂，原因就在这里。

实验 2.5 的第二个查询是「类别价格一样的各种商品数量总和」，分组依据从一列变成两列。

-- 实验 2.5-2：多列分组，分组键是 (cate_id, price) 的组合 SELECT cate_id, price, COUNT(*) AS goods_cnt FROM goods GROUP BY cate_id, price ORDER BY cate_id, price;

逻辑说明：多列分组的语义是「组合相同才归为一组」，(1, 880.00)和(1, 420.00)属于两个不同的组，哪怕类别相同。结果行数等于分组键去重后的组合数，而不是某个单列的去重数——这是初学者最容易估错的地方，写分页或者做报表汇总时会直接导致数字对不上。参数上COUNT(*)统计组内行数，不受任何列是否为 NULL 的影响；换成COUNT(price)就会跳过 price 为 NULL 的行，语义完全不同。

多列分组还有一个隐性成本：分组键越多，MySQL 需要维护的临时分组结构越大。实验阶段数据量小，可以随便写；真表上如果GROUP BY的列没有合适索引，执行计划里会出现Using temporary; Using filesort，几百万行就能跑出几十秒。

3.3 五个聚合函数：NULL、精度和字符串列

实验 2.6 到 2.10 把 COUNT、SUM、AVG、MAX、MIN 各过了一遍，题目本身不难，难的是每个函数的边界行为。先把五组查询摆出来：

-- 实验 2.6-1：类别数量，COUNT(*) 统计行数 SELECT COUNT(*) AS cate_cnt FROM category; -- 实验 2.6-2：每天的接单数，按日期分组后计数 SELECT order_date, COUNT(*) AS order_cnt FROM orders GROUP BY order_date; -- 实验 2.7：每天的销售额，按日期分组后对订单总价求和 SELECT order_date, SUM(order_total) AS amount FROM orders GROUP BY order_date; -- 实验 2.8：所有订单的平均销售金额 SELECT AVG(order_total) AS avg_amount FROM orders; -- 实验 2.9：库存最多的商品；用户名字母序最靠前者 SELECT MAX(stock) AS max_stock FROM goods; SELECT MAX(user_name) AS max_name FROM users; -- 实验 2.10：价格最低的商品 SELECT MIN(price) AS min_price FROM goods;

逻辑说明与参数：COUNT(*)统计的是行数，包含所有列都为 NULL 的行；COUNT(列名)只统计该列非 NULL 的行数；COUNT(DISTINCT 列名)再去一层重，三者结果可能完全不同，实验报告里最好把三条都跑一遍对比。SUM和AVG都会自动忽略 NULL，也就是说 10 条订单里 3 条金额为 NULL 时，AVG的分母是 7 不是 10，这一点和很多人直觉相反，做人均消费类指标时必须用SUM(x) / COUNT(*)而不是直接AVG(x)。

MAX和MIN不只是数值函数，字符串列也能用。MAX(user_name)返回按排序规则比较下来最大的那个名字，MIN反之。这个特性在实验里只是演示，实际业务里更常见的用法是「取每组最新一条记录的时间」，配合GROUP BY使用。另外MAX(stock)只返回值本身，拿不到对应的商品 ID——想同时要 ID 和数量，得用子查询或者窗口函数，这是实验 2.9 没展开但很关键的一步。

聚合函数和GROUP BY的组合顺序也有讲究：WHERE在分组前过滤行，HAVING在分组后过滤组。比如「消费总额超过 500 的用户」，过滤条件依赖聚合结果，只能写在 HAVING 里：

-- HAVING 过滤的是分组后的结果，WHERE 阶段还拿不到 SUM 值 SELECT user_id, SUM(order_total) AS total_amount FROM orders GROUP BY user_id HAVING SUM(order_total) > 500;

把这条写成WHERE SUM(order_total) > 500会直接报错，因为 WHERE 执行时聚合还没发生。这个顺序感建立起来，后面写复杂报表才不会乱。

4. 连接查询：INNER JOIN、LEFT/RIGHT JOIN 与复合条件的落点

4.1 内连接：用外键把两张表搭起来

实验 2.11 的两个查询都要跨表：订单表里有 user_id，但没有用户名；购物车表里有 goods_id 和 user_id，但没有商品名称。内连接解决的就是「两张表各拿一部分字段，按关联键拼成一行」。

-- 实验 2.11-1：订单表与用户表按 user_id 内连接，拿到下单人姓名 SELECT o.order_id, u.user_name, o.order_total, o.order_date FROM orders AS o INNER JOIN users AS u ON o.user_id = u.user_id ORDER BY o.order_id;

逻辑说明：INNER JOIN只保留两侧都能匹配上的行，订单表里有 user_id 但用户表里查不到对应用户时，这条订单不会出现在结果里。ON o.user_id = u.user_id是连接条件，写法和 WHERE 一样但语义不同——它描述的是「两张表怎么拼」，不是「拼完怎么筛」。给表起别名o和u之后，所有字段都带前缀，多表连接时不会因为同名字段报 ambiguous 错误。

实验 2.11 的第二个查询要连三张表：购物车表提供数量，用户表提供用户名，商品表提供商品名。

-- 实验 2.11-2：三表内连接，购物车 → 用户 → 商品 SELECT u.user_name, g.goods_name, c.quantity FROM cart AS c INNER JOIN users AS u ON c.user_id = u.user_id INNER JOIN goods AS g ON c.goods_id = g.goods_id ORDER BY u.user_name, g.goods_name;

逻辑说明：三表连接时先确定「主表」再往外扩。这里以 cart 为起点，因为它同时持有 user_id 和 goods_id 两个外键，是最自然的连接枢纽。连接顺序不影响最终结果，但影响可读性——把驱动表写在最前面，后面的 JOIN 像链条一样挂上去，出问题时容易定位是哪一段没匹配上。参数上注意c.goods_id = g.goods_id这种等值连接会走索引，如果两张表的关联字段类型不一致（比如一边 INT 一边 VARCHAR），MySQL 会做隐式转换，索引直接失效。

4.2 外连接：LEFT JOIN 的保留表和 RIGHT JOIN 的等价改写

实验 2.12 要求「列出所有用户 ID 以及他们的评论，如果有的话」。关键词是「所有用户」——没评论过的用户也要出现在结果里，评论列补 NULL，这就是外连接存在的意义。

-- 实验 2.12-1：users 是保留表，没有评论的用户评论列补 NULL SELECT u.user_id, c.comment_id, c.content FROM users AS u LEFT JOIN comments AS c ON u.user_id = c.user_id ORDER BY u.user_id, c.comment_id;

逻辑说明：LEFT JOIN以左表为保留表，左表每一行至少输出一次，右表匹配不上就整行填 NULL。这里的左表是 users，所以三个用户都会出现，其中没有评论的用户评论字段为 NULL。判断「哪些用户没评论过」，可以在 WHERE 里加c.comment_id IS NULL，这是外连接最常用的一种衍生写法。

实验 2.12 的第二问用 RIGHT JOIN 实现同一个需求：

-- 实验 2.12-2：RIGHT JOIN 的保留表在右侧，结果与上一条等价 SELECT u.user_id, c.comment_id, c.content FROM comments AS c RIGHT JOIN users AS u ON c.user_id = u.user_id ORDER BY u.user_id, c.comment_id;

逻辑说明：RIGHT JOIN以右表为保留表，所以要把 users 放到 JOIN 的右边。两条 SQL 结果完全一致，区别只是书写顺序。实际工作中我基本只用 LEFT JOIN，把保留表固定放在左边，读起来方向统一，团队里也不会因为「这条到底是左保留还是右保留」来回确认。RIGHT JOIN 更多出现在老代码和教学题里，能看懂就行，新写的一律 LEFT。

外连接还有一个反直觉的地方：ON 后面的条件和 WHERE 后面的条件效果不一样。把c.content = '包装完好'写在 ON 里，没匹配上的用户仍然保留；写在 WHERE 里，NULL 行会被过滤掉，外连接直接退化成内连接。这个差异在 4.3 节展开。

4.3 复合条件连接：ON 加条件和 WHERE 加条件不是一回事

实验 2.13 的第一个查询是「用户 ID 为 1 的订单信息和客户名」，在内连接基础上加一个过滤条件。

-- 实验 2.13-1：内连接 + WHERE 精确过滤，只保留 user_id = 1 的订单 SELECT u.user_name, o.order_id, o.order_total, o.order_date FROM orders AS o INNER JOIN users AS u ON o.user_id = u.user_id WHERE u.user_id = 1 ORDER BY o.order_id;

逻辑说明：内连接下 WHERE 和 ON 加条件的最终结果相同，都是先拼再筛，优化器通常会把这个条件下推到连接过程中。写法上建议把「表与表怎么关联」放 ON，「业务筛选条件」放 WHERE，语义分层清晰，后面改成外连接时也不会踩坑。

实验 2.13 的第二个查询要连购物车和商品表拿价格，再按价格排序：

-- 实验 2.13-2：连接后排序，ORDER BY 作用在最终结果集上 SELECT g.goods_name, g.price, c.quantity FROM cart AS c INNER JOIN goods AS g ON c.goods_id = g.goods_id ORDER BY g.price DESC;

逻辑说明：ORDER BY永远在连接、过滤、分组之后执行，写多少列都不影响连接逻辑。参数上g.price DESC用的是商品表的价格列，如果购物车里有促销价字段，得想清楚是按下单价排还是按成交价排，这两种口径在报表里经常被混用。

外连接下 ON 和 WHERE 的差异就必须说清楚了。假设要「列出所有用户，以及他们内容为『包装完好』的评论」：

-- 条件写在 ON：所有用户保留，不匹配的评论列为 NULL SELECT u.user_id, c.comment_id, c.content FROM users AS u LEFT JOIN comments AS c ON u.user_id = c.user_id AND c.content = '包装完好'; -- 条件写在 WHERE：NULL 行被过滤，等价于内连接 SELECT u.user_id, c.comment_id, c.content FROM users AS u LEFT JOIN comments AS c ON u.user_id = c.user_id WHERE c.content = '包装完好';

第一条会输出全部三个用户，只有匹配上的那一行有评论内容，其余两行评论列为 NULL；第二条只输出真正有这条评论的用户，没评论的用户被WHERE NULL = '包装完好'这个永假条件筛掉了。这个差异是外连接最常见的翻车点，判断标准很简单：过滤条件针对保留表的列，写 WHERE；针对非保留表的列且希望保留 NULL 行，写 ON。

5. 嵌套查询与集合运算：IN/EXISTS/ANY/ALL/UNION 的写法与五类排查

5.1 IN 与 NOT IN：子查询先跑，NULL 会让结果整体失效

实验 2.14 是嵌套查询的入口：先查订单明细表拿到商品 1 对应的订单 ID，再拿这些 ID 回订单表查用户。

-- 实验 2.14-1：IN 子查询，内层先执行，外层按结果集匹配 SELECT order_id, user_id, order_total FROM orders WHERE order_id IN ( SELECT order_id FROM order_detail WHERE goods_id = 1 );

逻辑说明：IN后面的子查询返回一列值，外层用=的扩展形式逐个匹配。子查询和外层查询没有依赖关系，属于不相关子查询，MySQL 一般会先算出内层结果再去重，然后驱动外层查询。参数上要保证子查询只返回一列——SELECT order_id, goods_id这种多列子查询配IN会直接报错。

把 IN 换成 NOT IN 就是实验 2.14 的第二问：

-- 实验 2.14-2：NOT IN 的 NULL 陷阱，子查询结果里只要有 NULL 就全盘失效 SELECT order_id, user_id FROM orders WHERE order_id NOT IN ( SELECT order_id FROM order_detail WHERE goods_id = 1 );

逻辑说明：NOT IN等价于对子查询结果里的每个值做<>比较再取 AND。只要子查询结果里出现一个 NULL，x <> NULL的结果就是 UNKNOWN，整个条件永远不成立，最终返回空集。这是 SQL 里最经典的坑之一，血泪经验是：子查询列可能为 NULL 时，永远不要用 NOT IN，改用 NOT EXISTS 或者加WHERE 列 IS NOT NULL。

5.2 EXISTS：只判断有没有，不搬运数据

实验 2.16 用 EXISTS 判断用户是否存在。EXISTS 的语义和 IN 不同：它不关心子查询返回什么值，只看子查询有没有返回行。

-- 实验 2.16-1：存在订单的用户，EXISTS 只判断子查询是否返回行 SELECT u.user_id, u.user_name FROM users AS u WHERE EXISTS ( SELECT 1 FROM orders AS o WHERE o.user_id = u.user_id );

逻辑说明：这是一个相关子查询，内层引用了外层的u.user_id，所以不能像 IN 那样一次算完，而是外层每取一行就执行一次内层。SELECT 1是习惯写法，写SELECT *结果一样但会让人误以为要取数据。参数上相关子查询的性能取决于orders.user_id上有没有索引，有索引时每行只是一次索引查找，没索引就是外层行数乘以内层行数的笛卡尔级扫描。

实验 2.16 的第二个查询在 EXISTS 外面再加一层过滤：

-- 实验 2.16-2：类别存在且价格低于 5 的商品 SELECT g.goods_id, g.goods_name, g.price FROM goods AS g WHERE g.price < 5 AND EXISTS ( SELECT 1 FROM category AS c WHERE c.cate_id = g.cate_id );

逻辑说明：price < 5是普通过滤，EXISTS 保证类别表里确实有这个类别。这类「存在性校验」在实际业务里很常见——查出来的商品必须挂在有效分类下，否则前端渲染会出空指针。参数上EXISTS和IN在该场景下结果一致，区别在优化器怎么处理：子查询结果集小的时候 IN 更快，外层结果集小、内层有索引的时候 EXISTS 更快。实验数据量下感受不到差异，记住判断依据就行。

5.3 ANY / ALL 与 UNION / UNION ALL

实验 2.17 和 2.18 是 ANY 与 ALL 的对照，两者都跟在比较运算符后面，区别是「任意一个满足」还是「全部满足」。

-- 实验 2.17：比类别 2 中任意一个商品价格高即可，等价于 > MIN(price) SELECT goods_id, goods_name, price FROM goods WHERE price > ANY ( SELECT price FROM goods WHERE cate_id = 2 ); -- 实验 2.18：比类别 2 中所有商品价格都高，等价于 > MAX(price) SELECT goods_id, goods_name, price FROM goods WHERE price > ALL ( SELECT price FROM goods WHERE cate_id = 2 );

逻辑说明：> ANY只要比子查询结果里最小的那个大就成立，> ALL必须比最大的那个还大。等价关系记牢：> ANY等于> MIN，> ALL等于> MAX，< ANY等于< MAX，< ALL等于< MIN。参数上要注意子查询返回空集时，ANY 返回 false，ALL 返回 true——这是数学上的空集语义，业务上通常不希望出现，所以用之前先确认子查询不会查空。

实验 2.19 是集合运算，UNION 和 UNION ALL 的区别只有一条：去不去重。

-- 实验 2.19-1：UNION 合并两个结果集并去重，隐含排序开销 SELECT goods_id, goods_name, price FROM goods WHERE price < 5 UNION SELECT goods_id, goods_name, price FROM goods WHERE cate_id IN (1, 2); -- 实验 2.19-2：UNION ALL 保留重复行，不排序，性能更好 SELECT goods_id, goods_name, price FROM goods WHERE price < 5 UNION ALL SELECT goods_id, goods_name, price FROM goods WHERE cate_id IN (1, 2);

逻辑说明：脚垫（goods_id=5，价格 3.50，类别 2）同时满足两个条件，在 UNION 结果里只出现一次，在 UNION ALL 里出现两次。UNION 的去重是靠临时表加排序实现的，数据量大时开销明显；如果业务上确定两个结果集不会重叠，或者重叠了也无所谓，一律用 UNION ALL。参数上参与 UNION 的各个 SELECT 必须列数相同、对应列类型兼容，列名以第一个 SELECT 为准。