第2章數(shù)據(jù)庫的創(chuàng)建和管理

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

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

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

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

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

6、。數(shù)據(jù)文件的邏輯名 文件位置 初始大小 文件所在的文件組 文件否允許自動增長 第2章 數(shù)據(jù)庫的創(chuàng)建和管理 （5）選擇“事務(wù)日志”選項卡，在該窗口中可設(shè)置日志文件的屬性。 （6）單擊“確定”按鈕，數(shù)據(jù)庫創(chuàng)建完成。 第2章 數(shù)據(jù)庫的創(chuàng)建和管理2.2.2 2.2.2 使用使用t-sqlt-sql語句創(chuàng)建數(shù)據(jù)庫語句創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫的t-sql語句的語法形式：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ù)庫的創(chuàng)建和管理v database_name：新創(chuàng)建的數(shù)據(jù)庫的名稱。v on：指出用來存儲數(shù)據(jù)庫中數(shù)據(jù)部分的磁盤文件（數(shù)據(jù)文件）。v primary：指定主文件組中的主文件。v log on：指定用來存儲數(shù)據(jù)庫日志的磁盤文件。v name = logical_file_name：指定數(shù)據(jù)文件或日志文件的邏輯名。v filename = os_file_name：

8、指定數(shù)據(jù)文件或日志文件的操作系統(tǒng)文件名，包括文件名和路徑。v size = size：指定數(shù)據(jù)文件或日志文件的初始大小，默認單位為mb。第2章 數(shù)據(jù)庫的創(chuàng)建和管理v maxsize = max_size | unlimited ：指定數(shù)據(jù)文件或日志文件可以增長到的最大容量，默認單位為mb。 v filegrowth = growth_increment：指定數(shù)據(jù)文件或日志文件的增長幅度，默認單位為mb。例2-1 創(chuàng)建一個數(shù)據(jù)庫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ù)庫的創(chuàng)建和管理例2-2 創(chuàng)建一個只包含一個數(shù)據(jù)文件和一個日志文件的數(shù)據(jù)庫。該數(shù)據(jù)庫名為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ù)庫的創(chuàng)建和管理例2-

11、3 創(chuàng)建一個包含多個數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫。該數(shù)據(jù)庫名為student，含有三個初始大小為10mb的數(shù)據(jù)文件和兩個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ù)庫的創(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ù)庫的創(chuàng)建和管理例2-4 創(chuàng)建一個包含兩個文件組的數(shù)據(jù)庫。該數(shù)據(jù)庫名為business，主文件組包含business_dat1和business_dat2兩個數(shù)據(jù)文件。文件組business_group包含文件數(shù)據(jù)文件business_dat3。該數(shù)據(jù)庫還包含一個日志文件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ù)庫的創(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ù)庫的創(chuàng)建和管理2.3 數(shù)據(jù)庫的管理數(shù)據(jù)庫的管理2.3.1 2.3.1 查看數(shù)據(jù)庫查看數(shù)據(jù)庫 1用企業(yè)管理器查看數(shù)據(jù)庫的屬性 進入sql server企業(yè)管理器，在左邊的目錄樹結(jié)構(gòu)中選中要查看的數(shù)據(jù)庫sales，單擊右鍵，在彈出的快捷菜單上單擊“屬性”命令，即可進入sales數(shù)據(jù)庫的屬性窗口 。 該窗口共有6個選項卡，包括“常規(guī)”、“數(shù)據(jù)文件”、“事務(wù)日志”、“文件組”、“選項”及“權(quán)限”，我們可以根據(jù)需要進入選擇不同的選項卡查看數(shù)據(jù)庫相應(yīng)

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

18、在刪除文件組時要求該文件組中不包含任何數(shù)據(jù)文件，否則sql server會給出錯誤信息2用t-sql語句修改數(shù)據(jù)庫屬性 其語法形式為：第2章 數(shù)據(jù)庫的創(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ù)庫的創(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：添加一個文件組。vremove filegroup filegroup_name：刪除某

20、一文件組。vmodify file ：修改某個文件的屬性。vmodify name = new_dbname：修改數(shù)據(jù)庫的名字 第2章 數(shù)據(jù)庫的創(chuàng)建和管理v modify filegroup filegroup_name filegroup_property | name = new_filegroup_name ：修改某一文件組的屬性。例2-7 為數(shù)據(jù)庫sample添加一個數(shù)據(jù)文件sample_dat2和一個日志文件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ù)庫sample添加一個文件組user1，并向該文件組添加一個二個數(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ù)庫的創(chuàng)建和管理例2-9 從數(shù)據(jù)庫sample中刪除文件sampuser_dat2。alter database sampleremove file sampuser_dat2例2-10 刪除數(shù)據(jù)庫sample中的文件組user1。alter database sampleremove file sampuser_dat1alter database sampleremove filegroup user1注意：在刪除文件組時必須先刪除文件組中所包含的文件。 第2章 數(shù)據(jù)庫的創(chuàng)建和

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

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

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

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