mysql必知必会（二）排序数据（order by）

布满荆棘的人生 2023-02-22 12:14 8阅读 0赞

### 文章目录 ###

*  按一列排序
 *  按多列排序
 *  指定排序方向：desc
 *  limit和order by组合
 *  总结

![在这里插入图片描述][20200703133620293.png]

# 按一列排序 #

# 按多列排序 #

![在这里插入图片描述][20200703133742106.png]

mysql> select prod_id, prod_price, prod_name
        -> from products
        -> order by prod_price, prod_name;
    +---------+------------+---------------------+
    | prod_id | prod_price | prod_name           |
    +---------+------------+---------------------+
    | BNBG02  |       3.49 | Bird bean bag toy   |
    | BNBG01  |       3.49 | Fish bean bag toy   |
    | BNBG03  |       3.49 | Rabbit bean bag toy |
    | RGAN01  |       4.99 | Raggedy Ann         |
    | BR01    |       5.99 | 8 inch teddy bear   |
    | BR02    |       8.99 | 12 inch teddy bear  |
    | RYL01   |       9.49 | King doll           |
    | RYL02   |       9.49 | Queen doll          |
    | BR03    |      11.99 | 18 inch teddy bear  |
    +---------+------------+---------------------+
    9 rows in set (0.00 sec)

![在这里插入图片描述][20200703133843912.png]

# 指定排序方向：desc #

mysql> select prod_id, prod_price, prod_name
        -> from products
        -> order by prod_price desc;
    +---------+------------+---------------------+
    | prod_id | prod_price | prod_name           |
    +---------+------------+---------------------+
    | BR03    |      11.99 | 18 inch teddy bear  |
    | RYL01   |       9.49 | King doll           |
    | RYL02   |       9.49 | Queen doll          |
    | BR02    |       8.99 | 12 inch teddy bear  |
    | BR01    |       5.99 | 8 inch teddy bear   |
    | RGAN01  |       4.99 | Raggedy Ann         |
    | BNBG01  |       3.49 | Fish bean bag toy   |
    | BNBG02  |       3.49 | Bird bean bag toy   |
    | BNBG03  |       3.49 | Rabbit bean bag toy |
    +---------+------------+---------------------+
    9 rows in set (0.00 sec)

mysql> select prod_id, prod_price, prod_name
        -> from products
        -> order by prod_price desc, prod_name;
    +---------+------------+---------------------+
    | prod_id | prod_price | prod_name           |
    +---------+------------+---------------------+
    | BR03    |      11.99 | 18 inch teddy bear  |
    | RYL01   |       9.49 | King doll           |
    | RYL02   |       9.49 | Queen doll          |
    | BR02    |       8.99 | 12 inch teddy bear  |
    | BR01    |       5.99 | 8 inch teddy bear   |
    | RGAN01  |       4.99 | Raggedy Ann         |
    | BNBG02  |       3.49 | Bird bean bag toy   |
    | BNBG01  |       3.49 | Fish bean bag toy   |
    | BNBG03  |       3.49 | Rabbit bean bag toy |
    +---------+------------+---------------------+
    9 rows in set (0.00 sec)

可见desc只作用于其前面的1列，而之前的distinct会作用于其后的所有列  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70 1]

# limit和order by组合 #

选出最大值

mysql> select prod_price
        -> from products
        -> order by prod_price desc
        -> limit 1;
    +------------+
    | prod_price |
    +------------+
    |      11.99 |
    +------------+
    1 row in set (0.00 sec)

返回最大的前4个

mysql> select prod_price
        -> from products
        -> order by prod_price desc
        -> limit 4;
    +------------+
    | prod_price |
    +------------+
    |      11.99 |
    |       9.49 |
    |       9.49 |
    |       8.99 |
    +------------+

![在这里插入图片描述][20200703134416196.png]

# 总结 #

*  order by子句必须在from子句后面；limit子句必须在order by子句后面

[20200703133620293.png]: https://img-blog.csdnimg.cn/20200703133620293.png
[20200703133742106.png]: https://img-blog.csdnimg.cn/20200703133742106.png
[20200703133843912.png]: https://img-blog.csdnimg.cn/20200703133843912.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200703134130437.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200703134145257.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NjA3ODk0,size_16,color_FFFFFF,t_70
[20200703134416196.png]: https://img-blog.csdnimg.cn/20200703134416196.png