第3章 數(shù)據(jù)庫(kù)和表

第3章數(shù)據(jù)庫(kù)和表本章任務(wù)創(chuàng)建數(shù)據(jù)庫(kù)管理數(shù)據(jù)庫(kù)創(chuàng)建表、操作表設(shè)置約束實(shí)施數(shù)據(jù)完整性1、數(shù)據(jù)庫(kù)文件數(shù)據(jù)文件——數(shù)據(jù)文件是數(shù)據(jù)庫(kù)對(duì)象的物理存儲(chǔ)器，所有的數(shù)據(jù)庫(kù)數(shù)據(jù)物理上都是存儲(chǔ)在數(shù)據(jù)文件中。日志文件——日記文件記錄了用戶(hù)對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作的信息。日記文件在恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)、維護(hù)數(shù)據(jù)庫(kù)的一致性方面起著舉足輕重的作用。文件組——至少要有一個(gè)數(shù)據(jù)文件和一個(gè)日志文件(.ldf)。數(shù)據(jù)文件又分為主文件(.mdf)和次文件(.ndf)，只能有一個(gè)主文件，可以有多個(gè)次文件。2、系統(tǒng)數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)庫(kù)是不能刪除的。Master——記錄著所有系統(tǒng)級(jí)別信息，包括所有的登錄帳戶(hù)和系統(tǒng)配置以及所包含的數(shù)據(jù)庫(kù)、數(shù)據(jù)文件的位置。Model——Model數(shù)據(jù)庫(kù)是新建數(shù)據(jù)庫(kù)的模板。Msdb——Msdb數(shù)據(jù)庫(kù)提供了SQLServer2005代理程序調(diào)度、警報(bào)、作業(yè)以及記錄操作員等活動(dòng)信息。Tempdb——Tempdb數(shù)據(jù)庫(kù)保存了所有的臨時(shí)表和臨時(shí)存儲(chǔ)過(guò)程。3、創(chuàng)建數(shù)據(jù)庫(kù)一種是使用圖形化工具——右擊【數(shù)據(jù)庫(kù)】節(jié)點(diǎn)【創(chuàng)建新的數(shù)據(jù)庫(kù)】另一種是命令行方式——CREATEDATABASE創(chuàng)建數(shù)據(jù)庫(kù)需要說(shuō)明的屬性：數(shù)據(jù)庫(kù)名稱(chēng)主文件和日志文件的邏輯文件名、物理文件名、初始大小、最大尺寸、文件的增量例1創(chuàng)建數(shù)據(jù)庫(kù)Sales各個(gè)屬性要求如下：文件組文件名路徑初始大小最大尺寸文件增量數(shù)據(jù)文件Sales.MdfE:\3MB10MB1MB事務(wù)日志文件Sales_Log.LdfE:\1MB5MB1MB使用命令行方式創(chuàng)建數(shù)據(jù)庫(kù)

Createdatabase

數(shù)據(jù)庫(kù)名

[On{[

primary](name=邏輯文件名,

FileName='操作系統(tǒng)下的文件名和路徑'[,Size=文件初始容量][,MaxSize={文件最大容量|unlimited}][,FileGrowth=文件的增量])}[,...n]][Logon{(Name=邏輯文件名,

FileName='操作系統(tǒng)下的文件名和路徑'[,Size=文件初始容量][,MaxSize={文件最大容量|unlimited}][,FileGrowth=文件的增量])}[,...N]]例2創(chuàng)建數(shù)據(jù)庫(kù)NewSales各個(gè)屬性要求如下：文件組文件名路徑初始大小最大尺寸文件增量數(shù)據(jù)文件NewSales.MdfE:\4MB10MB1MB事務(wù)日志文件NewSales_Log.LdfE:\2MB5MB1MB例3創(chuàng)建包括文件組的數(shù)據(jù)庫(kù)StuInfo

各個(gè)屬性要求如下：文件組文件名路徑初始大小最大尺寸文件增量主文件StuInfo

.MdfE:\3MB10MB1MB事務(wù)日志文件StuInfo_Log.LdfE:\1MB5MB1MB數(shù)據(jù)文件組Client1

StudentCourseScoreD:\2MB10MB1MB4、管理數(shù)據(jù)庫(kù)管理數(shù)據(jù)庫(kù)的內(nèi)容通常包括顯示數(shù)據(jù)庫(kù)信息、擴(kuò)充數(shù)據(jù)庫(kù)容量、配置數(shù)據(jù)庫(kù)、重命名數(shù)據(jù)庫(kù)和刪除數(shù)據(jù)庫(kù)。4_1顯示數(shù)據(jù)庫(kù)信息使用圖形化工具顯示數(shù)據(jù)庫(kù)信息右擊數(shù)據(jù)庫(kù)

