MySQL：基础增删查改

---

插入

• 基本插入语法：

insert [into] 表名 (列1, 列2 ...) values (值1, 值2 ...);

1. into可以省略
2. (列1, 列2 ...)与后面的(值1, 值2)一一对应
3. 如果插入时数据完全按照表从左往右的顺序，(列1, 列2 ...)可省略

示例：

create table students(
id int unsigned primary key auto_increment,
sn int not null unique comment '学号',
name varchar(20) not null,
tel varchar(20)
);

一次性指定所有值，那么省略(列1, 列2 ...)部分：

insert into students values (100, 20240001, '张三', NULL);

插入时只指定部分值，那么就需要通过(列1, 列2 ...)来指定：

insert into students (id, sn, name) values (101, 20240006, '小明');

也可以一次性插入多行，每一行的数据用(值1, 值2 ...)表示，不同行数据之间用逗号隔开：

insert into students (id, sn, name) values 
(105, 20240052, '小张'), 
(109, 20240096, '小李');

此处换行只是为了方便展示，换行对SQL语句的效果不会造成影响。

之前的插入结果如下：

---

插入冲突

在插入数据时，有可能因为主键和唯一键的存在，导致冲突而插入失败。此时可以利用插入替换，或者直接替换。

插入替换：

• 插入替换语法：

insert ... on duplicate key update 列1=值1, 列2=值2 ...;

其中insert ...表示一个完整的插入语句，该语句的作用是：insert语句如果冲突，则执行后面的列1=值1, 列2=值2 ...进行值替换。

示例：

insert into students (id, sn, name) values (101, 20240066, '昊天') 
on duplicate key update sn=20240066;

之前插入时，已经插入了(101, 20240006, '小明')，所以这个插入会发生主键冲突，导致插入失败。而on duplicate key update sn=20240066的作用就是，如果插入失败，说明主键已经存在，那么修改其学号为20240066。

最后该学生的sn从20240006变成了20240066：

插入替换的作用是：插入失败时，只替换部分指定的值，保留部分原先的值。

直接替换：

插入时，也可以直接替换，只需把insert改为replace即可：

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

这种直接替换，如果插入时发生冲突，会直接删除原先的行，重新插入当前行。

replace into students (id,sn,name) values (109, 20240100, '小杨');

执行以上语句时，由于已经有id=109的数据存在，会发生主键冲突，此时会直接删除原先的行，插入这一行：

整个id=109的数据，都变成刚刚的内容了，就好像执行了一个insert语句。

---

查询

示例表格：

create table exam_result (
id ind unsigned primary akey auto_increment,
name varchar(20) not null comment '同学姓名',
chinese float default 0.0 comment '语文成绩',
math float default 0.0 comment '数学成绩',
english float default 0.0 comment '英语成绩'
);

示例数据：

insert into 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),
('小朵', NULL, NULL, NULL);

• 基本查询语法

select 表达式1, 表达式2 ... from 表名;

此处的表达式含义非常广，它可以是列名，通配符*，或者基本字面量，计算式等。

1. 通配符*做表达式，输出所有的列

测试表中，有id, name, chinses, math, english列，通过*全部都查询出来了

2. 列名做表达式，输出指定的列

此处的表达式为name, chinese，所以只输出这两列。

3. 基本字面量与计算式做表达式，输出计算结果

此处的'name'与刚刚的name不同，此处的''name'是一个字符串，而不是列名，所以输出的是name字符串本身。而第三列用1024 + 2048做表达式，输出的就是计算结果。

在计算表达式时，如果指定了表，那么这个表有几行，表达式就计算多少次。如果表达式中没有依赖表中的列，而是单纯的计算，那么可以不指定表名：

这次的查询中，没有指定表名，也没有使用表的列，那么表达式默认只计算一次，输出一行。

计算的意义在于：列与列之间同样可以计算！

第二列中，通过chinese + english + math计算出了总分。

但是这样看表的话，chinese + english + math这个整体作为列名有点长了，此时可以使用as来对列重命名。

• 使用as对列重命名：

select 表达式1 as 新名1, 表达式2 as 新名2 ... from 表名;

as也可以省略：

select 表达式1 新名1, 表达式2 新名2 ... from 表名;

此处使用chinese + english + math as total将总分的整体重命名了，最后输出结果更加直观，当然也可以省略as，写为：chinese + english + math total。

---

distinct

直接查询math列：

此处出现了两个98，能否对查询结果去重？

此时就需要用到distinct关键字。

• 使用distinct对查询结果去重：

select distinct 表达式1, 表达式2 ... from 表名;

这次查询就只有一个98了。

---

where

观察一下之前的查询，会发现我们一直在对列做限制，而每次查询出来的行数目是一样的。如果说我们想对数据的值做限制，比如“数学成绩大于等于60分”，那么就需要where子句。

• 通过where子句来查询符合要求的数据：

select 表达式1, 表达式2 ... from 表名 where 表达式;

表达式中常用的运算符如下：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，任何值与NULL比较结果为NULL
<=>	等于，其他值与NULL比较为false，NULL与NULL比较为true
!=,<>	不等于
between x and y	范围匹配[x, y]闭区间，如果x <= value <= y返回true
in (option1, option2 ...)	如果是其中一个option返回true
is null	如果是NULL返回true
is not null	如果不是NULL返回true
like	模糊匹配，%表示多个字符，_表示一个字符
not like	反向模糊匹配，%表示多个字符，_表示一个字符
and	逻辑与
or	逻辑或
not	逻辑取反

