MySQL CRUD 核心指南：查询、插入、更新、删除全实战

🔥草莓熊Lotso：个人主页
❄️个人专栏: 《C++知识分享》《Linux 入门到实践：零基础也能懂》
✨生活是默默的坚持，毅力是永久的享受！

🎬 博主简介：

文章目录

• 前言：
• 一. 基础准备：创建测试表与测试数据
  • 1.1 学生表（students）
  • 1.2 考试成绩表（exam_result）
• 二. Create（插入数据）
  • 2.1 单行全列插入
  • 2.2 多行指定列插入
  • 2.3 插入冲突处理（on duplicate key update）
  • 2.4 替换插入（replace into）
  • 2.5 插入查询结果
• 三. Retrieve（查询数据）
  • 3.1 基础查询
    • 3.1.1 全列查询（不推荐）
    • 3.1.2 指定列查询
    • 3.1.3 查询表达式
    • 3.1.4 结果别名（as 可省略）
    • 3.1.5 结果去重（distinct）
  • 3.2 条件查询（where）
    • 3.2.1 比较运算符
    • 3.2.2 逻辑运算符
    • 3.2.3 条件查询示例
  • 3.3 结果排序（order by）
  • 3.4 分页查询（limit）
  • 3.5 聚合查询（聚合函数）
  • 3.6 分组查询（group by + having）
• 四. Update（更新数据）
• 五. Delete（删除数据）
  • 5.1 条件删除
  • 5.2 全表删除（delete）
  • 5.3 截断表（truncate）
• 六. SQL 执行顺序与避坑指南
  • 6.1 SQL 关键字执行顺序
  • 6.2 避坑要点和总结
• 结语：

前言：

在 MySQL 日常开发中，CRUD（create/retrieve/update/delete）是最核心的高频操作。掌握规范的 CRUD 语法、灵活的查询技巧和避坑要点，能大幅提升开发效率和 SQL 可读性。本文基于实战场景，全面拆解 MySQL 的增删改查操作，所有 SQL 语句均采用小写形式，贴合实际开发规范，同时涵盖聚合查询、分组统计等进阶内容。

一. 基础准备：创建测试表与测试数据

为了让所有示例更直观，先创建两张测试表并插入测试数据，后续操作均基于这两张表：

• 语法：

INSERT[INTO] table_name [(column[,column]...)]VALUES(value_list)[,(value_list)]... value_list: value,[,value]...

1.1 学生表（students）

createtable students ( id intunsignedprimarykeyauto_increment, sn intnotnulluniquecomment'学号', name varchar(20)notnull, qq varchar(20));-- 插入测试数据insertinto students values(100,10000,'唐三藏',null),(101,10001,'孙悟空','11111'),(102,20001,'曹孟德',null),(103,20002,'孙仲谋',null);

1.2 考试成绩表（exam_result）

createtable exam_result ( id intunsignedprimarykeyauto_increment, name varchar(20)notnullcomment'同学姓名', chinese floatdefault0.0comment'语文成绩', math floatdefault0.0comment'数学成绩', english floatdefault0.0comment'英语成绩');-- 插入测试数据insertinto 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);

二. Create（插入数据）

插入数据核心是insert语句，支持单行 / 多行插入、指定列插入、冲突处理等场景。

2.1 单行全列插入

插入数据需与表结构的列数和顺序完全一致（自增主键可省略，自动生成）：

-- 全列插入（指定id）insertinto students values(104,20003,'鲁智深','22222');-- 省略自增主键（自动生成id）insertinto students (sn, name, qq)values(20004,'林冲','33333');

2.2 多行指定列插入

一次插入多条数据，仅指定需要赋值的列，未指定列使用默认值或null：

insertinto students (sn, name)values(20005,'武松'),(20006,'杨志');

2.3 插入冲突处理（on duplicate key update）

当主键或唯一键冲突时，不报错而是执行更新操作：

-- 主键冲突（id=100已存在），执行更新insertinto students (id, sn, name)values(100,10010,'唐大师')onduplicatekeyupdate sn =10010, name ='唐大师';// 同步更新语法-- 唯一键冲突（sn=20001已存在），执行更新insertinto students (sn, name)values(20001,'曹阿瞒')onduplicatekeyupdate name ='曹阿瞒';

2.4 替换插入（replace into）

主键或唯一键冲突时，删除原记录后重新插入：

-- sn=20002已存在，删除原记录后插入新数据replaceinto students (sn, name)values(20002,'孙伯符');

2.5 插入查询结果

将一张表的查询结果插入另一张表（常用于数据迁移、去重）：

-- 创建空表（结构与students一致）createtable students_copy like students;-- 将students的去重数据插入新表insertinto students_copy selectdistinct*from students;

三. Retrieve（查询数据）

查询是 CRUD 中最复杂的操作，支持全列查询、条件查询、排序、分页、聚合等功能，核心语法：

select[distinct] {*|column[,column]...} from table_name [where...][orderbycolumn[asc|desc],...]limit...

3.1 基础查询

3.1.1 全列查询（不推荐）

-- 全列查询（数据量大时性能差，不建议在生产环境使用）-- 1. 查询的列越多，意味着需要传输的数据量越大-- 2. 可能会影响到索引的使用。(索引待后面我们再进行理解)select*from exam_result;

3.1.2 指定列查询

按需查询列，顺序可与表结构不一致：

-- 查询姓名、语文、数学成绩select name, chinese, math from exam_result;

3.1.3 查询表达式

