【MySQL】数据操作语句（DML）

👦个人主页：@Weraphael
✍🏻作者简介：目前学习计网、mysql和算法
✈️专栏：MySQL学习
🐋 希望大家多多支持，咱一起进步！😁
如果文章对你有帮助的话
欢迎 评论💬 点赞👍🏻 收藏 📂 加关注✨

---

一、增

1.1 insert

【语法】

insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...);

• 插入数据时要确保插入的数据类型格式正确，符合表定义的约束条件（如主键、唯一性约束、非空约束等），以避免出现错误。

• 可以省略into。

• 可以省略(字段1, 字段2, ...)，如果将列名部分全部省略，默认是全列插入；当然也可以指定列名插入。

• 支持批量插入。

insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...), (值1, 值2, ...), ...;

【使用案例】

• 单行数据 + 全列插入

• 多行数据 + 指定列插入

1.2 插入或更新

由于主键或者唯一键对应的值已经存在，那么一定会导致插入失败

在这种情况下，可以选择性的进行同步更新操作。

语法如下：

insert into 表名 (字段1,...) values (值1,...) on duplicate key update 列1 = 值1, 列2 = 值2, 列3 = 值3, ...;

如果表中已经存在相同的唯一键或主键，则会更新已存在的记录；如果不存在，则会插入新记录。

【使用案例】

假设有一个名为 users的表，其中id列是主键，name列是唯一键，要插入一条数据，如果已存在相同的name，则更新对应的age，如果不存在，则插入新记录，可以这样写：

那么问题来了，我们怎么知道是执行了插入操作还是更新操作？

方法一：可以通过MySQL数据库返回的信息来判断

-- 0 row affected: 表中有冲突数据，更新的内容还是和表中原有的数据冲突
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新

方法二：通过MySQL函数获取受到影响的数据行数

select row_count();

1.3 replace

替换其实类似于insert into ... on duplicate key update，但是在某些情况下具有不同的行为。具体来说，替换会尝试插入一条新记录，如果表中已存在与新记录具有相同唯一键或主键的记录，则会删除已存在的记录，然后插入新记录，是一种比较暴力的做法。

基本语法如下：

replace into 表名 (列1, 列2, ...) values (值1, 值2, ...);

【使用案例】

假设有一个名为 users的表，其中id列是主键，name列是唯一键，要插入一条数据，如果已存在相同的主键或唯一键，则替换已存在的记录，可以这样写：

1.4 将查询结果插入到指定的表中

insert into 表名 [指定列] select ...

• [指定列]是可选项，指定要插入数据的目标表的列名。如果省略了列名，则默认假定插入的数据顺序与表中列的顺序相同。
• select ...: 指定要插入的数据来源。这个select查询语句将返回一组数据，这些数据将会被插入到目标表中。

举例来说，假设有一个名为target_table的表，具有列col1、col2、col3，并且表数据如下：

然后新建一个名为nums表结构（结果需要和target_table 一样）

最后将target_table中所有的数据插入到nums中

二、查

2.1 select

select是用于从数据库中检索数据的关键字，是SQL中最常用的语句之一。通过select语句，你可以从一个或多个表中检索出符合特定条件的数据，并根据需要对数据进行排序、分组等操作。

【语法】

select distinct 列1,列2,... from 表名 where 条件 order by 列名 limit 行数;

• distinct可以对表中重复的数据去重。

• 列1, 列2, ... 是要检索的列的列表；如果需要检索所有列，则可以使用通配符 *。

• where 子句用于指定检索数据的条件，可以根据需求添加，当然也可以省略。

• order by 子句用于对检索出的数据进行排序。

• limit 子句用于限制检索出的数据行数，可以指定从检索结果的开头返回的行数，也可以指定起始位置和行数。

接下来一一介绍select的一些重要功能。已知表的结构以及数据如下所示：

2.2 全列查询 + 指定列查询

全列查询是指使用通配符*来检索表中的所有列数据

select * from 表名; 

