7.3 執行基本的SELECT語句

SELECT語句的實際功能體現在它的執行中。執行所有查詢語言語句的關鍵是理解其語法和決定其用法的規則。首先討論這個主題，然后是基本查詢的執行，最后介紹表達式和運算符——它們能夠極大地增強存儲在關系表中數據的效用。再接著介紹空值(null value)的概念以及它的缺點。這些主題可分為下面四個部分進行討論：

● 基本SELECT語句的語法

● 必須遵守的規則

● SQL表達式和運算符

● NULL表示空

7.3.1 基本SELECT語句的語法

SELECT語句的最基本形式支持列投影和創建算術、字符和日期表達式。它也能夠從結果集中刪除重復值。基本SELECT語句的語法如下所示：

        SELECT *|{[DISTINCT] column|expression [alias], ...}
        FROM table;

SELECT語句語法的特殊關鍵字或者保留字使用大寫字母。然而，當使用命令時，查詢語句中保留字的大小寫并不重要。保留字不能用作列名或者其他數據庫對象的名稱。SELECT、DISTINCT和FROM是三個關鍵字。一條SELECT語句總是包含兩個或者更多子句。兩個強制子句是SELECT子句和FROM子句。管道符號(|)表示OR。因此可以將上面SELECT語句的第一種形式讀作：

        SELECT *
        FROM table;

在這種格式中，星號(*)表示所有列。SELECT *是要求Oracle服務器返回所有可能列的簡潔方式。我們將它作為一種速記和省時的符號，這樣就不需要輸入SELECT column1, column2, column3, column4, …, columnX來選擇所有列。FROM子句指定查詢哪個表來獲得SELECT子句所請求的列。

可以發出下面的SQL命令從HR模式的REGIONS表中檢索所有列和所有行：

        select * from regions;

當執行這條命令時，它返回所有數據行和屬于該表的所有列。在SELECT語句中使用星號有時表示“盲目(blind)”查詢，因為沒有確切指定要提取的列。

基本SELECT語句的第二種形式的FROM子句與第一種形式的相同，但SELECT子句不同：

        SELECT {[DISTINCT] column|expression [alias], ...}FROM table;

該SELECT子句可以簡化為兩種形式：

        SELECT column1 (possibly other columns or expressions) [alias optional]

或者

        SELECT DISTINCT column1 (possibly other columns or expressions)
        [alias optional]

別名是表示列或者表達式的可選名稱。別名通常用于以用戶友好的方式顯示輸出。當表示列或者表達式時，它們也可以作為速記方式以減少輸入量。本章稍后將討論別名。通過顯式列出SELECT子句中的相關列，實際上可以投影要檢索結果的確切子集。下面的語句只返回REGIONS表的REGION_NAME列：

        select region_name from regions;

假定要求獲得組織中員工曾經從事過的所有工作角色。為此可以發出命令：SELECT *FROM JOB_HISTORY。然而，SELECT *結構會另外返回EMPLOYEE_ID、START_DATE和END_DATE列。使用下列語句，可以獲得只包含JOB_ID和DEPARTMENT_ID列的整齊結果集。

        select job_id, department_id from job_history;

使用DISTINCT關鍵字允許從結果集中刪除重復行。在很多情況下，只需要唯一的行集合。重要的是要注意，Oracle 服務器在確定行是唯一還是重復時所采用的標準完全取決于SELECT子句中DISTINCT關鍵字之后指定的內容。使用下列語句從JOB_ HISTORY表中選擇不同的JOB_ID值會返回8種不同的工作類型。

        select distinct job_id from job_history;

DISTINCT關鍵字的一個重要特性是從列的組合中刪除重復值。

7.3.2 必須遵守的規則

SQL是一種在語法規則上非常嚴格的語言，但它也非常簡單和靈活，足以支持大量編程樣式。本節將討論SQL語句的一些基本規則。

1．大寫字母或者小寫字母

使用大寫字母還是小寫字母將SQL語句提交給數據庫，這只是個人愛好。包括本書作者在內的許多開發人員都喜歡用小寫字母來寫SQL語句。需要用大寫字母指定SQL的保留字，這也是一種常見誤解。再次重申，這取決于你的愛好。建議使用一致的標準化格式。

關于字母大小寫有一條告誡。當與字面值交互時，區分大小寫就有意義了。請看JOB_HISTORY表的JOB_ID列。這一列包含的數據行剛好以大寫字母形式存儲在數據庫中；例如，SA_REP和ST_ CLERK。當要求使用字面列限制結果集時，大小寫就非常重要。Oracle服務器認為在JOB_HISTORY表中，對于JOB_ID列中包含St_Clerk值的所有行的請求不同于對JOB_ID列中包含ST_CLERK值的所有行的請求。

關于不同數據庫對象的元數據默認使用大寫字母存儲在數據字典中。如果查詢數據庫字典表來返回HR模式所擁有的表列表，那么很可能返回的表名是存儲為大寫字母。這并不意味著不能使用小寫名稱創建表；其實可以這樣做。只不過在數據庫字典中使用大寫字母創建和存儲表、列以及其他數據庫對象元數據的情況更常見，而且是Oracle服務器的默認行為。

