第5章表的創(chuàng)建與管理及其索引本章導(dǎo)讀：在SQLServer環(huán)境中，表是存儲數(shù)據(jù)和操作數(shù)據(jù)的邏輯結(jié)構(gòu)，是數(shù)據(jù)庫中存儲的基本對象，對數(shù)據(jù)庫的操作大多數(shù)依賴于某個或某些特定的表進(jìn)行的，因而對數(shù)據(jù)庫的管理和操作，實質(zhì)是對表的管理和操作。知識要點：數(shù)據(jù)表概述數(shù)據(jù)類型創(chuàng)建表修改表刪除表數(shù)據(jù)操作索引5.1數(shù)據(jù)庫表概述SQLServer的每個數(shù)據(jù)庫最多提供20億個表，表是數(shù)據(jù)庫中具體組織和存儲數(shù)據(jù)的對象，每個表都代表著一個實體集或者實體集之間的聯(lián)系。表由行和列組成，最多可以有1024列，每行對應(yīng)實體集的一個個體，稱為記錄，每列代表一個屬性，稱為字段。5.1.1數(shù)據(jù)表概述5.1.2建表步驟5.1.3完整性約束類型5.1.1表類型在SQLserver中，表分為系統(tǒng)表和用戶表。1.系統(tǒng)表系統(tǒng)表是SQLServer系統(tǒng)的數(shù)據(jù)字典，記錄了服務(wù)器所有活動信息，一般以sys開頭。任何用戶都不應(yīng)直接修改系統(tǒng)表，也不允許直接訪問表中的信息，如要訪問其內(nèi)容，最好通過系統(tǒng)存儲過程或系統(tǒng)函數(shù)。2.用戶表用戶表是由用戶自行建立的數(shù)據(jù)表，用于存儲用戶數(shù)據(jù)，又分為永久表和臨時表兩種。其中，永久表通常存儲在用戶數(shù)據(jù)庫中，除非刪除永久表，否則永久表及其數(shù)據(jù)將永久存在。臨時表存儲在tempdb數(shù)據(jù)庫中，當(dāng)不再使用時，會被系統(tǒng)自動刪除。臨時表又分為本地臨時表和全局臨時表。本地臨時表名以#開頭，僅對當(dāng)前數(shù)據(jù)庫用戶有效，一旦斷開連接，就自動刪除。全局臨時表名以##開頭，對所有數(shù)據(jù)庫用戶有效，所有用戶斷開連接后才自動刪除。5.1.2建表步驟設(shè)計表時，要事先確定需要什么樣的表，各表有哪些數(shù)據(jù)，表中各字段的數(shù)據(jù)類型及其屬性。建表一般經(jīng)過定義表結(jié)構(gòu)、設(shè)置完整性約束、輸入數(shù)據(jù)記錄等步驟，其中設(shè)置完整性約束既可以在定義表結(jié)構(gòu)時進(jìn)行，也可以在表結(jié)構(gòu)定義完成之后進(jìn)行。（1）定義表結(jié)構(gòu)：確定表名和各列名及列屬性（數(shù)據(jù)類型、數(shù)據(jù)長度、是否允許空等）；（2）設(shè)置完整性約束：限制列數(shù)據(jù)值輸入的范圍，保證數(shù)據(jù)的完整性；（3）輸入數(shù)據(jù)記錄：表結(jié)構(gòu)完成之后，就可以向表中輸入數(shù)據(jù)了。5.1.3完整性約束類型為了維護(hù)數(shù)據(jù)的正確性、有效性和相容性，防止錯誤信息的輸入和輸出，SQLServer提供了實體完整性、參照完整性和用戶自定義完整性等實現(xiàn)關(guān)系數(shù)據(jù)庫的完整性約束。1．實體完整性實體完整性：又稱行完整性，要求表中有一個主鍵，其值不能取空值且能唯一地標(biāo)識每一行。主要包括PrimaryKey約束、Unique約束、列Identity屬性、唯一索引等。（1）主鍵約束（PrimaryKey）：限制主鍵約束列中不能輸入重復(fù)的列值，組成PrimaryKey約束的各列值都不能為空值（Null）。一個表中只允許定義一個主鍵約束，且image和text類型的字段不能指定為主關(guān)鍵字，主鍵約束自動建立主鍵聚集索引。主鍵約束的語法格式如下：[constraint<約束名>]primarykey[clustered|nonclustered][(<列名>[,…16])]（2）惟一約束（Unique）：限制非主鍵約束列中不能輸入重復(fù)的列值，組成Unique約束的各列值可以為空。一個表允許定義多個唯一約束，唯一約束自動建立唯一非聚集索引，因為Unique約束優(yōu)先唯一索引。唯一約束的語法格式如下：[constraint<約束名>]unique[nonclustered|clustered][(<列名>[,…16])]（3）標(biāo)識列（Identity）：標(biāo)識列自動生成能唯一標(biāo)識表中每一行數(shù)據(jù)的序列值（默認(rèn)初始值為1，增量值為1），每個表都允許有一個標(biāo)識列，該列的數(shù)據(jù)類型必須是decimal、numeric、int、smallint或tinyint，且不允許為空值，也不能修改和人為輸入值，也不能有默認(rèn)值。標(biāo)識列的語法格式如下：Identity[(初始值,增量值)]5.1.3完整性約束類型2．參照完整性參照完整性：又稱引用完整性，用于保證兩個相關(guān)表的數(shù)據(jù)一致性，主要通過定義主表（被參照表）PrimaryKey或Unique約束和從表（參照表）ForeignKey約束來實現(xiàn)。（1）外鍵約束（ForeignKey）：根據(jù)主表主鍵的數(shù)據(jù)集合來限制從表外鍵的數(shù)據(jù)相容性，作為外鍵的值要么是空值，要么是主表主鍵存在的值。相應(yīng)地，包含外鍵的表也稱為外鍵表，主表也稱主鍵表或引用表，常用外鍵和主鍵來強(qiáng)制參照完整性，以維護(hù)兩個表之間的關(guān)系。外鍵約束的語法格式如下：[constraint<約束名>]foreignkey[(<列名>[,…16])]references<主表名>(<列名>[,…16])當(dāng)創(chuàng)建外鍵約束時，必須遵循以下原則：主表中（引用）列名必須是主表中的候選鍵（通常為主鍵）。外鍵約束分為表級約束或列級約束。創(chuàng)建為列級約束時，只能包括一列；創(chuàng)建為表級約束時，可以包括一列或多列。從表外鍵的列數(shù)和主表主鍵的列數(shù)必須相同，并且對應(yīng)列的數(shù)據(jù)類型也必須相同，但是外鍵（被引用）列名與主鍵（引用）列名不必相同。創(chuàng)建外鍵約束時，如果沒有指定外鍵（被引用）列名，那么默認(rèn)為外鍵（被引用）列名與主鍵定義的（引用）列名同名。5.1.3完整性約束類型3．用戶自定義的完整性在SQLServer2000中，用戶自定義的完整性是指域完整性（也稱列完整性），用于保證列數(shù)據(jù)輸入的有效性和合理性。其主要包括Default約束、Check約束、NotNull約束等。（1）默認(rèn)值約束（Default）：當(dāng)輸入數(shù)據(jù)時若沒有為某列提供值，則將所定義的默認(rèn)值提供給該列。默認(rèn)值可以是常量，也可以是表達(dá)式，如getdate()返回系統(tǒng)日期。默認(rèn)約束的語法格式如下：[constraint<約束名>]default<默認(rèn)值>[for<列名>]（2）檢查約束（Check）：通過限制列的取值范圍來強(qiáng)制域的完整性，這與外鍵約束中的數(shù)據(jù)相容性規(guī)則相似，不過外鍵約束是依據(jù)主表主鍵的數(shù)據(jù)集合，而檢查約束則是利用邏輯表達(dá)式來限制列上可接受的數(shù)據(jù)范圍，而非基于其它表的數(shù)據(jù)集合。不能在text、ntext、image列上定義check約束。檢查約束的語法格式如下：[constraint<約束名>]check(<列名條件表達(dá)式>)（3）非空值約束（NotNull）：限制字段不接受Null值，即當(dāng)對表進(jìn)行插入（Insert）操作時，必須給出確定的值?？罩凳侵肝刺顚?、未知、不可用或?qū)⒃谝院筇砑拥臄?shù)據(jù)，并不等價于空白（空字符串）或數(shù)值0。列默認(rèn)屬性為空（Null）。非空值約束的語法格式如下：<列名>NotNull5.2數(shù)據(jù)類型數(shù)據(jù)類型用來表現(xiàn)數(shù)據(jù)的特征，它決定了數(shù)據(jù)存儲格式、存儲長度、取值范圍（含數(shù)據(jù)精度和小數(shù)位數(shù)），以及可參與的運(yùn)算法則。在SQLServer系統(tǒng)中，數(shù)據(jù)類型分為系統(tǒng)數(shù)據(jù)類型和用戶自定義數(shù)據(jù)類型。5.2.1系統(tǒng)數(shù)據(jù)類型5.2.2用戶自定義數(shù)據(jù)類型5.2.1系統(tǒng)數(shù)據(jù)類型在SQLServer2000中，系統(tǒng)數(shù)據(jù)類型包括字符類型、數(shù)值類型、二進(jìn)制類型、貨幣類型和日期/時間數(shù)據(jù)類型等。而數(shù)值類型又分為整數(shù)類型、精確小數(shù)類型和