但往后不建议使用*进行全列查询。因为查询的列越多，意味着需要传输的数据量越大。同时可能会影响到索引的使用（遍历）。

因此我们可以使用指定列查询：指定列的顺序可以不用按定义表的顺序来

select 列1,列2, ... from 表名;

2.3 查询字段为表达式

• select可以计算表达式。
  

假设需要计算出chinese、math和english三科加起来的总成绩：

我们也可以通过 别名（as关键字） 来指定返回的字段名。这样可以方便地在查询结果中使用计算后的值，并且不会影响原始数据。注意：as也可以省略不写。

2.4 去重

使用distinct关键字。这会删除结果集中重复的行，只保留唯一的行。

select distinct 指定列 from 列名;

在以上表的数据中，数学成绩存在重复，因此可以使用去重查询

2.5 where子句（过滤筛选）

where其实相当于C/C++中的if

• 比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于。不能用此运算符来判断是否等于NULL
<=>	等于。专门用来判断是否等于NULL（建议用 is null）
!=	不等于。不能用此运算符来判断是否不等于null，建议使用is not null
<>	不等于。不能用此运算符来判断是否不等于null，建议使用is not null
between a0 and a1	范围匹配，[a0, a1]，如果a0 <= value <= a1，返回1(true)
in (option, ...)	如果是option中的任意一个，返回1(true)
is null	是null
is not null	不是null
like	模糊匹配。%表示任意多个（包括 0 个）任意字符；_表示任意一个字符

• 逻辑运算符

运算符	说明
and	相当于C/C++中的&&
or	相当于C/C++中的或
not	相当于C/C++中的!

大家看完这几道题，就能很好掌握where子句了。点击跳转

2.6 排序order by

在MySQL中，可以使用order by子句对查询结果进行排序。order by子句允许你按照一个或多个列的值对结果进行排序，可以按照升序（从小到大）或者降序（从大到小）的顺序进行排序。

select 列名 from 表名 order by 列1 [ASC/DESC], 列2 [ASC/DESC], ...;
# -- ASC 为升序（从小到大）
# -- DESC 为降序（从大到小）
# -- 默认为升序ASC

大家看完这几道题，就能很好掌握order by了。点击跳转

2.7 筛选分页结果limit

在MySQL中，当一个表的数据非常大的时候，按照以上方法会导致查询出来的效果不太明显，你可以使用limit子句来实现分页功能

建议：对未知表进行查询时，最好加一条 limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

• 写法一：读取表中的前n行

select * from 列表 limit n;

• 写法二：从某行row开始读取n行

select * from 列表 limit row, n;
# row - 开始位置（下标从0开始，0表示第一条记录）
# n - 步长。从开始位置开始，连续读取多少条记录

• 写法三：和写法二类似

select * from 列表 limit n, offset row;
# row - 起始位置
# n - 步长

三、改

3.1 update

在mysql中，可以使用update语句对查询到的结果进行列值更新（修改）。

【语法】

update 表名 set column1 = value1, column2 = value2, ... [where condition] [order by] [limit];

解释一下各个部分的含义：

• set： 用于指定要更新的列column和它们的新值value。
• column1, column2, ...： 要更新的列的名称。
• value1, value2, ...： 要将相应列设置为的新值。
• where： 可选项，用于指定筛选条件，只有满足条件的记录才会被更新。如果省略where子句，则所有记录都将被更新。
• order by： 可选项
• limit： 可选项

3.2 案例1：将孙悟空的数学成绩变更为 80 分

update exam_result set math=80 where name='孙悟空';

3:3 案例2：将曹孟德的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60,chinese=70 where name='曹孟德';

3.4 案例3：将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

update exam_result set math=math+30 order by chinese+math+english asc limit 3;

注意：mysql不支持math += 30这种语法

3.5 案例4：将所有同学的语文成绩更新为原来的 2 倍

select name,chinese from exam_result;

四、删

4.1 delete

delete语句用于从表中删除记录。它可以根据指定的条件删除一行或多行数据。

