SQL查询初学者指南读书笔记(四)where从句

CHAPTER6 Filtering Your Data

本章介绍WHERE从句.

 

predicates

Comparison,BETWEEN,IN, LIKE, and IS NULL.

We’ll cover the other two—Quantified and EXISTS—in Chapter 11, Subqueries.

 

 

Comparison

Equality and Inequality	= ,<>
Less Than and Greater Than	<,>
less than or equal to	<=
greater than or equal to	>=

 

combine comparisons using AND and OR

 

Range

The BETWEEN . . . AND predicate defines the range by using the value of the second value expression

as the start point and the value of the third value expression as the end point.

Both the start point and end point are part of the range.

 

习惯使用 (Value Expression1 <= Value Experssion2) and (Value Expression1 >= Value Experssion3)的各位同学,可以考虑使用BETWEEN . . . AND替代,这样SQL语句会更容易阅读理解.

 

Set Membership

 

Pattern Match

 

A pattern string can consist of any logical combination of regular string characters and

two special wildcard characters: the percent sign (%) and the underscore (_).

The percent sign represents zero or more arbitrary regular characters, and the underscore represents a single arbitrary regular character.

 

%与_通配符类似于正则表达式中的*与?

 

遇到通配符与正常字符混淆的情况怎么办?比如我们需要匹配含有下划线的字符串怎么办?这也是功能字符包含在常规字符会遇到的混淆情况,编码中经常碰到.这时我们需要将功能字符转义为常规字符,我们使用ESCAPE关键字实现这个功能.

举个例子,一目了然:

“Show me a list of products that have product codes beginning with‘G_00’ and ending in a single number orletter.”

SQL   SELECT ProductName, ProductCode

FROM Products

WHERE ProductCode LIKE 'G\_00_' ESCAPE '\'

 

Keep in mind that the character you use as an escape character should not be

part of the values you’re trying to retrieve.

 

Null

判断Value Expression是否为NULL的时候请不要使用Value Expression = NULL,这是常犯的小错误.

 

Excluding Rows with NOT

 

Order of Precedence

 

When you treat a combined set of conditions as a single unit, by definition it becomes a

search condition, and you must enclose it in parentheses.

添加括号以避免可能的混淆.

 

When you need to use multiple conditions, make certain that the condition that excludes the

most rows from the result set is processed first so that your database can

potentially find the answer faster.