Like Share Discussion Bookmark Smile

J.J. Huang   2020-06-12   MySQL   瀏覽次數:

MySQL - 第五章 | 排序檢索資料

準備

SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`price` int(11) NOT NULL DEFAULT '0' COMMENT '價格',
`prod_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '產品',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('100', 'underwear', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('222', 'boxers', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('111', 'sleepwear', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('20', 'socks', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('55', 'slippers', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('100', 'sandals', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('599', 'flip_flops', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('666', 'pants', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('399', 'long_sleeve_shirt', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('1000', 'blazer', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('9999', 'sweater', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('345', 'tie', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('123', 'dress_shoes', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('654', 'T-shirt', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('643', 'polo', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('234', 'short_sleeve_shirt', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('1233', 'jeans', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('877', 'shorts', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('236', 'swimsuit', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('724', 'casual_shoes', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('123', 'jacket', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('690', 'coat', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('890', 'raincoat', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('777', 'hat', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('3000', 'gloves', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('1234', 'scarf', now());
INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('9999', 'belt', now());

執行:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> CREATE TABLE `products` (
-> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
-> `price` int(11) NOT NULL DEFAULT '0' COMMENT '',
-> `prod_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '',
-> `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('100', 'underwear', now());
Query OK, 1 row affected (0.00 sec)

...中間省略

mysql> INSERT INTO `testdb`.`products` (`price`, `prod_name`, `create_time`) VALUES ('9999', 'belt', now());
Query OK, 1 row affected (0.00 sec)

排序資料

正如前一章所述,下面的SQL語句返回某個資料庫表的單個列。但請看其輸出,並沒有特定的順序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select prod_name from products;
+--------------------+
| prod_name |
+--------------------+
| underwear |
| boxers |
| sleepwear |
| socks |
| slippers |
| sandals |
| flip_flops |
| pants |
| long_sleeve_shirt |
| blazer |
| sweater |
| tie |
| dress_shoes |
| T-shirt |
| polo |
| short_sleeve_shirt |
| jeans |
| shorts |
| swimsuit |
| casual_shoes |
| jacket |
| coat |
| raincoat |
| hat |
| gloves |
| scarf |
| belt |
+--------------------+
27 rows in set (0.00 sec)

其實,檢索出的資料並不是以純粹的隨機順序顯示的。如果不排序,資料一般將以它在底層表中出現的順序顯示。這可以是資料最初添加到表中的順序。但是,如果資料後來進行過更新或刪除,則此順序將會受到MySQL重用回收存儲空間的影響。因此,如果不明確控制的話,不能(也不應該)依賴該排序順序。關係資料庫設計理論認為,如果不明確規定排序順序,則不應該假定檢索出的資料的順序有意義。

術語:子句(clause) SQL語句由子句構成,有些子句是必需的,而有的是可選的。一個子句通常由一個關鍵字和所提供的資料組成。子句的例子有SELECT語句的FROM子句,我們在前一章看到過這個子句。

為了明確地排序用SELECT語句檢索出的資料,可使用ORDER BY子句。ORDER BY子句取一個或多個列的名字,據此對輸出進行排序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select prod_name from products order by prod_name;
+--------------------+
| prod_name |
+--------------------+
| belt |
| blazer |
| boxers |
| casual_shoes |
| coat |
| dress_shoes |
| flip_flops |
| gloves |
| hat |
| jacket |
| jeans |
| long_sleeve_shirt |
| pants |
| polo |
| raincoat |
| sandals |
| scarf |
| shorts |
| short_sleeve_shirt |
| sleepwear |
| slippers |
| socks |
| sweater |
| swimsuit |
| T-shirt |
| tie |
| underwear |
+--------------------+
27 rows in set (0.00 sec)

分析:這條語句除了指示MySQL對prod_name列以字母順序排序資料的ORDER BY子句外,與前面的語句相同。

提示:通過非選擇列進行排序 通常,ORDERBY子句中使用的列將是為顯示所選擇的列。但是,實際上並不一定要這樣,用非檢索的列排序資料是完全合法的。

按多個列排序

經常需要按不止一個列進行資料排序。例如,如果要顯示僱員清單,可能希望按姓和名排序(首先按姓排序,然後在每個姓中再按名排序)。如果多個僱員具有相同的姓,這樣做很有用。
為了按多個列排序,只要指定列名,列名之間用逗號分開即可(就 像選擇多個列時所做的那樣)。
下面的程式碼檢索3個列,並按其中兩個列對結果進行排序——首先按價格,然後再按名稱排序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select id, price, prod_name from products order by price, prod_name;
+----+-------+--------------------+
| id | price | prod_name |
+----+-------+--------------------+
| 4 | 20 | socks |
| 5 | 55 | slippers |
| 6 | 100 | sandals |
| 1 | 100 | underwear |
| 3 | 111 | sleepwear |
| 13 | 123 | dress_shoes |
| 21 | 123 | jacket |
| 2 | 222 | boxers |
| 16 | 234 | short_sleeve_shirt |
| 19 | 236 | swimsuit |
| 12 | 345 | tie |
| 9 | 399 | long_sleeve_shirt |
| 7 | 599 | flip_flops |
| 15 | 643 | polo |
| 14 | 654 | T-shirt |
| 8 | 666 | pants |
| 22 | 690 | coat |
| 20 | 724 | casual_shoes |
| 24 | 777 | hat |
| 18 | 877 | shorts |
| 23 | 890 | raincoat |
| 10 | 1000 | blazer |
| 17 | 1233 | jeans |
| 26 | 1234 | scarf |
| 25 | 3000 | gloves |
| 27 | 9999 | belt |
| 11 | 9999 | sweater |
+----+-------+--------------------+
27 rows in set (0.00 sec)

重要的是理解在按多個列排序時,排序完全按所規定的順序進行。換句話說,對於上述例子中的輸出,僅在多個行具有相同的price值時才對產品按prod_name進行排序。如果price列中所有的值都是唯一的,則不會按prod_name排序。

指定排序方向

資料排序不限於升序排序(從A到Z)。這只是默認的排序順序,還可以使用ORDER BY子句以降序(從Z到A)順序排序。為了進行降序排序, 必須指定DESC關鍵字。

下面的例子按價格以降序排序產品(最貴的排在最前面):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select id, price, prod_name from products order by price desc;
+----+-------+--------------------+
| id | price | prod_name |
+----+-------+--------------------+
| 27 | 9999 | belt |
| 11 | 9999 | sweater |
| 25 | 3000 | gloves |
| 26 | 1234 | scarf |
| 17 | 1233 | jeans |
| 10 | 1000 | blazer |
| 23 | 890 | raincoat |
| 18 | 877 | shorts |
| 24 | 777 | hat |
| 20 | 724 | casual_shoes |
| 22 | 690 | coat |
| 8 | 666 | pants |
| 14 | 654 | T-shirt |
| 15 | 643 | polo |
| 7 | 599 | flip_flops |
| 9 | 399 | long_sleeve_shirt |
| 12 | 345 | tie |
| 19 | 236 | swimsuit |
| 16 | 234 | short_sleeve_shirt |
| 2 | 222 | boxers |
| 13 | 123 | dress_shoes |
| 21 | 123 | jacket |
| 3 | 111 | sleepwear |
| 1 | 100 | underwear |
| 6 | 100 | sandals |
| 5 | 55 | slippers |
| 4 | 20 | socks |
+----+-------+--------------------+
27 rows in set (0.00 sec)

如果打算用多個列排序怎麼辦?下面的例子以降序排序產品(最貴的在最前面),然後再對產品名排序:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select id, price, prod_name from products order by price desc, prod_name;
+----+-------+--------------------+
| id | price | prod_name |
+----+-------+--------------------+
| 27 | 9999 | belt |
| 11 | 9999 | sweater |
| 25 | 3000 | gloves |
| 26 | 1234 | scarf |
| 17 | 1233 | jeans |
| 10 | 1000 | blazer |
| 23 | 890 | raincoat |
| 18 | 877 | shorts |
| 24 | 777 | hat |
| 20 | 724 | casual_shoes |
| 22 | 690 | coat |
| 8 | 666 | pants |
| 14 | 654 | T-shirt |
| 15 | 643 | polo |
| 7 | 599 | flip_flops |
| 9 | 399 | long_sleeve_shirt |
| 12 | 345 | tie |
| 19 | 236 | swimsuit |
| 16 | 234 | short_sleeve_shirt |
| 2 | 222 | boxers |
| 13 | 123 | dress_shoes |
| 21 | 123 | jacket |
| 3 | 111 | sleepwear |
| 6 | 100 | sandals |
| 1 | 100 | underwear |
| 5 | 55 | slippers |
| 4 | 20 | socks |
+----+-------+--------------------+
27 rows in set (0.00 sec)

分析:DESC關鍵字只應用到直接位於其前面的列名。在上例中,只對price列指定DESC,對prod_name列不指定。因此,price列以降序排序,而prod_name列(在每個價格內)仍然按標準的升序排序。

提示:在多個列上降序排序 如果想在多個列上進行降序排序,必須對每個列指定DESC關鍵字。

與DESC相反的關鍵字是ASC(ASCENDING),在升序排序時可以指定它。但實際上,ASC沒有多大用處,因為升序是默認的(如果既不指定ASC也不指定DESC,則假定為ASC)。

提示:區分大小寫和排序順序 在對文本性的資料進行排序時,A與a相同嗎?a位於B之前還是位於Z之後?這些問題不是理論問題,其答案取決於資料庫如何設置。
在字典(dictionary)排序順序中,A被視為與a相同,這是MySQL(和大多數資料庫管理系統)的默認行為。但是,許多資料庫管理員能夠在需要時改變這種行為(如果你的資料庫包含大量外語字符,可能必須這樣做)。
這裡,關鍵的問題是,如果確實需要改變這種排序順序,用簡單的ORDER BY子句做不到。你必須請求資料庫管理員的幫助。

使用ORDER BY和LIMIT的組合,能夠找出一個列中最高或最低的值。

下面的例子演示如何找出最昂貴物品的值:

1
2
3
4
5
6
7
mysql> select id, price, prod_name from products order by price desc limit 1;
+----+-------+-----------+
| id | price | prod_name |
+----+-------+-----------+
| 27 | 9999 | belt |
+----+-------+-----------+
1 row in set (0.00 sec)

分析:price DESC保證行是按照由最昂貴到最便宜檢索的,而LIMIT 1告訴MySQL僅返回一行。

注意:ORDERBY子句的位置 在給出ORDERBY子句時,應該保證它位於FROM子句之後。如果使用LIMIT,它必須位於ORDER BY之後。使用子句的次序不對將產生錯誤消息。

結語

這邊在對排序上特別做說明,這個排序往往最常使用在專案上的日期部分,像是註冊的時間排序,或是每日每月的報表產出的排序。
而其中我覺得比較要特別說的就是大小寫的部分,這在開發專案的過程中,必須要在一開始就定義好,因為在專案開發會需要接觸不同的資料庫,每個資料庫特性又不同,這點在處理的時候要特別小心。

註:以上參考了
MySQL必知必会 MySQL Crash Course