【屬性】命令使用命令行方式顯示數(shù)據(jù)庫(kù)信息使用SP_HELPDBdatabase_name命令4_2擴(kuò)充數(shù)據(jù)庫(kù)容量擴(kuò)充數(shù)據(jù)庫(kù)容量可以使用兩種方法來(lái)實(shí)現(xiàn)。方法一：增加數(shù)據(jù)文件和事務(wù)日志文件的容量。方法二：為數(shù)據(jù)庫(kù)增加文件組，而后在文件組中增加數(shù)據(jù)文件和日志文件。使用圖形化工具擴(kuò)充數(shù)據(jù)文件、日記文件容量使用圖形化工具擴(kuò)充數(shù)據(jù)文件、日記文件容量的步驟如下：在SSMS中，右鍵單擊所需的數(shù)據(jù)庫(kù)

【屬性】，在“數(shù)據(jù)庫(kù)屬性”對(duì)話(huà)框中的選擇“文件”選項(xiàng)，在“數(shù)據(jù)文件”窗口直接修改數(shù)據(jù)文件、日記文件的初始大小。例5擴(kuò)充銷(xiāo)售數(shù)據(jù)庫(kù)Sales的文件容量例6在NewSales數(shù)據(jù)庫(kù)中增加一個(gè)用戶(hù)自定義文件組Client1文件組文件名原來(lái)大小擴(kuò)充后大小主文件Sales.Mdf3MB4MB事務(wù)日志文件Sales_Log.Ldf1MB2MB文件組文件名路徑初始大小最大尺寸文件增量文件組Client1Client1_DataE:\2MB10MB1MBClient1_Log

E:\1MB5MB1MB使用命令擴(kuò)充數(shù)據(jù)庫(kù)容量修改數(shù)據(jù)庫(kù)文件的大小，命令格式：例7擴(kuò)充數(shù)據(jù)庫(kù)NewSales的容量Alter

DataBase

數(shù)據(jù)庫(kù)名

ModifyFile

(name=邏輯文件名,[,Size=文件初始容量][,MaxSize={文件最大容量|unlimited}][,FileGrowth=文件增長(zhǎng)幅度])文件組文件名原來(lái)大小擴(kuò)充后大小主文件NewSales.Mdf4MB8MB事務(wù)日志文件NewSales_Log.Ldf2MB4MB使用命令為數(shù)據(jù)庫(kù)增加文件組在數(shù)據(jù)庫(kù)中建立文件組命令格式：增加文件組到數(shù)據(jù)庫(kù)Alter

Database

數(shù)據(jù)庫(kù)名

AddFileGroup

文件組名Alter

database

數(shù)據(jù)庫(kù)名

AddFile(Name=邏輯文件名,

FileName='操作系統(tǒng)下的文件名和路徑'[,Size=文件初始容量][,MaxSize={文件最大容量|unlimited}][,FileGrowth=文件增長(zhǎng)幅度])ToFileGroup

文件組名增加事務(wù)日志文件到數(shù)據(jù)庫(kù)中Alter

Database

數(shù)據(jù)庫(kù)名AddLogFile(Name=邏輯文件名,

FileName='操作系統(tǒng)下的文件名和路徑'[,Size=文件初始容量][,MaxSize={文件最大容量|unlimited}][,FileGrowth=文件增長(zhǎng)幅度])例8在NewSales數(shù)據(jù)庫(kù)增加文件組要求如下：文件組文件名路徑初始大小最大尺寸文件增量文件組Client2Client2_DataE:\2MB10MB1MB事務(wù)日志文件Client2_Log.LdfD:\1MB5MB1MB4_3配置數(shù)據(jù)庫(kù)使用ManagementStudio圖形化工具在命令行方式下使用系統(tǒng)存儲(chǔ)過(guò)程SP_DBOPTION例9顯示數(shù)據(jù)庫(kù)Sales可以進(jìn)行重新設(shè)置的選項(xiàng)；例10使用命令行方式將NewSales數(shù)據(jù)庫(kù)設(shè)置為只讀。例11將NewSales數(shù)據(jù)庫(kù)設(shè)置為單用戶(hù)方式sp_DBoption['數(shù)據(jù)庫(kù)名'][,'選項(xiàng)名'][,'值']功能選項(xiàng)名值只讀readonlyTRUE、FALSE、ON或OFF，默認(rèn)值為NULL。單用戶(hù)singleuser4_4重命名和刪除數(shù)據(jù)庫(kù)重命名數(shù)據(jù)庫(kù)刪除數(shù)據(jù)庫(kù)刪除數(shù)據(jù)庫(kù)失敗有以下幾種情況：（1）當(dāng)數(shù)據(jù)庫(kù)正在執(zhí)行數(shù)據(jù)復(fù)制時(shí)；（2）數(shù)據(jù)庫(kù)正在恢復(fù)時(shí)；（3）當(dāng)有用戶(hù)正在對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作時(shí)。Sp_RenameDb'數(shù)據(jù)庫(kù)原名','數(shù)據(jù)庫(kù)新名'DropDataBase

