mysql过滤数据

mysql过滤数据

1. 使用WHERE子句

示例1：

SELECT prod_name,prod_price FROM products WHERE prod_price = 2.50;

结果：

+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
2 rows in set (0.02 sec)

示例2：

SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';

结果：

+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+
1 row in set (0.00 sec)

示例3：

SELECT prod_name,prod_price FROM products WHERE prod_price < 10 ORDER BY prod_price DESC,prod_name;

结果：

+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| 1 ton anvil   |       9.99 |
| Oil can       |       8.99 |
| .5 ton anvil  |       5.99 |
| Sling         |       4.49 |
| Fuses         |       3.42 |
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
7 rows in set (0.05 sec)

示例4：

SELECT prod_name,prod_price FROM products WHERE prod_price <= 10 ORDER BY prod_price;

结果：

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Carrots        |       2.50 |
| TNT (1 stick)  |       2.50 |
| Fuses          |       3.42 |
| Sling          |       4.49 |
| .5 ton anvil   |       5.99 |
| Oil can        |       8.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

2. 不匹配检查

示例1：

SELECT vend_id,prod_name FROM products WHERE vend_id <> 1003 ORDER BY vend_id;

结果：

+---------+--------------+
| 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.00 sec)

示例2：

SELECT vend_id,prod_name FROM products WHERE vend_id != 1003 ORDER BY vend_id DESC,prod_name DESC;

结果：

+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1005 | JetPack 2000 |
|    1005 | JetPack 1000 |
|    1002 | Oil can      |
|    1002 | Fuses        |
|    1001 | 2 ton anvil  |
|    1001 | 1 ton anvil  |
|    1001 | .5 ton anvil |
+---------+--------------+
7 rows in set (0.00 sec)

WHERE子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

3. 范围值检索

SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10 ORDER BY prod_price;

结果：

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| Oil can        |       8.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.00 sec)

4. 空值检查

示例1：

SELECT prod_name FROM products WHERE prod_price IS NULL;

结果：

Empty set (0.00 sec)

返回prod_price为空值null的prod_name,无对应数据

示例2：

SELECT cust_id FROM customers WHERE cust_email IS NULL;

结果：

+---------+
| cust_id |
+---------+
|   10002 |
|   10005 |
+---------+
2 rows in set (0.05 sec)