3.2　創建數據表

SQL Server 2016是一個關系數據庫，關系型數據庫中的數據表之間存在一定的關聯關系。關系數據庫提供了3種數據完整性規則：實體完整性規則、參照完整性規則、用戶定義完整性規則。其中實體完整性規則和參照完整性規則是關系模型必須滿足的約束條件。

實體完整性：指每條記錄的主鍵組成部分不能為空值，也就是必須得有一個確定的值?，F實世界中的實體是可區分的，即它們具有某種唯一性標識。映射到關系模型中也就是記錄是可區分的，區分記錄靠的就是主鍵。如果主鍵為空，則記錄不可區分，進而與之相對應的現實世界中的實體也是不可區分的，與現實矛盾。

1．約束方法：唯一約束、主鍵約束、標識列

參照完整性：一個表的外鍵可以為空值。如果不為空值，則每一個外鍵值必須等于相關聯的另外那張表中主鍵的某個值。

2．約束方法：外鍵約束

用戶定義完整性：它是設計者為了保證表中某些行或者列的數據滿足具體應用需求而自定義的一些規則。關系模型提供定義和檢驗這類完整性的機制，以便使用統一的系統方法處理，而不必由應用程序承擔這一功能。

3．約束方法：檢查約束、存儲過程、觸發器

SQL Server創建表的過程就是規定數據列的屬性的過程，同時也是實施數據完整性約束的過程。

創建數據表需要確定表的列名、數據類型、是否允許為空，還需要確定主鍵、必要的默認值、標識列和檢查約束。

表是用來存儲數據和操作數據的邏輯結構，用來組織和存儲數據，關系數據庫中的所有數據都表現為表的形式，數據表由行和列組成，對數據庫的操作，基本上就是對數據表的操作。SQL Server中的數據表分為臨時表和永久表，臨時表存儲在tempdb系統數據庫中，當不再使用或者退出SQL Server時，臨時表會自動刪除；而永久表一旦創建之后，除非用戶刪除，否則將一直存放在數據庫文件中。SQL Server 2016中提供了兩種創建數據表的方法：一種是通過對象資源管理器創建，另一種是通過Transact-SQL語句進行創建，下面分別詳細介紹這兩種方法。

3.2.1　數據類型

數據類型是一種屬性，用于指定對象可保存的數據的類型，SQL Server 2016中支持多種數據類型，包括字符類型、數值類型以及日期時間類型等。數據類型相當于一個容器，容器的大小決定了裝的東西的多少，將數據分為不同的類型可以節省磁盤空間和資源。

SQL Server還能自動限制每個數據類型的取值范圍，例如定義了一個數據類型為int的字段，如果插入數據時插入的值的大小在smallint或者tinyint范圍之內，SQL Server會自動將類型轉換為smallint或tinyint，這樣一來，在存儲數據時，占用的存儲空間只有int數據類型的1/2或者1/4。

SQL Server數據庫管理系統中的數據類型可以分為兩類，分別是：系統默認的數據類型和用戶自定義的數據類型，下面分別介紹這兩大類數據類型的內容。

1．系統數據類型

SQL Server 2016提供的系統數據類型有以下幾大類，共25種。SQL Server會自動限制每個系統數據類型的值的范圍，當插入數據庫中的值超過了數據類型允許的范圍時，SQL Server就會報錯。

（1）整數數據類型

整數數據類型是常用的數據類型之一，主要用于存儲數值，可以直接進行數據運算而不必使用函數轉換。

① bigint

每個bigint存儲在8字節中，其中1個二進制位表示符號，其他63個二進制位表示長度和大小，可以表示-2⁶³~2⁶³-1范圍內的所有整數。

② int

int或者integer，每個int存儲在4字節中，其中1個二進制位表示符號，其他31個二進制位表示長度和大小，可以表示-2³¹~2³¹-1范圍內的所有整數。

③ smallint

每個smallint類型的數據占用了兩個字節的存儲空間，其中一個二進制位表示整數值的正負號，其他15個二進制位表示長度和大小，可以表示-2¹⁵~2¹⁵-1范圍內的所有整數。

④ tinyint

每個tinyint類型的數據占用了一個字節的存儲空間，可以表示0~255范圍內的所有整數。

（2）浮點數據類型

