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 | ylee@coyote.com | | 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL | | 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com | | 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com | | 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 | +---------+-------------+