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

準備

SQL：

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());

執行：

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語句返回某個資料庫表的單個列。但請看其輸出，並沒有特定的順序。

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子句取一個或多個列的名字，據此對輸出進行排序。

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個列，並按其中兩個列對結果進行排序——首先按價格，然後再按名稱排序。

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關鍵字。

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

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)

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

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的組合，能夠找出一個列中最高或最低的值。

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

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僅返回一行。