黑马程序员---聚合函数与分组查询

一：聚合函数：
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
1：计算班级学生的总数

mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)

2：查询男性有多少人，女性有多少人：

mysql> select count(*) from students where gender="男";
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from students where gender="女";
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

3：查询最大的年龄

mysql> select max(age) from students;
+----------+
| max(age) |
+----------+
|       59 |
+----------+
1 row in set (0.00 sec)

4：查询女性的最高 身高

mysql> select max(height) from students where gender="女";
+-------------+
| max(height) |
+-------------+
|      180.00 |
+-------------+
1 row in set (0.00 sec)

mysql> select min(height) from students where gender="女";
+-------------+
| min(height) |
+-------------+
|      160.00 |
+-------------+
1 row in set (0.00 sec)

5：计算所有人的年龄总和，和平均年龄：

mysql> select sum(age) from students;
+----------+
| sum(age) |
+----------+
|      443 |
+----------+
1 row in set (0.00 sec)
mysql> select avg(age) from students;
+----------+
| avg(age) |
+----------+
|  27.6875 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(age)/count(*) from students;
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           18.3333 |
+-------------------+
1 row in set (0.00 sec)

注意：这里我们发现这样求出来的和avg不一样，因为avg在求平均年龄的时候，会把空数据去掉。而下面这种方式是数出所有的个数。
如何四舍五入保留呢？

mysql> select round(sum(age)/count(*),2) from students;
+----------------------------+
| round(sum(age)/count(*),2) |
+----------------------------+
|                      18.33 |
+----------------------------+
1 row in set (0.00 sec)

二：分组查询：
1：group by + group_concat()的使用
2：group by + having的使用
3：group by + with rollup的使用
4：group by + 聚合函数的使用

1：按照性别分组,查询所有的性别：

mysql> select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+
4 rows in set (0.00 sec)

注意：以性别分组只能查询性别，不能查询别的，因为顺序已经被打乱了。
2： 计算每种性别中的人数

mysql> select gender, count(gender)  from students group by gender;
+--------+---------------+
| gender | count(gender) |
+--------+---------------+
| 男     |             6 |
| 女     |             8 |
| 中性   |             1 |
| 保密   |             1 |
+--------+---------------+
4 rows in set (0.00 sec)

3： 计算每个年龄中的人数

mysql> select gender, max(age)  from students group by gender;
+--------+----------+
| gender | max(age) |
+--------+----------+
| 男     |       59 |
| 女     |       38 |
| 中性   |       33 |
| 保密   |       28 |
+--------+----------+
4 rows in set (0.01 sec

4： 查询 男、女性别中年龄的最大值

mysql> select gender, max(age)  from students group by gender having(gender='男'or gender="女");
+--------+----------+
| gender | max(age) |
+--------+----------+
| 男     |       59 |
| 女     |       38 |
+--------+----------+
2 rows in set (0.00 sec)

5：查询每组性别的平均年龄

mysql> select gender, avg(age)  from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  31.8333 |
| 女     |  23.8750 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+
4 rows in set (0.00 sec)

6：查询同种性别中的姓名

mysql> select gender, group_concat(name)  from students group by gender;
+--------+---------------------------------------------------------------------+
| gender | group_concat(name)                                                  |
+--------+---------------------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗                             |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰,凌小小                     |
| 中性   | 金星                                                                |
| 保密   | 凤姐                                                                |
+--------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)

7：查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30(重点)

mysql> select gender, group_concat(name)  from students group by gender having avg(age)>30;
+--------+----------------------------------------------------------+
| gender | group_concat(name)                                       |
+--------+----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗                  |
| 中性   | 金星                                                     |
+--------+----------------------------------------------------------+
2 rows in set (0.01 sec)

8： 查询每种性别的平均年龄和名字

mysql> select gender,avg(age), group_concat(name) from students group by gender;
+--------+----------+---------------------------------------------------------------------+
| gender | avg(age) | group_concat(name)                                                  |
+--------+----------+---------------------------------------------------------------------+
| 男     |  31.8333 | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗                             |
| 女     |  23.8750 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰,凌小小                     |
| 中性   |  33.0000 | 金星                                                                |
| 保密   |  28.0000 | 凤姐                                                                |
+--------+----------+---------------------------------------------------------------------+
4 rows in set (0.00 sec)

9：查询每种性别中的人数多于2个的性别和姓名（重点）

mysql> select gender, group_concat(name) from students group by gender having count(gender)>2;
+--------+---------------------------------------------------------------------+
| gender | group_concat(name)                                                  |
+--------+---------------------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗                             |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰,凌小小                     |
+--------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select gender, group_concat(name) from students group by gender having count(*)>2;
+--------+---------------------------------------------------------------------+
| gender | group_concat(name)                                                  |
+--------+---------------------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗                             |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰,凌小小                     |
+--------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

10：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

mysql> select gender, group_concat(name),max(age),min(age),sum(age),avg(age) from students group by gender with rollup;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+----------+
| gender | group_concat(name)                                                                                                                         | max(age) | min(age) | sum(age) | avg(age) |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+----------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗                                                                                                    |       59 |       12 |      191 |  31.8333 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰,凌小小                                                                                            |       38 |       12 |      191 |  23.8750 |
| 中性   | 金星                                                                                                                                       |       33 |       33 |       33 |  33.0000 |
| 保密   | 凤姐                                                                                                                                       |       28 |       28 |       28 |  28.0000 |
| NULL   | 彭于晏,刘德华,周杰伦,程坤,郭靖,司马二狗,小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰,凌小小,金星,凤姐                                          |       59 |       12 |      443 |  27.6875 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------+----------+----------+----------+----------+
5 rows in set (0.00 sec)