轉：如何學習SQL（第二部分：從關係角度理解SQL）

標籤：blog   http   io   os   使用   ar   strong   for   資料   

轉自：http://blog.163.com/[email protected]/blog/static/285720652010950825538/

6. 從關係角度理解SQL

6.1. 關係和表

眾所周知，我們目前所用的資料庫，通常都是關聯式資料庫。關係自然在其中處於關鍵位置。初學資料庫原理的人可能會很困惑關係和表是什麼聯絡，如果沒有清楚的理解，很可能會認為關係這個概念沒有實際意義，只會引起混淆。
其實這兩組概念只是由於理論界與技術界的著重點不同。前者需要用一個專業的、沒有歧義的概念來進行理論探討，後者則希望在實際應用中能夠使用一個直觀的、容易理解的詞彙。通常情況下，可以認為關係和表是一回事。

就定義來說：關係是元組（即表的記錄，或行）的集合。此外，關係還有以下特徵：
- 關係含有一組屬性（即表的欄位，或列），含有N個屬性的關係可稱為N元關係。
- 一個關係的元組含有與關係相同的屬性，N元關係的元組都是N元組，一個元組中對應每個屬性有一個值。
- 一個屬性的域（即欄位的資料類型，但域的要求更嚴格，詳見下文“資料類型”），即該屬性所有可能的值的集合。

從 這裡可以看出關係和表的區別：關係作為一種集合，不會包含重複元組；而表則可以包含重複記錄。這是SQL面對的諸多指責之一，但有其技術合理性。這裡的區 別在理解上影響不大，不妨把表理解為“可能（但通常不應該）重複的集合”。注意到這點區別，以下我們便可以對關係和表不加區別的使用了。

另外，這裡的關係和表，指的是所有表值的東西，包含物理表、虛擬表（視圖）、派生表（一個用在FROM子句的子查詢）、表變數、資料表值函式、等等。它們在物理上有區別，但在邏輯上是等價的。

6.2. 關聯式模式

數 據庫建模（即表結構設計）的過程，是根據現實世界的業務需求，設計一個表示和儲存業務資料的關係資料模型。在設計過程中可以藉助E-R模型來簡化問題，因 為E-R模型可以更直觀地對應於現實世界，也可以很容易地轉化為關聯式模式。對於熟練的設計者，可以省略E-R模型，直接構建關聯式模式。

而關聯式模式在關聯式資料庫中基本上可以直接表示，所以關聯式模式與物理模型差別不大。物理模型通常只是根據需要添加必要的索引，或是將概念上的表在物理上映射為分區視圖或分區表。

以上幾個模型的關係見：


簡單總結一下關聯式模式設計中的兩個要點：

1. 完整性條件約束(Integrity constraint)：

完整性條件約束保證資料的一致性（符合基本條件），包含以下3種類型：
- 實體完整性（主鍵約束）：一個表的主鍵不可為空。
- 參照完整性（外鍵約束）：一個表的外鍵必須存在於所參照的表中。
- 自訂完整性（CHECK約束，UNIQUE約束）：即表中的資料不能違反約束定義的條件（不能使CHECK的運算式為False，不能使UNIQUE約束的欄位或欄位組合出現重複值）。

完整性條件約束定義了系統概念性模型的邊界，很大程度上防止了髒資料進入系統，這是非常重要的，因為髒資料往往比沒有資料還要討厭（這與“錯誤的觀點勝過沒有觀點”恰恰相反）。

在 設計表結構時，外鍵、CHECK、UNIQUE約束或許可以適當省略（出於運行效能和開發效率的考慮，並且相信表資料只有統一預存程序修改，不會出現髒數 據），但主鍵通常是一定需要的。主鍵不僅意味著可以高效查詢（因為目前DBMS的主鍵通常都是通過B+樹叢集索引實現的），更重要的是清楚地說明了表中數 據的唯一標識是什麼。（目前我只發現一種不需要主鍵的情況：日誌表——同一時刻可能有多筆記錄，所以datetime不能作為主鍵；而一個遞增的 LogID也沒有太大實際意義，參看關於叢集索引選擇方案的疑問一帖。）

關於主鍵的選擇方案，詳見一個基礎問題一帖。

給 我看錶的資料樣本，以及（可能）過時的資料字典和程式文檔，我仍然迷惑不解。如果給我看完整的表的定義（要包括各種完整性條件約束，特別是主鍵），通常就不需 要查看錶中的資料樣本了，甚至連文檔也可以省去。（這兩句話借鑒了Brooks在《人月神話》一書中的話。參見《UNIX編程藝術》1.6節腳註。）

2. 範式(Normal Form, NF)：

範式是一組關係（表）設計的原則，通過避免冗餘防止出現資料的更新異常（即DRY原則的體現）。在實踐上常用的是以下3個層次的範式：
- 1NF：表中的欄位都是原子的。
- 2NF：表中的所有欄位都可以由主鍵唯一決定（函數依賴）。
- 3NF：除完整主鍵以外，其它欄位（包括部分主鍵）之間不存在決定關係（函數依賴）。

首 先說明一下1NF的“原子的”。這個“原子的”是指業務需求不需要對這個值進行拆分（沒有前提條件，“拆分”一詞是有多種解釋的，如字串可以拆分成字 符，整數可以拆分成二進位的位串或素因子的乘積）。例如，城區、街道、門牌號是地址的三部分，如果地址只是作為一個記錄，不需要更細粒度的處理，則可以將 三部分存在一個欄位；如果需要根據城區進行查詢和分組統計，則至少需要把城區作為一個單獨的欄位。所以，一個欄位是不是“原子的”必鬚根據業務需求這個條 件來定義。實踐中業務需求是會變化的，因而系統設計還需要一定的前瞻性。目前一個原子的欄位可能隨著需求變化而不再是原子的。

範式給出了一組表應該怎樣設計的原則，但沒有說明如何把表設計成這樣。資料庫理論上的關係範式分解過於抽象，以下是一點實用性的思路：
- 1NF：讓表中的每個欄位都不需要拆分處理（至少不需要太複雜的拆分處理）。如姓名的結構很簡單，通常不需要設計成姓和名兩個欄位，但如果是一個國際化的系統，不同文化中姓名的結構可以不同，這時則最好把LastName和FirstName分開存放，比如Facebook、Twitter等網站的設計。
- 2NF：給表定義主鍵。參看上文關於實體完整性的討論。
- 3NF：不要在同一個表中存放相關資料或派生資料，只存放主要資料，其它資料通過聯結查詢或計算獲得。如不要在員工表中同時存放部門ID和部門名稱（相關資料）或同時存放出生年月和年齡（派生資料），其中部門ID或出生年月是主要資料，部門名稱可以通過聯結查詢獲得，年齡可以通過計算獲得。

有 些情況下出於結構的直觀或查詢效能的考慮，可能會需要反範式的設計。如在一個字串欄位中存放逗號分隔的多個值（形如‘1,2,3,5,8‘，違反 1NF），或是在一個表中同時存放相關資料或派生資料來避免聯結或計算開銷（比如同時存放部門ID和部門資訊來避免聯結部門表，或同時存放員工各項薪酬福 利和總薪資來避免複雜的薪資計算，違反3NF）。反範式的設計會帶來複雜的查詢處理或冗餘，更好的方案是基本資料用符合範式的表格儲存體，通過統一的過程來計 算和重新整理緩衝表來提高查詢時的效能，參看《程式員修鍊之道》第7節關於DRY原則的討論。

6.3. 關係運算

表的查詢，與關係代數(Relational Algebra)定義的關係運算是等價的。理解關係運算，或許可以簡化對查詢的認識。

常用的基本關係運算只有4類（夠簡單吧）：

1. 基本的集合運算（雙目運算）

關係是元組的集合，所以關係也支援基本的集合運算：
- 並(union)：對應SQL關鍵字UNION
- 交(intersection)：對應SQL關鍵字INTERSECT
- 差(difference)：對應SQL關鍵字EXCEPT

不同的是，關係的集合運算，要求參與運算的兩個關係必須含有相同屬性集（屬性的個數和類型都一樣）。
由於表允許重複記錄，在SQL中以上三種操作還可以是UNION ALL/INTERSECT ALL/EXCEPT ALL的形式，結果不去除重複記錄。

