MYSQL第6章 聚合函数和分组查询

一

一 、 聚合函数介绍

1、在数据库查询过程中，不仅只返回数据的基础信息，有时还需对这些数据进行统计和汇总。MySQL 提供了聚合函数，用于实现这些高级功能。

2、聚合函数用于对一组值进行计算并返回一个汇总值，使用聚合函数可以统计记录行数、计算某个字段值的总和以及这些值的最大值、最小值和平均值等。

函数名称	功能
sum	返回选取的某列值的总和
max	返回选取的某列的最大值
min	返回选取的某列的最小值
avg	返回选取的某列的平均值
count	返回选取的某列或记录的行数

3、示例

计算所有线路总里程数：

select sum(miles) 线路总里程数 from line; 

计算公交二公司所属线路总里程数：

select sum(miles) 线路总里程数 from line where company=' 公交二公司 ';

对所有线路的总站点数进行汇总：

select sum(number) 线路总站点数 from line;

查询年龄最大的公交司机的出生年月：

select min(birthday) 最年长司机生日 , ceil(datediff(curdate(),min(birthday))/365) 该司机年龄 from driver;

 内置函数 curdate() 显示当前日期。  内置函数 datediff(date1,date2) 用于计算日期 date1 和 date2 之间相隔的天数。  内置函数 ceil(num1) 的值为大于等于 num1 的最小整数。

二 、avg 函数 

1、avg(column) 用于返回数字型字段 column 的平均值，注意：列值为 null 的字段不参与运算。

示例：

获取线路里程的平均值：

select avg(miles) 平均里程数 from line;

获取公交二公司所属线路的平均里程数：

select avg(miles) 平均里程数 from line where company=' 公交二公司 ';

2、count 函数

count 函数用于统计记录行数。使用 count 函数时，必须指定一个列的名称或使用“*”。使用“*”可获取整张表的记录行数。注意：列值为 null 的字段不参与运算。

统计所有公交线路的数量：

select count(*) 线路数 from line;   或 select count(lineNo) 线路数 from line;

三 、分组查询 group by 子句

group by 子句通常与聚合函数同时使用，如 max()、min()、count()、sum() 和 avg()。

示例：

统计非“通恒公司”所属线路的数量和营运里程的平均值、最大值和最小值，并按照线路数升序显示查询结果：

select company 公司 , count(*) 线路数 , truncate(avg(miles),1) 平均线路里程 , max(miles) 最长线路里程 , min(miles) 最短线路里程 from line where company<> ' 通恒公司 ' group by company order by count(*)

四 、多字段分组和 having 子句

group by 子句的分组字段是一个字段列表，即 MySQL 支持按多个字段进行分组。具体的分组策略：分组优先级从左至右，即先按第一个字段进行分组，然后在第一个字段值相同的记录中，再根据第二个字段的值进行分组， 依次类推

示例：

依据公交线路所属公司和营运车辆数，分组统计平均营运里程：

select company 公司 , number 营运车辆数 , truncate(avg(miles),1) 平均营运里程 from line group by company, number

五 、总结

(1) count 和 sum 函数的区别：

sum 函数是对符合条件的记录的数值列求和，count 函数的值是满足查询条件的结果（或记录）个数。

count(column) 用于统计 column 列不为 null 的记录个数。

(2) 如何对查询结果进行过滤之后再分组统计：

先使用 where 子句表达式对查询结果进行过滤筛选。然后，再使用 group by 子句对 where 子句的输出进行分组统计。  

(3)having 子句的用法：

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据。

having 子句的位置放在 group by 子句之后，常包含聚合函数。