MySQL 查詢的排序、分頁相關(guān)

更新時間：2020年11月13日 10:59:35 作者：翁智華

這篇文章主要介紹了MySQL 查詢的排序、分頁相關(guān)的相關(guān)知識，幫助大家更好的理解和使用數(shù)據(jù)庫，感興趣的朋友可以了解下

概述

數(shù)據(jù)庫中的數(shù)據(jù)直接呈現(xiàn)出來一般不是我們想要的，所以我們上兩節(jié)演示了如何對數(shù)據(jù)進行過濾的方法。除了對數(shù)據(jù)進行過濾，

我們可能還需要對數(shù)據(jù)進行排序，比如想從列表中了解消費最高的項，就可能需要對金額字段做降序排序，想看年齡從小到大的分布情況，就可能需要對user表的age字段進行升序排序。

也可能需要對數(shù)據(jù)進行限制，比如我們需要對付款的1~10，11~20，21~30 名的用戶分別贈予不同的禮品，這時候?qū)?shù)據(jù)的限制就很有用了。

備注：下面腳本中[]包含的表示可選，| 分隔符表示可選其一。

數(shù)據(jù)排序 order by

語法格式如下：

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

2、asc 和 desc表示排序的規(guī)則，asc：升序，desc：降序，默認為升序 asc；

3、排序可以指定多次字段，多字段排序之間用逗號隔開。

4、多字段排序中，越靠前優(yōu)先級越高，下面中cname1優(yōu)先排序，當(dāng)cname1等值的時候，cname2開始排序，直至所有字段都排序完。

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

單個字段排序

舉個例子，在銷售額中通按照交易的訂單進行金額額度降序的方式顯示：

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

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

多個字段排序

多個字段排序用逗號隔開，優(yōu)先級從左到右逐次遞減，如下圖，如果金額一致，則按照購買商品數(shù)量從多到少排序：

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

按alias排序

按照別名排序或者做條件查詢的目的都是為了簡化代碼，方便使用，別名可以是英文，也可以是中文：

mysql> select account as ac,amount as am,goods as gd from t_order order by am,gd desc;

+-------+---------+----+
| ac  | am   | gd |
+-------+---------+----+
| diny | 12   | 1 |
| weng | 52.2  | 5 |
| brand | 52.2  | 2 |
| helyn | 88.5  | 4 |
| sally | 99.71  | 9 |
| sol  | 1007.9 | 11 |
| hen  | 1752.02 | 7 |
+-------+---------+----+
7 rows in set

字段排序中使用函數(shù)

下面使用了abs取絕對值函數(shù)，所以在 am字段降序排序中，-99.99 排在 99.71之上。

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

mysql> select account as ac,amount as am,goods as gd from t_order order by abs(am) desc;

+--------+---------+----+
| ac   | am   | gd |
+--------+---------+----+
| hen  | 1752.02 | 7 |
| sol  | 1007.9 | 11 |
| brand1 | -99.99 | 5 |
| sally | 99.71  | 9 |
| helyn | 88.5  | 4 |
| brand | 52.2  | 2 |
| weng  | 52.2  | 5 |
| diny  | 12   | 1 |
+--------+---------+----+
8 rows in set

與Where條件結(jié)合使用

order 在 where 條件之后，根據(jù)where已經(jīng)過濾好的數(shù)據(jù)再進行排序。下面是過濾出購買金額>80 且購買數(shù)量>5的數(shù)據(jù)，并且按照價格降序排序。

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

mysql> select * from t_order where amount>80 and goods>5 order by amount desc;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
|    9 | hen   | 1752.02 |   7 |
|   11 | sol   | 1007.9 |  11 |
|   14 | sally  | 99.71  |   9 |
+---------+---------+---------+-------+

數(shù)據(jù)limit

很多時候我們過濾出符合要求的數(shù)據(jù)之后，還需要得到這些數(shù)據(jù)中的某一個具體區(qū)間，比如對付款超過1000的用戶的第1~10，11~20，21~30 名分別贈予不同的禮品，這時候就要使用limit操作了。

limit用來限制select查詢返回的數(shù)據(jù)，常用于數(shù)據(jù)排行或者分頁等情況。

語法格式如下：

select cname from tname limit [offset,] count;

1、offset表示偏移量，就是指跳過的行數(shù)，可以省略不寫，默認為0，表示跳過0行，如 limit 8 等同于 limit 0,8。

2、count：跳過偏移量offset之后開始取的數(shù)據(jù)行數(shù)，有count行。

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

獲取前n條記錄

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

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

mysql> select * from t_order limit 2
;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
|    8 | brand  | 52.2  |   2 |
|    9 | hen   | 1752.02 |   7 |
+---------+---------+---------+-------+
2 rows in set

mysql> select * from t_order limit 0,2;
+---------+---------+---------+-------+
| orderid | account | amount | goods |
+---------+---------+---------+-------+
|    8 | brand  | 52.2  |   2 |
|    9 | hen   | 1752.02 |   7 |
+---------+---------+---------+-------+
2 rows in set

limit限制單條記錄

這邊我們獲取支付金額中最大和最小的的一條記錄?？梢韵仁褂?order 條件進行排序，然后limit 第1條記錄即可：

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

 mysql> select * from t_order where amount>0 order by amount desc limit 1;
 +---------+---------+---------+-------+
 | orderid | account | amount | goods |
 +---------+---------+---------+-------+
 |    9 | hen   | 1752.02 |   7 |
 +---------+---------+---------+-------+
 1 row in set

 mysql> select * from t_order where amount>0 order by amount asc limit 1;
 +---------+---------+--------+-------+
 | orderid | account | amount | goods |
 +---------+---------+--------+-------+
 |   12 | diny  | 12   |   1 |
 +---------+---------+--------+-------+
 1 row in set

以上就是MySQL 查詢的排序、分頁相關(guān)的詳細內(nèi)容，更多關(guān)于MySQL 查詢的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: