Mysql必知必会教程--【检索数据】

首先给出本次教程需要用到的数据表的内容：products

+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| 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)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+

 

检索单个列：

select prod_id from products;
输出结果：
+---------+
| prod_id |
+---------+
| ANV01   |
| ANV02   |
| ANV03   |
| FU1     |
| OL1     |
| DTNTR   |
| FB      |
| FC      |
| SAFE    |
| SLING   |
| TNT1    |
| TNT2    |
| JP1000  |
| JP2000  |
+---------+

检索多个列：

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

检索不同值的列表：

select distinct vend_id from products;
输出结果：
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+

检索结果限定行数：

select prod_desc from products limit 5;
输出结果：
+----------------------------------------------------------------+
| prod_desc                                                      |
+----------------------------------------------------------------+
| .5 ton anvil, black, complete with handy hook                  |
| 1 ton anvil, black, complete with handy hook and carrying case |
| 2 ton anvil, black, complete with handy hook and carrying case |
| Detonator (plunger powered), fuses not included                |
| Large bag (suitable for road runners)                          |
+----------------------------------------------------------------+

检索数据限定行数范围：

select prod_desc from products limit 3,4;
输出结果：
+-------------------------------------------------+
| prod_desc                                       |
+-------------------------------------------------+
| Detonator (plunger powered), fuses not included |
| Large bag (suitable for road runners)           |
| Carrots (rabbit hunting season only)            |
| 1 dozen, extra long                             |
+-------------------------------------------------+

这里的第一个3表示从第三行还是get，4表示要取多少条数据，从第三行开始，拿4条数据，可对比文章刚开始的表内容。