MySQL 增删改查（CRUD）完全指南：从入门到面试

本文面向 MySQL 初学者，系统讲解数据库表的增删改查（CRUD）操作。从创建学生表开始，逐步演示insert、select、update、delete四大核心语句的语法与实战案例，并深入对比replace与on duplicate key update的区别，同时涵盖注意事项与常见面试题，助你从零掌握 MySQL 数据操作的核心技能。

1. 引言

在数据库开发中，对表的增删改查（CRUD）是最基础也是最重要的操作。crud 是 create（创建）、retrieve（读取）、update（更新）、delete（删除）四个单词的缩写，分别对应 SQL 中的insert、select、update、delete语句。

本文将从零开始，带你系统掌握 MySQL 的增删改查操作。你将学会如何创建表、插入数据、查询数据、更新数据以及删除数据，并通过大量实战案例理解where条件、排序、分页、聚合函数等核心知识点，同时掌握replace与on duplicate key update的区别，为面试和实际开发打下坚实基础。

2. 准备工作：创建学生表

在开始增删改查之前，我们先创建一张学生表作为演示基础：

-- 创建一张学生表createtablestudents(idintunsignedprimarykeyauto_increment,snintnotnulluniquecomment'学号',namevarchar(20)notnull,qqvarchar(20));

这张表包含四个字段：

• id：主键，自增，无符号整数
• sn：学号，非空且唯一
• name：姓名，非空
• qq：QQ 号，可空

3. create：插入数据

3.1 单行数据 + 全列插入

insertintostudentsvalues(101,10001,'孙悟空','11111');

执行结果：

query ok, 1 row affected (0.02 sec)

3.2 多行数据 + 指定列插入

insertintostudents(id,sn,name)values(102,20001,'曹孟德'),(103,20002,'孙仲谋');

执行结果：

Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0

注意：value_list的数量必须和指定列的数量及顺序一致。插入时也可以不指定id，MySQL 会使用默认值进行自增。

3.3 插入否则更新（on duplicate key update）

当主键或唯一键对应的值已经存在而导致插入失败时，可以选择性地进行同步更新操作：

insertintostudents(id,sn,name)values(100,10010,'唐大师')onduplicatekeyupdatesn=10010,name='唐大师';

执行结果：

query ok, 2 rows affected (0.47 sec)

影响行数说明：

• 0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected：表中没有冲突数据，数据被插入
• 2 row affected：表中有冲突数据，并且数据已经被更新

可以通过 MySQL 函数获取受影响的数据行数：

selectrow_count();

3.4 替换（replace）

replace的机制是：主键或唯一键没有冲突则直接插入；如果冲突，则删除后再插入。

replaceintostudents(sn,name)values(20001,'曹阿瞒');

执行结果：

query ok, 2 rows affected (0.00 sec)

影响行数说明：

• 1 row affected：表中没有冲突数据，数据被插入
• 2 row affected：表中有冲突数据，删除后重新插入

3.5 replace 与 on duplicate key update 的区别

replace和on duplicate key update都用于处理主键或唯一键冲突，但底层机制和适用场景有明显差异：

核心区别示例：

假设students表中已存在sn = 20001的记录，且该行name为'曹孟德'、qq为'12345'：

-- 使用 replace：qq 列会被重置为默认值（null）replaceintostudents(sn,name)values(20001,'曹阿瞒');-- 结果：sn = 20001, name = '曹阿瞒', qq = null-- 使用 on duplicate key update：qq 列保持原值不变insertintostudents(sn,name)values(20001,'曹阿瞒')onduplicatekeyupdatename='曹阿瞒';-- 结果：sn = 20001, name = '曹阿瞒', qq = '12345'

选择建议：

• 需要整行替换、不关心其他列原值时，用replace into
• 需要保留其他列原值、只更新部分列时，用on duplicate key update
• 涉及外键约束时慎用replace，因为先删后插可能触发外键级联删除

4. retrieve：查询数据

4.1 创建成绩表

