guo第4章表的存儲(chǔ)原理及完整性創(chuàng)建管理

1、1,計(jì)算機(jī)學(xué)院 郭占龍,第4章 表的存儲(chǔ)原理及完整性創(chuàng)建管理,4.1 SQL Server表的類型 4.2 表的存儲(chǔ)原理 4.3 SQL Server數(shù)據(jù)類型 4.4 數(shù)據(jù)表的創(chuàng)建和管理,2,計(jì)算機(jī)學(xué)院 郭占龍,4.1 SQL Server表的類型,4.1.1 SQL Server的臨時(shí)表 4.1.2 SQL Server的系統(tǒng)表,3,計(jì)算機(jī)學(xué)院 郭占龍,4.1.1 SQL Server的臨

2、時(shí)表,SQL Server中的數(shù)據(jù)表分為：永久表：創(chuàng)建后一直存儲(chǔ)在數(shù)據(jù)庫文件中，直到用戶刪除為止。臨時(shí)表 臨時(shí)表分兩種：局部臨時(shí)表：表名用#開頭。只能由創(chuàng)建它的用戶使用，在該用戶連接斷開時(shí)，它被自動(dòng)刪除。全局臨時(shí)表：表名用##開頭。當(dāng)前所有連接用戶都可以使用，它在最后一個(gè)會(huì)話結(jié)束時(shí)被自動(dòng)刪除。系統(tǒng)根據(jù)表名前有無#符號(hào)確定創(chuàng)建的是臨時(shí)表還是永久表。,4,計(jì)算機(jī)學(xué)院 郭占龍,4.2.2 SQL Server的系統(tǒng)表,一些

3、系統(tǒng)表只存在于master數(shù)據(jù)庫，它們包含系統(tǒng)級(jí)信息。在SQL Server 2000里共有18個(gè)表，在SQL Server 2005里對(duì)應(yīng)20個(gè)視圖。 。 而一些系統(tǒng)表則存在于每一個(gè)數(shù)據(jù)庫（包括master數(shù)據(jù)庫），它們包含屬于這個(gè)特定數(shù)據(jù)庫的對(duì)象和資源的相關(guān)信息。在SQL Server 2000里共有17個(gè)表，在SQL Server 2005里對(duì)應(yīng)22個(gè)視圖。 注意：不允許使用SQL語句直接修改系統(tǒng)表中的內(nèi)容；

4、不允許編寫程序直接訪問系統(tǒng)表中的信息；如果需要系統(tǒng)表信息，可以通過系統(tǒng)的存儲(chǔ)過程和系統(tǒng)提供的函數(shù)進(jìn)行。,5,計(jì)算機(jī)學(xué)院 郭占龍,4.2 表的存儲(chǔ)原理,4.2.1 內(nèi)部存儲(chǔ)概述 4.2.2 SQL Server數(shù)據(jù)記錄結(jié)構(gòu),6,計(jì)算機(jī)學(xué)院 郭占龍,4.1.1 內(nèi)部存儲(chǔ)概述,表是有關(guān)某個(gè)特定實(shí)例的數(shù)據(jù)集合，在關(guān)系數(shù)據(jù)庫中處于核心地位。創(chuàng)建一個(gè)表，就會(huì)有一行或多行插入到用來管理這個(gè)表的多個(gè)系統(tǒng)表里。至少要寫信息到sysob

5、jects、sysindexes和syscolumns這三個(gè)系統(tǒng)表里，當(dāng)新建的表有外碼約束時(shí)，相關(guān)的信息還會(huì)插入到sysrefrences系統(tǒng)表里。,7,計(jì)算機(jī)學(xué)院 郭占龍,,Sysobjects主要記錄新表的基本信息，如表名、對(duì)象ID以及表的所有者等等。Syscolumns主要記錄新表列的信息，如列名、類型和長(zhǎng)度等。 Sysindexes系統(tǒng)表記錄包含指向新表所使用的存儲(chǔ)空間的指針和有關(guān)新表大小的信息。sysrefrence

6、s系統(tǒng)表里記錄參照表的外鍵信息。例如：創(chuàng)建一個(gè)表，它在系統(tǒng)表中的記錄信息如下圖所示：,8,計(jì)算機(jī)學(xué)院 郭占龍,CREATE TABLE 課程表(課號(hào) CHAR (6) NOT NULL ,課名 CHAR (20) NOT NULL ,教材名稱 CHAR (20) NULL ,編著者 CHAR (10) NULL ,出版社 CHAR (20) NULL ,版號(hào) CHAR (15) NULL ,定價(jià)

7、 MONEY NULL，PRIMARY KEY(課號(hào)) ),,,,9,計(jì)算機(jī)學(xué)院 郭占龍,注意：,當(dāng)一個(gè)新表剛創(chuàng)建時(shí)，在插入第一行數(shù)據(jù)之前系統(tǒng)不會(huì)立即為其分配存儲(chǔ)空間，因此Sysindexes表中指示頁地址和預(yù)留存儲(chǔ)空間的列將都是0值。如果在該表上定義了PRIMARY KEY或UNIQUE約束，而PRIMARY KEY或UNIQUE約束的背后是由簇集索引支持的，則該表在Sysindexes中對(duì)應(yīng)行的indid值為1。任何有非

