讀書筆記--SQL必知必會03--排序檢索數據

3.1 排序數據

子句（clause）
SQL語句由子句構成。一個子句通常由一個關鍵字加上所提供的數據組成。
ORDER BY子句可以取一個或多個列的名字，將SELECT語句檢索出的數據進行排序。
ORDER BY子句可以使用非檢索的列排序數據。
ORDER BY子句必須作為SELECT語句中最后一條子句。

MariaDB [sqlbzbh]> SELECT prod_name FROM Products ORDER BY prod_name;
+---------------------+
| prod_name           |
+---------------------+
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| 8 inch teddy bear   |
| Bird bean bag toy   |
| Fish bean bag toy   |
| King doll           |
| Queen doll          |
| Rabbit bean bag toy |
| Raggedy Ann         |
+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]> 
MariaDB [sqlbzbh]> SELECT prod_name FROM Products ORDER BY vend_id;
+---------------------+
| prod_name           |
+---------------------+
| 8 inch teddy bear   |
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| Fish bean bag toy   |
| Bird bean bag toy   |
| Rabbit bean bag toy |
| Raggedy Ann         |
| King doll           |
| Queen doll          |
+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]> 

3.2 按多個列排序

要按多個列排序，簡單指定列名，列名之間用逗號分開即可。
在按多個列排序時，排序的順序完全按規定進行。也就是說，僅在前一列的多個行具有相同的值時，才按照下一列進行排序。

MariaDB [sqlbzbh]> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]> 

3.3 按列位置排序

ORDER BY支持按相對列位置進行排序。
優點是不用重新輸入列名，缺點是容易造成錯誤排序。個人不建議使用。
如果有必要，可以混合使用實際列名和相對列位置。

MariaDB [sqlbzbh]> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]> 

3.4 制定排序方向

默認的排序順序是升序排序（從A到Z）。
可以使用ORDER BY子句進行降序排序（從Z到A），但必須指定DESC關鍵字。
DESC關鍵字只應用到直接位于其前面的列名。如果想在多個列上進行降序排序，必須對每一列指定DESC關鍵字。
DESC是DESCENDING的縮寫，這兩個關鍵字都可以使用。
ASC是ASCENDING的縮寫，因為升序是默認的，所以不必指定關鍵字。

MariaDB [sqlbzbh]> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]>  
MariaDB [sqlbzbh]> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    |      11.99 | 18 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR02    |       8.99 | 12 inch teddy bear  |
| BR01    |       5.99 | 8 inch teddy bear   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]> 
MariaDB [sqlbzbh]> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC, prod_name;
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    |      11.99 | 18 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR02    |       8.99 | 12 inch teddy bear  |
| BR01    |       5.99 | 8 inch teddy bear   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)MariaDB [sqlbzbh]>