第三講管理表第3講管理表哈爾濱師范大學恒星學院本講主要內容表的基礎知識用SSMS管理表用T-SQL語言管理表常用工具簡介123數據庫創(chuàng)建后，就可以創(chuàng)建表來存儲數據，通常是存儲在永久表中.不過,用戶也可以建立臨時表.表會一直存儲在數據庫文件內,直到被刪除為止.只要用戶擁有適當權限,就可以使用它們.一.

表的基礎知識

表是由行和列組成的.創(chuàng)建表的過程主要就是定義表的列的過程.與表有關的兩個常用概念是：字段和記錄.其中記錄是表的一行，是表所塑造的單一對象的實體.而字段是表中的列，描述一條記錄要有若干個列.列代表對象的屬性(例如,姓名、住址等).

在SQLServer中，每個數據庫中最多可以創(chuàng)建200萬個表，用戶創(chuàng)建數據庫表時，最多可以定義1024列，也就是可以定義1024個字段.

但是很少有哪個設計良好的關系數據庫表會包含25個以上的列，它們絕大多數都只包含少數的列.

每個列有自己的屬性,常用且重要的三個屬性是:列名、數據類型和約束.

SQLServer系統(tǒng)內的對象在命名時可以使用中文,但要盡量避免這樣做，以免引發(fā)工具程序或前端應用程序的無謂錯誤.對象的命名必須符合系統(tǒng)的命名規(guī)則.比如名稱標識符含字母在1~128個(臨時表的名稱在116個字符內),特殊字符的名稱要放到分隔符[]或””內等等.命名規(guī)則數據類型是數據的格式,表定義中的每個列必須有數據類型,數據操作中,SQLServer自動進行數據檢驗.

常用數據類型主要有:

整數型:int,smallint,bigint

浮點型:numeric,float(n),real

字符型:char(n),varchar(n),nchar(n)

日期時間型:smalldatetime,date,time數據類型SQLServer提供了兩種方法創(chuàng)建數據庫表.第一種方法是通過圖形化界面創(chuàng)建即使用“SQLServerManagementStudio”(簡記為SSMS)創(chuàng)建表，它提供了一個前端的、填充式的表設計器以簡化表的設計工作；另一種方法是利用T-SQL語句中的CREATE

命令創(chuàng)建表.二.用SSMS管理表在SSMS中,展開要建表的數據庫，比如pubs,右鍵單擊“表”節(jié)點,在彈出的菜單中選擇“新建表”,打開表設計器,如下圖.

注意在下方列屬性視圖中設置主鍵,非空,標識列,默認值.

創(chuàng)建表

例建立表student存放學生的信息.idchar(7),namechar(10),

sexchar(2),birthdaysmalldatetime,birth_placevarchar(50),classchar(10)練習

---用T-SQL命令CREATE創(chuàng)建表.

CREATETABLEstudent(idchar(7),namechar(10),

sexchar(2),birthdaysmalldatetime,birth_placevarchar(50),classchar(10))三.用T-SQL語言

管理表哈爾濱師范大學恒星學院

修改表(1)啟動SSMS后,展開SQLServer實例和數據庫.(2)右擊要修改的表，然后從彈出的快捷菜單中選擇“設計”命令.

(3)在“表設計器”中，可以新增列、刪除列和修改列的名稱、數據類型、長度、是否允許為空等.

(4)當完成修改表的操作后，單擊工具欄上的“保存”按鈕.利用T-SQL語句修改數據表

修改數據表的語法格式如下：ALTERTABLEtable_name{ALTERCOLUMNcolumn_namenewdatatype[(precision[，scale])][NULL|NOTNULL]|ADD{[<column_definition>][，．．．n]}|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}[，．．．n]}哈爾濱師范大學恒星學院

刪除表(1)啟動SSMS后,展開SQLServer實例和數據庫.(2)右擊要修改的表，然后從彈出的快捷菜單中選擇“刪除”，打開“刪除對象”對話框.

(3)在“刪除對象”對話框中，顯示出刪除對象的屬性信息，單擊“確定”按鈕.

注意:刪除表只能夠刪除用戶表，不能夠刪除系統(tǒng)表.刪除表一旦操作完成，表中數據也一并被刪除，而且是無法恢復的.利用T-SQL語句刪除數據表

刪除數據表的語法格式如下：

DROPTABLEtable_name[，．．．n]

其中，table_name為所要刪除的表的名稱。

刪除表只能夠刪除用戶表，不能夠刪除系統(tǒng)表。

完整性與約束

完整性

數據完整性是數據庫設計方面一個非常重要的問題，數據完整性代表數據的正確性、一致性與可靠性，實施完整性的目的在于確保數據的質量。

在SQLServer中，根據數據完整性措施所作用的數據庫對象和范圍不同，可以將數據完整性分類為實體完整性、域完整性和參照完整性等。

