MySQL 常见场景 SQL 语句与说明

这篇文章不只列 SQL 语法，而是按真实业务场景整理：

1. 这个场景要解决什么问题
2. 常见 SQL 应该怎么写
3. 这些写法背后的含义是什么
4. 实战里有哪些容易踩坑的点

如果你在做项目，这篇文章可以当作 常用写法模板库； 如果你在系统梳理 SQL 能力，也可以把它当作一份按场景组织的速查表。

1. 先约定一套示例表

为了避免例子太碎，下面统一使用电商系统的几个简化表。

create table user (
  id bigint primary key auto_increment,
  username varchar(64) not null,
  age int,
  city varchar(64),
  status tinyint not null default 1,
  created_at datetime not null default current_timestamp
);

create table product (
  id bigint primary key auto_increment,
  name varchar(128) not null,
  category_id bigint not null,
  price decimal(10, 2) not null,
  stock int not null default 0,
  created_at datetime not null default current_timestamp
);

create table orders (
  id bigint primary key auto_increment,
  user_id bigint not null,
  total_amount decimal(10, 2) not null,
  status varchar(32) not null,
  created_at datetime not null default current_timestamp
);

create table order_item (
  id bigint primary key auto_increment,
  order_id bigint not null,
  product_id bigint not null,
  quantity int not null,
  price decimal(10, 2) not null
);

这里没有把所有外键、索引、约束全部展开，重点先放在 SQL 场景本身。

2. 新增数据

2.1 插入一条数据

insert into user (username, age, city, status)
values ('tom', 25, 'Shanghai', 1);

说明：

1. 最常见的新增写法
2. 显式写字段名是好习惯，避免表结构调整后 SQL 出错
3. 不依赖字段顺序，可读性更好

2.2 一次插入多条数据

insert into product (name, category_id, price, stock)
values
  ('iPhone 16', 1, 6999.00, 100),
  ('MacBook Pro', 1, 14999.00, 50),
  ('AirPods Pro', 1, 1899.00, 200);

说明：

1. 批量插入通常比一条一条插入效率更高
2. 能减少网络往返和事务提交次数
3. 大批量写入时不要一次塞太多，避免单条 SQL 过大

2.3 插入时处理唯一冲突

假设 username 上有唯一索引：

insert into user (username, age, city, status)
values ('tom', 26, 'Beijing', 1)
on duplicate key update
  age = values(age),
  city = values(city),
  status = values(status);

说明：

1. 适合“有则更新，无则插入”的场景
2. 常见于同步任务、用户画像、统计结果落库
3. 前提是冲突列上有主键或唯一索引

3. 更新数据

3.1 按主键更新

update user
set city = 'Hangzhou', age = 26
where id = 1;

说明：

1. 最推荐按主键更新，范围最明确
2. 命中主键索引，定位快
3. 也更不容易误更新大量数据

3.2 按条件批量更新

update product
set stock = stock - 1
where id = 1001
  and stock > 0;

说明：

1. 这是扣减库存的经典写法
2. stock > 0 是业务保护条件，避免库存扣成负数
3. 高并发下通常还要结合事务、锁或乐观锁字段一起使用

3.3 使用 case when 批量更新不同值

update user
set status = case
  when id = 1 then 1
  when id = 2 then 0
  when id = 3 then 1
end
where id in (1, 2, 3);

说明：

1. 一条 SQL 完成多行不同值更新
2. 适合后台批量改状态
3. 数据量再大时，通常会拆批处理

4. 删除数据

4.1 按条件删除

delete from user
where id = 10;

说明：

1. 一定要带 where
2. 真正执行前先确认影响范围
3. 高频业务表很多时候更倾向逻辑删除，而不是物理删除

4.2 清空整张表

truncate table order_item;

说明：

1. truncate 通常比 delete from table 更快
2. 它更像“重建空表”
3. 适合测试数据清理，不适合随意在业务表上执行

5. 单表查询

5.1 查询指定列

select id, username, city
from user;

说明：

1. 尽量少用 select *
2. 只查需要的列，减少网络传输和回表成本
3. 有时还能更容易走覆盖索引

5.2 条件查询

select id, username, age
from user
where status = 1
  and age >= 18;

说明：

1. where 负责过滤数据
2. 组合条件是最常见的业务查询形式
3. 条件列是否建索引，直接影响性能

5.3 模糊查询

select id, username
from user
where username like 'tom%';

说明：

1. 前缀匹配比 %tom% 更容易利用索引
2. %关键字% 这类全文模糊匹配，大表上通常性能较差
3. 搜索需求复杂时，往往会交给 ES 一类搜索系统

