MySQL全面瓦解9：查询的排序、分页相关

最新推荐文章于 2024-05-20 14:38:13 发布

smart哥

最新推荐文章于 2024-05-20 14:38:13 发布

阅读量628

点赞数 13

分类专栏： mysql专题文章标签： mysql

本文链接：https://blog.csdn.net/smart_an/article/details/138324415

版权

mysql专题专栏收录该内容

29 篇文章 0 订阅

订阅专栏

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

学习必须往深处挖，挖的越深，基础越扎实！

阶段1、深入多线程

 阶段2、深入多线程设计模式

 阶段3、深入juc源码解析

阶段4、深入jdk其余源码解析

阶段5、深入jvm源码解析

码哥源码部分

码哥讲源码-原理源码篇【2024年最新大厂关于线程池使用的场景题】

码哥讲源码【炸雷啦！炸雷啦！黄光头他终于跑路啦！】

码哥讲源码-【jvm课程前置知识及c/c++调试环境搭建】

码哥讲源码-原理源码篇【揭秘join方法的唤醒本质上决定于jvm的底层析构函数】

码哥源码-原理源码篇【Doug Lea为什么要将成员变量赋值给局部变量后再操作？】

码哥讲源码【你水不是你的错,但是你胡说八道就是你不对了！】

码哥讲源码【谁再说Spring不支持多线程事务，你给我抽他！】

终结B站没人能讲清楚红黑树的历史，不服等你来踢馆！

打脸系列【020-3小时讲解MESI协议和volatile之间的关系，那些将x86下的验证结果当作最终结果的水货们请闭嘴】

概述

数据库中的数据直接呈现出来一般不是我们想要的，所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤，

我们可能还需要对数据进行排序，比如想从列表中了解消费最高的项，就可能需要对金额字段做降序排序，想看年龄从小到大的分布情况，就可能需要对user表的age字段进行升序排序。

也可能需要对数据进行限制，比如我们需要对付款的1~10，11~20，21~30 名的用户分别赠予不同的礼品，这时候对数据的限制就很有用了。

备注：下面脚本中[]包含的表示可选，| 分隔符表示可选其一。

数据排序 order by

语法格式如下：

1、需要排序的字段跟在order by之后；

2、asc 和 desc表示排序的规则，asc：升序，desc：降序，默认为升序 asc；

3、排序可以指定多次字段，多字段排序之间用逗号隔开。

4、多字段排序中，越靠前优先级越高，下面中cname1优先排序，当cname1等值的时候，cname2开始排序，直至所有字段都排序完。

    1 select cname from tname order by cname1 [asc|desc],cname2 [asc|desc]...;

单个字段排序

举个例子，在销售额中通按照交易的订单进行金额额度降序的方式显示：

     1 mysql> select * from t_order;
     2 +---------+---------+---------+-------+
     3 | orderid | account | amount  | goods |
     4 +---------+---------+---------+-------+
     5 |       8 | brand   | 52.2    |     2 |
     6 |       9 | hen     | 1752.02 |     7 |
     7 |      10 | helyn   | 88.5    |     4 |
     8 |      11 | sol     | 1007.9  |    11 |
     9 |      12 | diny    | 12      |     1 |
    10 |      13 | weng    | 52.2    |     5 |
    11 |      14 | sally   | 99.71   |     9 |
    12 +---------+---------+---------+-------+
    13 7 rows in set
    14 
    15 mysql> select * from t_order order by amount desc;
    16 +---------+---------+---------+-------+
    17 | orderid | account | amount  | goods |
    18 +---------+---------+---------+-------+
    19 |       9 | hen     | 1752.02 |     7 |
    20 |      11 | sol     | 1007.9  |    11 |
    21 |      14 | sally   | 99.71   |     9 |
    22 |      10 | helyn   | 88.5    |     4 |
    23 |       8 | brand   | 52.2    |     2 |
    24 |      13 | weng    | 52.2    |     5 |
    25 |      12 | diny    | 12      |     1 |
    26 +---------+---------+---------+-------+
    27 7 rows in set