浮點類型。1．字符串?dāng)?shù)據(jù)類型字符串?dāng)?shù)據(jù)類型是用單引號定界的字符串，包括char、varchar和text數(shù)據(jù)類型，如表5-1所示。char字符串若小于定義長度，則填入空格字符，其長度不變。數(shù)據(jù)類型長度取值范圍char(n)長度不變，最多8000個字符固定長度的非unicode字符varchar(n)長度可變，最多8000個字符可變長度的非unicode字符text長度可變，最多2147483647個字符可變長度的非unicode字符表5-1字符串?dāng)?shù)據(jù)類型5.2.1系統(tǒng)數(shù)據(jù)類型2．Unicode字符串?dāng)?shù)據(jù)類型Unicode字符串?dāng)?shù)據(jù)類型采用雙字節(jié)存儲每個字符。Unicode數(shù)據(jù)類型包括nchar，nvarchar和ntext類型，如表5-2所示。nchar字符串小于定義長度時，以0x00填入。表5-2Unicode字符串?dāng)?shù)據(jù)類型數(shù)據(jù)類型長度取值范圍nchar(n)長度不變，最多8000個字符固定長度的unicode字符nvarchar(n)長度可變，最多8000個字符可變長度的unicode字符ntext長度可變，最多107341823個字符可變長度的unicode字符5.2.1系統(tǒng)數(shù)據(jù)類型3．整型數(shù)據(jù)類型整型數(shù)據(jù)類型是指不含小數(shù)的數(shù)值數(shù)據(jù)，包括bit，tinyint，smallint，int，bigint類型，他們之間的區(qū)別主要在于存儲的數(shù)值范圍不同，如表5-3所示。表5-3整型數(shù)據(jù)類型數(shù)據(jù)類型長度取值范圍bit1位1或0tinyint1字節(jié)0~255smallint2字節(jié)-32768~32767（-215~215-1）int4字節(jié)-2147483648~2147483647（-231~231-1）bigint8字節(jié)-9223372036854775808~9223372036854775807（-263~263-1）5.2.1系統(tǒng)數(shù)據(jù)類型4．浮點數(shù)據(jù)類型浮點數(shù)據(jù)類型是一種近似小數(shù)的數(shù)值數(shù)據(jù)，通常采用科學(xué)計數(shù)法近似存儲十進(jìn)制小數(shù)，包括real和float類型，如表5-6所示。表5-4浮點數(shù)據(jù)類型數(shù)據(jù)類型長度取值范圍float8字節(jié)-1.79E+308~-1.79E+308real4字節(jié)-3.40E+38~-3.40E+385.2.1系統(tǒng)數(shù)據(jù)類型5．精確小數(shù)數(shù)據(jù)類型精確小數(shù)類型是指包含小數(shù)的位數(shù)確定的數(shù)值數(shù)據(jù)，包括decimal，numeric類型，如表5-5所示。數(shù)據(jù)類型長度取值范圍數(shù)據(jù)類型長度取值范圍數(shù)據(jù)類型長度取值范圍Decimal(p,s)精度1~9位時，占5字節(jié)精度10~19位時，占9字節(jié)精度20~28位時，占13字節(jié)精度29~38位時，占17字節(jié)-238+1~238-1p（精度）表示小數(shù)點兩邊的總位數(shù)s（刻度）表示小數(shù)點右邊的位數(shù)1≤p≤38，0≤s≤pnumeric(p,s)同decimal-238+1~238-1同decimal-238+1~238-1表5-5精確小數(shù)數(shù)據(jù)類型5.2.1系統(tǒng)數(shù)據(jù)類型6．二進(jìn)制數(shù)據(jù)類二進(jìn)制數(shù)據(jù)類型是指用16進(jìn)制（0x開頭）表示的數(shù)據(jù)，包括binary，varbinary和image類型，如表5-4所示。數(shù)據(jù)類型長度取值范圍binary(n)n+4，長度不變，最多8000個字節(jié)固定長度的二進(jìn)制數(shù)據(jù)varbinary(n)實際長度+4，長度可變，最多8000個字符可定長度的二進(jìn)制數(shù)據(jù)image長度可變，最多107341823個字符可定長度的二進(jìn)制數(shù)據(jù)表5-6二進(jìn)制數(shù)據(jù)類型一般說來，最好使用binary和varbinary存儲二進(jìn)制數(shù)據(jù)，除非存儲字節(jié)數(shù)超過了8K，才使用image類型。二進(jìn)制數(shù)據(jù)指Word文檔、Excel電子表格、圖像等文件。5.2.1系統(tǒng)數(shù)據(jù)類型7．貨幣數(shù)據(jù)類型貨幣數(shù)據(jù)類型是用于表示貨幣和現(xiàn)金值的數(shù)值數(shù)據(jù)，精確到小數(shù)點后4位。貨幣數(shù)據(jù)類型包括money，smallmoney類型，如表5-7所示。表5-7貨幣數(shù)據(jù)類型數(shù)據(jù)類型長度取值范圍money8字節(jié)-922337203685477.5808~922337203685477.5807smallmoney4字節(jié)-214748.3648~214748.36475.2.1系統(tǒng)數(shù)據(jù)類型8．日期和時間數(shù)據(jù)類型日期和時間數(shù)據(jù)類型是指表示日期和時間的數(shù)據(jù)類型。日期和時間數(shù)據(jù)類型包括datetime，smalldatetime類型，如表5-8所示。數(shù)據(jù)類型長度取值范圍datetime8字節(jié)（精確到百分之三秒）1753-1-100:00:00~9999-12-3123:59:59smalldatetime4字節(jié)（精確到分鐘）1900-1-100:00:00~2079-6-623:59:59表5-8日期和時間數(shù)據(jù)類型注意：在SQLServer中，word文檔、excel電子表格以及.bmp、.gif、.jpg等圖像格式的文件用text、ntext和image數(shù)據(jù)類型來存儲。5.2.1系統(tǒng)數(shù)據(jù)類型9．其它數(shù)據(jù)類型其它數(shù)據(jù)類型用于表示一些特殊的數(shù)據(jù)，包括cursor，sql_variant、table、timestamp和uniqueidentifier類型，如表5-9所示。數(shù)據(jù)類型長度取值范圍cursor長度不變，最多8000個字節(jié)保存查詢結(jié)果集sql_variant長度可變，最多8000個字符存儲非text，ntext，image，timestamp數(shù)據(jù)table長度可變，最多107341823個字符存儲對表和視圖處理后的結(jié)果集timestamp8字節(jié)時間戳數(shù)據(jù)類型，產(chǎn)生的唯一數(shù)據(jù)類型uniqueidentifier16字節(jié)存儲計算機(jī)網(wǎng)絡(luò)和cpu全球唯一標(biāo)識的數(shù)據(jù)類型表5-9其它數(shù)據(jù)類型5.2.2用戶自定義數(shù)據(jù)類型SQLserver提供了兩種創(chuàng)建用戶自定義數(shù)據(jù)類型的方法：使用企業(yè)管理器創(chuàng)建用戶自定義數(shù)據(jù)類型和使用系統(tǒng)存儲過程創(chuàng)建用戶自定義數(shù)據(jù)類型。1.使用企業(yè)管理器創(chuàng)建用戶自定義數(shù)據(jù)類型【例5-1】創(chuàng)建用戶自定義數(shù)據(jù)類型“zipcode”，數(shù)據(jù)類型來源char(6)，且允許空值。（1）在企業(yè)管理器中，展開要創(chuàng)建用戶自定義數(shù)據(jù)類型的數(shù)據(jù)庫（jxgl），右擊“用戶自定義的數(shù)據(jù)類型”節(jié)點，彈出快捷菜單，選擇“新建用戶定義數(shù)據(jù)類型”命令，如圖5-1所示。（2）單擊，彈出“用戶定義數(shù)據(jù)類型屬性”對話框，在其中輸入數(shù)據(jù)類型的名稱“zipcode”，數(shù)據(jù)類型和長度char(6)，是否允許空值（允許），規(guī)則和默認(rèn)值等，如圖5-2所示。5.2.2用戶自定義數(shù)據(jù)類型圖5-1“企業(yè)管理”對話框① 圖5-2“用戶定義數(shù)據(jù)類型屬性”對話框5.2.2用戶自定義數(shù)據(jù)類型（3）單擊“確定”按鈕返回企業(yè)管理，如圖5-3所示，在此窗口中，選擇剛創(chuàng)建的用戶自定義數(shù)據(jù)類型“zipcode”可以將其刪除。

