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

1、網(wǎng)絡數(shù)據(jù)庫技術第第6章章 數(shù)據(jù)庫創(chuàng)建和管理數(shù)據(jù)庫創(chuàng)建和管理 創(chuàng)建數(shù)據(jù)庫和表是SQL Server最基本的功能 網(wǎng)絡數(shù)據(jù)庫技術6.1 SQL SERVER 2005的體系結構的體系結構6.1.1SQL Server 2005服務器的組成服務器的組成SQLServer2005客戶機網(wǎng)絡存儲引擎關系引擎Master系統(tǒng)數(shù)據(jù)庫MSDB系統(tǒng)數(shù)據(jù)庫Model系統(tǒng)數(shù)據(jù)庫Tempdb系統(tǒng)數(shù)據(jù)庫MSsqlsystemresource系統(tǒng)數(shù)據(jù)庫用戶數(shù)據(jù)庫RDBMS網(wǎng)絡數(shù)據(jù)庫技術1.數(shù)據(jù)庫引擎的體系結構數(shù)據(jù)庫引擎的體系結構數(shù)據(jù)庫引擎的工作流程數(shù)據(jù)庫引擎的工作流程網(wǎng)絡數(shù)據(jù)庫技術用戶界面處理查詢處理事務處理事務調(diào)度內(nèi)存

2、緩沖管理存儲管理故障恢復創(chuàng)建和維護SQL Server 2005客戶機SQL Server 2005數(shù)據(jù)庫引擎SQL Server 2005數(shù)據(jù)庫SQL查詢結果用戶管理維護命令結果DBA數(shù)據(jù)字典網(wǎng)絡數(shù)據(jù)庫技術2.SQL Server 2005數(shù)據(jù)庫的體系結構數(shù)據(jù)庫的體系結構從物理角度和邏輯角度兩個方面考察從物理角度和邏輯角度兩個方面考察SQL Server 2005數(shù)據(jù)庫分為系統(tǒng)數(shù)據(jù)庫和用戶數(shù)數(shù)據(jù)庫分為系統(tǒng)數(shù)據(jù)庫和用戶數(shù)據(jù)庫兩大類據(jù)庫兩大類系統(tǒng)數(shù)據(jù)庫：系統(tǒng)數(shù)據(jù)庫：master、model、msdb、mssqlsystemresource和和tempdb用戶數(shù)據(jù)庫：用戶根據(jù)需要在用戶數(shù)據(jù)庫：用戶

3、根據(jù)需要在SQL Server 2005服服務器上創(chuàng)建的數(shù)據(jù)管理單位務器上創(chuàng)建的數(shù)據(jù)管理單位網(wǎng)絡數(shù)據(jù)庫技術從邏輯角度看從邏輯角度看SQL Server 2005數(shù)據(jù)庫數(shù)據(jù)庫從邏輯角度看從邏輯角度看SQL Server 2005數(shù)據(jù)庫呈現(xiàn)為各種數(shù)據(jù)數(shù)據(jù)庫呈現(xiàn)為各種數(shù)據(jù)對象。這些數(shù)據(jù)對象用于編寫數(shù)據(jù)庫應用系統(tǒng)和管理數(shù)據(jù)對象。這些數(shù)據(jù)對象用于編寫數(shù)據(jù)庫應用系統(tǒng)和管理數(shù)據(jù)庫的單位。庫的單位。SQL Server 2005的數(shù)據(jù)庫對象主要包括：表、視圖、的數(shù)據(jù)庫對象主要包括：表、視圖、索引、數(shù)據(jù)類型、存儲過程、觸發(fā)器。索引、數(shù)據(jù)類型、存儲過程、觸發(fā)器。網(wǎng)絡數(shù)據(jù)庫技術2.從物理角度看從物理角度看SQL S

