《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;

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

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