圖5-3“企業(yè)管理”對話框② 圖5-4“例5-2”運(yùn)行結(jié)果5.2.2用戶自定義數(shù)據(jù)類型2．使用系統(tǒng)存儲過程創(chuàng)建用戶自定義數(shù)據(jù)類型（1）使用系統(tǒng)存儲過程創(chuàng)建用戶自定義數(shù)據(jù)類型的語法格式如下：sp_addtype用戶自定義數(shù)據(jù)類型名稱,系統(tǒng)數(shù)據(jù)類型[,’null’|’notnull’]（2）使用系統(tǒng)存儲過程刪除用戶自定義數(shù)據(jù)類型的語法格式如下：sp_droptype用戶自定義數(shù)據(jù)類型名稱【例5-2】在“jxgl”數(shù)據(jù)庫中創(chuàng)建一個用戶自定義數(shù)據(jù)類型名稱為“birthday”，數(shù)據(jù)類型來源于系統(tǒng)數(shù)據(jù)類型“smalldatetime”，不允許為空值。usejxglexecsp_addtype'birthday','smalldatetime','notnull'運(yùn)行結(jié)果如圖5-4所示。圖5-4“例5-2”運(yùn)行結(jié)果5.3創(chuàng)建數(shù)據(jù)庫表SQLserver提供了兩種創(chuàng)建表的方法：使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表和使用T-SQL語句設(shè)計數(shù)據(jù)表。5.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)5.3.2使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表5.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)數(shù)據(jù)庫“jxgl”中存在如下幾個表，各表的邏輯結(jié)構(gòu)分別定義如下：表5-10班級信息表班級字段名數(shù)據(jù)類型備注班級號char(6)主鍵班級名稱varchar(20)唯一約束班級人數(shù)tinyint學(xué)制char(1)默認(rèn)值為4招生性質(zhì)char(4)5.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)表5-11選修信息表選修 字段名數(shù)據(jù)類型備注成績編碼int標(biāo)識，主鍵學(xué)號char(8)非空、外鍵課程號char(2)非空、外鍵成績numeric(4,1)100≥成績≥0備注text5.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)表5-12學(xué)生信息表學(xué)生 字段名數(shù)據(jù)類型備注學(xué)號char(8)非空、主鍵姓名char(6)非空性別char(2)默認(rèn)值男出生日期datetime總分int籍貫char(4)默認(rèn)值安徽備注text照片image5.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)表5-13教師信息表教師字段名數(shù)據(jù)類型備注工號char(6)非空、主鍵姓名char(6)非空、性別char(2)默認(rèn)值男出生日期datetime工作日期datetime職稱char(6)基本工資int婚否bit默認(rèn)值05.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)表5-14課程信息表課程 字段名數(shù)據(jù)類型備注課程號char(2)主鍵課程名稱varchar(20)唯一約束課程類型char(4)學(xué)時smallint學(xué)時≥0學(xué)分tinyint學(xué)分≥0備注text5.3.1數(shù)據(jù)庫表邏輯結(jié)構(gòu)字段名數(shù)據(jù)類型備注工號char(6)非空、外鍵課程號char(2)非空、外鍵班級號char(6)非空、外鍵課酬int3000≥課酬≥0學(xué)期char(1)評價text表5-15授課信息表授課注意：本書程序如果特別說明均以以上表內(nèi)容作為基本表結(jié)構(gòu)，表中數(shù)據(jù)見第6章。5.3.2使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表【例5-3】用企業(yè)管理器設(shè)計表“班級”，并設(shè)置班級號為主鍵，設(shè)置學(xué)制默認(rèn)值為4。操作步驟如下：（1）在企業(yè)管理器中選擇并展開要創(chuàng)建表的數(shù)據(jù)庫（jxgl），選擇“表”節(jié)點，右擊之彈出快捷菜單，選擇“新建表”，如圖5-5所示。（2）單擊后釋放，彈出“新建表”對話框，并在“新建表”對話框中設(shè)置各列的屬性：列名、數(shù)據(jù)類型、長度、精度、小數(shù)位數(shù)、是否允許空、默認(rèn)值等，如圖5-6所示。5.3.2使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表