8、簇集索引支持的其他約束在Sysindexes表中都有一行，而且該行的indid值在2到250之間。,10,計(jì)算機(jī)學(xué)院 郭占龍,4.2.2 數(shù)據(jù)行（記錄）結(jié)構(gòu),在 SQL Server 中，數(shù)據(jù)存儲(chǔ)的基本單位是頁。緊跟著頁頭的就是存儲(chǔ)表的真正數(shù)據(jù)行區(qū)域。單個(gè)數(shù)據(jù)行的最大長(zhǎng)度是8060字節(jié)。數(shù)據(jù)行不能跨頁存儲(chǔ)（文本和圖像例外）。頁內(nèi)數(shù)據(jù)行的多少依賴于表的結(jié)構(gòu)和要存儲(chǔ)的數(shù)據(jù)。如果一個(gè)表的所有列都是定長(zhǎng)，那么該表在每一頁上存儲(chǔ)相同數(shù)目

9、的行。如果一個(gè)表里有變長(zhǎng)列，那么該表總是在每一頁上存儲(chǔ)盡可能多的行。,11,計(jì)算機(jī)學(xué)院 郭占龍,4.2.2 數(shù)據(jù)行（記錄）結(jié)構(gòu),1、定長(zhǎng)記錄在定長(zhǎng)記錄里，每個(gè)字段都有固定的長(zhǎng)度，而且字段數(shù)也是固定的。這種記錄的字段能夠連續(xù)存儲(chǔ)，因此在給定記錄地址的情況下，借助系統(tǒng)目錄里有關(guān)字段的長(zhǎng)度信息就能計(jì)算出某個(gè)特殊字段的的地址。2、變長(zhǎng)記錄對(duì)于變長(zhǎng)記錄來說，一種可能的記錄組織方式就是像定長(zhǎng)記錄一樣連續(xù)地存放字段，字段之間通過分割符隔開

10、。這種組織方式需要掃描記錄才能定位需要訪問的字段。另一種方法是在記錄開始處預(yù)留一些空間作為存放一個(gè)整數(shù)偏移量數(shù)組的空間，數(shù)組中的第I個(gè)整數(shù)表示記錄的第I個(gè)字段的其始地址，當(dāng)然這個(gè)地址是相對(duì)于記錄的起始地址的。另外在數(shù)組中也存儲(chǔ)記錄尾部的偏移量，這個(gè)偏移量用來識(shí)別最后一個(gè)字段的結(jié)束位置。,12,計(jì)算機(jī)學(xué)院 郭占龍,1、 SQL Server定長(zhǎng)記錄的存儲(chǔ),首先來看最簡(jiǎn)單的情況，記錄中所有字段都是定長(zhǎng)的。CREATE TABLE F

11、ixed(col1 int NOT NULLcol2 char(5) NOT NULLcol3 char(3) NULLcol4 float NOT NULL)當(dāng)這個(gè)表被創(chuàng)建以后，就有類似下面一個(gè)記錄被插入到sysindexes系統(tǒng)表中：id name indid first

12、 minlen2099048 Fixed 0 0x000000000000 24而其各個(gè)字段則會(huì)被插入到syscolumns系統(tǒng)表中：name colid xtype length xoffsetcol1 1 56 4

13、 4col2 2 175 5 8col3 3 175 3 13col4 4 62 8 16,13,計(jì)算機(jī)學(xué)院 郭占龍,1、 SQL Server定長(zhǎng)

14、記錄的存儲(chǔ),當(dāng)往Fixed表中插入一個(gè)記錄數(shù)據(jù)時(shí)，例如：INSERT INTO Fixed VALUES(123,‘ABCD’,NULL,45.5)Fixed表在sysindexes系統(tǒng)表里的內(nèi)容就會(huì)發(fā)生變化：id name indid first minlen2099048 Fixed 0 0x720000000000 24這說明在插

15、入了一記錄數(shù)據(jù)之后，SQL Server就為Fixed表分配了一個(gè)數(shù)據(jù)頁。Fixed表只包含四個(gè)定長(zhǎng)字段，sysindexes表中minlen字段的值表示記錄的最小長(zhǎng)度，該長(zhǎng)度恰好是syscolumns表中表示字段長(zhǎng)度的length的數(shù)字之和再加上4個(gè)字節(jié)。其中額外的4個(gè)字節(jié)是用于記錄字段數(shù)目的兩個(gè)字節(jié)和表示字段中NULL的字節(jié)數(shù)。,14,計(jì)算機(jī)學(xué)院 郭占龍,,第一個(gè)字節(jié)是狀態(tài)位A，它的值是0x10，表示只有位4是1，其他位都是0，

