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 |
+---------------+----------+
posted on 2016-07-11 22:32 Mrs.Totoro 阅读(332) 评论(0) 编辑 收藏 举报