多个字段排序

多个字段排序用逗号隔开，优先级从左到右逐次递减，如下图，如果金额一致，则按照购买商品数量从多到少排序：

     1 mysql> select * from t_order order by amount desc,goods desc;
     2 +---------+---------+---------+-------+
     3 | orderid | account | amount  | goods |
     4 +---------+---------+---------+-------+
     5 |       9 | hen     | 1752.02 |     7 |
     6 |      11 | sol     | 1007.9  |    11 |
     7 |      14 | sally   | 99.71   |     9 |
     8 |      10 | helyn   | 88.5    |     4 |
     9 |      13 | weng    | 52.2    |     5 |
    10 |       8 | brand   | 52.2    |     2 |
    11 |      12 | diny    | 12      |     1 |
    12 +---------+---------+---------+-------+
    13 7 rows in set

按alias排序

按照别名排序或者做条件查询的目的都是为了简化代码，方便使用，别名可以是英文，也可以是中文：

     1 mysql> select account as ac,amount as am,goods as gd from t_order order by am,gd desc;
     2    
     3 +-------+---------+----+
     4 | ac    | am      | gd |
     5 +-------+---------+----+
     6 | diny  | 12      |  1 |
     7 | weng  | 52.2    |  5 |
     8 | brand | 52.2    |  2 |
     9 | helyn | 88.5    |  4 |
    10 | sally | 99.71   |  9 |
    11 | sol   | 1007.9  | 11 |
    12 | hen   | 1752.02 |  7 |
    13 +-------+---------+----+
    14 7 rows in set

字段排序中使用函数

下面使用了abs取绝对值函数，所以在 am字段降序排序中，-99.99 排在 99.71之上。

     1 mysql> select * from t_order;
     2 +---------+---------+---------+-------+
     3 | orderid | account | amount  | goods |
     4 +---------+---------+---------+-------+
     5 |       8 | brand   | 52.2    |     2 |
     6 |       9 | hen     | 1752.02 |     7 |
     7 |      10 | helyn   | 88.5    |     4 |
     8 |      11 | sol     | 1007.9  |    11 |
     9 |      12 | diny    | 12      |     1 |
    10 |      13 | weng    | 52.2    |     5 |
    11 |      14 | sally   | 99.71   |     9 |
    12 |      15 | brand1  | -99.99  |     5 |
    13 +---------+---------+---------+-------+
    14 8 rows in set
    15 
    16 mysql> select account as ac,amount as am,goods as gd from t_order order by abs(am) desc;
    17    
    18 +--------+---------+----+
    19 | ac     | am      | gd |
    20 +--------+---------+----+
    21 | hen    | 1752.02 |  7 |
    22 | sol    | 1007.9  | 11 |
    23 | brand1 | -99.99  |  5 |
    24 | sally  | 99.71   |  9 |
    25 | helyn  | 88.5    |  4 |
    26 | brand  | 52.2    |  2 |
    27 | weng   | 52.2    |  5 |
    28 | diny   | 12      |  1 |
    29 +--------+---------+----+
    30 8 rows in set

与Where条件结合使用

