第二部分：從關(guān)系角度理解SQL
6. 從關(guān)系角度理解SQL6.1. 關(guān)系和表眾所周知，我們目前所用的數據庫，通常都是關(guān)系數據庫。關(guān)系自然在其中處于關(guān)鍵位置。初學(xué)數據庫原理的人可能會(huì )很困惑關(guān)系和表是什么聯(lián)系，如果沒(méi)有清楚的理解，很可能會(huì )認為關(guān)系這個(gè)概念沒(méi)有實(shí)際意義，只會(huì )引起混淆。
其實(shí)這兩組概念只是由于理論界與技術(shù)界的著(zhù)重點(diǎn)不同。前者需要用一個(gè)專(zhuān)業(yè)的、沒(méi)有歧義的概念來(lái)進(jìn)行理論探討，后者則希望在實(shí)際應用中能夠使用一個(gè)直觀(guān)的、容易理解的詞匯。通常情況下，可以認為關(guān)系和表是一回事。
就定義來(lái)說(shuō)：
關(guān)系是元組（即表的記錄，或行）的集合。此外，關(guān)系還有以下特征：
- 關(guān)系含有一組屬性（即表的字段，或列），含有N個(gè)屬性的關(guān)系可稱(chēng)為N元關(guān)系。
- 一個(gè)關(guān)系的元組含有與關(guān)系相同的屬性，N元關(guān)系的元組都是N元組，一個(gè)元組中對應每個(gè)屬性有一個(gè)值。
- 一個(gè)屬性的域（即字段的數據類(lèi)型，但域的要求更嚴格，詳見(jiàn)下文“數據類(lèi)型”），即該屬性所有可能的值的集合。
從這里可以看出關(guān)系和表的區別：關(guān)系作為一種集合，不會(huì )包含重復元組；而表則可以包含重復記錄。這是SQL面對的諸多指責之一，但有其技術(shù)合理性。這里的區別在理解上影響不大，不妨把表理解為“可能（但通常不應該）重復的集合”。注意到這點(diǎn)區別，以下我們便可以對關(guān)系和表不加區別的使用了。
另外，這里的關(guān)系和表，指的是所有表值的東西，包含物理表、虛擬表（視圖）、派生表（一個(gè)用在FROM子句的子查詢(xún)）、表變量、表值函數、等等。它們在物理上有區別，但在邏輯上是等價(jià)的。
6.2. 關(guān)系模型數據庫建模（即表結構設計）的過(guò)程，是根據現實(shí)世界的業(yè)務(wù)需求，設計一個(gè)表示和存儲業(yè)務(wù)數據的關(guān)系數據模型。在設計過(guò)程中可以借助E-R模型來(lái)簡(jiǎn)化問(wèn)題，因為E-R模型可以更直觀(guān)地對應于現實(shí)世界，也可以很容易地轉化為關(guān)系模型。對于熟練的設計者，可以省略E-R模型，直接構建關(guān)系模型。
而關(guān)系模型在關(guān)系數據庫中基本上可以直接表示，所以關(guān)系模型與物理模型差別不大。物理模型通常只是根據需要添加必要的索引，或是將概念上的表在物理上映射為分區視圖或分區表。
以上幾個(gè)模型的關(guān)系見(jiàn)下圖：
簡(jiǎn)單總結一下關(guān)系模型設計中的兩個(gè)要點(diǎn)：
1.
完整性約束(Integrity constraint)：
完整性約束保證數據的一致性（符合基本條件），包含以下3種類(lèi)型：
- 實(shí)體完整性（主鍵約束）：一個(gè)表的主鍵不能為空。
- 參照完整性（外鍵約束）：一個(gè)表的外鍵必須存在于所參照的表中。
- 自定義完整性（CHECK約束，UNIQUE約束）：即表中的數據不能違反約束定義的條件（不能使CHECK的表達式為False，不能使UNIQUE約束的字段或字段組合出現重復值）。
完整性約束定義了系統概念模型的邊界，很大程度上防止了臟數據進(jìn)入系統，這是非常重要的，因為臟數據往往比沒(méi)有數據還要討厭（這與“錯誤的觀(guān)點(diǎn)勝過(guò)沒(méi)有觀(guān)點(diǎn)”恰恰相反）。
在設計表結構時(shí)，外鍵、CHECK、UNIQUE約束或許可以適當省略（出于運行性能和開(kāi)發(fā)效率的考慮，并且相信表數據只有統一存儲過(guò)程修改，不會(huì )出現臟數據），但主鍵通常是一定需要的。主鍵不僅意味著(zhù)可以高效查詢(xún)（因為目前DBMS的主鍵通常都是通過(guò)B+樹(shù)聚集索引實(shí)現的），更重要的是清楚地說(shuō)明了表中數據的唯一標識是什么。（目前我只發(fā)現一種不需要主鍵的情況：日志表——同一時(shí)刻可能有多筆記錄，所以datetime不能作為主鍵；而一個(gè)遞增的LogID也沒(méi)有太大實(shí)際意義，參看
關(guān)于聚集索引選擇方案的疑問(wèn)一帖。）
關(guān)于主鍵的選擇方案，詳見(jiàn)
一個(gè)基礎問(wèn)題一帖。
給我看表的數據樣本，以及（可能）過(guò)時(shí)的數據字典和程序文檔，我仍然迷惑不解。如果給我看完整的表的定義（要包括各種完整性約束，特別是主鍵），通常就不需要查看表中的數據樣本了，甚至連文檔也可以省去。（這兩句話(huà)借鑒了Brooks在《人月神話(huà)》一書(shū)中的話(huà)。參見(jiàn)《UNIX編程藝術(shù)》1.6節腳注。）
2.
范式(Normal Form, NF)：
范式是一組關(guān)系（表）設計的原則，通過(guò)避免冗余防止出現數據的更新異常（即DRY原則的體現）。在實(shí)踐上常用的是以下3個(gè)層次的范式：
- 1NF：表中的字段都是原子的。
- 2NF：表中的所有字段都可以由主鍵唯一決定（函數依賴(lài)）。
- 3NF：除完整主鍵以外，其它字段（包括部分主鍵）之間不存在決定關(guān)系（函數依賴(lài)）。
首先說(shuō)明一下1NF的“原子的”。這個(gè)“原子的”是指業(yè)務(wù)需求不需要對這個(gè)值進(jìn)行拆分（沒(méi)有前提條件，“拆分”一詞是有多種解釋的，如字符串可以拆分成字符，整數可以拆分成二進(jìn)制的位串或素因子的乘積）。例如，城區、街道、門(mén)牌號是地址的三部分，如果地址只是作為一個(gè)記錄，不需要更細粒度的處理，則可以將三部分存在一個(gè)字段；如果需要根據城區進(jìn)行查詢(xún)和分組統計，則至少需要把城區作為一個(gè)單獨的字段。所以，一個(gè)字段是不是“原子的”必須根據業(yè)務(wù)需求這個(gè)條件來(lái)定義。實(shí)踐中業(yè)務(wù)需求是會(huì )變化的，因而系統設計還需要一定的前瞻性。目前一個(gè)原子的字段可能隨著(zhù)需求變化而不再是原子的。
范式給出了一組表應該怎樣設計的原則，但沒(méi)有說(shuō)明如何把表設計成這樣。數據庫理論上的關(guān)系范式分解過(guò)于抽象，以下是一點(diǎn)實(shí)用性的思路：
-
1NF：讓表中的每個(gè)字段都不需要拆分處理（至少不需要太復雜的拆分處理）。如姓名的結構很簡(jiǎn)單，通常不需要設計成姓和名兩個(gè)字段，但如果是一個(gè)國際化的系統，不同文化中姓名的結構可以不同，這時(shí)則最好把LastName和FirstName分開(kāi)存放，比如Facebook、Twitter等網(wǎng)站的設計。
-
2NF：給表定義主鍵。參看上文關(guān)于實(shí)體完整性的討論。
-
3NF：不要在同一個(gè)表中存放相關(guān)數據或派生數據，只存放主要數據，其它數據通過(guò)聯(lián)接查詢(xún)或計算獲得。如不要在員工表中同時(shí)存放部門(mén)ID和部門(mén)名稱(chēng)（相關(guān)數據）或同時(shí)存放出生年月和年齡（派生數據），其中部門(mén)ID或出生年月是主要數據，部門(mén)名稱(chēng)可以通過(guò)聯(lián)接查詢(xún)獲得，年齡可以通過(guò)計算獲得。
有些情況下出于結構的直觀(guān)或查詢(xún)性能的考慮，可能會(huì )需要反范式的設計。如在一個(gè)字符串字段中存放逗號分隔的多個(gè)值（形如'1,2,3,5,8'，違反1NF），或是在一個(gè)表中同時(shí)存放相關(guān)數據或派生數據來(lái)避免聯(lián)接或計算開(kāi)銷(xiāo)（比如同時(shí)存放部門(mén)ID和部門(mén)信息來(lái)避免聯(lián)接部門(mén)表，或同時(shí)存放員工各項薪酬福利和總薪資來(lái)避免復雜的薪資計算，違反3NF）。反范式的設計會(huì )帶來(lái)復雜的查詢(xún)處理或冗余，更好的方案是基本數據用符合范式的表存儲，通過(guò)統一的過(guò)程來(lái)計算和刷新緩沖表來(lái)提高查詢(xún)時(shí)的性能，參看《程序員修煉之道》第7節關(guān)于DRY原則的討論。
6.3. 關(guān)系運算表的查詢(xún)，與關(guān)系代數(Relational Algebra)定義的關(guān)系運算是等價(jià)的。理解關(guān)系運算，或許可以簡(jiǎn)化對查詢(xún)的認識。
常用的基本關(guān)系運算只有4類(lèi)（夠簡(jiǎn)單吧）：
1. 基本的集合運算（雙目運算）
關(guān)系是元組的集合，所以關(guān)系也支持基本的集合運算：
- 并(union)：對應SQL關(guān)鍵字UNION
- 交(intersection)：對應SQL關(guān)鍵字INTERSECT
- 差(difference)：對應SQL關(guān)鍵字EXCEPT
不同的是，關(guān)系的集合運算，要求參與運算的兩個(gè)關(guān)系必須含有相同屬性集（屬性的個(gè)數和類(lèi)型都一樣）。
由于表允許重復記錄，在SQL中以上三種操作還可以是UNION ALL/INTERSECT ALL/EXCEPT ALL的形式，結果不去除重復記錄。
2. 提取關(guān)系的一部分的運算（單目運算）
- 選擇(selection)：根據條件過(guò)濾出指定的元組（行），對應SQL查詢(xún)的WHERE子句
- 投影(projection)：根據列表過(guò)濾出指定的屬性（列），對應SQL查詢(xún)的SELECT子句
由于表允許重復記錄，關(guān)系的投影運算事實(shí)上等價(jià)于SELECT DISTINCT的效果。而SELECT的默認效果是不去除重復記錄。
3. 兩個(gè)關(guān)系的聯(lián)接（雙目運算）
- 笛卡爾積(Cartesian product)：對應SQL關(guān)鍵字CROSS JOIN（與FROM后的多個(gè)表直接用逗號分隔效果相同）
- 內聯(lián)接(Inner Join)：對應SQL關(guān)鍵字INNER JOIN
- 外聯(lián)接(Outer Join)：對應SQL關(guān)鍵字{LEFT | RIGHT | FULL} OUTER JOIN
4. 聚合運算（單目運算）
根據指定屬性（列）分組，同時(shí)可以使用聚合函數。對應SQL查詢(xún)的GROUP BY子句。
以上4類(lèi)關(guān)系運算，不管是單目運算還是雙目運算，其結果依然是一個(gè)關(guān)系，因而可以繼續進(jìn)行運算。
通常情況下的SQL查詢(xún)，除一些特殊的語(yǔ)言特性外（如TOP、排序函數等），主要的查詢(xún)邏輯都是這4類(lèi)關(guān)系運算的組合。
6.4. 數據查詢(xún)1. 查詢(xún)的邏輯處理過(guò)程
以T-SQL為例，一個(gè)查詢(xún)（完整SELECT語(yǔ)句）的邏輯處理過(guò)程如下（其中括號中的數字表示處理順序）：
[code=sql]
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list> (6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
[/code]
說(shuō)明：
+ 有些子句是可選的。比如JOIN可能出現0到多次，GROUP BY和HAVING可能出現0到1次。
+ 從以上順序可以看出，為何在WHERE子句不能使用SELECT的計算結果，但在ORDER BY子句卻可以。
+ 查詢(xún)的邏輯處理過(guò)程與物理處理過(guò)程可能并不相同。但對于SQL的學(xué)習來(lái)說(shuō)，先理解邏輯處理過(guò)程是必須的。先要知道怎樣計算出正確的結果，才談得上怎樣更高效地計算出正確的結果。
該內容詳見(jiàn)《Microsoft SQL Server 2005技術(shù)內幕：T-SQL查詢(xún)》第1章。
2. 查詢(xún)條件
在SQL Server聯(lián)機叢書(shū)中，查詢(xún)條件的BNF語(yǔ)法圖如下：
[code=sql]
Search Condition
< search_condition > ::=
{ [ NOT ] <predicate> | ( <search_condition> ) }
[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]
[ ,...n ]
<predicate> ::=
{ expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery ) }
| CONTAINS ( { column | * } , ' < contains_search_condition >' )
| FREETEXT ( { column | * } , 'freetext_string' )
[/code]
其中：predicate為斷言，expression為標量表達式，subquery為子查詢(xún)。查詢(xún)語(yǔ)句返回使查詢(xún)條件為T(mén)rue的結果。
3. 子查詢(xún)
一個(gè)查詢(xún)如果作為一個(gè)語(yǔ)句的一部分，則稱(chēng)為子查詢(xún)。
a. 按結果分類(lèi)：
- scalar subquery：如果查詢(xún)結果是標量值，即只有一行一列，則為標量子查詢(xún)（標量表達式）。
- table-valued subquery：反之則是表值子查詢(xún)（表值表達式）。
b. 按查詢(xún)是否涉及外層表分類(lèi)：
- self-contained subquery：不涉及外層表的子查詢(xún)是不相關(guān)子查詢(xún)，如SELECT a.* FROM a WHERE a.id IN (SELECT b.id FROM b)。
- correlated subquery：反之則是相關(guān)子查詢(xún)，如SELECT a.* FROM a WHERE EXISTS (SELECT * FROM b WHERE b.id = a.id)。
c. 按子查詢(xún)所在的位置分類(lèi)：
- In search_condition：在查詢(xún)條件中的子查詢(xún)，比如上文語(yǔ)法圖中的所有subquery。
- In FROM clause：在FROM子句中的子查詢(xún)，又稱(chēng)派生表，如SELECT * FROM (SELECT * FROM a) tmp，派生表一定要指定表別名。（SQL Server 2005之后支持Common Table Expressions，可視為派生表的變形，但可以在一個(gè)查詢(xún)中多次使用，而且支持Recursive CTE這種高級功能，詳見(jiàn)聯(lián)機叢書(shū)。）
- In SELECT clause：在SELECT子句中的子查詢(xún)，如SELECT d.DepID, ManagerName = (SELECT e.EmpName FROM Employee e WHERE e.EmpID = d.ManagerID) FROM Department d WHERE ...，這種子查詢(xún)性能較差，通?？梢杂肑OIN代替。如果可能，盡量避免使用SELECT子句中的子查詢(xún)。
6.5. 數據修改在SQL Server中，修改數據（增、刪、改）的語(yǔ)句支持以下格式：
1. 增(INSERT)
[code=sql]
INSERT INTO <table>( <column_list>) VALUES( <values>)
INSERT INTO <table>( <column_list>) SELECT <values> FROM ...
INSERT INTO <table>( <column_list>) EXEC <usp>
SELECT <values> INTO <table> FROM ...
[/code]
以上4個(gè)語(yǔ)句：
第1個(gè)是SQL標準的插入語(yǔ)句（SQL Server 2008還支持在VALUES子句指定多個(gè)元組）；
第2個(gè)和第3個(gè)是T-SQL擴展的插入語(yǔ)句，但要求SELECT語(yǔ)句和EXEC存儲過(guò)程的結果集與目標表的指定插入列字段個(gè)數一致且數據類(lèi)型一一對應（或可以隱式轉換）；
第4個(gè)不是插入語(yǔ)句，而是根據SELECT語(yǔ)句的結果集創(chuàng )建一個(gè)表并將結果數據插入其中，注意與第2個(gè)語(yǔ)句的區別。
2. 刪(DELETE)
[code=sql]
DELETE FROM <table> [WHERE <where_condition>] !!!
DELETE FROM <table> FROM <table> JOIN <another_table> ON <join_condition> WHERE <where_condition>
TRUNCATE TABLE <table>
[/code]
以上3個(gè)語(yǔ)句：
第1個(gè)是SQL標準的刪除語(yǔ)句（WHERE子句省略的結果是刪除全部數據，注意?。?；
第2個(gè)是T-SQL擴展的刪除語(yǔ)句，效果是將符合聯(lián)接查詢(xún)條件的目標表數據刪除（將DELETE FROM <table>改為SELECT DISTINCT <table>.*可以看到刪除哪些數據）；
第3個(gè)實(shí)際上是DDL而不是DML，需要的權限和運行條件都與DELETE不同，但效果卻是清除表中所有數據，而且比DELETE高效。
3. 改(UPDATE)
[code=sql]
UPDATE <table> SET <col> = <new_value> [WHERE <where_condition>] !!!
UPDATE <table> SET <col> = <new_value> FROM <table> JOIN <another_table> ON <join_condition> WHERE <where_condition>
[/code]
以上2個(gè)語(yǔ)句：
第1個(gè)是SQL標準的更新語(yǔ)句（WHERE子句省略的結果是更新全部數據，注意?。?；
第2個(gè)是T-SQL擴展的更新語(yǔ)句，效果是將符合聯(lián)接查詢(xún)條件的目標表數據更新為指定結果（將UPDATE <table> SET <col> = <new_value>改為SELECT <table>.<col>, <new_value>可以看到把哪些數據更新為哪些新值，其中<new_value>可以是聯(lián)接查詢(xún)的計算值，但如果聯(lián)接查詢(xún)結果使得目標表的<col>和新值<new_value>成為一對多的關(guān)系，則<col>會(huì )更新為哪個(gè)<new_value>是不確定的，這種情況可能導致意想不到的bug）。
該內容詳見(jiàn)《Microsoft SQL Server 2005技術(shù)內幕：T-SQL查詢(xún)》第8章。
6.6. 表的邏輯含義很多使用數據庫的人，不了解數據庫原理，不能從邏輯上理解表的含義，從而只能把表看作一種數據結構，看作一種類(lèi)似二維數組的東西，于是寫(xiě)出低效的循環(huán)語(yǔ)句就不難理解了，數據一致性更是難以保證。
可以從以下兩個(gè)層面來(lái)理解表：
1.
一個(gè)表是一類(lèi)事物（物件object和事實(shí)fact的統稱(chēng)）的集合，其中表的每行記錄表示一個(gè)該類(lèi)事物，主鍵是一個(gè)事物的唯一標識。
如：學(xué)生表（#學(xué)號，姓名，專(zhuān)業(yè)，……）是學(xué)生（物件）的集合，一個(gè)學(xué)號可以唯一標識一個(gè)學(xué)生；學(xué)生選課表（#學(xué)號，#課程ID，選課時(shí)間）是學(xué)生選課（事實(shí)）的集合，聯(lián)合主鍵學(xué)號和課程ID可以唯一標識某個(gè)學(xué)生選了某門(mén)課的事實(shí)。
數據庫建模就是根據業(yè)務(wù)需求設計一組表，用來(lái)表示業(yè)務(wù)系統中的所有事物。
2. 一個(gè)表的表結構定義了一個(gè)謂詞，表中的每行記錄都是對該謂詞的一個(gè)真值量化。由于量化后的謂詞是一個(gè)命題，所以，
一個(gè)表是一組真命題的集合。
（關(guān)于謂詞和量化的概念，可參看《離散數學(xué)及其應用(第5版)》一書(shū)中關(guān)于數據邏輯的部分。）
如：學(xué)生選課表（#學(xué)號，#課程ID，選課時(shí)間）定義了這樣一個(gè)謂詞——“學(xué)生{#學(xué)號}，在{選課時(shí)間}，選了課程{#課程ID}。”，其中{}中的部分是一個(gè)變量。謂詞不是命題，只有對其量化（或稱(chēng)實(shí)例化）之后才是命題。該表的一行記錄('S001','C0001','2010-08-24 17:16:58')表示一個(gè)真值量化，量化后的命題是“學(xué)生S001，在2010-08-24 17:16:58，選了課程C0001。”。
一個(gè)數據庫系統包含了很多表，每個(gè)表是一組真命題的集合，所有這些真命題則表示了系統中可信的知識。
設計一個(gè)表，就是設計一個(gè)謂詞。只要表結構文檔把這個(gè)謂詞的含義說(shuō)明清楚了，則表中記錄的含義也自然清楚了；反之，如果一個(gè)表的謂詞存在模糊或歧義，則表中的記錄也是沒(méi)有意義的。
從這個(gè)角度理解完整性約束：
- 實(shí)體完整性（主鍵約束）：如果一個(gè)表包含了完全相同的兩條記錄，則把一個(gè)真命題重復一遍并不能增加知識；如果一個(gè)表的兩條不同記錄有著(zhù)相同主鍵，則說(shuō)明這兩個(gè)命題是沖突的。實(shí)體完整性保證了每個(gè)命題的唯一和無(wú)沖突。
- 參照完整性（外鍵約束）：參照完整性保證了每個(gè)命題涉及的事物都是有意義的（在該事物的表中有定義）。
- 域完整性（CHECK約束）：域完整性保證每個(gè)命題都是一致的（不違反CHECK約束）。“每個(gè)命題都是一致的”是“每個(gè)命題都是正確的”的必要非充分條件，所以約束只是一種最小保證。