16、因此該記錄沒有變長(zhǎng)字段（如果位5為1說明存在變長(zhǎng)字段）。 最后一個(gè)字節(jié)是NULL位圖，其值4意味只有第三位是1，表示第三個(gè)字段是NULL。,1、 SQL Server定長(zhǎng)記錄的存儲(chǔ),15,計(jì)算機(jī)學(xué)院 郭占龍,2、 SQL Server變長(zhǎng)記錄的存儲(chǔ),CREATE TABLE Variable(col1 char(3) NOT NULLcol2 varchar(15) NOT NUL

17、Lcol3 varchar(5) NULLcol4 varchar(10) NOT NULLcol5 smallint NOT NULL)當(dāng)這個(gè)表被創(chuàng)建以后，就有類似下面一個(gè)記錄被插入到sysindexes系統(tǒng)表中：id name indid first minlen18099105

18、 Variable 0 0x000000000000 9而其各個(gè)字段則會(huì)被插入到syscolumns系統(tǒng)表中：name colid xtype length xoffsetcol1 1 175 3 4col2 2

19、 167 15 -1col3 3 167 5 -1col4 4 167 10 -1col5 5 52 2

20、 7,16,計(jì)算機(jī)學(xué)院 郭占龍,2、 SQL Server變長(zhǎng)記錄的存儲(chǔ),當(dāng)往Variable表中插入一個(gè)記錄數(shù)據(jù)時(shí)，例如：INSERT INTO Variable VALUES(‘xyz’,‘ABCDe’,NULL, ‘123’,999)Variable表在sysindexes系統(tǒng)表里的內(nèi)容就會(huì)發(fā)生變化：id name indid first minlen18099

21、105 Variable 0 0x880000000000 9定長(zhǎng)字段的數(shù)據(jù)位于記錄中由syscolumns的xoffset值指定的字節(jié)偏移量所在的位置，即col1起始于字節(jié)偏移量4的位置，而col5起始于字節(jié)偏移量7的位置。,17,計(jì)算機(jī)學(xué)院 郭占龍,,為了找到變長(zhǎng)字段，首先要確定記錄中列偏移數(shù)組的位置。在表示總字段數(shù)的2個(gè)字節(jié)（其值是0500）和表示位圖的1字節(jié)（其值為04）之后就是變長(zhǎng)字段數(shù)的兩個(gè)字節(jié)，在

22、本例中其值是0300，換算成十進(jìn)制是3，說明該記錄有3個(gè)變長(zhǎng)字段存在。緊跟其后的字節(jié)就是變長(zhǎng)字段偏移數(shù)組。該例變長(zhǎng)字段偏移數(shù)組用三個(gè)2字節(jié)來表示3個(gè)變長(zhǎng)字段在記錄中的結(jié)束位置。1900經(jīng)過字節(jié)交換是0x0019，所以第一個(gè)變長(zhǎng)字段結(jié)束于25字節(jié)處。接下來也是0x0019，所以第二個(gè)變長(zhǎng)字段實(shí)際長(zhǎng)度為0，表明沒有任何東西存儲(chǔ)在變長(zhǎng)數(shù)據(jù)區(qū)域。1c00經(jīng)過字節(jié)交換是0x001c，所以第三個(gè)變長(zhǎng)字段結(jié)束于28字節(jié)處，而且整個(gè)記錄也結(jié)束于28字節(jié)

23、處，換句話說，目前整個(gè)記錄的實(shí)際長(zhǎng)度是28個(gè)字節(jié)長(zhǎng)。,18,計(jì)算機(jī)學(xué)院 郭占龍,4.3 SQL Server數(shù)據(jù)類型,4.3.1 數(shù)值型數(shù)據(jù) 4.3.2 貨幣型數(shù)據(jù) 4.3.3 字符型數(shù)據(jù) 4.3.4 日期/時(shí)間數(shù)據(jù)類型 4.3.5 二進(jìn)制數(shù)據(jù)類型 4.3.5 雙字節(jié)數(shù)據(jù)類型,19,計(jì)算機(jī)學(xué)院 郭占龍,4.3.1 數(shù)值型數(shù)據(jù),⑴Bigint?？梢源娣艔?263到263-1范圍內(nèi)的整型數(shù)據(jù)。以bigint

24、數(shù)據(jù)類型存儲(chǔ)的每個(gè)值占用8個(gè)字節(jié)，共64位，其中63位用于存儲(chǔ)數(shù)字，1位用于表示正負(fù)。⑵⑵Int。也可以寫作integer，可以存儲(chǔ)從-231到231-1范圍內(nèi)的全部整數(shù)。以int數(shù)據(jù)類型存儲(chǔ)的每個(gè)值占用4個(gè)字節(jié)，共32位，其中31位用于存儲(chǔ)數(shù)字，1位用于表示正負(fù)的區(qū)別。⑶Smallint?？梢源鎯?chǔ)從-215到215- 1范圍內(nèi)的所有整數(shù)。以smallint數(shù)據(jù)類型存儲(chǔ)的每個(gè)值占用2個(gè)字節(jié)，共16位，其中15位用于存儲(chǔ)數(shù)字，1位用于

