數(shù)據(jù)庫(kù)系統(tǒng)原理實(shí)踐

——SQLServer20052創(chuàng)建和管理數(shù)據(jù)庫(kù)1創(chuàng)建和管理數(shù)據(jù)庫(kù)1SQLServer數(shù)據(jù)庫(kù)概述2創(chuàng)建數(shù)據(jù)庫(kù)3修改數(shù)據(jù)庫(kù)4刪除數(shù)據(jù)庫(kù)5分離和附加數(shù)據(jù)庫(kù)*21SQLServer數(shù)據(jù)庫(kù)概述在一個(gè)實(shí)例中可以創(chuàng)建多個(gè)數(shù)據(jù)庫(kù)，每個(gè)數(shù)據(jù)庫(kù)在物理上和邏輯上都是獨(dú)立的。每個(gè)數(shù)據(jù)庫(kù)存儲(chǔ)相關(guān)的數(shù)據(jù)。*3數(shù)據(jù)庫(kù)分類(lèi)SQLServer將數(shù)據(jù)庫(kù)分為兩大類(lèi)：系統(tǒng)數(shù)據(jù)庫(kù)用戶(hù)數(shù)據(jù)庫(kù)。系統(tǒng)數(shù)據(jù)庫(kù)是SQLServer數(shù)據(jù)庫(kù)管理系統(tǒng)自動(dòng)創(chuàng)建和維護(hù)的，這些數(shù)據(jù)庫(kù)用于保存維護(hù)系統(tǒng)正常運(yùn)行的信息。用戶(hù)數(shù)據(jù)庫(kù)保存與用戶(hù)的業(yè)務(wù)有關(guān)的數(shù)據(jù)，用戶(hù)數(shù)據(jù)庫(kù)中的數(shù)據(jù)是用戶(hù)來(lái)維護(hù)的。*4數(shù)據(jù)庫(kù)分類(lèi)示意圖*51.1系統(tǒng)數(shù)據(jù)庫(kù)master：是最重要的數(shù)據(jù)庫(kù)，用于記錄SQLServer系統(tǒng)中所有系統(tǒng)級(jí)信息。msdb：保存關(guān)于調(diào)度報(bào)警、作業(yè)、操作員等信息。model：是用戶(hù)數(shù)據(jù)庫(kù)的模板，其中包含所有用戶(hù)數(shù)據(jù)庫(kù)的共享信息。當(dāng)創(chuàng)建用戶(hù)數(shù)據(jù)庫(kù)時(shí)，系統(tǒng)自動(dòng)將model數(shù)據(jù)庫(kù)中的全部?jī)?nèi)容復(fù)制到新建數(shù)據(jù)庫(kù)中。tempdb：臨時(shí)數(shù)據(jù)庫(kù)，用于存儲(chǔ)臨時(shí)信息。*61.2SQLServer數(shù)據(jù)庫(kù)的組成由若干個(gè)文件組成，文件被分為兩類(lèi)：數(shù)據(jù)文件：用于存放數(shù)據(jù)庫(kù)中的數(shù)據(jù)信息。日志文件：用于存放對(duì)數(shù)據(jù)庫(kù)的操作記錄。*7數(shù)據(jù)庫(kù)文件每個(gè)數(shù)據(jù)庫(kù)都包含：一個(gè)主數(shù)據(jù)文件一個(gè)或多個(gè)日志文件零個(gè)或多個(gè)輔助數(shù)據(jù)文件每個(gè)文件都有：物理文件名邏輯文件名*8數(shù)據(jù)頁(yè)在SQLServer2005中，數(shù)據(jù)的存儲(chǔ)單位是頁(yè)（Page，也稱(chēng)為數(shù)據(jù)頁(yè)）。一頁(yè)是一塊8KB（8×1024字節(jié)，其中用8060個(gè)字節(jié)存放數(shù)據(jù)，另外的132個(gè)字節(jié)存放系統(tǒng)信息）的連續(xù)磁盤(pán)空間，頁(yè)是存儲(chǔ)數(shù)據(jù)的最小單位。頁(yè)的大小決定了數(shù)據(jù)庫(kù)表中一行數(shù)據(jù)的最大大小。*9不允許表中的一行數(shù)據(jù)存儲(chǔ)在不同頁(yè)上，即行不能跨頁(yè)存儲(chǔ)。因此表中一行數(shù)據(jù)的大小不能超過(guò)8060字節(jié)。例：一個(gè)數(shù)據(jù)表有10000行數(shù)據(jù)，每行3000字節(jié)。則每個(gè)數(shù)據(jù)頁(yè)可存放兩行數(shù)據(jù)，此表需要的空間為：(10000/2)*8KB=40MB*10數(shù)據(jù)存儲(chǔ)示意圖*111.3數(shù)據(jù)文件和日志文件數(shù)據(jù)文件用于存放數(shù)據(jù)庫(kù)數(shù)據(jù)。分為：主數(shù)據(jù)文件和輔助數(shù)據(jù)文件事務(wù)日志文件推薦擴(kuò)展名為.ldf。用于存放恢復(fù)數(shù)據(jù)庫(kù)的所有日志信息。每個(gè)數(shù)據(jù)庫(kù)必須至少有一個(gè)日志文件。最小為512KB，但最好不要小于1MB。*12數(shù)據(jù)文件主數(shù)據(jù)文件：推薦擴(kuò)展名是.mdf包含數(shù)據(jù)庫(kù)的系統(tǒng)信息，并可存放用戶(hù)數(shù)據(jù)每個(gè)數(shù)據(jù)庫(kù)都有且僅有一個(gè)主數(shù)據(jù)文件。輔助數(shù)據(jù)文件：推薦擴(kuò)展名是.ndf。一個(gè)數(shù)據(jù)庫(kù)可以包含0~n個(gè)輔助數(shù)據(jù)文件。輔助數(shù)據(jù)文件可以分別建立不同的磁盤(pán)上。*131.4數(shù)據(jù)庫(kù)文件的屬性文件名及其位置初始大小增長(zhǎng)方式最大大小*14文件名及其位置每個(gè)數(shù)據(jù)文件和日志文件都具有一個(gè)邏輯文件名和物理存儲(chǔ)位置（包括物理文件名）。一般情況下，如果有多個(gè)數(shù)據(jù)文件，則為了獲得更好的性能，建議將文件分散存儲(chǔ)在多個(gè)磁盤(pán)上。*15初始大小主數(shù)據(jù)文件的初始大小時(shí)，其大小不能小于model數(shù)據(jù)庫(kù)主文件的大小。因?yàn)橄到y(tǒng)是將model數(shù)據(jù)庫(kù)中主數(shù)據(jù)文件的內(nèi)容拷貝到用戶(hù)數(shù)據(jù)庫(kù)的主數(shù)據(jù)文件上。*16增長(zhǎng)方式如果需要的話(huà)，可以指定文件是否自動(dòng)增長(zhǎng)。默認(rèn)配置為自動(dòng)增長(zhǎng)，即當(dāng)數(shù)據(jù)庫(kù)的空間用完后，系統(tǒng)自動(dòng)擴(kuò)大數(shù)據(jù)庫(kù)的空間，以防止由于數(shù)據(jù)庫(kù)空間用完而造成的不能進(jìn)行數(shù)據(jù)操作的錯(cuò)誤。*17最大大小指文件增長(zhǎng)的最大空間限制。默認(rèn)情況是無(wú)限制。建議設(shè)定允許文件增長(zhǎng)的最大空間大小，以防止因文件無(wú)限制增長(zhǎng)而將磁盤(pán)空間用完。*182創(chuàng)建數(shù)據(jù)庫(kù)可以在SQLServerManagementStudio（SSMS）工具中用圖形化的方式實(shí)現(xiàn)，也可以通過(guò)T-SQL語(yǔ)句實(shí)現(xiàn)。*19T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)CREATEDATABASEdatabase_name[ON--數(shù)據(jù)文件[PRIMARY][<filespec>[,...n][,<filegroup>[,...n]][LOGON–日志文件{<filespec>[,...n]}]]]*20T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)（續(xù)）<filespec>::={(NAME=logical_file_name,--邏輯名FILENAME={‘os_file_name’|‘filestream_path'}--物理名[,SIZE=size[KB|MB|GB|TB]]--初始大小[,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}]--最大大小[,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]]--增長(zhǎng)方式，0為不增長(zhǎng))[,...n]}*21T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)（續(xù)）<filegroup>::={FILEGROUPfilegroup_name[DEFAULT]<filespec>[,...n]}*22示例1例1．創(chuàng)建一個(gè)名字為“學(xué)生管理數(shù)據(jù)庫(kù)”的數(shù)據(jù)庫(kù)，其他選項(xiàng)均采用默認(rèn)設(shè)置。

CREATEDATABASE學(xué)生管理數(shù)據(jù)庫(kù)*23示例2CREATEDATABASERShDBON(NAME=RShDB_Data,FILENAME='D:\RShDB_Data\RShDB_Data.mdf',SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=RShDB_log,FILENAME='D:\RShDB_Data\RShDB_log.ldf',SIZE=3,MAXSIZE=12,FILEGROWTH=2)*24示例3CREATEDATABASEstudentsONPRIMARY(NAME=students,FILENAME='F:\Data\students.mdf',SIZE=3MB,MAXSIZE=UNLIMITED),(NAME=students_data1,FILENAME='D:\Data\students_data1.ndf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)*25示例3（續(xù)）LOGON(NAME=students_log,

FILENAME='F:\Data\students_log.ldf',SIZE=2MB,MAXSIZE=6MB,FILEGROWTH=10%)*263查看和設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)數(shù)據(jù)庫(kù)選項(xiàng)是指在數(shù)據(jù)庫(kù)范圍內(nèi)有效的一些參數(shù)，用于控制這個(gè)數(shù)據(jù)庫(kù)的某些特性和行為。所有的數(shù)據(jù)庫(kù)選項(xiàng)只能取True或False。*27設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)窗口*284修改數(shù)據(jù)庫(kù)擴(kuò)大數(shù)據(jù)庫(kù)空間；縮小數(shù)據(jù)庫(kù)空間；添加和刪除文件。*29擴(kuò)大數(shù)據(jù)庫(kù)空間擴(kuò)大數(shù)據(jù)庫(kù)空間有兩種方法，擴(kuò)大數(shù)據(jù)庫(kù)中已有文件的大小為數(shù)據(jù)庫(kù)添加新的文件這兩種方法均可在SQLServerManagementStudio中用圖形化的方法實(shí)現(xiàn)，也可以用T-SQL語(yǔ)句實(shí)現(xiàn)。*30修改數(shù)據(jù)庫(kù)的語(yǔ)句ALTERDATABASEdatabase_name{

<add_or_modify_files>}<add_or_modify_files>::={

ADDFILE<filespec>[,...n]--添加數(shù)據(jù)文件

[TOFILEGROUP{filegroup_name|DEFAULT}]

|ADDLOGFILE<filespec>[,...n]—添加日志文件

|MODIFYFILE<filespec>--修改文件*31示例1例1．為“RShDB”數(shù)據(jù)庫(kù)添加一個(gè)新的數(shù)據(jù)文件，邏輯文件名為：RShDB_Data2，物理存儲(chǔ)位置為：E:\Data文件夾下，物理文件名為：RShDB_Data2.ndf,初始大小為6MB，不自動(dòng)增長(zhǎng)。ALTERDATABASERShDBADDFILE(NAME=RShDB_Data2,FILENAME='E:\Data\RShDB_Data2.ndf',SIZE=6MB,FILEGROWTH=0)*32示例2例2．增加students數(shù)據(jù)庫(kù)中students文件的初始大小，將其初始大小改為8MB。ALTERDATABASEstudentsMODIFYFILE(NAME=students,SIZE=8MB)*33示例3例3.為students數(shù)據(jù)庫(kù)加添加一個(gè)新的日志文件。ALTERDATABASEstudentsADDLOGFILE(NAME=students_log1,FILENAME='D:\Data\students_log1.ldf',SIZE=2MB,FILEGROWTH=1MB,MAXSIZE=10MB)*34收縮數(shù)據(jù)庫(kù)空間就是釋放數(shù)據(jù)庫(kù)中未使用的空間?？梢允湛s數(shù)據(jù)文件和事務(wù)日志文件?？梢猿山M或單獨(dú)地手動(dòng)收縮數(shù)據(jù)庫(kù)文件?？梢酝ㄟ^(guò)設(shè)置數(shù)據(jù)庫(kù)選項(xiàng)，使其按照指定的間隔自動(dòng)收縮。*35收縮數(shù)據(jù)庫(kù)（續(xù)）文件的收縮都是從末尾開(kāi)始的。如果文件中被釋放的空間部分包含使用過(guò)的頁(yè)，則數(shù)據(jù)庫(kù)引擎先將這些頁(yè)重新放置到保留的空間部分，然后再進(jìn)行收縮。只能將數(shù)據(jù)庫(kù)收縮到?jīng)]有剩余的可用空間為止。*36自動(dòng)收縮只需將該數(shù)據(jù)庫(kù)的“自動(dòng)收縮”（AUTO_SHRINK）選項(xiàng)設(shè)置為“ON”即可（在數(shù)據(jù)庫(kù)屬性窗口的“選項(xiàng)”對(duì)應(yīng)的窗口中，在“自動(dòng)”部分的“自動(dòng)收縮”選項(xiàng)），數(shù)據(jù)庫(kù)引擎會(huì)定期檢查數(shù)據(jù)庫(kù)空間的使用情況，并減少數(shù)據(jù)庫(kù)中文件的大小。*37手動(dòng)收縮兩種收縮方式：收縮數(shù)據(jù)庫(kù)中某個(gè)文件的大小，收縮整個(gè)數(shù)據(jù)庫(kù)的大小。當(dāng)收縮整個(gè)數(shù)據(jù)庫(kù)空間的大小時(shí)，收縮后各文件的大小不能小于創(chuàng)建這些文件時(shí)指定的初始大小。*38收縮整個(gè)數(shù)據(jù)庫(kù)大小的SQL語(yǔ)句DBCCSHRINKDATABASE('database_name'|database_id|0--當(dāng)前正在使用的數(shù)據(jù)庫(kù)[,target_percent]--剩余百分比[,{NOTRUNCATE|TRUNCATEONLY}])NOTRUNCATE|TRUNCATEONLY：釋放空間是否給操作系統(tǒng)*39收縮指定文件大小的SQL語(yǔ)句DBCCSHRINKFILE({'file_name'|file_id}{[,EMPTYFILE]--清空該文件|[[,target_size]--目標(biāo)大小[,{NOTRUNCATE|TRUNCATEONLY}]]})*40數(shù)據(jù)庫(kù)文件使用情況數(shù)據(jù)文件按比例填充數(shù)據(jù)，各文件存儲(chǔ)的數(shù)據(jù)量與文件的可用空間成正比，這種方式使得所有數(shù)據(jù)文件幾乎是同時(shí)被填滿(mǎn)的。日志文件是填充到滿(mǎn)的策略,即先填充第一個(gè)日志文件，第一個(gè)日志文件填滿(mǎn)后，再填充第二個(gè)日志文件，依此類(lèi)推。*41添加文件向數(shù)據(jù)庫(kù)添加文件時(shí)，可以指定文件的初始大小存放位置增長(zhǎng)方式等屬性同創(chuàng)建數(shù)據(jù)庫(kù)時(shí)指定文件屬性方法相同。*42刪除文件刪除數(shù)據(jù)或日志文件是將該文件從數(shù)據(jù)庫(kù)中刪除。只有當(dāng)文件中沒(méi)有數(shù)據(jù)或事務(wù)日志信息，文件完全為空時(shí)，才可以從數(shù)據(jù)庫(kù)中刪除該文件。*43刪除文件的T-SQL語(yǔ)句ALTERDATABASEdatabase_nameREMOVEFILElogical_file_name例1.刪除students數(shù)據(jù)庫(kù)中的students_data1文件。ALTERDATABASEstudentsREMOVEFILEstudents_data1*445刪除數(shù)據(jù)庫(kù)刪除一個(gè)數(shù)據(jù)庫(kù)，也就刪除了該數(shù)據(jù)庫(kù)的全部對(duì)象，包括數(shù)據(jù)文件和日志文件也被從磁盤(pán)上刪除。一旦刪除數(shù)據(jù)庫(kù)，它即被永久刪除。*45刪除數(shù)據(jù)庫(kù)的T-SQL語(yǔ)句DROPDATABASEdatabase_name[,…n]例1．刪除studnets數(shù)據(jù)庫(kù)。DROPDATABASEstudents例2．刪除“學(xué)生管理數(shù)據(jù)庫(kù)”和“RShDB”數(shù)據(jù)庫(kù)。DROPDATABASE學(xué)生管理數(shù)據(jù)庫(kù),RShDB*466分離和附加數(shù)據(jù)庫(kù)可以實(shí)現(xiàn)將數(shù)據(jù)庫(kù)從一臺(tái)計(jì)算機(jī)移動(dòng)到另一臺(tái)計(jì)算機(jī)，而不需要重新創(chuàng)建數(shù)據(jù)庫(kù)?？梢苑蛛x數(shù)據(jù)庫(kù)的數(shù)據(jù)文件和事務(wù)日志文件，然后將它們重新附加到其他SQLServer實(shí)例中。*47分離數(shù)據(jù)庫(kù)分離數(shù)據(jù)庫(kù)是指將數(shù)據(jù)庫(kù)從SQLServer實(shí)例中刪除，但不刪除數(shù)據(jù)庫(kù)中的數(shù)據(jù)文件和事務(wù)日志文件。分離數(shù)據(jù)庫(kù)會(huì)保持?jǐn)?shù)據(jù)庫(kù)的數(shù)據(jù)文件和日志文件的完整和一致。用戶(hù)不能使用被分離的數(shù)據(jù)庫(kù)。*48分離數(shù)據(jù)庫(kù)的語(yǔ)句sp_detach_db[@dbname=]'dbname'

[,[@skipchecks=]'skipchecks'][,[@KeepFulltextIndexFile=]'KeepFulltextIndexFile'][@skipchecks=]'skipchecks'：默認(rèn)為NULL。True：跳過(guò)“更新統(tǒng)計(jì)信息”；false：運(yùn)行“更新統(tǒng)計(jì)信息”，則指定。[@KeepFulltextIndexFile=]‘KeepFulltextIndexFile’:是否保留“全文目錄”。*49示例例．分離students數(shù)據(jù)庫(kù)，并跳過(guò)“更新統(tǒng)計(jì)信息”。EXECsp_detach_db'students','true'*50附加數(shù)據(jù)庫(kù)將分離的數(shù)據(jù)庫(kù)重