mysql 03(排序、聚合函数、分组、分页)

MySQL表里的内容接上篇文章（MySQL 02（查询））

1、排序
oder by、asc、desc

# 查询 18到20的学生，从小到大排序
select * from students where age between 18 and 20 order
by age;
# order后面省略了asc（默认从小到大排序）

# 查询 18到20的学生，从大到小排序
select * from students where age between 18 and 20 order
by age desc;

2、多个条件排序

# 查询 18到20的学生，先按年龄从大到小排序，有相同年龄再按身高从小到大排序
select * from students where age between 18 and 20 order
by age desc,height asc;

3、聚合函数
count、max、min、avg、sum、round

# cuont
# 计算18岁的有多少人
select count(*) from students where age=18;
select count(*) as 18岁的人数 from students where age=18;

# max最大值、min最小值
# 找到年龄最高是多少
select max(age) from students;

# sum、avg
# 求平均年龄（两种方法）
select sum(age)/count(*) from students;
select avg(age) from students;

# round(数，保留几位小数) 四舍五入法保留
select round(avg(age),2) from students;

4、分组
group by

# 按性别分组
select gender from students group by gender;

# 计算每种性别中的人数
select gender,count(*) from students group by gender;

# 查看每个组里的姓名都有哪些
select gender,group_concat(name) from students group by gender;

# 查看每个组里的姓名都有哪些
select gender,group_concat(name) from students group by gender;
select gender,group_concat(name，id) from students group by gender;
select gender,group_concat(name) from students where gender=1 group by gender;

having（对查询结果集进行条件筛选）

# 查询每种性别中的人数大于2个的信息
select gender,group_concat(name) from students group by gender having count(*);

# 查询平均年龄超过19岁的性别，以及其对应姓名
select gender,group_concat(name,id),avg(age) from students group by gender having avg(age)>19;

where和having的区别
where：对查询分组前进行条件筛选
having：对查询分组后进行条件筛选，一般放在最后
5、分页
limit

# 直接限制查询出来的数据个数为2
select * from students where gender=1 limit=2;

# 显示前5个数据
select * from students  limit 0,5;

# limit （第N页-1）*每页个数，每页个数
# 每页显示3个，第1页
select * from students  limit 0,3;
# 每页显示3个，第2页
select * from students  limit 3,3;
# 每页显示3个，第3页
select * from students  limit 6,3;

注意：limit在所有关键字最后