MySql基础（四）查询之聚合

• 数据分组 GROUP BY

user表

mysql> SELECT name,age,salary,createtime FROM user;
+-----------------+------+---------+---------------------+
| name            | age  | salary  | createtime          |
+-----------------+------+---------+---------------------+
| 燕虹            |   21 | 7000.14 | 2017-08-31 10:53:52 |
| 鬼王            |  180 |    8000 | 2017-08-31 10:54:51 |
| 毒神            |  200 |    7000 | 2017-08-31 10:55:19 |
| 陆小琪          |   20 |    1000 | 2017-08-31 10:55:59 |
| 苍松好道人      |  170 |    7000 | 2017-08-31 10:57:14 |
| 周一仙          |  500 |    5000 | 2017-09-01 16:18:20 |
| 小环            |   18 |    3000 | 2017-09-01 16:19:57 |
| 笨蛋            |    1 |       1 | 2017-09-07 16:24:43 |
| 笨笨            |    1 |       1 | 2017-09-07 16:25:03 |
| 小本            |   12 |    1000 | 2017-09-08 09:29:52 |
| 小欢1           |   32 |    1000 | 2017-09-08 09:59:54 |
| 小欢2           |   22 |    1000 | 2017-09-08 15:41:19 |
| 小欢3           |   17 |    1000 | 2017-09-08 15:43:11 |
| 小欢4           |   26 |    1000 | 2017-09-08 15:48:03 |
| 1               |    1 |       1 | 2017-09-09 14:49:54 |
| xiaoming        |   22 |   15555 | 2017-09-09 15:04:24 |
| 1               |    1 |       1 | 2017-09-15 15:00:02 |
+-----------------+------+---------+---------------------+
17 rows in set (0.00 sec)

查询各个年龄段的具体人数

mysql> SELECT age ,COUNT(*) AS Total FROM user  GROUP BY age;
+------+-------+
| age  | Total |
+------+-------+
|    1 |     4 |
|   12 |     1 |
|   17 |     1 |
|   18 |     1 |
|   20 |     1 |
|   21 |     1 |
|   22 |     2 |
|   26 |     1 |
|   32 |     1 |
|  170 |     1 |
|  180 |     1 |
|  200 |     1 |
|  500 |     1 |
+------+-------+
13 rows in set (0.00 sec)

查询各个年龄的具体人数，并把对应的人员的姓名显示出来

mysql> SELECT age ,GROUP_CONCAT(name), COUNT(*) AS Total FROM user  GROUP BY age;
+------+--------------------+-------+
| age  | GROUP_CONCAT(name) | Total |
+------+--------------------+-------+
|    1 | 笨蛋,笨笨,1,1      |     4 |
|   12 | 小本               |     1 |
|   17 | 小欢3              |     1 |
|   18 | 小环               |     1 |
|   20 | 陆小琪             |     1 |
|   21 | 燕虹               |     1 |
|   22 | 小欢2,xiaoming     |     2 |
|   26 | 小欢4              |     1 |
|   32 | 小欢1              |     1 |
|  170 | 苍松好道人         |     1 |
|  180 | 鬼王               |     1 |
|  200 | 毒神               |     1 |
|  500 | 周一仙             |     1 |
+------+--------------------+-------+
13 rows in set (0.00 sec)

• 数据分组 过滤

数据分组后，往往通过HAVING 关键字，取最大值，最小值，平均值（MAX(),MIN(),AVG(),SUM()）等对分组后的数据进行过滤。
按年龄进行分组，查找分组后工资最大值大于7000并且最小值小于20 的数据

mysql>  SELECT age, GROUP_CONCAT(salary) as "工资范围" , GROUP_CONCAT(name) as "姓名范围" ,  COUNT(*) AS Total FROM user GROUP BY age HAVING MAX(salary)>3000 AND MIN(salary)<20;
+------+--------------+---------------+-------+
| age  | 工资范围     | 姓名范围      | Total |
+------+--------------+---------------+-------+
|   21 | 7000,14      | 燕虹,小鬼     |     2 |
+------+--------------+---------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| o2   |  103 | cocount    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
9 rows in set (0.00 sec)

mysql> SELECT
    -> s_id,
    -> GROUP_CONCAT(f_name) as '包含种类',
    -> GROUP_CONCAT(f_price) as '各个价格',
    -> COUNT(*) AS Total,
    -> SUM(f_price) AS '总价',
    -> AVG(f_price) AS '平均价格'
    -> FROM
    -> fruits
    -> GROUP BY s_id;
+------+-------------------------+------------------+-------+--------+--------------+
| s_id | 包含种类                | 各个价格         | Total | 总价   | 平均价格     |
+------+-------------------------+------------------+-------+--------+--------------+
|  101 | apple,blackberry,cherry | 5.20,10.20,3.20  |     3 |  18.60 |     6.200000 |
|  102 | orange,banana,grape     | 11.20,10.30,5.30 |     3 |  26.80 |     8.933333 |
|  103 | cocount                 | 9.20             |     1 |   9.20 |     9.200000 |
|  105 | melon                   | 8.20             |     1 |   8.20 |     8.200000 |
|  107 | xbababa                 | 3.60             |     1 |   3.60 |     3.600000 |
+------+-------------------------+------------------+-------+--------+--------------+
5 rows in set (0.00 sec)

GROUP BY一般常与用HAVING 对分组后的数据进行过滤 ，满足条件的分组才会显示

查询水果表按种类s_id进行分组，并显示每个种类分组中水果名称大于1的数据
mysql> SELECT
    -> s_id,
    -> GROUP_CONCAT(f_name) as '包含种类',
    -> GROUP_CONCAT(f_price) as '各个价格',
    -> COUNT(*) AS Total,
    -> SUM(f_price) AS '总价',
    -> AVG(f_price) AS '平均价格'
    -> FROM
    -> fruits
    -> GROUP BY s_id
    -> HAVING COUNT(f_name)>1;
+------+-------------------------+------------------+-------+--------+--------------+
| s_id | 包含种类                | 各个价格         | Total | 总价   | 平均价格     |
+------+-------------------------+------------------+-------+--------+--------------+
|  101 | apple,blackberry,cherry | 5.20,10.20,3.20  |     3 |  18.60 |     6.200000 |
|  102 | orange,banana,grape     | 11.20,10.30,5.30 |     3 |  26.80 |     8.933333 |
+------+-------------------------+------------------+-------+--------+--------------+
2 rows in set (0.00 sec)

GROUP BY后使用WITH ROLLUP

使用WITH ROLLUP,会增加一行，显示所有的统计数量
mysql> SELECT
    -> s_id,
    -> COUNT(*) AS Total
    -> FROM
    -> fruits
    -> GROUP BY s_id
    -> WITH ROLLUP
    -> ;
+------+-------+
| s_id | Total |
+------+-------+
|  101 |     3 |
|  102 |     3 |
|  103 |     1 |
|  105 |     1 |
|  107 |     1 |
| NULL |     9 |
+------+-------+
6 rows in set (0.00 sec)

• COUNT()函数

• count(*):计算表中总的行数，不忽略某列有数据或者是null;

• count(字段名):计算表中总的行数，忽略为null值的行
  如下两个查询的不同结果：

  
  image.png
  image.png