1、第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理第第2章章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理數(shù)據(jù)庫(kù)的創(chuàng)建和管理學(xué)習(xí)要點(diǎn)：學(xué)習(xí)要點(diǎn)： sql server數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu) 事務(wù)日志的概念 數(shù)據(jù)庫(kù)的創(chuàng)建、刪除、管理 2.1 基本概念基本概念 2.2 創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù) 2.3 數(shù)據(jù)庫(kù)的管理數(shù)據(jù)庫(kù)的管理 2.4 刪除數(shù)據(jù)庫(kù)刪除數(shù)據(jù)庫(kù) 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.1 基本概念基本概念 主要數(shù)據(jù)文件 數(shù)據(jù)文件v 數(shù)據(jù)庫(kù) 次要數(shù)據(jù)文件 事務(wù)日志文件主要數(shù)據(jù)文件用來存儲(chǔ)數(shù)據(jù)庫(kù)的啟動(dòng)信息、部分或全部數(shù)據(jù)，擴(kuò)展名為.mdf 每個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)主要數(shù)據(jù)文件次要數(shù)據(jù)文件用于保存所有主要數(shù)據(jù)文件中容納不下的數(shù)據(jù)，擴(kuò)展名為.ndf 用于存儲(chǔ)數(shù)據(jù)庫(kù)中

2、的所有對(duì)象 保存用于恢復(fù)數(shù)據(jù)庫(kù)的日志信息 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v 文件組 :將多個(gè)數(shù)據(jù)文件集合起來形成的一個(gè)整體 目的：便于進(jìn)行管理和數(shù)據(jù)的分配 類型： 主文件組：包含了所有的系統(tǒng)表 用戶自定義文件組 默認(rèn)文件組：包含所有在創(chuàng)建時(shí)沒有指定文件組的表、索引等數(shù)據(jù)庫(kù)對(duì)象注意：一個(gè)數(shù)據(jù)文件不能存在于兩個(gè)或兩個(gè)以上的文件組里，日志文件不屬于任何文件組第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.1.1 2.1.1 數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)數(shù)據(jù)庫(kù)的物理存儲(chǔ)結(jié)構(gòu)第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.1.2 2.1.2 事務(wù)日志事務(wù)日志 事務(wù)日志文件，用以記錄所有事務(wù)和每個(gè)事務(wù)對(duì)數(shù)據(jù)庫(kù)所做的修改，當(dāng)數(shù)據(jù)庫(kù)破壞時(shí)可以用事務(wù)日志恢復(fù)

3、數(shù)據(jù)庫(kù)內(nèi)容。 2.1.3 2.1.3 系統(tǒng)數(shù)據(jù)庫(kù)和示例數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)庫(kù)和示例數(shù)據(jù)庫(kù) 安裝sql server2000時(shí)，系統(tǒng)自動(dòng)創(chuàng)建4個(gè)系統(tǒng)數(shù)據(jù)庫(kù)和2個(gè)示例數(shù)據(jù)庫(kù)。 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v master master數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) master數(shù)據(jù)庫(kù)記錄了sql server系統(tǒng)的所有系統(tǒng)級(jí)別的信息，包括所有的登錄帳戶和密碼、系統(tǒng)的配置信息以及所有用戶定義數(shù)據(jù)庫(kù)的存儲(chǔ)位置、初始化信息。master是sql server中重要的數(shù)據(jù)庫(kù)，如果master數(shù)據(jù)庫(kù)受到損壞，sql server將不能啟動(dòng)。v modelmodel數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) model數(shù)據(jù)庫(kù)是建立新數(shù)據(jù)庫(kù)的模板，它包含了將復(fù)制到每

