mysql 分组后合并某些组 mysql分组两次

目录

• 环境
• 分组 GROUP BY
• 分组统计 WITH ROLLUP
• 过滤分组 HAVING
• 连接 JOIN
• 限量 LIMIT
• NULL值处理
• 更新表定义 ALTER
• 事务开始 BIGIN / START
• 事务回滚 ROLLBACK
• 事务提交 COMMIT
• 保存点 SAVEPOINT
• 自动提交
• 索引 INDEX 和 EXPLAIN
• 查看执行时间
• 参考

环境

cmd命令行

MySQL 8.0.25

分组 GROUP BY

书接上回，GRUOP BY可以按照你指定的条目进行分组，还记得我们的my_table2：

如果我想看看在my_table2当中不同的诗人出现多少次咋办呢？（或者说，诗人的作品在表my_table2中到底有多少？）此时我们可以用在SELECT子句中使用GROUP BY：

SELECT my_author, COUNT(*) FROM my_table2 GROUP BY my_author;

可以看到在表中，李白和苏轼各自都有2个作品，其他人只有1个。

分组统计 WITH ROLLUP

WITH ROLLUP能够让我们在分组的基础上再进行统计（SUM,AVG,COUNT）。

比如使用：

SELECT my_author, SUM(my_id) AS id_count FROM my_table2 GROUP BY my_author WITH ROLLUP;

SUM(my_id) AS id_count就是把my_id加起来作为id_count这一个新列，我们有：

虽然没有意义，我们把相同my_author的id加起来了，并且还求了总和，NULL 表示所有作者id的和。

不过还是太丑了，我们可以用coalesce：

SELECT coalesce(my_author, '总id数'), SUM(my_id) AS id_count FROM my_table2 
GROUP BY my_author WITH ROLLUP;

过滤分组 HAVING

HAVING可以在GROUP BY分组的时候添加条件，过滤分组，跟WHERE很像，不同的是HAVING是在分组的时候用的。

SELECT my_author, SUM(my_id) AS id_count FROM my_table2 
GROUP BY my_author HAVING SUM(my_id) <= 10;

连接 JOIN

还记得我们有my_table2和my_table3：

JOIN能够在SELECT查询字句下连接不同的表，并且将查询结果横向连接（而不是纵向连接）。

我们试试用JOIN把my_table2和my_table3中拥有相同id的作品和作者横向拼接在一起：

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author
FROM my_table2 a JOIN my_table3 b ON a.my_id = b.my_id;

这里我们把my_table2指代为a，my_table3指代为b，依次打印a.my_id, a.my_title, a.my_author, b.my_title, b.my_author这些列。条件是取a(my_table2)和b(my_table3)中id相等的那些。

这里的ON和WHERE是一样的，所以换一下也一样：

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author
FROM my_table2 a JOIN my_table3 b WHERE a.my_id = b.my_id;

JOIN和INNER JOIN一样

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author
FROM my_table2 a INNER JOIN my_table3 b WHERE a.my_id = b.my_id;

你甚至可以不用JOIN，用一个逗号,替代:

SELECT a.my_id, a.my_title, a.my_author, b.my_title, b.my_author 
FROM my_table2 a, my_table3 b WHERE a.my_id = b.my_id;

到这里你甚至可以去刷题了，非常简单：牛客SQL4 查找所有已经分配部门的员工

我的解答：【牛客SQL】SQL4 查找所有已经分配部门的员工

限量 LIMIT

LIMIT可以给SELECT的查询数据限量，比如我只想要my_table2的第一个数据：

SELECT * FROM my_table2 LIMIT 1;

或者我想要第1个到第3个数据（LIMIT后面跟两个数时，第一个数表示要跳过的数量，后一个数表示要取的数量）：

SELECT * FROM my_table2 LIMIT 0,3;

NULL值处理

我们的my_table2中的submission_date没有定义NOT NULL，那就往这个位置插入NULL：

INSERT INTO my_table2
(my_title, my_author, submission_date)
VALUES
("定风波", "苏轼", NULL);

查找数据表列是否是 NULL，可以使用 IS NULL 和 IS NOT NULL：

SELECT * FROM my_table2 WHERE submission_date IS NULL;

顺利找到刚加入的行。

还可以找非NULL的行：

SELECT * FROM my_table2 WHERE submission_date IS NOT NULL;

更新表定义 ALTER

ALTER TABLE命令可以用于更新表定义

还记得my_table2长这样：

我们可以用ALTER TABLE把submission_date列去掉

ALTER TABLE my_table2 DROP submission_date;

看看my_table2，submission_date果然被删了。

那再把submission_date加回来：

ALTER TABLE my_table2 ADD submission_date DATE;

我们可以设置默认值：

ALTER TABLE my_table2 ALTER submission_date SET DEFAULT '2021-07-10';

这里是看不到的，ALTER只是改变了定义：

DESC可以查看表的定义：

DESC my_table2;

可以看到默认值变为了'2021-07-10'。我们再把它删掉吧，可以DROP DEFAULT

ALTER TABLE my_table2 ALTER submission_date DROP DEFAULT;

你还可以改engine，但我不想改就不演示了：

ALTER TABLE my_table2 ENGINE = MYISAM;

你还可以改类型：

ALTER TABLE my_table2 MODIFY submission_date YEAR;

类型再改回来：

ALTER TABLE my_table2 CHANGE submission_date submission_date DATE;

你还可以改名字：

ALTER TABLE my_table2 RENAME TO my_table4;

然后我又改了回来

事务开始 BIGIN / START

