MySQL数据库入门与实践(三)：聚合与排序

---

一、聚合函数

用于汇总的函数称为聚合函数或者聚集函数， 本书中统称为聚合函数。所谓聚合，就是将多行汇总为一行。实际上，所有的聚合函数都是这样，输入多行输出一行。常见的聚合函数有：count(),sum(),min(),sum(),avg()

-- 看看这个表共有多少行
SELECT COUNT(*) FROM Product;

常见的聚合函数有以下五个：

函数名称	作用
count()	对某一列进行计数
max()	求某一列的最大值
min()	求某一列的最小值
sum()	求某一列的和
avg()	求某一列的平均值

这些函数的定义和平常的定义并无区别。但需要注意的是单元格中存在 null 的值，所以搞清楚聚合函数在处理含null 的列时的表现很重要，来看一个例子：
首先先放出使用的表：

select count(purchase_price),count(*) from product; 

可以看出当使用count(*)时，查询的是所有的行数，但是如果跟的参数是某个列，查询的其实是不是null 的行数。
再看一个例子：

select avg(purchase_price),sum(purchase_price)/count(purchase_price) ,sum(purchase_price)/count(*) 
from product;

可以看出，前两种表达式算出来的平均价格是一样的，也就是无论什么函数对于 null 值的数据都会自动忽略。
需要说明的是 前面我们说到所有数据类型的数据都可以进行排序，但是只有数值型和日期型数据可以进行算术运算，所以这里的max,min 函数是所有数据类型都可以使用的，但是对于字符串型数据只能使用max,min，但是对于日期型数据，似乎求和和求平均值也没有什么意义。

• distinct关键字的应用
  在之前进行简单查询的时候，用过这个关键字，用在列名之前，会把重复的列名去除，在聚合函数中仍然可以使用。

SELECT COUNT(DISTINCT product_type) FROM Product;

这里需要注意一下逻辑顺序，应该是先去重再计数。如果代码进行以下修改

SELECT DISTINCT COUNT(product_type) FROM Product;

可以发现结果是很不同的，原因在于下面的命令是先计数再去重，但其实在完成计数后就是上面这一列，根本就不存在去不去重了。
其实不仅仅是count函数，别的聚合函数也可以使用distinct先去重，比如：

select sum(sale_price),sum(distinct sale_price) from product;

可以发现结果也是发生了变化，结果和上面说的相同。

二、分组

目前为止，我们看到的聚合函数的使用方法，无论是否包含 NULL，无论是否删除重复数据，都是针对表中的所有数据进行的汇总处理。下面，我们先把表分成几组，然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。

SELECT product_type, COUNT(*) FROM Product
GROUP BY product_type;

未使用 GROUP BY 子句时，结果只有 1 行，而这次的结果却是多行。这是因为不使用 GROUP BY 子句时，是将表中的所有数据作为一组来对待的。而使用 GROUP BY 子句时，会将表中的数据分为多个组进行处理。
打个比方，group by 子句就像是把整个表切成了几小份。group by 后面跟的列名也叫聚合键。
GROUP BY 子句的书写位置也有严格要求，一定要写在FROM 语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后），顺序如下：select->from>where>group by。
当聚合键中含有null 数据时，会单独把null 作为一组，其实也就是不确定值。

SELECT regist_date , COUNT(*) FROM Product GROUP BY regist_date ;

上面说到group by 是跟在where子句后面的，来看一下where子句起到了什么作用

SELECT purchase_price, COUNT(*)
FROM Product
-- WHERE product_type = '衣服'
GROUP BY purchase_price;

这是不加where 过滤条件的，那如果加了where的过滤条件呢？

SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;

可以发现显示的行数变少了，原因是先执行了where的过滤子句，将数据过滤成只有两行，分组分了个寂寞。
所以可以看出，现在的执行顺序是，from->where->group by ->select.
在这一小节，在书写sql语句的过程中很容易发生的一些错误在于

