MySQL学习足迹记录02--SELECT

柔光的暖阳◎ 2022-01-12 14:57 241阅读 0赞

***本查询所用到的表格结构和数据***  
  
  
**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) |  
\+----------------+