MySQL学习笔记（三）

1、检索数据

　　单列检索

 1 mysql> SELECT prod_name
 2     -> FROM products;
 3 +----------------+
 4 | prod_name      |
 5 +----------------+
 6 | .5 ton anvil   |
 7 | 1 ton anvil    |
 8 | 2 ton anvil    |
 9 | Detonator      |
10 | Bird seed      |
11 | Carrots        |
12 | Fuses          |
13 | JetPack 1000   |
14 | JetPack 2000   |
15 | Oil can        |
16 | Safe           |
17 | Sling          |
18 | TNT (1 stick)  |
19 | TNT (5 sticks) |
20 +----------------+
21 14 rows in set (0.00 sec)

　　多列检索

 1 mysql> SELECT prod_id, prod_name, prod_price
 2     -> FROM products;
 3 +---------+----------------+------------+
 4 | prod_id | prod_name      | prod_price |
 5 +---------+----------------+------------+
 6 | ANV01   | .5 ton anvil   |       5.99 |
 7 | ANV02   | 1 ton anvil    |       9.99 |
 8 | ANV03   | 2 ton anvil    |      14.99 |
 9 | DTNTR   | Detonator      |      13.00 |
10 | FB      | Bird seed      |      10.00 |
11 | FC      | Carrots        |       2.50 |
12 | FU1     | Fuses          |       3.42 |
13 | JP1000  | JetPack 1000   |      35.00 |
14 | JP2000  | JetPack 2000   |      55.00 |
15 | OL1     | Oil can        |       8.99 |
16 | SAFE    | Safe           |      50.00 |
17 | SLING   | Sling          |       4.49 |
18 | TNT1    | TNT (1 stick)  |       2.50 |
19 | TNT2    | TNT (5 sticks) |      10.00 |
20 +---------+----------------+------------+
21 14 rows in set (0.00 sec)

　　在实际列名的位置使用(*)通配符，SELECT语句便可以检索出所有的列。

　　检索不同的行

通过DISTINCT关键字可以是Mysql只返回不同的行

mysql> SELECT DISTINCT vend_id
    -> FROM products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.08 sec)

　　限制结果

只返回第一行或前几行，可以使用LIMIT子句

 1 mysql> SELECT prod_name
 2     -> FROM products
 3     -> LIMIT 5;
 4 +--------------+
 5 | prod_name    |
 6 +--------------+
 7 | .5 ton anvil |
 8 | 1 ton anvil  |
 9 | 2 ton anvil  |
10 | Detonator    |
11 | Bird seed    |
12 +--------------+
13 5 rows in set (0.00 sec)

2、排序数据

 1 mysql> SELECT prod_name
 2     -> FROM products
 3     -> ORDER BY prod_name;
 4 +----------------+
 5 | prod_name      |
 6 +----------------+
 7 | .5 ton anvil   |
 8 | 1 ton anvil    |
 9 | 2 ton anvil    |
10 | Bird seed      |
11 | Carrots        |
12 | Detonator      |
13 | Fuses          |
14 | JetPack 1000   |
15 | JetPack 2000   |
16 | Oil can        |
17 | Safe           |
18 | Sling          |
19 | TNT (1 stick)  |
20 | TNT (5 sticks) |
21 +----------------+
22 14 rows in set (0.27 sec)

　　多列排序

 1 mysql> SELECT prod_id, prod_price, prod_name
 2     -> FROM products
 3     -> ORDER BY prod_price, prod_name;
 4 +---------+------------+----------------+
 5 | prod_id | prod_price | prod_name      |
 6 +---------+------------+----------------+
 7 | FC      |       2.50 | Carrots        |
 8 | TNT1    |       2.50 | TNT (1 stick)  |
 9 | FU1     |       3.42 | Fuses          |
10 | SLING   |       4.49 | Sling          |
11 | ANV01   |       5.99 | .5 ton anvil   |
12 | OL1     |       8.99 | Oil can        |
13 | ANV02   |       9.99 | 1 ton anvil    |
14 | FB      |      10.00 | Bird seed      |
15 | TNT2    |      10.00 | TNT (5 sticks) |
16 | DTNTR   |      13.00 | Detonator      |
17 | ANV03   |      14.99 | 2 ton anvil    |
18 | JP1000  |      35.00 | JetPack 1000   |
19 | SAFE    |      50.00 | Safe           |
20 | JP2000  |      55.00 | JetPack 2000   |
21 +---------+------------+----------------+
22 14 rows in set (0.03 sec)

　　指定排序顺序

 1 mysql> SELECT prod_id, prod_price, prod_name
 2     -> FROM products
 3     -> ORDER BY prod_price DESC;
 4 +---------+------------+----------------+
 5 | prod_id | prod_price | prod_name      |
 6 +---------+------------+----------------+
 7 | JP2000  |      55.00 | JetPack 2000   |
 8 | SAFE    |      50.00 | Safe           |
 9 | JP1000  |      35.00 | JetPack 1000   |
10 | ANV03   |      14.99 | 2 ton anvil    |
11 | DTNTR   |      13.00 | Detonator      |
12 | TNT2    |      10.00 | TNT (5 sticks) |
13 | FB      |      10.00 | Bird seed      |
14 | ANV02   |       9.99 | 1 ton anvil    |
15 | OL1     |       8.99 | Oil can        |
16 | ANV01   |       5.99 | .5 ton anvil   |
17 | SLING   |       4.49 | Sling          |
18 | FU1     |       3.42 | Fuses          |
19 | FC      |       2.50 | Carrots        |
20 | TNT1    |       2.50 | TNT (1 stick)  |
21 +---------+------------+----------------+
22 14 rows in set (0.00 sec)

DESC关键字只应用到直接位于其前面的列名。DESC：降序排列，ASC：升序排列(默认排序)