1. select 语句中含有多余的列，select 后面的跟的只能是聚合函数和聚合键
2. group by 后面跟的列名是别名，虽然在select 语句中可以为列名指定别名，但是不要忘记了sql 执行的顺序是最后执行select 语句，所以在执行group by 语句时，还没有这个别名。再有一点，select 语句设置别名也只是显示在最后查询的表上，并没有改变原来的表的列名，但是group by 后面跟的列名仍然是原来表中的列名，因此不可在group by 中使用别名。
3. group by 中显示的顺序是随机的，除非使用order by 子句指定
4. 在where子句中使用聚合函数，来看下面一个例子

SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

如果这时候我们想把数量为2的商品挑出来，能不能使用下面的代码呢？

ELECT product_type, COUNT(*) FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;

这一看好像是可以的，但其实不可以，会报错。其实分析一下执行顺序就行，我们知道先执行where子句，但是其实这时候还没有进行分组，那你count(*)得到的就是总函数，总函数又不会等于2，那么这时候得不到什么结果。
一个思想就是先分组，在去看哪个组的计数为2.这也就引出了下面的having子句。

SELECT product_type, COUNT(*)    FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;

这个就达到了我们的预期，可以看出，having子句是接在group by 后面的，就是在group by 分组后，再进行筛选。这时我们的语句顺序为 select->from ->where->group by ->having ，但是执行顺序确是：from ->where->group by ->having->select,
having 后面跟的也不是随意的，和select 语句一样，后面只能有聚合函数和聚合键，其实也很好理解，是先执行group by 语句，这时候的表中只有聚合函数和聚合键，这时候用别的列名来筛选肯定是不行的。
我们知道有些条件写在where中，但这些条件写在having中其实得到的结果相同，但一般还是写在having 中，其实从执行顺序上可以看出，where是对原始的表的行进行过滤，而having 是对分完后的组进行过滤，所以一般聚合键所对应的条件是扔在where中，而聚合函数的结果的筛选是扔在having中。

三、排序

到现在为止，我们讲了查询的过滤，分组，以及分组后再过滤，在分组的过程中，我们说到了数据显示的顺序其实是随机的，那如果要按照一定的顺序来排列，比如说，按照总分最多来对学生进行排序，诸如此类。
这时候我们可以添加order by 子句

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;

这里并没有使用group by 子句，而是对整张表进行了排序。
不论何种情况， ORDER BY 子句都需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。 ORDER BY子句中书写的列名称为排序键。
.SELECT → FROM → WHERE → GROUP BY →HAVING → ORDER BY ，
当然，我们知道，排序也分顺序和降序 asc 和desc，默认都是按照升序排序，如果按降序排列，可使用下面的代码：

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;

对于排序键值相同的两行又该有什么顺序呢，其实我们可以给order by 指定多个排序键，比如

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;

这就表示，先按照sales_price排序，再按照product_id 排序，按规定是先用最左边的键排序，在改键相同时才会使用后面的键继续排序，也就是排序键之间也有优先级，那么对于多个排序键也是同样的道理。
那对于多个键排序如果要指定升序或降序的话，需要在每个键的后面加asc,或者时desc,比如

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price asc, product_id desc;

在前面讲运算符时讲到null 数据类型时，其实它是不参与运算的，那排序时遇到null 其实也是不参与的，一般都是放在开头或者末尾，但时究竟是开头还是末尾，根据软件的不同有所差异。

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;

前面我们说到group by 中不允许使用别名，因为在执行group by子句时还没有执行select 语句，这时候没有别名，那么在order by 中能不能使用别名呢？答案是可以的，为什么呢？在开头我们说了，order by 是在显示之前进行的排序，也就是，order by 是在select 语句执行之后，那么这时候已经有别名了，所以可以使用别名。
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
前面说到 having 和select 如果遇到了group by 必须使用 聚合键，但是order by可以，比如下面

SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;

这是很有意思的一点，order by 调用了select 以外列的信息，甚至order by 中还可以和group by 以及聚合函数结合起来。

SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);

---

总结

在这小节，我们讲完了聚合查询，很重要的是5个聚合函数以及有关null 的注意点，对表的分组，分组后再过滤的having 子句，having子句和where子句的区别，以及在select 执行后的order by 排序。