SQL邏輯運算子

2019-10-16 22:50:52

1 ALL 如果所有比較都為真，則返回`true`
2 AND 如果兩個表示式都為真，則返回`true`
3 ANY 如果任何一個比較為真，則返回`true`
4 BETWEEN 如果運算元在一個指定範圍內，則返回`true`
5 EXISTS 如果子查詢有結果集，則返回`true`
6 IN 如果運算元等於列表中的值之一，則返回`true`
7 LIKE 如果運算元與模式匹配，則返回`true`
8 NOT 反轉其他布林運算子的結果。
9 OR 如果任一表示式為真，則返回`true`
10 SOME 如果某些表示式為真，則返回`true`

1. AND運算子

`AND`運算子用於在SQL語句的`WHERE`子句中構造多個條件，例如：`SELECT``UPDATE``DELETE`語句中：

``````expression1 AND expression2
``````

``````SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary > 5000 AND salary < 7000
ORDER BY salary;
``````

``````+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Bruce      | Wong      | 6000   |
| Pat        | Zhou      | 6000   |
| Charles    | Yang      | 6200   |
| Shanta     | Liu       | 6500   |
| Susan      | Zhou      | 6500   |
| Min        | Su        | 6900   |
+------------+-----------+--------+
6 rows in set
``````

2. OR運算子

`AND`運算子類似，`OR`運算子用於在SQL語句的`WHERE`子句中組合多個條件：

``````expression1 OR expression2
``````

``````SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary = 8000 OR salary = 9000
ORDER BY salary;
``````

``````+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Matthew    | Han       | 8000   |
| Alexander  | Lee       | 9000   |
| Daniel     | Chen      | 9000   |
+------------+-----------+--------+
3 rows in set
``````

3. IS NULL運算子

`IS NULL`運算子將列的值與`null`值進行比較，如果比較的值為`null`，則返回`true`; 否則，它返回`false`。 例如，以下語句查詢沒有電話號碼的所有員工資訊：

``````SELECT
first_name, last_name, phone_number
FROM
employees
WHERE
phone_number IS NULL
ORDER BY first_name , last_name;
``````

``````+------------+-----------+--------------+
| first_name | last_name | phone_number |
+------------+-----------+--------------+
| Charles    | Yang      | NULL         |
| Jack       | Yang      | NULL         |
| John       | Liu       | NULL         |
| Jonathon   | Yang      | NULL         |
| Karen      | Liu       | NULL         |
| Kimberely  | Yang      | NULL         |
+------------+-----------+--------------+
6 rows in set
``````

4. BETWEEN運算子

`BETWEEN`運算子用於搜尋在給定最小值和最大值範圍內的值。 請注意，最小值和最大值包含在條件集合中。

``````SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary BETWEEN 8000 AND 10000
ORDER BY salary;
``````

``````+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Matthew    | Han       | 8000   |
| John       | Chen      | 8200   |
| Max        | Han       | 8200   |
| William    | Wu        | 8300   |
| Jack       | Yang      | 8400   |
| Jonathon   | Yang      | 8600   |
| Alexander  | Lee       | 9000   |
| Daniel     | Chen      | 9000   |
| Hermann    | Wu        | 10000  |
+------------+-----------+--------+
9 rows in set
``````

5. IN運算子

`IN`運算子將值與指定值列表進行比較。 如果比較值與列表中的其中一個值匹配，則`IN`運算子返回`true`; 否則返回`false`

``````SELECT
first_name, last_name, department_id
FROM
employees
WHERE
department_id IN (1 , 9)
ORDER BY department_id;
``````

``````+------------+-----------+---------------+
| first_name | last_name | department_id |
+------------+-----------+---------------+
| Jennifer   | Zhao      |             1 |
| Steven     | Lee       |             9 |
| Neena      | Wong      |             9 |
| Lex        | Liang     |             9 |
+------------+-----------+---------------+
4 rows in set
``````

6. LIKE運算子

`LIKE`運算子使用萬用字元運算子將值與類似值進行比較。 SQL提供了兩個與`LIKE`運算子一起使用的萬用字元：

• 百分號(`%`)表示零個，一個或多個字元。
• 下劃線符號(`_`)表示單個字元。

``````SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
first_name LIKE 'Ma%'
ORDER BY first_name;
``````

``````+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         120 | Matthew    | Han       |
|         112 | Max        | Su        |
|         121 | Max        | Han       |
+-------------+------------+-----------+
3 rows in set
``````

``````SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
first_name LIKE '_i%'
ORDER BY first_name;
``````

``````+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         107 | Diana      | Chen      |
|         178 | Kimberely  | Yang      |
|         201 | Michael    | Zhou      |
|         113 | Min        | Su        |
|         122 | Min        | Liu       |
|         117 | Sigal      | Zhang     |
|         206 | William    | Wu        |
+-------------+------------+-----------+
7 rows in set
``````

7. ALL運算子

`ALL`運算子將值與另一個值集中的所有值進行比較。 `ALL`運算子必須以比較運算子開頭，後跟子查詢。

``````comparison_operator ALL (subquery)
``````

``````SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary >= ALL (SELECT
salary
FROM
employees
WHERE
department_id = 8)
ORDER BY salary DESC;
``````

``````+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Steven     | Lee       | 24000  |
| Neena      | Wong      | 17000  |
| Lex        | Liang     | 17000  |
| John       | Liu       | 14000  |
+------------+-----------+--------+
4 rows in set
``````

8. ANY運算子

`ANY`運算子根據條件將值與集合中的任何值進行比較，如下所示：

``````comparison_operator ANY(subquery)
``````

`ALL`運算子類似，`ANY`運算子必須以比較運算子開頭，後跟子查詢。

``````SELECT
first_name, last_name, salary
FROM
employees
WHERE
salary > ANY(SELECT
AVG(salary)
FROM
employees
GROUP BY department_id)
ORDER BY first_name , last_name;
``````

``````+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Alexander  | Lee       | 9000   |
| Avg        | Su        | 11000  |
| Bruce      | Wong      | 6000   |
| Charles    | Yang      | 6200   |
| Daniel     | Chen      | 9000   |
... ...
| Shelley    | Wu        | 12000  |
| Steven     | Lee       | 24000  |
| Susan      | Zhou      | 6500   |
| Valli      | Chen      | 4800   |
| William    | Wu        | 8300   |
+------------+-----------+--------+
32 rows in set
``````

9. EXISTS運算子

`EXISTS`運算子測試子查詢是否包含任何行：

``````EXISTS (subquery)
``````

``````SELECT
first_name, last_name
FROM
employees e
WHERE
EXISTS( SELECT
1
FROM
dependents d
WHERE
d.employee_id = e.employee_id);
``````

``````+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Steven     | Lee       |
| Neena      | Wong      |
| Lex        | Liang     |
| Alexander  | Lee       |
| Bruce      | Wong      |
| David      | Liang     |
| Valli      | Chen      |
| Diana      | Chen      |
... ...
| Shelley    | Wu        |
| William    | Wu        |
+------------+-----------+
30 rows in set
``````