11.表的查询5种子句之group by与having

本节讲解group by 和 having两种子句。group by与having是分组数据和过滤分组的操作符。在介绍之前，先学习5个聚集函数。

为了大家更好的理解，我将例子中需要的sql文件上传到了百度网盘，供大家食用。
链接：https://pan.baidu.com/s/1dqQS2sQj8QoCKi3UfN1wVA 密码：h7hh

1. 聚集函数

1. max()函数
   max()返回指定列中的最大值。

mysql> # max用法
mysql> # 在products中求出商品价格最大为多少
mysql> select max(prod_price) as maxprice from products;
+----------+
| maxprice |
+----------+
|    55.00 |
+----------+
1 row in set (0.16 sec)

温馨提示 ：as表示重命名，也可以不进行重命名。
2. min()函数
min()返回指定列中的最小值

mysql> # min用法
mysql> # 在products中求出商品价格最小为多少
mysql> select min(prod_price) as minprice from products;
+----------+
| minprice |
+----------+
|     2.50 |
+----------+
1 row in set (0.00 sec)

3.avg()函数
avg()返回指定列值之和的平均值

mysql> # avg用法
mysql> # 在products中求出商品平均价格为多少
mysql> select avg(prod_price) as avgprice from products;
+-----------+
| avgprice  |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.11 sec)

4.sum()函数
sum()函数返回指定列值的总和

mysql> # sum用法
mysql> # 在products中求出商品价格总和为多少
mysql> select sum(prod_price) as sumprice from products;
+----------+
| sumprice |
+----------+
|   225.87 |
+----------+
1 row in set (0.14 sec)

5.count()函数
count()函数进行计数
count用法一：使用count(*),对表中行就行计数，不管是否为空值。
count用二：使用count(column),对表中行就行计数，忽略空值null。

count有两种使用方式
mysql> # count用法一
mysql> # 在products中求出商品的数量
mysql> select count(*) as cutnum from customers;
+--------+
| cutnum |
+--------+
|      5 |
+--------+
1 row in set (2.28 sec)

mysql> # count用法二
mysql> select count(cust_email) as cutnum from customers;
+--------+
| cutnum |
+--------+
|      3 |
+--------+
1 row in set (2.10 sec)

2. group by分组

1. group by 顾名思义就是分组。分组指的是将表按照特定的规则来分成若干组。
2. group by 一般搭配前面的聚集函数一起使用，不然就分组而言就没有什么意思了。
3. 格式：group by column;

举个栗子(大家细细体会其中的分组实现)

mysql> # group by与统计函数结合使用
mysql> # 在products中求出每个vend_id中求出最大的商品
mysql> select vend_id,max(prod_price) as maxprice from products group by vend_id;
+---------+----------+
| vend_id | maxprice |
+---------+----------+
|    1001 |    14.99 |
|    1002 |     8.99 |
|    1003 |    50.00 |
|    1005 |    55.00 |
+---------+----------+
4 rows in set (0.00 sec)

对上面栗子进行分析，先将vend_id分组（这里为1001，1002，1003，1005），然后对每一组进行求和，就得到了上面的表。

其他栗子

mysql> # 在products中求出每个vend_id中商品的种类数量
mysql> select vend_id,count(prod_price) as num from products group by vend_id;
+---------+-----+
| vend_id | num |
+---------+-----+
|    1001 |   3 |
|    1002 |   2 |
|    1003 |   7 |
|    1005 |   2 |
+---------+-----+
4 rows in set (0.00 sec)

mysql> # 在products中求出每个vend_id中商品的平均价格
mysql> select vend_id,avg(prod_price) as avgprice from products group by vend_id;
+---------+-----------+
| vend_id | avgprice  |
+---------+-----------+
|    1001 | 10.323333 |
|    1002 |  6.205000 |
|    1003 | 13.212857 |
|    1005 | 45.000000 |
+---------+-----------+
4 rows in set (0.00 sec)

mysql> # 在products中求出每个vend_id中商品的价格总和
mysql> select vend_id,sum(prod_price) as sumprice from products group by vend_id;
+---------+--------+
| vend_id |sumprice|
+---------+--------+
|    1001 | 30.97  |
|    1002 | 12.41  |
|    1003 | 92.49  |
|    1005 | 90.00  |
+---------+--------+
4 rows in set (0.00 sec)

1. group by 与 with rollup选项
   with rollup就是对组内的数据进行信息汇总，在分组的最后加上一条汇总记录。
   举个栗子

   mysql> # group by 与 with rollup选项
   mysql> select vend_id,max(prod_price) as maxprice from products
       -> group by vend_id with rollup;
   +---------+----------+
   | vend_id | maxprice |
   +---------+----------+
   |    1001 |    14.99 |
   |    1002 |     8.99 |
   |    1003 |    50.00 |
   |    1005 |    55.00 |
   |    NULL |    55.00 |
   +---------+----------+
   5 rows in set (0.13 sec)

2. 注意

   • 当分组中出现null值，则将null值作为一个分组返回。多个null值，就将其分为一组。
   • group by出现在where子句之后。

3. having过滤分组

having用于设置分组或聚合函数的过滤筛选条件。用法与where子句语法格式类似。
举个栗子

mysql> # 在products中求出每个vend_id中商品的种类数量,并过滤出数量大于2的vend_id
mysql> select vend_id,count(prod_price) as num from products group by vend_id having num > 2;
+---------+-----+
| vend_id | num |
+---------+-----+
|    1001 |   3 |
|    1003 |   7 |
+---------+-----+
2 rows in set (0.00 sec)

上面例子就是将vend_id分组，然后对分组的结果进行筛选。
1. having与where的区别（必须要理解）
相似处：having非常类似于where。对于前面学过的所有的where子句的技术和选项都都适用于having。
不同之处：having与where有很大的区别。where是对原表的查询，而having是对分组进行过滤，也可以理解成对结果表的查询。（不懂的看下面的例子及详解）
举个栗子

mysql> # having与where联合使用
mysql> # 列出具有2个及以上、价格大于等于10的供应商
mysql> select vend_id,count(*) as prodnum from products
    -> where prod_price >= 10
    -> group by vend_id
    -> having prodnum >= 2;
+---------+---------+
| vend_id | prodnum |
+---------+---------+
|    1003 |       4 |
|    1005 |       2 |
+---------+---------+
2 rows in set (0.07 sec)

这里的where是对products表来进行查询的。having可以看成是对分组来进行过滤，也可以理解成是对vender_id与prodnum组合成的新表进行查询。如果用where对分组来进行过滤就会报错。

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录