MySQL学习足迹记录02--SELECT_MySQL

MySQL学习足迹记录02--SELECT

本查询所用到的表格结构和数据

mysql> SHOW COLUMNS FROM products;  +------------+--------------+------+-----+---------+-------+| Field      | Type         | Null | Key | Default | Extra |+------------+--------------+------+-----+---------+-------+| prod_id    | char(10)     | NO   | PRI | NULL    |       || vend_id    | int(11)      | NO   | MUL | NULL    |       || prod_name  | char(255)    | NO   |     | NULL    |       || prod_price | decimal(8,2) | NO   |     | NULL    |       || prod_desc  | text         | YES  |     | NULL    |       |+------------+--------------+------+-----+---------+-------+mysql> select * from 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                                    |+---------+---------+----------------+------------+----------------------------------------------------------------+

1.检索单个列：

 eg:   mysql> SELECT prod_NAME FROM products;     #MYSQL并不区分大小写+----------------+| 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) |+----------------+

2.检索多个列

  eg:     mysql> 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 |+---------+----------------+------------+

3.检索所有列

  eg:   mysql> SELECT * FROM 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                                    |+---------+---------+----------------+------------+----------------------------------------------------------------+

4.检索不同的行

先列出所有的行以便作对比

 eg:    mysql> SELECT vend_id FROM products;+---------+| vend_id |+---------+|    1001 ||    1001 ||    1001 ||    1002 ||    1002 ||    1003 ||    1003 ||    1003 ||    1003 ||    1003 ||    1003 ||    1003 ||    1005 ||    1005 |+---------+ *DISTINGCT关键字便可以把相同的行去掉 mysql> SELECT DISTINCT vend_id FROM products;+---------+| vend_id |+---------+|    1001 ||    1002 ||    1003 ||    1005 |+---------+

5.限制结果

  *限制返回前几行  eg:   mysql> SELECT prod_name FROM products LIMIT 5;+--------------+| prod_name    |+--------------+| .5 ton anvil || 1 ton anvil  || 2 ton anvil  || Detonator    || Bird seed    |+--------------+ *限制返回从从第N行开始（下标从0开始），一直持续M行结束  eg:    mysql> SELECT prod_name FROM products LIMIT 5,5;+--------------+| prod_name    |+--------------+| Carrots      || Fuses        || JetPack 1000 || JetPack 2000 || Oil can      |+--------------+

6.使用完全限定的表名

  eg:   mysql> SELECT products.prod_name FROM MySQL_ex.products;  #products为表名，MySQL_ex为数据库名                                                                +----------------+| 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) |+----------------+