轉：如何學習SQL（第三部分：SQL資料類型與三值邏輯）

標籤：blog   http   os   使用   ar   strong   資料   sp   問題   

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

7. 資料類型

在資料庫理論中，關聯式模式和資料類型這兩部分內容是正交的（參看《程式員修鍊之道》第 8節關於“正交性”的討論），互不依賴。換言之，關聯式模式並不關心每個表的欄位的資料類型是什麼，是整數、字串等基本類型也好，是組合類別型、類等自訂 類型也好，關聯式模式只要求每個欄位是原子的。

在資料庫理論中，資料類型又被稱為域，但域是更為嚴格的定義。比如一個班級的學生個數和學生 平均分可能都是int類型，但這是兩個不同的域，“學生個數 * 學生平均分 = 學生總分”，但“學生個數 + 學生平均分”是沒有意義的。目前的主流DBMS似乎尚未對域有很好的支援，但未來的情況可能會有所改變，而且，設計自訂類型也需要對這一問題有充分的認 識。詳見《深度探索關聯式資料庫》第2章。

對於資料庫和SQL的應用來說，除掌握關聯式模式的原理，還需要對DBMS支援的資料類型及其轉換規則有所認識。

1. 基礎資料型別 (Elementary Data Type)

一個DBMS通常都會支援以下幾類基礎資料型別 (Elementary Data Type)（以SQL Server為例）：
- 精確數字：整數(bigint/int/smallint/tinyint/bit)，定點小數(decimal)，貨幣(money/smallmoney)
- 近似數字：浮點數(float)
- 日期和時間：datetime/smalldatetime; date, time, datetime2, datetimeoffset（後4種為SQL Server 2008的新增類型）
- 字串和Unicode字串：varchar/nvarchar, char/nchar（text/ntext已不再建議使用，用varchar(max)/nvarchar(max)代替）
- 二進位串（即位元組流）：varbinary, binary（image已不再建議使用，用varbinary(max)代替）
- 其他資料類型：具有特殊功能的類型(sql_variant, timestamp, uniqueidentifier, xml)，不能用於表的特殊類型(cursor, table)

2. 關於資料類型需要注意的問題

a. 兩類特殊的資料類型
- 日期和時間類型的資料存放區方式和可用值範圍、相關的計算、比較、顯示（轉換為指定格式的字串）都比較複雜，還涉及一組日期時間函數。參看datetime類型分析一帖。
- 字串類型涉及到字元編碼和定序，比較操作還包含LIKE匹配（未來還可能會支援Regex匹配），非常需要注意。參看理解字元編碼和SQLServer中文處理二帖。

b. 如果對不同定序的兩個字串進行計算或比較，將會根據定序優先順序來決定計算結果的定序或比較的方法。

c. 如果對不同類型的兩個值進行計算或比較，將會根據資料類型優先順序進行隱式轉換。資料類型優先順序基本規則如下：
- 大 > 小（>指優先順序高於，下同）：如bigint > int > smallint > tinyint > bit，varchar(20) > varchar(10)，datetime > smalldatetime，等等。
- 可變 > 固定：如float > decimal，varchar > char，nvarchar > nchar，varbinary > binary。
- 各類型大類的優先順序：datetime > float > decimal > integer > unicode string > ansi string > binary。
- 特殊資料類型的優先順序和轉換規則需要特殊考慮，詳見聯機叢書。

d. 如果對不同大小的兩個值進行計算，將會根據精度、小數位元和長度的規則來產生新的類型大小。

e. 常量的資料類型（可以通過SELECT col = 常量值 INTO testdt然後查看testdt表col欄位的資料類型來觀察）
- 如果不顯式指定和隱式轉換，NULL會按int類型處理。
- ‘‘, N‘‘, 1, 0x01, 1.0, 1E0, $1分別對應varchar, nvarchar, int, varbinary, decimal, float, money類型，並且長度是儲存相應值所需要的最小長度。

f. 在軟體開發領域眾所周知：“隱式轉換是bug的源泉”。因此，有兩個建議：
- 使用常量時，最好使用對應類型的常量。比如，如果table.col是varchar類型，那麼WHERE table.col = 10的查詢將不能使用索引，而且當遇到col中存放有不能轉換為數位值時將出錯。
- 除非相應值的隱式轉換非常直觀，否則寧可用CAST()/CONVERT()指定明確的顯式轉換。

