注意
-
-
-
-
当选择多个列，一定要在列名之间加上逗号，但最后一个列名后不加。
SELECT语句
检索类别列
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)
检索所有列
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 |+---------+---------+----------------+------------+----------------------------------------------------------------+14 rows in set (0.00 sec)
检索不同行
mysql> SELECT vend_id FROM products;+---------+| vend_id |+---------+| 1001 || 1001 || 1001 || 1002 || 1002 || 1003 || 1003 || 1003 || 1003 || 1003 || 1003 || 1003 || 1005 || 1005 |+---------+14 rows in set (0.00 sec)
去重
mysql> SELECT DISTINCT vend_id FROM products;+---------+| vend_id |+---------+| 1001 || 1002 || 1003 || 1005 |+---------+4 rows in set (0.00 sec)
-
DISTINCT关键字，合并所有列而不是预先它的列。
限制结果
mysql> SELECT vend_id FROM products LIMIT 5,5;+---------+| vend_id |+---------+| 1003 || 1003 || 1003 || 1003 || 1003 |+---------+5 rows in set (0.00 sec)
-
LIMIT 5,5指示MySQL返回从行5开始的5行。
订购子句
单一排序
mysql> SELECT prod_name FROM products ORDER BY prod_name;+----------------+| prod_name |+----------------+| .5 ton anvil || 1 ton anvil || 2 ton anvil || Bird seed || Carrots || Detonator || Fuses || JetPack 1000 || JetPack 2000 || Oil can || Safe || Sling || TNT (1 stick) || TNT (5 sticks) |+----------------+14 rows in set (0.00 sec)
多个列排序
mysql> SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;+---------+------------+----------------+| prod_id | prod_price | prod_name |+---------+------------+----------------+| FC | 2.50 | Carrots || TNT1 | 2.50 | TNT (1 stick) || FU1 | 3.42 | Fuses || SLING | 4.49 | Sling || ANV01 | 5.99 | .5 ton anvil || OL1 | 8.99 | Oil can || ANV02 | 9.99 | 1 ton anvil || FB | 10.00 | Bird seed || TNT2 | 10.00 | TNT (5 sticks) || DTNTR | 13.00 | Detonator || ANV03 | 14.99 | 2 ton anvil || JP1000 | 35.00 | JetPack 1000 || SAFE | 50.00 | Safe || JP2000 | 55.00 | JetPack 2000 |+---------+------------+----------------+14 rows in set (0.00 sec)
-
对于上述示例中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。
指定排序方向
mysql> SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name,prod_name;+---------+------------+----------------+| prod_id | prod_price | prod_name |+---------+------------+----------------+| FC | 2.50 | Carrots || TNT1 | 2.50 | TNT (1 stick) || FU1 | 3.42 | Fuses || SLING | 4.49 | Sling || ANV01 | 5.99 | .5 ton anvil || OL1 | 8.99 | Oil can || ANV02 | 9.99 | 1 ton anvil || FB | 10.00 | Bird seed || TNT2 | 10.00 | TNT (5 sticks) || DTNTR | 13.00 | Detonator || ANV03 | 14.99 | 2 ton anvil || JP1000 | 35.00 | JetPack 1000 || SAFE | 50.00 | Safe || JP2000 | 55.00 | JetPack 2000 |+---------+------------+----------------+14 rows in set (0.00 sec)
在哪子句
WHERE子句操作符
| 操作符 |
说明 |
| = |
等于 |
| <> |
不等于 |
| ！= |
不等于 |
| < |
小于 |
| <= |
小于等于 |
| > |
大于 |
| > = |
大于等于 |
| = |
等于 |
| 之间 |
在指定的两个值之间 |
检查标准值
mysql> SELECT prod_id,prod_price,prod_name FROM products WHERE prod_name='fuses';+---------+------------+-----------+| prod_id | prod_price | prod_name |+---------+------------+-----------+| FU1 | 3.42 | Fuses |+---------+------------+-----------+1 row in set (0.00 sec)
不匹配检查
mysql> SELECT vend_id,prod_name FROM products WHERE vend_id <> 1003;+---------+--------------+| vend_id | prod_name |+---------+--------------+| 1001 | .5 ton anvil || 1001 | 1 ton anvil || 1001 | 2 ton anvil || 1002 | Fuses || 1002 | Oil can || 1005 | JetPack 1000 || 1005 | JetPack 2000 |+---------+--------------+7 rows in set (0.01 sec)
范围值检查
mysql> SELECT vend_id,prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;+---------+----------------+| vend_id | prod_name |+---------+----------------+| 1001 | .5 ton anvil || 1001 | 1 ton anvil || 1003 | Bird seed || 1002 | Oil can || 1003 | TNT (5 sticks) |+---------+----------------+5 rows in set (0.01 sec)
空值检查
mysql> SELECT cust_id FROM customers WHERE cust_email IS NULL;+---------+| cust_id |+---------+| 10002 || 10005 |+---------+2 rows in set (0.00 sec)
