MySQL 数据库开发中,CRUD(增删改查)是最核心的高频操作。基于实战场景,拆解 MySQL 的增删改查语法,涵盖单行多行插入、冲突处理、条件查询、排序分页及聚合分组等功能。重点讲解 SQL 执行顺序与常见避坑要点,如 null 判断、全表更新风险及 truncate 与 delete 的区别,帮助开发者提升 SQL 可读性与执行效率。
霸天1 浏览
在 MySQL 日常开发中,CRUD(create/retrieve/update/delete)是最核心的高频操作。掌握规范的 CRUD 语法、灵活的查询技巧和避坑要点,能大幅提升开发效率和 SQL 可读性。本文基于实战场景,全面拆解 MySQL 的增删改查操作,所有 SQL 语句均采用小写形式,贴合实际开发规范,同时涵盖聚合查询、分组统计等进阶内容。
基础准备:创建测试表与测试数据
为了让示例更直观,我们先创建两张测试表并插入数据,后续操作均基于这两张表。
学生表(students)
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INTNOT NULLUNIQUE 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);
考试成绩表(exam_result)
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese FLOATDEFAULT0.0 COMMENT '语文成绩',
math COMMENT ,
english COMMENT
);
exam_result (name, chinese, math, english)
(, , , ),
(, , , ),
(, , , ),
(, , , ),
(, , , ),
(, , , ),
(, , , );
FLOAT
DEFAULT
0.0
'数学成绩'
FLOAT
DEFAULT
0.0
'英语成绩'
INSERT INTO
VALUES
'唐三藏'
67
98
56
'孙悟空'
87
78
77
'猪悟能'
88
98
90
'曹孟德'
82
84
67
'刘玄德'
55
85
45
'孙权'
70
73
78
'宋公明'
75
65
30
插入数据(Create)
插入数据核心是 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, '杨志');
插入冲突处理(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 ='曹阿瞒';
替换插入(REPLACE INTO)
主键或唯一键冲突时,删除原记录后重新插入:
-- sn=20002 已存在,删除原记录后插入新数据
REPLACE INTO students (sn, name) VALUES (20002, '孙伯符');
插入查询结果
将一张表的查询结果插入另一张表(常用于数据迁移、去重):
-- 创建空表(结构与 students 一致)CREATE TABLE students_copy LIKE students;
-- 将 students 的去重数据插入新表INSERT INTO students_copy SELECTDISTINCT*FROM students;
查询数据(Retrieve)
查询是 CRUD 中最复杂的操作,支持全列查询、条件查询、排序、分页、聚合等功能,核心语法如下:
SELECT [DISTINCT] {*|column[,column]...}
FROM table_name
[WHERE...]
[ORDERBYcolumn [ASC|DESC],...]
LIMIT ...;
-- 查询姓名、语文、数学成绩SELECT name, chinese, math FROM exam_result;
查询表达式
支持常量、单字段运算、多字段运算:
-- 常量表达式SELECT id, name, 10FROM exam_result;
-- 单字段运算(英语成绩 +10)SELECT id, name, english +10FROM exam_result;
-- 多字段运算(总分)SELECT id, name, chinese + math + english FROM exam_result;
结果别名(AS 可省略)
给查询结果列指定别名,增强可读性:
SELECT id, name, chinese + math + english AS 总分 FROM exam_result;
结果去重(DISTINCT)
去除查询结果中的重复记录:
-- 去重查询数学成绩SELECTDISTINCT math FROM exam_result;
条件查询(WHERE)
通过比较运算符和逻辑运算符筛选数据,支持多种条件组合。
比较运算符
运算符
说明
>, >=, <, <=
大于、大于等于、小于、小于等于
=
等于(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 BETWEEN80AND90;
-- 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;
-- 注意:不能直接用'总分' < 200,因为执行这里的时候还没执行前面的部分-- 8. 语文>80 且不姓孙(AND + NOT)SELECT name, chinese FROM exam_result WHERE chinese >80AND name NOTLIKE'孙%';
-- 9. QQ 号不为空(IS NOT NULL)SELECT name, qq FROM students WHERE qq ISNOT NULL;
-- 10. NULL 安全比较(<=>)SELECT name, qq FROM students WHERE qq <=>NULL;
结果排序(ORDER BY)
默认升序(ASC),可指定降序(DESC),支持多字段排序。
-- 1. 按数学成绩升序SELECT name, math FROM exam_result ORDERBY math;
-- 2. 按数学降序,英语升序,语文升序SELECT name, math, english, chinese FROM exam_result ORDERBY math DESC, english, chinese;
-- 3. 按总分降序(表达式排序)SELECT name, chinese + math + english AS 总分 FROM exam_result ORDERBY 总分 DESC;
-- 4. NULL 排序(NULL 视为最小值,升序在最前)SELECT name, qq FROM students ORDERBY qq;
SELECT name, qq FROM students ORDERBY qq DESC;
分页查询(LIMIT)
限制查询结果数量,避免数据量过大导致性能问题,起始下标从 0 开始。
-- 语法 1:LIMIT 条数(取前 n 条)SELECT*FROM exam_result ORDERBY id LIMIT 3;
-- 语法 2:LIMIT 起始下标,条数(从 s(下标从 0 开始) 开始取 n 条)SELECT*FROM exam_result ORDERBY id LIMIT 3, 3;
-- 语法 3:LIMIT 条数 OFFSET 起始下标(推荐,更清晰)SELECT*FROM exam_result ORDERBY id LIMIT 3OFFSET6;
-- 分页示例:每页 3 条,第 1-3 页SELECT*FROM exam_result ORDERBY id LIMIT 3OFFSET0; -- 第 1 页SELECT*FROM exam_result ORDERBY id LIMIT 3OFFSET3; -- 第 2 页SELECT*FROM exam_result ORDERBY id LIMIT 3OFFSET6; -- 第 3 页
聚合查询(聚合函数)
对查询结果进行统计计算,常用聚合函数如下:
函数
说明
COUNT([DISTINCT] expr)
统计记录数(DISTINCT 去重)
SUM([DISTINCT] expr)
求和(仅数字类型)
AVG([DISTINCT] expr)
求平均值(仅数字类型)
MAX([DISTINCT] expr)
求最大值(仅数字类型)
MIN([DISTINCT] expr)
求最小值(仅数字类型)
聚合查询示例:
-- 1. 统计学生总数(COUNT(*) 不受 NULL 影响)SELECTCOUNT(*) AS 学生总数 FROM students;
-- 2. 统计 QQ 号非空的学生数(NULL 不计入)SELECTCOUNT(qq) AS qq 已收集人数 FROM students;
-- 3. 统计数学成绩总分和去重后总分SELECTSUM(math) AS 数学总分, SUM(DISTINCT math) AS 去重数学总分 FROM exam_result;
-- 4. 统计语文成绩平均分SELECTAVG(chinese) AS 语文平均分 FROM exam_result;
-- 5. 英语最高分和最低分SELECTMAX(english) AS 英语最高分, MIN(english) AS 英语最低分 FROM exam_result;
-- 6. 统计 70 分以上的数学最低分SELECTMIN(math) AS70+ 数学最低分 FROM exam_result WHERE math >70;
-- 1. 更新单字段(孙悟空数学成绩改为 80)UPDATE exam_result SET math =80WHERE name ='孙悟空';
-- 2. 更新多字段(曹孟德数学 60、语文 70)UPDATE exam_result SET math =60, chinese =70WHERE name ='曹孟德';
-- 3. 按表达式更新(所有同学语文成绩翻倍)UPDATE exam_result SET chinese = chinese *2;
-- 4. 结合排序和 limit(总分倒数前三的同学数学 +30)UPDATE exam_result SET math = math +30ORDERBY chinese + math + english LIMIT 3;
-- 5. 条件更新(英语<60 的同学英语 +10)UPDATE exam_result SET english = english +10WHERE english <60;
警告:无 WHERE 子句会更新全表数据,生产环境需谨慎。
删除数据(Delete)
删除表中数据,支持条件删除、全表删除,还有高效的 TRUNCATE 截断表。
条件删除
-- 1. 删除孙悟空的考试成绩DELETEFROM exam_result WHERE name ='孙悟空';
-- 2. 删除英语<60 的同学成绩DELETEFROM exam_result WHERE english <60;
全表删除(DELETE)
-- 删除 for_delete 表所有数据(自增 id 不重置)CREATE TABLE for_delete (id INTPRIMARY KEY AUTO_INCREMENT, name VARCHAR(20));
INSERT INTO for_delete (name) VALUES ('a'), ('b'), ('c');
DELETEFROM for_delete;
-- 插入新数据,自增 id 从 4 开始INSERT INTO for_delete (name) VALUES ('d');
SELECT*FROM for_delete;
-- id=4
截断表(TRUNCATE)
快速删除全表数据,重置自增 id,比 DELETE 更高效(不记录事务):
-- 截断表(自增 id 重置为 1)CREATE TABLE for_truncate (id INTPRIMARY 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 直接重置表(不可回滚),效率更高。
SQL 执行顺序与避坑指南
SQL 关键字执行顺序
FROM → ON → JOIN → WHERE → GROUPBY → WITH → HAVING → SELECT → DISTINCT → ORDERBY → LIMIT
别名不能在 WHERE 中使用(SELECT 在 WHERE 之后执行);
HAVING 用于筛选分组结果(GROUP BY 之后执行),WHERE 用于筛选行数据(GROUP BY 之前执行)。