SQL查詢初學者指南讀書筆記(四)where從句，初學者指南where

SQL查詢初學者指南讀書筆記(四)where從句，初學者指南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

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

 

combinecomparisons usingAND andOR

 

Range

TheBETWEEN . . . ANDpredicatedefines 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

 

Apatternstring 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 theunderscore represents a single arbitraryregular character.

 

%與_萬用字元類似於Regex中的*與?

 

遇到萬用字元與正常字元混淆的情況怎麼辦?比如我們需要匹配含有底線的字串怎麼辦?這也是功能字元包含在常規字元會遇到的混淆情況,編碼中經常碰到.這時我們需要將功能字元轉義為常規字元,我們使用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.