【Mysql】 查询数据排序以及聚合函数

# 排序
  # order by 字段
  # asc 从小到大排序，即升序
  # desc从大到小排序，即降序
  
  # 查询年龄在18到34岁之间的男性，按照年龄从小到大排序
  select * from students where (age between 18 and 34) and gender=1 order by age;
  select * from students where (age between 18 and 34) and gender=1 order by age asc;
  # 查询年龄在18到34岁之间的男性，按照年龄从小到大排序
  select * from students where (age between 18 and 34) and gender=1 order by age desc;
  # 查询年龄在18到34岁之间的女性，身高从高到矮排序
  select * from students where age between 18 and 34 and gender=2 order by height desc; 
 
  # order by 多个字段
  # 查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照序列从小到大排序
  select * from students where (age between 18 and 34) and gender=2 order by height desc,id desc;

  # 聚合函数
  # count
  # 查询男性有多少人
  select count(*) from students where gender=1;
  select count(*) as 男性人数 from students where gender=1;
  
  # 最大值
  # 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 sum(age)/count(*) from students;

  # 四舍五入，保留两位小数
  select round(sum(age)/count(*),2) from students;
  
  # 计算男性的平均身高,保留两位小数
  select round(sum(height)/count(*),2) from students where gender=1;
  

  # 分组
  # 按照性别分组，查询所有的性别
  select gender from students group by gender;
  select gender,count(*) from students group by gender; 
  # count(*) 是对前面的分组每组进行求数据
  
  # group_concat 
  select gender,group_concat(name) from students group by gender;
  select gender,group_concat(name,"_",age,"_",id) from students where gender=1 group by gender;

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

  # where 和 having 的区别 where在group by 前面 ，having 在group by 后面 ，where 是在对原表进行判断，having是在对判断分组之后的表格
 
  select gender,group_concat(name) from stundents group by gender having count(*)>2;