mysql_lab

基本查询骨架

SELECT 列名
FROM 表名
WHERE 行筛选条件
GROUP BY 分组列
HAVING 分组筛选条件
ORDER BY 排序列 DESC
LIMIT 条数 OFFSET 跳过条数;

常用基础查询

SELECT * FROM users;                         -- 所有列
SELECT id, name FROM users;                  -- 指定列
SELECT DISTINCT name FROM users;             -- 去重
SELECT * FROM users WHERE id = 3;            -- 筛选
SELECT * FROM users WHERE name LIKE '张%';   -- 以“张”开头
SELECT * FROM users WHERE id IN (1, 2, 3);   -- 多个值
SELECT * FROM users WHERE name IS NULL;      -- 判断空值
SELECT * FROM users ORDER BY id DESC;        -- 降序

判断 NULL 要用 IS NULL，不能写 = NULL。

分页查询

-- 第 1 页，每页 10 条
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

-- 第 3 页，每页 10 条：跳过 (3 - 1) × 10 = 20 条
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

MySQL 也支持 LIMIT 20, 10，含义是“跳过 20 条，取 10 条”。分页要配合 ORDER BY，否则每页的顺序不可靠。

左连接与右连接

-- 保留所有用户；没有订单的用户也会出现，订单列为 NULL
SELECT u.name, o.amount
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

-- 保留所有订单；找不到对应用户的订单也会出现，用户列为 NULL
SELECT u.name, o.amount
FROM users AS u
RIGHT JOIN orders AS o ON o.user_id = u.id;

LEFT JOIN 保留左表全部行，RIGHT JOIN 保留右表全部行；匹配不上的另一侧填 NULL。调换表的书写顺序，右连接通常可以改写成左连接。

进阶 01 · INNER JOIN 与多表查询

INNER JOIN 只返回两边能够匹配的行。连接多张表时，每个 JOIN 都写清楚关联条件，并始终给表设置简短别名，避免同名字段含义不清。

-- 查询有订单的用户及其订单金额
SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id
ORDER BY o.id DESC;

-- 统计每位下过单的用户的订单数和总金额
SELECT u.id, u.name,
       COUNT(o.id) AS order_count,
       SUM(o.amount) AS total_amount
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name;

JOIN 默认就是 INNER JOIN。一对多连接会让用户行重复，这是正常结果；需要“一位用户一行”时，再使用 GROUP BY 聚合。

快速自测：只查询订单金额大于 100 的用户和订单，应把 o.amount > 100 放在哪里？答案：放在 WHERE 中。

进阶 02 · 子查询与 EXISTS

子查询把一个查询的结果交给另一个查询使用。返回单个值时可以直接比较；只关心“是否存在”时优先使用 EXISTS，数据库找到第一条匹配记录后即可停止继续查找。

-- 查询订单金额高于平均订单金额的订单
SELECT id, user_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- 查询至少下过一单的用户
SELECT u.id, u.name
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.user_id = u.id
);

-- 查询从未下过订单的用户
SELECT u.id, u.name
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.user_id = u.id
);

NOT IN 的子查询结果一旦包含 NULL，判断可能全部变成未知；表达“没有关联记录”时，NOT EXISTS 通常更直观稳妥。

快速自测：查询至少有一笔金额大于 500 的订单的用户，只需在 EXISTS 子查询中再添加 AND o.amount > 500。

进阶 03 · CASE 条件表达式

CASE 可以在查询中完成分类、状态翻译和条件统计。它不会修改原数据，只负责根据条件计算当前结果列。

-- 按金额给订单分级
SELECT id, amount,
       CASE
         WHEN amount >= 1000 THEN '大额订单'
         WHEN amount >= 100  THEN '普通订单'
         ELSE '小额订单'
       END AS amount_level
FROM orders;

-- 一次查询统计不同金额区间
SELECT COUNT(*) AS total_orders,
       SUM(CASE WHEN amount >= 1000 THEN 1 ELSE 0 END) AS large_orders,
       SUM(CASE WHEN amount < 1000 THEN amount ELSE 0 END) AS other_amount
FROM orders;

多个 WHEN 按书写顺序判断，匹配第一项后就停止。范围条件应从更具体或更高的阈值开始，否则前面的宽泛条件会提前截获数据。

快速自测：把没有订单的用户显示为“未消费”，可以对左连接后的 o.id 写 CASE WHEN o.id IS NULL THEN '未消费' ELSE '已消费' END。

进阶 04 · 日期、字符串与空值处理

假设 orders 再增加 created_at 和 remark 字段。业务查询经常需要处理时间范围、拼接展示字段和提供默认值；筛选索引列时，应尽量让原字段保持不变。

