圖解 SQL 基礎知識

圖解SQL基礎知識在計算機領域有許多偉大的設計理念和思想，例如：在Unix中，一切皆文件。在面向對象的編程語言中，一切皆對象。關系數(shù)據(jù)庫同樣也有自己的設計思想：在SQL中，一切皆關系。

關系模型

關系模型（Relationalmodel）由E.F.Codd博士于1970年提出，以集合論中的關系概念為基礎；無論是現(xiàn)實世界中的實體對象還是它們之間的聯(lián)系都使用關系表示。我們在數(shù)據(jù)庫系統(tǒng)中看到的關系就是二維表（Table），由行（Row）和列（Column）組成。因此，也可以說關系表是由數(shù)據(jù)行構成的集合。關系模型由數(shù)據(jù)結構、關系操作、完整性約束三部分組成。關系模型中的數(shù)據(jù)結構就是關系表，包括基礎表、派生表（查詢結果）和虛擬表（視圖）。常用的關系操作包括增加、刪除、修改和查詢（CRUD），使用的就是SQL語言。其中查詢操作最為復雜，包括選擇（Selection）、投影（Projection）、并集（Union）、交集（Intersection）、差集（Exception）以及笛卡兒積（Cartesianproduct）等。完整性約束用于維護數(shù)據(jù)的完整性或者滿足業(yè)務約束的需求，包括實體完整性（主鍵約束）、參照完整性（外鍵約束）以及用戶定義的完整性（非空約束、唯一約束、檢查約束和默認值）。我們今天的主題是關系操作語言，也就是SQL。本文使用的示例數(shù)據(jù)來源于/horses/article/details/86518676。

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

接下來我們具體分析一下關系的各種操作語句；目的是為了讓大家能夠了解

SQL是一種面向集合的編程語言，它的操作對象是集合，操作的結果也是集合。??在關系數(shù)據(jù)庫中，關系、表、集合三者通常表示相同的概念。SELECT下面是一個簡單的查詢語句：SELECT

employee_id,

first_name,

last_name,

hire_date

FROM

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

*

FROM

(SELECT

employee_id,first_name,last_name,hire_date

FROM

employees)t;括號內(nèi)的查詢語句被稱為派生表，我們給它指定了一個別名叫做t。同樣，整個查詢結果也是一個表；這就意味著我們可以繼續(xù)嵌套，雖然這么做很無聊。我們再看一個PostgreSQL中的示例：--PostgreSQL

SELECT

*

FROM

upper('sql');

|upper|

|-------|

|SQL|upper()是一個大寫轉換的函數(shù)。它出現(xiàn)再FROM子句中，意味著它的結果也是一個表，只不過是1行1列的特殊表。SELECT子句用于指定需要查詢的字段，可以包含表達式、函數(shù)值等。SELECT在關系操作中被稱為投影（Projection），看下面的示意圖應該就比較好理解了。

除了SELECT之外，還有一些常用的SQL子句。WHERE用于指定數(shù)據(jù)過濾的條件，在關系運算中被稱為選擇（Selection），示意圖如下：

ORDERBY用于對查詢的結果進行排序，示意圖如下：

總之，SQL可以完成各種數(shù)據(jù)操作，例如過濾、分組、排序、限定數(shù)量等；所有這些操作的對象都是關系表，結果也是關系表。

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

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

department_id,

count(*),first_name

FROM

employees

GROUP

BY

department_id;該語句的目的是按照部門統(tǒng)計員工的數(shù)量，但是存在一個語法錯誤，就是first_name不能出現(xiàn)在查詢列表中。原因在于按照部門進行分組的話，每個部門包含多個員工；無法確定需要顯示哪個員工的姓名，這是一個邏輯上的錯誤。所以說，GROUPBY改變了集合元素（數(shù)據(jù)行）的結構，創(chuàng)建了一個全新的關系。分組操作的示意圖如下：

盡管如此，GROUPBY的結果仍然是一個集合。

UNIONSQL面向集合特性最明顯的體現(xiàn)就是UNION（并集運算）、INTERSECT（交集運算）和EXCEPT/MINUS（差集運算）。

