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

分手后的思念是犯贱 2022-11-27 12:24 281阅读 0赞

一：聚合函数：  
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)