MySQL DML操作(CRUD)总结

来源：互联网 2026-07-10 08:39:12

基于MySQL8.0，系统梳理数据表增查改删核心语法、实战案例与易错点，涵盖INSERT冲突处理、SELECT查询框架（去重、条件、排序、分页、聚合、分组）及执行顺序，总结开发中常见注意事项，如索引失效、事务隔离等误区。

在数据库开发工作中，增、查、改、删（即CRUD）是每天都需要处理的核心操作。本文基于MySQL 8.0，系统梳理数据表增(Create)、查(Retrieve)、改(Update)、删(Delete)的核心语法、实战案例、常见易错点以及SQL执行顺序。无论你是刚接触数据库的新手，还是希望查漏补缺的开发者，这篇文章都能提供实用的参考。

MySQL DML操作(CRUD)总结

长期稳定更新的攒劲资源： >>>点此立即查看<<<

一、前言：什么是CRUD

CRUD四个字母概括了日常数据管理的全部场景。下表清晰展示了各部分对应的SQL操作：

英文	中文	对应SQL操作
Create	新增数据	INSERT
Retrieve	查询数据	SELECT（使用频率最高）
Update	修改数据	UPDATE
Delete	删除数据	DELETE / TRUNCATE

在开始正式内容之前，需要准备好测试环境。本文所有案例均基于以下两张数据表，建议直接复制建表语句并在本地运行，跟随操作效果最佳。

-- 学生表：存储学生基础信息
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID，自增',
    sn INT NOT NULL UNIQUE COMMENT '学号，唯一约束',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    qq VARCHAR(20) COMMENT 'QQ号，允许为空'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 成绩表：存储学生考试成绩（核心查询案例表）
CREATE TABLE exam_result (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    name VARCHAR(20) NOT NULL COMMENT '学生姓名',
    chinese FLOAT DEFAULT 0.0 COMMENT '语文成绩',
    math FLOAT DEFAULT 0.0 COMMENT '数学成绩',
    english FLOAT DEFAULT 0.0 COMMENT '英语成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 给成绩表插入测试数据
INSERT INTO exam_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);

二、数据新增：INSERT（Create）

2.1 基础语法

-- 标准语法，INTO可省略
INSERT [INTO] 表名 [(字段1, 字段2, ...)] VALUES (值1, 值2, ...), (值1, 值2, ...);

使用INSERT时需要掌握几个关键规则：

字段列表和VALUES值列表的数量、顺序、数据类型必须一一对应；
自增主键及有默认值的字段可以省略不写，MySQL会自动填充；
支持单行插入和多行批量插入。在实际开发中，批量插入的效率远高于多次单行插入，建议优先使用。

2.2 分类实战案例

2.2.1 全列插入（不指定字段）

如果不写字段列表，需要为表中所有字段按顺序赋值，包括自增主键也需要手动指定值。

-- 单行全列插入
INSERT INTO students VALUES (1, 1001, '张三', '123456');

-- 多行全列插入
INSERT INTO students VALUES 
(2, 1002, '李四', '654321'),
(3, 1003, '王五', NULL); -- QQ为空，使用NULL

2.2.2 指定列插入（推荐用法）

只给部分字段赋值，未指定的字段会自动使用默认值或自增规则。日常开发中这种写法最为常用，灵活且安全。

-- 仅插入学号、姓名，id自增、qq默认为NULL
INSERT INTO students (sn, name) VALUES (1004, '赵六'), (1005, '钱七');

2.2.3 插入冲突处理（主键/唯一键重复）

当主键(PRIMARY KEY)或唯一键(UNIQUE)出现重复时，直接插入会报错Duplicate entry。MySQL提供了两种解决方案，需根据场景选择使用。

方案1：存在则更新 ON DUPLICATE KEY UPDATE

逻辑清晰：键重复时更新指定字段，无重复则正常插入。

-- id=1已存在，触发更新；不存在则新增
INSERT INTO students (id, sn, name) VALUES (1, 1009, '小张')
ON DUPLICATE KEY UPDATE sn = 1009, name = '小张';

