组合WHERE子句
AND操作符
同时匹配两个条件
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
OR操作符
匹配任一条件,而不是同时匹配两个条件
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
求值顺序
圆括号>AND>OR
注:使用具有AND和OR的WHERE子句时,用圆括号可以消除歧义。
IN操作符
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;
注:IN与OR相当,但比OR更好用(直观清晰、求值顺序易于管理、运行更快,可嵌套SELECT语句)
NOT操作符
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
注:
1、NOT用于否定其后条件,此处与WHERE vend_id <> 'DLL01’类似。
2、NOT适用于更复杂的子句中,如与IN联用。