day02-排序检索数据
order by如何搭配DESC使用
排序数据
SELECT prod_name
FROM Products;
输出：
mysql> SELECT prod_name
-> FROM Products;
+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)
SELECT prod_name
FROM Products
ORDER BY prod_name;
输出：
mysql> 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)
mysql>
按多个列排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
输出：
mysql> 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)
mysql>
按列位置排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
输出：
mysql> 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)
mysql>
注意：
1.ORDER BY 2
表示按 SELECT 清单中的第二个列 prod_price 进行排序。ORDER BY 2，3 表示先按 prod_price，再按 prod_name 进行排序。
2.这一技术的主要好处在于不用重新输入列名。但它也有缺点。首先，不明确给出列名有可能造成错用列名排序。其次，在对 SELECT 清单进行更改时容易错误地对数据进行排序（忘记对 ORDER BY 子句做相应的改动）。
3.最后，如果进行排序的列不在 SELECT 清单中，显然不能使用这项技术。
指定排序方向
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
输出：
mysql> 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)
mysql>
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
输出：
mysql> 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)
mysql>
注意：
1.数据排序不限于升序排序（从 A 到 Z），这只是默认的排序顺序。还可以使用 ORDER BY 子句进行降序（从 Z 到 A）排序。为了进行降序排序，必须指定 DESC 关键字。
小练习
1.编写 SQL 语句，从 Customers 中检索所有的顾客名称（cust_names）， 并按从 Z 到 A 的顺序显示结果。
SELECT cust_name
FROM Customers
ORDER BY cust_name DESC;
输出：
mysql> SELECT cust_name
-> FROM Customers
-> ORDER BY cust_name DESC;
+---------------+
| cust_name |
+---------------+
| Village Toys |
| The Toy Store |
| Kids Place |
| Fun4All |
| Fun4All |
+---------------+
5 rows in set (0.00 sec)
mysql>
2.编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号 （order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id, order_date DESC;
输出：
mysql> SELECT cust_id, order_num
-> FROM Orders
-> ORDER BY cust_id, order_date DESC;
+------------+-----------+
| cust_id | order_num |
+------------+-----------+
| 1000000001 | 20005 |
| 1000000001 | 20009 |
| 1000000003 | 20006 |
| 1000000004 | 20007 |
| 1000000005 | 20008 |
+------------+-----------+
5 rows in set (0.00 sec)
mysql>
3. 显然，我们的虚拟商店更喜欢出售比较贵的物品，而且这类物品有很多。 编写 SQL 语句，显示 OrderItems 表中的数量和价格（item_price）， 并按数量由多到少、价格由高到低排序
SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC, item_price DESC;
输出：
mysql> SELECT quantity, item_price
-> FROM OrderItems
-> ORDER BY quantity DESC, item_price DESC;
+----------+------------+
| quantity | item_price |
+----------+------------+
| 250 | 2.49 |
| 250 | 2.49 |
| 250 | 2.49 |
| 100 | 10.99 |
| 100 | 5.49 |
| 100 | 2.99 |
| 100 | 2.99 |
| 100 | 2.99 |
| 50 | 11.49 |
| 50 | 4.49 |
| 20 | 5.99 |
| 10 | 11.99 |
| 10 | 8.99 |
| 10 | 3.49 |
| 10 | 3.49 |
| 10 | 3.49 |
| 5 | 11.99 |
| 5 | 4.99 |
+----------+------------+
18 rows in set (0.00 sec)
mysql>
4.下面的 SQL 语句有问题吗？（尝试在不运行的情况下指出。）
SELECT vend_name,
FROM Vendors
ORDER vend_name DESC;
vend_name 后不应有逗号（逗号仅用于分隔多列），并且 ORDER 后缺少 BY。
浙公网安备 33010602011771号