欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL学习足迹记录02--SELECT_MySQL

程序员文章站 2024-04-05 12:16:18
...
bitsCN.com

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) |+----------------+


bitsCN.com
相关标签: mysql 记录