浮點數據類型存儲十進制小數，用于表示浮點數值數據的大致數值數據類型。浮點數據為近似值；浮點數值的數據在SQL Server中采用只入不舍的方式進行存儲，即當且僅當要舍入的數是一個非零數時，對其保留數字部分的最低有效位上的數值加1，并進行必要的進位。

① real

可以存儲正的或者負的十進制數值，它的存儲范圍為-3.40E+38~-1.18E-38、0以及1.18E-38~3.40E + 38。每個real類型的數據占用4個字節的存儲空間。

② float [( n )]

其中n為用于存儲float數值尾數的位數（以科學記數法表示），因此可以確定精度和存儲大小。如果指定了n，則它必須是介于1和53之間的某個值。n的默認值為53。

其范圍為-1.79E+308~-2.23E-308、0以及2.23E–308~1.79E+308。如果不指定數據類型float的長度，它占用8個字節的存儲空間。float數據類型可以寫成float(n)的形式，n指定float數據的精度，n為1~53的整數值。當n取1~24時，實際上是定義了一個real類型的數據，系統用4個字節存儲它；當n取25~53時，系統認為其是float類型，用8個字節存儲它。

③ decimal[ (p[ , s] )]和numeric[ (p[ , s] )]

帶固定精度和小數位數的數值數據類型。使用最大精度時，有效值為-10³⁸+1~10³⁸-1。numeric在功能上等價于decimal。

p（精度）指定了最多可以存儲的十進制數字的總位數，包括小數點左邊和右邊的位數。該精度必須是從1到最大精度38之間的值。默認精度為18。

s（小數位數）指定小數點右邊可以存儲的十進制數字的最大位數。小數位數必須是從0到p之間的值。僅在指定精度后才可以指定小數位數。默認的小數位數為0；因此，0 <= s <= p。最大存儲大小基于精度而變化。例如：decimal(10,5)表示共有10位數，其中整數5位，小數5位。

（3）字符數據類型

字符數據類型也是SQL Server中最常用的數據類型之一，用來存儲各種字母、數字符號和特殊符號。在使用字符數據類型時，需要在其前后加上英文單引號或者雙引號。

① char(n)

當用char數據類型存儲數據時，每個字符和符號占用一個字節的存儲空間。n表示所有字符所占的存儲空間，n的取值為1~8000。若不指定n值，系統默認n的值為1。若輸入數據的字符串長度小于n，則系統自動在其后添加空格來填滿設定好的空間；若輸入的數據過長，將會截掉其超出部分。

② varchar(n|max)

n為存儲字符的最大長度，取值范圍為1~8000，但可根據實際存儲的字符數改變存儲空間，max表示最大存儲大小是2³¹-1個字節。存儲大小是輸入數據的實際長度加2個字節。所輸入數據的長度可以為0個字符。如varchar(20)，則對應的變量最多只能存儲20個字符，不夠20個字符時按實際存儲。

③ nchar(n)

n個字符的固定長度的Unicode字符數據。n值必須在1到4000之間（含），如果沒有在數據定義或變量聲明語句中指定n，默認長度為1。此數據類型采用Unicode標準字符集，因此每一個存儲單位占兩個字節，可將全世界文字囊括在內。

④ nvarchar(n | max)

與varchar相似，存儲可變長度Unicode字符數據。n值在1到4000之間（含），如果沒有在數據定義或變量聲明語句中指定n，默認長度為1。max指示最大存儲大小為2³¹-1字節。存儲大小是所輸入字符個數的兩倍加2個字節。所輸入數據的長度可以為0個字符。

（4）日期和時間數據類型

① date

存儲用字符串表示的日期數據，可以表示0001-01-01到9999-12-31（公元元年1月1日到公元9999年12月31日）間的任意日期值。數據格式為“YYYY-MM-DD”：

• YYYY：表示年份的四位數字，范圍為0001~9999。
• MM：表示指定年份中的月份的兩位數字，范圍為01~12。
• DD：表示指定月份中的某一天的兩位數字，范圍為01~31（最高值取決于具體月份）。

該類型數據占用3個字節的空間。

② time

以字符串形式記錄一天中的某個時間，取值范圍為00:00:00.0000000~23:59:59.9999999，數據格式為“hh:mm:ss[.nnnnnnn]”：

