《Oracle 入門教程》第 05 篇 數(shù)據(jù)過濾

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        5.1 過濾條件
        5.2 模式匹配
        5.3 空值判斷
        5.4 復(fù)合條件
        5.5 去除重復(fù)數(shù)據(jù)

上一篇介紹了如何使用 SELECT 語句查詢表中的數(shù)據(jù)，查詢返回了全部的數(shù)據(jù)行。本篇我們討論如何利用WHERE子句返回滿足條件的數(shù)據(jù)，以及使用DISTINCT去除查詢結(jié)果中的重復(fù)值。
5.1 過濾條件

在實際應(yīng)用中，我們通常不需要查看表中的全部數(shù)據(jù)，而是返回滿足指定條件的數(shù)據(jù)；例如找出某天入職的員工，某個用戶未完成的訂單等。此時，我們就可以利用WHERE子句來實現(xiàn)數(shù)據(jù)的過濾。

SELECT column1, column2, ...
FROM table_name
WHERE conditions;

 

其中，WHERE 子句位于 FROM 子句之后，conditions 用于指定一個或者多個邏輯條件。對于表中的數(shù)據(jù)行，如果滿足該條件就會返回，否則將被忽略。例如：

SELECT emp_name, hire_date, salary
FROM employee
WHERE hire_date = DATE '2018-11-11';

EMP_NAME|HIRE_DATE          |SALARY|
--------|-------------------|------|
鄧芝     |2018-11-11 00:00:00|  4000|

 

其中，WHERE 子句中的等于號（=）是一個比較運算符，只有比較的結(jié)果為真（True）時才返回對應(yīng)的數(shù)據(jù)行。因此，以上語句返回了 2018 年 11 月 11 日入職的員工。

除了等號運算符之外，Oracle 還提供了很多其他的比較運算符，如下表所示：
 

以上這些運算符的作用都比較容易理解，需要注意BETWEEN包含了兩端的值，等價于>=加上<=。例如：

SELECT emp_name,
       salary
FROM employee
WHERE salary BETWEEN 11000 AND 12000;

EMP_NAME|SALARY|
--------|------|
孫尚香   | 12000|

 

運算符IN可以實現(xiàn)一次和多個數(shù)據(jù)的等于比較，例如：

SELECT emp_name,
       salary
FROM employee
WHERE salary IN (10000 ,12000, 15000);

EMP_NAME|SALARY|
--------|------|
孫尚香   | 12000|
趙云     | 15000|
法正     | 10000|

 

以上查詢返回了月薪為 10000、12000 或者 15000 的員工。該查詢也可以使用ANY或者SOME運算符實現(xiàn)：

SELECT emp_name,
       salary
FROM employee
WHERE salary =ANY (10000 ,12000, 15000);

EMP_NAME|SALARY|
--------|------|
孫尚香   | 12000|
趙云     | 15000|
法正     | 10000|

 

    ??SOME 和 ANY 是同義詞，可以互相替換。

運算符ALL表示和列表中的所有數(shù)據(jù)比較，例如：

SELECT emp_name,
       salary
FROM employee
WHERE salary >ALL (10000 ,12000, 15000);

EMP_NAME|SALARY|
--------|------|
劉備     | 30000|
關(guān)羽     | 26000|
張飛     | 24000|
諸葛亮    | 24000|

 

查詢返回的是月薪大于列表中所有數(shù)據(jù)的員工，也就是大于 15000 的員工。

    ??ANY/SOME 以及 ALL 通常和 =、!=、>、>=、 <、<= 等一起使用。同時，IN、ANY/SOME 以及 ALL 中還可以使用子查詢語句，我們在后續(xù)文漲中進行介紹。

5.2 模式匹配

Oracle 提供了字符串的模式匹配功能，也就是LIKE運算符。例如查找名字中包含“飛”字的員工：

SELECT emp_name,
       salary
FROM employee
WHERE emp_name LIKE '%飛%';

 

其中，百分號（%）是一個通配符，表示匹配零個或者多個任意字符。另外，也可以使用下劃線（_）匹配一個任意字符。例如：

    “%飛”匹配以“飛”字結(jié)束的字符串；
    “諸_亮”匹配“諸葛亮”、“諸云亮”等；
    “SQL”匹配“SQL”，但是不能匹配“sql”，因為 LIKE 運算符區(qū)分大小寫。

如果查找的模式自身包含這兩個通配符（% 或 _），可以通過ESCAPE子句指定一個的轉(zhuǎn)義字符。例如：

SELECT 'OK'
FROM dual
WHERE '完成進度 25%。' LIKE '%25\%。' ESCAPE '\';

'OK'|
----|
OK  |

 

語句中的反斜線被定義為轉(zhuǎn)義字符，\% 表示匹配百分號，而不是任意字符。

另外，NOT LIKE運算符執(zhí)行 LIKE 相反的操作。例如：

SELECT emp_name,
       salary
FROM employee
WHERE emp_name NOT LIKE '%飛%';

EMP_NAME|SALARY|
--------|------|
劉備     | 30000|
關(guān)羽     | 26000|
諸葛亮   | 24000|
...

 

以上語句返回了名字中不包含“飛”字的員工。
5.3 空值判斷

在數(shù)據(jù)庫中，空值（NULL）是一個特殊值，代表了未知數(shù)據(jù)或者不適用的場景。Oracle 中的 NULL 不等于數(shù)字 0，也不等于空字符串。如果使用常規(guī)的比較運算符進行 NULL 值進行比較，結(jié)果總是未知。