4、個(gè)新建數(shù)據(jù)庫(kù)中的系統(tǒng)表。第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v msdbmsdb數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) 該數(shù)據(jù)庫(kù)主要被sql server agent用于進(jìn)行復(fù)制、作業(yè)調(diào)度以及管理報(bào)警等活動(dòng)。v tempdbtempdb數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù) tempdb數(shù)據(jù)庫(kù)保存所有的臨時(shí)表、臨時(shí)數(shù)據(jù)以及臨時(shí)創(chuàng)建的存儲(chǔ)過程。這四個(gè)系統(tǒng)數(shù)據(jù)庫(kù)是運(yùn)行這四個(gè)系統(tǒng)數(shù)據(jù)庫(kù)是運(yùn)行sql serversql server的基礎(chǔ)。的基礎(chǔ)。v 示例數(shù)據(jù)庫(kù)示例數(shù)據(jù)庫(kù) pubs數(shù)據(jù)庫(kù)記錄一個(gè)虛擬的圖書出版公司的數(shù)據(jù)信息 northwind數(shù)據(jù)庫(kù)保存了一個(gè)虛擬的貿(mào)易公司的數(shù)據(jù)信息 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.2 創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù) 創(chuàng)建數(shù)據(jù)庫(kù)是創(chuàng)建表及其它

5、數(shù)據(jù)庫(kù)對(duì)象的第一步。數(shù)據(jù)庫(kù)的創(chuàng)建包括為數(shù)據(jù)庫(kù)確定名稱、決定數(shù)據(jù)庫(kù)大小，及在數(shù)據(jù)庫(kù)中用來存儲(chǔ)數(shù)據(jù)的文件。 2.2.1 2.2.1 使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫(kù)使用企業(yè)管理器創(chuàng)建數(shù)據(jù)庫(kù) （1）進(jìn)入sql server企業(yè)管理器，在左邊的目錄樹結(jié)構(gòu)中選擇“數(shù)據(jù)庫(kù)”文件夾 。 （2）單擊“操作”菜單中的“新建數(shù)據(jù)庫(kù)”命令或右鍵單擊“數(shù)據(jù)庫(kù)”文件夾，在彈出的快捷菜單上選擇“新建數(shù)據(jù)庫(kù)”命令 。系統(tǒng)打開“數(shù)據(jù)庫(kù)屬性”對(duì)話框。第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理 （3）在“常規(guī)”選項(xiàng)卡中輸入數(shù)據(jù)庫(kù)的名稱，假設(shè)為sales。輸入數(shù)據(jù)庫(kù)名稱第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理 （4）選擇“數(shù)據(jù)文件”選項(xiàng)卡，在該窗口中設(shè)置數(shù)據(jù)文件的屬性

6、。數(shù)據(jù)文件的邏輯名 文件位置 初始大小 文件所在的文件組 文件否允許自動(dòng)增長(zhǎng) 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理 （5）選擇“事務(wù)日志”選項(xiàng)卡，在該窗口中可設(shè)置日志文件的屬性。 （6）單擊“確定”按鈕，數(shù)據(jù)庫(kù)創(chuàng)建完成。 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.2.2 2.2.2 使用使用t-sqlt-sql語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)的t-sql語(yǔ)句的語(yǔ)法形式：create database database_name on ,.n , ,.n log on ,.n := primary ( name = logical_file_name , filename = os_file_name , si

7、ze = size , maxsize = max_size | unlimited , filegrowth = growth_increment ) ,.n :=filegroup filegroup_name ,.n 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v database_name：新創(chuàng)建的數(shù)據(jù)庫(kù)的名稱。v on：指出用來存儲(chǔ)數(shù)據(jù)庫(kù)中數(shù)據(jù)部分的磁盤文件（數(shù)據(jù)文件）。v primary：指定主文件組中的主文件。v log on：指定用來存儲(chǔ)數(shù)據(jù)庫(kù)日志的磁盤文件。v name = logical_file_name：指定數(shù)據(jù)文件或日志文件的邏輯名。v filename = os_file_name：

