《SQL基础教程》笔记（2）

《SQL基础教程》读书笔记

3.对表进行分组

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

使用聚合函数和GROUP BY子句时需要注意以下4点：

只能写在SELECT子句中GROUP BY子句中不能使用SELECT子句中列的别名GROUP BY子句的聚合结果是无序的WHERE子句中不能使用聚合函数 -- 按照商品种类统计数据行数 SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui; shobin_bunrui count 衣服 2 办公用品 2 厨房用具 4

不使用GROUP BY子句时，是将表中的所有行数据作为一组来对待的。而使用GROUP BY子句时，会将表中的数据分为多个组进行处理。

在GROUP BY子句中指出的列称为聚合键或分组列。

聚合键中包含NULL的情况：

SELECT shiire_tanka, COUNT(*) FROM Shohin GROUB BY shiire_tanka; shiire_tanka count 2 320 1 500 1 5000 1 2800 2 790 1

当聚合键这包含NULL时，也会将NULL作为一组特定的数据。

使用WHERE子句时GROUP BY的执行结果：

会先根据WHERE子句指定的条件进行过滤，然后再进行聚合处理。

-- 同时使用WHERE子句和GROUP BY子句 SELECT shiire_tanka, COUNT(*) FROM Shohin WHERE shohin_bunrui = '衣服' GROUP BY shiire_tanka; shiire_tanka count 500 1 2800 1

与聚合函数和GROUP BY子句有关的常见错误：

1.常见错误—在SELECT子句书写了多余的列

SELECT shohin_mei, shiire_tanka, COUNT(*) FROM Shohin GROUP BY shiire_tanka; -- 执行结果 ERROR: 列'shohin, shohin_mei' 必须包含在GROUP BY子句之中，或者必须在聚合函数内使用

列名shohin_mei并没有包含在GROUP BY子句当中。因此，该列名也不能书写在SELECT子句之中。也就是使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列。

但是可以这样写：

SELECT shohin_bunrui, AVG(hanbai_tanka) FROM GROUP BY shohin_bunrui; shobin_bunrui avg 衣服 2500 办公用品 300 厨房用具 2795

2.常见错误2—在GROUP BY子句中写了列的别名

SELECT shohin_bunrui AS sb, COUNT(*) FROM Shohin GROUB BY sb;

SELECT子句中的项目可以通过AS关键字来指定别名，但是，在GROUP BY子句中是不能使用别名的。

3.常见错误3—GROUP BY子句的结果能排序吗？

GROUP BY子句的结果通常都包含多行，有时可能还会是成百上千行。那么，这些结果究竟是按照什么顺序排列的呢？—随机的。

4.常见错误4—在WHERE子句中使用聚合函数

如果我们想要取出恰好包含2行数据的组该怎么办呢？

SELECT shohin_bunrui, COUNT(*) FROM Shohin WHERE COUNT(*) = 2 GROUP BY shohin_bunrui;

实际上，只有SELECT和HAVING子句以及ORDER BY子句中能够使用聚合函数，并且，HAVING子句可以非常方便的实现上述要求。

为聚合结果指定条件：

使用COUNT函数等对表中数据进行聚合操作时，为其指定条件的不是WHERE子句，而需要使用HAVING子句。聚合函数可以在SELECT子句、HAVING子句和ORDER BY子句中使用。HAVING子句要写在GROUP BY子句之后。WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件。 -- 取出 “包含数据的行数为2行” SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui HAVING COUNT(*) = 2 -- 取出 “销售单价的平均值大于等于2500日元” SELECT shohin_bunrui, AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) >= 2500;

HAVING子句的构成要素：

常数聚合函数GROUP BY子句中指定的列名（即聚合键） -- HAVING子句的不正确使用方法 SELECT shohin_bunrui, COUNT(*) FROM Shobin GROUP BY shohin_bunrui HAVING shobin_mei = '圆珠笔'

shobin_mei列并不包含在GROUP BY子句之中，因此不允许写在HAVING子句里。

可以把这种情况想象为使用GROUP BY子句时的SELECT子句。聚合之后得到的表中并不存在shobin_mei这个列，SQL当然也无法为表中不存在的列设定条件啦。

相对于HAVING子句，更适合写在WHERE子句中的条件：

有些条件即可以写在HAVING子句当中，也可以写在WHERE子句当中。

SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shobin_bunrui HAVING shobin_bunrui = '衣服'; SELECT shobin_bunrui, COUNT(*) FROM Shohin WHERE shobin_bunrui = '衣服' GROUP BY shobin_bunrui;

通常情况下，将条件写在WHERE子句中要比写在HAVING子句的处理速度要快，返回结果所需时间更短。

聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。

4.对查询结果进行排序

使用ORDER BY子句对查询结果进行排序。在ORDER BY子句中列名的后面使用关键字ASC可以进行升序排序，使用DESC关键字可以进行降序排序。ORDER BY子句中可以指定多个排序键。排序键中包含NULL时，会在开头或末尾进行汇总。ORDER BY子句中可以使用SELECT子句中定义的列的别名。ORDER BY子句中可以使用SELECT子句中未出现的列或者聚合函数(*)。ORDER BY子句中不能使用列的编号。 SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin ORDER BY hanbai_tanka DESC; -- 指定多个排序键(会优先使用左侧的键，如果该列存在相同值的话，会接着参考右侧的键。) SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM Shohin ORDER BY hanbai_tanka shobin_id;

NULL的顺序：

不能对NULL使用比较运算符，也就是说，不能对NULL和数字进行排序。也不能与字符串和日期比较大小。因此，使用含有NULL的列作为排序键时，NULL会在结果的开头或末尾汇总显示。

SELECT shohin_id, shiire_tanka FROM Shohin ORDER BY shiire_tanka; shohin_id shiire_tanka 0002 320 0001 500 0007 790 0003 2800 0004 2800 0005 500 0006 <-- 0008 <-- NULL值会汇集在开头或末尾

在排序键中使用显示用别名：

SELECT shobin_id AS id, hanbai_tanka AS ht FROM Shobin ORDER BY ht, id; id ht 008 100 002 500 006 500

ORDER BY 子句中可以使用的列：

ORDER BY子句中可以使用存在于表中，但并不包含在SELECT子句之中的列。

SELECT shobin_mei FROM Shobin ORDER BY shobin_id;

除此之外，ORDER BY中还可以使用聚合函数。

SELECT shobin_mei FROM Shobin GROUP BY shohin_bunrui ORDER BY COUNT(*); shohin_bunrui count 衣服 2 办公用品 2 厨房用品 4

不要使用列编号：

在ORDER BY子句中，还可以使用在SELECT子句中出现的列所对应的编号。列编号是指SELECT子句中的列按照从左到右的顺序进行排列时所对应的编号（1，2，3，…）。

SELECT shohin_id, shobin_mei, hanbai_tanka, shiire_tanka FROM Shobin ORDER BY 3 DESC, 1;

虽然列编号使用起来非常方便，但我们并不推荐使用，原因有以下两点。

代码阅读起来比较难。以后执行起来可能会出错。