5.4 范围查询

select id, name, price
from product
where price between 1000 and 5000;

说明：

1. between ... and ... 包含边界值
2. 价格、时间区间、分数区间很常见
3. 联合索引里一旦遇到范围条件，后续列利用效果会受影响

5.5 判断空值

select id, username
from user
where city is null;

说明：

1. 判断空值要用 is null，不要写 = null
2. null 参与比较时逻辑和普通值不同

6. 排序与分页

6.1 排序查询

select id, username, created_at
from user
order by created_at desc;

说明：

1. order by 是列表页非常常见的需求
2. 如果排序列没有合适索引，容易触发额外排序开销

6.2 基础分页

select id, username, created_at
from user
order by id desc
limit 20 offset 40;

说明：

1. 表示跳过前 40 条，取后 20 条
2. 适合后台管理系统、小数据量列表
3. 深分页会越来越慢，因为前面的记录仍然要被扫描和丢弃

6.3 游标分页

select id, username, created_at
from user
where id < 10000
order by id desc
limit 20;

说明：

1. 这是更适合大数据量列表的分页方式
2. 核心思路是用上一次最后一条记录的主键继续翻页
3. 比 limit 100000, 20 更稳定

7. 聚合统计

7.1 🌟 统计总数

select count(*) as total
from orders
where status = 'PAID';

说明：

1. count(*) 是最常见的统计语句
2. 统计分页总数、订单总数、用户总数时经常用到

7.2 分组统计

select status, count(*) as cnt
from orders
group by status;

说明：

1. 按状态分组统计订单数
2. 常用于报表、数据看板、后台统计

7.3 🌟 分组后过滤

select user_id, sum(total_amount) as total_cost
from orders
group by user_id
having sum(total_amount) > 10000;

说明：

1. where 是分组前过滤
2. having 是分组后过滤
3. 这也是写聚合查询时最容易混淆的区别

7.4 求和、平均值、最大最小值

select
  sum(total_amount) as total_amount,
  avg(total_amount) as avg_amount,
  max(total_amount) as max_amount,
  min(total_amount) as min_amount
from orders
where status = 'PAID';

说明：

1. 这类聚合函数是报表 SQL 基础
2. 一定要关注统计口径，比如是否只统计已支付订单

8. 多表查询

8.1 内连接

查询订单及其所属用户：

select
  o.id as order_id,
  o.total_amount,
  o.status,
  u.username
from orders o
inner join user u on o.user_id = u.id;

说明：

1. inner join 只返回能匹配上的数据
2. 最常用于主表和关联表同时展示

8.2 左连接

查询所有用户及其订单，没有订单的用户也保留：

select
  u.id,
  u.username,
  o.id as order_id,
  o.total_amount
from user u
left join orders o on u.id = o.user_id;

说明：

1. left join 会保留左表全部数据
2. 适合“主数据必须保留，关联数据可为空”的场景

8.3 三表联查

查询订单、下单用户和商品明细：

select
  o.id as order_id,
  u.username,
  p.name as product_name,
  oi.quantity,
  oi.price
from orders o
join user u on o.user_id = u.id
join order_item oi on o.id = oi.order_id
join product p on oi.product_id = p.id
where o.id = 10001;

说明：

1. 多表联查是业务查询高频场景
2. 表一多就容易变慢，要关注索引和返回字段
3. 联表不是越少越好，而是要和业务模型匹配

9. 子查询与存在性判断

9.1 🌟 in 子查询

select id, username
from user
where id in (
  select user_id
  from orders
  where status = 'PAID'
);

说明：

1. 适合“查出满足某条件的一批关联主键，再反查主表”
2. 数据量大时要结合执行计划判断是否合适

9.2 🌟 exists 子查询

select u.id, u.username
from user u
where exists (
  select 1
  from orders o
  where o.user_id = u.id
    and o.status = 'PAID'
);

说明：

1. 更强调“是否存在”
2. 有些场景下 exists 比 in 更贴近语义
3. 最终还是要看执行计划和数据分布

10. 去重、合并与条件表达式

10.1 去重查询

select distinct city
from user;

说明：

1. distinct 用于结果去重
2. 常用于筛选维度值，如城市、分类、标签

10.2 使用 case when 做条件分类

select
  id,
  username,
  case
    when age < 18 then 'minor'
    when age between 18 and 35 then 'young'
    else 'adult'
  end as age_group
from user;

说明：

1. 适合在查询结果里做简单分桶
2. 报表和数据分析里经常出现