这里有一个易错点：受影响的行数含义不同。

1 row affected：无冲突，数据新增成功；
2 row affected：存在冲突，数据更新成功；
0 row affected：存在冲突，但新旧数据完全一致，无变更。

方案2：存在则替换 REPLACE INTO

逻辑更直接：键重复时先删除原数据，再插入新数据；无重复则直接插入。

-- 学号sn是唯一键，重复则删除旧数据、插入新数据
REPLACE INTO students (sn, name, qq) VALUES (1001, '新张三', '999999');

两种方案的关键区别：

ON DUPLICATE KEY UPDATE：原地更新，保留原数据主键；
REPLACE：删旧插新，自增主键会重新生成，需谨慎使用。

2.3 拓展：插入查询结果

将一张表的查询结果直接插入另一张表，常用于数据备份、去重及数据迁移等场景。

-- 语法：INSERT ... SELECT
INSERT INTO 目标表(字段1,字段2) SELECT 字段1,字段2 FROM 源表 [条件];

-- 示例：将成绩表中数学>80的学生姓名、成绩插入新表
CREATE TABLE temp_result (name VARCHAR(20), math FLOAT);
INSERT INTO temp_result (name, math) 
SELECT name, math FROM exam_result WHERE math > 80;

三、数据查询：SELECT（Retrieve）【重点】

SELECT是MySQL中使用频率最高的语句。它支持字段筛选、条件过滤、排序、分页、聚合、分组等功能，以下逐一进行拆解。

3.1 完整语法框架

SELECT [DISTINCT] 字段列表
FROM 表名
[WHERE 条件]          -- 行过滤
[GROUP BY 分组字段]   -- 分组
[HA VING 分组后条件]   -- 分组过滤
[ORDER BY 排序字段]   -- 结果排序
[LIMIT 分页限制];     -- 分页

3.2 基础查询

3.2.1 全列查询

*代表查询表中所有字段，是最简便的写法。

SELECT * FROM exam_result;

开发易错点：生产环境中应避免使用SELECT *。原因有三：第一，传输大量无用数据，影响性能；第二，无法利用索引，查询效率降低；第三，表结构发生变化后，查询结果列可能出现错乱。建议养成指定字段的好习惯。

3.2.2 指定列查询（推荐）

手动指定需要查询的字段，顺序可自定义，与表结构顺序无关。

-- 只查询姓名、语文、数学成绩
SELECT name, chinese, math FROM exam_result;

3.2.3 查询表达式与字段别名

查询列可以是算术表达式（加减乘除），使用AS为列设置别名（AS可省略）。

-- 1. 计算总分，并设置别名total_score
SELECT 
    name,
    chinese,
    math,
    english,
    chinese + math + english AS total_score
FROM exam_result;

-- 2. AS可省略，简写形式
SELECT name, chinese + math + english total FROM exam_result;

易错点：WHERE子句不能使用字段别名（与执行顺序有关），但ORDER BY可以使用别名。这个细节容易被忽略。

3.2.4 结果去重 DISTINCT

去除查询结果中完全重复的行，写在SELECT之后。

-- 查询所有不重复的数学成绩
SELECT DISTINCT math FROM exam_result;

3.3 条件过滤：WHERE子句

WHERE用于筛选符合条件的行，支持比较运算符、逻辑运算符、模糊查询、空值判断等操作。

3.3.1 常用运算符汇总

1）比较运算符

运算符	作用	备注
> >= < <=	大小比较	常规数值判断
=	等于	对NULL无效，NULL = NULL结果仍为NULL
<=>	安全等于	支持NULL比较，NULL <=> NULL结果为真
!= / <>	不等于	两种写法等价
BETWEEN a AND b	区间匹配	闭区间 [a,b]，包含两端值
IN(值1,值2...)	枚举匹配	匹配括号内任意一个值

2）空值判断