圖5-5“企業(yè)管理”對話框 圖5-6“新建表”對話框5.3.2使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表（3）在編輯完各列屬性后，單擊“保存”按鈕，彈出“選擇名稱”對話框，輸入“班級”名稱，如圖5-7所示。（4）單擊“確定”按鈕，返回“新建表”對話框，單擊該對話框右上角的關(guān)閉按鈕，返回“企業(yè)管理器”對話框，拖動右框格垂直滾動條，可看到“班級”表，如圖5-8所示。

圖5-7“選擇名稱”對話框 圖5-8“企業(yè)管理器”對話框5.3.2使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表注意：①在“表設(shè)計器”對話框中，選中列名“班級號”后，在該對話框中任意位置右擊，彈出快捷菜單，選擇“設(shè)置主鍵”即可將“班級號”設(shè)置為主鍵?！鞍嗉壧枴弊髠?cè)有一把鑰匙標(biāo)記即表示已經(jīng)設(shè)置為主鍵。②在“新建表”對話框中，右擊列名如“班級號”所在行的任何位置，彈出快捷菜單，選擇“插入列”可以在當(dāng)前列前位置增加列；選擇“刪除列”可以刪除當(dāng)前列；另外列的上下位置也可以調(diào)整：選擇要移動的列，然后拖動該列到要釋放的位置。5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表在T-SQL語句中，創(chuàng)建表可用createtable語句創(chuàng)建表，其完整語法格式如下：createtable[數(shù)據(jù)庫名.[所有者].|所有者.]<表名>({<列定義說明>|[<列名>as<計算機(jī)列表達(dá)式>]|[<表約束說明>]}|[{primarykey|unique}][,…n])[on{文件組名|default}] /*指定存儲表的文件組*/[textimage_on{文件組名|default}] /*指定存儲text、ntext和image類型數(shù)據(jù)的文件組*/功能：在指定的數(shù)據(jù)庫的指定用戶上創(chuàng)建一個表，缺省值為當(dāng)前數(shù)據(jù)庫的現(xiàn)有用戶。5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表（1）<列定義說明>：用來定義一列，列的定義如下：<列定義說明>::={列名列數(shù)據(jù)類型} /*指定列名、數(shù)據(jù)類型*/|[notnull|null] /*指定列空值非空值約束*/[collate<排序規(guī)則名>] /*指定排序規(guī)則*/[[default常量表達(dá)式] /*指定默認(rèn)值*/|[identity[(初始值,增量)[notforreplication]]]/*指定列為標(biāo)識列*/][rowguidcol] /*指定列為全局標(biāo)識符列*/[<列約束說明>][,…n] /*指定列的約束*/5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表（4）<表約束說明>：定義表約束，約束格式如下：<表約束說明>::=[constraint<約束名>]{[{primarykey|unique} /*定義主鍵，唯一性約束*/{(列名[asc|desc][,…16])} /*定義索引升序或降序的列名*/[clustered|nonclustered] /*定義聚集索引，非聚集索引*/[withfillfactor=fillfactor] /*唯一性約束為非聚集*/[on{文件組|default}]]|[foreignkey[(列名[,…16])] /*定義外鍵約束*/references參照表名[(參照列名[,…n])] /*外鍵約束引用的表及其列*/[ondelete{cascade|onaction}] /*刪除行是否級聯(lián)刪除子表相關(guān)行*/[onupdate{cascade|onaction}] /*更新行是否級聯(lián)更新子表相關(guān)行*/[notforreplication]]|check(邏輯表達(dá)式)[notforreplication]}5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表（5）<列約束說明>：定義列約束說明，約束格式如下：<列約束說明>：：=[constraint約束名]{[null|notnull]|[{primarykey|unique}[clustered|nonclustered][with

fillfactor=fillfactor][on{文件組名|default}]]|[foreignkeyrefrences

參照表名[(參照列名)][ondelete{cascade|onaction}][onupdate{cascade|onaction}][notforreplication]]|check(邏輯表達(dá)式)[notforreplication]}（6）on{文件組名|default}：用來指定存儲表的文件組。沒有指定on參數(shù)值或缺省時均為默認(rèn)文件組。（7）textimage_on{文件組名|default}：用來指定存儲text、ntext和image類型數(shù)據(jù)的文件組，沒有指定textimage_on參數(shù)值時或缺省時均與表存儲在同一文件組中。5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表【例5-4】用T-SQL語句設(shè)計表“授課”，但不設(shè)置其相關(guān)約束。usejxglcreatetable授課(工號char(6),課程號char(6),班級號char(6),課酬int,學(xué)期char(1))【例5-5】用T-SQL語句設(shè)計表“教師”，僅設(shè)置列“工號”為主鍵，不設(shè)置其它約束。usejxglcreatetable教師(工號char(6)constraintPk_教師_工號primarykey,姓名char(6),性別char(2),出生日期datetime,工作日期datetime,職稱char(6),基本工資int,婚否bit)注意：代碼中的第三行中的子句constraintPk_教師_工號可以省略時，省略時會產(chǎn)生一個隨機(jī)分配的約束標(biāo)志名。5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表【例5-6】用T-SQL語句設(shè)計表“學(xué)生”，僅設(shè)置學(xué)號為非空、主鍵，姓名非空，性別默認(rèn)值為“男”，但不設(shè)置籍貫的默認(rèn)約束。usejxglcreatetable學(xué)生(學(xué)號char(8)notnullconstraintpk_學(xué)生primarykey,姓名char(6)notnull,性別char(2)constraintdf_學(xué)生_性別default'男',出生日期datetime,總分int,籍貫char(4),備注text,照片image)5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表在查詢分析器左側(cè)的“對象瀏覽器”窗口中展開數(shù)據(jù)庫“jxgl”用戶表，可以查看“學(xué)生”表的信息，如圖5-9所示。當(dāng)然在企業(yè)管理器中也能查看該表相關(guān)屬性。