支持常量、单字段运算、多字段运算：

-- 常量表达式select id, name,10from exam_result;-- 单字段运算（英语成绩+10）select id, name, english +10from exam_result;-- 多字段运算（总分）select id, name, chinese + math + english from exam_result;

3.1.4 结果别名（as 可省略）

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

select id, name, chinese + math + english as 总分 from exam_result;

3.1.5 结果去重（distinct）

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

-- 去重查询数学成绩 distinctselectdistinct math from exam_result;

3.2 条件查询（where）

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

3.2.1 比较运算符

以下是您提供的运算符说明表格：

3.2.2 逻辑运算符

3.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 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;// 不能直接用总分 < 20,因为执行这里的时候还没执行前面的部分-- 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 isnotnull;-- 10. null安全比较（<=>）select name, qq from students where qq <=>null;

3.3 结果排序（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;

3.4 分页查询（limit）

限制查询结果数量，避免数据量过大导致性能问题，起始下标从 0 开始。

-- 语法1：limit 条数（取前n条）select*from exam_result orderby id limit3;-- 语法2：limit 起始下标, 条数（从s(下标从0开始)开始取n条）select*from exam_result orderby id limit3,3;-- 语法3：limit 条数 offset 起始下标（推荐，更清晰）select*from exam_result orderby id limit3offset6;-- 分页示例：每页3条，第1-3页select*from exam_result orderby id limit3offset0;-- 第1页select*from exam_result orderby id limit3offset3;-- 第2页select*from exam_result orderby id limit3offset6;-- 第3页

3.5 聚合查询（聚合函数）

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

聚合查询示例：

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

3.6 分组查询（group by + having）

group by按指定列分组，having筛选分组结果（类似where，但作用于分组）。

-- 准备雇员表（经典测试表）createtable emp ( empno intprimarykey, ename varchar(20), job varchar(20), deptno int, sal float);insertinto 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 groupby deptno;-- 2. 按部门和岗位分组，统计平均工资和最低工资select deptno, job,avg(sal)as 平均工资,min(sal)as 最低工资 from emp groupby deptno, job;-- 3. 筛选平均工资低于2000的部门（having筛选分组结果）select deptno,avg(sal)as 平均工资 from emp groupby deptno havingavg(sal)<2000;

四. Update（更新数据）

修改表中已有数据，支持单字段、多字段更新，结合where、order by、limit精准控制更新范围。

• 语法：

UPDATE table_name SETcolumn= expr [,column= expr ...][WHERE...][ORDERBY...][LIMIT...]

-- 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 limit3;-- 5. 条件更新（英语<60的同学英语+10）update exam_result set english = english +10where english <60;

• 警告：无where子句会更新全表数据，生产环境需谨慎

五. Delete（删除数据）

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

5.1 条件删除

-- 1. 删除孙悟空的考试成绩deletefrom exam_result where name ='孙悟空';-- 2. 删除英语<60的同学成绩deletefrom exam_result where english <60;

5.2 全表删除（delete）

-- 删除for_delete表所有数据（自增id不重置）createtable for_delete ( id intprimarykeyauto_increment, name varchar(20));insertinto for_delete (name)values('a'),('b'),('c');deletefrom for_delete;-- 插入新数据，自增id从4开始insertinto for_delete (name)values('d');select*from for_delete;-- id=4

5.3 截断表（truncate）

快速删除全表数据，重置自增 id，比delete更高效（不记录事务）：

-- 截断表（自增id重置为1）createtable for_truncate ( id intprimarykeyauto_increment, name varchar(20));insertinto for_truncate (name)values('a'),('b'),('c');truncate for_truncate;-- 插入新数据，自增id从1开始insertinto for_truncate (name)values('d');select*from for_truncate;-- id=1

• 区别：delete是逐行删除（可回滚），truncate直接重置表（不可回滚），效率更高。

六. SQL 执行顺序与避坑指南

6.1 SQL 关键字执行顺序

from → on → join → where → groupby → with → having → select → distinct → orderby → limit

• 别名不能在where中使用（select在where之后执行）；
• having用于筛选分组结果（group by之后执行），where用于筛选行数据（group by之前执行）。

6.2 避坑要点和总结

• 避免全列查询：仅查询需要的列，减少数据传输和内存占用；
• 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判断、别名使用等常见坑。

结语：

🍓 我是草莓熊 Lotso！若这篇技术干货帮你打通了学习中的卡点： 👀 【关注】跟我一起深耕技术领域，从基础到进阶，见证每一次成长 ❤️ 【点赞】让优质内容被更多人看见，让知识传递更有力量 ⭐ 【收藏】把核心知识点、实战技巧存好，需要时直接查、随时用 💬 【评论】分享你的经验或疑问（比如曾踩过的技术坑？），一起交流避坑 🗳️ 【投票】用你的选择助力社区内容方向，告诉大家哪个技术点最该重点拆解 技术之路难免有困惑，但同行的人会让前进更有方向～愿我们都能在自己专注的领域里，一步步靠近心中的技术目标！ 

结语：掌握这些操作后，可应对大部分业务场景。如果需要针对复杂查询（如多表联查）、事务、索引优化等进阶内容，欢迎在评论区留言交流！创作不易，觉得有帮助的话，欢迎点赞、收藏、关注三连～ 后续会持续更新 MySQL 进阶技巧，带你从入门到精通数据库开发。

✨把这些内容吃透超牛的！放松下吧✨ʕ˘ᴥ˘ʔづきらど