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

系统管理员 2022-11-27 12:09 178阅读 0赞

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