组团学

DQL-聚合函数

阅读 (162270)

SQL基本函数,聚合函数对一组值执行计算,并返回单个值,也被称为组函数。

聚合函数对一组值执行计算并返回单一的值。除 COUNT 以外,聚合函数忽略空值,如果COUNT函数的应用对象是一个确定列名,并且该列存在空值,此时COUNT仍会忽略空值。

所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时,都返回相同的值。聚合函数可以应用于查询语句的SELECT中,或者HAVING子句中,但不可用于WHERE语句中,因为WHERE是对逐条的行记录进行筛选。

一、聚合函数概述

1.1、什么是聚合函数

SQL基本函数,聚合函数对一组值执行计算,并返回单个值,也被称为组函数。 聚合函数经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。

1.2、聚合函数的特点

除了 COUNT 以外,聚合函数忽略空值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时,都返回相同的值。

1.3、常用聚合函数

count(字段名):统计总行数
sum(字段名):计算列总和
avg(字段名):求某一列平均值
min(字段名):求某一列的最小值
max(字段名):求某一列的最大值

1.4、聚合函数语法

格式:
select 聚合函数(字段名) from 数据库表名;

二、聚合函数应用

2.1、聚合函数基础应用

案例:

查看学生信息表中通过英语成绩统计所有的学生数

mysql> select count(english) as 学生人数 from students;
+-----+
| 总人数 |
+-----+
| 7   |
+-----+
1 行于数据集 (0.01 秒)

注意:

null的记录是不会统计,如果要想统计null,可以用ifnull(字段名,默认值)

默认值里设置:null用0表示

mysql> select count(ifnull(english,0)) as 总人数 from students;
+-----+
| 总人数 |
+-----+
| 9   |
+-----+
1 行于数据集 (0.02 秒)

除了前面这个方法可以统计学生人数外,我们还可以:

#比较常用的方法
mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
| 9        |
+----------+
1 行于数据集 (0.01 秒)

查看学生信息表中年龄大于19的总人数

mysql> select count(*) from students where age>19;
+----------+
| count(*) |
+----------+
| 2        |
+----------+
1 行于数据集 (0.02 秒)

查看学生信息表中英语成绩的总分

mysql> select sum(english) as 英语总分 from students;
+-------+
| 英语总分  |
+-------+
| 544.5 |
+-------+
1 行于数据集 (0.01 秒)

查看学生信息表中英语成绩的平均分

mysql> select avg(english) as 英语平均分 from students;
+----------+
| 英语平均分    |
+----------+
| 77.78571 |
+----------+
1 行于数据集 (0.01 秒)

查看学生信息表中英语成绩的最高分

mysql> select max(english) as 英语最高分 from students;
+-------+
| 英语最高分 |
+-------+
| 98.5  |
+-------+
1 行于数据集 (0.01 秒)

查看学生信息表中英语成绩的最低分

mysql> select min(english) as 英语最低分 from students;
+-------+
| 英语最低分 |
+-------+
| 50.0  |
+-------+
1 行于数据集 (0.01 秒)

2.2、聚合函数分组应用

案例:

统计学生信息表中男生的人数与女生的人数

mysql> select count(*),sex from students group by sex;
+----------+------+
| count(*) | sex  |
+----------+------+
| 6        | 男  |
| 3        | 女  |
+----------+------+
2 行于数据集 (0.01 秒)

统计学生信息表中男生和女生的人数、英语总成绩、数学平均成绩

mysql> select count(*),sex,sum(english),avg(math) from students group by sex;
+----------+------+--------------+-----------+
| count(*) | sex  | sum(english) | avg(math) |
+----------+------+--------------+-----------+
| 6        | 男  | 318.5        | 87.25000  |
| 3        | 女  | 226.0        | 82.66667  |
+----------+------+--------------+-----------+
2 行于数据集 (0.01 秒)

统计学生信息表中男生和女生的人数、英语总成绩、数学平均成绩,总人数超过3人显示

mysql> select count(*) as n,sex,sum(english),avg(math) from students group by sex having n>3;
+---+------+--------------+-----------+
| n | sex  | sum(english) | avg(math) |
+---+------+--------------+-----------+
| 6 | 男  | 318.5        | 87.25000  |
+---+------+--------------+-----------+
1 行于数据集 (0.02 秒)

三、聚合函数应用扩展

3.1、group_concat

group_concat(字段名)可以作为一个输出字段来使用,表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合。

案例:

统计学生信息表中男生和女生的人数、英语总成绩、数学平均成绩及数学成绩的集合

mysql> select count(*),sex,sum(english),avg(math),group_concat(math) from students group by sex;
+----------+------+--------------+-----------+---------------------+
| count(*) | sex  | sum(english) | avg(math) | group_concat(math)  |
+----------+------+--------------+-----------+---------------------+
| 3        | 女  | 226.0        | 82.66667  | 80.0,70.0,98.0      |
| 6        | 男  | 318.5        | 87.25000  | 88.0,88.0,85.0,88.0 |
+----------+------+--------------+-----------+---------------------+
2 行于数据集 (0.02 秒)

3.2、with rollup

在最后新增一行,来记录当前列里所有记录的总和

案例:

统计学生信息表中男生和女生的人数、英语总成绩、数学平均成绩及增加显示列的记录总和

mysql> select count(*),sex,sum(english),avg(math) from students group by sex with rollup;
+----------+------+--------------+-----------+
| count(*) | sex  | sum(english) | avg(math) |
+----------+------+--------------+-----------+
| 3        | 女  | 226.0        | 82.66667  |
| 6        | 男  | 318.5        | 87.25000  |
| 9        | NULL | 544.5        | 85.28571  |
+----------+------+--------------+-----------+
3 行于数据集 (0.02 秒)
需要 登录 才可以提问哦