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

1、1,計(jì)算機(jī)學(xué)院 郭占龍,第4章 表的存儲原理及完整性創(chuàng)建管理,4.1 SQL Server表的類型 4.2 表的存儲原理 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的臨時表 4.1.2 SQL Server的系統(tǒng)表,3,計(jì)算機(jī)學(xué)院 郭占龍,4.1.1 SQL Server的臨

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

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

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

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

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

7、 MONEY NULL，PRIMARY KEY(課號) ),,,,9,計(jì)算機(jī)學(xué)院 郭占龍,注意：,當(dāng)一個新表剛創(chuàng)建時，在插入第一行數(shù)據(jù)之前系統(tǒng)不會立即為其分配存儲空間，因此Sysindexes表中指示頁地址和預(yù)留存儲空間的列將都是0值。如果在該表上定義了PRIMARY KEY或UNIQUE約束，而PRIMARY KEY或UNIQUE約束的背后是由簇集索引支持的，則該表在Sysindexes中對應(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ù)存儲的基本單位是頁。緊跟著頁頭的就是存儲表的真正數(shù)據(jù)行區(qū)域。單個數(shù)據(jù)行的最大長度是8060字節(jié)。數(shù)據(jù)行不能跨頁存儲（文本和圖像例外）。頁內(nèi)數(shù)據(jù)行的多少依賴于表的結(jié)構(gòu)和要存儲的數(shù)據(jù)。如果一個表的所有列都是定長，那么該表在每一頁上存儲相同數(shù)目

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

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

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

12、 minlen2099048 Fixed 0 0x000000000000 24而其各個字段則會被插入到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定長

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

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

16、因此該記錄沒有變長字段（如果位5為1說明存在變長字段）。 最后一個字節(jié)是NULL位圖，其值4意味只有第三位是1，表示第三個字段是NULL。,1、 SQL Server定長記錄的存儲,15,計(jì)算機(jī)學(xué)院 郭占龍,2、 SQL Server變長記錄的存儲,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)這個表被創(chuàng)建以后，就有類似下面一個記錄被插入到sysindexes系統(tǒng)表中：id name indid first minlen18099105

18、 Variable 0 0x000000000000 9而其各個字段則會被插入到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變長記錄的存儲,當(dāng)往Variable表中插入一個記錄數(shù)據(jù)時，例如：INSERT INTO Variable VALUES(‘xyz’,‘ABCDe’,NULL, ‘123’,999)Variable表在sysindexes系統(tǒng)表里的內(nèi)容就會發(fā)生變化：id name indid first minlen18099

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

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

23、處，換句話說，目前整個記錄的實(shí)際長度是28個字節(jié)長。,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ù)據(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ù)類型存儲的每個值占用8個字節(jié)，共64位，其中63位用于存儲數(shù)字，1位用于表示正負(fù)。⑵⑵Int。也可以寫作integer，可以存儲從-231到231-1范圍內(nèi)的全部整數(shù)。以int數(shù)據(jù)類型存儲的每個值占用4個字節(jié)，共32位，其中31位用于存儲數(shù)字，1位用于表示正負(fù)的區(qū)別。⑶Smallint?？梢源鎯?215到215- 1范圍內(nèi)的所有整數(shù)。以smallint數(shù)據(jù)類型存儲的每個值占用2個字節(jié)，共16位，其中15位用于存儲數(shù)字，1位用于

25、表示正負(fù)的區(qū)別。⑷Tinyint?？梢源鎯?到255范圍內(nèi)的所有整數(shù)。以tinyint數(shù)據(jù)類型存儲的每個值占用1個字節(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。它存儲的貨幣值由2個4字節(jié)整數(shù)構(gòu)成。前面的一個4字節(jié)表示貨幣值的整數(shù)部分，后面的一個4字節(jié)表示貨幣值的小數(shù)部分。以Money存儲的貨幣值的范圍從-263到263-1，

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

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

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

30、 郭占龍,4.3.4 日期/時間數(shù)據(jù)類型,⑴Datetime。Datetime數(shù)據(jù)類型范圍從1753年1月1日到9999年12月31日，可以精確到千分之一秒。Datetime數(shù)據(jù)類型的數(shù)據(jù)占用8個字節(jié)的存儲空間。⑵Smalldatetime。Smalldatetime數(shù)據(jù)范圍從1900年1月1日到2079年6月6日，可以精確到分。Smalldatetime數(shù)據(jù)類型占4個字節(jié)的存儲空間。,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。具有固定的長度，最大長度可以達(dá)到8K字節(jié)。（2）varbinary。具有不固定的長度，其最大長度也不得超過8K字節(jié)。（3）Image。該數(shù)據(jù)類型可用于存儲字節(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)是固定長度的雙字節(jié)數(shù)據(jù)類型，括號里的n用來定義數(shù)據(jù)的最大長度。n的取值范圍是1~4000。（2）Nvarchar(n)。Nvarchar(n)數(shù)據(jù)類型存儲可變長度的雙字節(jié)數(shù)據(jù)類型，括號里的n用來定義數(shù)據(jù)的最大長度。n的取值為0~4000。（3）Ntext(n)。Ntext數(shù)據(jù)類型存儲的是可變長度的雙字節(jié)字符，Ntext數(shù)

33、據(jù)類型突破了前2種雙字節(jié)數(shù)據(jù)類型不能超過4000字符的規(guī)定，最多可以存儲多達(dá)230-1個雙字節(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中，每個數(shù)據(jù)庫中最多可以創(chuàng)建200萬個表，用戶創(chuàng)建數(shù)據(jù)庫表時，最多可以定義1024列，也就是可以定義1024個字段。

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 ( [列級完整性約束條件] [, [列級完整性約束條件] …][,]),28,計(jì)算機(jī)學(xué)院 郭占龍,1. 利

35、用create命令創(chuàng)建表,參數(shù)說明：（1）是所要定義的基本表的名字。一個表它可以由一個或多個屬性組成。（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)建表時運(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)建表時運(yùn)用約束的說明,（2）主鍵約束（primary key constraint） 主健約束要求主健屬性取值必須惟一，一個表只能包含一個主健約束。如果沒有在主健約束中指定CLUSTERED或NONCLUSTERED，并且沒有為UNIQUE約束指定聚集索引，則將對該主健約束用CLUSTERE

38、D。主鍵約束SQL的語法形式如下：[CONSTRAINT 約束名] PRIMARY KEY [CLUSTERED|NONCLUSTERED]（列名[,…n]）,31,計(jì)算機(jī)學(xué)院 郭占龍,,例如：在執(zhí)行創(chuàng)建產(chǎn)品信息表的操作時，指定產(chǎn)品編號為主鍵值。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建立了一個索引排序。,32,計(jì)算機(jī)學(xué)院 郭占龍,2．關(guān)于創(chuàng)建表時運(yùn)用約束的說明,（3）唯一性約束（unique constraint）唯一性約束用于指定一個或者多個列的組合的值具有唯一性，以防止在列中輸入重

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)建表時運(yùn)用約束的說明,（4）檢查約束（check constraint）使用檢查約束時，應(yīng)該注意以下幾點(diǎn)：① 一個列級檢查約束只能與限制的字段有關(guān)；一個表級檢查約束只能與限制的表中字段有關(guān)。② 一個表中可以定義多個檢查約束。③ 每個CREATE TABLE語句中每個字段只能定義一個檢查約束。④ 在多個字段上定義檢查約束，則必須將檢查約束定義為表級約束。⑤ 檢查約束中不能包含子查詢。創(chuàng)建檢查約束S