2. 提取關係的一部分的運算（單目運算）

- 選擇(selection)：根據條件過濾出指定的元組（行），對應SQL查詢的WHERE子句
- 投影(projection)：根據列表過濾出指定的屬性（列），對應SQL查詢的SELECT子句

由於表允許重複記錄，關係的投影運算事實上等價於SELECT DISTINCT的效果。而SELECT的預設效果是不去除重複記錄。

3. 兩個關係的聯結（雙目運算）

- 笛卡爾積(Cartesian product)：對應SQL關鍵字CROSS JOIN（與FROM後的多個表直接用逗號分隔效果相同）
- 內聯結(Inner Join)：對應SQL關鍵字INNER JOIN
- 外聯結(Outer Join)：對應SQL關鍵字{LEFT | RIGHT | FULL} OUTER JOIN

4. 彙總運算（單目運算）

根據指定屬性（列）分組，同時可以使用彙總函式。對應SQL查詢的GROUP BY子句。

以上4類別關係運算，不管是單目運算還是雙目運算，其結果依然是一個關係，因而可以繼續進行運算。

通常情況下的SQL查詢，除一些特殊的語言特性外（如TOP、排序函數等），主要的查詢邏輯都是這4類別關係運算的組合。

6.4. 資料查詢

1. 查詢的邏輯處理過程

以T-SQL為例，一個查詢（完整SELECT語句）的邏輯處理過程如下（其中括弧中的數字表示處理順序）：
[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]
說明：
+ 有些子句是可選的。比如JOIN可能出現0到多次，GROUP BY和HAVING可能出現0到1次。
+ 從以上順序可以看出，為何在WHERE子句不能使用SELECT的計算結果，但在ORDER BY子句卻可以。
+ 查詢的邏輯處理過程與物理處理過程可能並不相同。但對於SQL的學習來說，先理解邏輯處理過程是必須的。先要知道怎樣計算出正確的結果，才談得上怎樣更高效地計算出正確的結果。

該內容詳見《Microsoft SQL Server 2005技術內幕：T-SQL查詢》第1章。

2. 查詢條件

