mysql从头开始（3）

排序检索数据

1.排序数据

1.1.1

下面的sql没有特定的顺序。

mysql> select prod_name from products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

其实，检索出的数据并不是以纯粹随机的顺序进行展示，如果不进行排序，数据一般以它在底层中出现的
顺序进行展示，这可能是他们最初添加的顺序，但是如果数据后来进行过更新或者删除，顺序会受到影响。

1.1.2

下面的sql有特定的排序。

mysql> 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) |
+----------------+
14 rows in set (0.00 sec)

**通过非选择列进行排序：**通常，order by 子句中使用的列是显示所选择的列，但是完全可以使用不是显示
的列。

1.1.3

按多个列进行排序

mysql> select * from products order by prod_price,prod_name;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.01 sec)

在进行按照多个排序时，排序完全按照所规定的顺序进行，先按给定的第一个字段，再按照第二个字段，以此类推。

1.1.4

指定排序方向
数据排序默认是升序，还可以使用order by的子句进行降序排序，为了进行降序排序必须指定DESC关键字。

mysql> select * from  products order by prod_price desc;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)

还可以指定方向多个排序。

mysql> select * from  products order by prod_price desc,prod_name;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)

注意1： DESC关键字只应用到直接位于其前面的列名，如果想在多个列上进行排序，所有的列都要指定关键字。
注意2： 区分大小写和排序顺序，在字典（dictionary）排序顺序中，A被视为与a相同，但是这个规则是可以进行改变的。

1.1.5

order by 和 limit

mysql> select prod_price from products order by prod_price desc limit 2;
+------------+
| prod_price |
+------------+
|      55.00 |
|      50.00 |
+------------+
2 rows in set (0.00 sec)

按照顺序检索，并且使用limit只获取固定的数据。