学习.png
随着表中记录（数据行）的不断积累，存储数据逐渐增加，有时我们可能希望计算出这些数据的合计值或者平均值等，这个时候就需要使用SQL语句的汇总操作等方法。
一：对表进行聚合排序
1：聚合函数
通过SQL对数据进行某种操作或计算时需要使用函数。SQL有五种常用的函数：
● COUNT：计算表中数据的行数（记录数）。
● SUM：计算表中数值列中数据的合计数。
● AVG：计算表中数值列中数据的平均值。
● MAX：计算表中数值列中数据的最大值。
● MIN：计算表中数值列中数据的最小值。
如上所示，用于汇总的函数成为聚合函数或者聚集函数。接下来，我们仍然使用之前创建的Product表进行函数的学习，Product表的结构和内容如下：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0003 | 运动T衫 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2017-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2017-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2017-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2016-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-11-11
(8 行记录)
2：计算表中数据的行数
使用COUNT函数时，输入表的列，就能输出数据行数：
例如，计算全部数据的行数：
SELECT COUNT(*)
FROM Product;
执行结果：
count-------
8
(1 行记录)
COUNT（）中的星号，代表全部列的意思。函数的输入值称为参数，输出值称为返回值。
3：计算NULL之外的数据的行数
SELECT COUNT(purchase_price)
FROM Product;
执行结果：
count-------
6
(1 行记录)
对于COUNT函数来说，参数列不同，计算结果也会不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
4：计算合计值
计算合计值需要使用SUM函数
例，计算销售单价的合计值：
SELECT SUM(sale_price)
FROM Product;
执行结果：
sum-------
16780
(1 行记录)
例如，计算销售单价和进货单价的合计值：
SELECT SUM(sale_price), SUM(purchase_price)
FROM Product;
执行结果：
sum | sum-------+-------
16780 | 12210
(1 行记录)
注释：
聚合函数会将NULL排除在外，但COUNT(*)例外，并不会排除NULL。
5：计算平均值
计算平均值需要使用AVG函数
例，计算销售单价的平均值：
SELECT AVG(sale_price)
FROM Product;
执行结果：
avg-----------------------
2097.5000000000000000
(1 行记录)
例如，计算销售单价和进货单价的平均值：
SELECT AVG(sale_price), AVG(purchase_price)
FROM Product;
执行结果：
avg | avg-----------------------+-----------------------
2097.5000000000000000 | 2035.0000000000000000
(1 行记录)
6：计算最大值和最小值
想要计算出多条记录的最大值或最小值，可以分别使用MAX和MIN函数。
例，计算销售单价的最大值和进货单价的最小值：
SELECT MAX(sale_price), MIN(purchase_price)
FROM Product;
执行结果：
max | min------+-----
6800 | 320
(1 行记录)
例如，计算登记日期的最大值和最小值：
SELECT MAX(regist_date), MIN(regist_date)
FROM Product;
执行结果：
max | min------------+------------
2017-11-11 | 2016-04-28
(1 行记录)
MAX/MIN函数几乎适用于所有数据类型的列。而SUM/AVG函数只适用于数值类型的列。
7：使用聚合函数删除重复值（关键字DISTINCT）
例，计算除去重复数据后的数据行数：
SELECT COUNT(DISTINCT product_type)
FROM Product;
执行结果：
count-------
3
(1 行记录)
想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT，可以删除重复数据。
二：对表进行分组
1：GROUP BY子句
使用GROUP BY子句可以像切蛋糕那样将表分割。GROUP BY子句的语法结构如下：
SELECT <列名1>, <列名2>,...
FROM <表名>
GROUP BY <列名1>, <列名2>,...;
例如，按照商品种类统计数据行数：
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
执行结果：
product_type | count--------------+-------
衣服 | 2
办公用品 | 2
厨房用具 | 4
(3 行记录)
● 在GROUP BY 子句中指定的列称为聚合键。
● 子句的书写顺序（暂定）：
SELECT→FROM→ WHERE→ GROUP BY
● SQL子句的顺序不能更改，也不能相互替换。
2：聚合键中包含NULL的情况
例，按照进货单价统计数据行数：
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
执行结果：
purchase_price | count----------------+-------
| 2
320 | 1
500 | 1
5000 | 1
2800 | 2
790 | 1
(6 行记录)
聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式显示出来。
3：使用WHERE子句和GROUP BY 子句的执行结果
例，同时使用WHERE子句和GROUP BY子句：
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
执行结果：
purchase_price | count----------------+-------
500 | 1
2800 | 1
(2 行记录)
4：与聚合函数和GROUP BY 子句有关的常见错误
● 使用GROUP BY 子句时，SELECT 子句中不能出现聚合键之外的列名。
● 在GROUP BY 子句中不能使用SELECT子句中定义的别名。
● GROUP BY 子句结果的显示是无序的。
● 只有SLEECT子句、GROUP BY 子句和HAVING子句中能够使用聚合函数，WHERE 子句中不能使用聚合函数。
三：为聚合结果指定条件
1：HAVING子句
对集合指定条件可以使用HAVING子句。HAVING子句的语法如下：
SELECT <列名1>, <列名2>,...
FROM <表名>
GROUP BY <列名1>, <列名2>,...
HAVING <分组结果对应的条件>；
注释：
HAVING子句要写在GROUP BY 子句之后。
例，从按照商品种类进行分组后的结果中，取出“包含的数据行数为2行”的组：
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
执行结果：
product_type | count--------------+-------
衣服 | 2
办公用品 | 2
(2 行记录)
不使用HAVING子句的情况
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
执行结果：
product_type | count--------------+-------
衣服 | 2
办公用品 | 2
厨房用具 | 4
(3 行记录)
2：HAVING子句的构成要素
HAVING子句中能够使用的3中要素如下：
● 常数
● 聚合函数
● GROUP BY 子句中 指定的列名（聚合键）
3：相比于HAVING子句，更适合于写在WHERE子句中的条件
我们会发现，有些条件既可以写在HAVING子句中，也可以写在WHERE 子句中，而且结果是一样的。这些条件就是聚合键所对应的条件。
例，将条件写在HAVING 子句中：
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';
执行结果：
product_type | count--------------+-------
衣服 | 2
(1 行记录)
将条件书写在WHERE子句中的情况
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;
执行结果：
product_type | count--------------+-------
衣服 | 2
(1 行记录)
两者结果完全相同，但是，从执行速度来讲，将条件写在WHERE 子句中要比写在HAVING子句中的处理速度要快。所以，聚合键所对应的条件应该书写在WHERE 子句中。
四：对查询结果进行排序
1：ORDER BY子句
使用ORDER BY 子句可对查询结果进行排序，ORDER BY子句的语法：
SELECT <列名1>, <列名2>,...
FROM <表名>
ORDER BY <排列基准1>, <排列基准2>,....;
例，按照销售单价由低到高（升序）进行排序：
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
执行结果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+----------------
0008 | 圆珠笔 | 100 |
0006 | 叉子 | 500 |
0002 | 打孔器 | 500 | 320
0007 | 擦菜板 | 880 | 790
0001 | T衫 | 1000 | 500
0004 | 菜刀 | 3000 | 2800
0003 | 运动T衫 | 4000 | 2800
0005 | 高压锅 | 6800 | 5000
(8 行记录)
● ORDER BY子句中书写的列名称为排序键。
● ORDER BY 子句通常写在SELECT语句的末尾。
2：指定升序或降序
降序排列时，可使用DESC关键字。
例，按照销售单价由高到低（降序）进行排序：
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;
执行结果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+----------------
0005 | 高压锅 | 6800 | 5000
0003 | 运动T衫 | 4000 | 2800
0004 | 菜刀 | 3000 | 2800
0001 | T衫 | 1000 | 500
0007 | 擦菜板 | 880 | 790
0002 | 打孔器 | 500 | 320
0006 | 叉子 | 500 |
0008 | 圆珠笔 | 100 |
(8 行记录)
注释：
未指定ORDER BY 子句中排列顺序时会默认以升序排列。
3：指定多个排序键
可以在ORDER BY 子句中指定多个排序键，规则是优先使用左侧的键，如果该列存在相同的值，再接着参考右侧的键。
例，按照销售单价和商品编号的升序进行排序：
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;
执行结果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+----------------
0008 | 圆珠笔 | 100 |
0002 | 打孔器 | 500 | 320
0006 | 叉子 | 500 |
0007 | 擦菜板 | 880 | 790
0001 | T衫 | 1000 | 500
0004 | 菜刀 | 3000 | 2800
0003 | 运动T衫 | 4000 | 2800
0005 | 高压锅 | 6800 | 5000
(8 行记录)
4：NULL 的排序
例，按照进货单价的升序进行排序：
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;
执行结果：
product_id | product_name | sale_price | purchase_price------------+--------------+------------+----------------
0002 | 打孔器 | 500 | 320
0001 | T衫 | 1000 | 500
0007 | 擦菜板 | 880 | 790
0003 | 运动T衫 | 4000 | 2800
0004 | 菜刀 | 3000 | 2800
0005 | 高压锅 | 6800 | 5000
0006 | 叉子 | 500 |
0008 | 圆珠笔 | 100 |
(8 行记录)
如上所示，排序键包含NULL时，会在开头或者末尾进行汇总。
5：几点关于ORDER BY子句的事项
● 在ORDER BY 子句中可以使用SELECT子句中定义的别名。
● 在ORDER BY 子句中可以使用SLEECT子句中为使用的列和聚合函数。
● 在ORDER BY 子句中不要使用列编号。
路漫漫其修远兮，吾将上下而求索。
每天学习一点点，每天进步一点点。
网友评论