一:排序:
1:查询年龄在18到34岁之间的男性,按照年龄从小到大到排序(默认是asc升序)
mysql> select * from students where (age between 18 and 34) and (gender="男") order by age ;
+----+--------------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------------+------+--------+--------+--------+-----------+
| 9 | 程坤 | 27 | 181.00 | 男 | 2 | |
| 16 | 司马二狗 | 28 | 120.00 | 男 | 1 | |
| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |
+----+--------------+------+--------+--------+--------+-----------+
3 rows in set (0.00 sec)
2:查询年龄在18到34岁之间的女性,身高从高到矮排序
mysql> select * from students where (age between 18 and 34) and (gender="女") order by height desc ;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 15 | 凌小小 | 28 | 180.00 | 女 | 1 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+-----------+------+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
3:查询年龄在18到34岁之间的女性,身高从高到矮排序, 如果身高相同的情况下按照年龄从大到小排序
mysql> select * from students where (age between 18 and 34) and (gender="女") order by height desc ,age desc;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 15 | 凌小小 | 28 | 180.00 | 女 | 1 | |
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+-----------+------+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
4:查询年龄在18到34岁之间的女性,身高从高到矮排序, 如果身高相同的情况下按照年龄从大到小排序,如果年龄也相同那么按照id从大到小排序
mysql> select * from students where (age between 18 and 34) and (gender="女") order by height desc ,age desc,id desc;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 15 | 凌小小 | 28 | 180.00 | 女 | 1 | |
| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |
| 1 | 小明 | 18 | 180.00 | 女 | 1 | |
| 14 | 周杰 | 34 | 176.00 | 女 | 5 | |
| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |
| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |
+----+-----------+------+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
二:分页:
1:分页查询的语法
select * from 表名 limit start,count
说明:
limit是分页查询关键字
start表示开始行索引,默认是0
count表示查询条数
例1:查询前3行男生信息:
select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;
2:分页查询案例
已知每页显示m条数据,求第n页显示的数据
提示: 关键是求每页的开始行索引
查询学生表,获取第n页数据的SQL语句:
select * from students limit (n-1)*m,m