10、MySQL的数据过滤

where条件过滤
找出价格为2.5的产品

mysql> select * from products where prod_price = 2.5;
+---------+---------+---------------+------------+--------------------------------------+
| prod_id | vend_id | prod_name     | prod_price | prod_desc                            |
+---------+---------+---------------+------------+--------------------------------------+
| FC      |    1003 | Carrots       | 2.50       | Carrots (rabbit hunting season only) |
| TNT1    |    1003 | TNT (1 stick) | 2.50       | TNT, red, single stick               |
+---------+---------+---------------+------------+--------------------------------------+
2 rows in set (0.07 sec)

where条件的操作符
= > < （ <>、 ！= 都是不等于） between

mysql> select * from products where prod_price > 2.5 and prod_price <= 10;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| 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 |
| FB      |    1003 | Bird seed      | 10.00      | Large bag (suitable for road runners)                          |
| FU1     |    1002 | Fuses          | 3.42       | 1 dozen, extra long                                            |
| OL1     |    1002 | Oil can        | 8.99       | Oil can, red                                                   |
| SLING   |    1003 | Sling          | 4.49       | Sling, one size fits all                                       |
| TNT2    |    1003 | TNT (5 sticks) | 10.00      | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
7 rows in set (0.09 sec)

between

mysql> select * from products where prod_price between 2.5 and 10;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| 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 |
| 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                                            |
| OL1     |    1002 | Oil can        | 8.99       | Oil can, red                                                   |
| 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                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
9 rows in set (0.10 sec)

与order by连用
空值检查

mysql> select * from products where prod_desc is null;