组合 WHERE 子句
- 操作符 operator:用来连结或改变 WHERE 子句中的子句的关键字,也叫 logical operator
AND 操作符
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;
OR 操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003;
计算次序
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
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;
- IN 的优点在于,其语法较为清晰,一般比 OR 要快,最大的优点是可以包含其他 SELECT 语句,可以动态地建立 WHERE 子句
NOT 操作符
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;
- MySQL 中支持 NOT 对 IN、BETWEEN 和 EXISTS 子句进行取反