在計算機領域有許多偉大的設計理念和思想，例如：

- 在 Unix 中，一切皆檔案。
 - 在物件導向的程式語言中，一切皆物件。

關聯式資料庫同樣也有自己的設計思想：**在 SQL 中，一切皆關係**。

### 關係模型
關係模型（Relational model）由 E.F.Codd 博士於 1970 年提出，以集合論中的關係概念為基礎；無論是現實世界中的實體物件還是它們之間的聯絡都使用關係表示。我們在資料庫系統中看到的關係就是二維表（Table），由行（Row）和列（Column）組成。因此，也可以說關係表是由資料行構成的集合。

![table](https://img-blog.csdnimg.cn/20200228154004171.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center)

關係模型由**資料結構**、**關係操作**、**完整性約束**三部分組成。

- 關係模型中的資料結構就是關係表，包括**基礎表**、**派生表**（查詢結果）和**虛擬表**（檢視）。
 - 常用的關係操作包括**增加**、**刪除**、**修改**和**查詢**（CRUD），使用的就是 SQL 語言。其中查詢操作最為複雜，包括選擇（Selection）、投影（Projection）、並集（Union）、交集（Intersection）、差集（Exception）以及笛卡兒積（Cartesian product）等。
 - 完整性約束用於維護資料的完整性或者滿足業務約束的需求，包括**實體完整性**（主鍵約束）、**參照完整性**（外來鍵約束）以及**使用者定義的完整性**（非空約束、唯一約束、檢查約束和預設值）。

我們今天的主題是關係操作語言，也就是 SQL。本文使用的示例資料來源於[這篇文章](https://blog.csdn.net/horses/article/details/86518676)。

### 面向集合
SQL（結構化查詢語言）是操作關聯式資料庫的標準語言。SQL 非常接近英語，使用起來非常簡單。它在設計之初就考慮了非技術人員的使用需求，我們通常只需說明想要的結果（What），而將資料處理的過程（How）交給資料庫管理系統。所以說，SQL 才是真正給人用的程式語言！?

接下來我們具體分析一下關係的各種操作語句；目的是為了讓大家能夠了解 **SQL 是一種面向集合的程式語言，它的操作物件是集合，操作的結果也是集合**。

> ?在關聯式資料庫中，關係、表、集合三者通常表示相同的概念。

#### SELECT

下面是一個簡單的查詢語句：

```sql
SELECT employee_id, first_name, last_name, hire_date
  FROM employees;
```

它的作用就是從 employees 表中查詢員工資訊。顯然，我們都知道 FROM 之後是一個表（關係、集合）。不僅如此，整個查詢語句的結果也是一個表。所以，我們可以將上面的查詢作為表使用：

```sql
SELECT *
  FROM (SELECT employee_id, first_name, last_name, hire_date
          FROM employees) t;
```

括號內的查詢語句被稱為派生表，我們給它指定了一個別名叫做 t。同樣，整個查詢結果也是一個表；這就意味著我們可以繼續巢狀，雖然這麼做很無聊。

我們再看一個 PostgreSQL 中的示例：

```sql
-- PostgreSQL
SELECT *
  FROM upper('sql');
| upper |
|-------|
|   SQL |
```

upper() 是一個大寫轉換的函式。它出現再 FROM 子句中，意味著它的結果也是一個表，只不過是 1 行 1 列的特殊表。

SELECT 子句用於指定需要查詢的欄位，可以包含表示式、函式值等。SELECT 在關係操作中被稱為投影（Projection），看下面的示意圖應該就比較好理解了。

![projecttion](https://img-blog.csdnimg.cn/20200229211750526.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =619x)

除了 SELECT 之外，還有一些常用的 SQL 子句。

WHERE 用於指定資料過濾的條件，在關係運算中被稱為選擇（Selection），示意圖如下：

![where](https://img-blog.csdnimg.cn/20200229213302814.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =751x)

ORDER BY 用於對查詢的結果進行排序，示意圖如下：

![order](https://img-blog.csdnimg.cn/20200229213446641.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =749x)

總之，SQL 可以完成各種資料操作，例如過濾、分組、排序、限定數量等；所有這些操作的物件都是關係表，結果也是關係表。

![sql](https://img-blog.csdnimg.cn/20200229213751161.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70)

在這些關係操作中，有一個比較特殊，就是分組。

#### GROUP BY
分組（ GROUP BY）操作和其他的關係操作不同，因為它改變了關係的結構。來看下面的示例：

```sql
SELECT department_id, count(*), first_name
  FROM employees
 GROUP BY department_id;
```

該語句的目的是按照部門統計員工的數量，但是存在一個語法錯誤，就是 first_name 不能出現在查詢列表中。原因在於按照部門進行分組的話，每個部門包含多個員工；無法確定需要顯示哪個員工的姓名，這是一個邏輯上的錯誤。

所以說，GROUP BY 改變了集合元素（資料行）的結構，建立了一個全新的關係。分組操作的示意圖如下：

![group](https://img-blog.csdnimg.cn/20200229214341557.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =836x)

儘管如此，GROUP BY 的結果仍然是一個集合。

#### UNION
SQL 面向集合特性最明顯的體現就是 UNION（並集運算）、INTERSECT（交集運算）和 EXCEPT/MINUS（差集運算）。

這些集合運算子的作用都是將兩個集合併成一個集合，因此需要滿足以下條件：

- 兩邊的集合中欄位的數量和順序必須相同；
 - 兩邊的集合中對應欄位的型別必須匹配或相容。

具體來說，UNION 和 UNION ALL 用於計算兩個集合的並集，返回出現在第一個查詢結果或者第二個查詢結果中的資料。它們的區別在於 UNION 排除了結果中的重複資料，UNION ALL 保留了重複資料。下面是 UNION 操作的示意圖：

![union](https://img-blog.csdnimg.cn/20200301145657113.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =272x)

INTERSECT 操作符用於返回兩個集合中的共同部分，即同時出現在第一個查詢結果和第二個查詢結果中的資料，並且排除了結果中的重複資料。INTERSECT 運算的示意圖如下：

![interscet](https://img-blog.csdnimg.cn/20200301150309620.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =272x)

EXCEPT 或者 MINUS 操作符用於返回兩個集合的差集，即出現在第一個查詢結果中，但不在第二個查詢結果中的記錄，並且排除了結果中的重複資料。EXCEPT 運算子的示意圖如下：

![except](https://img-blog.csdnimg.cn/20200301150941502.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =272x)

除此之外，DISTINCT 運算子用於消除重複資料，也就是排除集合中的重複元素。

> ?SQL 中的關係概念來自數學中的集合理論，因此 UNION、INTERSECT 和 EXCEPT 分別來自集合論中的並集（$\cup$）、交集（$\cap$）和差集（$\setminus$）運算。需要注意的是，集合理論中的集合不允許存在重複的資料，但是 SQL 允許。因此，SQL 中的集合也被稱為多重集合（multiset）；多重集合與集合理論中的集合都是無序的，但是 SQL 可以通過 ORDER BY 子句對查詢結果進行排序。

#### JOIN
在 SQL 中，不僅實體物件儲存在關係表中，物件之間的聯絡也儲存在關係表中。因此，當我們想要獲取這些相關的資料時，需要使用到另一個操作：連線查詢（JOIN）。

常見的 SQL連線查型別包括**內連線**、**外連線**、**交叉連線**等。其中，外連線又可以分為**左外連線**、**右外連線**以及**全外連線**。

內連線（Inner Join）返回兩個表中滿足連線條件的資料，內連線的原理如下圖所示：

![inner](https://img-blog.csdnimg.cn/20200301180309875.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =929x)

左外連線（Left Outer Join）返回左表中所有的資料；對於右表，返回滿足連線條件的資料；如果沒有就返回空值。左外連線的原理如下圖所示：

![left](https://img-blog.csdnimg.cn/20200301180510934.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =929x)

右外連線（Right Outer Join）返回右表中所有的資料；對於左表，返回滿足連線條件的資料，如果沒有就返回空值。右外連線與左外連線可以互換，以下兩者等價：

```sql
t1 RIGHT JOIN t2
t2 LEFT JOIN t1
```

全外連線（Full Outer Join）等價於左外連線加上右外連線，同時返回左表和右表中所有的資料；對於兩個表中不滿足連線條件的資料返回空值。全外連線的原理如下圖所示：

![full](https://img-blog.csdnimg.cn/20200301180753764.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =929x)

交叉連線也稱為笛卡爾積（Cartesian Product）。兩個表的交叉連線相當於一個表的所有行和另一個表的所有行兩兩組合，結果的數量為兩個表的行數相乘。交叉連線的原理如下圖所示：

![cross](https://img-blog.csdnimg.cn/20200301181241861.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvcnNlcw==,size_16,color_FFFFFF,t_70#pic_center =929x)
> ?其他型別的連線還有[半連線](https://tonydong.blog.csdn.net/article/details/108152329)（SEMI JOIN）、[反連線](https://tonydong.blog.csdn.net/article/details/108152329)（ANTI JOIN）。

集合操作將兩個集合合併成一個更大或更小的集合；連線查詢將兩個集合轉換成一個更大或更小的集合，同時獲得了一個更大的元素（更多的列）。很多時候集合操作都可以通過連線查詢來實現，例如：

```sql
SELECT department_id
  FROM departments
 UNION
SELECT department_id
  FROM employees;
```

等價於：

```sql
SELECT COALESCE(d.department_id, e.department_id)
  FROM departments d
  FULL JOIN employees e ON (e.department_id = d.department_id);
```

我們已經介紹了許多查詢的示例，接下來看看其他的資料操作。

#### DML

DML 表示資料操作語言，也就是插入、更新和刪除。以下是一個插入語句示例：

```sql
CREATE TABLE test(id int);

-- MySQL、SQL Server 等
INSERT INTO test(id) VALUES (1),(2),(3);

-- Oracle
INSERT INTO test(id)
(SELECT 1 AS id FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL);
```

我們通過一個 INSERT 語句插入了 3 條記錄，或者說是插入了一個包含 3 條記錄的關係表。因為，UNION ALL 返回的是一個關係表。VALUES 同樣是指定了一個關係表，在 SQL Server 和 PostgreSQL 中支援以下語句：

```sql
SELECT *
FROM (
  VALUES(1),(2),(3)
) test(id);
```

前面我們已經說過，FROM 之後是一個關係表，所以這裡的 VALUES 也是一樣。由於我們經常插入單條記錄，並沒有意識到實際上是以表為單位進行操作。

同樣，UPDATE 和 DELETE 語句也都是以關係表為單位的操作；只不過我們習慣了說更新一行資料或者刪除幾條記錄。

如果覺得文章對你有用，請不要白嫖！歡迎關注❤️、點贊?、轉發?！

SQL 程式設計思想：一切皆關係

相關文章