自学第七章 数据过滤
本章节引入 操作符(operator)的概念, 用于组合WHERE子句进行使用;
[ AND | OR | IN | NOT ]
1、 AND 操作符
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
2、OR 操作符
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
3、计算次序(优先级)
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
SELECT vend_id, prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
3、IN 操作符
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
- IN 的功能和OR 相同
- IN的语法简洁清晰;
- 使用IN计算次序清晰;
- IN的执行效率更快
4、NOT 操作符
SELECT vend_id, prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
- MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反