1、第5章 SQLSERVER 數據表本章的學習目標：掌握創建數據庫的技能熟悉數據表的基本特點和類型掌握用SQL Server Management Studio和Transact-SQL語言創建數據表的技能掌握用SQL Server Management Studio和Transact-SQL語言修改數據表結構的技能掌握用SQL Server Management Studio和Transact-SQL語言操作數據表中數據的相關技能理解什么是數據的完整性掌握約束的創建、查看及刪除相關技能5.1 數據表創建在Microsoft SQL Server 2008中，創建數據表有兩種方式：一是用該數據庫

2、管理系統自帶的SQL Server Management Studio可視化地創建數據表，二是使用Transact-SQL語言直接創建數據表。5.1 數據表創建5.1.1 使用SQL Server Management Studio創建數據表5.1.2 使用Transact-SQL語言創建數據表5.1.1 使用SQL Server Management Studio創建數據表1 打開Microsoft SQL Server 20082 創建數據庫3創建數據表表結構設置主鍵5.1.2 使用Transact-SQL語言創建數據表使用Transact-SQL語言創建數據表時，主要是用到CREATE

3、TABLE語句。CREATE TABLE 數據表名字（字段名稱1 (對字段的定義部分)字段名稱2 (對字段的定義部分) .字段名稱n (對字段的定義部分)）；1客房類型表的創建CREATE TABLE 客房類型( 類型編號 char(4) CONSTRAINT pk_lxbh PRIMARY KEY NOT NULL , 名稱 varchar(10) UNIQUE NOT NULL , 面積 float , 床位 int , 價格 smallmoney NOT NULL, 空調 bit , 電視 bit , 衛生間 bit) 2客房信息表的創建CREATE TABLE 客房信息( 客房號 ch

4、ar(6) CONSTRAINT pk_kfbh PRIMARY KEY NOT NULL , 類型編號 char(4) CONSTRAINT fk_kfxxlxdm REFERENCES 客房類型(類型編號) NOT NULL , 位置 varchar(20) , 描述 varchar(50) ) 3客戶信息表的創建CREATE TABLE 客戶信息 ( 身份證號 varchar(18) CONSTRAINT pk_sfzh PRIMARY KEY NOT NULL , 姓名 varchar(12) NOT NULL , 電話 varchar(15) NOT NULL ) 4客房業務表的創建

5、CREATE TABLE 客房業務 ( 客房號 char(6) CONSTRAINT fk_kfhyw REFERENCES 客房信息(客房號) NOT NULL , 入住時間 datetime NOT NULL , 身份證號 varchar(18) CONSTRAINT fk_sfzhyw REFERENCES 客戶信息(身份證號) NOT NULL,客戶姓名 varchar(12) NOT NULL ,客戶電話 varchar(15) NOT NULL ,備注 nVarchar(50) NULL ) 5.2 修改表結構在Microsoft SQL Server 2008中，修改表結構有兩種

6、方式：一是用該數據庫管理系統自帶的SQL Server Management Studio可視化地修改表結構，二是使用Transact-SQL語言直接修改表結構。5.2 修改表結構5.2.1 使用SQL Server Management Studio修改表結構5.2.2 使用Transact-SQL語言修改表結構5.2.1 使用SQL Server Management Studio修改表結構1打開表設計界面2 修改表結構5.2.2 使用Transact-SQL語言修改表結構1 使用ALTER語句添加字段對數據表進行字段添加的語法如下：ALTER TABLE 數據表名 ADD 字段名 字段類

7、型 是否為空【例5-7】向客房類型表中添加一個字段【備注】，類型為nchar，長度為10。USE hotelGOALTER TABLE 客房類型 ADD 備注 nchar(10) NULLGO執行后的查詢結果2 使用ALTER語句修改字段類型對數據表進行字段類型修改的語法如下：ALTER TABLE table_name ALTER COLUMN column_name new_type_name【例5-8】客房類型的【備注】屬性列的字段類型為nchar10，現在把它修改為ntext類型。具體的Transact-SQL語句如下所示：USE hotelGOALTER TABLE 客房類型 ALT

