MySQL 增删改查实战与常见坑位

二、插入数据

insert 是最基础的写入语句，常见的就几种：单行、多行、指定列、冲突处理，还有把查询结果直接塞进另一张表。

2.1 单行全列插入

插入的值顺序必须和表结构对上。自增主键可以省略，MySQL 会自己补。

-- 全列插入（指定 id）
insert into students values (104, 20003, '鲁智深', '22222');
-- 省略自增主键（自动生成 id）
insert into students (sn, name, qq) values (20004, '林冲', '33333');

2.2 多行指定列插入

批量插入时，通常只给必要列，没写的列走默认值或者 null。

insert into students (sn, name) values (20005, '武松'), (20006, '杨志');

2.3 冲突时更新：on duplicate key update

这个语句很实用。主键或唯一键撞上了，不报错，改成更新。适合幂等写入，但也别滥用，字段少还好，字段多的时候容易把逻辑写散。

-- 主键冲突（id=100 已存在），执行更新
insert into students (id, sn, name) values (100, 10010, '唐大师')
on duplicate key update sn = 10010, name = '唐大师';

-- 唯一键冲突（sn=20001 已存在），执行更新
insert into students (sn, name) values (20001, '曹阿瞒')
on duplicate key update name = '曹阿瞒';

2.4 替换插入：replace into

replace 和上面那种'冲突后更新'不是一回事，它会先删掉旧记录，再重新插入。看起来省事，实际更激进，尤其是有外键或触发器的时候要小心。

-- sn=20002 已存在，删除原记录后插入新数据
replace into students (sn, name) values (20002, '孙伯符');

2.5 把查询结果插入新表

这个场景在迁移和去重时比较常见。

-- 创建空表（结构与 students 一致）
create table students_copy like students;
-- 将 students 的去重数据插入新表
insert into students_copy select distinct * from students;

三、查询数据

查询是 CRUD 里最灵活的一块，也是最容易把语句写复杂的地方。基础语法先记住，后面再往上叠条件、排序、分页、聚合和分组。

select [distinct] {*|column[,column]...} from table_name [where...] [order by column[asc|desc],...] limit ...;

3.1 基础查询

3.1.1 全列查询

能查，但我一般不建议在生产里随手写 select *。列一多，传输和解析开销都会上来，后面表结构变了也容易把下游带崩。

-- 全列查询（数据量大时性能差，不建议在生产环境使用）
-- 1. 查询的列越多，意味着需要传输的数据量越大
-- 2. 可能会影响到索引的使用
select * from exam_result;

3.1.2 指定列查询

按需取列，顺序也可以和表结构不一样。

-- 查询姓名、语文、数学成绩
select name, chinese, math from exam_result;

3.1.3 查询表达式

可以查常量，也可以做简单运算。

-- 常量表达式
select id, name, 10 from exam_result;
-- 单字段运算（英语成绩 +10）
select id, name, english + 10 from exam_result;
-- 多字段运算（总分）
select id, name, chinese + math + english from exam_result;

3.1.4 结果别名

别名主要是为了让结果更好读，尤其是表达式列。

select id, name, chinese + math + english as 总分 from exam_result;

3.1.5 去重

-- 去重查询数学成绩 distinct
select distinct math from exam_result;

3.2 条件查询：where

where 负责过滤行。比较运算符和逻辑运算符组合起来，基本够应付大多数场景。

-- 1. 英语不及格（<60）
select name, english from exam_result where english < 60;
-- 2. 语文成绩在 [80, 90] 之间（between...and）
select name, chinese from exam_result where chinese between 80 and 90;
-- 3. 数学成绩是 58、59、98、99 中的一个（in）
select name, math from exam_result where math in (58, 59, 98, 99);
-- 4. 姓孙的同学（like %）
select name from exam_result where name like '孙%';
-- 5. 姓名是两个字且姓孙（like _）
select name from exam_result where name like '孙_';
-- 6. 语文成绩好于英语成绩
select name, chinese, english from exam_result where chinese > english;
-- 7. 总分低于 200 分（表达式作为条件）
select name, chinese + math + english as 总分 from exam_result where chinese + math + english < 200;
-- 不能直接用总分 < 20,因为执行这里的时候还没执行前面的部分
-- 8. 语文>80 且不姓孙（and + not）
select name, chinese from exam_result where chinese > 80 and name not like '孙%';
-- 9. qq 号不为空（is not null）
select name, qq from students where qq is not null;
-- 10. null 安全比较（<=>）
select name, qq from students where qq <=> null;

3.3 排序：order by

默认是升序，想倒过来就加 desc。多字段排序也很常用，尤其是先按主排序字段排，再用次级字段兜底。

-- 1. 按数学成绩升序
select name, math from exam_result order by math;
-- 2. 按数学降序，英语升序，语文升序
select name, math, english, chinese from exam_result order by math desc, english, chinese;
-- 3. 按总分降序（表达式排序）
select name, chinese + math + english as 总分 from exam_result order by 总分 desc;
-- 4. null 排序（null 视为最小值，升序在最前）
select name, qq from students order by qq;
select name, qq from students order by qq desc;

3.4 分页：limit

分页一定要配合排序用，不然每次返回的顺序都可能不一样，翻页结果会飘。

-- 语法 1：limit 条数（取前 n 条）
select * from exam_result order by id limit 3;
-- 语法 2：limit 起始下标，条数（从 s(下标从 0 开始) 开始取 n 条）
select * from exam_result order by id limit 3, 3;
-- 语法 3：limit 条数 offset 起始下标（推荐，更清晰）
select * from exam_result order by id limit 3 offset 6;
-- 分页示例：每页 3 条，第 1-3 页
select * from exam_result order by id limit 3 offset 0; -- 第 1 页
select * from exam_result order by id limit 3 offset 3; -- 第 2 页
select * from exam_result order by id limit 3 offset 6; -- 第 3 页