在SQL Server聯機叢書中，查詢條件的BNF文法圖如下：
[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為子查詢。查詢語句返回使查詢條件為True的結果。

3. 子查詢

一個查詢如果作為一個語句的一部分，則稱為子查詢。

a. 按結果分類：
- scalar subquery：如果查詢結果是標量值，即只有一行一列，則為標量子查詢（標量運算式）。
- table-valued subquery：反之則是表值子查詢（表值運算式）。

b. 按查詢是否涉及外層表分類：
- self-contained subquery：不涉及外層表的子查詢是不相互關聯的子查詢，如SELECT a.* FROM a WHERE a.id IN (SELECT b.id FROM b)。
- correlated subquery：反之則是相互關聯的子查詢，如SELECT a.* FROM a WHERE EXISTS (SELECT * FROM b WHERE b.id = a.id)。

c. 按子查詢所在的位置分類：
- In search_condition：在查詢條件中的子查詢，比如上文文法圖中的所有subquery。
- In FROM clause：在FROM子句中的子查詢，又稱派生表，如SELECT * FROM (SELECT * FROM a) tmp，派生表一定要指定表別名。（SQL Server 2005之後支援Common Table Expressions，可視為派生表的變形，但可以在一個查詢中多次使用，而且支援Recursive CTE這種進階功能，詳見聯機叢書。）
- In SELECT clause：在SELECT子句中的子查詢，如SELECT d.DepID, ManagerName = (SELECT e.EmpName FROM Employee e WHERE e.EmpID = d.ManagerID) FROM Department d WHERE ...，這種子查詢效能較差，通常可以用JOIN代替。如果可能，盡量避免使用SELECT子句中的子查詢。

6.5. 資料修改

在SQL Server中，修改資料（增、刪、改）的語句支援以下格式：

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個語句：
第1個是SQL標準的插入語句（SQL Server 2008還支援在VALUES子句指定多個元組）；
第2個和第3個是T-SQL擴充的插入語句，但要求SELECT語句和EXEC預存程序的結果集與目標表的指定插入欄欄位個數一致且資料類型一一對應（或可以隱式轉換）；
第4個不是插入語句，而是根據SELECT語句的結果集建立一個表並將結果資料插入其中，注意與第2個語句的區別。

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個語句：
第1個是SQL標準的刪除語句（WHERE子句省略的結果是刪除全部資料，注意！）；
第2個是T-SQL擴充的刪除語句，效果是將符合聯結查詢條件的目標表資料刪除（將DELETE FROM <table>改為SELECT DISTINCT <table>.*可以看到刪除哪些資料）；
第3個實際上是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個語句：
第1個是SQL標準的更新語句（WHERE子句省略的結果是更新全部資料，注意！）；
第 2個是T-SQL擴充的更新語句，效果是將符合聯結查詢條件的目標表資料更新為指定結果（將UPDATE <table> SET <col> = <new_value>改為SELECT <table>.<col>, <new_value>可以看到把哪些資料更新為哪些新值，其中<new_value>可以是聯結查詢的計算值，但如果聯結查詢 結果使得目標表的<col>和新值<new_value>成為一對多的關係，則<col>會更新為哪 個<new_value>是不確定的，這種情況可能導致意想不到的bug）。

該內容詳見《Microsoft SQL Server 2005技術內幕：T-SQL查詢》第8章。

6.6. 表的邏輯含義

很多使用資料庫的人，不瞭解資料庫原理，不能從邏輯上理解表的含義，從而只能把表看作一種資料結構，看作一種類似二維數組的東西，於是寫出低效的迴圈語句就不難理解了，資料一致性更是難以保證。

可以從以下兩個層面來理解表：

1. 一個表是一類事物（物件object和事實fact的統稱）的集合，其中表的每行記錄表示一個該類事物，主鍵是一個事物的唯一標識。
如：學生表（#學號，姓名，專業，……）是學生（物件）的集合，一個學號可以唯一標識一個學生；學生選課表（#學號，#課程ID，選課時間）是學生選課（事實）的集合，聯合主鍵學號和課程ID可以唯一標識某個學生選了某門課的事實。

資料庫建模就是根據業務需求設計一組表，用來表示業務系統中的所有事物。

2. 一個表的表結構定義了一個謂詞，表中的每行記錄都是對該謂詞的一個真值量化。由於量化後的謂詞是一個命題，所以，一個表是一組真命題的集合。
（關於謂詞和量化的概念，可參看《離散數學及其應用(第5版)》一書中關於資料邏輯的部分。）
如： 學生選課表（#學號，#課程ID，選課時間）定義了這樣一個謂詞——“學生{#學號}，在{選課時間}，選了課程{#課程ID}。”，其中{}中的部分是 一個變數。謂詞不是命題，只有對其量化（或稱執行個體化）之後才是命題。該表的一行記錄(‘S001‘,‘C0001‘,‘2010-08-24 17:16:58‘)表示一個真值量化，量化後的命題是“學生S001，在2010-08-24 17:16:58，選了課程C0001。”。

一個資料庫系統包含了很多表，每個表是一組真命題的集合，所有這些真命題則表示了系統中可信的知識。
設計一個表，就是設計一個謂詞。只要表結構文檔把這個謂詞的含義說明清楚了，則表中記錄的含義也自然清楚了；反之，如果一個表的謂詞存在模糊或歧義，則表中的記錄也是沒有意義的。

從這個角度理解完整性條件約束：
- 實體完整性（主鍵約束）：如果一個表包含了完全相同的兩條記錄，則把一個真命題重複一遍並不能增加知識；如果一個表的兩條不同記錄有著相同主鍵，則說明這兩個命題是衝突的。實體完整性保證了每個命題的唯一和無衝突。
- 參照完整性（外鍵約束）：參照完整性保證了每個命題涉及的事物都是有意義的（在該事物的表中有定義）。
- 值域完整性（CHECK約束）：值域完整性保證每個命題都是一致的（不違反CHECK約束）。“每個命題都是一致的”是“每個命題都是正確的”的必要非充分條件，所以約束只是一種最小保證。

Trackback : http://topic.csdn.net/u/20100826/18/ba72991f-f961-4c18-8d9b-f234c87a609d.html

轉：如何學習SQL（第二部分：從關係角度理解SQL）