8、ER COLUMN 備注 ntext GO執行后的結果3 使用ALTER語句刪除字段對數據表進行字段刪除的語法如下：ALTER TABLE 數據表名 DROP COLUMN 字段名【例5-9】對客房類型表刪除剛才添加的字段【備注】。具體的Transact-SQL語句如下所示：USE hotelGOALTER TABLE 客房類型 DROP COLUMN 備注 GO執行后的結果4刪除數據表操作介紹刪除表就是將表中數據和表的結構從數據庫中永久性地去除。表被刪除之后，就不能再恢復該表的定義。刪除表可以使用DROP TABLE語句來完成，該語句的語法形式如下：DROP TABLE table_name

9、可以使用DROP TABLE語句一次性地刪除多個表，表之間使用逗號分開。5.3 表數據的操作所謂表數據的操作，就是對存儲在數據表中的數據的相關操作。在Microsoft SQL Server 2008中，對表數據操作有兩種方式：一是用該數據庫管理系統自帶的SQL Server Management Studio可視化地對數據表操作，二是使用Transact-SQL語言直接操作表數據。5.3 表數據的操作5.3.1 使用SQL Server Management Studio插入、更新和刪除表數據5.3.2 使用Transact-SQL語言插入、更新和刪除表數據5.3.1 使用SQL Serve

10、r Management Studio插入、更新和刪除表數據1 打開表數據編輯界面2 對表數據進行編輯5.3.2 使用Transact-SQL語言插入、更新和刪除表數據雖然使用SQL Server Management Studio可以很方便地進行數據的插入、更新和刪除等操作，但是對于編程人員來說，使用最多的還是通過Transact-SQL語言來完成這些操作。從另一方面來說，當數據量很龐大后，使用SQL Server Management Studio對數據進行操作也不方便，更多時候需要使用Transact-SQL語言對數據進行批量操作。本小節講詳細介紹如何使用Transact-SQL語言進行

11、數據的插入、更新和刪除操作（以節創建的客房類型數據表為操作對象）。1插入數據一般地，使用INSERT語句一次只能插入一行數據。INSERT語句的基本語法形式如下：INSERT INTO table_or_view_name (column_list) VALUES (expression)【例5-11】在客房類型表中插入一行數據，類型編號為“P004”,名稱為“普通三人間”，面積為“45”，床位為“3”，價格為“550”，空調為“1”，電視為“1”，衛生間為“0”。具體的Transact-SQL語句如下所示：INSERT INTO hotel.dbo.客房類型 (類型編號,名稱 ,面積 ,床位

12、 ,價格 ,空調 ,電視 ,衛生間) VALUES (P004 ,普通三人間 ,45 ,3 ,550 ,1 ,1 ,0)GO查看新添加的數據2更新數據可以使用UPDATE語句更新表中已經存在的數據。UPDATE語句既可以一次更新一行數據，也可以一次更新許多行，甚至可以一次更新表中的全部數據行。UPDATE語句的基本語法形式如下：UPDATE table_or_view_name SET column_name = expression, WHERE search_condition （1）不帶where子句的UPDATE語句【例5-13】由于物價上漲，現在對酒店所有類型房間的價格統一上調50元

13、。具體的Transact-SQL語句如下所示：USE hotelGOUPDATE 客房類型 SET 價格 = 價格+50GO執行結果（2）帶where子句的UPDATE語句【例5-14】由于物價上漲，現在對酒店的豪華商務房的價格統一上調50元。具體的Transact-SQL語句如下所示：USE hotelGOUPDATE 客房類型 SET 價格 = 價格+50 WHERE 名稱=豪華商務房GO執行結果3刪除數據當表中的數據不再需要時，可以將其刪除。一般情況下，使用DELETE語句刪除數據。DELETE語句可以從一個表中刪除一行或多行數據。刪除數據的DELEE語句的基本語法形式如下：DELETE

