SQL --Chater03 聚合与排序

数据说明：

+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T恤        | 衣服          |         1000 |          500 | 2009-09-20 |
| 0002      | 打孔器      | 办公用品      |          500 |          320 | 2009-09-11 |
| 0003      | 运动T恤     | 衣服          |         4000 |         2800 | NULL       |
| 0004      | 菜刀        | 厨房用具      |         3000 |         2800 | 2009-09-20 |
| 0005      | 高压锅      | 厨房用具      |         6800 |         5000 | 2009-01-15 |
| 0006      | 叉子        | 厨房用具      |          500 |         NULL | 2009-09-20 |
| 0007      | 擦菜板      | 厨房用具      |          880 |          790 | 2008-04-28 |
| 0008      | 圆珠笔      | 办公用品      |          100 |         NULL | 2009-11-11 |
+-----------+------------+---------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

对表进行聚合查询

聚合函数：

COUNT：计算表中的记录数（行数）。

SUM：计算表中数值列的数据合计值。

AVG：计算表中数值列的数据平均值。

MAX：求出表中任意列中数据的最大值。

MIN：求出表中任意列中数据的最小值。

 

COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。

MAX/MIN函数几乎适用所有数据类型的列。SUM/AVG函数只适用数字类型的列。

想要计算值得种类时，可以在COUNT函数的参数中使用DISTINCT。

在聚合函数的参数中使用DISTINCT，可以删除重复数据。

 

对表进行分组

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


+---------------+----------+
| shohin_bunrui | COUNT(*) |
+---------------+----------+
| 办公用品      |        2 |
| 厨房用具      |        4 |
| 衣服          |        2 |
+---------------+----------+

GOOUP BY 就像切分表的一把刀。

子句的书写顺序：

SELECTàFROMàWHEREàGROUP BY

SQL子句的顺序不能改变，也不能互相替换。

 

SELECT shiire_tanka, COUNT(*)
  FROM Shohin
 GROUP BY shiire_tanka;

+--------------+----------+
| shiire_tanka | COUNT(*) |
+--------------+----------+
|         NULL |        2 |
|          320 |        1 |
|          500 |        1 |
|          790 |        1 |
|         2800 |        2 |
|         5000 |        1 |
+--------------+----------+

　

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

 

GROUP BY 和WHERE并用时， SELECT语句的执行顺序如下：

FROM---WHERE---GROUP BY---SELECT

 

使用聚合函数时，SELECT字句中只能存在以下三种元素：

• 常数
• 聚合函数
• GROUP BY字句中指定的列明（也就是聚合键）

 

GROUP BY子句中不能够使用SELECT子句中定义的别名。

因为SELECT子句在GROUP BY子句之后执行。

 

GROUP BY子句结果的显示是无序的。

只有SELECT和HAVING子句（以及ORDER BY字句）中能够使用聚合函数。

 

 

为聚合函数结果指定条件

HAVING子句：

SELECT <列名1>, <列名2>, <列名3>, …

FROM <表名>

GROUP BY <列名1>, <列名2>, <列名3>, …

HAVING <分组结果对应的条件>

 

使用HAVING子句时SELECT语句的顺序：

SELECT---FROM---WHERE---GROUP BY---HAVING

 

HAVING子句要写在GROUP BY子句之后，在DBMS内部的执行顺序也排在GROUP BY子句之后。

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

in_bunrui | COUNT(*) |
+---------------+----------+
| 办公用品      |        2 |
| 衣服          |        2 |
+---------------+----------+

SELECT shohin_bunrui, AVG(hanbai_tanka)
  FROM Shohin
 GROUP BY shohin_bunrui
HAVING AVG(hanbai_tanka) >= 2500;

ohin_bunrui | AVG(hanbai_tanka) |
+---------------+-------------------+
| 厨房用具      |         2795.0000 |
| 衣服          |         2500.0000 |
+---------------+-------------------+

　

HAVING字句中能够使用的3种要素如下：

• 常数
• 聚合函数
• GROUP BY子句中指定的列名（即聚合键）

WHERE子句 = 指定行所对应的条件

HAVING子句 = 指定组所对应的条件

对查询结构进行排序

ORDER BY子句：

SELECT <列名1>, <列名2>, <列名3>, …

FROM <表名>

ORDER BY <排序基准列1>, <排序基准列1>,…

 

不论何种情况，ORDER BY子句都需要写在SELECT语句的末尾。这是因为对数据进行排序的操作必须在结果即将返回时执行。

子句的书写顺序：

SELECT---FROM---WHERE---GROUP BY ---HAVING---ORDER BY

 

未指定ORDER BY子句中排序时会默认使用升序进行排序。ASC升序，DESC降序。

 

排序键中包含NULL时，会在开头或末尾进行汇总。（MySQL中，升序会在开头汇总，降序会在末尾汇总）

 

使用HAVING子句时SELECT语句的执行顺序：

FROM---WHERE---GROUP BY---HAVING---SELECT---ORDER BY

SELECT子句的执行顺序在GROUP BY子句之后，ORDER BY子句之前。因此在执行GROUP BY子句时，SELECT语句中定义的别名无法别识别。对于SELECT子句之后执行GROUP BY子句来说，就没有这样的问题了。

 

 

在ORDER BY子句中可以使用SELECT子句中为使用的列和聚合函数.

 

SELECT shohin_mei, hanbai_tanka, shiire_tanka
  FROM Shohin
ORDER BY shohin_id;

+------------+--------------+--------------+
| shohin_mei | hanbai_tanka | shiire_tanka |
+------------+--------------+--------------+
| T恤        |         1000 |          500 |
| 打孔器     |          500 |          320 |
| 运动T恤    |         4000 |         2800 |
| 菜刀       |         3000 |         2800 |
| 高压锅     |         6800 |         5000 |
| 叉子       |          500 |         NULL |
| 擦菜板     |          880 |          790 |
| 圆珠笔     |          100 |         NULL |
+------------+--------------+--------------+


SELECT shohin_bunrui, COUNT(*)
  FROM Shohin
 GROUP BY shohin_bunrui
ORDER BY COUNT(*);

+---------------+----------+
| shohin_bunrui | COUNT(*) |
+---------------+----------+
| 办公用品      |        2 |
| 衣服          |        2 |
| 厨房用具      |        4 |
+---------------+----------+