order 在 where 条件之后，根据where已经过滤好的数据再进行排序。下面是过滤出购买金额>80 且购买数量>5的数据，并且按照价格降序排序。

     1 mysql> select * from t_order;
     2 +---------+---------+---------+-------+
     3 | orderid | account | amount  | goods |
     4 +---------+---------+---------+-------+
     5 |       8 | brand   | 52.2    |     2 |
     6 |       9 | hen     | 1752.02 |     7 |
     7 |      10 | helyn   | 88.5    |     4 |
     8 |      11 | sol     | 1007.9  |    11 |
     9 |      12 | diny    | 12      |     1 |
    10 |      13 | weng    | 52.2    |     5 |
    11 |      14 | sally   | 99.71   |     9 |
    12 |      15 | brand1  | -99.99  |     5 |
    13 +---------+---------+---------+-------+
    14 8 rows in set
    15 
    16 mysql> select * from t_order where amount>80 and goods>5 order by amount desc;
    17 +---------+---------+---------+-------+
    18 | orderid | account | amount  | goods |
    19 +---------+---------+---------+-------+
    20 |       9 | hen     | 1752.02 |     7 |
    21 |      11 | sol     | 1007.9  |    11 |
    22 |      14 | sally   | 99.71   |     9 |
    23 +---------+---------+---------+-------+

数据limit

很多时候我们过滤出符合要求的数据之后，还需要得到这些数据中的某一个具体区间，比如对付款超过1000的用户的第1~10，11~20，21~30 名分别赠予不同的礼品，这时候就要使用limit操作了。

limit用来限制select查询返回的数据，常用于数据排行或者分页等情况。

语法格式如下：

    1 select cname from tname limit [offset,] count;

1、offset表示偏移量，就是指跳过的行数，可以省略不写，默认为0，表示跳过0行，如 limit 8 等同于 limit 0,8。

2、count：跳过偏移量offset之后开始取的数据行数，有count行。

3、limit中offset和count的值不能用表达式。

获取前n条记录

如下图，limit n 和 limit 0,n 是一致的：

     1 mysql> select * from t_order;
     2 +---------+---------+---------+-------+
     3 | orderid | account | amount  | goods |
     4 +---------+---------+---------+-------+
     5 |       8 | brand   | 52.2    |     2 |
     6 |       9 | hen     | 1752.02 |     7 |
     7 |      10 | helyn   | 88.5    |     4 |
     8 |      11 | sol     | 1007.9  |    11 |
     9 |      12 | diny    | 12      |     1 |
    10 |      13 | weng    | 52.2    |     5 |
    11 |      14 | sally   | 99.71   |     9 |
    12 |      15 | brand1  | -99.99  |     5 |
    13 +---------+---------+---------+-------+
    14 8 rows in set
    15 
    16 mysql> select * from t_order limit 2
    17 ;
    18 +---------+---------+---------+-------+
    19 | orderid | account | amount  | goods |
    20 +---------+---------+---------+-------+
    21 |       8 | brand   | 52.2    |     2 |
    22 |       9 | hen     | 1752.02 |     7 |
    23 +---------+---------+---------+-------+
    24 2 rows in set
    25 
    26 mysql> select * from t_order limit 0,2;
    27 +---------+---------+---------+-------+
    28 | orderid | account | amount  | goods |
    29 +---------+---------+---------+-------+
    30 |       8 | brand   | 52.2    |     2 |
    31 |       9 | hen     | 1752.02 |     7 |
    32 +---------+---------+---------+-------+
    33 2 rows in set

limit限制单条记录

这边我们获取支付金额中最大和最小的的一条记录。可以先使用 order 条件进行排序，然后limit 第1条记录即可：

     1  1 mysql> select * from t_order;
     2  2 +---------+---------+---------+-------+
     3  3 | orderid | account | amount  | goods |
     4  4 +---------+---------+---------+-------+
     5  5 |       8 | brand   | 52.2    |     2 |
     6  6 |       9 | hen     | 1752.02 |     7 |
     7  7 |      10 | helyn   | 88.5    |     4 |
     8  8 |      11 | sol     | 1007.9  |    11 |
     9  9 |      12 | diny    | 12      |     1 |
    10 10 |      13 | weng    | 52.2    |     5 |
    11 11 |      14 | sally   | 99.71   |     9 |
    12 12 |      15 | brand1  | -99.99  |     5 |
    13 13 +---------+---------+---------+-------+
    14 14 8 rows in set
    15 15 
    16 16 mysql> select * from t_order where amount>0 order by amount desc limit 1;
    17 17 +---------+---------+---------+-------+
    18 18 | orderid | account | amount  | goods |
    19 19 +---------+---------+---------+-------+
    20 20 |       9 | hen     | 1752.02 |     7 |
    21 21 +---------+---------+---------+-------+
    22 22 1 row in set
    23 23 
    24 24 mysql> select * from t_order where amount>0 order by amount asc limit 1;
    25 25 +---------+---------+--------+-------+
    26 26 | orderid | account | amount | goods |
    27 27 +---------+---------+--------+-------+
    28 28 |      12 | diny    | 12     |     1 |
    29 29 +---------+---------+--------+-------+
    30 30 1 row in set
    31