createtableexam_result(idintunsignedprimarykeyauto_increment,namevarchar(20)notnullcomment'同学姓名',chinesefloatdefault0.0comment'语文成绩',mathfloatdefault0.0comment'数学成绩',englishfloatdefault0.0comment'英语成绩');-- 插入测试数据insertintoexam_result(name,chinese,math,english)values('唐三藏',67,98,56),('孙悟空',87,78,77),('猪悟能',88,98,90),('曹孟德',82,84,67),('刘玄德',55,85,45),('孙权',70,73,78),('宋公明',75,65,30);

4.2 select 列

4.2.1 全列查询

select*fromexam_result;

注意：通常情况下不建议使用*进行全列查询：

1. 查询的列越多，需要传输的数据量越大
2. 可能会影响到索引的使用

4.2.2 指定列查询

selectid,name,englishfromexam_result;

指定列的顺序不需要按定义表的顺序来。

4.2.3 查询字段为表达式

-- 表达式不包含字段selectid,name,10fromexam_result;-- 表达式包含一个字段selectid,name,english+10fromexam_result;-- 表达式包含多个字段selectid,name,chinese+math+englishfromexam_result;

4.2.4 为查询结果指定别名

selectid,name,chinese+math+english 总分fromexam_result;

4.2.5 结果去重

-- 98 分重复了selectmathfromexam_result;-- 去重结果selectdistinctmathfromexam_result;

4.3 where 条件

4.3.1 比较运算符

4.3.2 逻辑运算符

4.3.3 实战案例

英语不及格的同学及英语成绩（< 60）：

selectname,englishfromexam_resultwhereenglish<60;

语文成绩在 [80, 90] 分的同学及语文成绩：

-- 使用 and 进行条件连接selectname,chinesefromexam_resultwherechinese>=80andchinese<=90;-- 使用 between ... and ... 条件selectname,chinesefromexam_resultwherechinesebetween80and90;

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩：

-- 使用 or 进行条件连接selectname,mathfromexam_resultwheremath=58ormath=59ormath=98ormath=99;-- 使用 in 条件selectname,mathfromexam_resultwheremathin(58,59,98,99);

姓孙的同学及孙某同学：

-- % 匹配任意多个（包括 0 个）任意字符selectnamefromexam_resultwherenamelike'孙%';-- _ 匹配严格的一个任意字符selectnamefromexam_resultwherenamelike'孙_';

语文成绩好于英语成绩的同学：

selectname,chinese,englishfromexam_resultwherechinese>english;

总分在 200 分以下的同学：

-- 别名不能用在 where 条件中selectname,chinese+math+english 总分fromexam_resultwherechinese+math+english<200;

语文成绩 > 80 并且不姓孙的同学：

selectname,chinesefromexam_resultwherechinese>80andnamenotlike'孙%';

孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80：

selectname,chinese,math,english,chinese+math+english 总分fromexam_resultwherenamelike'孙_'or(chinese+math+english>200andchinese<mathandenglish>80);

NULL 的查询：

-- 查询 qq 号已知的同学姓名selectname,qqfromstudentswhereqqisnotnull;-- null 和 null 的比较，= 和 <=> 的区别selectnull=null,null=1,null=0;selectnull<=>null,null<=>1,null<=>0;

4.4 结果排序（order by）

语法：

select...fromtable_name[where...]orderbycolumn[asc|desc],[...];

• asc为升序（从小到大）
• desc为降序（从大到小）
• 默认为asc

注意：没有order by子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

同学及数学成绩，按数学成绩升序显示：

selectname,mathfromexam_resultorderbymath;

同学及 qq 号，按 qq 号排序显示：

-- null 视为比任何值都小，升序出现在最上面selectname,qqfromstudentsorderbyqq;-- null 视为比任何值都小，降序出现在最下面selectname,qqfromstudentsorderbyqqdesc;

查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示：

-- 多字段排序，排序优先级随书写顺序selectname,math,english,chinesefromexam_resultorderbymathdesc,english,chinese;

按总分降序显示：

-- 别名可以在 order by 中使用selectname,chinese+math+english 总分fromexam_resultorderby总分desc;

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示：

selectname,mathfromexam_resultwherenamelike'孙%'ornamelike'曹%'orderbymathdesc;

4.5 筛选分页结果（LIMIT）

语法：

-- 起始下标为 0-- 从 s 开始，筛选 n 条结果select...fromtable_name[where...][orderby...]limits,n;-- 从 0 开始，筛选 n 条结果select...fromtable_name[where...][orderby...]limitn;-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用select...fromtable_name[where...][orderby...]limitnoffsets;

