Mysql必知必会教程--数据过滤

关键字：where

这里只说明不匹配的用法，其他的较为简单，不予叙述。

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        |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
|    1002 | Oil can      |
+---------+--------------+

上述sql语句可改写为：select vend_id,prod_name from products where vend_id != 1003;

范围值检查：between

select prod_id,prod_price,prod_name from products where prod_price between 5 and 10;
输出结果：
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| ANV01   |       5.99 | .5 ton anvil   |
| ANV02   |       9.99 | 1 ton anvil    |
| FB      |      10.00 | Bird seed      |
| OL1     |       8.99 | Oil can        |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+

空值检查：这里用到另外一张表：customers

表数据如下：

+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | [email protected]     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | [email protected] |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | [email protected]    |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+

select cust_id,cust_name from customers where cust_email is null;
输出结果：
+---------+-------------+
| cust_id | cust_name   |
+---------+-------------+
|   10002 | Mouse House |
|   10005 | E Fudd      |
+---------+-------------+