获取(m,n)区间记录

即跳过m条，获取n条，示例如下，我们跳过两条，从第三条开始，连取四条的操作：

     1 mysql> select * from t_order order by amount;
     2 +---------+---------+---------+-------+
     3 | orderid | account | amount  | goods |
     4 +---------+---------+---------+-------+
     5 |      15 | brand1  | -99.99  |     5 |
     6 |      12 | diny    | 12      |     1 |
     7 |       8 | brand   | 52.2    |     2 |
     8 |      13 | weng    | 52.2    |     5 |
     9 |      10 | helyn   | 88.5    |     4 |
    10 |      14 | sally   | 99.71   |     9 |
    11 |      11 | sol     | 1007.9  |    11 |
    12 |       9 | hen     | 1752.02 |     7 |
    13 +---------+---------+---------+-------+
    14 8 rows in set
    15 
    16 mysql> select * from t_order order by amount limit 2,4;
    17 +---------+---------+--------+-------+
    18 | orderid | account | amount | goods |
    19 +---------+---------+--------+-------+
    20 |       8 | brand   | 52.2   |     2 |
    21 |      13 | weng    | 52.2   |     5 |
    22 |      10 | helyn   | 88.5   |     4 |
    23 |      14 | sally   | 99.71  |     9 |
    24 +---------+---------+--------+-------+
    25 4 rows in set

分页的做法与这个类似，我们程序业务上看到的分页一般有 pageIndex，pageSize等参数，我们通常的做法是 limit pageIndex*pageSize,pageSize。

这边假设有31条数据，每页数量pageSize=10，页面索引pageIndex默认0，则第一页就是 limit 0,10，第二页 limit10,10，第三页 limit 20,10，第四页 limit 30,10。

注意点：

1、limit 后面不能使用表达式，只能使用明确的数值，否则会爆出异常，比如 limit 0*10,10，是不对的，这个上面提过了。

2、limit后续的数值只能是正整数和0，也就是说，不能是负数，否则同样会报错。

3、排序字段的值相同情况下，排序后分页会出现混乱重复的情况。

第3点详细说明下：假如根据age排序，但是有多个age都是20岁的同学，这时候我们3条记录一页，就会出现分页混乱数据重复。因为年龄相同的人有多个，这是几个人的排序在每次查询的时候会有不确定性。

举个例子，下面的分页，混乱了：

     1 mysql> select * from user3;
     2 +----+------+-------+
     3 | id | age  | name  |
     4 +----+------+-------+
     5 |  1 |   20 | brand |
     6 |  2 |   22 | sol   |
     7 |  3 |   20 | helen |
     8 |  4 | 19.5 | diny  |
     9 |  6 |   19 | a     |
    10 |  7 |   20 | b     |
    11 |  8 |   20 | c     |
    12 |  9 |   20 | d     |
    13 | 10 |   20 | e     |
    14 | 11 |   23 | f     |
    15 +----+------+-------+
    16 10 rows in set
    17 
    18 mysql> select * from user3 order by age limit 0,3; 
    19 +----+------+-------+
    20 | id | age  | name  |
    21 +----+------+-------+
    22 |  6 |   19 | a     |
    23 |  4 | 19.5 | diny  |
    24 |  3 |   20 | helen |
    25 +----+------+-------+
    26 3 rows in set
    27 
    28 mysql> select * from user3 order by age limit 3,3;
    29  
    30 +----+-----+-------+
    31 | id | age | name  |
    32 +----+-----+-------+
    33 |  3 |  20 | helen |
    34 |  7 |  20 | b     |
    35 |  8 |  20 | c     |
    36 +----+-----+-------+
    37 3 rows in set
    38 
    39 mysql> select * from user3 order by age limit 6,3;
    40  
    41 +----+-----+-------+
    42 | id | age | name  |
    43 +----+-----+-------+
    44 |  7 |  20 | b     |
    45 |  3 |  20 | helen |
    46 |  2 |  22 | sol   |
    47 +----+-----+-------+
    48 3 rows in set