14、 FROM table_or_name WHERE search_condition （1）帶where子句的DELETE語句【例5-15】刪除客房類型數據表中【面積】=30平方米的房間類型數據。具體的Transact-SQL語句如下所示：USE hotelGODELETE FROM 客房類型 WHERE 面積=30GO刪除失敗，因為有外鍵關系應先刪除外鍵數據執行成功查看結果（2）不帶where子句的DELETE語句5.4 數據表的約束數據完整性就是指存儲在數據庫中的數據的一致性和準確性。在評價數據庫的設計時，數據完整性的設計是數據庫設計好壞的一項重要指標。在Microsoft SQL Ser

15、ver 2008系統中，有3種數據完整性類型，即域完整性、實體完整性和引用完整性。5.4 數據表的約束5.4.1 約束的概念和類型5.4.2 約束的管理5.4.1 約束的概念和類型完整性類型約 束 類 型描 述域完整性DEFAULT在使用INSERT語句插入數據時，如果某個列的值沒有明確提供，則將定義的缺省值插入到該列中CHECK指定某一個列中的可保存值的范圍實體完整性主鍵每一行的唯一標識符，確保用戶不能輸入冗余值和確保創建索引，提高性能，不允許空值UNIQUE防止出現冗余值，并且確保創建索引，提高性能，允許空值引用完整性外鍵定義一列或者幾列，其值與本表或者另外一個表的主鍵值匹配約束使用注意事

16、項（1）不必刪除表，就可以直接創建、修改和刪除約束的定義。（2）應該在應用程序中增加錯誤檢查機制，測試數據是否與約束相沖突。（3）當在表上增加約束時，SQL Server系統將檢查表中的數據是否與約束沖突。5.4.2 約束的管理本節詳細研究各種DEFAULT、CHECK、主鍵、UNIQUE、外鍵等約束的特點、創建方法、修改等內容。1 DEFAULT約束DEFAULT 約束用于向列中插入默認值。如果沒有規定其他的值，那么會將默認值添加到所有的新紀錄。在創建表時和創建表后，都可以對表中的列使用DEFAULT 約束。創建表時添加DEFAULT約束【例5-18】對于客房類型數據表，創建的時候可以把【電

17、視】字段默認為“1”，這樣插入數據的時候就可以不用輸入這個屬性列的值。Transact-SQL代碼如下所示：CREATE TABLE 客房類型( 類型編號 char(4) CONSTRAINT pk_lxbh PRIMARY KEY NOT NULL , 名稱 varchar(10) UNIQUE NOT NULL , 面積 float , 床位 int , 價格 smallmoney NOT NULL, 空調 bit , 電視 bit DEFAULT 1, 衛生間 bit ) 創建表后添加DEFAULT約束對于已經創建完成的數據表，也可以對某些屬性列添加默認值。具體Transact-SQL代

18、碼如下所示：ALTER TABLE 客房類型ADD CONSTRAINT col_b_defDEFAULT 1 FOR 電視 ;2 CHECK約束CHECK約束用來限制用戶輸入某一個列的數據，即在該列中只能輸入指定范圍的數據。CHECK約束的兩種基本語法形式如下：CONSTRAINT constraint_name CHECK (logical_expression)CHECK (logical_expression)創建表時添加CHECK約束【例5-19】在數據表房間類型的【名稱】列中可以創建一個CHECK約束，指定其取值范圍是“單人間”、“雙人間”、“三人間”。這樣，當向【名稱】列輸入數據

