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