MYSQL之简单语句查询二-优快云博客

本文深入探讨了SQL语言中关键的查询技巧，包括条件筛选、排序、聚合函数使用、分组统计及分页操作，旨在帮助读者提升数据处理效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

排序: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;