黑马程序员---mysql数据库---排序与分页

一：排序：
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