NULL = 0; -- 結(jié)果未知
NULL = NULL; -- 結(jié)果未知
NULL ！= NULL; -- 結(jié)果未知

 

我們既不確定 NULL 等于 NULL，也不確定 NULL 不等于 NULL，因為任何值和未知數(shù)據(jù)比較的結(jié)果總是未知。如果在 WHERE 子句中使用這種查詢條件，將不會返回任何結(jié)果。

對于 NULL 值的比較，需要使用特殊的運算符：IS NULL。例如：

SELECT emp_name, manager
FROM employee
WHERE manager IS NULL;

EMP_NAME|MANAGER|
--------|-------|
劉備     |       |

 

以上查詢返回了沒有上級領(lǐng)導(dǎo)的員工，也就是公司的最高領(lǐng)導(dǎo)。

另外，IS NOT NULL運算符執(zhí)行與IS NULL相反的操作，返回取值不為空的數(shù)據(jù)。例如：

SELECT emp_name, manager
FROM employee
WHERE manager IS NOT NULL;

EMP_NAME|MANAGER|
--------|-------|
關(guān)羽     |      1|
張飛     |      1|
諸葛亮   |      1|
...

 

5.4 復(fù)合條件

Oracle 中的復(fù)合條件是指通過邏輯運算符（AND、OR、NOT）將多個條件進行組合的結(jié)果。

AND表示邏輯與運算符，它的邏輯真值表如下：
 

對于AND運算符，只有當(dāng)它兩邊的結(jié)果都為真時，最終結(jié)果才為真；否則最終結(jié)果為假，不返回結(jié)果。以下查詢返回薪水為 4000，并且有獎金的員工：

SELECT emp_name, salary, bonus
FROM employee
WHERE salary = 4000
AND bonus IS NOT NULL;

EMP_NAME|SALARY|BONUS|
--------|------|-----|
蔣琬     |  4000| 1500|

 

OR表示邏輯或運算符，它的邏輯真值表如下：
 

OR運算符只要有一個條件為真，結(jié)果就為真。以下查詢返回薪水為 4000，或者有獎金的員工：

SELECT emp_name, salary, bonus
FROM employee
WHERE salary = 4000
OR bonus IS NOT NULL;

EMP_NAME|SALARY|BONUS|
--------|------|-----|
劉備     | 30000|10000|
...
蔣琬     |  4000| 1500|
鄧芝     |  4000|     |

 

邏輯運算符AND和OR使用短路運算；也就是說，只要前面的表達式能夠決定最終的結(jié)果，省略后面的計算。這樣能夠提高運算的效率。因此，以下語句不會產(chǎn)生除零錯誤：

SELECT 1 FROM dual WHERE 1 = 0 AND 1/0 = 1;

SELECT 1 FROM dual WHERE 1 = 1 OR 1/0 = 1;

 

另外，當(dāng)我們組合使用AND和OR運算符時，AND運算符優(yōu)先級更高，先執(zhí)行。例如：

SELECT emp_name, salary, bonus
FROM employee
WHERE salary = 4000
OR salary = 6000
AND bonus IS NOT NULL;

EMP_NAME|SALARY|BONUS|
--------|------|-----|
蔣琬     |  4000| 1500|
鄧芝     |  4000|     |

 

由于 AND 優(yōu)先級高，以上查詢返回的是薪水為 6000 并且有獎金的員工，或者月薪為 4000 的員工。如果相要返回月薪為 4000 或 6000，并且有獎金的員工，可以使用括號：

SELECT emp_name, salary, bonus
FROM employee
WHERE (salary = 4000 OR salary = 6000)
AND bonus IS NOT NULL;

EMP_NAME|SALARY|BONUS|
--------|------|-----|
蔣琬     |  4000| 1500|

 

NOT表示邏輯取反運算符，它的邏輯真值表如下：
 

注意，對于未知的NULL值，經(jīng)過NOT處理之后仍然是未知值。

NOT經(jīng)常和前面介紹的運算符一起使用：

    NOT BETWEEN，位于指定范圍之外；
    NOT IN，不在指定列表之中；
    NOT LIKE，不匹配某個模式。

當(dāng)查詢條件包含復(fù)合邏輯時，它們的運算優(yōu)先級從高到低排列如下：
 

如有必要，可以使用括號調(diào)整多個運算符之間的優(yōu)先級。
5.5 去除重復(fù)數(shù)據(jù)

DISTINCT是一種特殊的運算符，可以去除查詢結(jié)果中的重復(fù)記錄。例如：

SELECT DISTINCT sex
FROM employee;

SEX|
---|
男  |
女  |

 

其中，DISTINCT 位于 SELECT 之后，而不是像其他過濾條件一樣位于 WHERE 之后。如果 DISTINCT 之后指定了多個字段或表達式，表示返回它們組合之后的唯一值：

SELECT DISTINCT dept_id, sex
FROM employee;

DEPT_ID|SEX|
-------|---|
      3|女  |
      1|男  |
      2|男  |
      5|男  |
      4|男  |
      4|女  |

 

    ??Oracle 中的UNIQUE等價于 DISTINCT，但是不屬于 SQL 標(biāo)準(zhǔn)。

與 DISTINCT 相反的關(guān)鍵字是ALL，表示返回全部結(jié)果。我們通常不需要加上 ALL 關(guān)鍵字，因為它是默認值。