8、指定數(shù)據(jù)文件或日志文件的操作系統(tǒng)文件名，包括文件名和路徑。v size = size：指定數(shù)據(jù)文件或日志文件的初始大小，默認(rèn)單位為mb。第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v maxsize = max_size | unlimited ：指定數(shù)據(jù)文件或日志文件可以增長(zhǎng)到的最大容量，默認(rèn)單位為mb。 v filegrowth = growth_increment：指定數(shù)據(jù)文件或日志文件的增長(zhǎng)幅度，默認(rèn)單位為mb。例2-1 創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)sample。主文件為sample_dat。create database sampleon primary( name=sample_dat, filename=d:p

9、rogram filesmicrosoft sql servermssqldatasample_data.mdf, size=5, maxsize=50, filegrowth=10 ) 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理例2-2 創(chuàng)建一個(gè)只包含一個(gè)數(shù)據(jù)文件和一個(gè)日志文件的數(shù)據(jù)庫(kù)。該數(shù)據(jù)庫(kù)名為sales，數(shù)據(jù)文件的邏輯名為sales_data，數(shù)據(jù)文件的操作系統(tǒng)名為sales_data.mdf，初始大小為10mb，最大可增加至500mb，增幅為10；日志文件的邏輯名為sales_log，操作系統(tǒng)名為sales_log.ldf，初始大小為5mb，最大值為100mb，日志文件大小以2mb增幅增加。 cre

10、ate database sales on ( name=sales_data, filename=d:program filesmicrosoft sql servermssqldatasales_data.mdf, size=10mb, maxsize=500mb, filegrowth=10%)log on( name=sales_log, filename=d:program filesmicrosoft sql servermssqldatasales_log.ldf, size=5mb, maxsize=100mb, filegrowth=2mb) 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理例2-

11、3 創(chuàng)建一個(gè)包含多個(gè)數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫(kù)。該數(shù)據(jù)庫(kù)名為student，含有三個(gè)初始大小為10mb的數(shù)據(jù)文件和兩個(gè)8mb的日志文件。 create database studenton primary( name=std_dat1, filename=d:program filesmicrosoft sql servermssqldatastudent1.mdf, size=10mb, maxsize=200mb, filegrowth=20),( name=std_dat2, filename=d:program filesmicrosoft sql servermssqldatastu

12、dent2.ndf, size=10mb, maxsize=200mb, filegrowth=20),第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理( name=std_dat3, filename=d:program filesmicrosoft sql servermssqldatastudent3.ndf, size=10mb, maxsize=200mb, filegrowth=20)log on( name=std_log1, filename=d:program filesmicrosoft sql servermssqldatastdlog1.ldf, size=8mb, maxsize=100m

13、b, filegrowth=10mb),( name=std_log2, filename=d:program filesmicrosoft sql servermssqldatastdlog2.ldf, size=8mb, maxsize=100mb, filegrowth=10mb) 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理例2-4 創(chuàng)建一個(gè)包含兩個(gè)文件組的數(shù)據(jù)庫(kù)。該數(shù)據(jù)庫(kù)名為business，主文件組包含business_dat1和business_dat2兩個(gè)數(shù)據(jù)文件。文件組business_group包含文件數(shù)據(jù)文件business_dat3。該數(shù)據(jù)庫(kù)還包含一個(gè)日志文件business_log。

14、create database businesson primary( name=business_dat1, filename=d:program filesmicrosoft sql servermssqldatabusinessdat1.mdf, size=10mb, maxsize=50mb, filegrowth=10),( name=business_dat2, filename=d:program filesmicrosoft sql servermssqldatabusinessdat2.ndf, size=10mb, maxsize=500mb, filegrowth=10)

15、,第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理filegroup business_group( name=business_dat3, f i l e n a m e = d : p r o g r a m f i l e s m i c r o s o f t s q l servermssqldatabusinessdat3.ndf, size=10mb, maxsize=50mb, filegrowth=10%)log on( name=business_log, f i l e n a m e = d : p r o g r a m f i l e s m i c r o s o f t s q l

16、servermssqldatabusinesslog.ldf, size=8mb, maxsize=100mb, filegrowth=10mb)第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.3 數(shù)據(jù)庫(kù)的管理數(shù)據(jù)庫(kù)的管理2.3.1 2.3.1 查看數(shù)據(jù)庫(kù)查看數(shù)據(jù)庫(kù) 1用企業(yè)管理器查看數(shù)據(jù)庫(kù)的屬性 進(jìn)入sql server企業(yè)管理器，在左邊的目錄樹結(jié)構(gòu)中選中要查看的數(shù)據(jù)庫(kù)sales，單擊右鍵，在彈出的快捷菜單上單擊“屬性”命令，即可進(jìn)入sales數(shù)據(jù)庫(kù)的屬性窗口 。 該窗口共有6個(gè)選項(xiàng)卡，包括“常規(guī)”、“數(shù)據(jù)文件”、“事務(wù)日志”、“文件組”、“選項(xiàng)”及“權(quán)限”，我們可以根據(jù)需要進(jìn)入選擇不同的選項(xiàng)卡查看數(shù)據(jù)庫(kù)相應(yīng)