25、表示正負(fù)的區(qū)別。⑷Tinyint?？梢源鎯?chǔ)0到255范圍內(nèi)的所有整數(shù)。以tinyint數(shù)據(jù)類型存儲(chǔ)的每個(gè)值占用1個(gè)字節(jié)。⑸Decimal和Numeric。在SQL Server中，decimal和numeric型數(shù)據(jù)的最高精度的可以達(dá)到38位，也就是說必須在-1038-1到1038-1之間。格式為：Decimal（n,d）或Numeric（n,d），其中n為總的位數(shù)，d為小數(shù)位數(shù)。⑹float和real。float型數(shù)據(jù)范圍從-1

26、.79E+38到1.79E+38，Real型數(shù)據(jù)范圍從-3.40E+38到3.40E+38。其中float可采用科學(xué)記數(shù)法表示，格式為 ： float（n），n必須在1~53之間。,20,計(jì)算機(jī)學(xué)院 郭占龍,4.3.2 貨幣型數(shù)據(jù),⑴Money。它存儲(chǔ)的貨幣值由2個(gè)4字節(jié)整數(shù)構(gòu)成。前面的一個(gè)4字節(jié)表示貨幣值的整數(shù)部分，后面的一個(gè)4字節(jié)表示貨幣值的小數(shù)部分。以Money存儲(chǔ)的貨幣值的范圍從-263到263-1，

27、可以精確到萬分之一貨幣單位。⑵Smallmoney。它存儲(chǔ)的貨幣值由2個(gè)2字節(jié)整數(shù)構(gòu)成。前面的一個(gè)2字節(jié)表示貨幣值的整數(shù)部分，后面的一個(gè)2字節(jié)表示貨幣值的小數(shù)部分。以Smallmoney存儲(chǔ)的貨幣值的范圍從-214,748.3648到+214,748.3647，也可以精確到萬分之一貨幣單位。,21,計(jì)算機(jī)學(xué)院 郭占龍,4.3.3 字符型數(shù)據(jù),⑴Char。利用Char數(shù)據(jù)類型存儲(chǔ)數(shù)據(jù)時(shí)，每個(gè)字符占用一個(gè)字節(jié)的存儲(chǔ)空間。Char數(shù)據(jù)

28、類型使用固定長(zhǎng)度來存儲(chǔ)字符，最長(zhǎng)可以容納8000個(gè)字符。利用Char數(shù)據(jù)類型來定義表列或者定義變量時(shí)，應(yīng)該給定數(shù)據(jù)的最大長(zhǎng)度。如果實(shí)際數(shù)據(jù)的字符長(zhǎng)度短于給定的最大長(zhǎng)度，則多余的字節(jié)會(huì)用空格填充。如果實(shí)際數(shù)據(jù)的字符長(zhǎng)度超過了給定的最大長(zhǎng)度，則超過的字符將會(huì)被截?cái)?。在使用字符型常量為字符?shù)據(jù)類型賦值時(shí)，必須使用單引號(hào)（‘’）將字符型常量括起來。⑵Varchar。Varchar數(shù)據(jù)類型的使用方式與Char數(shù)據(jù)類型類似。SQL Server

29、利用Varchar數(shù)據(jù)類型來存儲(chǔ)最長(zhǎng)可以達(dá)到8000字符的變長(zhǎng)字符。與Char 數(shù)據(jù)類型不同，Varchar數(shù)據(jù)類型的存儲(chǔ)空間隨存儲(chǔ)在表列中的每一個(gè)數(shù)據(jù)的字符數(shù)的不同而變化。⑶Text。當(dāng)要存儲(chǔ)的字符型數(shù)據(jù)非常龐大以至于8000字節(jié)完全不夠用時(shí)，Char和Varchar數(shù)據(jù)類型都失去了作用。這時(shí)應(yīng)該選擇Text數(shù)據(jù)類型。 Text數(shù)據(jù)類型專門用于存儲(chǔ)數(shù)量龐大的變長(zhǎng)字符數(shù)據(jù)。最大長(zhǎng)度可以達(dá)到231-1個(gè)字符，約2GB。,22,計(jì)算機(jī)學(xué)院

30、 郭占龍,4.3.4 日期/時(shí)間數(shù)據(jù)類型,⑴Datetime。Datetime數(shù)據(jù)類型范圍從1753年1月1日到9999年12月31日，可以精確到千分之一秒。Datetime數(shù)據(jù)類型的數(shù)據(jù)占用8個(gè)字節(jié)的存儲(chǔ)空間。⑵Smalldatetime。Smalldatetime數(shù)據(jù)范圍從1900年1月1日到2079年6月6日，可以精確到分。Smalldatetime數(shù)據(jù)類型占4個(gè)字節(jié)的存儲(chǔ)空間。,23,計(jì)算機(jī)學(xué)院 郭占龍,4.3.5