建议：对未知表进行查询时，最好加一条limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页：

-- 第 1 页selectid,name,math,english,chinesefromexam_resultorderbyidlimit3offset0;-- 第 2 页selectid,name,math,english,chinesefromexam_resultorderbyidlimit3offset3;-- 第 3 页，如果结果不足 3 个，不会有影响selectid,name,math,english,chinesefromexam_resultorderbyidlimit3offset6;

5. update：更新数据

语法：

updatetable_namesetcolumn=expr[,column=expr...][where...][orderby...][limit...]

5.1 将孙悟空同学的数学成绩变更为 80 分

-- 查看原数据SELECTname,mathFROMexam_resultWHEREname='孙悟空';-- 数据更新updateexam_resultsetmath=80wherename='孙悟空';-- 查看更新后数据SELECTname,mathFROMexam_resultWHEREname='孙悟空';

5.2 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

-- 一次更新多个列updateexam_resultsetmath=60,chinese=70wherename='曹孟德';

5.3 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

-- 查看原数据（别名可以在 ORDER BY 中使用）selectname,math,chinese+math+english 总分fromexam_resultorderby总分limit3;-- 数据更新，不支持 math += 30 这种语法updateexam_resultsetmath=math+30orderbychinese+math+englishlimit3;

5.4 将所有同学的语文成绩更新为原来的 2 倍

-- 没有 WHERE 子句，则更新全表updateexam_resultsetchinese=chinese*2;

注意：更新全表的语句慎用！

6. delete：删除数据

6.1 删除数据

语法：

deletefromtable_name[where...][orderby...][limit...]

删除孙悟空同学的考试成绩：

deletefromexam_resultwherename='孙悟空';

6.2 删除整张表数据

-- 准备测试表createtablefor_delete(idintprimarykeyauto_increment,namevarchar(20));-- 插入测试数据insertintofor_delete(name)values('A'),('B'),('C');-- 删除整表数据deletefromfor_delete;-- 再插入一条数据，自增 id 在原值上增长insertintofor_delete(name)values('D');

注意：删除整表操作要慎用！DELETE删除整表后，自增id会在原值上继续增长。

6.3 截断表（truncate）

语法：

truncate[table]table_name

注意：这个操作慎用！

1. 只能对整表操作，不能像delete一样针对部分数据操作
2. 实际上 mysql 不对数据操作，所以比delete更快，但是truncate在删除数据的时候，并不经过真正的事务，所以无法回滚
3. 会重置auto_increment项

-- 准备测试表createtablefor_truncate(idintprimarykeyauto_increment,namevarchar(20));-- 插入测试数据insertintofor_truncate(name)values('A'),('B'),('C');-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作truncatefor_truncate;-- 再插入一条数据，自增 id 重新增长insertintofor_truncate(name)values('D');

7. 插入查询结果

语法：

insertintotable_name[(column[,column...])]select...

案例：删除表中的重复记录，重复的数据只能有一份：

-- 创建原数据表createtableduplicate_table(idint,namevarchar(20));-- 插入测试数据insertintoduplicate_tablevalues(100,'aaa'),(100,'aaa'),(200,'bbb'),(200,'bbb'),(300,'ccc');-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样createtableno_duplicate_tablelikeduplicate_table;-- 将 duplicate_table 的去重数据插入到 no_duplicate_tableinsertintono_duplicate_tableselectdistinct*fromduplicate_table;-- 通过重命名表，实现原子的去重操作renametableduplicate_tabletoold_duplicate_table,no_duplicate_tabletoduplicate_table;

8. 聚合函数

8.1 统计班级共有多少同学

-- 使用 * 做统计，不受 NULL 影响selectcount(*)fromstudents;-- 使用表达式做统计selectcount(1)fromstudents;

8.2 统计班级收集的 qq 号有多少

-- null 不会计入结果selectcount(qq)fromstudents;

8.3 统计本次考试的数学成绩分数个数

-- count(math) 统计的是全部成绩selectcount(math)fromexam_result;-- count(distinct math) 统计的是去重后的成绩个数selectcount(distinctmath)fromexam_result;