MySQL CRUD 操作涵盖创建、读取、更新、删除四大核心功能。文章通过构建测试表演示单行/多行插入、冲突处理及替换插入；详解基础查询、条件筛选、排序分页及聚合分组统计；对比 Update 与 Delete 的安全用法，并解析 SQL 执行顺序与常见避坑要点，帮助开发者规范编写高效 SQL 语句。
在 MySQL 日常开发中，CRUD（Create/Retrieve/Update/Delete）是最核心的高频操作。掌握规范的 CRUD 语法、灵活的查询技巧和避坑要点，能大幅提升开发效率和 SQL 可读性。本文基于实战场景，全面拆解 MySQL 的增删改查操作，所有 SQL 语句均采用小写形式，贴合实际开发规范，同时涵盖聚合查询、分组统计等进阶内容。
为了让所有示例更直观，先创建两张测试表并插入测试数据，后续操作均基于这两张表：
INSERT [INTO] table_name [(column[,column]...)] VALUES (value_list)[,(value_list)]...
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
-- 插入测试数据
INSERT INTO students VALUES
(100, 10000, '唐三藏', NULL),
(101, 10001, '孙悟空', '11111'),
(102, 20001, '曹孟德', NULL),
(103, 20002, '孙仲谋', NULL);
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese FLOAT DEFAULT 0.0 COMMENT '语文成绩',
math FLOAT DEFAULT 0.0 COMMENT '数学成绩',
english FLOAT DEFAULT 0.0 COMMENT '英语成绩'
);
-- 插入测试数据
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 语句，支持单行 / 多行插入、指定列插入、冲突处理等场景。
插入数据需与表结构的列数和顺序完全一致（自增主键可省略，自动生成）：
-- 全列插入（指定 id）
INSERT INTO students VALUES (104, 20003, '鲁智深', '22222');
-- 省略自增主键（自动生成 id）
INSERT INTO students (sn, name, qq) VALUES (20004, '林冲', '33333');
一次插入多条数据，仅指定需要赋值的列，未指定列使用默认值或 null：
INSERT INTO students (sn, name) VALUES (20005, '武松'), (20006, '杨志');
当主键或唯一键冲突时，不报错而是执行更新操作：
-- 主键冲突（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 = '曹阿瞒';
主键或唯一键冲突时，删除原记录后重新插入：
-- sn=20002 已存在，删除原记录后插入新数据
REPLACE INTO students (sn, name) VALUES (20002, '孙伯符');
将一张表的查询结果插入另一张表（常用于数据迁移、去重）：
-- 创建空表（结构与 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 ...;
-- 全列查询（数据量大时性能差，不建议在生产环境使用）
-- 1. 查询的列越多，意味着需要传输的数据量越大
-- 2. 可能会影响到索引的使用
SELECT * FROM exam_result;
按需查询列，顺序可与表结构不一致：
-- 查询姓名、语文、数学成绩
SELECT name, chinese, math FROM exam_result;
支持常量、单字段运算、多字段运算：
-- 常量表达式
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;
给查询结果列指定别名，增强可读性：
SELECT id, name, chinese + math + english AS 总分 FROM exam_result;
去除查询结果中的重复记录：
-- 去重查询数学成绩 distinct
SELECT DISTINCT math FROM exam_result;
通过比较运算符和逻辑运算符筛选数据，支持多种条件组合。
| 运算符 | 说明 |
|---|---|
>, >=, <, <= | 大于、大于等于、小于、小于等于 |
= | 等于（null 不安全，null = null 结果为 null） |
<=> | 等于（null 安全，null <=> null 结果为 1） |
!=, <> | 不等于 |
between a and b | 范围匹配（[a, b]） |
in (option...) | 匹配选项中的任意一个 |
is null | 为空 |
is not null | 不为空 |
like | 模糊匹配（% 匹配任意字符，_ 匹配单个字符） |
| 运算符 | 说明 |
|---|---|
| AND | 多个条件同时成立 |
| OR | 任意一个条件成立 |
| NOT | 条件取反 |
-- 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;
默认升序（asc），可指定降序（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;
限制查询结果数量，避免数据量过大导致性能问题，起始下标从 0 开始。
-- 语法 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 页
对查询结果进行统计计算，常用聚合函数如下：
| 函数 | 说明 |
|---|---|
count([DISTINCT] expr) | 统计记录数（distinct 去重） |
sum([DISTINCT] expr) | 求和（仅数字类型） |
avg([DISTINCT] expr) | 求平均值（仅数字类型） |
max([DISTINCT] expr) | 求最大值（仅数字类型） |
min([DISTINCT] expr) | 求最小值（仅数字类型） |
聚合查询示例：
-- 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;
group by 按指定列分组，having 筛选分组结果（类似 where，但作用于分组）。
-- 准备雇员表（经典测试表）
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;
修改表中已有数据，支持单字段、多字段更新，结合 where、order by、limit 精准控制更新范围。
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 子句会更新全表数据，生产环境需谨慎删除表中数据，支持条件删除、全表删除，还有高效的 truncate 截断表。
-- 1. 删除孙悟空的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';
-- 2. 删除英语<60 的同学成绩
DELETE FROM exam_result WHERE english < 60;
-- 删除 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
快速删除全表数据，重置自增 id，比 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 直接重置表（不可回滚），效率更高。FROM → ON → JOIN → WHERE → GROUP BY → WITH → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
where 中使用（select 在 where 之后执行）；having 用于筛选分组结果（group by 之后执行），where 用于筛选行数据（group by 之前执行）。
null 判断用 is null/is not null：= 和 != 对 null 无效；where：防止误操作全表数据；order by：避免分页结果混乱；null：count(qq) 不计入 qq 为 null 的记录；truncate 不可回滚：删除全表数据优先考虑 delete（需回滚）或 truncate（高效）。总结： MySQL CRUD 是数据库开发的基础，核心要点：
where、order by、limit、聚合函数、分组查询满足复杂需求；null 判断、别名使用等常见坑。掌握这些操作后，可应对大部分业务场景。如果需要针对复杂查询（如多表联查）、事务、索引优化等进阶内容，欢迎在评论区留言交流！后续会持续更新 MySQL 进阶技巧，带你从入门到精通数据库开发。
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online
将 Markdown（GFM）转为 HTML 片段，浏览器内 marked 解析；与 HTML转Markdown 互为补充。 在线工具，Markdown转HTML在线工具，online