如果你学过任意编程语言，上表格很好理解。另外的，以上所有操作都可以通过小括号()调整优先级。

1. 查询英语小于60分的同学：

select name, english from exam_result where english < 60;

2. 查询数学成绩为临界值(58, 59, 98, 99)的学生：

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

3. 查询数学成绩大于成绩的同学：

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

4. 查询总分200以上的同学：

select name, math + english + chinese as total from exam_result 
where math + english + chinese > 200;

前面已经将math + english + chinese重命名为了total，那么后面的where能不能写为：where total > 200？

查询时报错了，找不到total这个列，这是为什么，明明已经重命名了。这和SQL语句的执行顺序有关，回顾以下语句：

select name, math + english + chinese as total from exam_result where total > 200;

其执行顺序为：

1. 确定要操作的表，执行from exam_result
2. 确定要查询什么条件的数据，执行where total > 200
3. 最后确定查询哪些列，执行select name, math + english + chinese as total

你会发现，在还没有重命名的时候，where就先使用了total，所以会发生报错。

---

order by

如果想要对查询的结果进行排序，就需要order by子句。

• 通过order by子句对查询结果排序：

select ... from 表名 where ... order by 列1 asc|desc, 列2 asc|desc ... ;

asc表示升序，desc表示降序，如果不指定那么默认为asc升序。

1. 以数学成绩升序展示所有成绩：

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

此处NULL被放在了第一列，在SQL中认为NULL比所有值都小。

此处小明和小翠的数学都是98分，为什么小翠的顺序更靠后呢？在查询时，我们只指定了根据math排序，如果math相同，那么顺序是不确定的。

比如根据数学降序：

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

你会发现降序的结果不是升序结果的逆置，小明依然比小翠更靠前。如果想要更加确切的排序，那么就需要指定多个列的排序，比如：”如果数学成绩相同，语文成绩高的更前“，此时可以通过order by指定多个列：

order by math desc, chinese desc;

以上order by的执行效果为：先根据math进行逆序排序，如果math相同，再根据chinese逆序排序。如果chinese相同，那么结果就是不确定的了，此时你可以继续往后加排序条件，比如english desc。

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

此时结果就是确定的了，小翠和小明数学成绩相同，此时触发语文成绩判断，小翠的成绩比小明高，所以小翠靠前。

2. 以总分降序查询：

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

问题来了，排序时能不能直接用别名order by total呢？

这次居然可以用别名total了，和之前where的查询情况不一样！这依然和SQL语句的执行顺序有关。

order by是在整个表查询结束后，也就是select name, chinese + math + english as total这个整体执行完毕，得到了所有符合条件的结果，order by才能进行排序。所以执行到order by时，as total的重命名语句已经生效了，order by可以使用这个别名。

总结一下目前的语句执行顺序：

1. 确定要操作的表，执行from ...
2. 确定要查询什么条件的数据，执行where ...
3. 确定查询哪些列，执行select ...
4. 对查询结果进行排序，执行order by ...

---

limit

假设已经查询好数据，排序完成了，如果只需要取用前三条数据，这要如何完成？此时就需要limit，其用于展示查询到的数据的指定部分。

• 输出前x条数据：

select ... from 表名 where ... order by ... limit x;

• 输出下标为[x, y]的数据（下标从0开始）：

select ... from 表名 where ... order by ... limit x, y;

select ... from 表名 where ... order by ... limit x offset y;

直接查询：

查询前三行：

select * from exam_result limit 3;

查询第2到5行：

select * from exam_result limit 1, 4;

[2, 5]的下标是[1, 4]，注意不要搞错了。

limit的执行顺序非常靠后，在整个数据查询，排序完毕后，才执行limit。

---

删除

MySQL中，有两种删除数据的方法，分别是delete和truncate，两者功能略有差别。

delete

• 通过delete删除数据

delete from 表名 where ... order by ... limit ...;

1. 删除整张表的数据：

delete from exam_result;

执行select时查询不到任何数据，说明确实删除成功了。在show create table时，字段auto_increment=9，说明delete是不会清空自增长的。

在执行改代码将数据插入回去：

insert into 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),
('小朵', NULL, NULL, NULL);

此时id从9开始自增长了：

2. 删除小朵所在行：

delete from exam_result where name = '小朵';

truncate

相比于delete，truncate有以下特点：

1. truncate只能用于删除整个表
2. truncate不经过事务，速度更快
3. 会重置auto_increment

• 使用truncate清空表的所有数据：

truncate table 表名;

此处的table可以省略，直接truncate 表名。

truncate后，auto_increment字段的值就被重置了。

---

更新

• 更新表中数据

update 表名 set 列1 = 值1, 列2 = 值2 where ... order by ... limit ...

1. 将小红的英语成绩改为100：

update exam_result set english = 100 where name = '小红';

此处如果不加where子句，那么所有学生的英语成绩都会变成100。

2. 将数学成绩倒数的三名同学，数学成绩加上10分：

update exam_result set math = math + 10 order by math asc limit 3;

---