1．實體完整性

實體完整性把數據表中的每行看作一個實體，它要求所有行都具有唯一標識。在SQLServer中，可以通過建立PRIMARYKEY約束、UNIQUE約束、唯一索引，以及列IDENTITY屬性等措施來實施實體完整性。2．域完整性

域完整性要求數據表中指定列的數據具有正確的數據類型、格式和有效的數據范圍。域完整性通過默認值、FOREIGNKEY、CHECK等約束，以及默認、規(guī)則等數據庫對象來實現。3．參照完整性

參照完整性維持被參照表和參照表之間的數據一致性。在SQLServer中，參照完整性通過主鍵與外鍵或唯一鍵與外鍵之間的關系來實現，通過建立FOREIGNKEY約束來實施。在被參照表中，當其主鍵值被其他表所參照時，該行不能被刪除，也不允許改變。在參照表中，不允許參照不存在的主鍵值。

約束語句是表定義語句的重要組成部分.通過約束機制,可使數據庫引擎強制控制數據內容的完整性.

約束語句可以在列或表兩個級別完成.例如,將PRIMARYKEY約束可以作用在列上,若PRIMARYKEY

約束結合多個字段,則只能在表級別建立.

使用時,約束優(yōu)先于DML

觸發(fā)過程、規(guī)則和默認.約束

●

not

null非空約束---不允許空值●primarykey主鍵約束---識別表的記錄

---主鍵值唯一,非空●

check檢查約束---限制列值范圍---操作數據時,系統(tǒng)自動驗證數據

●

unique唯一性約束---值唯一

---該約束可建在表上,可用于有空值的列●

default默認值約束---默認的值

---常用的默認值getdate(),newid()●foreignkey外鍵約束---該鍵值參照另一表鍵值

---另一表(引用表)的值必須存在唯一

約束的種類PK

客戶編號公司名稱聯系人聯系人稱呼地址城市行政區(qū)郵政編碼國家地區(qū)電話傳真

訂單編號FK

客戶編號員工編號訂單日期要貨日期送貨日期送貨方式運費收貨人送貨地址送貨城市哈爾濱師范大學恒星學院使用表設計器創(chuàng)建PrimaryKey約束在表設計器中，選擇需要設置主鍵的列(如需要設置多個列為主鍵，則選中所有需要設置為主鍵的所有列)，單擊鼠標右鍵，然后從彈出的快捷菜單中選擇“設置主鍵”命令，完成主鍵設置，這時主鍵列的左邊會顯示“黃色鑰匙”圖標啟動.如何修改和刪除PrimaryKey約束呢?

建立約束使用T-SQL語句創(chuàng)建PRIMARYKEY約束

創(chuàng)建主鍵約束的語法形式如下：

[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]

(column_name[,…n])

其中，CLUSTERED|NONCLUSTERED表示所創(chuàng)建的UNIQUE約束是聚集索引還是非聚集索引，默認為CLUSTERED聚集索引。哈爾濱師范大學恒星學院

使用表設計器創(chuàng)建Unique

約束

在表設計器中可以創(chuàng)建、修改和刪除Unique

約束.

要點:選中列,按右鍵,選“索引/鍵(I)”.

如何創(chuàng)建Check約束呢?建立約束使用T-SQL語句創(chuàng)建UNIQUE約束

創(chuàng)建唯一性約束的語法形式如下：

[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]

其中，CLUSTERED|NONCLUSTERED表示所創(chuàng)建的UNIQUE約束是聚集索引還是非聚集索引，默認為NONCLUSTERED非聚集索引。哈爾濱師范大學恒星學院

CHECK約束

CHECK約束用于限制輸入到一列或多列的值的范圍，從邏輯表達式判斷數據的有效性，也就是一個列的輸入內容必須滿足CHECK約束的條件，否則，數據無法正常輸入，從而強制數據的域完整性。建立約束使用T-SQL語句創(chuàng)建CHECK約束

創(chuàng)建檢查約束的語法形式如下：

[CONSTRAINTconstraint_name]CHECK(check_expression)

其中，check_expression為檢查表達式。使用表設計器創(chuàng)建DEFAULT約束

在表設計器中可以創(chuàng)建、修改和刪除DEFAULT約束。其操作步驟如下：

在表設計器中，選擇需要設置DEFAULT值的列，在下面“列屬性”的“默認值或綁定”欄中輸入默認值，然后單擊工具欄中的“保存”按鈕，即完成DEFAULT約束的創(chuàng)建。

使用T-SQL語句創(chuàng)建DEFAULT約束

創(chuàng)建默認值約束的語法形式如下：

[CONSTRAINTconstraint_name]DEFAULTconstraint_expression

其中，constraint_expression為默認值。哈爾濱師范大學恒星學院

使用表設計器建立ForeignKey約束