事务的深度内涵在这就不说了，这里可以简单理解为对数据的一系列操作封装打包。事务中的操作要么一起执行，执行的时候绝对不会被打断，要么就都不执行（原子性）。

事务开始可以使用BEGIN或者START TRANSACTION（都一样）

我们启动一个事务：

事务回滚 ROLLBACK

事务回滚ROLLBACK或者ROLLBACK WORK（两个都一样）可以让我们回退到上一条语句中，

我们刚刚启动了事务，现在来修改一下数据：

UPDATE my_table2 SET my_title = "江城子·密州出猎" WHERE my_id = 6;

我们把第六首诗从"念奴娇·赤壁怀古"改成了"江城子·密州出猎"，查看一下：

改好了~

回滚可以让我们回到执行UPDATE命令之前，输入ROLLBACK：

可以看到我们的第六首诗又变成了"念奴娇·赤壁怀古"。

注意：回滚ROLLBACK只能回滚INSERT，UPDATE和DELETE 语句。我们刚刚调用回滚之后，SELECT操作不会被回滚，而是被自动忽略，从而寻找到比SELECT更早之前的UPDATE来回滚。（回滚SELECT也没有意义啊。）

我又又反悔了，我还是想把第六首诗改成"江城子"：

UPDATE my_table2 SET my_title = "江城子·密州出猎" WHERE my_id = 6;

事务提交 COMMIT

使用COMMIT或者COMMIT WORK（两个是一样的）可以提交事务，现在来提交一下：

可以看到，我们第一次的修改虽然被我们回滚撤掉了。但是第二次修改还是起作用了。

提交事务之后，从BEGIN或者START TRANSACTION事务开始，一直到COMMIT事务提交，期间的操作都会被真的执行（除非发生报错），对数据库造成永久性的修改。

保存点 SAVEPOINT

保存点SAVEPOINT就像游戏存档一样，能够在事务中标记一个保存点，如果我们对保存点之后的修改不满意，可以直接ROLLBACK回退到之前的保存点。

BEGIN再启动一个事务：

继续针对my_table2改一改：

但在此之前我们记录一个保存点SAVEPOINT save1;，命名为save1：

然后我们插入一个新人物：

INSERT INTO my_table2
(my_title, my_author, submission_date)
VALUES
("夜雨寄北”, "李商隐", NOW());

再添一首：

INSERT INTO my_table2
(my_title, my_author, submission_date)
VALUES
("霜月”, "李商隐", NOW());

再存一个保存点SAVEPOINT save2：

李商隐的诗太难，我又不想看到他了，此时可以回退到刚刚的保存点save1：

ROLLBACK TO save1;

是的，我们又回到了没有李商隐的时候。

但是后来我又又改主意了，我还是觉得让李商隐回来吧，所以我就ROLLBACK TO save2：

是的，我们回不去了。

因为回到第一个保存点save1的时候，根据时间线，save2还没出现，那么它就真的没出现！所以保存点的回溯会使得回溯时间之后的所有保存点自动消失。

保存点在COMMIT提交事务的时候自动消失。当然也可以通过RELEASE SAVEPOINT来主动释放保存点。

先把事务提交一下吧。

自动提交

如果不用事务，我们输入的所有指令都是自动默认提交COMMIT的。

不过我们可以把自动提交关掉（直到再重新打开自动提交为止）：

SET autocommit=0;

SET autocommit=0/1;是针对连接，而不是针对服务器的。

索引 INDEX 和 EXPLAIN

索引可以这样创建：

CREATE INDEX index_id ON my_table (my_id);

我们也可以通过ALTER TABLE 数据表名 ADD INDEX 索引名 (列名)添加普通索引

ALTER TABLE my_table2 
ADD INDEX index_id (my_id);

添加全文索引：

ALTER TABLE my_table2 
ADD FULLTEXT index_title (my_title);

可以SHOW INDEX FROM my_table2;查看索引情况：

可以看到主键本身就是一种索引，即主键索引。

索引的查看可以通过EXPLAIN：

EXPLAIN SELECT * FROM my_table2 WHERE my_id =1;

或者竖着打印

EXPLAIN SELECT * FROM my_table2 WHERE my_id =1 \G;

另外删除索引可以（我就不演示了）：

DROP INDEX index_id2 ON my_table2;

EXPLAIN包含的信息：
table：显示这一行的数据是关于哪张表的

1. type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和all
2. possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句
3. key： 实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引
4. key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
5. ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
6. rows：mysql认为必须检查的用来返回请求数据的行数
7. extra：关于mysql如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是using temporary和using filesort，意思mysql根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义:

1. distinct:一旦mysql找到了与行相联合匹配的行，就不再搜索了
2. not exists: mysql优化了left join，一旦它找到了匹配left join标准的行，就不再搜索了
3. range checked for each record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
4. using filesort: 看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
5. using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
6. using temporary 看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上
7. where used 使用了where从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型all或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
8. system 表只有一行：system表。这是const连接类型的特殊情况
9. const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为mysql先读这个值然后把它当做常数来对待
10. eq_ref:在连接中，mysql在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
11. ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
12. range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
13. index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比all更好，因为索引一般小于表数据）
14. all:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

这里感谢大佬的文章。

唯一索引创建：

CREATE UNIQUE INDEX index_id ON my_table(my_id);

查看执行时间

需要用到SHOW PROFILES;，提起是看profile是否开启，数据库默认是不开启的。变量profiling是用户变量，每次都得重新启用：

很简单，置1就行：

SET PROFILING = 1;

SHOW VARIABLES LIKE "%PRO%";

可以看到已经开启。

我们调用SHOW PROFILES;

可以查看耗时。