MySQL聚合函数、分组、分页

1. count,计数

统计学生数据表中男性数量
select count(*) from students where gender=‘男’;

2. max,查找最大值

查找学生表中的最大年龄
select max(age) from students;

3. min,查找最小值

查找最小年龄值
select min(age) from students;

4. sum，求和

学生的年龄总和
select sum(age) from students;

5. avg，求平均值

计算年龄平均值
select avg(age) from students;

6. round(xxx.xxx,n)，四舍五入小数点后保留几位，n表示保留位数

学生平均年龄小数点保留一位
select round(avg(age),1) from students;

7. group by，分组

1. 按性别分组
   select gender from students group by gender;
2. 分组并统计每个组的数量
   select gender, count(*) from students group by gender;
3. 分组并计算每个组的平均值
   select gender,avg(age) from students group by gender;
4. 分组并展示每组包含的信息
   select gender,group_concat(name) from students group by gender;
5. 统计男性人数
   select gender,count(*) from students where gender=‘男’ group by gender;
6. 统计所有男性的姓名
   select gender,group_concat(name) from students where gender=‘男’ group by gender;
7. 统计所有男性的信息
   select gender,group_concat(name,’ 年龄：’,age) from students where gender=‘男’ group by gender;
8. 只显示平均年龄大于30的分组
   select gender,avg(age) fromst from students group by gender having avg(age)>30;

8. 分页

1. limit，限制数量
   值显示5条数据
   select * from students limit 5;
   指定从哪条数据开始显示
   select * from students limit 1,5; 1表示从下标为1的开始，5表示限制数量
   显示2条女性信息并按身高升序排列
   select * from students where gender=’女’ order by height limit 2;