MySQL中NULL不等于任何值，必须使用专用关键字：

IS NULL：判断字段为空
IS NOT NULL：判断字段不为空

3）逻辑运算符

AND：并且（所有条件同时成立）
OR：或者（任意一个条件成立）
NOT：取反（条件不成立）

4）模糊查询 LIKE

搭配通配符使用，常用于搜索场景：

%：匹配任意长度字符（包含0个字符）
_：匹配单个字符

3.3.2 实战案例

-- 1. 英语成绩低于60分的学生（比较运算）
SELECT name, english FROM exam_result WHERE english < 60;

-- 2. 语文成绩在[80,90]区间（两种写法等价）
SELECT name, chinese FROM exam_result WHERE chinese >=80 AND chinese <=90;
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 3. 数学成绩为78、84、98之一（IN枚举）
SELECT name, math FROM exam_result WHERE math IN(78,84,98);

-- 4. 模糊查询：姓"张"的学生（%通配）
SELECT name FROM exam_result WHERE name LIKE '张%';
-- 名字第二个字为"五"（_单个字符）
SELECT name FROM exam_result WHERE name LIKE '_五';

-- 5. 判断空值：查询QQ号为空的学生
SELECT name FROM students WHERE qq IS NULL;

-- 6. 多条件组合：语文>80且不姓王
SELECT name, chinese FROM exam_result WHERE chinese > 80 AND NOT name LIKE '王%';

3.4 结果排序：ORDER BY

对查询结果进行排序，默认升序(ASC)。语法：ORDER BY 字段 [ASC|DESC]

ASC：升序（从小到大，默认值可省略）
DESC：降序（从大到小）

3.4.1 单字段排序

-- 数学成绩升序（默认ASC）
SELECT name, math FROM exam_result ORDER BY math;
-- 数学成绩降序
SELECT name, math FROM exam_result ORDER BY math DESC;

3.4.2 多字段排序（优先级从左到右）

先按第一个字段排序，值相同时再按第二个字段排序。

-- 先数学降序，数学相同则英语升序
SELECT name, math, english FROM exam_result
ORDER BY math DESC, english ASC;

3.4.3 排序规则补充（易错点）

NULL在排序中视为最小值：升序时NULL排在最前，降序时排在最后；
ORDER BY支持表达式和字段别名；
没有ORDER BY时，查询结果顺序无定义，不要依赖默认顺序。

3.5 分页查询：LIMIT

分页是项目中的必备功能，用于限制返回数据的行数。推荐使用语义更清晰的第三种写法：

-- 写法1：LIMIT 行数（从第0条开始，取N条）
SELECT 字段 FROM 表 LIMIT N;

-- 写法2：LIMIT 偏移量, 行数（偏移量从0开始）
SELECT 字段 FROM 表 LIMIT offset, N;

-- 写法3（推荐，语义清晰）：LIMIT 行数 OFFSET 偏移量
SELECT 字段 FROM 表 LIMIT N OFFSET offset;

分页实战（每页3条数据）

-- 第1页：偏移0，取3条
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;

-- 第2页：偏移3，取3条
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 3;

-- 第3页：偏移6，取3条（不足3条则返回剩余数据）
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;

开发建议：查询未知大表时，先加LIMIT 1试水，避免全表查询对数据库造成过大压力。

3.6 聚合函数

聚合函数用于统计计算，作用于一组数据并返回一个结果。常用的五大聚合函数如下：

