11、MySQL的数据过滤（二）

not in and or

mysql> select * from products where vend_id = 1003 and prod_price <= 10;
+---------+---------+----------------+------------+---------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                             |
+---------+---------+----------------+------------+---------------------------------------+
| FB      |    1003 | Bird seed      | 10.00      | Large bag (suitable for road runners) |
| FC      |    1003 | Carrots        | 2.50       | Carrots (rabbit hunting season only)  |
| 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           |
+---------+---------+----------------+------------+---------------------------------------+
5 rows in set (0.09 sec)

mysql> select * from products where vend_id = 1003 or 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 |
| DTNTR   |    1003 | Detonator      | 13.00      | Detonator (plunger powered), fuses not included                |
| 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                                                   |
| SAFE    |    1003 | Safe           | 50.00      | Safe with combination lock                                     |
| 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                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
11 rows in set (0.07 sec)

2、计算的次序 and > or 但最好要加（）

mysql> select * from products where vend_id in (1001, 1005, 1002);
+---------+---------+--------------+------------+----------------------------------------------------------------+
| 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 |
| ANV03   |    1001 | 2 ton anvil  | 14.99      | 2 ton anvil, black, complete with handy hook and carrying case |
| anv21   |    1001 | 3 ton anvil  | 19.99      | 333333hhhdhdh                                                  |
| FU1     |    1002 | Fuses        | 3.42       | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000 | 35.00      | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000 | 55.00      | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can      | 8.99       | Oil can, red                                                   |
+---------+---------+--------------+------------+----------------------------------------------------------------+
8 rows in set (0.07 sec)

mysql> select * from products where vend_id not in (1002);
+---------+---------+----------------+------------+----------------------------------------------------------------+
| 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 |
| ANV03   |    1001 | 2 ton anvil    | 14.99      | 2 ton anvil, black, complete with handy hook and carrying case |
| anv21   |    1001 | 3 ton anvil    | 19.99      | 333333hhhdhdh                                                  |
| DTNTR   |    1003 | Detonator      | 13.00      | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      | 10.00      | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        | 2.50       | Carrots (rabbit hunting season only)                           |
| JP1000  |    1005 | JetPack 1000   | 35.00      | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   | 55.00      | JetPack 2000, multi-use                                        |
| SAFE    |    1003 | Safe           | 50.00      | Safe with combination lock                                     |
| 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                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
13 rows in set (0.10 sec)