mysql 聚合函数 分组
聚合函数:
总数 count
查询所有男性记录数量:
select count(*) from students where gender=“男”;
最大值 max
查询最大年龄:
select max(age) from students;
最小值 min
查询最小年龄:
select min(age) from students;
求和 sum
查询所有年龄总和:
select sum(age) from students;
平均值 avg
查询所有人的平均年龄:
select avg(age) from students;
select avg(age) from students; #这个例子说明select后面可以支持表达式
四舍五入 round
round(12.1223, 1) #后面的1是指保留1位小数,0为不保留
查询所有人的平均年龄,保留1位小数:
1、select round((sum(age)/count(*)),1) from students;
2、select round(avg(age),1) from students;
分组 group by
group by和聚合函数一起用
按性别分组,查看有多少性别:
select gender from students group by gender;
查看每种性别中的人数:
select gender, count() from students group by gender;#此时的count()是对分组进行计算个数,而不是整个表
查看分组中最大年龄:
select gender,max(age) from students group by gender;
查看每个分组中的姓名:
select gender,group_concat(name) from students group by gender;
查看所有男性的数量:
select gender,count(*) from students where gender=1 group by gender;
查看所有男性的数量,并列出名字:
1、select gender,count(*),group_concat(name) from students where gender=1 group by gender;
2、select gender,count(*),group_concat(name) from students where gender=1;
查看男性人数、年龄、名字:
1、select gender,count(*),group_concat(name,age) from students where gender=1 group by gender;
2、select gender,count(*),group_concat(name,age) from students where gender=1;
group_concat()括号中写什么有什么,甚至可以写字符串,例如将上面查询中的名字和年龄之间插入一个"_":
select dept,count(*),group_concat(name,"_",age)from students where dept=“hr”;
having
对查询出的条件结果进行判断,放在group by 的后面,where指定查询位置,放在group by的前面
查询表中平均年龄大于30的性别:
select gender,group_concat(name) from students group by gender having avg(age)>30;
查询表中数量超过3的性别:
select gender,group_concat(name) from students group by gender having count(*)>3;
查询表中年龄大于30的:
select age,group_concat(name) from students group by age having age>30;
查询表中年龄超过30的男性:
select age,group_concat(name) from students where gender=1 group by age having age>30;
转自:https://www.cnblogs.com/Huiw/articles/10301632.html