4、erver 2005數(shù)據(jù)庫數(shù)據(jù)庫物理角度的物理角度的SQL Server 2005數(shù)據(jù)庫呈現(xiàn)為各種操作系數(shù)據(jù)庫呈現(xiàn)為各種操作系統(tǒng)文件統(tǒng)文件默認的安裝路徑下默認的安裝路徑下SQL Server 2005的每個數(shù)據(jù)庫有兩類文件，缺一不可。的每個數(shù)據(jù)庫有兩類文件，缺一不可。 數(shù)據(jù)文件：用于存放所有的數(shù)據(jù)和數(shù)據(jù)庫中所建立的所有數(shù)據(jù)文件：用于存放所有的數(shù)據(jù)和數(shù)據(jù)庫中所建立的所有邏輯數(shù)據(jù)對象邏輯數(shù)據(jù)對象 主數(shù)據(jù)文件數(shù)據(jù)庫的關鍵文件，每個數(shù)據(jù)庫必須且主數(shù)據(jù)文件數(shù)據(jù)庫的關鍵文件，每個數(shù)據(jù)庫必須且僅能有一個主數(shù)據(jù)文件，默認擴展名為僅能有一個主數(shù)據(jù)文件，默認擴展名為.mdf 輔數(shù)據(jù)文件用于存儲未包含在主文件內(nèi)的其

5、他數(shù)輔數(shù)據(jù)文件用于存儲未包含在主文件內(nèi)的其他數(shù)據(jù)據(jù).ndf 日志文件日志文件:記錄了用戶對數(shù)據(jù)庫進行的所有操作，每個數(shù)記錄了用戶對數(shù)據(jù)庫進行的所有操作，每個數(shù)據(jù)庫至少有一個日志文件，據(jù)庫至少有一個日志文件，.ldf網(wǎng)絡數(shù)據(jù)庫技術3.SQL Server 2005的數(shù)據(jù)文件的數(shù)據(jù)文件()數(shù)據(jù)文件是如何存儲的數(shù)據(jù)文件是如何存儲的數(shù)據(jù)文件可看成由若干標準數(shù)據(jù)文件可看成由若干標準64KB大小的區(qū)組成，每個大小的區(qū)組成，每個區(qū)是由個大小的標準頁組成的區(qū)是由個大小的標準頁組成的()數(shù)據(jù)頁的類型數(shù)據(jù)頁的類型 SQL Server 2005每個數(shù)據(jù)文件的基本存儲單位為每個數(shù)據(jù)文件的基本存儲單位為的連續(xù)空間，稱

6、為一個數(shù)據(jù)頁的連續(xù)空間，稱為一個數(shù)據(jù)頁 數(shù)據(jù)頁數(shù)據(jù)頁索引頁索引頁文本圖象頁文本圖象頁全局、共享全局分配映射頁全局、共享全局分配映射頁 可用空間頁可用空間頁 索引分配映射表頁索引分配映射表頁 大容量更改映射表頁大容量更改映射表頁 差異更改映射表差異更改映射表頁頁網(wǎng)絡數(shù)據(jù)庫技術4.SQL Server 2005的日志文件的日志文件主要作用主要作用:(1).恢復個別事務恢復個別事務(2).啟動時恢復尚未完成的事務啟動時恢復尚未完成的事務(3).將數(shù)據(jù)庫將數(shù)據(jù)庫文件回滾到故障點文件回滾到故障點(4).事務復制事務復制(5).備用服務器備用服務器網(wǎng)絡數(shù)據(jù)庫技術 文件組是文件組是SQL Server 20

7、05數(shù)據(jù)文件的一種邏輯管理單數(shù)據(jù)文件的一種邏輯管理單位位,若干個分布在不同的磁盤驅(qū)動器上的數(shù)據(jù)文件可以組若干個分布在不同的磁盤驅(qū)動器上的數(shù)據(jù)文件可以組織成一個文件組織成一個文件組.文件組可以跨越不同的數(shù)據(jù)庫。文件組可以跨越不同的數(shù)據(jù)庫。建立文件組的目的：建立文件組的目的： （1）可以更好地分配和管理存儲空間，通過控制在特定）可以更好地分配和管理存儲空間，通過控制在特定磁盤驅(qū)動器上單獨放置數(shù)據(jù)和索引來提高數(shù)據(jù)庫的性能磁盤驅(qū)動器上單獨放置數(shù)據(jù)和索引來提高數(shù)據(jù)庫的性能 （2）擴充存儲空間）擴充存儲空間網(wǎng)絡數(shù)據(jù)庫技術 五、五、SQL Server 2005系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫1.Master系統(tǒng)數(shù)據(jù)庫