函数	作用	特性
COUNT(字段/*)	统计行数	COUNT(*)统计所有行；COUNT(字段)忽略NULL
SUM(字段)	求和	仅对数值有效，无数据返回NULL
A VG(字段)	求平均值	忽略NULL
MAX(字段)	求最大值	支持数值、字符串
MIN(字段)	求最小值	支持数值、字符串

聚合函数案例

-- 1. 统计总人数（COUNT(*)统计所有行）
SELECT COUNT(*) AS total_student FROM exam_result;

-- 2. 统计有QQ号的学生数（COUNT(字段)忽略NULL）
SELECT COUNT(qq) AS ha ve_qq FROM students;

-- 3. 数学成绩总分、平均分
SELECT SUM(math) AS math_sum, A VG(math) AS math_a vg FROM exam_result;

-- 4. 英语最高分、最低分
SELECT MAX(english) AS max_en, MIN(english) AS min_en FROM exam_result;

-- 5. 去重统计：不重复的数学成绩数量
SELECT COUNT(DISTINCT math) FROM exam_result;

3.7 分组查询：GROUP BY + HA VING

GROUP BY用于按照指定字段分组，配合聚合函数进行分组统计；HA VING用于对分组后的结果进行再次过滤（与WHERE不同）。

核心区别（高频面试题）

WHERE：分组前过滤原始数据，不能使用聚合函数；
HA VING：分组后过滤分组结果，可以使用聚合函数。

分组案例

-- 模拟场景：新增班级字段，按班级分组统计
ALTER TABLE exam_result ADD class VARCHAR(10) COMMENT '班级';
UPDATE exam_result SET class = '一班' WHERE id <=4;
UPDATE exam_result SET class = '二班' WHERE id >4;

-- 1. 按班级分组，统计每个班级人数、语文平均分
SELECT class, COUNT(*) AS num, A VG(chinese) AS a vg_ch
FROM exam_result
GROUP BY class;

-- 2. 分组后过滤：只显示平均分大于70的班级（HA VING）
SELECT class, A VG(chinese) AS a vg_ch
FROM exam_result
GROUP BY class
HA VING a vg_ch > 70;

四、数据修改：UPDATE

4.1 基础语法

UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2, ...
[WHERE 条件] [ORDER BY 排序][LIMIT 行数];

高危警告：省略WHERE条件会更新全表所有数据。生产环境中严禁直接使用UPDATE 表名 SET ...而不加条件！

4.2 实战案例

-- 1. 单字段更新：将张三的数学成绩改为80
UPDATE exam_result SET math = 80 WHERE name = '张三';

-- 2. 多字段更新：同时修改语文、英语成绩
UPDATE exam_result SET chinese = 70, english = 60 WHERE name = '李四';

-- 3. 基于原数据更新（字段自增或自减）
UPDATE exam_result SET math = math + 10 WHERE math < 80;

-- 4. 结合排序+分页更新：成绩倒数3名，数学+20分
UPDATE exam_result SET math = math + 20
ORDER BY chinese + math + english ASC
LIMIT 3;

五、数据删除：DELETE & TRUNCATE

5.1 DELETE语句（删除行数据）

5.1.1 基础语法

-- 删除符合条件的行
DELETE FROM 表名 [WHERE 条件] [LIMIT 行数];

案例

-- 1. 删除指定学生数据（推荐，带WHERE条件）
DELETE FROM exam_result WHERE name = '李四';

-- 2. 删除全表数据（高危操作！）
DELETE FROM students;

DELETE特性

只删除数据，保留表结构；
支持事务，可以回滚；
自增主键AUTO_INCREMENT不会重置，再次插入时会延续之前的ID。

5.2 TRUNCATE语句（截断表）

语法

TRUNCATE [TABLE] 表名;

TRUNCATE特性（与DELETE的核心区别，面试重点）

只能清空整张表，无法按条件删除单行；
不触发事务、无法回滚，执行速度远快于DELETE；
清空后重置自增主键，新数据ID从1重新开始；
属于DDL语句（数据定义），而DELETE属于DML语句（数据操作）。

适用场景

仅用于整表数据清空（测试环境、数据初始化等），生产环境需谨慎使用。

5.3 DELETE vs TRUNCATE 对比表

特性	DELETE	TRUNCATE
操作范围	可按条件删除单行或多行	只能清空全表
事务支持	支持事务、可回滚	不支持事务、不可回滚
自增ID	不重置	重置为初始值
执行速度	较慢	极快
语句类型	DML（数据操作）	DDL（数据定义）