31、 二進(jìn)制數(shù)據(jù)類型,所謂二進(jìn)制數(shù)據(jù)是一些用十六進(jìn)制來表示的數(shù)據(jù)。例如，十進(jìn)制數(shù)據(jù)245表示成十六進(jìn)制數(shù)據(jù)就應(yīng)該是F5。（1）binary。具有固定的長(zhǎng)度，最大長(zhǎng)度可以達(dá)到8K字節(jié)。（2）varbinary。具有不固定的長(zhǎng)度，其最大長(zhǎng)度也不得超過8K字節(jié)。（3）Image。該數(shù)據(jù)類型可用于存儲(chǔ)字節(jié)數(shù)超過8K字節(jié)的數(shù)據(jù)，比如Microsoft Word文檔、Microsoft Excel圖表以及圖像數(shù)據(jù)（包括.GIF、.BMP、.JPE

32、G文件）等。,24,計(jì)算機(jī)學(xué)院 郭占龍,4.3.5 雙字節(jié)數(shù)據(jù)類型,（1）Nchar(n)。Nchar(n)是固定長(zhǎng)度的雙字節(jié)數(shù)據(jù)類型，括號(hào)里的n用來定義數(shù)據(jù)的最大長(zhǎng)度。n的取值范圍是1~4000。（2）Nvarchar(n)。Nvarchar(n)數(shù)據(jù)類型存儲(chǔ)可變長(zhǎng)度的雙字節(jié)數(shù)據(jù)類型，括號(hào)里的n用來定義數(shù)據(jù)的最大長(zhǎng)度。n的取值為0~4000。（3）Ntext(n)。Ntext數(shù)據(jù)類型存儲(chǔ)的是可變長(zhǎng)度的雙字節(jié)字符，Ntext數(shù)

33、據(jù)類型突破了前2種雙字節(jié)數(shù)據(jù)類型不能超過4000字符的規(guī)定，最多可以存儲(chǔ)多達(dá)230-1個(gè)雙字節(jié)字符。,25,計(jì)算機(jī)學(xué)院 郭占龍,4.4 數(shù)據(jù)表的創(chuàng)建和管理,4.4.1 數(shù)據(jù)表結(jié)構(gòu)的創(chuàng)建 4.4.2 數(shù)據(jù)表結(jié)構(gòu)的管理,26,計(jì)算機(jī)學(xué)院 郭占龍,4.4.1 數(shù)據(jù)表結(jié)構(gòu)的創(chuàng)建,在SQL Server 2005中，每個(gè)數(shù)據(jù)庫中最多可以創(chuàng)建200萬個(gè)表，用戶創(chuàng)建數(shù)據(jù)庫表時(shí)，最多可以定義1024列，也就是可以定義1024個(gè)字段。

34、 SQL Server 2000提供了兩種方法創(chuàng)建數(shù)據(jù)庫表：第一種方法是利用企業(yè)管理器（Enterprise Manager）創(chuàng)建表；另一種方法是利用Transact-SQL語句中的create命令創(chuàng)建表。,27,計(jì)算機(jī)學(xué)院 郭占龍,1. 利用create命令創(chuàng)建表,其語法形式如下：CREATE TABLE ( [列級(jí)完整性約束條件] [, [列級(jí)完整性約束條件] …][,]),28,計(jì)算機(jī)學(xué)院 郭占龍,1. 利

35、用create命令創(chuàng)建表,參數(shù)說明：（1）是所要定義的基本表的名字。一個(gè)表它可以由一個(gè)或多個(gè)屬性組成。（2）一般取有實(shí)際意義的名字。（3）可以是前面介紹的數(shù)據(jù)類型。（4）在SQL Server 2000中有下面幾種完整性約束條件： 空值約束（NULL or NOT NULL）；主鍵約束（primary key constraint）；唯一性約束（unique constraint）；檢查約束（check constr

36、aint）；缺省約束（default constraint）；外部鍵約束（foreign key constraint）；規(guī)則（rule）；缺省值（default）。,29,計(jì)算機(jī)學(xué)院 郭占龍,2．關(guān)于創(chuàng)建表時(shí)運(yùn)用約束的說明,（1）空值約束（NULL or NOT NULL）空值NULL約束決定屬性值是否允許為空值（NULL）。NULL表示沒有輸入任何內(nèi)容，它不是零和空白，不允許為空值則用NOT NULL表示。例如：設(shè)置屬性te

37、acher允許為空值。CREATE TABLE t( teacher char(8) NULL),30,計(jì)算機(jī)學(xué)院 郭占龍,2．關(guān)于創(chuàng)建表時(shí)運(yùn)用約束的說明,（2）主鍵約束（primary key constraint） 主健約束要求主健屬性取值必須惟一，一個(gè)表只能包含一個(gè)主健約束。如果沒有在主健約束中指定CLUSTERED或NONCLUSTERED，并且沒有為UNIQUE約束指定聚集索引，則將對(duì)該主健約束用CLUSTERE