8、系統(tǒng)數(shù)據(jù)庫 最重要的數(shù)據(jù)庫最重要的數(shù)據(jù)庫,存儲的是存儲的是SQL Server 2005的系統(tǒng)級的系統(tǒng)級信息信息,包括實例范圍的元數(shù)據(jù)包括實例范圍的元數(shù)據(jù)(如登錄帳戶如登錄帳戶)端點、鏈接服務端點、鏈接服務器和系統(tǒng)配置設置器和系統(tǒng)配置設置2Model系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫存儲了所有用戶數(shù)據(jù)庫和存儲了所有用戶數(shù)據(jù)庫和tempdb數(shù)據(jù)庫的模板數(shù)據(jù)庫的模板3. Msdb系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫SQL Server 2005代理服務使用的數(shù)據(jù)庫，為警報、作代理服務使用的數(shù)據(jù)庫，為警報、作業(yè)、任務調(diào)度和記錄操作員的操作提供存儲空間業(yè)、任務調(diào)度和記錄操作員的操作提供存儲空間網(wǎng)絡數(shù)據(jù)庫技術4.Tempdb系統(tǒng)數(shù)據(jù)

9、庫系統(tǒng)數(shù)據(jù)庫臨時數(shù)據(jù)庫，為所有的臨時表、臨時存儲過程，以臨時數(shù)據(jù)庫，為所有的臨時表、臨時存儲過程，以及其他的臨時操作系統(tǒng)的空間。及其他的臨時操作系統(tǒng)的空間。5. Mssqlsystemresource(資源資源)系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫只讀數(shù)據(jù)庫，它包含了只讀數(shù)據(jù)庫，它包含了SQL server 2005的所有系統(tǒng)的所有系統(tǒng)對象。對象。網(wǎng)絡數(shù)據(jù)庫技術6.2 界面創(chuàng)建用戶數(shù)據(jù)庫數(shù)據(jù)庫預算數(shù)據(jù)庫預算.,數(shù)據(jù)庫用戶：單用戶、多用戶，用戶權限數(shù)據(jù)庫用戶：單用戶、多用戶，用戶權限數(shù)據(jù)庫使用中的數(shù)據(jù)容量：數(shù)據(jù)庫的初始數(shù)據(jù)庫使用中的數(shù)據(jù)容量：數(shù)據(jù)庫的初始大小、增長方式、增長極限大小、增長方式、增長極限數(shù)據(jù)庫性能

10、數(shù)據(jù)庫性能:數(shù)據(jù)庫的大小與硬件的平衡，數(shù)據(jù)庫的大小與硬件的平衡，是否使用文件組、索引是否使用文件組、索引數(shù)據(jù)庫文件的存儲位置、路徑和屬性數(shù)據(jù)庫文件的存儲位置、路徑和屬性數(shù)據(jù)庫的維護數(shù)據(jù)庫的維護;數(shù)據(jù)庫的備份與恢復數(shù)據(jù)庫的備份與恢復網(wǎng)絡數(shù)據(jù)庫技術用戶數(shù)據(jù)庫的命名規(guī)則：用戶數(shù)據(jù)庫的命名規(guī)則： 名稱長度為名稱長度為1128 名稱的第一個字符必須是一個字母或者名稱的第一個字符必須是一個字母或者“”、“”和和“”中的任意一個字符。中的任意一個字符。 在中文版在中文版SQL SERVER 2005中，可以直接中，可以直接使用中文名稱使用中文名稱 名稱中不能有空格，不允許使用名稱中不能有空格，不允許使用SQ

11、L SERVER 2005的保留字的保留字網(wǎng)絡數(shù)據(jù)庫技術一、數(shù)據(jù)庫的創(chuàng)建、修改和刪除1. 創(chuàng)建數(shù)據(jù)庫（1）通過管理器2. 修改數(shù)據(jù)庫（1）改變數(shù)據(jù)文件的大小和增長方式（2）增加數(shù)據(jù)文件（3）刪除數(shù)據(jù)文件（4）增加和刪除文件組3 刪除數(shù)據(jù)庫網(wǎng)絡數(shù)據(jù)庫技術 6.3 命令方式創(chuàng)建數(shù)據(jù)庫 除了使用管理器的 圖形界面 創(chuàng)建數(shù)據(jù)庫和表外，還可以使用T-SQL語句來創(chuàng)建數(shù)據(jù)庫和表，命令方式更為靈活。 一、使用CREATE DATABASE 創(chuàng)建數(shù)據(jù)庫 一個示例： 創(chuàng)建一個名為XSCJ1的數(shù)據(jù)庫，其初始大小為5MB，最大大小為50MB，允許數(shù)據(jù)庫自動增長，增長方式為按10%比例增長，日志文件初始大小為2MB，