2．語句終止符

通常用分號作為SQL語句的終止符。SQL*Plus總是需要語句終止符，通常使用分號。單個SQL語句甚至是幾組相關語句通常存儲為腳本文件以便將來使用。SQL腳本中的單個語句通常用換行(或者回車)終止，并且在下一行使用正斜杠(/)，而不是使用分號終止。可以創建SELECT語句，用換行終止它，包含一條正斜杠來執行語句，并在腳本文件中存儲它。然后可以從SQL*Plus內部調用腳本文件。注意，如果只是單個語句，SQL Developer就不需要語句終止符，但如果使用了終止符也沒有錯。好的做法是使用分號終止SQL語句。SQL*Plus語句的一些示例如下所示：

        select country_name, country_id, location_id from countries;

       select city, location_id,
              state_province, country_id
        from locations
        /

第一個代碼示例說明兩條重要規則：第一，用分號終止語句；第二，整條語句寫成一行。對于SQL語句而言，只要語句中的單詞沒有跨越數行，那么寫成一行或者跨越多行，這完全可以接受。第二個代碼樣本顯示的語句跨越三行，由新行終止，并使用正斜杠執行。

3．縮排、可讀性和好的實踐

考察下面的查詢：

        select city, location_id,
              state_province, country_id
        from locations
        /

該示例突出強調了縮排SQL語句，以增強代碼可讀性的好處。Oracle服務器并不反對將整條語句寫成一行、沒有縮排。良好的實踐是將SELECT語句的不同子句分離到不同的行。當子句中的表達式特別復雜時，通常最好將這種語句分離到新的行，以提高可讀性。當開發SQL來滿足報告需要時，通常需要重復這個過程。如果將復雜的表達式分成單獨的行，那么在開發過程中SQL解釋器就會更有用，因為拋出錯誤的格式通常是：“ERROR at line X:”，這使調試過程變得更容易。

練習7-1 使用SQL回答幾個問題

在這個練習中，使用SQL*Plus作為HR用戶進行連接，使用SELECT語句回答兩個問題。

問題1：當前有多少唯一部門有員工在里面工作？

(1) 啟動SQL*Plus，連接到HR模式。

(2) 最初可能試圖在DEPARTMENTS表中查找答案。但通過仔細研究會發現，該問題要求的是員工信息。此信息包含在EMPLOYEES表中。

(3) 單詞“唯一(unique)”說明應該使用DISTINCT關鍵字。

(4) 結合第(2)步和第(3)步，就可以構造下面的SQL語句：

        select distinct department_id
        from employees;

(5) 該查詢返回12行。注意，第3行是空的。這是DEPARTMENT_ID列中的空值。

(6) 所以第一個問題的答案是：11個唯一部門中有員工工作，但至少有一名員工沒有分配部門。

問題2：歐洲地區有多少個國家？

(1) 這個問題包含兩個部分。考察REGIONS表(它包含四個地區，每個地區都由REGION_ID值唯一標識)和COUNTRIES表(它包含REGION_ID列，該列顯示這個國家屬于哪個地區)。

(2) 第一個查詢需要確定歐洲地區的REGION_ID。這由SQL語句完成，它顯示歐洲地區的REGION_ID值為1：

        select * from regions;

(3) 要確定哪些國家將1作為其REGION_ID，就需要執行下面的SQL查詢：

        select region_id, country_name from countries;

(4) 手動計算REGION_ID值為1的行就知道：對于HR數據模型而言，歐洲地區有8個國家。

7.3.3 SQL表達式和運算符

SELECT語句的一般形式引入了可對列和表達式進行選擇的概念。表達式通常由在一個或多個列值或者表達式上執行的運算組成。能夠作用在列值上以構成表達式的運算符取決于底層數據類型。數字列主要有四種算術運算符(加、減、乘和除)；字符或者字符串列有串聯運算符；日期和時間戳列有加減運算符。在一般算術運算中，當表達式中出現多個運算符時，有預定義的計算順序(運算符優先級)。圓括號的優先級最高，乘法和除法次之，之后是加法和減法(它們的優先級最低)。

具有相同優先級的運算從左到右計算。圓括號用來強制非默認運算符優先級。當構造復雜的表達式時，大量使用括號是好的實踐，通常鼓勵這樣做。因為這樣代碼就更具有可讀性，也不容易出錯。表達式使許多有用的數據操作變得可行。

1．算術運算符

考察JOB_HISTORY表的示例，該表存儲員工的前一個工作角色的起止日期。例如，出于稅收或養老金方面的考慮，通常需要計算該員工在此角色中工作了多長時間。可以使用算術表達式獲得這些信息。SQL語句和圖7-8返回的結果都有一些有意思的元素，值得進一步討論。

在SELECT子句中指定了5個元素。前4個元素是JOB_HISTORY表的標準列。后面提供了計算員工在特定崗位上工作的天數所需的源信息。考察輸出中第9行編號為176的員工。該員工從1999年1月1日起擔任銷售主管(Sales Manager)，于1999年12月31日結束雇傭。因此，這名員工剛好工作了一年(即1999年)，共計365天。