38、D。主鍵約束SQL的語法形式如下：[CONSTRAINT 約束名] PRIMARY KEY [CLUSTERED|NONCLUSTERED]（列名[,…n]）,31,計(jì)算機(jī)學(xué)院 郭占龍,,例如：在執(zhí)行創(chuàng)建產(chǎn)品信息表的操作時(shí)，指定產(chǎn)品編號(hào)為主鍵值。CREATE TABLE products(id char(10) NOT NULL,name char(20) NOT NULL,price money，qu

39、antity smallint NULL,CONSTRAINT pk_products PRIMARY KEY CLUSTERED (id))此例的主健名稱是pk_products，在products表中關(guān)于id建立了一個(gè)索引排序。,32,計(jì)算機(jī)學(xué)院 郭占龍,2．關(guān)于創(chuàng)建表時(shí)運(yùn)用約束的說明,（3）唯一性約束（unique constraint）唯一性約束用于指定一個(gè)或者多個(gè)列的組合的值具有唯一性，以防止在列中輸入重

40、復(fù)的值。 創(chuàng)建唯一性約束SQL語句：[CONSTRAINT 約束名] UNIQUE [CLUSTERED|NONCLUSTERED]（列名[,…n]） 例：CREATE TABLE student1(sno char(8),sname char(16),constraint pk_student primary key(sno),constraint uniq_student unique(sname)),33,計(jì)算機(jī)

41、學(xué)院 郭占龍,2．關(guān)于創(chuàng)建表時(shí)運(yùn)用約束的說明,（4）檢查約束（check constraint）使用檢查約束時(shí)，應(yīng)該注意以下幾點(diǎn)：① 一個(gè)列級(jí)檢查約束只能與限制的字段有關(guān)；一個(gè)表級(jí)檢查約束只能與限制的表中字段有關(guān)。② 一個(gè)表中可以定義多個(gè)檢查約束。③ 每個(gè)CREATE TABLE語句中每個(gè)字段只能定義一個(gè)檢查約束。④ 在多個(gè)字段上定義檢查約束，則必須將檢查約束定義為表級(jí)約束。⑤ 檢查約束中不能包含子查詢。創(chuàng)建檢查約束S

42、QL語法格式為：CONSTRAINT constraint_nameCHECK [NOT FOR REPLICATION] (logical_expression),34,計(jì)算機(jī)學(xué)院 郭占龍,例如：創(chuàng)建一個(gè)職工信息表，其中輸入性別字段值時(shí)，只能接受“F”或者“M”，并且為phonenum字段創(chuàng)建檢查約束，限制只能輸入類似01080798654之類的數(shù)據(jù)，而不能隨意輸入其他數(shù)據(jù)。create table employee(

43、Id char(8),name char(8) CHECK(NAME LIKE ‘[A-C,a-c][A-Z,a-z][A-Z][A-Z]’),sex char(2),phonenum int,constraint chk_sex check（sex in （’F’,’M’））default ‘F’,Constraint chk_phonenum check（phonenum like ‘(010)[8,9][0-9][0-9

44、][0-9] [0-9][0-9][0-9][0-9]’）),35,計(jì)算機(jī)學(xué)院 郭占龍,2．關(guān)于創(chuàng)建表時(shí)運(yùn)用約束的說明,（6）外部鍵約束外部鍵約束用于強(qiáng)制參照完整性，提供單個(gè)字段或者多個(gè)字段的參照完整性。當(dāng)使用外部鍵約束時(shí)，應(yīng)該考慮以下幾個(gè)因素：① 外部鍵約束提供了字段參照完整性。② 外部鍵從句中的字段數(shù)目和每個(gè)字段指定的數(shù)據(jù)類型必須和REFERENCES從句中的字段相匹配。③ 外部鍵約束不能自動(dòng)創(chuàng)建索引，需要用戶手動(dòng)創(chuàng)建。

45、④ 一個(gè)表中最多可以有31個(gè)外部鍵約束。⑤ 在臨時(shí)表中，不能使用外部鍵約束。⑥ 主鍵和外部鍵的數(shù)據(jù)類型必須嚴(yán)格匹配。外鍵約束SQL的語法形式如下：[CONSTRAINT 約束名] FOREIGN KEY （外鍵列名）REFERENCES 參照表（參照列名）,36,計(jì)算機(jī)學(xué)院 郭占龍,外鍵約束主要用來維護(hù)兩個(gè)表之間的一致性關(guān)系。 外鍵的建立主要是通過將一個(gè)表的主鍵所在列包含在另一個(gè)表中，這些列就是另一個(gè)