17、的信息。 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2用t-sql語(yǔ)句查看數(shù)據(jù)庫(kù)的屬性 使用系統(tǒng)存儲(chǔ)過程sp_helpdb可以查看某個(gè)數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的屬性。格式：sp_helpdb database_name 該存儲(chǔ)過程顯示了sales數(shù)據(jù)庫(kù)的名稱、大小、所有者、創(chuàng)建日期以及數(shù)據(jù)文件和日志文件等屬性。例2-5 查看數(shù)據(jù)庫(kù)sales的屬性。 sp_helpdb sales 例2-6 查看所有數(shù)據(jù)庫(kù)的屬性 sp_helpdb 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.3.2 2.3.2 修改數(shù)據(jù)庫(kù)修改數(shù)據(jù)庫(kù) 1用企業(yè)管理器修改數(shù)據(jù)庫(kù)屬性 進(jìn)入數(shù)據(jù)庫(kù)的屬性窗口 ，在屬性窗口中修改數(shù)據(jù)庫(kù)屬性。注意：

18、在刪除文件組時(shí)要求該文件組中不包含任何數(shù)據(jù)文件，否則sql server會(huì)給出錯(cuò)誤信息2用t-sql語(yǔ)句修改數(shù)據(jù)庫(kù)屬性 其語(yǔ)法形式為：第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理alter database database add file ,.n to filegroup filegroup_name | add log file ,.n | remove file logical_file_name | add filegroup filegroup_name | remove filegroup filegroup_name | modify file | modify name = new_dbnam

19、e | modify filegroup filegroup_name filegroup_property | name = new_filegroup_name 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v add file ,.n to filegroup filegroup_name ：表示向指定的文件組添加新的數(shù)據(jù)文件。vadd log file ,.n ：添加新的事務(wù)日志文件。vremove file logical_file_name：刪除某一文件。vadd filegroup filegroup_name：添加一個(gè)文件組。vremove filegroup filegroup_name：刪除某

20、一文件組。vmodify file ：修改某個(gè)文件的屬性。vmodify name = new_dbname：修改數(shù)據(jù)庫(kù)的名字 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v modify filegroup filegroup_name filegroup_property | name = new_filegroup_name ：修改某一文件組的屬性。例2-7 為數(shù)據(jù)庫(kù)sample添加一個(gè)數(shù)據(jù)文件sample_dat2和一個(gè)日志文件sample_log2。alter database sampleadd file( name=sample_dat2, filename=d:program filesmicr

21、osoft sql servermssqldatasample_dat2.ndf, size=4, maxsize=10, filegrowth=1 )alter database sampleadd log file( name=sample_log2, filename=d:program filesmicrosoft sql servermssqldatasample_log2.ldf, size=4, maxsize=10, filegrowth=1 )例2-8 為數(shù)據(jù)庫(kù)sample添加一個(gè)文件組user1，并向該文件組添加一個(gè)二個(gè)數(shù)據(jù)文件sampuser_dat1和sampuser_

22、dat2。 alter database sampleadd filegroup user1 alter database sampleadd file ( name=sampuser_dat1, filename=d:program filesmicrosoft sql servermssqldatasampuser_dat1.ndf, size=4, maxsize=10, filegrowth=1 ),( name=sampuser_dat2, filename=d:program filesmicrosoft sql servermssqldatasampuser_dat2.ndf,

23、size=4, maxsize=10, filegrowth=1 )to filegroup user1第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理例2-9 從數(shù)據(jù)庫(kù)sample中刪除文件sampuser_dat2。alter database sampleremove file sampuser_dat2例2-10 刪除數(shù)據(jù)庫(kù)sample中的文件組user1。alter database sampleremove file sampuser_dat1alter database sampleremove filegroup user1注意：在刪除文件組時(shí)必須先刪除文件組中所包含的文件。 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和

