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

CHAPTER6 Filtering Your Data

本章介绍WHERE从句.

predicates

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

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

Comparison

combinecomparisons using AND and OR

Range

TheBETWEEN . . . AND predicatedefines the range by using the value of the second value expression

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

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

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

Set Membership

Pattern Match

Apattern string can consist ofany logical combination of regular string characters and

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

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

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

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

举个例子,一目了然:

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

SQL   SELECT ProductName, ProductCode

FROMProducts

WHEREProductCode LIKE ‘G\_00_‘ ESCAPE ‘\‘

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

part of the valuesyou’re trying to retrieve.

Null

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

Excluding Rows with NOT

Order of Precedence

Whenyou  treat a combined set of conditions as a single unit, by definitionit becomes a

searchcondition, and you must enclose it in parentheses.

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

Whenyou need  to use multiple conditions, make certain that the condition thatexcludes the

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

potentially find theanswer faster.