46、表的外鍵。,外鍵的作用不只是對(duì)輸入自身的數(shù)據(jù)進(jìn)行限制，同時(shí)也限制了對(duì)主鍵所在表的數(shù)據(jù)進(jìn)行修改。用戶無法對(duì)主鍵里的數(shù)據(jù)進(jìn)行修改和刪除，除非事先刪除或修改外鍵引用的數(shù)據(jù)。,37,計(jì)算機(jī)學(xué)院 郭占龍,,例如：學(xué)生表S（SNO，SNAME，）中的SNO是S表的主健，課程表C（CNO，CNAME，）中的CNO是C表的主健，而學(xué)習(xí)表SC（SNO，CNO，GRADE）中的SNO、CNO是SC表的主健，并且SNO和CNO又分別相對(duì)于S表和C表是S

47、C表的外健。則創(chuàng)建SC表的SQL語句如下：CREATE TABLE SC (SNO CHAR(10) NOT NULL,CNO CHAR(6) NOT NULL,GRADE TINYINT DEFAULT NULL,CONSTRAINT PK_SC PRIMARY KEY ( SNO,CNO ),CONSTRAINT FK_SC FOREIGN KEY ( SNO ) REFERENCES Student(Sno),FOR

48、EIGN KEY ( CNO ) REFERENCES Course(Cno)),38,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,【例4-1】創(chuàng)建數(shù)據(jù)庫“教學(xué)管理”的數(shù)據(jù)表學(xué)生基本資料表，數(shù)據(jù)表結(jié)構(gòu)如表1-3、表的完整性約束如表1-8、表1-9所示。方法一：使用對(duì)象資源管理器創(chuàng)建第一步設(shè)置STUDENT數(shù)據(jù)表的結(jié)構(gòu)，步驟如下：① 在樹形目錄中找到要建表的數(shù)據(jù)庫。② 在該數(shù)據(jù)庫上單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“新建”

49、->“表”命令，如圖4-4所示。出現(xiàn)創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)的窗口，如圖4-5所示。,39,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,,,,圖4-4 新建數(shù)據(jù)表,圖4-5 利用表設(shè)計(jì)器創(chuàng)建表結(jié)構(gòu),,,40,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,③ 表設(shè)計(jì)器的上半部分有一個(gè)表格，在這個(gè)表格中輸入列的屬性，表格的每一行對(duì)應(yīng)一列。對(duì)每一列都需要進(jìn)行設(shè)置，其中前三項(xiàng)是必須在建表時(shí)給出的。④ 完成后，單擊工具欄上的【保存】按鈕。

50、在出現(xiàn)的選擇名稱對(duì)話框中輸入表名：學(xué)生。⑤ 單擊【確定】按鈕退出。,41,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,第二步設(shè)置STUDENT表完整性約束，步驟如下：① 展開企業(yè)管理器的數(shù)據(jù)庫“教學(xué)管理”，單擊“表”節(jié)點(diǎn)，選定數(shù)據(jù)表學(xué)生，單擊右鍵，在快捷菜單上，單擊“設(shè)計(jì)表”，出現(xiàn)如圖4-5所示的設(shè)計(jì)表結(jié)構(gòu)窗口。② 設(shè)置主鍵約束（PRIMARY KEY），光標(biāo)移到需要設(shè)置主鍵的字段學(xué)號(hào)，單擊工具欄上的主鍵按鈕，學(xué)號(hào)列名左側(cè)出現(xiàn)

51、“鑰匙”圖標(biāo)。如果要設(shè)置多屬性作為主鍵，可以通過按住鍵，用鼠標(biāo)左鍵依次單擊要選定的列，選定多列后，單擊工具欄上的主鍵按鈕。取消主鍵設(shè)置的方法是，選定主鍵字段，單擊主鍵按鈕即可。設(shè)置主鍵后，系統(tǒng)自動(dòng)建立了一個(gè)索引。,42,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,③ 在圖4-5所示的表設(shè)計(jì)器上右擊，出現(xiàn)快捷菜單，如圖4-6所示。④ 選擇“索引/鍵”選項(xiàng)，如圖4-7所示，系統(tǒng)設(shè)置表學(xué)生.學(xué)號(hào)屬性為主鍵，因此自動(dòng)在表中建立一個(gè)根據(jù)

52、學(xué)號(hào)的值的大小升序排列的索引。⑤ 設(shè)置檢查約束（CHECK），學(xué)生表定義了三個(gè)CHECK約束，第一個(gè)約束是學(xué)號(hào)學(xué)號(hào)，只能有7個(gè)字符構(gòu)成，其中第一個(gè)必須是S，后面全部是數(shù)字，邏輯表達(dá)式為“（學(xué)號(hào) LIKE ‘S[0-9][0-9][0-9][0-9][0-9][0-9]’）”。單擊“CHECK約束”選項(xiàng)卡，在“CHECK約束”對(duì)話框中新建約束。見圖4-8。,43,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,,,,圖4-6 表設(shè)置