以上內容中，加底線的粗體是聯機叢書的標題。詳細分析參看《Microsoft SQL Server 2005技術內幕：T-SQL程式設計》第1章。


8. NULL與三值邏輯

三值邏輯(3VL, Three-valued Logic)絕對是SQL修鍊中的一個緊要關卡，值得特別注意，閉關靜修。待衝破這一關卡之後，SQL中的NULL與NOT NULL將別無二致。

關於SQL是否應該允許NULL，在資料庫領域已經近乎一個信仰式的爭論。E.F.Codd認為NULL有存在的必要，但他的好友C.J.Date認為NULL完全可以取消。最終結果是，SQL標準支援NULL。

理論上的爭論且不管。但在實踐中，一定要知道NULL的三值邏輯會帶來很多困擾的問題。
a. 不使用NULL的理由：
- NULL會引入複雜的三值邏輯。
- NULL在查詢條件、外鍵和CHECK約束、唯一約束、GROUP BY、ORDER BY中的行為都是不一致的。
b. 使用NULL的理由：
- 當需要表示一個未知的、不確定的值時，用NULL更自然。比如一個現在職員工的離職時間、頂級員工（BOSS）的上級員工，等等。
- 外聯結通常會引入NULL，即使所有表的欄位都定義為NOT NULL。

首先，如果可能，盡量讓所有欄位都聲明為NOT NULL。除非是更適合使用NULL的場合（從業務出發）。

其次，在使用NULL時，一定要搞清楚三值邏輯和資料庫引擎對NULL的處理：
（SQLServer有一個選項SET ANSI_DEFAULTS，預設為ON，即與SQL標準一致。設為OFF的效果詳見聯機叢書。）

1. NULL與別的值進行+-*/等計算操作（包括在大多數函數中使用NULL）後，結果是NULL（標量運算式）。NULL與別的值進行=、>、<等比較操作後，結果是Unknown（斷言）。
Unknown相關的邏輯運算：
[code=sql] 
NOT Unknown --> Unknown 
Unknown AND/OR Unknown --> Unknown 
Unknown OR TRUE --> TRUE 
Unknown AND TRUE --> Unknown 
Unknown OR FALSE --> Unknown 
Unknown AND FALSE --> FALSE 
[/code]
具體可查三值邏輯的真值表。

2. 在where/on/having和if/case when中，只有True才使條件成立（即Unknown當作False來處理）。比如：
where column = value：表中column為NULL的行永遠不會返回，即使value是NULL；
case value when NULL then XXX when ... end：XXX永遠不會執行，即使value是NULL；
if <Unknown> XXX else YYY end或case when <Unknown> then XXX else YYY end：這兩種情況下，YYY會執行。

3. 包含外鍵約束和Check約束的欄位允許NULL（即約束只當條件為False時出錯，Unknown是不管的）。
4. 包含唯一約束（unique index）的欄位只允許一個NULL的行，再插入或更新該欄位為NULL的行會報欄位重複的錯誤。
5. GROUP BY時，所有NULL被視為一組。
6. ORDER BY時，所有NULL排在一起，但NULL排在非空值的前面（如SQL Server）還是後面（如Oracle），SQL標準未規定。
7. 聚集合函式（SUM/AVG/MAX/MIN/COUNT）忽略NULL的行。
8. declare的變數，在未賦值之前為NULL。
9. 與NULL處理相關的運算子和函數：
- IS NULL/IS NOT NULL：用這兩個運算子來判斷一個值是否為NULL，而不是=或<>。
- ISNULL/COALESCE：取第一個非空值（注意兩個函數的資料類型轉換規則不同）。
- NULLIF(a,b)：等價於CASE WHEN a = b THEN NULL ELSE a END。

Trackback : http://topic.csdn.net/u/20100826/18/7b81012a-b5c4-48b1-b5d1-40a92f3e0388.html

轉：如何學習SQL（第三部分：SQL資料類型與三值邏輯）