圖5-9“學(xué)生”表信息 圖5-10“選修”表信息5.3.3使用T-SQL語句設(shè)計數(shù)據(jù)表【例5-7】用T-SQL語句設(shè)計表“選修”，并設(shè)置成績編碼為標(biāo)識列，學(xué)號為非空、外鍵約束于“學(xué)生”表“學(xué)號”列，學(xué)號和課程號共同建立主鍵，成績檢查約束0~100之間。usejxglcreatetable選修(成績編碼intidentity(1,1),學(xué)號char(8)notnullconstraintfk_學(xué)號references學(xué)生(學(xué)號),課程號char(2)notnull,constraintpk_選修primarykey(學(xué)號,課程號),成績tinyintcheck(成績>=0and成績<=100),備注text)在查詢分析器左側(cè)的“對象瀏覽器”窗口中展開數(shù)據(jù)庫“jxgl”用戶表，可以查看“選修”表的信息，如圖5-10所示。當(dāng)然在企業(yè)管理器中也能查看該表相關(guān)屬性。5.4修改數(shù)據(jù)庫表修改表不僅能修改表的結(jié)構(gòu)，如增加和刪除列，修改現(xiàn)有列的屬性，還能增加、刪除、啟動和暫停約束。但是修改表時，不能破壞表原有的數(shù)據(jù)完整性，如不能為有主關(guān)鍵字列的表再增加一個主關(guān)鍵字列，不能為有空值的列設(shè)置主鍵約束等。修改表，SQLServer同樣提供了兩種的方法：即使用企業(yè)管理器設(shè)計數(shù)據(jù)庫表和使用T-SQL語句修改數(shù)據(jù)表。5.4.1利用企業(yè)管理修改表5.4.2利用T-SQL語句修改表5.4.1利用企業(yè)管理修改表在企業(yè)管理器中展開控制臺目錄，選擇要修改的表，如表“班級”，打開“設(shè)計表”對話框，即可修改表的結(jié)構(gòu)和列的屬性等，使用方法參照創(chuàng)建表的方法?！纠?-8】用企業(yè)管理修改表“授課”，將其列“班級號”設(shè)置為外鍵，對應(yīng)關(guān)聯(lián)于表“班級”的列“班級號”。（1）打開表“授課”的“設(shè)計表”對話框，選擇列“班級號”，右擊彈出快捷菜單，選擇“關(guān)系”命令，如圖5-11所示。（2）單擊釋放后，彈出“屬性”對話框的“關(guān)系”界面①，如圖5-12所示。

