目录

3. 更新

4. 删除

截断表

日志的作用

5. (实验) 插入查询结果

6. 聚合函数

7. 分组查询

---

接着上篇文章[MySQL#6] 表的CRUD (1) | Create | Retrieve(查) | where继续讲解~

3. 更新

语法：

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]

• set后面跟的是要重新设定的值，可以是多列。
• 一般在update的时候必须采用对应where子句进行条件筛选，如果没有的话会把这个表中指定的列全部都更新，这是不合理的。

对查询到的结果进行列值更新

1. 将孙悟空同学的数学成绩变更为 80 分

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

2. 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

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

3. 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

更新值为原值基础上变更。
注意据更新，不支持 math += 30 这种语法。

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

4. 将所有同学的语文成绩更新为原来的 2 倍

update exam_result set chinese=chinese*2;

注意：更新全表的语句慎用！

---

4. 删除

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

• 一般都是拿着条件删除 where
• 不加条件就是把整表的内容删除了，不过表结构还在。
• 删表结构drop

删除孙悟空同学的考试成绩

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

测试表

-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

我们看到这个id设置了自增，目前已经插入三条记录了，如果在插入一条记录这个id就是4了。

但我们现在不插，先删除

delete from for_delete;

我们看到表的内容已经没有了，但是查看创建表结构这个语句还在，并且自增长也还在。

当新插入一条记录时这个id是4，并且自增长已经变成下一个id值了

说明delete from清空表的方式不会将自增值置0。

清空表还有一种做法叫做截断表。在效果和delete一模一样，但是在细节和原理是有差别的。

截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

• 可以看到表结构还在，但是内容已经被清空了。但是这里值得注意的一点是，之前自增长是4，现在truncata清空表后自增从已经没有了。
• 然后新插一条记录，id变成1了。自增长变成2了
• 换句话说truncate会重置自增长。而delete并不会。

实际上，TRUNCATE 和 DELETE 还存在一些差异。TRUNCATE 操作是直接将表中的数据清空，并且这个操作不通过事务处理。而 DELETE 和其他 SQL 操作则会在执行时被包装进事务中，再由 MySQL 处理。

事务的影响

事务的使用与否会影响 MySQL 对操作信息的记录方式。MySQL 使用其自身的日志系统来记录数据操作的信息，主要包括以下几种日志：

• bin.log：存储经过优化的历史 SQL 语句。
• redo.log：用于在 MySQL 遭遇宕机时能够恢复数据。
• undo.log：用于存储回滚段信息。

日志的作用

• bin.log 记录的是历史 SQL 语句，并支持 MySQL 的主从同步机制。当一个数据库执行完某些操作后，可以通过 bin.log 将这些 SQL 同步到另一个数据库，从而使两个数据库的数据保持一致（主从同步）， 需要注意，默认情况下 bin.log 是关闭的。
• redo.log 在 MySQL 遇到故障时提供数据恢复功能。

持久化方式

持久化方式指的是为了能够在系统崩溃后快速恢复数据库数据的方法。

将数据以文件的形式写入磁盘，通常有两种方式

1. 记录历史sql语句
2. 记录数据本身

Truncate的特点

由于 TRUNCATE 不记录自己的操作到日志中，也不将其作为事务的一部分，因此它仅是简单地清空表中的数据，这样做的结果是 TRUNCATE 的执行速度较快。

• TRUNCATE 因为其非事务性及不记录日志的特点，在执行速度上有优势
• 但在数据恢复和一致性方面不如 DELETE。

---

5. (实验) 插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

我们要插就插，要删就删，要改就改，要查就查，实际我们也可以将select和insert组合。可以把数据从其他表里面筛选出来，然后插入到另一个表里面。

我们来实现如下一个小实验：

删除表中的重复记录，重复的数据只能有一份

我的做法是

