SQLserver數(shù)據(jù)庫創(chuàng)建管理

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

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

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

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