數(shù)據(jù)庫(kù)名[,...n]4_5分離與附加數(shù)據(jù)庫(kù)使用圖形工具分離與附加數(shù)據(jù)庫(kù)：執(zhí)行【任務(wù)】【分離】/【附加】分離數(shù)據(jù)庫(kù)的命令：附加數(shù)據(jù)庫(kù)的命令：sp_detach_Db'數(shù)據(jù)庫(kù)名'[,[@skipchecks=]'skipchecks']sp_attach_DB'數(shù)據(jù)庫(kù)名'，'主數(shù)據(jù)文件的路徑'5、創(chuàng)建表在設(shè)計(jì)表時(shí)，一般應(yīng)考慮：（1）表所包含的列數(shù)，每一列的數(shù)據(jù)類(lèi)型，列是否允許空值；（2）表是否需要索引，哪些列是主鍵，哪些列是外鍵；（3）表是否需要約束、默認(rèn)設(shè)置或規(guī)則。分析現(xiàn)實(shí)世界，設(shè)計(jì)表銷(xiāo)售人員商品銷(xiāo)售地址電話(huà)部門(mén)性別姓名編號(hào)Employees進(jìn)貨員工編號(hào)進(jìn)貨時(shí)間數(shù)量零售價(jià)進(jìn)貨價(jià)生產(chǎn)廠(chǎng)商商品名稱(chēng)商品編號(hào)Goods售貨員工編號(hào)售出時(shí)間數(shù)量商品編號(hào)銷(xiāo)售編號(hào)Sell5、創(chuàng)建表使用SSMS 圖形化工具創(chuàng)建表展開(kāi)所需的數(shù)據(jù)庫(kù)節(jié)點(diǎn)，右擊“表”節(jié)點(diǎn)【新建表】命令，輸入相關(guān)的“列名”、“數(shù)據(jù)類(lèi)型”，設(shè)置是否允許空。使用命令行方式創(chuàng)建表CreateTable

[[數(shù)據(jù)庫(kù)名.]表所有者.]表名

(列名列的屬性[,…n])創(chuàng)建表例18使用圖形化工具在Sales數(shù)據(jù)庫(kù)中創(chuàng)建Employees表。表的結(jié)構(gòu)如下：列名數(shù)據(jù)類(lèi)型是否為空說(shuō)明備注編號(hào)Char(6)No主鍵姓名Char(8)No性別BitNo部門(mén)Varchar(16)Yes電話(huà)Varchar(20)Yes地址Varchar(50)Yes例19使用命令行方式在Sales數(shù)據(jù)庫(kù)中創(chuàng)建Goods表。表的結(jié)構(gòu)如下：列名數(shù)據(jù)類(lèi)型是否為空說(shuō)明備注商品編號(hào)int

否主鍵商品名稱(chēng)Varchar(20)否生產(chǎn)廠(chǎng)商Varchar(30)否進(jìn)貨價(jià)money否零售價(jià)money否數(shù)量int否進(jìn)貨時(shí)間datetime否進(jìn)貨員工編號(hào)Char（6）否外鍵與employees關(guān)聯(lián)6、設(shè)置約束約束定義了關(guān)于允許什么數(shù)據(jù)進(jìn)入數(shù)據(jù)庫(kù)的規(guī)則。使用約束的目的是為了防止列出現(xiàn)非法數(shù)據(jù)，以保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的一致性和完整性。在SQLServer中，有以下類(lèi)型的約束PrimaryKey（主鍵）約束ForeignKey（外鍵）約束Unique（惟一）約束Check（檢查）約束Default（默認(rèn)）約束約束PrimaryKey（主鍵）約束表中一列或多列的組合的值能惟一標(biāo)識(shí)這個(gè)表的每一行，SQLServer自動(dòng)為主鍵列創(chuàng)建惟一索引。ForeignKey（外鍵）約束實(shí)現(xiàn)兩個(gè)表之間數(shù)據(jù)的相關(guān)性，限制外鍵的取值必須是主表的主鍵值?？梢詫⒈碇兄麈I值的一列或多列添加到另一張表中，以創(chuàng)建兩張表之間的鏈接。Unique（惟一）約束使用UNIQUE約束可以確保表中每條記錄的某些字段值不會(huì)重復(fù)。Check（檢查）約束使用一個(gè)邏輯表達(dá)式來(lái)判斷列中數(shù)據(jù)值的合法性。Default（默認(rèn)）約束不為字段輸入值使用默認(rèn)值Employees表編號(hào)姓名性別部門(mén)電話(huà)地址1001趙飛燕0采購(gòu)部…………1301馮曉丹

