第四课 过滤数据（Filting Data）
本课学习使用SELECT声明的WHERE语句来确定搜索条件。
4.1使用WHERE语句
search criteria
filter condition
输入：
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;
Tips：How many zeros？
Tips：SQL Versus Application Filtering
Caution：WHERE Clause Position
4.2 WHERE Clause Operators（WHERE语句操作符）
4.2.1 针对单个值的检查
输入：
SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;
输入：
SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 10;
4.2.2 检查不匹配项
输入：
SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';
输入：
SELECT vend_id, prod_name
FROM Products
WHERE vend_id != 'DLL01';
Tip: When to Use Quotes
If you look closely at the conditions used in the above WHERE clauses,you will notice that some values are enclosed within single quotes, and others are not. The single quotes are used to delimit a string. If you are comparing a value against a column that is a string datatype, the delimiting quotes are required. Quotes are not used to delimit values used with numeric columns.
Caution: != Or <>?
!= and <> can usually be used interchangeably. However, not all DBMSs support both forms of the non-equality operator. Microsoft Access, for example, supports <> but does not support !=. If in doubt, consult your DBMS documentation.
4.2.3 检查值的范围
使用BETWEEN操作符
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
4.2.4 检查No值
当某列包含no值，亦即包含一个NULL值。
NULL
No value, as opposed to a field containing 0, or an empty string, or just
spaces.
输入：
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;
输入：
SELECT cust_name
FROM CUSTOMERS
WHERE cust_email IS NULL;
Tip: DBMS Specific Operators
Many DBMSs extend the standard set of operators, providing advanced filtering options. Refer to your DBMS documentation for more information.
Caution: NULL and Non-matches
You might expect that when you filter to select all rows that do not have
a particular value, rows with a NULL will be returned. But they will
not. Because of the special meaning of unknown, the database does not know whether or not they match, and so they are not returned when filtering for matches or when filtering for non-matches.
When filtering data, make sure to verify that the rows with a NULL in
the filtered column are really present in the returned data.
4.3 总结
In this lesson, you learned how to filter returned data using the SELECT statement’s WHERE clause. You learned how to test for equality, nonequality, greater than and less than, value ranges, as well as for NULL values.