1. 创建一个和原表一样结构的空表 no_duplicate_table ，
2. 从原始表中把去重之后的结果筛选出来插入到no_duplicate_table 这个表不就是不重复的吗，
3. 然后对duplicate_table重命名，no_duplicate_table改名字为duplicate_table。最终不就是完成了duplicate_table去重了吗。

create table no_duplicate_table like duplicate_table;

创建一个完全一样的表，使用like即可

全列插入就不用指定列了

insert into no_duplicate_table select distinct * from duplicate_table;

现在有两个表，一个duplicate_table，一个no_duplicate_table。然后对这两个表做一下重命名

下面两个sql语句可以写一起

rename table duplicate_table to old_duplicata_table;
rename table no_duplicate_table to duplicate_table;

当前我们就完成了去重

❓这里有个细节问题，为什么最后是通过rename方式进行的？

• 如果今天想把一个文件上传到linux上，比如这个文件是1G上传时间可能是10分钟，我们想把这个文件上传号之后放到一个目录下，我要求它是为原子性方式放入的。
• 所以一般我们不能直接把文件上传到对应的目录下，因为它上传的过程一种在写入一定不是原子，它太慢了。
• 所以我们把这个文件上传到临时目标下，全部上传之后然后再把文件move到那个目录下。
• 直接move这个动作实际上是原子的。其实对一个文件进行重命名也是同一个道理
• 所以我们最后通过rename方式，就是单纯的想等一切都就绪了，然后统一放入，更新，生效等！ 和冗长的其他动作相比，这个动作非常轻。

6. 聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和
AVG([DISTINCT] expr)	返回查询到的数据的平均值
MAX([DISTINCT] expr)	返回查询到的数据的最大值
MIN([DISTINCT] expr)	返回查询到的数据的最小值

• 这里是为select分组查询做准备的，聚合函数是以查出来的记录为单位帮我们进行数据聚合统计的。这种聚合统计方式通常是产出一个期望的结果，如个数、和、平均值、最大值、最小值。
• mysql中其实也是有函数的，这个函数可以被直接调用，我们可以在mysql直接使用聚合函数直接对一组结果进行聚合统计。
• 聚合函数（）里面可以是全列，可以是指定列。

示例：

统计班级共有多少同学

select count(*) from exam_result;

统计班级去重后数学成绩有多少

select count(distinct math) from exam_result;

统计数学成绩总分

select sum(math) from exam_result;

统计平均总分

select avg(math+chinese+english) from exam_result;

返回 > 70 分以上的数学最低分

select min(math) from exam_result where math>70;

这里为什么不能把name带上呢？

• 聚合统计的前提条件，一定是你先把我要聚合的数据先拿出来，然后才能聚合。
• 做聚合的时候必须保证你要显示的或者你要查询的数据列是被允许聚合的。
• 最低成绩只有一个，但name每个人都不一样没有办法做聚合。
• 例如三个人都是 73，那返回谁的名字呢

---

7. 分组查询

• 分组是对表中的数据进行分组，分完组之后，在对表中每一组进行相关聚合统计。
• 而分组的目的是为了进行分组之后，方便进行聚合统计。
• 如班级里有男生女生，我们相对男生女生成绩分别统计，所以可以对性别进行分组然后在进行成绩的聚合统计。
• 在select中使用group by 子句可以对指定列进行分组查询。
• 我们也可以把数据筛选之后再进行分组然后再聚合统计。

语法：

select column1, column2, .. from table group by column;

测试：

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

正是因为我们表有各种不同的信息，因此我们可以对表进行分组查询。

如何显示每个部门的平均工资和最高工资

• 注意说的是每个部门！我们未来是要分组的，凭什么分组，是由需求来决定的。
• 这里很明显说的是每个部门，公司员工信息全部都在员工表里，部门号不同员工一定隶属于不同部门，所以我们要显示每一个部门的平均和最高工资。
• 以前我们找公司最高工资和平均工资。我们是把这一张表当作一个大的组，这一张表在我看来就是一个整体的组，我要统计的就是全公司最高工资和平均工资。

