MySql基础（四）查询之聚合
2018-01-01 本文已影响12人
凌雲木
-
数据分组 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