使用SELECT子句中的第5個元素，可以計算雇傭員工的天數，第5個元素是一個表達式。該表達式說明，在包含日期信息的列上執行的算術運算返回數字值，這些數值表示某個數量的天數。

為了強制減法運算的運算符優先級，可以用圓括號包含子表達式end_date-start_date。加1，以補償減法運算中減少的一天。

2．表達式和列別名

圖7-8引入了一個新概念——列別名。注意，表達式列有一個名為Days Employed的有意義的題頭。這個題頭就是別名。別名是列或者表達式的另一種可選名稱。如果該表達式沒有使用別名，列題頭就是(END_ DATE-START_DATE)+1，這不太用戶友好。別名對于表達式和計算特別有用，并且可以用幾種方式實現它們。在SELECT語句中，有一些規則控制列別名的使用。圖7-8中的別名“Days Employed”通過保留空格并且在雙引號中輸入別名來指定。這些引號必不可少，原因有兩點：第一，該別名由多個單詞組成。第二，只有用雙引號包含別名，才能保留別名的大小寫。如果在指定空格分隔的多單詞的別名時，沒有使用雙引號，就會返回“ORA-00923:FROM keyword not found where expected”錯誤。SQL提供了一種更正式的方法來插入別名——在列或表達式和別名之間插入AS關鍵字，如下列查詢的第一行中所示：

        SELECT EMPLOYEE_ID AS "Employee ID",
              JOB_ID AS "Occupation",
              START_DATE, END_DATE,
              (END_DATE-START_DATE)+1 "Days Employed"
        FROM JOB_HISTORY;

3．字符和字符串串聯運算符

雙管道符號||表示字符串聯運算符。這個運算符用來將字符表達式或者列聯接在一起，從而創建一個更大的字符表達式。表的列可以相互鏈接或者鏈接字面字符的字符串，以創建一個合成字符表達式。

串聯運算符非常靈活，可以在字符表達式中的幾乎任何位置多次使用。考慮下面這個查詢：

        SELECT 'THE '||REGION_NAME||' region is on Planet Earth' "Planetary Location",
        FROM REGIONS;

這里，字符字面值“The”串聯到REGION_NAME列的內容。新的字符串進一步串聯到字符字面值“region is on Planet Earth”，再使用友好的列題頭“Planetary Location”作為整個表達式的別名。

4．字面值和DUAL表

字面值在表達式中很常見。這些值表示SELECT子句中不來自任何數據庫對象的數字、字符或者日期和時間值。將字符字面值串聯到現有列數據通常很有用，那么應該如何處理與現有列數據無關的字面值？為了確保關系一致，對于使用數據庫計算與任何表或者列都無關的表達式這個問題，Oracle提供了一種很好的解決方案。為了使用數據庫來計算表達式，必須提交在語法上合法的SELECT語句。如果要知道兩個數字型字面值的和，又該怎么辦？需要用關系方式與數據庫交互。因此Oracle提供了一種特殊的單行單列表DUAL，來解決與作用于字面表達式的數據庫的交互問題。

回憶一下前面描述的DUAL表。它包含名為字符數據類型的DUMMY列。可以執行查詢：SELECT * FROM DUAL，返回數據值“X”作為DUMMY列的內容。在開發過程中，通過查詢DUAL表，來測試復雜表達式是評估這些表達式是否正確運行的有效方法。可以從任何表中查詢字面表達式，但記住，表中的每一行都要處理表達式，而查詢DUAL表只返回一行。

        select 'literal '||'processing using the REGIONS table'
        from regions;

       select 'literal '||'processing using the DUAL table'
        from dual;

第一條語句在結果集中返回四行，因為在REGIONS表中有四行數據，而第二行語句只返回一行。

5．兩個單引號或者可選擇的引用運算符

到目前為止串聯的字面字符串，都是將單數形式的單詞附加到列表達式的前面或后面。這些字符字面值使用單引號指定。例如：

        select 'I am a character literal string' from dual;

那么，該怎樣處理包含單引號的字符字面值呢？復數造成了字符字面處理的特殊問題。考察下面的語句：

        select 'Plural's have one quote too many' from dual;

執行這條語句將導致產生一個Oracle錯誤。那么，如何處理包含單引號的句子呢？主要可以使用兩種方法。其中最常見的是在字符串中應該出現的單引號前面再添加一個單引號。下面的語句說明了用字面值’Plural''s取代字符字面值’Plural's，就可以避免這個錯誤。

        select 'Plural''s have one quote too many' from dual;

使用兩個單引號來處理字符字面值中應該出現的單引號的方法，會隨著受影響字面值的增加而變得混亂和容易出錯。Oracle用可選擇的引用運算符(q)的形式提供了處理這類字符的簡潔方法。注意，產生這個問題是因為Oracle選擇單引號字符作為封裝或者包含其他字符字面值的特殊符號。這些封裝字符的符號可以是除了單引號之外的任何符號。

考慮可選擇的引用運算符(q)。q運算符允許從一組可以用于封裝字符字面值的符號對中選擇一種，作為單引號的替代符號。它們可以是任何單字節、多字節字符或者四種括號——圓括號()、花括號{}、方括號[]或者尖括號< >。使用q運算符，實際上可以將字符分隔符從單引號改變為其他任何字符，如下所示：

        SELECT q'<Plural's can also be specified with alternate quote operators>'
        FROM DUAL;

       SELECT  q'[Even square brackets [] can be used for Plural's]' FROM DUAL;

       SELECT  q'XWhat about UPPER CASE X for Plural's ? X'
        FROM DUAL;