-- 查询 2026 年 9 月的订单：使用左闭右开区间
SELECT id, user_id, amount
FROM orders
WHERE created_at >= '2026-09-01'
  AND created_at <  '2026-10-01';

-- 格式化日期、拼接说明，并给 NULL 设置默认值
SELECT id,
       DATE_FORMAT(created_at, '%Y-%m-%d') AS order_date,
       CONCAT('订单金额：', amount) AS summary,
       COALESCE(remark, '无备注') AS remark
FROM orders;

筛选时间时，created_at >= 起点 AND created_at < 终点 能覆盖带时分秒的数据。避免写 DATE(created_at) = ...，对索引列套函数可能让普通索引无法直接用于范围定位。

快速自测：查询最近 7 天订单，可以写 created_at >= CURRENT_DATE - INTERVAL 7 DAY。

进阶 05 · 索引与 EXPLAIN

索引帮助 MySQL 少扫描数据。设计联合索引时，从实际查询出发，把常用等值条件放在前面，再考虑范围、排序和需要覆盖的列。

-- 支持“某用户按订单 id 倒序查看订单”
CREATE INDEX idx_orders_user_id_id
ON orders (user_id, id);

EXPLAIN
SELECT id, amount
FROM orders
WHERE user_id = 10
ORDER BY id DESC
LIMIT 20;

阅读执行计划时先看 type 是否为 ALL，再看 key 是否命中预期索引、rows 预计扫描多少行，以及 Extra 中是否出现 Using filesort 或 Using temporary。索引越多，写入维护成本也越高。

-- MySQL 8：实际执行查询并展示每个步骤的真实耗时和行数
EXPLAIN ANALYZE
SELECT id, amount FROM orders WHERE user_id = 10;

快速自测：联合索引是 (user_id, id) 时，只按 id 查询能否稳定使用它？不一定，因为跳过了最左列 user_id。

进阶 06 · 事务、回滚与行锁

事务把多条 SQL 视为一个整体：全部成功就 COMMIT，任何一步失败就 ROLLBACK。需要“先读取、再根据结果修改”时，可以用 FOR UPDATE 锁住将要修改的记录。

START TRANSACTION;

-- 锁定订单 1001，其他事务暂时不能修改这条记录
SELECT id, amount
FROM orders
WHERE id = 1001
FOR UPDATE;

UPDATE orders
SET amount = amount + 20
WHERE id = 1001;

COMMIT;
-- 发生异常时执行 ROLLBACK;

FOR UPDATE 必须在事务中使用才有实际意义。事务应尽量短，不要在持锁期间等待网络请求或人工操作；多个业务流程按一致顺序锁定记录，可以减少死锁。

快速自测：第二条 UPDATE 失败后，能否继续 COMMIT 第一条修改？通常不应这样做，应捕获异常并执行 ROLLBACK，让业务保持完整。

进阶 07 · CTE 与窗口函数

MySQL 8 可以用 WITH 把复杂查询拆成有名字的步骤。窗口函数在保留每一行的同时完成排名、累计或前后行比较，不会像 GROUP BY 那样把多行合并成一行。

-- 先汇总每位用户的消费，再按总金额排名
WITH user_totals AS (
  SELECT u.id, u.name,
         COUNT(o.id) AS order_count,
         COALESCE(SUM(o.amount), 0) AS total_amount
  FROM users AS u
  LEFT JOIN orders AS o ON o.user_id = u.id
  GROUP BY u.id, u.name
)
SELECT id, name, order_count, total_amount,
       DENSE_RANK() OVER (ORDER BY total_amount DESC) AS amount_rank
FROM user_totals
ORDER BY amount_rank, id;

ROW_NUMBER() 给每行唯一序号；RANK() 遇到并列会跳号；DENSE_RANK() 遇到并列不会跳号。窗口中的 PARTITION BY 可以让排名在每个分组内重新开始。

快速自测：要计算每位用户自己的订单金额累计值，可写 SUM(amount) OVER (PARTITION BY user_id ORDER BY id)。

统计与分组

SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2
ORDER BY total DESC;

WHERE 筛选分组之前的行，HAVING 筛选分组之后的结果。常用统计函数有 COUNT()、SUM()、AVG()、MAX()、MIN()。

增加、修改与删除

INSERT INTO users (name) VALUES ('小王');

UPDATE users SET name = '王同学' WHERE id = 3;

DELETE FROM users WHERE id = 3;

执行 UPDATE 和 DELETE 前要仔细检查 WHERE；省略它会影响整张表。生产环境建议先用相同条件执行 SELECT，确认影响范围。