• hh：小時的兩位數字，范圍為0~23。
• mm：分鐘的兩位數字，范圍為0~59。
• ss：秒的兩位數字，范圍為0~59。
• n*：是0到7位數字，范圍為0~9999999，它表示秒的小數部分。

time值在存儲時占用5個字節的空間。

③ datetime

用于存儲時間和日期數據，從1753年1月1日到9999年12月31日，默認值為1900-01-01 00:00:00，當插入數據或在其他地方使用時，需用單引號或雙引號括起來?？梢允褂谩?”、“-”和“.”作為分隔符。該類型數據占用8個字節的空間。

④ datetime2

datetime類型的擴展，其數據范圍更大，默認的小數精度更高，并具有可選的用戶定義的精度。默認格式是：YYYY-MM-DD hh:mm:ss[.fractional seconds]，日期存取范圍是0001-01-01~9999-12-31（公元元年1月1日到公元9999年12月31日）。

⑤ smalldatetime

smalldatetime類型與datetime類型相似，只是其存取的范圍是從1900年1月1日到2079年6月6日，當日期時間值精度較小時，可以使用smalldatetime，該類型數據占用4個字節的空間。

⑥ datetimeoffset

用于定義一個采用24小時制與日期相組合并可識別時區的一日內時間。默認格式是“YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]”：

• hh：兩位數，范圍為-14~+14。
• mm：兩位數，范圍為00~59。

這里hh是時區偏移量，該類型數據中保存的是世界標準時間（UTC）值，例如要存儲北京時間2011年11月11日12點整，存儲時該值將是2011-11-11 12:00:00+08:00，因為北京處于東八區，比UTC早8個小時。存儲該類型數據時默認占用10個字節大小的固定存儲空間。

（5）文本和圖形數據類型

① text

用于存儲文本數據，服務器代碼頁中長度可變的非Unicode數據，最大長度為2³¹-1（2 147 483 647）個字符。當服務器代碼頁使用雙字節字符時，存儲仍是2 147 483 647字節。

② ntext

與text類型作用相同，為長度可變的Unicode數據，最大長度為2³⁰-1（1 073 741 823）個字符。存儲大小是所輸入字符個數的兩倍（以字節為單位）。

③ image

長度可變的二進制數據，為0~2³¹-1個字節。用于存儲照片、目錄圖片或者圖畫，容量也是2 147 483 647個字節，由系統根據數據的長度自動分配空間，存儲該字段的數據一般不能使用INSERT語句直接輸入。

（6）貨幣數據類型

① money

用于存儲貨幣值，取值范圍為正負922 337 213 685 477.580 8之間。money數據類型中整數部分包含19個數字，小數部分包含4位數字，因此money數據類型的精度是19，存儲時占用8個字節的存儲空間。

② smallmoney

與money類型相似，取值范圍為正負214 748.346 8之間，smallmoney存儲時占用4個字節存儲空間。輸入數據時在前面加上一個貨幣符號，如人民幣為￥或其他定義的貨幣符號。

（7）位數據類型

bit稱為位數據類型，只取0或1為值，長度1字節。bit值經常當作邏輯值用于判斷TRUE（1）和FALSE（0），輸入非零值時系統將其換為1。

（8）二進制數據類型

① binary(n)

長度為n字節的固定長度二進制數據，其中n是1~8000的值。存儲大小為n字節。在輸入binary值時，必須在前面帶0x，可以使用0~9和A~F表示二進制值，例如輸入0xAA5代表AA5，如果輸入數據長度大于定義的長度，超出的部分會被截斷。

② varbinary(n|max)

可變長度二進制數據。n可以是1~8000的值。max指示最大存儲大小為2³¹-1字節。存儲大小為所輸入數據的實際長度+2個字節。

在定義的范圍內，不論輸入的時間長度是多少，binary類型的數據都占用相同的存儲空間；而對于varbinary類型的數據，在存儲時根據實際值的長度使用存儲空間。

（9）其他數據類型

① rowversion

每個數據庫都有一個計數器，當對數據庫中包含rowversion列的表執行插入或更新操作時，該計數器值就會增加。此計數器是數據庫行版本。一個表只能有一個rowversion列。每次修改或插入包含rowversion列的行時，就會在rowversion列中插入經過增量的數據庫行版本值。