可選擇的引用運算符的語法如下所示：

        q'delimiter character literal which may include single quotes delimiter'

其中delimiter可以是任何字符或括號。第一個和第二個示例顯示將尖括號和方括號作為字符分隔符，而第三個示例說明如何通過可選擇的引用運算符將大寫字母“X”用作特殊字符分隔符。注意，字符“X”本身可包括在字符串中——只要其后面不跟引號。

7.3.4 NULL

空值表示沒有數據。包含空值的行沒有該列的數據。空值通常定義為不可用、未分配、未知或者不適用的值。沒有注意空值的特殊要求，通常會導致錯誤，或者更糟糕的是，導致答案不正確。本節重點討論使用SELECT語句與空值列數據的交互以及它對表達式的影響。

1．Not Null和Nullable列

表存儲數據行，這些行被劃分為一列或者多列。列有相應的名稱和數據類型。其中有些列被數據庫規則限制為強制列。在每行的NOT NULL列中，會強制存儲某些數據。然而，當數據庫約束沒有強制表的列存儲行的數據時，這些列就有可能為空。

Oracle提供了一種機制，可以使用第8章討論的一般函數在算術上與NULL值交互。除以空值也會得到空值，和除以零不一樣(它會產生錯誤)。當使用字符串聯運算符遇到空值時，會忽略空值，而涉及空值的算術運算總是產生空值。

2．外鍵和Nullable列

當表通過主鍵和外鍵關系相互關聯時，數據模型設計有時會產生問題，但基于外鍵的列可以為空。

DEPARTMENTS表以DEPARTMENT_ID列作為其主鍵。EMPLOYEES表包含DEPARTMENT_ID列，它由外鍵關系限制到DEPARTMENTS表中的DEPARTMENT_ID列。這意味著EMPLOYEES表中的DEPARTMENT_ID列不能記錄在DEPARTMENTS表中沒有的值。這種引用完整性構成了第三范式的基礎，對于整個數據庫的完整性也至關重要。

但如果是NULL值又如何？DEPARTMENTS表中的DEPARTMENT_ID列可以包含空值嗎？答案是不能。Oracle堅持主鍵的所有列都被隱式限制為強制列。但對外鍵列的隱式約束又怎樣呢？這是Oracle的難題，因為為了保持靈活性和迎合廣大用戶，它無法堅持說由引用完整性約束的列必須是強制的。而且，不是所有情況都需要這種功能。

EMPLOYEES表中的DEPARTMENT_ID列實際上可以為空值。因此，在表中可能包含空的DEPARTMENT_ID值的記錄。實際上，在EMPLOYEES表中也有這樣的記錄。HR數據模型允許員工(不管正確與否)不屬于任何部門。當實現兩個表之間的關系聯接時，完全可以遺漏或者排除某些在聯接列中包含空值的記錄。第10章將討論應對這種挑戰的方法。

練習7-2 構造表達式

在這個練習中，將使用表達式和別名構造一個查詢，以用戶友好的方式顯示HR模式的結果。

(1) 查詢HR.JOBS表并返回一個格式為The Job Id for the <job_title's> job is: <job_id>的表達式。注意，job_title 應有一個單引號，并且給它添加“s”，以便看起來更自然。工作頭銜是主席的輸出樣本是“The Job Id for the President's job is: AD_PRES”。使用AS關鍵字給該列表達式起一個別名為Job Description。對此問題有多種解決方案。這里選擇的方法是用另一個單引號處理自然出現的單引號。也可以使用可選擇的引用運算符，用另一字符來限定自然出現的引號。

