MYSQL之简单语句查询二

• 排序:order

//查询年龄在20-30之间的男性,按照年龄从小到大;
//默认就是asc从小到大排序,desc:从大到小排序;
select * from students where (age between 20 and 30) and gender=1 order by age;
select * from students where (age between 20 and 30) and gender=1 order by age asc;
//order by 后面可以跟多个参数:依次比较参数,如果相等则按照后一个的规则进行比较
select * from students where (age between 20 and 30) and gender=1 order by age asc,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

//round(值,小数点位数)
select round(sum(age)/count(*),2) from student;
select round(avg(age),2) from students where gender=1;

• 分组:
• group by:

select gender from students gourp by gender;

select gender max(age) from students gourp by gender;

select gender avg(age) from students gourp by gender;
//group_concat查看组内包含的name列表;
select gender group_concat(name) from students gourp by gender;
select gender group_concat(name,age,id) from students gourp by gender;
select gender group_concat(name,"_",age,"_",id) from students gourp by gender;

//查看男性的总数:
select gender count(*) from students where gender=1 gourp by gender;

//查询平均年龄大于30分组;
select gender group_concat(name,avg(age)) from students where gender=1 gourp by gender having avg(age) > 30;

• 分页:

  //限制查询个数
  select * from students where gender=1 limit 2;
  
  //查询从1开始到4的数据
  select * from students where gender=1 limit 1,4;
  
  //limit需要放在最后边
  select * from students order by age desc limit 0,5;