★ FEATURED ARTICLE 简介面向国家开放大学 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) ) ENGINEInnoDB; CREATE TABLE category ( cate_id INT PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(32) NOT NULL ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB; 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) ) ENGINEInnoDB;逻辑说明六张表之间只靠业务字段关联没有建外键约束。实验阶段数据量小外键反而会在插数据时增加顺序负担真到生产环境再加约束也不迟索引先建上就够用。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 BYDESC 只作用于紧挨着的那一列实验 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 一边 VARCHARMySQL 会做隐式转换索引直接失效。4.2 外连接LEFT JOIN 的保留表和 RIGHT JOIN 的等价改写实验 2.12 要求「列出所有用户 ID 以及他们的评论如果有的话」。关键词是「所有用户」——没评论过的用户也要出现在结果里评论列补 NULL这就是外连接存在的意义。-- 实验 2.12-1users 是保留表没有评论的用户评论列补 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-2RIGHT 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 包装完好; -- 条件写在 WHERENULL 行被过滤等价于内连接 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-1IN 子查询内层先执行外层按结果集匹配 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-2NOT 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。只要子查询结果里出现一个 NULLx 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 返回 falseALL 返回 true——这是数学上的空集语义业务上通常不希望出现所以用之前先确认子查询不会查空。实验 2.19 是集合运算UNION 和 UNION ALL 的区别只有一条去不去重。-- 实验 2.19-1UNION 合并两个结果集并去重隐含排序开销 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-2UNION 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_id5价格 3.50类别 2同时满足两个条件在 UNION 结果里只出现一次在 UNION ALL 里出现两次。UNION 的去重是靠临时表加排序实现的数据量大时开销明显如果业务上确定两个结果集不会重叠或者重叠了也无所谓一律用 UNION ALL。参数上参与 UNION 的各个 SELECT 必须列数相同、对应列类型兼容列名以第一个 SELECT 为准。5.4 五类排查记录从现象倒推原因现象一查询结果为空但表里明明有数据。原因通常是 NOT IN 遇到了 NULL或者字符串比较本文还有配套的精品资源点击获取
阅读完成 · 觉得有帮助？