42、QL語法格式為：CONSTRAINT constraint_nameCHECK [NOT FOR REPLICATION] (logical_expression),34,計(jì)算機(jī)學(xué)院 郭占龍,例如：創(chuàng)建一個職工信息表，其中輸入性別字段值時，只能接受“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)建表時運(yùn)用約束的說明,（6）外部鍵約束外部鍵約束用于強(qiáng)制參照完整性，提供單個字段或者多個字段的參照完整性。當(dāng)使用外部鍵約束時，應(yīng)該考慮以下幾個因素：① 外部鍵約束提供了字段參照完整性。② 外部鍵從句中的字段數(shù)目和每個字段指定的數(shù)據(jù)類型必須和REFERENCES從句中的字段相匹配。③ 外部鍵約束不能自動創(chuàng)建索引，需要用戶手動創(chuàng)建。

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

46、表的外鍵。,外鍵的作用不只是對輸入自身的數(shù)據(jù)進(jìn)行限制，同時也限制了對主鍵所在表的數(shù)據(jù)進(jìn)行修改。用戶無法對主鍵里的數(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又分別相對于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所示。方法一：使用對象資源管理器創(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ì)器的上半部分有一個表格，在這個表格中輸入列的屬性，表格的每一行對應(yīng)一列。對每一列都需要進(jìn)行設(shè)置，其中前三項(xiàng)是必須在建表時給出的。④ 完成后，單擊工具欄上的【保存】按鈕。

50、在出現(xiàn)的選擇名稱對話框中輸入表名：學(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é)號，單擊工具欄上的主鍵按鈕，學(xué)號列名左側(cè)出現(xiàn)

51、“鑰匙”圖標(biāo)。如果要設(shè)置多屬性作為主鍵，可以通過按住鍵，用鼠標(biāo)左鍵依次單擊要選定的列，選定多列后，單擊工具欄上的主鍵按鈕。取消主鍵設(shè)置的方法是，選定主鍵字段，單擊主鍵按鈕即可。設(shè)置主鍵后，系統(tǒng)自動建立了一個索引。,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é)號屬性為主鍵，因此自動在表中建立一個根據(jù)

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

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

54、0-9][0-9][0-9][0-9]’）。建立身份證約束：單擊“添加”，系統(tǒng)自動給定一個約束名，同上，改名為“CK_學(xué)生表_身份證號”，然后在約束表達(dá)式文本框中輸入：（身份證號 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]'）。建立移動電話的約束與上類似。,46,

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

56、號 VARCHAR(20), 定價 money, CONSTRAINT PK_課程表_課號 PRIMARY KEY(課號), CONSTRAINT CK_課程表_課號 CHECK(課號 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)常會發(fā)現(xiàn)原來創(chuàng)建的表可能

57、存在結(jié)構(gòu)、約束等方面的問題，在這種情況下，需要對原表進(jìn)行修改。如果用創(chuàng)建一個新表的方法替換原表，將造成表中數(shù)據(jù)的丟失，而通過修改表則可以在保留表中原有數(shù)據(jù)的基礎(chǔ)上修改表結(jié)構(gòu)，打開、關(guān)閉或刪除已有約束，或增加新的約束等。 修改表結(jié)構(gòu)有兩種方法： 一種是利用資源對象管理器，自己試。 一種是使用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、表的屬性性別的長度改為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ù)據(jù)類型為日期型，增加年齡

60、屬性，其類型為整型，取值為13～70。USE 教學(xué)管理GOALTER TABLE 學(xué)生表 ADD 入學(xué)時間 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è)