　　3、过滤数据 (使用WHERE子句)

 1 mysql> SELECT prod_name, prod_price
 2     -> FROM products
 3     -> WHERE prod_price = 2.50;
 4 +---------------+------------+
 5 | prod_name     | prod_price |
 6 +---------------+------------+
 7 | Carrots       |       2.50 |
 8 | TNT (1 stick) |       2.50 |
 9 +---------------+------------+
10 2 rows in set (0.03 sec)

WHERE子句的位置：在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

　　其他WHERE子句操作符的使用

1 mysql> SELECT prod_name, prod_price
2     -> FROM products
3     -> WHERE prod_name = 'fuses';
4 +-----------+------------+
5 | prod_name | prod_price |
6 +-----------+------------+
7 | Fuses     |       3.42 |
8 +-----------+------------+
9 1 row in set (0.00 sec)

 1 mysql> SELECT prod_name, prod_price
 2     -> FROM products
 3     -> WHERE prod_price < 10;
 4 +---------------+------------+
 5 | prod_name     | prod_price |
 6 +---------------+------------+
 7 | .5 ton anvil  |       5.99 |
 8 | 1 ton anvil   |       9.99 |
 9 | Carrots       |       2.50 |
10 | Fuses         |       3.42 |
11 | Oil can       |       8.99 |
12 | Sling         |       4.49 |
13 | TNT (1 stick) |       2.50 |
14 +---------------+------------+
15 7 rows in set (0.27 sec)

 1 mysql> SELECT vend_id, prod_name
 2     -> FROM products
 3     -> WHERE vend_id <> 1003;
 4 +---------+--------------+
 5 | vend_id | prod_name    |
 6 +---------+--------------+
 7 |    1001 | .5 ton anvil |
 8 |    1001 | 1 ton anvil  |
 9 |    1001 | 2 ton anvil  |
10 |    1002 | Fuses        |
11 |    1005 | JetPack 1000 |
12 |    1005 | JetPack 2000 |
13 |    1002 | Oil can      |
14 +---------+--------------+
15 7 rows in set (0.53 sec)

 1 mysql> SELECT prod_name, prod_price
 2     -> FROM products
 3     -> WHERE prod_price BETWEEN 5 AND 10;
 4 +----------------+------------+
 5 | prod_name      | prod_price |
 6 +----------------+------------+
 7 | .5 ton anvil   |       5.99 |
 8 | 1 ton anvil    |       9.99 |
 9 | Bird seed      |      10.00 |
10 | Oil can        |       8.99 |
11 | TNT (5 sticks) |      10.00 |
12 +----------------+------------+
13 5 rows in set (0.00 sec)

　　空值检查

 1 mysql> SELECT cust_id
 2     -> FROM customers
 3     -> WHERE cust_email IS NULL;
 4 +---------+
 5 | cust_id |
 6 +---------+
 7 |   10002 |
 8 |   10005 |
 9 +---------+
10 2 rows in set (0.06 sec)

　　AND操作符

 1 mysql> SELECT prod_id, prod_price, prod_name
 2     -> FROM products
 3     -> WHERE vend_id = 1003 AND prod_price <= 10;
 4 +---------+------------+----------------+
 5 | prod_id | prod_price | prod_name      |
 6 +---------+------------+----------------+
 7 | FB      |      10.00 | Bird seed      |
 8 | FC      |       2.50 | Carrots        |
 9 | SLING   |       4.49 | Sling          |
10 | TNT1    |       2.50 | TNT (1 stick)  |
11 | TNT2    |      10.00 | TNT (5 sticks) |
12 +---------+------------+----------------+
13 5 rows in set (0.00 sec)

　　OR操作符

 1 mysql> SELECT prod_name, prod_price
 2     -> FROM products
 3     -> WHERE vend_id = 1002 OR vend_id = 1003;
 4 +----------------+------------+
 5 | prod_name      | prod_price |
 6 +----------------+------------+
 7 | Detonator      |      13.00 |
 8 | Bird seed      |      10.00 |
 9 | Carrots        |       2.50 |
10 | Fuses          |       3.42 |
11 | Oil can        |       8.99 |
12 | Safe           |      50.00 |
13 | Sling          |       4.49 |
14 | TNT (1 stick)  |       2.50 |
15 | TNT (5 sticks) |      10.00 |
16 +----------------+------------+
17 9 rows in set (0.00 sec)

　　IN操作符

 1 mysql> SELECT prod_name, prod_price
 2     -> FROM products
 3     -> WHERE vend_id IN (1002, 1003)
 4     -> ORDER BY prod_name;
 5 +----------------+------------+
 6 | prod_name      | prod_price |
 7 +----------------+------------+
 8 | Bird seed      |      10.00 |
 9 | Carrots        |       2.50 |
10 | Detonator      |      13.00 |
11 | Fuses          |       3.42 |
12 | Oil can        |       8.99 |
13 | Safe           |      50.00 |
14 | Sling          |       4.49 |
15 | TNT (1 stick)  |       2.50 |
16 | TNT (5 sticks) |      10.00 |
17 +----------------+------------+
18 9 rows in set (0.00 sec)

　　NOT操作符

 1 mysql> SELECT prod_name, prod_price
 2     -> FROM products
 3     -> WHERE vend_id NOT IN (1002, 1003);
 4 +--------------+------------+
 5 | prod_name    | prod_price |
 6 +--------------+------------+
 7 | .5 ton anvil |       5.99 |
 8 | 1 ton anvil  |       9.99 |
 9 | 2 ton anvil  |      14.99 |
10 | JetPack 1000 |      35.00 |
11 | JetPack 2000 |      55.00 |
12 +--------------+------------+
13 5 rows in set (0.00 sec)