0銷(xiāo)售部…………1003李建國(guó)1采購(gòu)部…………………………………………………………………………Goods表商品編號(hào)商品名稱(chēng)生產(chǎn)廠(chǎng)商進(jìn)貨價(jià)零售價(jià)數(shù)量進(jìn)貨時(shí)間進(jìn)貨員工編號(hào)2打印機(jī)…………………………10013液晶顯示器…………………………10014數(shù)碼相機(jī)…………………………10025掃描儀…………………………1001Sell表銷(xiāo)售編號(hào)商品編號(hào)數(shù)量售出時(shí)間售貨員工編號(hào)1212004-10-1513012212004-10-1613023522004-10-2613034612005-3-2013015722005-1-31301設(shè)計(jì)約束銷(xiāo)售人員商品銷(xiāo)售地址電話(huà)部門(mén)性別姓名編號(hào)Employees進(jìn)貨員工編號(hào)進(jìn)貨時(shí)間數(shù)量零售價(jià)進(jìn)貨價(jià)生產(chǎn)廠(chǎng)商商品名稱(chēng)商品編號(hào)Goods售貨員工編號(hào)售出時(shí)間數(shù)量商品編號(hào)銷(xiāo)售編號(hào)Sell主鍵外鍵（1）PrimaryKey約束例20使用圖形化工具在Sales數(shù)據(jù)庫(kù)中為Employees表的“編號(hào)”列創(chuàng)建PrimaryKey約束。例21使用SQL語(yǔ)句為Goods表的“商品編號(hào)”列創(chuàng)建PrimaryKey約束，約束名為pk_GoodsNo

。

altertable

表名

addconstraint

主鍵約束名primarykey(列名)（2）FOREIGNKEY約束使用圖形工具建立外鍵約束。使用命令建立外鍵約束，語(yǔ)法格式：例3.22為Goods表創(chuàng)建名為FK_Goods_Employees的外鍵約束，該約束限制“進(jìn)貨員工編號(hào)”列的數(shù)據(jù)只能是Employees表“編號(hào)”列中存在的數(shù)據(jù)。AlterTable表名AddConstraint

外鍵約束名ForeignKey(列名)References

主表名(列名)Employees表編號(hào)姓名性別部門(mén)電話(huà)地址1001趙飛燕0采購(gòu)部…………1301馮曉丹

0銷(xiāo)售部…………1003李建國(guó)1采購(gòu)部…………………………………………………………………………Goods表商品編號(hào)商品名稱(chēng)生產(chǎn)廠(chǎng)商進(jìn)貨價(jià)零售價(jià)數(shù)量進(jìn)貨時(shí)間進(jìn)貨員工編號(hào)2打印機(jī)…………………………10013液晶顯示器…………………………10014數(shù)碼相機(jī)…………………………10025掃描儀…………………………1001Sell表銷(xiāo)售編號(hào)商品編號(hào)數(shù)量售出時(shí)間售貨員工編號(hào)1212004-10-1513012212004-10-1613023522004-10-2613034612005-3-2013015722005-1-31301一對(duì)多外鍵外鍵是子表上的鍵，定義在該鍵中的列（一個(gè)或一組）能正好與主表中的列在信息和數(shù)量上完全匹配。主表主鍵一條記錄子表外鍵多條記錄一對(duì)多外鍵級(jí)聯(lián)更新級(jí)聯(lián)修改onupdatecascade級(jí)聯(lián)刪除ondeletecascade（3）唯一值約束使用圖形工具建立唯一約束。使用命令建立唯一約束，語(yǔ)法格式：例3.23為Employees表創(chuàng)建名為IX_EmployeesName的Unique約束，以保證“姓名”列的取值不重復(fù)Altertable表名AddConstraint

