mysql数据过滤

mysql数据过滤

1. AND或OR操作符连接多个WHERE子句

AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
检索由供应商1003制造且价格小于等于10美元的产品信息：

SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

结果：

+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FB      |      10.00 | Bird seed      |
| FC      |       2.50 | Carrots        |
| SLING   |       4.49 | Sling          |
| TNT1    |       2.50 | TNT (1 stick)  |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+
5 rows in set (0.00 sec)

OR操作符，指示MySQL检索匹配任一条件的行。

SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;

结果：

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

2. AND和OR结合，AND优先计算

select prod_name,prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;

优先计算and，查找vend_id为1003且价格>=10的产品，或者vend_id为1002的产品，不管价格如何

使用圆括号明确运算顺序：查找vend_id为1002或1003，且价格>=10的产品。

SELECT prod_name,prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

结果：

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Safe           |      50.00 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
4 rows in set (0.00 sec)

3. IN操作符

IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号。

SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
# SELECT prod_name,prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;

结果：

+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Detonator      |      13.00 |
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

4. NOT操作符

SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

结果：

+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
5 rows in set (0.00 sec)

Mysql支持not对in，between，exsits子句取反