公開數據庫中自動生成的唯一二進制數字的數據類型。rowversion通常用作給表行加版本戳的機制。存儲大小為8個字節。rowversion數據類型只是遞增的數字，不保留日期或時間。

② timestamp

時間戳數據類型，timestamp的數據類型為rowversion數據類型的同義詞，提供數據庫范圍內的唯一值，反映數據修改的相對順序，是一個單調上升的計數器，此列的值被自動更新。

在CREATE TABLE或ALTER TABLE語句中，不必為timestamp數據類型指定列名，例如：

此時SQL Server數據庫引擎將生成timestamp列名；但rowversion不具有這樣的行為。在使用rowversion時，必須指定列名，例如：

③ uniqueidentifier

16字節GUID（Globally Unique Identifier，全球唯一標識符），是SQL Server根據網絡適配器地址和主機CPU時鐘產生的唯一號碼，其中，每個位都是0~9或a~f范圍內的十六進制數字。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF，此號碼可以通過調用newid()函數獲得，在全世界各地的計算機經由此函數產生的數字不會相同。

④ cursor

游標數據類型，該類型類似于數據表，其保存的數據中包含行和列值，但是沒有索引，游標用來建立一個數據的數據集，每次處理一行數據。

⑤ sql_variant

用于存儲除文本、圖形數據和timestamp數據外的其他任何合法的SQL Server數據，可以方便SQL Server的開發工作。

⑥ table

用于存儲對表或者視圖處理后的結果集。這種新的數據類型使得變量可以存儲一個表，從而使函數或過程返回查詢結果更加方便、快捷。

⑦ xml

存儲xml數據的數據類型?？梢栽诹兄谢蛘選ml類型的變量中存儲xml實例。存儲的xml數據類型表示實例大小不能超過2 GB。

2．自定義數據類型

SQL Server允許用戶自定義數據類型，用戶自定義數據類型是建立在SQL Server系統數據類型基礎上的，自定義的數據類型使得數據庫開發人員能夠根據需要定義符合自己開發需求的數據類型。自定義數據類型雖然使用比較方便，但是需要大量的性能開銷，所以使用時要謹慎。當用戶定義一種數據類型時，需要指定該類型的名稱、所基于的系統數據類型以及是否允許為空等。SQL Server為用戶提供了兩種方法來創建自定義數據類型。下面將分別介紹這兩種定義數據類型的方法。

（1）使用對象資源管理器創建用戶定義數據類型

首先連接到SQL Server服務器，自定義數據類型與具體的數據庫相關，因此在對象資源管理器中創建新數據類型之前，需要選擇要創建數據類型所在的數據庫，這里，按照第2章介紹的創建數據庫的方法，創建一個名稱為test的數據庫，使用系統默認的參數即可。

創建用戶自定義數據類型的具體操作步驟如下。

數據庫創建成功之后，在【對象資源管理器】中依次打開【test】|【可編程性】|【類型】節點，右擊【用戶定義數據類型】節點，在彈出的快捷菜單中選擇【新建用戶定義數據類型】菜單命令，如圖3-1所示。

打開【新建用戶定義數據類型】窗口，在【名稱】文本框中輸入需要定義的數據類型的名稱，這里輸入新數據類型的名稱為mingchen，表示存儲一個地址數據值，在【數據類型】下拉列表框中選擇char的系統數據類型，【長度】指定為8000，如果用戶希望該類型的字段值為空的話，可以選擇【允許NULL值】復選框，其他參數不做更改，如圖3-2所示。

單擊【確認】按鈕，完成用戶定義數據類型的創建，即可看到新創建的自定義數據類型，如圖3-3所示。

（2）使用存儲過程創建用戶定義數據類型

除了使用圖形界面創建自定義數據類型，SQL Server 2016中的系統存儲過程sp_addtype也可以為用戶提供使用T-SQL語句創建自定義數據類型的方法，其語法形式如下：

其中，各參數的含義如下。

• type：用于指定用戶定義的數據類型的名稱。
• system_data_type：用于指定相應的系統提供的數據類型的名稱及定義。注意，未能使用timestamp數據類型，當所使用的系統數據類型有額外說明時，需要用引號將其括起來。
• null_type：用于指定用戶自定義的數據類型的null屬性，其值可以為“null”“not null”或“nonull”。默認時與系統默認的null屬性相同。用戶自定義的數據類型的名稱在數據庫中應該是唯一的。