delete from 表名 [where condition] [order by] [limit];
# 如果省略where子句，表中的所有数据将会被删除
# 不省略，则仅在满足条件的记录才会被删除。
# 注意：delete只会删除数据，不会删除表结构

请注意，删除操作是不可逆的，因此在执行delete语句之前，请务必谨慎考虑并确保您真正希望删除这些记录。

4.2 截断表truncate

truncate和delete一样都是用于从表中删除数据的SQL命令

truncate [table] 表名

但它们之间有一些重要的区别：

• 只能对整表操作，不能像delete一样针对部分数据操作。
• truncate会重置auto_increment项，而delete不会。
• truncate在删除数据的时候，并不经过事务，也就是说它不会将操作记录保存在日志文件中，同时也使得truncate操作更加快速。

4.3 案例1：删除孙悟空同学的考试成绩

delete from exam_result where name='孙悟空';

4.4 案例2：删除总分是倒数第一的同学

delete from exam_result order by chinese+english+math asc limit 1;

五、补充：where子句案例

5.1 英语成绩<60的同学及英语成绩

select name 姓名, english 英语成绩 from exam_result where english<60;

5.2 语文成绩在 [80, 90] 分的同学及语文成绩

select name 姓名,chinese 语文成绩 from exam_result where chinese >= 80 and chinese <= 90;

但是以上代码不够优雅，对于这种范围匹配，还可以用between and

select name 姓名,chinese 语文成绩 from exam_result where chinese between 80 and 90;

5.3 数学成绩是58或者59或者98或者99分的同学及数学成绩

select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

但是以上代码不够优雅，在mysql中，可以使用 in操作符来检查某个表达式是否在一个列表中的多个值之中。当某个表达式的值与列表中的任何一个值匹配时，in操作符将返回1（true），否则返回0（false）。

select name,math from exam_result where math in (58,59,98,99);

5.4 姓孙的同学及孙某同学（模糊匹配）

这道题考察的是模糊匹配like

• 姓孙的同学：可能叫孙，可能叫孙某，可能叫孙某某 …，因此要用 %来表示任意字符。

select name from exam_result where name like '孙%';

• 孙某同学：则需要使用 _表示任意一个字符。

select name from exam_result where name like '孙_';

5.5 语文成绩好于英语成绩的同学

select name,chinese,english from exam_result where chinese>english;

5.6 总分在 200 分以下的同学（执行顺序问题）

select name 姓名,chinese+math+english 总分 from exam_result where chinese+math+english<200;

这里有一个问题：where子句后面的表达式chinese+math+english太长了，而前面我们将其重命名为总分，所以直接简写成总分<200即可。

mysql直接报错了error，这里其实涉及 mysql的执行顺序问题。

总结：别名不能用在where条件中使用

5.7 语文成绩 > 80 并且不姓孙的同学

select name,chinese from exam_result where chinese>80 and name not like '孙%';

5.8 筛选出孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

select name,chinese,math,english from exam_result where name like '孙_' or chinese+math+english>200 and chinese<math and english > 80;

六、补充：order by子句案例

6.1 同学及数学成绩，按数学成绩升序显示

select name,math from exam_result order by math asc;

6.2 同学及英语成绩，按英语成绩升序排序

select name,english from exam_result order by english asc;

需要注意的是：null视为比任何值都小。

6.3 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示

这将按照数学成绩降序排列，然后在数学成绩相同的情况下按照英语成绩升序排列，最后在数学和英语成绩都相同时按照语文成绩升序排列。

select name,math,english,chinese from exam_result order by math desc,english asc,chinese asc;

6.4 查询同学及总分，由高到低

• order by子句中可以使用表达式

select name,chinese+math+english from exam_result order by chinese+math+english desc;

• order by子句中可以使用列别名

select name,chinese+math+english 总分 from exam_result order by 总分 desc;

为什么这里就可以使用别名了，而where子句后面不能使用别名？同样可以一起再来分析执行顺序：

总之，order by子句是在整个过程中最后执行的语句。

6.5 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;