《MySQL必知必会》第七章:数据过滤
@author: Tobin
@date: 2019/10/23 21:39:15
组合WHERE子句建立功能更强的更高级的搜索条件,使用NOT和IN操作符。
# AND
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id=1003 AND prod_price<=10;
# 类似地OR,注意AND优先级更高,可以使用圆括号
SELECT prod_id, prod_name, prod_price
FROM products
WHERE (vend_id=1003 OR vend_id=1004) AND prod_price<=10;
# IN,功能与OR相当,一般比OR快,更方便,还可以包含其他SELECT语句
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;
# NOT
# MySQL中地NOT支持对IN,BETWEEN,和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大差别
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;