但今天需求是按照组来统计的，根据的emo表中deptno列来进行分组。

select max(sal) 最高, avg(sal) 平均 from emp group by deptno;

• 首先再次强调group by不是你想用就能用，一定要结合需求。
• 其次group by的核心作用是让我们继续分组聚合统计的，所以你要把需求分清楚然后和group by功能对上，才能用group by。
• 我们在进行分组统计的时候，group by后面指定列名，指明我们要分组的列是谁，但实际分组是用该列的不同的行数据是否相同进行分组的！
• 当我们分完组之后，那分组的条件（deptno），组内一定是相同的，因此可以被聚合压缩。

理解：

• 分组，不就是把一组按照条件拆分多个组，进行各组内的统计。
• 分组（" 分表 "），不就是把一张表按照条件在逻辑上拆成多个子表，然后分别对各自的子表进行聚合统计。
• 拆成各个组不就是在逻辑上拆成各个表，然后分别在每个表里做聚合统计，以前我们做的聚合统计是在一张表里进行的
• 换句话说，只要掌握在一张表里查询，在查询之前先做好分组，后面的工作和思路理解上和之前单表上的聚合统计是一模一样的。

显示每个部门的每种岗位的平均工资和最低工资

需求是每个部门的每种岗位，注定了一定是要分组的，而且还不是分一组，我们首先按部门来分组，然后在按岗位来分组。

在我看来每一张组都是独立的组，在组内做聚合也没什么问题。无非就是比以前多做一份工作，先分组，再聚合。 一张整表内做聚合和多个子表内做聚合，总之还是在一张表内聚合。

select avg(sal) 平均, min(sal) 最低 from emp group by deptno, job;

在想看看是哪个部门的那个岗位的谁的平均工资和最高工资

• 故意加了一个ename，然后就报错了，说的是ename没有在分组条件内出现不属于分组条件，所以无法进行压缩和聚合。
• 刚才我们说了分组内一定是某个相同的。走到一个组内一定是部门号相同工作总类相同。
• 所以相同的列可以进行压缩聚合。现在出来一个名字，名字一定是人人都不同的，没有办法进行聚合，所以直接报错。

一般大原则，只有在group by中出现的具体的列名称，才可以在select后面具体出现

除此之外另一类可以直接出现的就是聚合函数，其余不能在select后面直接跟不是具体分组条件的列。

显示平均工资低于 2000 的部门和它的平均工资

需求是平均工资低于2000的部门，要的是部门，和它的平均工资是多少。

1. 我们统计出来每一个部门的平均工资(结果先聚合出来)
2. 在进行判断(对聚合的结果，进行判断)

我要的不是所有部门，我要的是平均工资低于2000的部门，此时要对聚合的结果拿出来判断。我们再说一个语法结构

• having经常和group by搭配使用
• 作用是对聚合后的统计数据，进行条件筛选
• 作用有些像where。

select deptno, avg(sal) 平均 from emp group by deptno having 平均 < 2000;

❓ having 和 where 区别理解？执行顺序？构建对 “结果” 的理解。

首先having和where都是够进行条件筛选，但是它们两个是完全不同的筛选。

• where是对具体的任意列进行条件筛选
• having对分组聚合之后的结果进行条件筛选。

它们俩的应用场景是完全不同的。换句话说where是先对原始表进行条件过滤，对过滤后的结果在进行分组。

执行顺序：1. 从表拿数据，2. 数据筛选条件，3. 数据分组，4. 分组结果聚合统计，5. 结果筛选。

构建对 “结果” 的理解（小小的建模）

• 不要单纯的认为，只有磁盘上表结构导入的mysql，真实存在的表，才叫做表。
• 中间筛选出来的，包括最终结果，在我看来，全部都是逻辑的表。
• 在我看来"MySQL一切皆表"，所以未来只要我们能够处理好单表的CURD，所有的sql场景，我们全部都能用统一的方式进行。