数据库—6（表数据操作的SQL语句,聚合函数、分组查询）

一、聚合函数
聚合函数，对表中的数据进行统计和计算，用于统计和计算分组数据。分组查询不统计字段为null 的数据

常用的分组查询   
    count(col): 表示求指定列的总行数
    max(col): 表示求指定列的最大值
    min(col): 表示求指定列的最小值
    sum(col): 表示求指定列的和
    avg(col): 表示求指定列的平均值

例: 

mysql> select *  from personinfo;
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
| id | name      | age  | gender | height | city   | addr                      | personId           | is_del |
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
|  2 | xiaozhang |   20 | 男     | 172.00 | NULL   | NULL                      | 510131198605171237 |        |
|  3 | xiaoliao  |   25 | 男     | 172.00 | 成都   | 高新区合作路1112号        | 510131198605106578 |        |
|  4 | xiaozhao  |   30 | 女     | 160.00 | 成都   | 双流区空港4路128号        | 510131198905101234 |        |
|  5 | xiaoliao  |   25 | 男     |   NULL | 重庆   | NULL                      | 510131198505101235 |        |
|  6 | xiaozhao  |   30 | 女     |   NULL | NULL   | NULL                      | 510131198505101236 |       |
+----+-----------+------+--------+--------+--------+---------------------------+--------------------+--------+
5 rows in set (0.00 sec)

#统计身高
mysql> select count(height) from personinfo;
+---------------+
| count(height) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

#统计身高 最大值
mysql> select max(height) from personinfo;
+-------------+
| max(height) |
+-------------+
|      172.00 |
+-------------+
1 row in set (0.00 sec)

#统计身高平均值
mysql> select avg(height) from personinfo;
+-------------+
| avg(height) |
+-------------+
|  168.000000 |
+-------------+
1 row in set (0.00 sec)

二、分组查询
分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。
    分组查询基本的语法格式如下：
    GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
    说明:
    列名: 是指按照指定字段的值进行分组。
    HAVING 条件表达式: 用来过滤分组后的数据。
    WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

2.1 group by可用于单个字段分组，也可用于多个字段分组

mysql> select * from personinfo;
+----+-----------+------+--------+--------+--------+--------------+--------------------+
| id | name      | age  | gender | height | city   | addr         | personId           |
+----+-----------+------+--------+--------+--------+--------------+--------------------+
|  1 | xiaozhang |   20 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136412 |
|  2 | xiaozhang |   20 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136413 |
|  3 | xiaowang  |   22 | 男     | 172.00 | 成都   | 成都高新     | 510131198605136414 |
|  4 | xiaowang  |   30 | 男     | 170.00 | 成都   | 成都高新     | 510131198605136415 |
|  5 | xiaozhao  |   25 | 女     | 165.00 | 重庆   | 重庆高新     | 510131198605136416 |
|  6 | xiaozhao  |   26 | 女     | 165.00 | 重庆   | 重庆高新     | 510131198605136417 |
+----+-----------+------+--------+--------+--------+--------------+--------------------+
6 rows in set (0.00 sec)

#以性别、名字进行分组，不会显示重复的
mysql> select gender,name from personinfo group by gender,name;
+--------+-----------+
| gender | name      |
+--------+-----------+
| 男     | xiaowang  |
| 男     | xiaozhang |
| 女     | xiaozhao  |
+--------+-----------+
3 rows in set (0.00 sec)

mysql> 

2.2  group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的 信息集合,会显示重复人名，每个信息之间使用逗号进行分割

mysql> select gender,group_concat(name) from personinfo group by gender;
+--------+---------------------------------------+
| gender | group_concat(name)                    |
+--------+---------------------------------------+
| 男     | xiaozhang,xiaozhang,xiaowang,xiaowang |
| 女     | xiaozhao,xiaozhao                     |
+--------+---------------------------------------+
2 rows in set (0.00 sec)

2.3 group by + 聚合函数的使用

统计不同身高下的平均年龄

mysql> select height,avg(age) from personinfo group by height;
+--------+----------+
| height | avg(age) |
+--------+----------+
| 165.00 |  25.5000 |
| 170.00 |  30.0000 |
| 172.00 |  20.6667 |
+--------+----------+
3 rows in set (0.00 sec)

统计不同身高下的人员个数

mysql> select height,count(name) from personinfo group by height;
+--------+-------------+
| height | count(name) |
+--------+-------------+
| 165.00 |           2 |
| 170.00 |           1 |
| 172.00 |           3 |
+--------+-------------+
3 rows in set (0.00 sec)

2.4  group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组后的数据，只能用于group by

统计不同身高下人员个数，并只统计身高大于170的
mysql> select height,count(name) from personinfo group by height having height >=170;
+--------+-------------+
| height | count(name) |
+--------+-------------+
| 170.00 |           1 |
| 172.00 |           3 |
+--------+-------------+
2 rows in set (0.00 sec)

2.5 group by + with rollup的使用

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

mysql> select height,count(name) from personinfo group by height;

根据height 字段进行分组，使用聚合函数统计每个身高的人数，并使用rollup 汇总总人数

mysql> select height,count(name) from personinfo group by height with rollup;
+--------+-------------+
| height | count(name) |
+--------+-------------+
| 165.00 |           2 |
| 170.00 |           1 |
| 172.00 |           3 |
|   NULL |           6 |
+--------+-------------+
4 rows in set (0.00 sec)