12、最大可增長到5MB，按1MB增長。 啟動查詢分析器，進入查詢分析器界面，將如下T-SQL語句輸入：網(wǎng)絡數(shù)據(jù)庫技術 CREATE DATABASE XSCJ ON (NAME=XSCJ_DATA, FILENAME=d:ljyDATAXSCJ.mdf, SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=10%) LOG ON (NAME=XSCJ_LOG FILENAME=d:ljyDATAXSCJ _LOG.ldf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB)GO 網(wǎng)絡數(shù)據(jù)庫技術2.CREATE DATABASE 的語法：CREATE DA

13、TABASE 數(shù)據(jù)庫名 /*指定數(shù)據(jù)庫名*/ON 子句 /*指定數(shù)據(jù)庫文件和文件組屬性*/LOG ON 子句 /*指定日志文件屬性*/網(wǎng)絡數(shù)據(jù)庫技術ON 子句的格式：ON PRIMARY ,n , ,n PRIMARY 用來指定主文件，如不指定，則諸文件中的第一個文件將成為 主文件，數(shù)據(jù)文件的描述主要給出文件的邏輯名、存儲路徑、大小及增長特性。LOG ON 子句的格式同ON 子句例：創(chuàng)建一個名為TEST1的數(shù)據(jù)庫。它有兩個數(shù)據(jù)文件，其中主數(shù)據(jù)文件為10MB，最大大小為20MB，按2MB增長；一個輔數(shù)據(jù)文件為2MB，最大大小不限，按10%比例增長。它還有兩個日志文件，大小均為5MB，最大大小均為

14、10MB，按1MB增長。網(wǎng)絡數(shù)據(jù)庫技術CREATE DATABASE TEST1 ON PRIMARY(NAME=TEST1_data1, FILENAME=D:LJYDATATEST1_data.mdf, size=10MB, MAXSIZE=20MB, FILEGROWTH=2MB),(NAME=TEST1_data2, filename=D:LJYDATAtest1_data2.ndf, size=2mb, maxsize=unlimited, filegrowth= 10%)網(wǎng)絡數(shù)據(jù)庫技術Log on (name= test1_log1, filename=D:LJYdatatest1

15、_log1.ldf, size=5mb, maxsize=10mb,filegrowth=1mb)，(name=test1_log2, filename=D:LJYdatatest1_log2.ldf, size=5mb, maxsize=10mb, filegrowth=1mb)Go網(wǎng)絡數(shù)據(jù)庫技術例：例：創(chuàng)建一個具有創(chuàng)建一個具有3個文件組的數(shù)據(jù)庫個文件組的數(shù)據(jù)庫TEST3.主文件組包括主文件組包括TEST3_dat1和和TEST3_dat2，文件初始大小都為，文件初始大小都為20MB，最大大小為，最大大小為60MB，按，按5MB增長；第二個文件增長；第二個文件組名為組名為TEST3Group

