【mysql】聚合函数和分组查询

1.聚合函数

对一组函数进行统计和计算的函数

聚合函数	说明
count()	返回总个数
max()	返回最大值
min()	返回最小值
sum()	返回总和
avg()	返回平均值

• ifnull()
  此函数可以判断是否为空值。使用聚合函数进行统计或计算会自动忽略空值，若想遇到空值使用指定的值代替，可以使用ifnull()函数

select avg(ifnull(height,0)) from students;

• round()
  此函数可以将小数四舍五入到指定位数

将身高平均值保留2位小数
select round(avg(height),2) from students;

2.分组查询

根据性别进行分组
select gender from students group by gender;

2.1 group by + group_concat()

select gender,group_concat(name) from students group by gender;

2.2 group by + 聚合函数

select gender,avg(height),max(age),min(age) from students group by gender;

2.3 group by + having

select gender,count(*),group_concat(name) from students group by gender having count(*)>2;

2.4 group by + with rollup

select gender,count(*),max(age),avg(height) from students group by gender with rollup;