圖5-11“設(shè)計表”對話框 圖5-12“屬性”對話框的“關(guān)系”界面①5.4.1利用企業(yè)管理修改表（1）單擊“新建”按鈕，彈出“屬性”對話框的“關(guān)系”界面②，如圖5-13所示。（2）分別選擇主鍵表“班級”和外鍵表“授課”的列“班級號”，如圖5-14所示。

圖5-13“屬性”對話框的“關(guān)系”界面② 圖5-14“屬性”對話框的“關(guān)系”界面③5.4.1利用企業(yè)管理修改表注意：①如果勾選“創(chuàng)建中檢查現(xiàn)有數(shù)據(jù)”復(fù)選框，則表示檢查兩表之間現(xiàn)有數(shù)據(jù)是否符合參照完整性，如果符合參照完整性就準(zhǔn)許建立關(guān)系，否則不允許建立；②如果勾選“對復(fù)制強(qiáng)制關(guān)系”復(fù)選框，則表示復(fù)制兩表時也要遵循參照完整性；③如果勾選“對insert和update強(qiáng)制關(guān)系”，則表示插入或更新時要符合參照完整性，否則拒絕插入或更新操作；④在③基礎(chǔ)上，如果繼續(xù)勾選“級聯(lián)更新相關(guān)的字段”，則表示更新主鍵表的鍵值時，自動更新外鍵表的關(guān)聯(lián)列值；⑤在③基礎(chǔ)上，如果繼續(xù)勾選“級聯(lián)刪除相關(guān)的記錄”，則表示刪除主鍵表的記錄時，自動刪除外鍵表的關(guān)聯(lián)記錄。5.4.1利用企業(yè)管理修改表（5）單擊“關(guān)閉”按鈕，返回“設(shè)計表”對話框，單擊“設(shè)計表”右上角的“關(guān)閉”按鈕，彈出保存提示對話框，如圖5-15所示。（6）單擊“是”按鈕，彈出保存方式對話框，如圖5-16所示，單擊“是”按鈕即可。

圖5-15“保存”提示對話框 圖5-16“保存”對話框5.4.1利用企業(yè)管理修改表注意：（1）如果要設(shè)置列“課酬”的check約束，參照步驟（1）打開“設(shè)計表”對話框，選中列“課酬”，右擊彈出快捷菜單，單擊“check約束”命令，彈出“屬性”對話框的“check約束”界面①，如圖5-17所示。