外鍵(ForeignKey)用于建立和加強兩個表(主表與從表)的一列或多列數據之間的鏈接，當數據添加、修改或刪除時，通過外鍵約束保證它們之間數據的一致性.

定義表之間的參照完整性是先定義主表的主鍵，再對從表定義外鍵約束.ForeignKey約束要求列中的每個值在所引用的表中對應的列中都存在.建立約束哈爾濱師范大學恒星學院在表設計器的空白處單擊鼠標右鍵選擇“關系”，彈出“外鍵關系”對話框.

單擊“添加”按鈕可以添加外鍵，單擊“表和列規(guī)范”右側的按鈕，彈出“表和列”對話框，選擇相應的主鍵表和外鍵表及對應的主鍵和外鍵，單擊“確定”按鈕返回“外鍵關系”對話框，在“標識”的名稱欄中填寫外鍵名稱即可創(chuàng)建外鍵約束.要刪除外鍵，只要在該對話框左側欄中選擇對應的外鍵名稱，單擊“刪除”按鈕即可.建立約束使用T-SQL語句創(chuàng)建FOREIGNKEY約束

創(chuàng)建外鍵約束的語法形式如下：

[CONSTRAINTconstraint_name]{FOREIGNKEY（column_name[,…n]）/*定義外鍵*/REFERENCESreferenced_table_name[(ref_column[,…n])]}[,…n]

參數說明如下。

referenced_table_name是FOREIGNKEY約束引用的表的名稱。

column_name是FOREIGNKEY約束所引用的表中的某列。

---創(chuàng)建表時給出列的約束.

CREATETABLEstudent1

(idchar(7)primarykeyclustered,namechar(10)notnull,sexchar(2)check(sexin('F','M')),birthdaysmalldatetime,birth_placevarchar(50)null,classchar(10))USEpubsCREATETABLEdbo.客戶(客戶編號intprimarykey,客戶名稱char(50)notNull,住址char(50),生日smalldatetime,int,建立日期smalldatetimedefaultGetDate(),constraintchk_phonecheck(like'(010)[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),constraintchk_idcheck(客戶編號between0and10000))例題---下列代碼測試“建立日期”字段是否輸入默認值.USEpubsINSERT客戶(客戶編號,客戶名稱,生日)VALUES(1001,'Candy','1980/04/22')GOSELECT*FROM客戶CREATETABLE產品

(產品編號intprimarykey,

產品名稱varchar(30));CREATETABLE訂單

(訂單編號int,

產品編號int

foreignkeyreferences產品(產品編號),

訂單數量int,

訂單日期datetimedefaultGetdate());

下面的范例使用訂單表與產品表來說明如何用代碼建立”外鍵”.首先,產品表有”主鍵”,才可用訂單表的”產品編號”字段為參考來建立”外鍵”.“外鍵”不一定要參考另一個表的”主鍵”,也可以定義為參考其他表中unique條件約束的列.”外鍵”可包含Null值.同時,復合”外鍵”的任何列包含了Null值,將會略過數據驗證工作.若要確保輸入的數據都符合”外鍵”要求,可對所有參與的列指定非空.

例題在執(zhí)行創(chuàng)建產品信息表的操作時，指定產品編號為主鍵值，并且創(chuàng)建一個聚集索引.

createtableproducts(idchar(10)notnull,namechar(20)notnull,pricemoneydefault0.01,quantitysmallintnullconstraintpk_idprimarykeyclustered(id)

)增加、刪除和修改列

例題在雇員信息表中增加、刪除和修改字段的SQL語句的程序清單如下：

CREATETABLEemployees(idchar(8)primarykey,namechar(20)notnull,departmentchar(20)null,memochar(30)null,ageintnull)ALTERTABLEemployees

ADDsalaryintnull

DROPcolumnageALTERcolumnmemovarchar(200)null

若要修改表的定義并在現有的表中新增列,操作必須在該列允許Null值或包含Default約束的條件下進行.

由于新的列被加入表中,數據庫引擎會針對表中的每條現有數據將值插入該列內.因此,當列被加入表中時,替該列加入Default定義會很有用或者指定該列允許Null值.不滿足這些條件,將返回錯誤信息.如果需要增加非空列呢?增加新列的限制

用戶可以刪除現有表中的列,但具有下列特性的列將無法刪除.---用于索引的列.---用于CHECK、FOREIGNKEY、

UNIQUE或PRIMARYKEY條件約束的列.---與Default定義建立關聯,或關聯至默認對象的列.---關聯至規(guī)則的列.---注冊成全文檢索支持的列.---作為表的全文檢索索引鍵的列.刪除列的限制●查看表格的定義

●查看表格中的數據●查看表格與其它數據庫對象的依賴關系●利用系統(tǒng)存儲過程sp_help查看表的信息例如

sp_help

titles

sp_helpdb

pubs

execsp_helpdb

查看