概述
數(shù)據(jù)庫(kù)中的數(shù)據(jù)直接呈現(xiàn)出來(lái)一般不是我們想要的，所以我們上兩節(jié)演示了如何對(duì)數(shù)據(jù)進(jìn)行過(guò)濾的方法。除了對(duì)數(shù)據(jù)進(jìn)行過(guò)濾，
我們可能還需要對(duì)數(shù)據(jù)進(jìn)行排序，比如想從列表中了解消費(fèi)最高的項(xiàng)，就可能需要對(duì)金額字段做降序排序，想看年齡從小到大的分布情況，就可能需要對(duì)user表的age字段進(jìn)行升序排序。
也可能需要對(duì)數(shù)據(jù)進(jìn)行限制，比如我們需要對(duì)付款的1~10，11~20，21~30 名的用戶分別贈(zèng)予不同的禮品，這時(shí)候?qū)?shù)據(jù)的限制就很有用了。
備注：下面腳本中[]包含的表示可選，| 分隔符表示可選其一。
數(shù)據(jù)排序 order by
語(yǔ)法格式如下：
1、需要排序的字段跟在order by之后；
2、asc 和 desc表示排序的規(guī)則，asc：升序，desc：降序，默認(rèn)為升序 asc；
3、排序可以指定多次字段，多字段排序之間用逗號(hào)隔開。
4、多字段排序中，越靠前優(yōu)先級(jí)越高，下面中cname1優(yōu)先排序，當(dāng)cname1等值的時(shí)候，cname2開始排序，直至所有字段都排序完。
select cname from tname order by cname1 [asc|desc],cname2 [asc|desc]...;
單個(gè)字段排序
舉個(gè)例子，在銷售額中通按照交易的訂單進(jì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 |
+---------+---------+---------+-------+
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
多個(gè)字段排序
多個(gè)字段排序用逗號(hào)隔開，優(yōu)先級(jí)從左到右逐次遞減，如下圖，如果金額一致，則按照購(gò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排序
按照別名排序或者做條件查詢的目的都是為了簡(jiǎn)化代碼，方便使用，別名可以是英文，也可以是中文：
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取絕對(duì)值函數(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)過(guò)濾好的數(shù)據(jù)再進(jìn)行排序。下面是過(guò)濾出購(gòu)買金額>80 且 購(gòu)買數(shù)量>5的數(shù)據(jù)，并且按照價(jià)格降序排序。
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í)候我們過(guò)濾出符合要求的數(shù)據(jù)之后，還需要得到這些數(shù)據(jù)中的某一個(gè)具體區(qū)間，比如對(duì)付款超過(guò)1000的用戶的第1~10，11~20，21~30 名分別贈(zèng)予不同的禮品，這時(shí)候就要使用limit操作了。
limit用來(lái)限制select查詢返回的數(shù)據(jù)，常用于數(shù)據(jù)排行或者分頁(yè)等情況。
語(yǔ)法格式如下：
select cname from tname limit [offset,] count;
1、offset表示偏移量，就是指跳過(guò)的行數(shù)，可以省略不寫，默認(rèn)為0，表示跳過(guò)0行，如 limit 8 等同于 limit 0,8。
2、count：跳過(guò)偏移量offset之后開始取的數(shù)據(jù)行數(shù)，有count行。
3、limit中offset和count的值不能用表達(dá)式。
獲取前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 條件進(jìn)行排序，然后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 查詢的排序、分頁(yè)相關(guān)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 查詢的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- MySQL排序原理和案例詳析
- MySQL利用索引優(yōu)化ORDER BY排序語(yǔ)句的方法
- Mysql排序和分頁(yè)(order by&limit)及存在的坑
- MySQL如何使用union all獲得并集排序
- Mysql8.0使用窗口函數(shù)解決排序問(wèn)題
- MySQL單表查詢操作實(shí)例詳解【語(yǔ)法、約束、分組、聚合、過(guò)濾、排序等】
- 基于mysql 默認(rèn)排序規(guī)則的坑