【例3.1】自定義一個地址HomeAddress2數據類型，輸入語句如下。

新建一個使用當前連接進行的查詢，在打開的查詢編輯器中輸入上面的語句，輸入完成之后單擊【執行】按鈕，即可完成用戶定義數據類型的創建，執行結果如圖3-4所示。

執行完成之后，刷新【用戶定義數據類型】節點，將會看到新增的數據類型，如圖3-5所示。

刪除用戶自定義數據類型的方法也有兩種。第一種是在對象資源管理器中右擊想要刪除的數據類型，在彈出的快捷菜單中選擇【刪除】菜單命令，如圖3-6所示。

打開【刪除對象】窗口，單擊【確定】按鈕即可，如圖3-7所示。

另一種方法就是使用系統存儲過程sp_droptype來刪除，語法格式如下：

type為用戶定義的數據類型，例如這里刪除HomeAddress2，T-SQL語句如下：

3.2.2　使用對象資源管理器創建表

對象資源管理器提供的創建表的方法可以讓用戶輕而易舉地完成表的創建，具體操作步驟如下。

啟動SSMS，在【對象資源管理器】中展開【數據庫】節點下面的【test】數據庫。右擊【表】節點，在彈出的快捷菜單中選擇【表】菜單命令，如圖3-8所示。

打開【表設計】窗口，在該窗口中創建表中各個字段的字段名和數據類型，這里定義一個名稱為member的表，其結構如下：

根據member表結構，分別指定各個字段的名稱和數據類型，如圖3-9所示。

表設計完成之后，單擊【保存】或者【關閉】按鈕，在彈出的【選擇名稱】對話框中輸入表名稱member，單擊【確定】按鈕，完成表的創建，如圖3-10所示。

單擊【對象資源管理器】窗口中的【刷新】按鈕，即可看到新增加的表，如圖3-11所示。

3.2.3　使用Transact-SQL創建表

在Transact-SQL中，使用CREATE TABLE語句創建數據表，該語句非常靈活，其基本語法格式如下：

其中，各參數說明如下。

• database_name：指定要在其中創建表的數據庫名稱，不指定數據庫名稱，則默認使用當前數據庫。
• schema_name：指定新表所屬架構的名稱，若此項為空，則默認為新表的創建者所在的當前架構。
• table_name：指定創建的數據表的名稱。
• column_name：指定數據表中的各個列的名稱，列名稱必須唯一。
• data_type：指定字段列的數據類型，可以是系統數據類型，也可以是用戶定義數據類型。
• NULL | NOT NULL：表示確定列中是否允許使用空值。
• DEFAULT：用于指定列的默認值。
• ROWGUIDCOL：指示新列是行GUID列。對于每個表，只能將其中的一個uniqueidentifier列指定為ROWGUIDCOL列。
• PRIMARY KEY：主鍵約束，通過唯一索引對給定的一列或多列強制實體完整性的約束。每個表只能創建一個PRIMARY KEY約束。PRIMARY KEY約束中的所有列都必須定義為NOT NULL。
• UNIQUE：唯一性約束，該約束通過唯一索引為一個或多個指定列提供實體完整性。一個表可以有多個UNIQUE約束。
• CLUSTERED | NONCLUSTERED：表示為PRIMARY KEY或UNIQUE約束創建聚集索引還是非聚集索引。PRIMARY KEY約束默認為CLUSTERED，UNIQUE約束默認為NONCLUSTERED。在CREATE TABLE語句中，可只為一個約束指定CLUSTERED。如果在為UNIQUE約束指定CLUSTERED的同時又指定了RIMARY KEY約束，則PRIMARY KEY將默認為NONCLUSTERED。
• [ ASC | DESC ]：指定加入到表約束中的一列或多列的排序順序，ASC為升序排列，DESC為降序排列，默認值為ASC。

介紹完T-SQL中創建數據表的語句，下面舉例說明。

【例3.2】使用T-SQL語句創建數據表authors，輸入語句如下。

新建立一個當前連接查詢，在查詢編輯器中輸入上面的代碼，如圖3-12所示。

執行成功之后，刷新數據庫列表可以看到新建名稱為authors的數據表，如圖3-13所示。