MySQL CRUD 核心实战：增删改查全流程解析

插入数据（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 SELECT DISTINCT * FROM students;

查询数据（Retrieve）

查询是 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;

结果别名（AS 可省略）

给查询结果列指定别名，增强可读性：

SELECT id, name, chinese + math + english AS 总分 FROM exam_result;

结果去重（DISTINCT）

去除查询结果中的重复记录：

-- 去重查询数学成绩
SELECT DISTINCT math FROM exam_result;

条件查询（WHERE）

通过比较运算符和逻辑运算符筛选数据，支持多种条件组合。

比较运算符

逻辑运算符

条件查询示例

-- 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;
-- 注意：不能直接用'总分' < 200，因为执行这里的时候还没执行前面的部分

-- 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;

结果排序（ORDER BY）

默认升序（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;

分页查询（LIMIT）

限制查询结果数量，避免数据量过大导致性能问题，起始下标从 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 页

聚合查询（聚合函数）

对查询结果进行统计计算，常用聚合函数如下：

聚合查询示例：

-- 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）

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;

更新数据（Update）

修改表中已有数据，支持单字段、多字段更新，结合 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 子句会更新全表数据，生产环境需谨慎。

删除数据（Delete）

删除表中数据，支持条件删除、全表删除，还有高效的 TRUNCATE 截断表。

条件删除

-- 1. 删除孙悟空的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';

-- 2. 删除英语<60 的同学成绩
DELETE FROM exam_result WHERE english < 60;

全表删除（DELETE）

-- 删除 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

截断表（TRUNCATE）

快速删除全表数据，重置自增 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 直接重置表（不可回滚），效率更高。

SQL 执行顺序与避坑指南

SQL 关键字执行顺序

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、聚合函数、分组查询满足复杂需求；
• 更新/删除需精准控制范围，避免全表操作；
• 遵循 SQL 执行顺序，避开 NULL 判断、别名使用等常见坑。