19、時，要么輸入數據這三個數據之一，而不能輸入其他數據。具體Transact-SQL代碼如下所示：CREATE TABLE 客房類型( 類型編號 char(4) CONSTRAINT pk_lxbh1 PRIMARY KEY NOT NULL , 名稱 varchar(10) UNIQUE NOT NULL CHECK(名稱=單人間 or 名稱=雙人間 or 名稱=三人間), 面積 float , 床位 int , 價格 smallmoney NOT NULL, 空調 bit , 電視 bit, 衛生間 bit)創建表后添加CHECK約束對于已經創建完成的數據表，也可以對某些屬性列添加CHECK約

20、束。具體Transact-SQL代碼如下所示：ALTER TABLE 客房類型ADD CONSTRAINT col_b_checkCHECK(名稱=單人間 or 名稱=雙人間 or 名稱=三人間),3 主鍵約束主鍵約束在表中定義一個主鍵值，這是唯一確定表中每一行數據的標識符。在所有的約束類型中，主鍵約束是最重要的一種約束類型，也是使用最廣泛的約束類型。定義主鍵約束的4種基本語法形式如下所示：CONSTRAINT constraint_name PRIMARY KEYPRIMARY KEYCONSTRAINT constraint_name PRIMARY KEY (column_list)PR

21、IMARY KEY (column_list)創建表后添加主鍵約束【例5-21】一般在創建數據表的時候就會創建數據表的主鍵（參考本章小節客房類型表的創建過程）。如果在創建數據表的時候沒有添加主鍵，則可以在后期使用Alter語句對數據表進行主鍵設置，具體Transact-SQL代碼如下所示：ALTER TABLE 客房類型ADD CONSTRAINT pk_TypeID PRIMARY KEY (類型編號)使用主鍵約束的注意事項（1）每一個表最多只能定義一個主鍵約束。（2）主鍵列所輸入的值必須是唯一的。如果主鍵約束由兩個或兩個以上的列組成，那么這些列的組合必須是唯一的。（3）主鍵列不允許空值。（

22、4）主鍵約束在指定的列上創建了一個唯一性索引。該唯一性索引既可以是聚集索引，也可以是非聚集索引。在默認情況下創建的是聚集索引。如果表中已經有聚集索引，那么在創建主鍵約束之前，要么把已有的聚集索引刪除，要么指定所創建的索引是非聚集索引。（5）可以在定義主鍵約束時添加級聯操作選項。4 UNIQUE約束UNIQUE約束指定表中某一個列或多個列不能有相同的兩行或兩行以上的數據存在。這種約束通過實現唯一性索引來強制實體完整性。UNIQUE約束的4種基本語法形式如下所示：CONSTRAINT constraint_name UNIQUEUNIQUECONSTRAINT constraint_name UN

23、IQUE (column_list)UNIQUE (column_list)使用UNIQUE約束的注意事項（1）UNIQUE約束所在的列允許空值，但是主鍵約束所在的列不允許空值。（2）一個表中可以有多個UNIQUE約束。（3）可以把UNIQUE約束放在一個或者多個列上，這些列或列的組合必須有唯一的值。但是，UNIQUE約束所在的列并不是表的主鍵列。（4）UNIQUE約束強制在指定的列上創建一個唯一性索引。在默認情況下，是創建唯一性的非聚集索引。但是，在定義UNIQUE約束時也可以指定所創建的索引是聚集索引。5 外鍵約束外鍵約束強制引用完整性, 一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY。外鍵約束定義一個或多個列，這些列可以引用同一個表或另外一個表中的主鍵約束列或UNIQUE約束列。創建表時添加外鍵約束【例5-22】在創建客房信息數據表的時候，把客房類型數據表中的【類型編號】作為其外鍵，代碼如下：CREATE TABLE 客房信息( 客房號 char(6) CONSTRAINT pk_kfbh PRIMARY KEY NOT NULL , 類型編號 char(4) CONSTRAINT fk_kfxxlxdm REFERENCES 客房類型(類型編號) NOT NULL , 位置 varchar(20) , 描述 varchar(50