(2) 需要別名為Job Description的單個表達式；通過將字面值“The Job Id for the”串聯到JOB_TITLE列，然后再串聯到字面值“'s job is:”，最后再串聯JOB_ID列就可構造它。添加另一個單引號就生成如下所示的SELECT語句：

        select 'The Job Id for the '||job_title||'''s job is: '||job_id
        AS "Job Description" from jobs;

7.4 限制查詢檢索的行

關系理論的基本原則之一是選擇。使用SELECT語句的WHERE子句(有時被稱為謂詞)實現選擇。限制返回數據集的條件有許多形式，它們對列和表達式都會產生影響。只有表中符合這些條件的行才會返回。條件使用比較運算符以及列和字面值來限制行。布爾運算符能夠指定多種條件，進而限制返回的行。當在SELECT語句中遇到布爾、條件、串聯和算術運算符時，需要確定它們的優先順序。

7.4.1 WHERE子句

WHERE子句通過依據一種或者多種條件限制返回的行，來擴充SELECT語句。只使用SELECT和FROM子句查詢表會返回表中所保存的數據的所有行。使用DISTINCT關鍵字，排除重復值，在某種程度上限制了結果行。如果需要從表中檢索非常明確的信息——例如，只需要某些列包含特定值的數據時，情況又如何？如何從COUNTRIES表中檢索位于歐洲地區的國家？如何只檢索作為銷售代表的員工？使用WHERE子句來指定必須返回哪些行，就可以回答這些問題。包含WHERE子句的SQL SELECT語句的格式為：

        SELECT *|{[DISTINCT] column|expression [alias], ...}
        FROM table
        [WHERE condition(s)];

WHERE子句總是緊跟在FROM子句之后。方括號表示WHERE子句是可選的。可以同時應用一種或者多種條件來限制結果集。使用條件運算符來比較兩個項，這樣可以指定條件。這些項可能是列值、字面值或者表達式。等于運算符(equality operator)最常用于限制結果集。使用WHERE子句的一個示例如下所示：

        select country_name
        from countries
        where region_id=3;

這個示例從COUNTRIES表中投影COUNTRY_NAME列。WHERE子句將返回的行限制為REGION_ID列中包含3的那些行，而不是選擇所有行。

1．基于數字列的條件

條件必須適應不同的列數據類型。可以用幾種不同的方法指定依據數字列來限制行的條件。考慮EMPLOYEES表中的SALARY列。這一列的數據類型為NUMBER(8,2)。可像如下所示限制SALARY列。

        select last_name, salary from employees where salary = 10000;

上面的語句可檢索薪水為$10 000的員工的LAST_NAME和SALARY值，因為運算符兩邊的數據類型匹配、可兼容。

可以將數字列與相同行中的另一個數字列進行比較，從而構造一個WHERE子句條件，如下面的查詢所示：

        select last_name, salary from employees
        where salary = department_id;

這個WHERE子句的限制過于嚴格，因而沒有選中行。這是因為SALARY值的范圍是2100～24000，而DEPARTMENT_ID值的范圍是10～110。因為DEPARTMENT_ID值和SALARY值的范圍沒有重疊，所以沒有滿足這種條件的行，因此什么也沒有返回。

還可以使用WHERE子句條件來比較數字列與表達式，或是將表達式與其他表達式相比較。

        select last_name, salary from employees
        where salary = department_id*100;

       select last_name, salary from employees
        where salary/10 = department_id*10;

第一個示例對于每一行而言，都要將SALARY列中的值與DEPARTMENT_ID值和100的乘積進行比較。第二個示例比較兩個表達式。注意，兩個示例中的條件在代數學中是相等的，執行時返回相同的結果集。

2．基于字符的條件

在條件子句內用單引號包含字符字面值，就可以指定依據字符數據確定選擇哪些行的條件。EMPLOYEES表中的JOB_ID列的數據類型為VARCHAR2(10)。假設想獲取當前為銷售代表的員工的LAST_NAME值的列表。銷售代表的JOB_ID值是SA_REP。下面的語句可以生成這樣的列表：

        select last_name from employees where job_id='SA_REP';

如果沒有用引號指定字符字面值，就會出現Oracle錯誤。記住，字符字面值數據區分大小寫，因此下面的WHERE子句是不對等的：

        子句1: where job_id=SA_REP
        子句2: where job_id='Sa_Rep'
        子句3: where job_id='sa_rep'

子句1產生“ORA-00904:‘SA_REP': invalid identifier”錯誤，因為字面值SA_REP沒有包含在單引號內。子句2和子句3在語法上是正確的，但并不對等。而且，這些子句都不會生成任何數據，因為EMPLOYEES表中沒有行的JOB_ID列值是Sa_Rep或者sa_rep。

基于字符的條件并不只限于比較列值和字面值。還可以使用其他字符列和表達式來指定它們。基于字符的表達式可以構成由條件運算符劃分的條件的一個或者兩個部分。通過將字面值連接到一個或者多個字符列，就可以形成字符表達式。下面4個子句說明了基于字符的表達式的幾個條件：

        子句1: where 'A '||last_name||first_name = 'A King'
        子句2: where first_name||' '||last_name = last_name||' '||first_name
        子句3: where 'SA_REP'||'King' = job_id||last_name
        子句4: where job_id||last_name ='SA_REP'||'King'

子句1將字符串字面值’A’連接到LAST_NAME和FIRST_NAME列。將這個表達式與字面值’A King’進行比較，返回所有滿足條件的行。子句2說明，字符表達式可以放在條件運算符的兩邊。子句3說明字面值表達式也可以放在條件運算符的左邊。從邏輯上說它與子句4對等，子句4交換了子句3中的操作數。子句3和子句4會返回相同的數據行。

3．基于日期的條件

當保存日期和時間信息時，DATE列就非常有用。日期字面值和字符數據一樣都必須包含在單引號內。在WHERE條件子句中使用時，DATE列會與其他DATE列、字面值或表達式進行比較。這些字面值會依據默認的日期格式(即DD-MON-RR)自動轉換為DATE值。如果字面值出現在涉及DATE列的表達式中，就會使用默認格式掩碼將它自動轉換為日期值。DD表示日，MON表示月的前三個字母，RR表示順應2000年的年(也就是說，如果RR介于50～99之間，Oracle服務器就會返回20世紀的日期值，否則返回21世紀的日期值)。也可以指定完整的4位數年份YYYY。例如下面的4個SQL語句：

        子句1: where start_date = end_date
        子句2: where start_date = '01-JAN-2001'
        子句3: where start_date = '01-JAN-01'
        子句4: where start_date = '01-JAN-99'

第一個子句測試兩個DATE列之間的相等性。會返回START_DATE和END_DATE列中包含相同值的行。然而請注意，只有所有部分(包括日、月、年、時、分和秒)都完全匹配， DATE值才相等。第8章將詳細討論存儲DATE值。到目前為止，不必考慮時、分和秒部分。在第二個WHERE子句中，START_DATE列比較字符字面值：“01-JAN-2001”。已經指定完整的4位數年份(YYYY)。這是Oracle服務器可以接受的。第三個條件與第二個條件相等，因為字面值“01-JAN-01”被轉換為日期值01-JAN-2001。這是因為RR部分小于50，因此將21世紀，即20，放在年RR部分的前面作為世紀值。在第4個條件中，字面值“01-JAN-99”的世紀部分變成20世紀，即19，產生的日期值為01-JAN-1999，這是因為RR部分是99，大于50。

在包含DATE值的表達式中也支持使用加減運算符的算術運算。表達式END_DATE -START_ DATE返回數字值，它表示START_ DATE和END_DATE之間的天數。表達式START_DATE+30返回START_DATE之后30天的DATE值。

7.4.2 比較運算符

等于運算符被廣泛用于說明使用WHERE子句限制行的概念，也可以使用其他一些可選運算符。不等于運算符(如“小于”或者“大于等于”)可以用來返回符合不等于條件的行。BETWEEN運算符可以實現基于范圍的比較，以測試列值是否介于兩個值之間。IN運算符測試集合關系，如果條件中測試的列值是字面值集合的成員，那么會返回行。模式匹配比較運算符LIKE非常強大，它允許字符列數據部分匹配符合特定模式的字面值。本節討論的最后一個比較運算符是IS NULL運算符，它返回列值包含空值的行。這些運算符可以在WHERE子句中組合使用。

1．等于和不等于運算符

限制查詢返回的行需要指定合適的WHERE子句。如果子句的限制過于嚴格，那么幾乎不會返回行。如果寬泛地指定條件子句，那么實際返回的行就多于要求返回的行。探討不同的可用運算符可以用語言請求自己感興趣的行。測試條件中的相等性很自然也很直觀。使用“等于(=)”運算符構成這種條件。如果一行的等于條件為真，那么返回該行。考察下面的查詢：

        select last_name, salary from employees where job_id='SA_REP';

該查詢測試EMPLOYEES表中各行的JOB_ID列與字符字面值SA_REP的相等性。如果字符信息相等，那么必須匹配正確的大小寫。當滿足這種匹配時，會返回投影的LAST_NAME和SALARY列的值。注意，盡管條件子句建立在JOB_ID列的基礎之上，但查詢不需要投影該列。

基于不等于的條件強化WHERE子句規范。使用不等于和等于運算符可以實現范圍和模式匹配比較，但通常更傾向于使用BETWEEN運算符和LIKE運算符來實現這種比較。不等于運算符如表7-6所示。

不等于運算符實現基于范圍的查詢。應用程序可能要求提供一個結果集，其中某個列值大于另一個值。例如，下面的查詢可以獲得薪水大于$5000的員工的LAST_NAME和SALARY值的列表：

        select last_name, salary from employees where salary > 5000;

下面的子句使用復合不等于運算符(由多個符號組成)：

        子句1: where salary <= 3000
        子句2: where salary <> department_id

子句1返回的行包含的SALARY值小于等于3000。子句2是“不等于”運算符的一種形式，返回SALARY列值不等于DEPARTMENT_ID值的行。

數字不相等當然很直觀。然而，字符和日期的比較則比較復雜。測試字符不相等很有意思，因為不等于運算符兩邊被比較的字符串被轉換為其字符的數字表示。依據數據庫字符集和國家語言支持(National Language Support, NLS)設置，每個字符串都分配了一個數值。這些數值就是計算不等于比較的基礎。例如下面的語句：

        select last_name from employees where last_name < 'King';

字符字面值’King’被轉換為數字表示。假設US7ASCII數據庫字符集使用AMERICAN NLS設置，那么字面值’King’被轉換為其各個字符的值：K (75)、i (105)、n (110)和g (103)。對于EMPLOYEES表中的各行而言，同樣將LAST_ NAME列數據轉換為每個字符的數值，再與字面值’King’中各個字符的值比較。例如，與LAST_NAME='Kaufling’的行進行比較，兩個字符串的第一個字符是’K'，其對應的值是75。所以，第二個字符(i=105)與(a=97)比較。因為(97 < 105)或(a < i), 'Kaufling' < 'King'，于是選擇該行。使用不等于運算符比較數值數據的過程同樣適用于字符數據。唯一的區別是Oracle服務器會根據某些數據庫設置，把字符數據隱式轉換為數值。

對日期值的不等于比較與字符數據的比較過程類似。Oracle服務器將日期保存為內部數字格式，在條件內比較這些值。

2．使用BETWEEN運算符進行范圍比較

BETWEEN運算符測試列或者表達式值是否介于兩個邊界值的范圍之內。被測試值至少與低邊界值相等，或者最大與高邊界值相等，或者在這個范圍之內，這樣條件才為true。

假設要查詢薪水介于$3400～$4000之間的員工的姓。一種使用BETWEEN運算符的可能解決方案如下所示：

        select last_name from employees where salary between 3400 and 4000;

用BETWEEN運算符指定條件相當于使用兩個基于不等于的條件：

        select last_name from employees where salary >=3400 and salary <=4000;

使用BETWEEN運算符指定范圍條件更簡潔、更方便。

3．用IN運算符進行集合比較

IN運算符測試某項是不是一組字面值的成員。用逗號將字面值分開，并將它們包含在圓括號內，通過這樣的方式來指定集合。如果字面值是字符或者日期值，那么必須使用單引號界定它們。在集合中可以包含任意多個字面值。考察下面的示例：

        select last_name from employees where salary in (1000,4000,6000);

每行中的SALARY值都會與集合中指定的字面值比較相等性。如果SALARY值等于1000、4000或者6000，那么會返回該行的LAST_NAME值。下面兩條語句說明IN運算符在字符和日期數據中的用法：

        select last_name from employees
        where last_name in ('King', 'Kochhar');

       select last_name from employees
        where hire_date in ('30-JAN-2004', '21-SEP-2005');

4．使用LIKE運算符進行模式比較

LIKE運算符專用于字符數據，它提供了搜索字母或者單詞的強大機制。LIKE還有兩個通配符：百分比符號(%)和下劃線字符(_)。百分比符號用來指定零個或多個通配符字符，而下劃線字符指定一個通配符字符。通配符字符可以表示任何字符。

可以使用下面的查詢提供名字以字母“A”開頭的員工列表：

        select first_name from employees where first_name like 'A%';

與FIRST_NAME列比較的字符字面值包含在單引號內，就像正常的字符字面值一樣。另外，它有百分比符號，在LIKE運算符的上下文中，它有特殊含義。百分比符號替代附加給字母A的零個或者更多字符。通配符字符可以出現在字符字面值的開頭、中間或者結尾。它們甚至能夠單獨出現，例如：

        where first_name like '%';

這里，將返回包含FIRST_NAME值不是空值的所有行。當使用LIKE運算符時，通配符不是強制的。在這種情況下，LIKE運算符就像是測試準確字符匹配的等于運算符；因此，下面兩個WHERE子句是相等的：

        where last_name like 'King';
        where last_name = 'King';

下劃線通配符替代字面值中的一個字符。假設要搜索員工，這些員工姓氏的長度為4個字母，以字母“K”開頭，第二個字母未知，結尾為ng。可以執行下面的語句：

        where last_name like 'K_ng';

如圖7-9所示，在一個WHERE條件中可以單獨、一起甚至是多次使用這兩個通配符。第一個查詢檢索這樣的記錄：其中COUNTRY_NAME以字母“I”開頭，后面是一個或者多個字符，其中一個字符必須是小寫字母“a”。

第二個查詢檢索的國家其名稱以字母“i”作為第5個字母。COUNTRY_NAME值的長度和它們的開頭字母并不重要。WHERE子句中小寫字母“i”之前的4個下劃線通配符符號表示4個字符(可能是任何字符)。第5個字母必須是“i”，百分比符號指定從第6個字符開始， COUNTRY_NAME可以有零個或者多個字符。

當搜索包含百分比或者下劃線字符的字面值時，會出現什么情況？與ESCAPE字符結合使用ESCAPE標識符，可以轉義(或者作為正常的非特殊符號對待)自然出現的下劃線字符。在下面的例子中，返回以三個字符“SA_”開頭的所有JOB_ID值：

        select job_id from jobs
        where job_id like 'SA\_%' escape '\';

一般來說，ESCAPE字符是反斜杠符號，但它不一定要這樣。下面的語句與上面的語句對等，但使用美元符號($)作為ESCAPE字符。

        select job_id from jobs
        where job_id like 'SA$_%' escape '$';

當百分比符號作為字符數據自然出現時，同樣可以轉義它。

練習7-3 使用LIKE運算符

構造一個查詢從DEPARTMENTS表中檢索以“ing”這三個字符結尾的部門名稱列表。

(1) 啟動SQL*Plus，連接到HR模式。

(2) WHERE子句必須執行DEPARTMENT_ NAME列值與以零個或者多個字符開頭但以三個特定字符“ing”結尾的模式的比較。用于字符模式匹配的運算符是LIKE運算符。DEPARTMENT_ NAME列必須符合的模式是’%ing'。

(3) 因此，正確的查詢是：

        select department_name from departments where department_name like '%ing';

5．使用IS NULL運算符進行NULL比較

數據庫表中不可避免會出現NULL值。有時要求只搜索特定列中是NULL值的那些記錄。IS NULL運算符只選擇特定列值為NULL的行。使用IS NULL運算符而不是“等于”運算符(=)來測試列值是不是等于NULL。

例如下面的查詢，它從EMPLOYEES表中提取某些行的LAST_NAME列，這些行的COMMISSION_ PCT列中保存有NULL值：

        select last_name from employees
        where commission_pct is null;

這個WHERE子句只檢索COMMISSION_PCT值為NULL的記錄。

7.4.3 布爾運算符

布爾(Boolean)或者邏輯(logical)運算符允許在SELECT語句的WHERE子句中指定多個條件。這提供了更精練的數據提取能力。假設要提取FIRST_NAME值以字母“J”開頭并且COMMISSION_PCT值大于10%的員工記錄。首先，必須將EMPLOYEES表中的數據限制為這樣的FIRST_NAME值“J%”；第二，必須測試記錄的COMMISSION_PCT值以確定它們是不是大于10%。使用布爾AND運算符可以將這兩個獨立條件聯系起來，從而能夠在WHERE子句中同時使用它們。使用OR、AND和NOT布爾運算符可以檢索符合任意或者全部條件，或者符合一個或多個條件的否定條件的結果集。

1．AND運算符

AND運算符將一些條件合并成一個更大的條件，結果集中包含的行必須符合這個大條件。如果用AND運算符連接WHERE子句中指定的兩個條件，那么在檢索之前，就會連續測試行，看它是否滿足這兩個條件。如果它不滿足或者只滿足其中一個，就不會返回行。使用下面的查詢可以檢索FIRST_NAME值以字母“J”開頭并且COMMISSION_PCT值大于10%的員工記錄：

        select first_name, last_name, commission_pct, hire_date from employees
        where first_name like 'J%' and commission_pct > 0.1;

注意，現在WHERE子句有兩個條件，但只有一個WHERE關鍵字。AND運算符分隔這兩個條件。要進一步指定更多強制條件，可以添加它們，并確保用AND運算符分隔它們。可以指定任意多個條件。但是請記住，指定的AND條件越多，查詢條件就越苛刻。

2．OR運算符

OR運算符分隔多個條件，結果集中包含的行必須至少滿足其中一個條件。如果用OR運算符連接WHERE子句中指定的兩個條件，那么在檢索之前，就會連續測試行，看它滿足一個條件還是兩個條件。對于返回的記錄而言，只要符合OR條件中的一個條件就行了。如果這些條件都不滿足，就不會返回行。檢索員工記錄，要求他們的FIRST_NAME值以字母“B”開頭或者COMMISSION_ PCT值大于35%，該查詢如下所示：

        select first_name, last_name, commission_pct, hire_date from employees
        where first_name like 'B%' or commission_pct > 0.35;

注意，用OR關鍵字分隔這兩個條件，會返回所有FIRST_NAME值以大寫字母“B”開頭的員工記錄，不管COMMISSION_PCT值是什么(即使它們是NULL)。同時還會返回COMMISSION_PCT值大于35%的所有記錄，不管FIRST_NAME值以什么字母開頭。

通過用OR運算符分隔條件，還可以指定更多OR條件。指定的OR條件越多，查詢的限制就越寬松。

3．NOT運算符

NOT運算符會否定條件運算符。結果集中包含的行必須符合條件的邏輯非。NOT運算符可以否定條件運算符，如表7-7中的WHERE子句所示。

NOT運算符否定條件中的比較運算符，不管它是等于、不等于、基于范圍、模式匹配、集合成員還是NULL測試運算符。

7.4.4 優先規則

在WHERE子句的上下文中可以使用算術、字符、比較和布爾表達式。但這些運算符如何相互交互呢？前面提及的運算符的優先層次結構如表7-8所示。

表達式中相同優先級的運算符按從左到右的順序計算。當NOT運算符修改LIKE、IS NULL和IN比較運算符時，優先級與這些運算符的肯定形式相同。

例如下面的SELECT語句，它說明了各種不同運算符之間的交互：

        select last_name, salary, department_id, job_id, commission_pct
        from employees
        where last_name like '%a%' and salary > department_id * 200
        or
        job_id in ('MK_REP', 'MK_MAN') and commission_pct is not null

依據兩個離散條件從EMPLOYEES表中投影LAST_NAME、SALARY、DEPARTMENT_ID、JOB_ID和COMMISSION_PCT列。第一個條件檢索其LAST_NAME字段中包含字符“a”，且SALARY值大于DEPARTMENT_ID值的200倍的記錄。查詢會在不等于運算符之前計算DEPARTMENT_ ID和200的乘積，因為乘法的優先級比不等于運算符的優先級高。

第二個條件返回JOB_ID值為MK_MAN或者MK_REP的那些行，其中COMMISSION_PCT值為非空。該查詢返回的行要滿足由OR分隔的兩個條件中的任一個。改變WHERE子句中條件的順序就是依據運算符的不同優先級改變其含義。考慮下面的查詢：

        select last_name, salary, department_id, job_id, commission_pct
        from employees
        where last_name like '%a%' and salary > department_id * 100 and commission_pct
    is not null
        or
        job_id = 'MK_MAN'

這個查詢有兩個組成條件。第一個條件檢索其LAST_NAME字段中包含字符“a”，且SALARY值大于DEPARTMENT_ID值的100倍，且其中COMMISSION_PCT值為非空的記錄。第二個條件返回JOB_ID值為MK_MAN的行。這個查詢返回這樣的行——它符合條件1或者(OR)條件2，但不需要同時滿足兩個條件。