小叶-duck个人主页❄️个人专栏《Data-Structure-Learning》《C入门到进阶自我学习过程记录》《Linux系统从入门到实践》《Linux网络从入门到实践》《Qt 方寸极境》 《MySQL》✨未择之路不须回头已择之路纵是荆棘遍野亦作花海遨游目录前言一、Create插入数据1.1 基础准备构建一张学生表1.2 单行全列/指定列插入1.3 多行全列/指定列插入1.4 插入冲突处理on duplicate key update1.5 替换插入replace into二、Retrieve查询数据2.1 基础查询2.1.1 全列查询不推荐2.1.2 指定列查询2.1.3 查询表达式2.1.4 结果别名as 可省略2.1.5 结果去重distinct2.2 条件查询where2.2.1 比较运算符2.2.2 逻辑运算符2.2.3 条件查询示例2.3 结果排序order by2.4 分页查询limit2.5 插入查询结果(常用于数据迁移、去重)2.6 聚合查询聚合函数2.7 分组查询group by having2.7.1 准备工作创建一个雇员信息表2.7.2 分组查询示例2.7.3 WHERE 和 HAVING 核心对比总结2.8 查询顺序小结三、Update更新数据四、Delete删除数据4.1 条件删除4.2 全表删除delete4.3 截断表truncate五、实战 OJ 真题DML 增删查改落地应用六、SQL 避坑要点和总结结束语前言MySQL 日常开发里增删改查CRUD是使用最频繁的基础操作。熟练使用标准化 SQL 语法、掌握高效查询技巧与常见踩坑规避方法能够有效提升编码效率也能让 SQL 语句更整洁易懂。本文结合真实开发场景细致拆解 MySQL 各类增删改查用法全文 SQL 统一采用小写书写贴合工业开发规范同时拓展聚合统计、分组查询等常用高阶知识点。一、Create插入数据插入数据核心是 insert 语句支持单行 / 多行插入、指定列插入、冲突处理等场景。语法INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ...1.1 基础准备构建一张学生表-- 创建一张学生表 CREATE TABLE students ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, sn INT NOT NULL UNIQUE COMMENT 学号, name VARCHAR(20) NOT NULL, qq VARCHAR(20) );1.2 单行全列/指定列插入插入数据需与表结构的列数和顺序完全一致自增主键可省略自动生成-- 全列插入指定id insert into students values (104, 20003, 鲁智深, 22222); -- 指定列插入省略自增主键自动生成id insert into students (sn, name, qq) values (20004, 林冲, 33333);1.3 多行全列/指定列插入一次插入多条数据仅指定需要赋值的列未指定列使用默认值或null多条数据之间使用逗号进行分隔insert into students (sn, name) values (20005, 武松), (20006, 杨志);1.4 插入冲突处理on duplicate key update当因为主键或唯一键冲突时不报错而是执行更新操作-- 主键冲突id100已存在执行更新 insert into students (id, sn, name) values (100, 10010, 唐大师) on duplicate key update sn 10010, name 唐大师; // 同步更新语法 -- 唯一键冲突sn20001已存在执行更新 insert into students (sn, name) values (20001, 曹阿瞒) on duplicate key update name 曹阿瞒;1.5 替换插入replace into主键或唯一键冲突时删除原记录后重新插入-- sn20002已存在删除原记录后插入新数据 replace into students (sn, name) values (20002, 孙伯符);二、Retrieve查询数据2.1 基础查询2.1.1 全列查询不推荐-- 全列查询数据量大时性能差不建议在生产环境使用 -- 1. 查询的列越多意味着需要传输的数据量越大 -- 2. 可能会影响到索引的使用。(索引待后面我们再进行理解) select * from exam_result;2.1.2 指定列查询按需查询列顺序可与表结构不一致-- 查询姓名、语文、数学成绩 select name, chinese, math from exam_result;2.1.3 查询表达式支持常量、单字段运算、多字段运算-- 常量表达式 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;2.1.4 结果别名as 可省略给查询结果列指定别名增强可读性select id, name, chinese math english as 总分 from exam_result;2.1.5 结果去重distinct但是需要注意distinct 的去重针对的是显示结果上面而并没有对原表的实际数据进行去重操作而对原表的实际数据进行去重操作的方法后面我们再进行讲解也是借助了 distinct 但是逻辑上更加复杂。去除查询结果中的重复记录-- 去重查询数学成绩 distinct select distinct math from exam_result;2.2 条件查询where通过比较运算符和逻辑运算符筛选数据支持多种条件组合。2.2.1 比较运算符运算符说明, , , 大于、大于等于、小于、小于等于等于null 不安全null null 结果为 null等于null 安全null null 结果为 1!, 不等于between a and b范围匹配[a, b]闭区间in (option...)匹配选项中的任意一个is null为空is not null不为空like模糊匹配%匹配任意字符_匹配单个字符2.2.2 逻辑运算符运算符说明and多个条件同时成立or任意一个条件成立not条件取反2.2.3 条件查询示例-- 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;2.3 结果排序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.查询姓孙的同学或者姓曹的同学数学成绩结果按数学成绩由高到低显示 select name, math from exam_result where name like 孙% or name like 曹% order by math desc;2.4 分页查询limit限制查询结果数量避免数据量过大导致性能问题起始下标从 0 开始。-- 语法1limit 条数取前n条 select * from exam_result order by id limit 3; -- 语法2limit 起始下标, 条数从s(下标从0开始)开始取n条 select * from exam_result order by id limit 3, 3; -- 语法3limit 条数 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页2.5 插入查询结果(常用于数据迁移、去重)语法INSERT INTO table_name [(column [, column ...])] SELECT ...将一张表的查询结果插入另一张表常用于数据迁移、去重-- 创建原数据表 CREATE TABLE duplicate_table (id int, name varchar(20)); Query OK, 0 rows affected (0.01 sec) -- 插入测试数据 INSERT INTO duplicate_table VALUES (100, aaa), (100, aaa), (200, bbb), (200, bbb), (200, bbb), (300, ccc); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 -- 思路 -- 创建一张空表 no_duplicate_table结构和 duplicate_table 一样 CREATE TABLE no_duplicate_table LIKE duplicate_table; Query OK, 0 rows affected (0.00 sec) -- 将 duplicate_table 的去重数据插入到 no_duplicate_table INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: -- 通过重命名表实现原子的去重操作 RENAME TABLE duplicate_table TO old_duplicate_table, no_duplicate_table TO duplicate_table; Query OK, 0 rows affected (0.00 sec) -- 查看最终结果 SELECT * FROM duplicate_table; ------------ | id | name | ------------ | 100 | aaa | | 200 | bbb | | 300 | ccc | ------------ 3 rows in set (0.00 sec)2.6 聚合查询聚合函数对查询结果进行统计计算常用聚合函数如下函数说明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(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;2.7 分组查询group by havinggroup by按指定列分组having筛选分组结果类似where但作用于分组结果。2.7.1 准备工作创建一个雇员信息表包含了三张表EMP员工表、DEPT部门表、SALGRADE工资等级表2.7.2 分组查询示例-- 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;2.7.3 WHERE 和 HAVING 核心对比总结维度WHEREHAVING执行阶段分组之前分组、聚合之后筛选对象表的单行原始数据分组后的整组聚合结果聚合函数不允许使用允许使用SELECT 别名无法识别可以识别摆放位置FROM 之后、GROUP BY 之前GROUP BY 之后、ORDER BY 之前性能优先使用提前减重仅分组结果筛选时使用2.8 查询顺序小结完整真实执行顺序底层执行先后从上到下SELECT 字段/聚合函数 FROM 表 【WHERE 原始单行过滤】 GROUP BY 分组字段 【HAVING 分组聚合结果过滤】 ORDER BY 排序字段 LIMIT 起始偏移, 截取条数;FROM → WHERE → GROUP BY → 聚合运算 → SELECT → HAVING → ORDER BY → LIMITFROM确定数据表WHERE过滤原始单行数据GROUP BY数据分组聚合函数 (avg/sum/max 等)每组统计计算SELECT挑选展示字段、定义别名HAVING过滤分组之后的统计结果ORDER BY对最终数据集排序LIMIT排序完成后截取指定行数查表→筛单行→分组统计→筛分组结果→排序→截取行数。三、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 曹孟德;-- 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;四、Delete删除数据删除表中数据支持条件删除、全表删除还有高效的truncate截断表。4.1 条件删除-- 1. 删除孙悟空的考试成绩 delete from exam_result where name 孙悟空; -- 2. 删除英语60的同学成绩 delete from exam_result where english 60;4.2 全表删除delete-- 删除for_delete表所有数据自增id不重置 -- 准备测试表 CREATE TABLE for_delete ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); Query OK, 0 rows affected (0.16 sec) -- 插入测试数据 INSERT INTO for_delete (name) VALUES (A), (B), (C); Query OK, 3 rows affected (1.05 sec) Records: 3 Duplicates: 0 Warnings: 0 -- 查看测试数据 SELECT * FROM for_delete; ---------- | id | name | ---------- | 1 | A | | 2 | B | | 3 | C | ---------- 3 rows in set (0.00 sec) -- 删除整表数据 DELETE FROM for_delete; Query OK, 3 rows affected (0.00 sec) -- 查看删除结果 SELECT * FROM for_delete; Empty set (0.00 sec) -- 再插入一条数据自增 id 在原值上增长 INSERT INTO for_delete (name) VALUES (D); Query OK, 1 row affected (0.00 sec) -- 查看数据 SELECT * FROM for_delete; ---------- | id | name | ---------- | 4 | D | ---------- 1 row in set (0.00 sec) -- 查看表结构会有 AUTO_INCREMENTn 项 SHOW CREATE TABLE for_delete\G *************************** 1. row *************************** Table: for_delete Create Table: CREATE TABLE for_delete ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 1 row in set (0.00 sec)4.3 截断表truncate快速删除全表数据重置自增 id比 delete 更高效不记录事务-- 截断表自增id重置为1 -- 准备测试表 CREATE TABLE for_truncate ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); Query OK, 0 rows affected (0.16 sec) -- 插入测试数据 INSERT INTO for_truncate (name) VALUES (A), (B), (C); Query OK, 3 rows affected (1.05 sec) Records: 3 Duplicates: 0 Warnings: 0 -- 查看测试数据 SELECT * FROM for_truncate; ---------- | id | name | ---------- | 1 | A | | 2 | B | | 3 | C | ---------- 3 rows in set (0.00 sec) -- 截断整表数据注意影响行数是 0所以实际上没有对数据真正操作 TRUNCATE for_truncate; Query OK, 0 rows affected (0.10 sec) -- 查看删除结果 SELECT * FROM for_truncate; Empty set (0.00 sec) -- 再插入一条数据自增 id 在重新增长 INSERT INTO for_truncate (name) VALUES (D); Query OK, 1 row affected (0.00 sec) -- 查看数据 SELECT * FROM for_truncate; ---------- | id | name | ---------- | 1 | D | ---------- 1 row in set (0.00 sec) -- 查看表结构会有 AUTO_INCREMENT2 项 SHOW CREATE TABLE for_truncate\G *************************** 1. row *************************** Table: for_truncate Create Table: CREATE TABLE for_truncate ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8 1 row in set (0.00 sec)区别delete是逐行删除可回滚truncate直接重置表不可回滚效率更高。五、实战 OJ 真题DML 增删查改落地应用结合牛客网经典 OJ 题练习 DML 增删查改的实际应用真题 1批量插入数据批量插入数据_牛客题霸_牛客网insert into actor values (1, PENELOPE, GUINESS, 2006-02-15 12:34:33), (2, NICK, WAHLBERG, 2006-02-15 12:34:33);真题 2找出所有员工当前薪水salary情况找出所有员工当前薪水salary情况_牛客题霸_牛客网select distinct salary from salaries order by salary desc;真题 3查找入职员工时间排名倒数第三的员工所有信息查找入职员工时间升序排名的情况下的倒数第三的员工所有信息_牛客题霸_牛客网select * from employees where hire_date (select hire_date from employees group by hire_date order by hire_date desc limit 2,1) order by emp_no asc;真题 4查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t查找薪水记录超过15条的员工号emp_no以及其对应的记录次_牛客题霸_牛客网解法一select emp_no, count(emp_no) t from salaries group by emp_no having t 15 order by emp_no;解法二select emp_no, t from (select emp_no, count(emp_no) t from salaries group by emp_no order by emp_no) s where t 15;第二种写法在当前阶段有点超纲需要学习后面的复合查询中的子查询才好理解。但其实根据代码我们也大致清楚一二from后面跟的其实就是一张表相当于我们先将题目的salaries表进行优化成我们需要的样子但结果仍然是一张表所以我们就可以把括号内的内容接到from的后面再进行操作。六、SQL 避坑要点和总结避免全列查询仅查询需要的列减少数据传输和内存占用null 判断用 is null/is not null 和 ! 对 null 无效更新 / 删除必加where防止误操作全表数据分页查询必加order by避免分页结果混乱聚合函数忽略nullcount(qq) 不计入 qq 为 null 的记录truncate不可回滚删除全表数据优先考虑 delete需回滚或 truncate高效。总结 MySQL CRUD 是数据库开发的基础核心要点插入数据支持单行 / 多行、冲突处理、查询结果插入查询是核心灵活组合where、order by、limit、聚合函数、分组查询满足复杂需求更新 / 删除需精准控制范围避免全表操作遵循 SQL 执行顺序避开null判断、别名使用等常见坑。结束语本篇完整梳理了 MySQL DML 增、删、改、查的全部常用语法从数据表初始化、各类插入方案、多维度查询写法到数据更新、两种删除方式的区别最后结合 SQL 真实执行顺序整理了开发里常见的易错问题。CRUD 是 MySQL 最基础也最高频的核心内容复杂查询、多表关联、事务优化等进阶知识全都建立在熟练掌握基础 SQL 之上。日常写 SQL 尽量摒弃全表查询、无限制删除等危险写法善用条件过滤、分页、去重、分组聚合优化语句养成规范的书写习惯。 后续会继续分享 MySQL 多表查询、索引、事务等进阶内容大家可以结合文中案例动手实操在练习中巩固知识点。