圖5-17“屬性”對話框的“check約束”界面①5.4.1利用企業(yè)管理修改表（2）單擊“新建”按鈕，彈出“屬性”對話框的“check約束”界面②，在約束表達(dá)式文本框中輸入“課酬>=0and課酬<=3000”，如圖5-18所示。圖5-18“屬性”對話框的“check約束”界面②5.4.1利用企業(yè)管理修改表（3）后續(xù)步驟重復(fù)（5）~（7）。在查詢分析器的“對象瀏覽器中”可以查看相關(guān)信息。注意：右擊表名，如“班級”表，彈出快捷菜單，選擇“重命名”命令還可以更改表名。5.4.2利用T-SQL語句修改表可以使用T-SQL語句altertable來修改表結(jié)構(gòu)，包括添加列及其約束、添加約束、刪除列、刪除約束、修改列屬性等操作。其語法格式如下：altertable<表名>{[[with{check|nocheck}]add{<列定義>|<列名>as<計算列表達(dá)式>}[,…n]]|[[with{check|nocheck}]add{<列約束>[,…n]}]|[drop{[constraint]<約束名>}[,…n]]|[drop{column<列名>}[,…n]]|[altercolumn<列名>{新數(shù)據(jù)類型[(小數(shù)精度[,小數(shù)范圍])][collate<排序規(guī)則>][null|notnull]|{add|drop}rowguidcol}]|[{check|nocheck}constraint{all|約束名[,…n]}]|[{enable|disable}trigger{all|觸發(fā)器名[,..n]}]5.4.2利用T-SQL語句修改表說明：（1）add{<列定義>|<列名>as<計算列定義>}[,…n]：添加一個新列或計算列表達(dá)式，其中with{check|nocheck}表示是否檢查表中現(xiàn)有數(shù)據(jù)的約束規(guī)則；（2）add{<列約束>[,…n]}：添加列約束；（3）drop{[constraint]<約束名>}[,…n]：刪除指定的約束；（4）drop{column<列名>}[,…n]：刪除指定的列名；（5）altercolumn<列名>{新數(shù)據(jù)類型[(小數(shù)精度[,小數(shù)范圍])]}：更改指定列的數(shù)據(jù)類型，小數(shù)精度，小數(shù)范圍，其中collate<排序規(guī)則>用于重新指定列的排序規(guī)則，{add|drop}rowguidcol用于添加或刪除列上rowguidcol屬性；（6）{check|nocheck}constraint{all|約束名[,…n]}：啟用或禁止外鍵或check約束；（7）{enable|disable}trigger{all|觸發(fā)器名[,..n]}：啟用或禁止指定觸發(fā)器。完整的T-SQL看起來比較復(fù)雜，不容易理解，但正如前文所說，修改表也就是對表結(jié)構(gòu)的增、刪、改操作，所以為了講解方便，可以將修改表的T-SQL語句簡化成以下幾種形式：5.4.2利用T-SQL語句修改表將修改表的T-SQL語句可以簡化成以下幾種形式：1．增加列增加列同時還可以附加列約束定義信息，使用altertable命令語句添加列的基本語法格式如下：altertable<表名>add<列名><數(shù)據(jù)類型和長度>[列約束]【例5-9】向“班級”表中添加一列，列名為“班主任”并具有唯一性約束。usejxglaltertable班級add班主任varchar(6)constraintUk_班級_班主任unique【例5-10】向“選修”表中添加一列，列名為“等級”，數(shù)據(jù)類型為varchar(6)，默認(rèn)約束是“合格”。usejxglaltertable選修add等級varchar(6)constraintdf_選修_等級default'合格'【例5-11】為“學(xué)生”表中增加一列，列名為“郵政編碼”，并附加一個check約束，限制輸入到列的數(shù)據(jù)范圍為6位0~9之間數(shù)字且以“23”開頭。usejxglaltertable學(xué)生add郵政編碼char(6)constraintCk_學(xué)生_郵政編碼check(郵政編碼like'23[0-9][0-9][0-9]')5.4.2利用T-SQL語句修改表2．增加約束增加約束時，如果新增約束與表中原有數(shù)據(jù)有沖突時，將導(dǎo)致異常，終止命令執(zhí)行。如果想忽略新增約束對原有數(shù)據(jù)的檢查，可以使用使用withnocheck選項，使新增約束只對以后數(shù)據(jù)起作用。使用altertable命令語句添加列約束的基本語法格式如下：altertable<表名>[withnocheck|check]addconstraint<約束名><約束定義>【例5-12】為“教師”表中的列名為“性別”的列設(shè)置默認(rèn)約束，默認(rèn)值是“男”。usejxglaltertable教師

addconstraintdf_教師_性別default'男'for性別【例5-13】為“班級”表中的列名為“班級人數(shù)”的列添加一個check約束，但不檢查表中現(xiàn)有數(shù)據(jù)，限制輸入到列的數(shù)據(jù)范圍為40~60之間。usejxglaltertable班級withnocheckaddconstraintCk_班級_班級人數(shù)check(班級人數(shù)>=40and班級人數(shù)<=60)【例5-14】為“選修”表中列名為“課程號”的列添加一個“外鍵約束”于課程表。usejxglaltertable選修

addconstraintFk_選修_課程號foreignkey(課程號)references課程(課程號)5.4.2利用T-SQL語句修改表3．修改列使用altertable命令修改列時，只能修改列的數(shù)據(jù)類型及其列值是否為空等屬性，使用altertable命令語句修改表中列定義的基本語法格式如下：altertable<表名>altercolumn<列名><新數(shù)據(jù)類型和長度>[(<精度>[,<小數(shù)位數(shù)>])]【例5-15】將“班級”表中列名為“班級名稱”的列的數(shù)據(jù)類型修改為char(20)。usejxglaltertable班級

altercolumn班級名稱char(20)null注意：如將一個原來允許為空的列修改為不允許為空（notnull），必須確保該列中沒有存放空值且該列上沒有建立索引。5.4.2利用T-SQL語句修改表4．刪除約束使用altertable命令語句刪除列約束定義的基本語法格式如下：altertable<表名>dropconstraint<約束名>【例5-16】將“選修”表中主鍵約束（pk_選修）刪除。usejxglaltertable選修

dropconstraintpk_選修【例5-17】將“選修”表中默認(rèn)值約束（df_選修_等級）刪除。usejxglaltertable選修dropconstraintdf_選修_等級5.4.2利用T-SQL語句修改表5．刪除列有約束的列或者與其它列有關(guān)聯(lián)的列不能直接刪除，刪除列之前必須先刪除約束或列關(guān)聯(lián)信息。使用altertable命令語句刪除列定義的基本語法格式如下：altertable<表名>dropcolumn<列名>[,…n]【例5-18】將“選修”表中列名為“等級”的列刪除。usejxglaltertable選修dropcolumn等級【例5-19】將“學(xué)生”表中的列“郵政編碼”刪除（“郵政編碼”的約束沒有刪除）。usejxglaltertable學(xué)生dropcolumn郵政編碼注意：列的附加約束沒有刪除之前，無法刪除該列。5.4.2利用T-SQL語句修改表6．啟用和暫停約束使用check和nocheck選項可以啟動或暫停SQLServer的某個或全部約束對新數(shù)據(jù)的約束檢查，但不適用主鍵約束和唯一約束?！纠?-20】暫?！斑x修”表中的外鍵約束（Fk_選修_課程號）。usejxglaltertable選修nocheckconstraintFk_選修_課程號【例5-21】暫?！皩W(xué)生”表中的check約束和默認(rèn)約束。usejxglgoaltertable學(xué)生nocheckconstraintall注意：使用系統(tǒng)存儲過程sp_rename也可以修改表名或表列名，或使用帶參數(shù)’column’的系統(tǒng)存儲過程sp_rename修改列名?！纠?-22】使用系統(tǒng)存儲過程修改表名“學(xué)生”為“student”。usejxglexecsp_rename

學(xué)生,student【例5-23】使用系統(tǒng)存儲過程將學(xué)生的“姓名”列修改為“學(xué)生姓名”。usejxglgoexecsp_rename'學(xué)生.姓名','學(xué)生姓名','column'5.5刪除數(shù)據(jù)表5.5.1使用企業(yè)管理刪除數(shù)據(jù)表5.5.2使用T-SQL語句刪除數(shù)據(jù)表5.5.1使用企業(yè)管理刪除數(shù)據(jù)表在企業(yè)管理器中展開控制臺目錄，直到數(shù)據(jù)庫“jxgl”的“表”節(jié)點，然后選擇要刪除的表，如表“班級”，如圖5-19所示。然后執(zhí)行“操作”→“刪除”命令后，彈出“除去對象”對話框，如圖5-20所示，單擊“全部除去”按鈕，就可以刪除表。

圖5-19選擇“班級”表對話框 圖5-20“除去對象”對話框5.5.2使用T-SQL語句刪除數(shù)據(jù)表也可以使用T-SQL刪除數(shù)據(jù)表，其語法格式如下：格式：droptable<表名>【例5-24】刪除“班級”表。usejxgldroptable班級5.6索引在SQLServer系統(tǒng)中，影響數(shù)據(jù)庫性能的因素很多，其中，設(shè)計良好的索引可以顯著提高數(shù)據(jù)庫的使用性能。索引的作用類似于書的目錄，使得用戶對數(shù)據(jù)庫的查詢無需逐行掃描，而是通過遍歷索引樹結(jié)構(gòu)的方法查找所需行的存儲位置，并通過查詢結(jié)果提取所需的數(shù)據(jù)行。5.6.1索引概述5.6.2索引建立索引的優(yōu)勢是加快訪問速度和實施行的唯一性。但并非索引越多越好，這是因為當(dāng)頻繁地增加、刪除和更新數(shù)據(jù)時，除了對表中數(shù)據(jù)進(jìn)行處理外，還會占用額外開銷來維護(hù)索引而引起磁盤存儲空間不足。通常情況下，只有數(shù)據(jù)量大、更新較少而查詢較多的數(shù)據(jù)列，才需要在表上創(chuàng)建該列的索引。

1．索引的概念

索引是對數(shù)據(jù)表中的一列或多列的值進(jìn)行排序而創(chuàng)建的一種結(jié)構(gòu)分散的數(shù)據(jù)庫對象。索引中包含索引列的鍵值（搜索碼），以及映射到相應(yīng)數(shù)據(jù)行的指針。索引依據(jù)用戶指定順序（升序或降序）對鍵值進(jìn)行排序，從而使程序無需瀏覽整個表，就可以快速找到所需的數(shù)據(jù)。索引提供了邏輯順序，可以提高數(shù)據(jù)的查詢響應(yīng)速度，另外索引也能加快連接和select語句中g(shù)roupby與orderby子句的檢索速度。5.6.1索引概述2．索引的類型在SQLServer中，根據(jù)索引的存儲結(jié)構(gòu)來分，可以將索引分為聚集索引和非聚集索引兩大類；根據(jù)索引的列值是否允許重復(fù)值的性質(zhì)來分，可以將索引分為主鍵索引、唯一性索引和普通索引三大類；根據(jù)索引的引用列數(shù)多少來分，可以將索引分為單列索引和復(fù)合列索引。5.6.2索引建立創(chuàng)建索引的過程實質(zhì)就是確立索引名、索引類型及其針對的表名及其列名。創(chuàng)建索引既可以使用企業(yè)管理器，也可以使用Transact-SQL語句。1．使用企業(yè)管理器創(chuàng)建索引利用企業(yè)管理器建立索引有多種方法，既可以使用表設(shè)計器建立索引，也可以使用工具向?qū)Ы⑺饕€可以使用索引管理器建立索引。這里我們介紹最常用的方法，即利用表設(shè)計器建立索引。5.6.2索引建立【例5-25】為“班級”表的列“班級名稱”建立唯一索引。操作步驟如下：（1）打開“班級”表的表設(shè)計器，右擊“班級名稱”列，彈出快捷菜單，選擇“索引/鍵”命令，如圖5-21所示。5.6.2索引建立（2）單擊“新建”按鈕，自動建立一個名為“IX_班級”索引，如圖5-24所示，等待用戶進(jìn)一步修改。