16、1，包括文件，包括文件TEST3_dat3和和TEST3_dat4，文件初始大小均為，文件初始大小均為10MB，最大大小為，最大大小為30MB，按，按10%增長，第增長，第 3個文件組名為個文件組名為TEST3Group2，包括文件，包括文件TEST3_dat5，文件，文件 初始初始大小為大小為10MB,最大大小為最大大小為30MB，按，按15%增長。該數(shù)據(jù)增長。該數(shù)據(jù)庫只有一個日志文件，初始大小為庫只有一個日志文件，初始大小為5MB，最大大小為，最大大小為15MB，按，按2MB增長增長網(wǎng)絡數(shù)據(jù)庫技術Create database TEST3 ONPRIMARY(NAME=TEST3_dat1

17、, FILENAME=E:SQLdatat3TEST3_dat1.mdf, size=20MB, MAXSIZE=60MB, filegrowth=5mb),(name=test3_dat2, filename=E:SQLdatat3TEST3_dat2.ndf, size=20mb, maxsize=60mb, filegrowth=5mb),Filegroup test3group1網(wǎng)絡數(shù)據(jù)庫技術(name=test3_dat3, filename=E:SQLdatat3TEST3_dat3.ndf, size=10MB, maxsize=30mb, filegrowth=10%),(na

18、me=test3_dat4, filename=E:SQLdatat3TEST3_dat4.ndf, size=10MB, maxsize=30mb, filegrowth=10%),Filegroup test3group2(name=test3_dat5, filename=E:SQLdatat3TEST3_dat5.ndf, size=10MB, maxsize=30mb, filegrowth=15%)網(wǎng)絡數(shù)據(jù)庫技術Log on(name=test3_log, filename=E:SQLdatat3TEST3_log.ldf, size=5MB, maxsize=15mb, file

19、growth=2MB)GO網(wǎng)絡數(shù)據(jù)庫技術二、使用 ALTER DATABASE 修改數(shù)據(jù)庫使用ALTER DATABASE語句 ALTER DATABASE database_name add file,nTO 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_dbname網(wǎng)絡數(shù)據(jù)庫技術1.為數(shù)據(jù)庫增加數(shù)據(jù)文件

20、:ADD FILE 例：為數(shù)據(jù)庫XSCJ增加數(shù)據(jù)文件XSCJBAK ALTER DATABASE XSCJ ADD FILE （name=xscjbak, filename=e:sqldatamssqldataxscjbak_bak.ndf, size=1mb, maxsize=2mb, filegrowth=5%） go網(wǎng)絡數(shù)據(jù)庫技術例：修改數(shù)據(jù)文件的兩個屬性例：修改數(shù)據(jù)文件的兩個屬性Alter database xscj modify file (name=XSCJ_data maxsize=unlimited) goAlter database xscj modify file (nam

21、e=XSCJ_data filegowth=5MB) go網(wǎng)絡數(shù)據(jù)庫技術2 刪除數(shù)據(jù)文件:remove file 例：刪除數(shù)據(jù)文件XSCJBAK: alter database xscj remove file xscjbak go網(wǎng)絡數(shù)據(jù)庫技術3 添加文件組，并向文件組中添加文件添加文件組，并向文件組中添加文件 alter database xscj add filegroup fgroupGoAlter database xscj add file (name=xscj_data2, filegname=.xscj_data2.ndf, size=10mb, filegrowth=1mb)

22、To filegroup fgroupgo網(wǎng)絡數(shù)據(jù)庫技術4添加日志文件添加日志文件alter database xscj add log file(name=,Filename=,Size=,Maxsize=,Filegrowth=) go 網(wǎng)絡數(shù)據(jù)庫技術三、 刪除數(shù)據(jù)庫：DROP DATABASE例：刪除數(shù)據(jù)庫TEST1 DROP DATABASE TEST1 GO 網(wǎng)絡數(shù)據(jù)庫技術6.4 管理管理SQL Server 2005數(shù)據(jù)庫數(shù)據(jù)庫一、數(shù)據(jù)庫狀態(tài)一、數(shù)據(jù)庫狀態(tài)查看數(shù)據(jù)庫狀態(tài)：查看數(shù)據(jù)庫狀態(tài)：Select name,state,state_desc from sys.databasesONLINEOFFLINERESTORINGRECOVERINGRECOVERING PENDINGSUSPECTEMERGENCY網(wǎng)絡數(shù)據(jù)庫技術二、二、 數(shù)據(jù)庫文件的狀態(tài)數(shù)據(jù)庫文件的狀態(tài) 1.查看數(shù)據(jù)庫文件的狀態(tài)查看數(shù)據(jù)庫文件的狀態(tài)select name,physical_name,type,type_desc,state,state_desc from sys.master_files2. 含義含義ONLINEOFFLINERESTORINGREcovering PENDINGSUSPECTDEFUNCT網(wǎng)絡數(shù)據(jù)庫技術6.5 配置用戶數(shù)據(jù)庫配置