這些集合運算符的作用都是將兩個集合并成一個集合，因此需要滿足以下條件：兩邊的集合中字段的數(shù)量和順序必須相同；兩邊的集合中對應字段的類型必須匹配或兼容。具體來說，UNION和UNIONALL用于計算兩個集合的并集，返回出現(xiàn)在第一個查詢結果或者第二個查詢結果中的數(shù)據(jù)。它們的區(qū)別在于UNION排除了結果中的重復數(shù)據(jù)，UNIONALL保留了重復數(shù)據(jù)。下面是UNION操作的示意圖：

INTERSECT操作符用于返回兩個集合中的共同部分，即同時出現(xiàn)在第一個查詢結果和第二個查詢結果中的數(shù)據(jù)，并且排除了結果中的重復數(shù)據(jù)。INTERSECT運算的示意圖如下：

EXCEPT或者MINUS操作符用于返回兩個集合的差集，即出現(xiàn)在第一個查詢結果中，但不在第二個查詢結果中的記錄，并且排除了結果中的重復數(shù)據(jù)。EXCEPT運算符的示意圖如下：

除此之外，DISTINCT運算符用于消除重復數(shù)據(jù)，也就是排除集合中的重復元素。??SQL中的關系概念來自數(shù)學中的集合理論，因此UNION、INTERSECT和EXCEPT分別來自集合論中的并集（∪\cup∪）、交集（∩\cap∩）和差集（?\setminus?）運算。需要注意的是，集合理論中的集合不允許存在重復的數(shù)據(jù)，但是SQL允許。因此，SQL中的集合也被稱為多重集合（multiset）；多重集合與集合理論中的集合都是無序的，但是SQL可以通過ORDERBY子句對查詢結果進行排序。

JOIN在SQL中，不僅實體對象存儲在關系表中，對象之間的聯(lián)系也存儲在關系表中。因此，當我們想要獲取這些相關的數(shù)據(jù)時，需要使用到另一個操作：連接查詢（JOIN）。常見的SQL連接查類型包括內(nèi)連接、外連接、交叉連接等。其中，外連接又可以分為左外連接、右外連接以及全外連接。內(nèi)連接（InnerJoin）返回兩個表中滿足連接條件的數(shù)據(jù)，內(nèi)連接的原理如下圖所示：

左外連接（LeftOuterJoin）返回左表中所有的數(shù)據(jù)；對于右表，返回滿足連接條件的數(shù)據(jù)；如果沒有就返回空值。左外連接的原理如下圖所示：

右外連接（RightOuterJoin）返回右表中所有的數(shù)據(jù)；對于左表，返回滿足連接條件的數(shù)據(jù)，如果沒有就返回空值。右外連接與左外連接可以互換，以下兩者等價：t1

RIGHT

JOINt2

t2

LEFT

JOINt1全外連接（FullOuterJoin）等價于左外連接加上右外連接，同時返回左表和右表中所有的數(shù)據(jù)；對于兩個表中不滿足連接條件的數(shù)據(jù)返回空值。全外連接的原理如下圖所示：

交叉連接也稱為笛卡爾積（CartesianProduct）。兩個表的交叉連接相當于一個表的所有行和另一個表的所有行兩兩組合，結果的數(shù)量為兩個表的行數(shù)相乘。交叉連接的原理如下圖所示：??其他類型的連接還有半連接（SEMIJOIN）、反連接（ANTIJOIN）。

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

department_id

FROM

departments

UNION

SELECT

department_id

FROM

employees;等價于：SELECT

COALESCE(d.department_id,e.department_id)

FROM

departmentsd

FULL

JOIN

employeese

ON

(e.department_id=d.department_id);我們已經(jīng)介紹了許多查詢的示例，接下來看看其他的數(shù)據(jù)操作。

DMLDML表示數(shù)據(jù)操作語言，也就是插入、更新和刪除。以下是一個插入語句示例：CREATE

TABLE

test(id

int);

--MySQL、SQLServer等

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條記錄的關系表。因為，UNIONALL返回的是一個關系表。VALUES同樣是指定了一個關系表，在SQLSe