//查询年龄在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;
排序: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) ...