圖5-23“屬性”對話框② 圖5-24“屬性”對話框③（3）修改索引名為“Uk_班級名稱”，列名選擇“班級名稱”，如圖5-25所示。表示建立以“班級名稱”唯一約束，同時自動建立相應(yīng)的唯一索引。（4）如果選擇單選“索引”項目，意味著創(chuàng)建“唯一”索引，但不包括唯一約束，如圖5-26所示。5.6.2索引建立5.6.2索引建立【例5-26】創(chuàng)建“課程”表，并建立“課程號”主鍵，“課程名稱”唯一約束。createtable課程(課程號char(2)constraintpk_課程_課程號primarykey,課程名稱varchar(20)constraintuk_課程_課程名稱unique,課程類型char(4),學(xué)時smallint,學(xué)分tinyint,備注text)5.6.2索引建立2．通過企業(yè)管理器創(chuàng)建索引利用企業(yè)管理器建立索引有多種方法，既可以使用表設(shè)計器建立索引，也可以使用工具向?qū)Ы⑺饕€可以使用索引管理器建立索引。這里我們介紹最常用的方法，即利用表設(shè)計器建立索引。【例5-31】為“班級”表的列“班級名稱”建立唯一索引。操作步驟如下：（1）打開“班級”表的表設(shè)計器，右擊“班級名稱”列，彈出快捷菜單，選擇“索引/鍵”命令，如圖5-44所示。（2）單擊“索引/鍵”命令后，彈出“屬性”對話框，如圖5-45所示，顯示由主鍵約束（“例5-3”建表時創(chuàng)建）自動建立的“pk_班級”主鍵索引信息。運(yùn)行程序后，用戶可以在企業(yè)管理器中通過表設(shè)計器查看索引信息。操作步驟如下：（1）展開“企業(yè)管理器”控制臺目錄，右擊“課程”表，彈出快捷菜單，選擇“設(shè)計表”命令，如圖5-27所示。（2）單擊“設(shè)計表”命令，彈出“設(shè)計表”對話框，右擊列名“課程號”，彈出快捷菜單，選擇“索引/鍵”命令，如圖5-28所示。（3）單擊“索引/鍵”命令后，彈出“屬性”對話框①，如圖5-29所示，顯示系統(tǒng)自動建立的“pk_課程_課程號”主鍵索引信息。單擊“選擇的索引”下拉列表框選擇“uk_課程_課程名稱”后，彈出“屬性”對話框②，如圖5-30所示，顯示系統(tǒng)自動建立的“uk_課