53、快捷菜單,圖4-7 “索引/鍵”屬性對(duì)話框,,,44,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,,,圖4-8 “CHECK約束”屬性對(duì)話框,,45,計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,建立學(xué)號(hào)的約束：?jiǎn)螕簟疤砑印?，系統(tǒng)自動(dòng)給定一個(gè)約束名， 可在“標(biāo)識(shí)-（名稱）”處改名為“CK_學(xué)生表_學(xué)號(hào)”，然后在“常規(guī)-表達(dá)式”行單擊 ，出現(xiàn)約束表達(dá)式文本框， 然后在約束表達(dá)式文本框中輸入（學(xué)號(hào) LIKE ‘S[0-9][0-9][

54、0-9][0-9][0-9][0-9]’）。建立身份證約束：?jiǎn)螕簟疤砑印?，系統(tǒng)自動(dòng)給定一個(gè)約束名，同上，改名為“CK_學(xué)生表_身份證號(hào)”，然后在約束表達(dá)式文本框中輸入：（身份證號(hào) LIKE '[0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9][0?9] [0?9][0?9][0?9][0?9]'）。建立移動(dòng)電話的約束與上類似。,46,

55、計(jì)算機(jī)學(xué)院 郭占龍,3．?dāng)?shù)據(jù)表結(jié)構(gòu)創(chuàng)建實(shí)例,方法二：使用SQL命令創(chuàng)建（1）創(chuàng)建表并包含完整性約束定義。 CREATE TABLE 課程表( 課號(hào) CHAR(6) NOT NULL, 課名 VARCHAR(30) NOT NULL, 學(xué)分 INT CHECK(學(xué)分>=1 and 學(xué)分<=5), 教材名稱 VARCHAR(30), 編著者 CHAR(8), 出版社 VARCHAR(20), 版

56、號(hào) VARCHAR(20), 定價(jià) money, CONSTRAINT PK_課程表_課號(hào) PRIMARY KEY(課號(hào)), CONSTRAINT CK_課程表_課號(hào) CHECK(課號(hào) LIKE 'C[0-9][0-9][0-9][0-9][0-9]')),47,計(jì)算機(jī)學(xué)院 郭占龍,4.4.2 數(shù)據(jù)表結(jié)構(gòu)的管理,1．修改數(shù)據(jù)表結(jié)構(gòu) 表結(jié)構(gòu)創(chuàng)建以后，在使用的過程中經(jīng)常會(huì)發(fā)現(xiàn)原來創(chuàng)建的表可能

57、存在結(jié)構(gòu)、約束等方面的問題，在這種情況下，需要對(duì)原表進(jìn)行修改。如果用創(chuàng)建一個(gè)新表的方法替換原表，將造成表中數(shù)據(jù)的丟失，而通過修改表則可以在保留表中原有數(shù)據(jù)的基礎(chǔ)上修改表結(jié)構(gòu)，打開、關(guān)閉或刪除已有約束，或增加新的約束等。 修改表結(jié)構(gòu)有兩種方法： 一種是利用資源對(duì)象管理器，自己試。 一種是使用SQL命令。,48,計(jì)算機(jī)學(xué)院 郭占龍,4.4.2 數(shù)據(jù)表結(jié)構(gòu)的管理,使用SQL命令修改表。語法格式：ALTER TAB

58、LE ADD[完整性約束]|ALTER COLUMN 列名 新類型|DROP COLUMN 列名|ADD PRIMARY KEY（列名[,…]）|ADD FOREIGN KEY(列名) REFERENCES 表名（列名）|ADD CONSTRAINT 約束名…. |DROP CONSTRAINT 約束名,49,計(jì)算機(jī)學(xué)院 郭占龍,4.4.2 數(shù)據(jù)表結(jié)構(gòu)的管理,【例4-2】將教學(xué)管理數(shù)據(jù)庫中的學(xué)生

59、表的屬性性別的長(zhǎng)度改為2。 其SQL語句的程序清單如下：USE 教學(xué)管理 --打開教學(xué)管理數(shù)據(jù)庫GOALTER TABLE 學(xué)生表 ALTER COLUMN 性別 CHAR(2) --修改屬性列性別,50,計(jì)算機(jī)學(xué)院 郭占龍,4.4.2 數(shù)據(jù)表結(jié)構(gòu)的管理,【例4-3】向教學(xué)管理數(shù)據(jù)庫中的學(xué)生表增加屬性入學(xué)時(shí)間，其數(shù)據(jù)類型為日期型，增加年齡

60、屬性，其類型為整型，取值為13～70。USE 教學(xué)管理GOALTER TABLE 學(xué)生表 ADD 入學(xué)時(shí)間 DATETIME, 年齡 INT CONSTRAINT CK_學(xué)生表_年齡CHECK（年齡>=13 AND年齡<70）,51,計(jì)算機(jī)學(xué)院 郭占龍,4.4.2 數(shù)據(jù)表結(jié)構(gòu)的管理,2．?dāng)?shù)據(jù)表結(jié)構(gòu)的刪除可以用企業(yè)管理器或SQL語句刪除基本表。方法一：使用企業(yè)