24、管理例2-11 修改數(shù)據(jù)庫(kù)sample中數(shù)據(jù)文件sample_dat2的屬性，將其初始大小改為10mb，最大容量改為80mb,增長(zhǎng)幅度改為5mb。alter database samplemodif file( name=sample_dat2, size=10, maxsize=80, filegrowth=5 )第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理例2-12 修改數(shù)據(jù)庫(kù)business中文件組business_group的屬性，將其改名為group1，并設(shè)置為default屬性（即該文件組為默認(rèn)文件組）。alter database businessmodify filegroup business

25、_group name=group1alter database businessmodify filegroup group1 default例2-13 將數(shù)據(jù)庫(kù)student改名為stud_teacher。alter database student modify name=stud_teacher 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.3.3 2.3.3 重命名數(shù)據(jù)庫(kù)重命名數(shù)據(jù)庫(kù)語(yǔ)法形式為：sp_renamedb old_name,new_nameold_name：數(shù)據(jù)庫(kù)的當(dāng)前名稱；new_name是數(shù)據(jù)庫(kù)的新名稱。例2-14 將數(shù)據(jù)庫(kù)business更名為company。 sp_renamed

26、b business,company 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2.3.4 2.3.4 數(shù)據(jù)庫(kù)的收縮數(shù)據(jù)庫(kù)的收縮 sql server2000允許收縮數(shù)據(jù)庫(kù)中的每個(gè)文件以刪除未使用的頁(yè)。數(shù)據(jù)文件和事務(wù)日志文件都可以收縮。 1自動(dòng)收縮（1）使用企業(yè)管理器設(shè)置自動(dòng)收縮數(shù)據(jù)庫(kù) 在數(shù)據(jù)庫(kù)的“屬性對(duì)話框”中，點(diǎn)擊“選項(xiàng)”選項(xiàng)卡，選中“自動(dòng)收縮”復(fù)選框，即可完成數(shù)據(jù)庫(kù)自動(dòng)收縮的設(shè)置。 設(shè)置自動(dòng)收縮第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理（2）t-sql語(yǔ)句自動(dòng)收縮數(shù)據(jù)庫(kù) 使用alter database語(yǔ)句可以將數(shù)據(jù)庫(kù)設(shè)為自動(dòng)收縮。當(dāng)數(shù)據(jù)庫(kù)中有足夠的可用空間時(shí)，就會(huì)發(fā)生收縮。其語(yǔ)法形式為： alter database

27、 database_name set auto_shrink on/off例2-15 將數(shù)據(jù)庫(kù)sales的收縮設(shè)為自動(dòng)收縮。 alter database sales set auto_shrink on注意：不能將整個(gè)數(shù)據(jù)庫(kù)收縮到比其原始大小還要小。第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理2手工收縮dbcc shrinkdatabase( database_name , target_percent , notruncate | truncateonly )v database_name：是要收縮的數(shù)據(jù)庫(kù)名稱。v target_percent：是數(shù)據(jù)庫(kù)收縮后的數(shù)據(jù)庫(kù)文件中所要的剩余可用空間百分比。v notruncate：被釋放的文件空間依然保持在數(shù)據(jù)庫(kù)文件的范圍內(nèi)。如果未指定，則被釋放的文件空間將被操作系統(tǒng)回收利用。 第2章 數(shù)據(jù)庫(kù)的創(chuàng)建和管理v truncateonly：將數(shù)據(jù)文件中的任何未使用的空間釋放給操作系統(tǒng)。例2-16 縮小sales數(shù)據(jù)庫(kù)的大小，使得該數(shù)據(jù)庫(kù)中的文件有20的可用空間。 dbcc shrinkdatabas