使用SELECT语句从表中检索出一个或多个数据列
一、检索单个列
select prod_name from products; 从products表中检索出一个名为prod_name的列（未排序的数据，如果没有明确排序查询结果，则返回的数据的顺序没有特殊意义）
mysql> select prod_name from products;
+----------------+
| 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) |
+----------------+
14 rows in set (0.00 sec)
二、SQL语句不区分大小写，大家习惯对所有SQL关键字使用大写，而对所有列和表名使用小写，易于阅读和调试
在处理SQL语句时，所有空格都被忽略，SQL语句可以在一行上给出，也可以分成许多行
三、检索多个列
SELECT prod_id,prod_name,prod_price FROM products; 使用逗号将多个列分隔开
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 |
+---------+----------------+------------+
14 rows in set (0.00 sec)
四、检索所有列
使用通配符*来表示返回表中的所有列，列的顺序是列在表定义中出现的顺序
（检索不需要的列会降低检索和应用程序的性能）
SELECT * FROM products;
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 |
+---------+---------+----------------+------------+----------------------------------------------------------------+
五、检索不同的行
DISTINCT关键字指示MySQL只返回不同的值，DISTINCT关键字放在列名的前面。并且DISTINCT关键字应用于所有列而不仅是前置它的列。比如SELECT DISTINCT vend_id,prod_price 表示这两列都不同
SELECT DISTINCT vend_id FROM products; 只返回不同（唯一）的vend_id行，
mysql> SELECT DISTINCT vend_id FROM products;
+---------+
| vend_id |
+---------+
| 1001 |
| 1002 |
| 1003 |
| 1005 |
+---------+
4 rows in set (0.00 sec)
六、限制结果
使用LIMIT子句，返回前几行
SELECT prod_name FROM products LIMIT 5;
也可以指定要检索的开始行和行数，LIMIT 5,5表示从行5开始的5行，第一个数为开始的位置，第二个数为要检索的行数。也可以表示为LIMIT 4 OFFSET 3（从行3开始取4行，等于LIMIT 3,4）
SELECT prod_name FROM products LIMIT 5,5;
mysql> SELECT prod_name FROM products LIMIT 5;
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| Detonator |
| Bird seed |
+--------------+
5 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products LIMIT 5,5;
+--------------+
| prod_name |
+--------------+
| Carrots |
| Fuses |
| JetPack 1000 |
| JetPack 2000 |
| Oil can |
+--------------+
5 rows in set (0.00 sec)
七、使用完全限定的表名
使用完全限定的名字来引用列（同时使用表名和列字）
SELECT products.prod_name FROM products;
表名也可以完全限定
SELECT products.prod_name FROM order_test.products;
网友评论