我们的做法是使用重复值字段做排序的时候再加个唯一依据（一般可以设主键），就不会混乱了。

如下示例，正常了：

     1 mysql> select * from user3;
     2 +----+------+-------+
     3 | id | age  | name  |
     4 +----+------+-------+
     5 |  1 |   20 | brand |
     6 |  2 |   22 | sol   |
     7 |  3 |   20 | helen |
     8 |  4 | 19.5 | diny  |
     9 |  6 |   19 | a     |
    10 |  7 |   20 | b     |
    11 |  8 |   20 | c     |
    12 |  9 |   20 | d     |
    13 | 10 |   20 | e     |
    14 | 11 |   23 | f     |
    15 +----+------+-------+
    16 10 rows in set
    17 
    18 mysql> select * from user3 order by age,id limit 0,3;
    19  
    20 +----+------+-------+
    21 | id | age  | name  |
    22 +----+------+-------+
    23 |  6 |   19 | a     |
    24 |  4 | 19.5 | diny  |
    25 |  1 |   20 | brand |
    26 +----+------+-------+
    27 3 rows in set
    28 
    29 mysql> select * from user3 order by age,id limit 3,3;
    30  
    31 +----+-----+-------+
    32 | id | age | name  |
    33 +----+-----+-------+
    34 |  3 |  20 | helen |
    35 |  7 |  20 | b     |
    36 |  8 |  20 | c     |
    37 +----+-----+-------+
    38 3 rows in set
    39 
    40 mysql> select * from user3 order by age,id limit 6,3;
    41  
    42 +----+-----+------+
    43 | id | age | name |
    44 +----+-----+------+
    45 |  9 |  20 | d    |
    46 | 10 |  20 | e    |
    47 |  2 |  22 | sol  |
    48 +----+-----+------+
    49 3 rows in set

上述总结

1、order by cname [asc|desc] 用于对查询结果排序，asc为升序，desc为降序，可以省略，省略情况下默认为asc。

2、limit用来限制查询结果返回的行数，包含2个参数（offset，count），offset：表示跳过多少行，count：表示跳过offset行之后取的行数。limit中offset可以省略，默认值为0；limit中offset 和 count都必须大于等于0；limit中offset和count的值不能用表达式。

3、分页排序时，排序字段不要有重复值，重复值情况下可能会导致分页结果混乱，建议在后面加一个主键排序。

smart哥

关注

13
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
MySQL全面瓦解9：查询的排序、分页相关

数据库中的数据直接呈现出来一般不是我们想要的，所以我们上两节演示了如何对数据进行过滤的方法。除了对数据进行过滤，我们可能还需要对数据进行排序，比如想从列表中了解消费最高的项，就可能需要对金额字段做降序排序，想看年龄从小到大的分布情况，就可能需要对user表的age字段进行升序排序。也可能需要对数据进行限制，比如我们需要对付款的1~10，11~20，21~30 名的用户分别赠予不同的礼品，这时候对数据的限制就很有用了。备注：下面脚本中[]包含的表示可选，| 分隔符表示可选其一。
复制链接

扫一扫