11. 事务与锁

11.1 开启事务并提交

start transaction;

update product
set stock = stock - 1
where id = 1001
  and stock > 0;

insert into orders (user_id, total_amount, status)
values (1, 6999.00, 'PAID');

commit;

说明：

1. 多条操作要么一起成功，要么一起失败时就需要事务
2. 下单、扣库存、扣余额这类场景最典型

11.2 回滚事务

start transaction;

update product
set stock = stock - 1
where id = 1001
  and stock > 0;

rollback;

说明：

1. 发生异常时可以撤销尚未提交的修改
2. 事务不是越大越好，范围越大锁持有时间越久

11.3 🌟 当前读与行锁

start transaction;

select *
from product
where id = 1001
for update;

说明：

1. for update 会读取当前版本并尝试加锁
2. 适合先查后改、需要防止并发修改的场景
3. 是否锁住一行还是更大范围，和索引命中情况密切相关

12. 表结构与索引管理

12.1 新增索引

create index idx_user_status_created_at
on user (status, created_at);

说明：

1. 给高频过滤和排序字段建索引
2. 联合索引字段顺序要贴合查询条件
3. 索引不是越多越好，写入成本也会增加

12.2 🌟 查看执行计划

explain
select id, username, created_at
from user
where status = 1
order by created_at desc
limit 20;

说明：

1. explain 是 SQL 优化的入口
2. 重点关注是否走索引、扫描行数是否合理
3. 优化 SQL 不能靠猜，看执行计划

12.3 修改表结构

alter table user
add column email varchar(128);

说明：

1. 用于新增字段、修改字段、加索引、删索引
2. 大表执行 DDL 要特别谨慎，可能带来锁表或较长变更时间

13. 批量导入与去重写入

13.1 批量写入订单明细

insert into order_item (order_id, product_id, quantity, price)
values
  (10001, 1, 2, 6999.00),
  (10001, 2, 1, 14999.00),
  (10001, 3, 3, 1899.00);

说明：

1. 适合一次写入同一订单下的多条明细
2. 批量语句通常比循环单条插入更高效

13.2 插入时忽略冲突

insert ignore into user (id, username, age, city, status)
values (1, 'tom', 25, 'Shanghai', 1);

说明：

1. 冲突时跳过，不报错
2. 适合容忍重复数据的导入任务
3. 但它可能掩盖脏数据问题，业务关键链路要慎用

14. 实战里常见的几个坑

14.1 不要随手写 select *

问题在于：

1. 返回列太多
2. 影响网络传输
3. 不利于覆盖索引
4. 表结构变更后结果集也可能超出预期

14.2 更新和删除一定要带条件

尤其在线上环境，要先确认：

1. 影响行数
2. 是否命中索引
3. 是否在事务里
4. 是否需要先备份或先查后改

14.3 🌟 分页不要忽视深分页问题

当 offset 很大时，数据库仍要扫描并丢弃前面的很多数据。大列表优先考虑：

1. 游标分页
2. 延迟关联
3. 搜索条件收敛

14.4 联表和子查询都不是原罪

真正需要关注的是：

1. 数据量
2. 索引
3. 执行计划
4. 返回字段数

不是简单地说“join 一定慢”或者“子查询一定不能用”。

15. 一段压缩版概览

如果想把常见 MySQL 语句场景压缩成一段话，可以这样概括：

MySQL 常见语句场景可以分成几类。第一类是增删改查，比如 insert、update、delete、select，这是最基础的 CRUD；第二类是排序分页和条件过滤，比如 where、order by、limit；第三类是聚合统计，比如 count、sum、group by、having；第四类是多表查询和子查询，比如 join、in、exists；第五类是事务和锁，比如 start transaction、commit、rollback、select for update；第六类是表结构和性能优化相关语句，比如 create index、alter table、explain。实际写 SQL 时，除了保证结果正确，更要关注索引命中、影响行数、事务范围和执行计划。

16. 总结

把 MySQL 语句按场景去理解，比按零散语法去背更有用。

你可以把它们压缩成下面几组：

1. 数据写入：insert、批量插入、冲突更新
2. 数据修改：update、批量更新、条件扣减
3. 数据删除：delete、truncate
4. 数据查询：where、order by、limit
5. 统计分析：count、sum、group by、having
6. 关联查询：join、in、exists
7. 并发控制：事务、for update
8. 性能优化：索引、explain

如果你能把“语句怎么写、适合什么场景、性能上要注意什么”这三层一起讲清楚，这一块就不只是会写 SQL，而是真的理解了 SQL。