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