测试工作干到一定阶段，你会发现SQL已经不只是“会用”的程度，而是你每天都要靠它去定位数据问题、校验业务逻辑、准备一堆测试数据，甚至帮开发同事验证某个修复到底有没有生效。上篇讲了SELECT的基础查询、条件过滤、排序和LIMIT分页，这篇直接上测试工作中真正高频的硬核操作：聚合统计、多表关联、CASE WHEN逻辑判断、子查询，以及最容易出事故的UPDATE和DELETE。每一段我都会贴出真实可跑通的SQL语句和执行结果，用一套电商测试数据贯穿全文，方便你直接复制到本地去验证。
1. 环境准备：一套可控的示例数据是后面一切操作的前提
1.1 为什么测试人员要专门准备一套自己的数据
很多测试新手喜欢直接连测试环境数据库，在生产或公共测试库上跑各种查询，查归查，一旦碰到UPDATE和DELETE就很容易误伤别人的数据。我自己的习惯是：本地维护一套专属的小型MySQL库，表结构尽量模拟业务，但数据量控制在十几条以内。这样无论是练习、验证SQL逻辑，还是排查一个接口取数是否符合预期，都能快速定位，不依赖别人。
这不是小题大做。你想想，你测一个订单列表接口，如果库里连一条订单都没有，你怎么知道返回字段是不是正确？如果库里数据乱成一片，你怎么断言“状态为已完成的订单只有3条”这个结果是准确的？测试的本质是“可控条件下的验证”，数据越可控，断言就越有底气。
1.2 三张业务表的建表语句与初始化数据
后面所有案例都围绕一个简单电商场景，三张表：用户表、商品表、订单表。我先贴建表语句。
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT, city VARCHAR(50), reg_date DATE ); CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2), category VARCHAR(50), stock INT ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, quantity INT, amount DECIMAL(10,2), status VARCHAR(20), order_date DATE );初始化数据：
INSERT INTO users (name, age, city, reg_date) VALUES ('张三', 25, '北京', '2024-01-10'), ('李四', 30, '上海', '2024-02-14'), ('王五', 28, '广州', '2024-03-20'), ('赵六', 35, '北京', '2024-04-05'), ('孙七', 22, '深圳', '2024-05-11'), ('周八', 27, '杭州', '2024-06-20'); INSERT INTO products (product_name, price, category, stock) VALUES ('笔记本电脑', 4999.00, '数码', 20), ('机械键盘', 349.00, '外设', 50), ('无线鼠标', 89.00, '外设', 100), ('显示器', 1299.00, '数码', 30), ('USB扩展坞', 159.00, '配件', 80); INSERT INTO orders (user_id, product_id, quantity, amount, status, order_date) VALUES (1, 1, 1, 4999.00, '已完成', '2024-06-01'), (1, 2, 2, 698.00, '待付款', '2024-06-03'), (2, 3, 1, 89.00, '已完成', '2024-06-05'), (3, 4, 1, 1299.00, '已取消', '2024-06-08'), (4, 2, 1, 349.00, '已完成', '2024-06-10'), (5, 5, 3, 477.00, '待发货', '2024-06-12'), (2, 1, 1, 4999.00, '已完成', '2024-06-15'), (3, 3, 2, 178.00, '待付款', '2024-06-18');这里我故意没有给orders表建外键，实际开发中很多业务表为了性能也会刻意不用外键。所以测试人员在写JOIN的时候就要格外小心，一旦两张表的关联字段出现孤儿数据，查出来的结果集就会和预期差很多，这一点后面我会专门踩坑说明。
2. 聚合统计与分组分析：结果校验的核心武器
2.1 COUNT、SUM、AVG、MAX、MIN不只是面试题
测试里最常见的场景是什么？开发说“我已经把数据清掉了”，你怎么验证？开发说“这个接口返回的订单总数是10”，你凭什么信？靠的就是聚合函数。
SELECT COUNT(*) AS total_users, COUNT(DISTINCT city) AS city_count FROM users;运行结果：
+-------------+------------+ | total_users | city_count | +-------------+------------+ | 6 | 6 | +-------------+------------+这个统计看起来简单，实际使用中有个很重要的细节：COUNT(*)是统计行数，COUNT(字段)是统计该字段非NULL的个数。如果你用COUNT(city)去统计用户数，恰好有某个用户city字段为NULL，那结果就会少一行。测试人员在核对总数时，如果发现数字对不上，第一个排查方向就是有没有NULL值在作怪。
再看金额类校验。测试一个订单列表页，页面上显示“已完成订单总额为10447.00”，你要验证这个数字对不对，直接跑：
SELECT COUNT(*) AS completed_count, SUM(amount) AS total_amount, ROUND(AVG(amount), 2) AS avg_amount FROM orders WHERE status = '已完成';运行结果：
+-----------------+--------------+------------+ | completed_count | total_amount | avg_amount | +-----------------+--------------+------------+ | 3 | 10447.00 | 3482.33 | +-----------------+--------------+------------+SUM验证的是总额，AVG验证的是平均值，MAX和MIN通常用来做边界值验证，比如查商品最高价、最低价，或者查某个用户最早注册日期。跑一下：
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;运行结果：
+-----------+-----------+ | max_price | min_price | +-----------+-----------+ | 4999.00 | 89.00 | +-----------+-----------+很多测试同学拿到接口返回的统计数据，反应是“开发说是这样的”，然后就不管了。其实把SQL一跑，自己心里就有数了。这个习惯非常重要，尤其是测报表功能、数据看板、支付统计这类模块。
2.2 GROUP BY和HAVING是数据分布测试的利器
回归测试里，我们经常要确认某个字段的不同取值分布是否符合预期。比如“订单状态有哪几种，各有多少单”，这就是分组统计的典型场景。
SELECT status, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY status;运行结果：
+----------+-------------+--------------+ | status | order_count | total_amount | +----------+-------------+--------------+ | 已完成 | 3 | 10447.00 | | 待付款 | 2 | 876.00 | | 已取消 | 1 | 1299.00 | | 待发货 | 1 | 477.00 | +----------+-------------+--------------+你把这个结果跟页面上的状态筛选列表对照一下，基本就能看出功能正常不正常。如果页面上显示“待付款有3单”，但SQL查出来只有2单，说明要么脏数据，要么页面取数逻辑有bug，这就是一个可提交的缺陷。
HAVING是分组之后过滤数据用的，和WHERE有本质区别：WHERE过滤的是原始行，HAVING过滤的是分组结果。举个例子，我要找出下过至少2单的用户：
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent FROM orders GROUP BY user_id HAVING COUNT(*) >= 2;运行结果：
+---------+-------------+-------------+ | user_id | order_count | total_spent | +---------+-------------+-------------+ | 1 | 2 | 5697.00 | | 2 | 2 | 5088.00 | +---------+-------------+-------------+这个SQL在做用户分层、复购率验证的时候非常常用。注意，HAVING后面不能用SELECT里的别名做过滤吗？其实MySQL允许在HAVING中使用别名，但为了兼容性考虑，我建议还是直接写聚合表达式或原始字段名，避免在复杂查询里出现不必要的坑。
2.3 聚合查询中那些一言难尽的坑
这里必须专门说一下测试人员最容易被坑的三个点。
第一个坑是COUNT(*)和COUNT(1)以及COUNT(字段)的结果差异。COUNT(*)和COUNT(1)几乎等价，都是统计行数；但COUNT(status)会跳过NULL值所在的行。如果你对orders表的status做统计，恰好存在status为NULL的订单，那你数出来的订单数就会偏少。测试时碰到总数不对，一定要想到这个可能。
第二个坑是SUM函数遇到全NULL或空表会返回NULL而不是0。很多初学者看到SUM结果是NULL，以为算错了，其实在MySQL中这是正常行为。所以在写接口返回值校验时，需要配合IFNULL或COALESCE处理。
SELECT IFNULL(SUM(amount), 0) AS total_amount FROM orders WHERE user_id = 999;运行结果：
+--------------+ | total_amount | +--------------+ | 0.00 | +--------------+第三个坑是GROUP BY的分组字段如果含有NULL值，所有NULL值会被分到同一组。测试场景中，如果你发现分组统计结果里出现一个“没有名字”的分组，不要慌，去查一下是不是字段里有NULL脏数据。
3. 多表连接查询：跨模块验证离不开的JOIN
3.1 INNER JOIN：只留两边都匹配得上的数据
功能测试做到后面，你大概率会遇到一个情况：接口返回了用户信息和订单信息，你要确认这个用户到底有没有订单、订单时间对不对、订的是什么商品。单表查肯定不行，这时候就要JOIN。
最简单的INNER JOIN，取两个表的交集：
SELECT u.id AS user_id, u.name, o.id AS order_id, o.status FROM users u INNER JOIN orders o ON u.id = o.user_id ORDER BY u.id;运行结果：
+---------+--------+----------+----------+ | user_id | name | order_id | status | +---------+--------+----------+----------+ | 1 | 张三 | 1 | 已完成 | | 1 | 张三 | 2 | 待付款 | | 2 | 李四 | 3 | 已完成 | | 2 | 李四 | 7 | 已完成 | | 3 | 王五 | 4 | 已取消 | | 3 | 王五 | 8 | 待付款 | | 4 | 赵六 | 5 | 已完成 | | 5 | 孙七 | 6 | 待发货 | +---------+--------+----------+----------+这个结果就是“有订单的用户以及他们的订单”。注意，周八（user_id=6）没有出现在结果里，因为INNER JOIN只保留两边都匹配的行。
测试中JOIN最常见的翻车场景是什么？是忘了写关联条件。你写一句FROM users u INNER JOIN orders o，不带ON，MySQL不会报错，它会把两张表做笛卡尔积，6个用户乘8条订单，返回48行。怎么看怎么不对。所以执行完JOIN查询，第一件事先看一眼返回行数是不是合理范围，再核对内容。
3.2 LEFT JOIN：专门用来查“缺失数据”
测试里查“哪些用户没有下过单”“哪些订单没有对应用户”，这属于异常数据校验。LEFT JOIN + IS NULL是这类问题的标准解法。
SELECT u.id, u.name FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;运行结果：
+----+--------+ | id | name | +----+--------+ | 6 | 周八 | +----+--------+这里我用的是WHERE o.id IS NULL，意思是左表（users）中那些在右表（orders）里找不到任何匹配的记录。NULL是“没有匹配”的标志，判断时必须用IS NULL，不能写成o.id = NULL，后者永远为假，查不到任何结果，这也是一个高频错误。
LEFT JOIN测试中还有个细节：如果右表有重复数据，左表的行会被翻倍。比如orders表里同一个人下了3单，LEFT JOIN出来这个人就会出现3行，如果你只是想列出所有用户，这个结果就“看起来不对”。要避免这种情况，可以使用EXISTS或子查询，后面会讲。
3.3 关联条件里的坑：ON还是WHERE，结果差很多
这个问题很多人学SQL的时候没想明白，测试的时候却容易踩雷。区别在于：ON是在JOIN的时候用于决定两个表怎么匹配的，WHERE是在JOIN完成之后对结果行进行过滤。
看一个对比。我要查“已完成订单的用户名称和订单金额”，下面两种写法结果是一样的：
SELECT u.name, o.amount FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = '已完成'; SELECT u.name, o.amount FROM orders o INNER JOIN users u ON o.user_id = u.id AND o.status = '已完成';但换成LEFT JOIN，结果就不一样了。如果把过滤条件放在ON子句里，那些“没匹配上的左表行”还是会保留；如果放在WHERE里，这些行就会被干掉。所以当你发现LEFT JOIN查出来的结果数量比自己预想的少，先检查是不是过滤条件被写到WHERE里，把本应保留的NULL行过滤掉了。
4. CASE WHEN与DISTINCT：测试断言里的逻辑判断和数据质量
4.1 用CASE WHEN给数据“打标签”
接口测试中经常需要验证前端展示的文案规则是否正确。比如订单金额低于500元显示“小额订单”，500到2000显示“中额订单”，高于2000显示“大额订单”。这种规则用CASE WHEN在数据库里模拟一遍，马上就知道规则有没有写错。
SELECT id AS order_id, amount, CASE WHEN amount < 500 THEN '小额订单' WHEN amount BETWEEN 500 AND 2000 THEN '中额订单' ELSE '大额订单' END AS order_level FROM orders;运行结果：
+----------+---------+-------------+ | order_id | amount | order_level | +----------+---------+-------------+ | 1 | 4999.00 | 大额订单 | | 2 | 698.00 | 中额订单 | | 3 | 89.00 | 小额订单 | | 4 | 1299.00 | 中额订单 | | 5 | 349.00 | 小额订单 | | 6 | 477.00 | 小额订单 | | 7 | 4999.00 | 大额订单 | | 8 | 178.00 | 小额订单 | +----------+---------+-------------+这个SQL表面上是“按金额分档”，实际开发中大量用在这种场景：把数据库里的状态码翻译成业务文案、把数值字段打上业务标签。测试人员在验证规则类需求时，直接跑一条CASE WHEN，把结果跟开发代码里的if-else逻辑对比一下，比手工造数据挨个看页面快得多。
还有一种更进阶的用法，是CASE WHEN配合聚合函数做条件统计。比如我想统计“已付款状态（已完成+待发货）和未付款状态（待付款）各自的订单总额”，一条SQL搞定：
SELECT SUM(CASE WHEN status IN ('已完成', '待发货') THEN amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN status = '待付款' THEN amount ELSE 0 END) AS unpaid_amount FROM orders;运行结果：
+-------------+---------------+ | paid_amount | unpaid_amount | +-------------+---------------+ | 10924.00 | 876.00 | +-------------+---------------+这种写法用来验证统计报表非常有效，不用写代码，不用写Python脚本，一个SQL就能把业务规则翻译出来。
4.2 DISTINCT去重与数据质量校验
测试中经常要检查“这张表到底有多少种状态”“有多少个不同的城市”，直接使用DISTINCT：
SELECT DISTINCT status FROM orders;运行结果：
+----------+ | status | +----------+ | 已完成 | | 待付款 | | 已取消 | | 待发货 | +----------+还有一个更常见的场景：检查数据是否存在重复。用户表正常应该是ID唯一，但如果你怀疑有脏数据导致重复，可以这样验证：
SELECT name, COUNT(*) AS cnt FROM users GROUP BY name, city HAVING COUNT(*) > 1;运行结果就是空集，说明当前没有重复。如果查出有记录，那就要考虑是不是存在相同人重复录入的问题，这就是一个数据质量缺陷。我习惯在做数据迁移、清库、导入导出后跑一遍这种“重复自检SQL”，非常省事。
4.3 子查询：JOIN之外的另一种思路
JOIN和子查询经常能互相替代，但测试场景里有各自的适用场景。比如我要查“下单次数大于等于2次的用户的姓名和城市”，可以先用GROUP BY查出user_id，再通过子查询去users表取用户信息：
SELECT name, city FROM users WHERE id IN ( SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) >= 2 );运行结果：
+--------+--------+ | name | city | +--------+--------+ | 张三 | 北京 | | 李四 | 上海 | +--------+--------+这段SQL相当于先做“用户筛选”，再拿筛选出的ID集合去匹配用户表。它和LEFT JOIN + GROUP BY相比，逻辑上更直观。还有一个常用场景是查“订单金额高于所有订单平均金额的订单”：
SELECT id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);运行结果：
+----+---------+ | id | amount | +----+---------+ | 1 | 4999.00 | | 4 | 1299.00 | | 7 | 4999.00 | +----+---------+子查询的性能在数据量小的时候感知不明显，但测试环境数据量如果比较大，子查询特别依赖外层和里层的索引，这一点在执行计划章节会展开。
5. 数据造数与清理：INSERT/UPDATE/DELETE的安全实践
5.1 INSERT造数：从手工一行到批量生成
测试最烦的就是造数据。如果每次都在Navicat里手工点着插入，效率低还容易出错。SQL本身提供了非常灵活的造数方式，你完全可以用一条SQL从现有表里批量生成数据。
比如我想给周八（id=6）造一单已完成订单，product_id取3，quantity取1：
INSERT INTO orders (user_id, product_id, quantity, amount, status, order_date) SELECT id, 3, 1, 89.00, '已完成', '2024-07-01' FROM users WHERE name = '周八';这条SQL的妙处在于不用硬编码user_id，直接从users表按名字解析出id。批量造数据时，把SELECT部分换成一个范围查询或JOIN，就能一次性插入几十条结构正确的模拟订单。这也是我在验收数据导入功能时常用的校验手段：先用SQL造一批预期数据，再跑业务接口，最后对比数据是否一致。
5.2 UPDATE：先查后改，事务兜底
UPDATE是测试环境里最容易出事故的操作。很多测试新人在测试环境执行UPDATE，忘记加WHERE条件，一条语句把所有订单状态全改了。改完一看，傻眼了。所以要养成一个习惯：先SELECT查一遍，确认要影响的行数，再改成UPDATE。
-- 先查 SELECT id, status FROM orders WHERE id = 8; -- 再改 UPDATE orders SET status = '已发货' WHERE id = 8;运行结果（UPDATE成功时）：
Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0这里“Rows matched: 1”的意思是匹配到1行，“Changed: 1”表示实际修改了1行。如果你执行UPDATE时发现Rows matched很大，但Changed是0，说明只是把值改成了原值，MySQL会认为没有实际变化。
另一个更稳的做法是把UPDATE放进事务里执行，改完先查一遍，确认无误再COMMIT：
START TRANSACTION; UPDATE orders SET status = '已发货' WHERE id = 8; -- 此时手动检查一下结果 -- 如果不对，执行 ROLLBACK; COMMIT;这个习惯在改复杂业务表时特别重要。测试环境没有备份的情况下，一个UPDATE下去就无法反悔了。事务是测试人员保护自己的最低成本手段。
5.3 DELETE、TRUNCATE、DROP怎么选
DELETE是删行，支持WHERE条件，可以用事务回滚。TRUNCATE是清空整张表，保留表结构，不能回滚（事务回滚也对TRUNCATE无效）。DROP是连表结构一起删掉。这三者的区别面试常考，实际测试中更常踩。
删除指定测试数据，使用DELETE：
DELETE FROM orders WHERE id = 10;如果你只想清空orders表数据，但保留表结构，方便重新插入测试数据，就用TRUNCATE：
TRUNCATE TABLE orders;要注意的是TRUNCATE会重置自增ID，也就是你清空后插入的第一条订单ID会从1开始。如果你想保留自增位置，用DELETE FROM orders不加WHERE反而更合适。这个差异在测试里实际会影响数据预期，比如你清空后重新造数，接口返回的ID和预期对不上，可能不是bug，而是TRUNCATE重置了自增。
最危险的是DROP，一条语句下去，表和所有数据都没了。测试环境可以这么操作，但如果连到错误的库，后果就是灾难。我的建议是执行DROP前，先用SHOW TABLES确认当前所在库，再确认表名拼写，最后再执行。
6. EXPLAIN执行计划：测试环境慢查询的初级定位思路
6.1 EXPLAIN到底能看出什么
测试人员在功能测试阶段一般不会关注SQL性能，但如果你的项目里有列表查询超时、接口响应很慢这类问题，这时候EXPLAIN就是分析SQL的第一步。
EXPLAIN SELECT * FROM orders WHERE user_id = 2;运行结果：
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+对于测试人员来说，最值得关注的是两个字段：type和rows。type表示访问类型，从好到差大概是：system、const、eq_ref、ref、range、index、ALL。上面这个查询type=ALL，说明全表扫描，rows=8表示扫描了8行。这个数据量不大所以无所谓，但如果表里有几十万条订单，ALL就会非常慢。
6.2 怎么判断要不要索引
看到ALL不代表一定要加索引，还要看表的数据量和查询频率。如果表只有几十行，全表扫描和走索引几乎没有差别；如果表是百万级，每次查询都扫全表，接口必然会慢。
给orders表的user_id字段加上索引后，再看EXPLAIN：
CREATE INDEX idx_user_id ON orders(user_id); EXPLAIN SELECT * FROM orders WHERE user_id = 2;运行结果：
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | orders | NULL | ref | idx_user_id | idx_user_id | 4 | const | 2 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+此时type从ALL变成了ref，rows从8降为2，说明MySQL可以通过索引快速定位到user_id=2的只有两行。测试人员不需要自己建索引去动表结构，但看到这样一个对比，就能理解开发为什么要给某些字段加索引，也能在测试数据量大的时候判断一个慢查询到底是不是因为缺少索引。
6.3 一个典型的慢查询定位过程
举个实际场景。测试环境订单表有几十万条数据，接口“查询用户历史订单”很慢，页面要转好几秒。开发说本地没问题，环境差异。这时候我可以直接在数据库里执行对应的SQL，并加上EXPLAIN：
EXPLAIN SELECT o.id, o.amount, o.status, o.order_date FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.phone = '13800138000';注意这里我用了一个不存在的字段phone来模拟业务查询，实际情况中业务方通常用一个唯一标识去查用户，然后拿user_id去关联订单表。如果users表的phone字段没有索引，EXPLAIN大概率也是ALL；即使users表查到了用户，orders表如果没有user_id索引，依然会全表扫描。我们当时定位到问题就是orders表缺少user_id索引，加索引之后，查询从几百毫秒降到了个位数毫秒。
我不打算在这里展开索引优化等DBA才需要掌握的内容，只是想告诉你：测试人员不需要成为数据库专家，但看到EXPLAIN里的rows从大变小，能看到加了索引和没加索引的差别，你就能更准确地判断性能问题是不是出在SQL层面。
说回这些SQL本身。测试工作里，SQL不是一个“会不会写”的问题，而是“写得对不对、查得全不全、改得安不安全”的问题。我见过太多测试同学在面试里能把LEFT JOIN和INNER JOIN的区别背得滚瓜烂熟，一到实际工作中，查个数据还要开三个窗口反复试。其实功夫就在平时：自己搭一套数据，把上面这些场景全部跑一遍，跑完顺手把报错也记下来。等你在测试环境真的碰上脏数据、慢查询、重复数据的时候，脑子里对这些SQL已经有肌肉记忆了，处理起来自然比临时查文档快得多。
我自己的习惯是把常用的这些SQL语句整理成一个sql文件，按场景分类，比如“造数”、“数据校验”、“数据清理”、“重复检查”、“性能定位”。每次新项目开始，先根据业务表结构调整一遍，整个测试周期都在用。节省下来的时间，远比当时整理花掉的两三个小时划算。你如果还在靠手工点界面造数、靠肉眼核对数据，不妨从这套环境开始，把SQL用起来，效率会明显不一样。
