數(shù)據(jù)庫存儲(chǔ)結(jié)構(gòu)SQL語句

1、一、數(shù)據(jù)庫存儲(chǔ)結(jié)構(gòu) SQL Server 7.0中的每個(gè)數(shù)據(jù)庫有多個(gè)操作系統(tǒng)文件組成，數(shù)據(jù)庫的所有資料、對(duì)象和數(shù)據(jù)庫操作日志均存儲(chǔ)在這些操作系統(tǒng)文件中。根據(jù)這些文件的作用不同，可以將它們劃分為以下三類：( 主數(shù)據(jù)文件：每個(gè)數(shù)據(jù)庫有且只有一個(gè)主數(shù)據(jù)文件，它是數(shù)據(jù)庫和其它數(shù)據(jù)文件的起點(diǎn)。主數(shù)據(jù)文件的擴(kuò)展名一般為.mdf； ( 輔數(shù)據(jù)文件：用于存儲(chǔ)主數(shù)據(jù)文件中未存儲(chǔ)的剩余資料和數(shù)據(jù)庫對(duì)象，一個(gè)數(shù)據(jù)庫可以沒有輔數(shù)據(jù)文件，但也可以同時(shí)擁有多個(gè)輔數(shù)據(jù)文件。輔數(shù)據(jù)文件的多少主要根據(jù)數(shù)據(jù)庫的大小、磁盤存儲(chǔ)情況和存儲(chǔ)性能要求而設(shè)置。輔數(shù)據(jù)文件的擴(kuò)展名一般為.ndf；( 日志文件：存儲(chǔ)數(shù)據(jù)庫的事務(wù)日志信息，當(dāng)數(shù)據(jù)

2、庫損壞時(shí)，管理員使用事務(wù)日志恢復(fù)數(shù)據(jù)庫。日志文件的擴(kuò)展名一般為.ldf。每個(gè)數(shù)據(jù)庫中至少兩個(gè)文件：主數(shù)據(jù)文件和日志文件。SQL Server數(shù)據(jù)庫文件除操作系統(tǒng)所賦予的物理文件名稱外，還有一個(gè)邏輯名稱。數(shù)據(jù)庫的邏輯名稱應(yīng)用于Transact-SQL語句中。例如，對(duì)于master系統(tǒng)數(shù)據(jù)庫，master為其邏輯名稱，使用Transact-SQL語句操作數(shù)據(jù)庫時(shí)，均使用該名稱。而對(duì)應(yīng)的物理文件名稱為master.mdf、其日志文件名稱為master.ldf。為了管理方便，可將多個(gè)數(shù)據(jù)庫文件組織為一組，稱作數(shù)據(jù)庫文件組。文件組能夠控制各個(gè)文件的存放位置，其中的每個(gè)文件常建立在不同的硬盤驅(qū)動(dòng)器上，這樣

3、可以減輕每個(gè)磁盤驅(qū)動(dòng)器的存儲(chǔ)壓力，提高數(shù)據(jù)庫的存儲(chǔ)效率，從而達(dá)到提高系統(tǒng)性能的目的。SQL Server采用比例填充策略使用文件組中的每個(gè)文件提供的存儲(chǔ)空間。在SQL Server中建立文件和文件組時(shí)，應(yīng)注意以下兩點(diǎn)：( 每個(gè)文件或文件組只能屬于一個(gè)數(shù)據(jù)庫，每個(gè)文件也只能成為一個(gè)文件組的成員，文件和文件組不能跨數(shù)據(jù)庫使用；( 日志文件是獨(dú)立的,它不能成為文件組的成員。也就是說，數(shù)據(jù)庫的資料內(nèi)容和日志內(nèi)容不能存入相同的文件或文件組。SQL Server中的數(shù)據(jù)庫文件組有以下三種類型：( 主文件組：其中包數(shù)據(jù)庫的主數(shù)據(jù)文件和不屬于其它文件組的數(shù)據(jù)庫文件，數(shù)據(jù)庫系統(tǒng)表的所有頁面存儲(chǔ)在主文件組中；(

4、用戶定義文件組：數(shù)據(jù)庫創(chuàng)建語句（CREATE DATABASE）或修改語句（ALTER DATABASE）中使用FILEGROUP關(guān)鍵詞所指定的文件組；( 默認(rèn)文件組：在創(chuàng)建數(shù)據(jù)庫對(duì)象時(shí)，如果沒有為它們指定文件組，它們將被存儲(chǔ)在默認(rèn)文件組中。可以使用ALTER DATABASE語句修改數(shù)據(jù)庫的默認(rèn)文件組設(shè)置，但每個(gè)數(shù)據(jù)庫同時(shí)最多只能有一個(gè)默認(rèn)文件組。當(dāng)數(shù)據(jù)庫沒有指定默認(rèn)文件組時(shí)，主文件組將被作為默認(rèn)文件組使用。由于默認(rèn)文件組的特殊作用，所以在創(chuàng)建數(shù)據(jù)庫對(duì)象時(shí)，即使不指定用戶文件組，SQL Server也能照常執(zhí)行。1.使用Transact-SQL語句建立數(shù)據(jù)庫 CREATE DATABASE

5、語句的語法格式為：CREATEDATABASEdatabase_nameONPRIMARY,n,nLOGON,nFORLOAD|FORATTACH:=(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),n:=FILEGROUPfilegroup_name,n其中，database_name為新建數(shù)據(jù)庫的邏輯名稱，在一個(gè)SQL Server服務(wù)器上，必須保證各數(shù)據(jù)庫名稱是唯一的。ON子句顯示指定存儲(chǔ)數(shù)據(jù)庫資料部分所使用的數(shù)據(jù)文

6、件和文件組列表，PRIMARY關(guān)鍵詞說明其后的數(shù)據(jù)文件屬于主文件組。如果PRIMARY關(guān)鍵詞未被指定，則關(guān)鍵詞CREATE DATABASE后的第一個(gè)文件列表將成為主數(shù)據(jù)文件。定義數(shù)據(jù)文件列表中各數(shù)據(jù)文件項(xiàng)，有多個(gè)數(shù)據(jù)文件項(xiàng)時(shí)，相互之間以逗號(hào)分隔。其中，logical_file_name參數(shù)指出數(shù)據(jù)文件的邏輯名稱，數(shù)據(jù)文件的邏輯名稱應(yīng)用在Transact-SQL語句中。在同一個(gè)數(shù)據(jù)庫中，必須保持?jǐn)?shù)據(jù)文件的邏輯名稱是唯一的。os_file_name參數(shù)說明數(shù)據(jù)文件對(duì)應(yīng)的操作系統(tǒng)文件名稱，即數(shù)據(jù)文件的物理文件名稱及其路徑。size參數(shù)指定數(shù)據(jù)文件的初始長度，其單位為MB或KB，默認(rèn)時(shí)為MB。對(duì)于主

7、數(shù)據(jù)文件，其size參數(shù)的最小值應(yīng)等于model數(shù)據(jù)庫中主數(shù)據(jù)文件的長度。對(duì)于其它數(shù)據(jù)文件，其長度最小為512KB。size參數(shù)默認(rèn)時(shí)，對(duì)于輔數(shù)據(jù)文件和日志文件，SQL Server將其長度設(shè)置為1MB，而對(duì)于主數(shù)據(jù)文件，SQL Server將其長度設(shè)為model數(shù)據(jù)庫中主數(shù)據(jù)文件的長度。SQL Server中，如果打開數(shù)據(jù)庫的autoshrink選項(xiàng)，當(dāng)數(shù)據(jù)庫文件空間用盡時(shí)，系統(tǒng)將自動(dòng)增加數(shù)據(jù)文件的大小。max_size參數(shù)定義數(shù)據(jù)文件可以增加到的最大尺寸，其單位為MB或KB。如果未定義max_size參數(shù)，數(shù)據(jù)庫文件的長度可根據(jù)需要一直增加，直到磁盤空間用盡為止。這時(shí)它等同于MAXSIZE

8、 = UNLIMITED。growth_increment參數(shù)說明數(shù)據(jù)文件空間的每次增加量，其單位為MB，KB或，默認(rèn)為MB。使用時(shí)說明數(shù)據(jù)文件每次增加的長度等于增加時(shí)文件現(xiàn)有長度的百分比，growth_increment參數(shù)的默認(rèn)值為10。用MB或KB表示時(shí)，其增加值應(yīng)為64KB或其倍數(shù)。ON子句中的參數(shù)用于指出數(shù)據(jù)庫的數(shù)據(jù)文件組，其中，filegroup_name為文件組名稱。文件組中各文件的定義格式與上面介紹的數(shù)據(jù)文件的定義格式相同。CREATE DATABASE語句中的LOG ON子句用于定義數(shù)據(jù)庫日志文件。各個(gè)日志文件的定義格式與數(shù)據(jù)文件相同。當(dāng)未使用LOG ON子句指定日志文件時(shí)，

9、SQL Server將自動(dòng)為數(shù)據(jù)庫建立一個(gè)日志文件，文件名稱由系統(tǒng)產(chǎn)生，其長度等于數(shù)據(jù)庫所有數(shù)據(jù)文件長度之和的25。SQL Server創(chuàng)建一個(gè)數(shù)據(jù)庫時(shí)要經(jīng)過以下兩個(gè)步驟： 使用model數(shù)據(jù)庫拷貝初始化新建立的數(shù)據(jù)庫，用戶在model數(shù)據(jù)庫中所建立的數(shù)據(jù)庫對(duì)象也一并被拷貝到新建數(shù)據(jù)庫中。此外，新建數(shù)據(jù)庫還繼承了model中的各種數(shù)據(jù)庫選項(xiàng)設(shè)置，如果model數(shù)據(jù)庫選項(xiàng)設(shè)置被修改，它只影響修改后所建立的數(shù)據(jù)庫，已經(jīng)建立的數(shù)據(jù)庫的各種選項(xiàng)不再發(fā)生變化了； 用空白頁面填充數(shù)據(jù)庫中的自由空間。(1)在創(chuàng)建數(shù)據(jù)庫時(shí)，如果省略了CREATE DATABASE語句中的所有可選參數(shù)，即使用下面的語句格式，它

10、所創(chuàng)建的數(shù)據(jù)庫大小完全等同于model數(shù)據(jù)庫：CREATE DATABASE MYDB1GO （2）下面例子在創(chuàng)建數(shù)據(jù)庫MYDB2時(shí)指定文件組，其數(shù)據(jù)文件有主文件組和MYDB2_GROUP文件組組成。MYDB2數(shù)據(jù)庫所包含的數(shù)據(jù)文件和日志文件，以及它們的參數(shù)如圖所示：CREATEDATABASEMYDB2ONPRIMARY(NAME=MYDB2_P1_dat,FILENAME=c:mssql7dataMYDB2_P1.mdf,SIZE=5,MAXSIZE=10,FILEGROWTH=20%),(NAME=MYDB2_P2_dat,FILENAME=c:mssql7dataMYDB2_P2.nd

11、f,SIZE=5,MAXSIZE=10,FILEGROWTH=1MB),FILEGROUPMYDB2_GROUP(NAME=MYDB2_S1_dat,FILENAME=c:mssql7dataMYDB2_S1.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=10),(NAME=MYDB2_S2_dat,FILENAME=c:mssql7dataMYDB2_S2.ndf,SIZE=20,MAXSIZE=100,FILEGROWTH=20)GoMYDB2數(shù)據(jù)庫文件 主文件組 MYDB2_GROUP文件組 日志文件邏輯名 MYDB2_P1_dat MYDB2_P2_dat MYD

12、B2_S1_dat MYDB2_S2_dat MYDB2_log文件名 C:mssql7dataMYDB2_P1.mdf c:mssql7dataMYDB2_P2.ndf C:mssql7dataMYDB2_S1.ndf c:mssql7dataMYDB2_S2.ndf c:mssql7dataMYDB2_log.ldf初始長度 5MB 5MB 10MB 20MB 10MB最大長度 10MB 10MB 50MB 100MB 無限制增 量 20 1MB 10MB 20MB 102.使用Transact-SQL語句修改數(shù)據(jù)庫ALTER DATABASE 語句的語法格式為：ALTERDATABASE

13、databaseADDFILE,nTOFILEGROUPfilegroup_name|ADDLOGFILE,n|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE|MODIFYFILEGROUPfilegroup_namefilegroup_property:=(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growt

14、h_increment)其中，database為待修改的數(shù)據(jù)庫名稱。ADD FILE子句指出向數(shù)據(jù)庫中添加數(shù)據(jù)文件，TO FILEGROUP說明新添加數(shù)據(jù)文件所屬的文件組名稱。ADD LOG FILE子句指出向數(shù)據(jù)庫中新添加的日志文件項(xiàng)。REMOVE FILE子句指出從數(shù)據(jù)庫中刪除文件，在刪除后，SQL Server將該文件對(duì)應(yīng)的物理文件一并從操作系統(tǒng)中刪除。注意： 當(dāng)數(shù)據(jù)文件內(nèi)容不為空時(shí)，不能將它們從指定數(shù)據(jù)庫中刪除； 當(dāng)數(shù)據(jù)文件為主數(shù)據(jù)文件或數(shù)據(jù)庫日志文件時(shí)，不能將它們從指定數(shù)據(jù)庫中刪除； 當(dāng)數(shù)據(jù)文件所屬的文件組為默認(rèn)文件組并且此數(shù)據(jù)文件是該默認(rèn)文件組的唯一成員時(shí)，不能將它從指定數(shù)據(jù)庫中刪

15、除。ADD FILEGROUP子句說明向數(shù)據(jù)庫中添加文件組。REMOVE FILEGROUP子句說明從數(shù)據(jù)庫中刪除文件組，在刪除時(shí)一并刪除文件組中所有數(shù)據(jù)文件對(duì)應(yīng)的操作系統(tǒng)文件。注意：只有當(dāng)文件組中所有的數(shù)據(jù)文件為空時(shí)才能將它們從數(shù)據(jù)庫中刪除。 MODIFY FILE指出修改數(shù)據(jù)文件，文件名稱由中的NAME參數(shù)指定。注意： 每次只能對(duì)數(shù)據(jù)文件的FILENAME、SIZE、FILEGROWTH和MAXSIZE中 的一項(xiàng)進(jìn)行修改； 使用ALTER DATABASE語句改變數(shù)據(jù)文件大小時(shí)，只能增加不能減少。 MODIFY FILEGROUP子句指出待修改的文件組，其中filegroup_name為文

16、件組名稱，file_property說明修改后的文件組屬性，其取值包含以下幾種：( READONLY：將文件組設(shè)置為只讀文件組，之后將禁止對(duì)其中的數(shù)據(jù)庫對(duì)象進(jìn)行修改。數(shù)據(jù)庫中的主文件組不能設(shè)置為只讀文件組；說明：如果將主文件組被設(shè)置為只讀文件組，所有用戶不能在該數(shù)據(jù)庫中再創(chuàng)建任何新的數(shù)據(jù)庫對(duì)象和登錄標(biāo)識(shí)，或重新編譯存儲(chǔ)過程，因?yàn)檫@些工作都需要修改數(shù)據(jù)庫中的系統(tǒng)表。( READWRITE：刪除文件中的只讀屬性，之后可對(duì)該文件組進(jìn)行讀、寫操作；( DEFAULT：將指定文件組設(shè)置為數(shù)據(jù)庫的默認(rèn)文件組，每個(gè)數(shù)據(jù)庫中只能有一個(gè)默認(rèn)文件組。（1）向MYDB2中添加一個(gè)數(shù)據(jù)文件MYDB2_P3_dat，其

17、大小為10MB，執(zhí)行后此數(shù)據(jù)文件被添加到主文件組PRIMARY中：ALTERDATABASEMYDB2ADDFILE（NAME=MYDB2_P3_dat,FILENAME=c:mssql7dataMYDB2_P3.ndf,SIZE=10MB,MAXSIZE=100MB,FILEGROWTH=2MB)GO(2)向MYDB2中添加一個(gè)日志文件MYDB2_LOG2：ALTERDATABASEMYDB2ADDLOGFILE（NAME=MYDB2_LOG2,FILENAME=c:mssql7dataMYDB2_LOG2.ldf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=2MB)

18、3)將數(shù)據(jù)庫MYDB2中的文件組MYDB2_GROUP設(shè)置為默認(rèn)文件組：ALTER DATABASE MODIFY FILEGROUP MYDB2_GROUP DEFAULT3.使用Transact-SQL語句刪除數(shù)據(jù)庫DROP DATABASE語句的語法格式為：DROP DATABASE database_name ,.n其中，database_name為待刪除的數(shù)據(jù)庫名。在SQL Server中只有系統(tǒng)管理員和數(shù)據(jù)庫所有者才有刪除數(shù)據(jù)庫的權(quán)限。（1）將以上建立的數(shù)據(jù)庫MYDB2刪除：DROP DATABASE MYDB24.使用系統(tǒng)存儲(chǔ)過程檢索數(shù)據(jù)庫的定義信息SQL Server提供了下列

19、系統(tǒng)存儲(chǔ)過程和語句，讓用戶檢索服務(wù)器上的數(shù)據(jù)庫定義信息以及每個(gè)數(shù)據(jù)庫文件空間的使用情況：( sp_helpdb：檢索服務(wù)器上的所有數(shù)據(jù)庫信息及單個(gè)數(shù)據(jù)庫的定義信息；對(duì)應(yīng)的語法格式為：sp_helpdb name其中，name參數(shù)為選項(xiàng)，當(dāng)提供該參數(shù)時(shí)，sp_helpdb檢索name參數(shù)指定的數(shù)據(jù)庫定義信息，否則它檢索服務(wù)器上的所有數(shù)據(jù)庫信息；( sp_spaceused：檢索數(shù)據(jù)庫中資料空間的使用情況以及表所占用的空間；對(duì)應(yīng)的語法格式為：sp_spaceused objname ,updateusage其中，objname是數(shù)據(jù)庫中的表名，它要求sp_spaceusage顯示系統(tǒng)分配給該表的空

20、間及其使用狀況。不指定objname參數(shù)時(shí)，系統(tǒng)存儲(chǔ)過程sp_spaceused將統(tǒng)計(jì)當(dāng)前數(shù)據(jù)庫中的資料空間信息。updateusage參數(shù)說明是否在統(tǒng)計(jì)空間使用情況前執(zhí)行DBCC UPDATEUSAGE語句。默認(rèn)時(shí)其值為false，即不執(zhí)行DBCC UPDATEUSAGE語句。將其值設(shè)置為true時(shí)，系統(tǒng)將對(duì)數(shù)據(jù)庫執(zhí)行DBCC UPDATEUSAGE語句，這樣所得到的空間使用信息將更為準(zhǔn)確，但執(zhí)行該語句要占用一定的時(shí)間，尤其是當(dāng)數(shù)據(jù)庫較大時(shí)，其執(zhí)行時(shí)間會(huì)更長；( DBCC SQLPERF(LOGSPACE)：檢索數(shù)據(jù)庫中的日志空間信息。二、數(shù)據(jù)庫表存儲(chǔ)結(jié)構(gòu) 在SQL Server中，每個(gè)數(shù)據(jù)

21、庫最多可創(chuàng)建20億個(gè)表，一個(gè)表允許定義1024列，每行的最大長度為8092字節(jié)（不包括文本和圖像類型的長度）。當(dāng)表中定義有varchar、nvarchar或varbinary類型列時(shí)，如果向表中插入的數(shù)據(jù)行超過8092字節(jié)時(shí)將導(dǎo)致Transact-SQL語句失敗，并產(chǎn)生錯(cuò)誤信息。SQL Server對(duì)每個(gè)表中行的數(shù)量沒有直接限制，但它受數(shù)據(jù)庫存儲(chǔ)空間的限制。每個(gè)數(shù)據(jù)庫的最大空間1048516TB，所以一個(gè)表可用的最大空間為1048516TB減去數(shù)據(jù)庫類系統(tǒng)表和其它數(shù)據(jù)庫對(duì)象所占用的空間。 SQL Server中的資料表分為永久表和臨時(shí)表兩種，永久表在創(chuàng)建后一直存儲(chǔ)在數(shù)據(jù)庫文件中，直至用戶刪除為

22、止。而臨時(shí)表則在用戶退出或系統(tǒng)修復(fù)時(shí)被自動(dòng)刪除。臨時(shí)表又分為局部臨時(shí)表和全局臨時(shí)表兩種，局部臨時(shí)表只能由創(chuàng)建它的用戶使用，在該用戶連接斷開時(shí)，它被自動(dòng)刪除。全局臨時(shí)表對(duì)系統(tǒng)當(dāng)前的所有連接用戶來說都是可用的，在使用它的最后一個(gè)會(huì)話結(jié)束時(shí)它被自動(dòng)刪除。在創(chuàng)建表時(shí)，系統(tǒng)根據(jù)當(dāng)前表名來確定是創(chuàng)建臨時(shí)表還是永久表，臨時(shí)表的表名以#開頭，除此之外為永久表。局部臨時(shí)表表名開頭包含一個(gè)#號(hào)，而全局臨時(shí)表的表名開頭包含兩個(gè)#號(hào)。1.使用Transact-SQL語句建立資料表CREATE TABLE 語句的語法格式為：CREATETABLEdatabase_name.owner.|owner.table_name

23、(|column_nameAScomputed_column_expression|,n)ONfilegroup|DEFAULTTEXTIMAGE_ONfilegroup|DEFAULT其中，table_name為新建立的表名。對(duì)于臨時(shí)表，表名字符串長度不能超過116個(gè)字符，而永久表的表名字符串長度則不能超過128個(gè)字符。此外，在同一個(gè)數(shù)據(jù)庫中，每個(gè)表所有者創(chuàng)建的表名必須保證唯一。computed_column_expression指出計(jì)算列的定義表達(dá)式，計(jì)算列是一個(gè)虛擬列，它并不是存儲(chǔ)在表中，而是由表中的其它非計(jì)算列（常規(guī)列）導(dǎo)出。計(jì)算列定義表達(dá)式可以為常規(guī)列、常量、變量、函數(shù)組成的表達(dá)式，

24、但它不能由一個(gè)子查詢構(gòu)成。除了下面情況，計(jì)算列可以與常規(guī)列一樣使用在SELECT列表、WHERE子句和ORDER BY子句中：( 計(jì)算列不能用在索引的關(guān)鍵詞列；( 計(jì)算列不能作為PRIMARY KEY、UNIQUE、FOREIGN KEY或DEFAULT約束定義的一部分；( 計(jì)算列不能用INSERT和UPDATE語句插入資料。 注意： 在建表語句中，不允許對(duì)計(jì)算列設(shè)置空值屬性（NULL或NOT NULL）； 在建表語句中，列與列的定義用逗號(hào)分隔，當(dāng)將PRIMARY KEY設(shè)置在最后時(shí)， 如果最后一列是常規(guī)列，則它后面的逗號(hào)可以省略；否則如果是計(jì)算列，則此逗號(hào)不可以省略。ON子句指出存儲(chǔ)新建表的

25、數(shù)據(jù)庫文件組。當(dāng)省略該子句或用DEFAULT關(guān)鍵詞時(shí)，表被建立在數(shù)據(jù)庫的默認(rèn)文件組中。TEXTIMAGE_ON子句說明存儲(chǔ)新建表中的text、ntext和image列資料的數(shù)據(jù)庫文件組名稱。當(dāng)省略TEXTIMAGE_ON子句時(shí)，text、ntext和image列資料與表存儲(chǔ)在同一個(gè)文件組中。如果表中不包含text、ntext和image列，則可以省略TEXTIMAGE_ON子句。 := column_name data_type NULL | NOT NULL IDENTITY (seed, increment ) NOT FOR REPLICATION ROWGUIDCOL .ncolumn

26、_definition和data_type參數(shù)分別說明列名及其數(shù)據(jù)類型，其中數(shù)據(jù)類型可以為系統(tǒng)數(shù)據(jù)類型或用戶定義數(shù)據(jù)類型。對(duì)于timestamp數(shù)據(jù)類型列，列名可以省略，此時(shí)系統(tǒng)用timestamp字符串作為列名。在一個(gè)表中只能有一個(gè)timestamp類型列。NULL和NOT NULL說明列值是否允許為NULL。在SQL Server中，NULL既不是0也不是空格，它意味著用戶還沒有為列輸入資料或是明確地插入了NULL。如果不使用NULL或NOT NULL為列設(shè)置空值屬性時(shí)，列空值屬性遵循以下規(guī)則：( 對(duì)于用戶定義數(shù)據(jù)類型，SQL Server使用該數(shù)據(jù)類型的空值屬性設(shè)置；( 對(duì)于系統(tǒng)數(shù)據(jù)類型

27、列，當(dāng)列數(shù)據(jù)類型為timestamp時(shí)，列的空值屬性為NOT NULL。對(duì)于其它數(shù)據(jù)類型列，其空值屬性則由連接選項(xiàng)ANSI_NULL_DFLT_ON和數(shù)據(jù)庫選項(xiàng)ANSI null default決定。說明：通過設(shè)置連接選項(xiàng)SET ANSI_NULL_DFLT_ON ON|OFF對(duì)列的默認(rèn)空值屬性進(jìn)行切換； 通過設(shè)置數(shù)據(jù)庫選項(xiàng)sp_dboption database,ANSI null default,true或sp_dboption database,ANSI null default,false 對(duì)列的默認(rèn)空值屬性進(jìn)行切換。IDENTITY關(guān)鍵詞指定該列為IDENTITY列。當(dāng)用戶向表中插入

28、新的資料時(shí)，系統(tǒng)自動(dòng)為該行的IDENTITY列賦值，并保證其值在表中的唯一性。每個(gè)表中只能有一個(gè)IDENTITY列，其列值不能由用戶更新，不允許空值，也不許關(guān)聯(lián)默認(rèn)值或建立DEFAULT約束。IDENTITY列常與PRIMARY KEY約束一起使用，從而保證表中各行具有唯一標(biāo)識(shí)。IDENTITY列的數(shù)據(jù)類型只能為int、smallint、tinyint、numeric、decimal等數(shù)據(jù)類型。當(dāng)IDENTITY列數(shù)據(jù)類型為numeric和decimal時(shí)，不允許出現(xiàn)小數(shù)字。對(duì)于IDENTITY列,可用seed參數(shù)和increment參數(shù)指出IDENTITY列的基值和列值增量。在建立新表時(shí)，必

29、須同時(shí)指定IDENTITY列的基值和增量，或同時(shí)省去這兩個(gè)參數(shù)。默認(rèn)時(shí)，seed和increment的值均為1。說明： 使用關(guān)鍵詞IDENTITYCOL可以引用表中的IDENTITY列，而不使用實(shí)際列名。 因?yàn)槊總€(gè)表中只有一個(gè)IDENTITY列，所以這樣操作不會(huì)引起二義性； 通過使用SQL Server中所提供的的系統(tǒng)函數(shù)IDENT_SEED(table_name)和IDENT_INCR(table_name)可以返回指定表中IDENTITY列的基值及其增量。ROWGUIDCOL關(guān)鍵詞說明該列為全局唯一標(biāo)識(shí)列，每個(gè)表中只能有一個(gè)ROWGUIDCOL列，ROWGUIDCOL列的數(shù)據(jù)類型必須為un

30、iqueidentifier。ROWGUIDCOL屬性不能自動(dòng)為列賦值，也不要求列值的唯一性。在INSERT語句中，可以使用NEWID函數(shù)為ROWGUIDCOL列賦值。2.使用Transact-SQL語句修改資料表ALTER TABLE 語句的語法格式為： ALTERTABLEtableALTERCOLUMNcolumn_namenew_data_type(precision,scale)NULL|NOTNULL|ADD|DROPROWGUIDCOL|ADD|column_nameAScomputed_column_expression,n|WITHCHECK|WITHNOCHECKADD,n

31、|DROPCONSTRAINTconstraint_name|COLUMNcolumn,n|CHECK|NOCHECKCONSTRAINTALL|constraint_name,n|ENABLE|DISABLETRIGGERALL|trigger_name,n在建立一個(gè)表后，在使用過程中經(jīng)常會(huì)發(fā)現(xiàn)原來創(chuàng)建的表可能存在結(jié)構(gòu)、約束等方面的問題。在這種情況下，如果用一個(gè)新表替換原來的表，將造成表中資料的丟失。使用ALTER TABLE語句可以在保留表中原有資料的基礎(chǔ)上修改表結(jié)構(gòu)，打開、關(guān)閉或刪除已有的約束，或增加新的約束。其中，table_name參數(shù)說明所修改的表名。WITH CHECK 和WIT

32、H NOCHECK選項(xiàng)說明向表中添加新的或打開表中原有的CHECK約束和FOREIGN KEY約束時(shí)，是否對(duì)表中已有資料進(jìn)行約束檢查。使用WITH NOCHECK選項(xiàng)可以禁止對(duì)表中已有數(shù)據(jù)進(jìn)行約束檢查，但該選項(xiàng)對(duì)新添加的數(shù)據(jù)無效，新插入的資料必須接受約束檢查。ALTER COLUMN子句說明修改表中column_name參數(shù)所指定列定義，它可以改變列數(shù)據(jù)類型和空值設(shè)置，添加或刪除ROWGUIDCOL屬性。但下列類型不能被修改：( text、ntext、image、timestamp列；( 計(jì)算列或用于計(jì)算的常規(guī)列；( 復(fù)制列；( 用于索引列，但如果這些列為varchar或varbinary數(shù)據(jù)

33、類型，可以增加它們的列長度；( 用在CHECK、FOREIGN KEY、UNIQUE或PRIMARY KEY約束中的列，但如果這些列為變長列，可以使用ALTER TABLE語句改變這些列的定義長度；( 關(guān)聯(lián)有默認(rèn)值的列。使用ALTER COLUMN子句修改列數(shù)據(jù)類型時(shí)，new_data_type參數(shù)必須符合以下條件：( 原數(shù)據(jù)類型必須能夠轉(zhuǎn)換為新的數(shù)據(jù)類型；( 修改后的數(shù)據(jù)類型不能為timestamp；( ANSI null default選項(xiàng)是打開的或被修改列允許空值；注意：如果表中的對(duì)應(yīng)列原定義為NULL并列中不存在資料時(shí)，將此列更改為NOT NULL時(shí)是允許的。( ANSI_PADDIN

34、G選項(xiàng)是打開的；( 對(duì)于被修改的IDENTITY列，必須具有有效的IDENTITY數(shù)據(jù)類型。ADD子句說明向表中添加新列，新列的定義方法與CREATE TABLE語句中的相同，包括列名、數(shù)據(jù)類型、約束條件等。注意：在ALTER TABLE語句中，對(duì)于新增加的列，必須允許空值，或關(guān)聯(lián)一個(gè)默認(rèn)值。 無論此列原定義是否為NULL或此列中是否存在資料。DROP CONSTRAINT constraint | COLUMN column 子句說明從表中刪除指定約束或列。一個(gè)表中的下列類型不能被刪除：( 復(fù)制列；( 用于索引列；( 用于CHECK、FOREIGN KEY、UNIQUE或 PRIMARY K

35、EY約束中的列；( 定義有默認(rèn)值或關(guān)聯(lián)有默認(rèn)對(duì)象的列；( 關(guān)聯(lián)有規(guī)則的列。 CHECK | NOCHECK CONSTRAINT ALL | constraint 子句說明打開或關(guān)閉表中所有或constraint參數(shù)指定的FOREIGN KEY和CHECK約束。當(dāng)使用NOCHECK CONSTRAINT關(guān)閉約束時(shí)，之后所插入到表中的資料不再接受該約束檢查。在打開或關(guān)閉表中約束時(shí)，可以使用WITH CHECK或WITH NOCHECK子句對(duì)表中的資料進(jìn)行（或不進(jìn)行）檢查。3.使用Transact-SQL語句刪除資料表DROP TABLE 語句的語法格式為：DROP TABLE table_nam

36、e 其中，table_name為待刪除表的名稱。當(dāng)刪除一個(gè)表時(shí)，表之定義和表中的所有數(shù)據(jù)、以及該表的索引、許可設(shè)置、約束、觸發(fā)器等均被自動(dòng)刪除，與該表相關(guān)聯(lián)的規(guī)則和默認(rèn)對(duì)象失去與它的關(guān)聯(lián)關(guān)系。但是，使用DROP TABLE語句不能刪除SQL Server系統(tǒng)表和被FOREIGN KEY約束所參照的用戶表。三、約束在數(shù)據(jù)庫管理系統(tǒng)中，保證數(shù)據(jù)庫中的資料完整性是非常重要的。所謂資料完整性，就是指存儲(chǔ)在數(shù)據(jù)庫中資料的一致性和正確性。在SQL Server中，可以通過各種約束和默認(rèn)、規(guī)則、觸發(fā)器等資料對(duì)象來保證資料的完整性。其中約束包括以下幾種：( PRIMARY KEY：主鍵約束；( FOREIGN

37、 KEY：外鍵約束；( UNIQUE： 唯一約束；( CHECK： 檢查約束；( DEFAULT： 默認(rèn)值約束。在SQL Server中，根據(jù)完整性措施所作用的數(shù)據(jù)庫對(duì)象和范圍不同，可將它們分類為以下幾種：( 實(shí)體完整性；( 域完整性；( 參照完整性；1. 實(shí)體完整性實(shí)體完整性把表中的每行看作一個(gè)實(shí)體，它要求所有行都具有唯一標(biāo)識(shí)。在SQL Server中，可以通過建立PARMARY KEY約束、UNIQUE約束，以及列的IDENTITY屬性等措施來實(shí)施實(shí)體完整性。2. 域完整性域完整性要求表中指定列的資料具有正確的數(shù)據(jù)類型、格式和有效的資料范圍。域完整性通過默認(rèn)值、FOREIGN KEY、CH

38、ECK等約束，以及默認(rèn)、規(guī)則等數(shù)據(jù)庫對(duì)象來實(shí)現(xiàn)。3. 參照完整性參照完整性維持被參照表和參照表之間的資料一致性，它通過主鍵（PRIMARY KEY）約束和外鍵（FOREIGN KEY）約束來實(shí)現(xiàn)。在被參照表中，當(dāng)其主鍵被其它表所參照時(shí)，該行不能被刪除，也不允許改變。在參照表中，不允許參照不存在的主鍵值。（1） DEFAULT約束 使用默認(rèn)值（DEFAULT）約束后，如果資料在插入新行時(shí)沒有顯示為列提供資料，系統(tǒng)將默認(rèn)值賦給該列。默認(rèn)值約束所提供的默認(rèn)值可以為常量、函數(shù)、系統(tǒng)零進(jìn)函數(shù)、空值（NULL）等。 默認(rèn)值約束的定義格式為： CONSTRAINT constraint_name DEFAU

39、LT constraint_expression 其中，constraint_name參數(shù)指出所建立的默認(rèn)值約束名稱。constraint_expression表達(dá)式為列提供默認(rèn)值。在使用DEFAULT約束時(shí)，還應(yīng)注意以下兩點(diǎn)：( 每列只能有一個(gè)DEFAULT約束；( 約束表達(dá)式不能參照表中的其它列和其它表、視圖或存儲(chǔ)過程。 例如，先創(chuàng)建TB_constraint表，并使用默認(rèn)值約束為country列設(shè)置默認(rèn)值。之后，再執(zhí)行ALTER TABLE語句為TB_constraint表的name列添加一個(gè)默認(rèn)值約束：CREATETABLETB_constraint(namechar(20)notnu

40、ll,countryvarchar(30)CONSTRAINTDF_countryDEFAULTChina)GOALTERTABLETB_countraintADDCONSTRANTDF_nameDEFAULTUNKNOWNFORname（2） CHECK約束 CHECK約束限制輸入到一列或多列的可能值，從而保證SQL Server數(shù)據(jù)庫中資料的域完整性。在CHECK約束中可以包含搜索條件，但不能包含子查詢。一個(gè)表可以定義多個(gè)CHECK約束，對(duì)于列也可以定義多個(gè)CHECK約束。 注意： 對(duì)于ALTER TABLE語句可以為同一列添加多個(gè)CHECK約束，但對(duì)于CREATE TABLE語句只能為每

41、列定義一個(gè)CHECK約束； 如果CHECK約束被應(yīng)用于一列，則它被定義為列級(jí)CHECK約束；如果CHECK約束被應(yīng)用于多列，則它被定義為表級(jí)CHECK約束。 在ALTER TABLE語句和CREATE TABLE語句中，列級(jí)CHECK約束和表級(jí)CHECK約束的定義格式分別為： CONSTRAINT constraint_name CHECK NOT FOR REPLICATION (邏輯表達(dá)式) CONSTRAINT constraint_name CHECK NOT FOR REPLICATION (搜索條件) 其中，constraint_name選項(xiàng)指出所建立的CHECK約束的名稱。 邏輯

42、表達(dá)式可以是AND和OR連接的多個(gè)簡單邏輯表達(dá)式而構(gòu)成的復(fù)合型邏輯表達(dá)式，搜索條件為布爾表達(dá)式。 列級(jí)CHECK約束只能參照被約束列，而表級(jí)CHECK約束則只能參照表中列，它不能參照其它表中資料。 例如，為前面已經(jīng)創(chuàng)建的表TB_constraint增加phone字段及其對(duì)應(yīng)的CHECK約束： ALTER TABLE TB_constraint ADD Phone char(8) null CONSTRAINT CH_phone CHECK (phone LIKE 0-90-90-90-90-90-90-90-9) 在ALTER TABLE語句中，使用CHECK或NOCHECK選項(xiàng)可以打開或關(guān)閉

43、某個(gè)約束，并可以使用WITH CHECK或WITH NOCHECK子句對(duì)表中的資料進(jìn)行（或不進(jìn)行）檢查。例如，將上例中所建立的CHECK約束關(guān)閉：ALTER TABLE TB_constraint NOCHECK CONSTRAINT CH_phone （3） PRIMARY KEY約束 PRIMARY KEY約束通過建立唯一索引保證指定列的實(shí)體完整性，使用PRIMARY KEY約束時(shí)，列的空值屬性必須定義為NOT NULL。PRIMARY KEY約束可以應(yīng)用于表中一列或多列，應(yīng)用于多列時(shí)，它被定義為表級(jí)PRIMARY KEY約束，否則被定義為列級(jí)PRIMARY KEY約束。列級(jí)PRIMARY

44、 KEY約束的定義格式為： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT 表級(jí)PRIMARY KEY約束的定義格式為： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED （ column ,.n ） WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT SQL Server自動(dòng)為實(shí)施P

45、RIMARY KEY約束的列建立唯一索引。如果在PRIMARY KEY約束中未指定索引類型時(shí)，默認(rèn)情況下所建立的索引為簇索引（CLUSTERED）。該索引只能夠通過刪除PRIMARY KEY約束或其相關(guān)表的方法來刪除，而不能使用DROP INDEX語句刪除。無論是建立列級(jí)PRIMARY KEY約束還是表級(jí)PRIMARY KEY約束，每個(gè)表只能創(chuàng)建一個(gè)PRIMARY KEY約束。在PRIMARY KEY約束定義中，WITH子句設(shè)置為PRIMARY KEY約束所建立索引的頁面填充度，ON子句指出存儲(chǔ)索引的數(shù)據(jù)庫文件組名稱。例如，為表TB_constraint的name列添加PRIMARY KEY約

46、束：因?yàn)镻RIMARY KEY約束禁止被約束列出現(xiàn)重復(fù)的列值，所以，在建立PRIMARY KEY約束前應(yīng)刪除前面為name列所建立的DEFAULT約束。ALTER TABLE TB_constraint DROP CONSTRAINT DF_nameGOALTER TABLE TB_constraint ADD CONSTRAINT PK_name PRIMARY KEY (name) （4） UNIQUE約束UNIQUE約束也能保證一列或多列的實(shí)體完整性，每個(gè)UNIQUE約束要建立一個(gè)唯一索引。對(duì)于實(shí)施UNIQUE約束的列，不允許有任意兩行具有相同的索引值。與PRIMARY KEY約束不同的

47、是，SQL Server允許為一個(gè)表建立多個(gè)UNIQUE約束。列級(jí)UNIQUE約束的定義格式為： CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT UNIQUE約束應(yīng)用于多列時(shí)稱作表級(jí)UNIQUE約束，其定義格式為： CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED （ column ,.n ） WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT CLUSTERED和NONCLUSTERED關(guān)鍵詞分別要求SQL