3.5 聚合查询

聚合函数是做统计的，写法不复杂，但要记得它们会忽略 null，这点常常影响结果判断。

-- 1. 统计学生总数（count(*)不受 null 影响）
select count(*) as 学生总数 from students;
-- 2. 统计 qq 号非空的学生数（null 不计入）
select count(qq) as qq 已收集人数 from students;
-- 3. 统计数学成绩总分和去重后总分
select sum(math) as 数学总分, sum(distinct math) as 去重数学总分 from exam_result;
-- 4. 统计语文成绩平均分
select avg(chinese) as 语文平均分 from exam_result;
-- 5. 英语最高分和最低分
select max(english) as 英语最高分, min(english) as 英语最低分 from exam_result;
-- 6. 统计 70 分以上的数学最低分
select min(math) as 70+ 数学最低分 from exam_result where math > 70;

3.6 分组查询：group by + having

group by 是先分组，having 是在分组之后再筛一遍。这个顺序别搞反了，where 不能直接替代 having。

-- 准备雇员表（经典测试表）
create table emp (
    empno int primary key,
    ename varchar(20),
    job varchar(20),
    deptno int,
    sal float
);
insert into emp values 
(7369, 'smith', 'clerk', 20, 800),
(7499, 'allen', 'salesman', 30, 1600),
(7521, 'ward', 'salesman', 30, 1250),
(7566, 'jones', 'manager', 20, 2975),
(7654, 'martin', 'salesman', 30, 1250),
(7698, 'blake', 'manager', 30, 2850),
(7782, 'clark', 'manager', 10, 2450),
(7788, 'scott', 'analyst', 20, 3000);

-- 1. 按部门分组，统计每个部门的平均工资和最高工资
select deptno, avg(sal) as 平均工资, max(sal) as 最高工资 from emp group by deptno;
-- 2. 按部门和岗位分组，统计平均工资和最低工资
select deptno, job, avg(sal) as 平均工资, min(sal) as 最低工资 from emp group by deptno, job;
-- 3. 筛选平均工资低于 2000 的部门（having 筛选分组结果）
select deptno, avg(sal) as 平均工资 from emp group by deptno having avg(sal) < 2000;

四、更新数据

更新是最容易误伤的一类操作。写 set 不难，难的是先把范围卡住。

update table_name set column = expr [,column = expr ...][where...][order by...][limit...]

-- 1. 更新单字段（孙悟空数学成绩改为 80）
update exam_result set math = 80 where name = '孙悟空';
-- 2. 更新多字段（曹孟德数学 60、语文 70）
update exam_result set math = 60, chinese = 70 where name = '曹孟德';
-- 3. 按表达式更新（所有同学语文成绩翻倍）
update exam_result set chinese = chinese * 2;
-- 4. 结合排序和 limit（总分倒数前三的同学数学 +30）
update exam_result set math = math + 30 order by chinese + math + english limit 3;
-- 5. 条件更新（英语<60 的同学英语 +10）
update exam_result set english = english + 10 where english < 60;

• 提醒：没有 where，就是全表更新。线上环境里这种语句最好先在客户端里跑一遍 select 看范围。

五、删除数据

删除分两种：带条件删，和直接清空。还有一个 truncate，它更快，但行为也更'硬'。

5.1 条件删除

-- 1. 删除孙悟空的考试成绩
delete from exam_result where name = '孙悟空';
-- 2. 删除英语<60 的同学成绩
delete from exam_result where english < 60;

5.2 全表删除：delete

delete 会一行一行删，事务里可回滚，自增主键不会重置。

-- 删除 for_delete 表所有数据（自增 id 不重置）
create table for_delete (id int primary key auto_increment, name varchar(20));
insert into for_delete (name) values ('a'), ('b'), ('c');
delete from for_delete;
-- 插入新数据，自增 id 从 4 开始
insert into for_delete (name) values ('d');
select * from for_delete; -- id=4

5.3 截断表：truncate

truncate 更快，做的是整表清空并重置自增值，但它不走逐行删除，回滚能力也和 delete 不一样。这个差别在出问题的时候会很明显。

-- 截断表（自增 id 重置为 1）
create table for_truncate (id int primary key auto_increment, name varchar(20));
insert into for_truncate (name) values ('a'), ('b'), ('c');
truncate for_truncate;
-- 插入新数据，自增 id 从 1 开始
insert into for_truncate (name) values ('d');
select * from for_truncate; -- id=1

• 区别：delete 是逐行删除，能回滚；truncate 是直接清空并重置表，速度更快，但不可回滚。

六、执行顺序和几个容易踩的坑

6.1 SQL 关键字执行顺序

from → on → join → where → group by → with → having → select → distinct → order by → limit

where 里不能直接用 select 阶段才算出来的别名，因为执行顺序摆在那儿。having 则是专门给分组结果用的，这点记住就少走很多弯路。

6.2 常见坑位

• 列够用就别写 select *，多取一列就多一份成本；
• 判断 null 用 is null / is not null，别拿 = 硬比；
• update 和 delete 先确认 where，不然就是全表操作；
• 分页一定配 order by，不然翻页顺序不稳定；
• 聚合函数会忽略 null，比如 count(qq) 不会把空值算进去；
• truncate 适合快速清空，但别把它当成可回滚的普通删除。

结语

CRUD 说到底就是把数据写对、查准、改稳、删干净。前面这些语句够应付大部分日常业务了，真正拉开差距的不是背语法，而是知道什么时候该收手、什么时候该加条件。多表联查、事务和索引优化属于下一层内容，等这套基础真顺手了，再往下学会轻松很多。