唯一約束名Unique(列名)（4）核（檢）查約束使用圖形工具建立核查約束。使用命令建立核查約束，語(yǔ)法格式：例3.24在Sales數(shù)據(jù)庫(kù)中限定Employees表的“部門(mén)”這一列只能從“財(cái)務(wù)部”、“銷(xiāo)售部”、“采購(gòu)部”及“庫(kù)存部”四個(gè)部門(mén)名稱(chēng)中選一個(gè)，不能輸入其他名稱(chēng)。Altertable表名AddConstraint核查約束名Check(條件)（5）默認(rèn)值約束使用圖形工具建立默認(rèn)值約束。使用命令建立默認(rèn)值約束，語(yǔ)法格式：例3.25在Sales數(shù)據(jù)庫(kù)中為Goods表創(chuàng)建名為DF_GoodsDate的Default約束，該約束使“進(jìn)貨時(shí)間”列的默認(rèn)值為當(dāng)前的日期。Altertable表名AddConstraint默認(rèn)值約束名Default(默認(rèn)值)For

字段名建立表時(shí)創(chuàng)建約束CREATETABLE

表名

{列名數(shù)據(jù)類(lèi)型,

Constraint約束名約束語(yǔ)句}CREATETABLE

表名

{列名數(shù)據(jù)類(lèi)型約束語(yǔ)句}例3.26使用命令行方式在Sales數(shù)據(jù)庫(kù)中創(chuàng)建Sell表，同時(shí)進(jìn)行約束的設(shè)置。Sell表的結(jié)構(gòu)如下表：列名數(shù)據(jù)類(lèi)型是否為空說(shuō)明備注銷(xiāo)售編號(hào)int

否主鍵自動(dòng)生成商品編號(hào)int否外鍵與Goods表關(guān)聯(lián)數(shù)量int否售出時(shí)間datetime否售貨員工編號(hào)Char(6)可外鍵與Employees關(guān)聯(lián)（6）查看約束使用圖形工具展開(kāi)“約束”節(jié)點(diǎn)查看使用系統(tǒng)存儲(chǔ)過(guò)程sp_helpConstraint

sp_helpConstraint

表名（7）禁用與啟用約束altertable

表名Check/nocheck

constraint

約束名添加約束

altertable

表名

addconstraint

主鍵約束名primarykey(列名)AlterTable表名AddConstraint

外鍵約束名ForeignKey(列名)References

主表名(列名)Altertable表名AddConstraint

唯一約束名Unique(列名)Altertable表名AddConstraint檢查約束名Check(條件)Altertable表名AddConstraint默認(rèn)值約束名Default(默認(rèn)值)For

字段名數(shù)據(jù)完整性域完整性域完整性也稱(chēng)為列完整性，是指定一個(gè)數(shù)據(jù)集對(duì)某個(gè)列是否有效和確定是否允許為空值。實(shí)體完整性實(shí)體完整性也稱(chēng)為行完整性，要求表中的所有行有一個(gè)惟一的標(biāo)識(shí)符，如主鍵標(biāo)識(shí)。引用完整性引用完整性也稱(chēng)為參照完整性，引用完整性禁止用戶(hù)進(jìn)行以下操作：當(dāng)主表中沒(méi)有關(guān)聯(lián)的記錄時(shí)，將記錄添加到相關(guān)表中。更改主表中的值并導(dǎo)致相關(guān)表中的記錄不符。從主表中刪除記錄，仍存在與該記錄匹配的相關(guān)記錄。外鍵約束是強(qiáng)制引用完整性的主要方法。數(shù)據(jù)完整性的類(lèi)型及其說(shuō)明完整性類(lèi)型約束類(lèi)型說(shuō)明域完整性default當(dāng)insert語(yǔ)句沒(méi)有明確提供某列的值時(shí)，指定為該列提供缺省值check指定某列可接受的值的范圍實(shí)體完整性primary每一行的惟一標(biāo)識(shí)符號(hào)，確保用戶(hù)沒(méi)有輸入重復(fù)的值，并且創(chuàng)建索引以提高性能。不允許有空值unique防止每一行的相關(guān)列(非主鍵)出現(xiàn)重復(fù)值，并且創(chuàng)建索引以提高性能。允許有空值引用完整性foreignkey定義一列或者多列，其值與本表或者其他表的主鍵值匹配管理表查看表的屬性sp_help

表名修改表的結(jié)構(gòu)重新命名表sp_rename‘舊表名’,’新表名’刪除表droptable表名altertable表