第五课 高级数据过滤（Advancecd Data Filtering）

本课学习如何结合WHERE语句创建强大与复杂的搜索条件。学习使用NOT和IN操作符。

5.1 组合WHERE语句（Combining WHERE Clauses）

Operator

特殊的关键字用于加入或改变WHERE语句中的语句。也为逻辑操作符。

5.1.1 使用AND操作符（Using the AND Operator）

输入：

SELECT prod_id,prod_price,prod_name

FROM Products

WHERE wend_id ='DLL01' AND prod_price <= 4;

AND

A keyword used in a WHERE clause to specify that only rows matching
all the specified conditions should be retrieved.

Note: No ORDER BY Clause Specified

In the interests of saving space (and your typing) I omitted ORDER BY
clause in many of these examples. As such, it is entirely possible that
your output won’t exactly match the output in the book. While the
number of returned rows should always match, their order may not. Of
course, feel free to add an ORDER BY clause if you’d like, it needs to
go after the WHERE clause.

5.1.2 使用OR操作符（Using OR Operator）

OR操作符命令数据库管理软件找到匹配任一条件的行。
输入：

SELECT prod_name, prod_price

FROM Products

WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

OR

A keyword used in a WHERE clause to specify that any rows matching
either of the specified conditions should be retrieved.

5.1.3 理解估计顺序（Understanding Order of Evaluation）

输入：

SELECT prod_name, prod_price

FROM Products

WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'

AND prod_price >= 10;

注意：AND优先级高于OR！！！

输入：

SELECT prod_name, prod_price

FROM Products

WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')

AND prod_price >= 10;

5.2 使用IN操作符（Using the IN Operator）

IN操作符用于确定任一可匹配的条件范围。

IN takes a comma-delimited list of valid values, all enclosed within parentheses.

输入：

SELECT prod_name, prod_price

FROM Products

WHERE vend_id IN ('DLL01','BRS01')

ORDER BY prod_name;

下面是等价的：

输入

SELECT prod_name, prod_price

FROM Products

WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'

ORDER BY prod_name;

IN

A keyword used in a WHERE clause to specify a list of values to be
matched using an OR comparison.

5.3 使用NOT操作符（Using the NOT Operator）

NOT

A keyword used in a WHERE clause to negate a condition.

输入：

SELECT prod_name

FROM Products

WHERE NOT vend_id = 'DLL01'

ORDER BY prod_name;

等价于：
输入

SELECT prod_name

FROM Products

WHERE vend_id <> 'DLL01'

ORDER BY prod_name;

5.4 总结

This lesson picked up where the last lesson left off and taught you how to combine WHERE clauses with the AND and OR operators. You also learned how to explicitly manage the order of evaluation and how to use the IN and NOT operators.