526互联

MySql必知必会教程--排序检索数据

发布时间 2023-06-09 15:41:53作者: 99号的格调

重点关键字：order by

排序单列数据：

select prod_name from products order by prod_name;
输出结果：
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

排序多列数据：

select prod_id,prod_price,prod_name from products order by prod_price,prod_name;
输出结果：
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
| FU1     |       3.42 | Fuses          |
| SLING   |       4.49 | Sling          |
| ANV01   |       5.99 | .5 ton anvil   |
| OL1     |       8.99 | Oil can        |
| ANV02   |       9.99 | 1 ton anvil    |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| DTNTR   |      13.00 | Detonator      |
| ANV03   |      14.99 | 2 ton anvil    |
| JP1000  |      35.00 | JetPack 1000   |
| SAFE    |      50.00 | Safe           |
| JP2000  |      55.00 | JetPack 2000   |
+---------+------------+----------------+

注意：如果prod_price的值都是唯一的，则不会按照prod_name去排序

将检索结果按照降序排列（关键字：DESC，指定列按照降序排列）

select prod_id,prod_price,prod_name from products order by prod_price DESC,prod_name;
输出结果：
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| JP2000  |      55.00 | JetPack 2000   |
| SAFE    |      50.00 | Safe           |
| JP1000  |      35.00 | JetPack 1000   |
| ANV03   |      14.99 | 2 ton anvil    |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| ANV02   |       9.99 | 1 ton anvil    |
| OL1     |       8.99 | Oil can        |
| ANV01   |       5.99 | .5 ton anvil   |
| SLING   |       4.49 | Sling          |
| FU1     |       3.42 | Fuses          |
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
+---------+------------+----------------+

结合limit找出该表中价格最高的物品：

select prod_id,prod_price,prod_name from products order by prod_price DESC limit 1;
输出结果：
+---------+------------+--------------+
| prod_id | prod_price | prod_name    |
+---------+------------+--------------+
| JP2000  |      55.00 | JetPack 2000 |
+---------+------------+--------------+

注意：order by 与 limit混合使用的时候，limit必须放置在order by的后面。