MySQL DML操作CRUD总结

做数据库开发，每天绕不开的就是那四个字——增、查、改、删，也就是CRUD。这篇文章基于MySQL 8.0，把数据表增(Create)、查(Retrieve)、改(Update)、删(Delete)的核心语法、实战案例、易错点和执行顺序一次性讲透。不管你是刚入门还是想查漏补缺，这里都有干货。

一、前言：什么是 CRUD

CRUD这个名字很形象，对应着日常数据管理的全部场景。一张表就能概括：

先准备好测试环境。后面所有案例都基于这两张表，建议直接复制建表语句跑一遍，跟着操作效果最好。

-- 学生表：存储学生基础信息
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, ...);

几个关键规则得记牢：

1. 字段列表和VALUES值列表，数量、顺序、数据类型必须一一对应，一个萝卜一个坑；
2. 自增主键、有默认值的字段可以省略不写，MySQL会自动填充；
3. 支持单行插入和多行批量插入。实际开发中，批量插入的效率远高于多次单行插入，能用就用。

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 分组字段]   -- 分组
[HAVING 分组后条件]   -- 分组过滤
[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. 计算总分，并设置别名 总分
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）比较运算符

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 排序规则补充（易错点）

1. NULL 在排序中视为最小值：升序时 NULL 排在最前，降序时排在最后；
2. ORDER BY 支持表达式、字段别名；
3. 没有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 聚合函数

聚合函数用于统计计算，作用于一组数据返回一个结果。常用5大聚合函数：

聚合函数案例

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

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

-- 3. 数学成绩总分、平均分
SELECT SUM(math) AS math_sum, AVG(math) AS math_avg 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 + HAVING

GROUP BY：按照指定字段分组，配合聚合函数做分组统计；HAVING：对分组后的结果再次过滤（区别于WHERE）。

核心区别（高频面试题）

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

分组案例

-- 模拟场景：新增班级字段，按班级分组统计
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, AVG(chinese) AS avg_ch
FROM exam_result
GROUP BY class;

-- 2. 分组后过滤：只显示平均分大于70的班级（HAVING）
SELECT class, AVG(chinese) AS avg_ch
FROM exam_result
GROUP BY class
HAVING avg_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 特性

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

5.2 TRUNCATE 语句（截断表）

语法

TRUNCATE [TABLE] 表名;

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

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

适用场景

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

5.3 DELETE vs TRUNCATE 对比表

六、高频考点：SQL 关键字执行顺序（面试必背）

完整执行顺序（从先到后）：

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

顺序解读（解释易错点）

1. 先过FROM找到表，WHERE过滤原始数据；
2. GROUP BY分组，HAVING过滤分组结果；
3. 再执行SELECT生成字段别名 → 去重DISTINCT；
4. 最后ORDER BY排序、LIMIT分页。

这个顺序对应了前面提到的一系列易错点：

• WHERE不能使用SELECT定义的别名，因为别名是WHERE执行完后才生成的；
• HAVING、ORDER BY可以使用别名，因为它们的执行顺序在SELECT之后。

七、全局易错点总结（避坑指南）

1. NULL 相关：= NULL永远查不到数据，必须用IS NULL / IS NOT NULL；NULL <=> NULL才会判定为真。
2. 全表操作：UPDATE、DELETE不加WHERE会操作全表，生产环境禁止使用。
3. 别名使用：WHERE不能用字段别名，ORDER BY/HAVING可以。
4. DISTINCT：针对整行去重，不是单个字段。
5. LIMIT 偏移量：偏移量从0开始，分页公式：偏移量 = (页码 - 1) * 每页条数。
6. TRUNCATE：不可逆、不支持事务，正式环境严禁随意执行。
7. INSERT 多行：优先使用多行VALUES批量插入，性能远高于多次单行插入。

八、总结

这篇文章把 MySQL CRUD 全部核心语法、实战案例、易错点、面试考点都梳理了一遍，算是 MySQL 入门阶段的重中之重：

1. 新增：INSERT 分单行/多行、指定列/全列，冲突处理用 ON DUPLICATE KEY / REPLACE；
2. 查询：SELECT 是核心，掌握条件、排序、分页、聚合、分组五大功能；
3. 修改：UPDATE 务必加 WHERE，避免全表更新；
4. 删除：区分 DELETE（灵活、可回滚）和 TRUNCATE（极速、清空全表）；
5. 执行顺序：理解关键字执行顺序，就能规避 90% 的语法报错。