《sql进阶教程》豆知识摘录

《sql进阶教程》豆知识摘录

⚠️熟练使用sql之后再来看这本书。

本书分为2部分：

1. sql的一些高级用法，及其背后的设计原理
2. 关系型数据库的设计思想。

 

摘录：SQL的层级

为什么使用group by后，不能引用原表中除聚合键之外的列？

答案：这是Sql的一种逻辑设计，用来严格区分层级。层级是sql的本质之一。

 

group by

对表进行聚合查询后，只能在select子句中使用：

1. 通过group by子句指定的列
2. 聚合函数
3. 常量

这是因为，使用group by后，SQL的操作对象便由0阶的‘row’行， 变为了1阶的“行的集合”。所以，行的属性就不能用了。

 

where 和 having

• where子句处理0阶的行
• having子句用来处理1阶的"集合"

 

单元素集合也是集合

因此对 于以集合论为基础的 SQL 来说，当然也需要严格地区分元素和单元素集合。a ≠ {a}

 

一个原书中的例子：

>create table teams(members varchar(20), team varchar(10), age int);

mysql> select * from teams;
+---------+------+------+
| members | team | age  |
+---------+------+------+
| 大木    | A    |   28 |
| 王某    | A    |   19 |
| 赵名    | A    |   23 |
| 山田    | B    |   40 |
+---------+------+------+
4 rows in set (0.00 sec)

 

使用group by分组：

mysql> select team, avg(age) from teams group by team;
+------+----------+
| team | avg(age) |
+------+----------+
| A    |  23.3333 |
| B    |  40.0000 |
+------+----------+
2 rows in set (0.00 sec)

 

扩展一下，分组的表，除了显示组名， 平均年龄，还要加上一列：每组年龄最大的人的名字。

❌select team, avg(age), max(members)  from teams group by team; 

 

只会返回每组最后一行记录中的名字。

需要增加一个子查询：

mysql> select team, avg(age),
    -> (select max(members) from teams as t2
    -> where t2.team = t1.team
    -> and t2.age = max(t1.age)
    -> ) as oldest
    -> from teams as t1
    -> group by team;
+------+----------+--------+
| team | avg(age) | oldest |
+------+----------+--------+
| A    |  23.3333 | 大木   |
| B    |  40.0000 | 山田   |
+------+----------+--------+
2 rows in set (0.02 sec)

 

•  oldest是一个聚合函数即max(members)，满足sql严格的分阶的设计逻辑。
•  另一点，where子句使用了聚合函数max(t1.age), 这是因为外层使用了聚合，相当于where升阶了