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

1、第5章 數(shù)據(jù)庫(kù)創(chuàng)建和管理,創(chuàng)建數(shù)據(jù)庫(kù)和表是SQL Server最基本的功能,5.1 SQL SERVER 2005的體系結(jié)構(gòu),5.1.1SQL Server 2005服務(wù)器的組成,,,,,,,,SQLServer2005客戶機(jī),網(wǎng)絡(luò),存儲(chǔ)引擎關(guān)系引擎,Master系統(tǒng)數(shù)據(jù)庫(kù),MSDB系統(tǒng)數(shù)據(jù)庫(kù),Model系統(tǒng)數(shù)據(jù)庫(kù),Tempdb系統(tǒng)數(shù)據(jù)庫(kù),MSsqlsystemresource系統(tǒng)數(shù)據(jù)庫(kù),用戶數(shù)據(jù)庫(kù),RDBMS,1.數(shù)據(jù)庫(kù)引擎的體系

2、結(jié)構(gòu)數(shù)據(jù)庫(kù)引擎的工作流程,,,用戶界面處理,,查詢處理,事務(wù)處理,事務(wù)調(diào)度,內(nèi)存緩沖管理,存儲(chǔ)管理,故障恢復(fù),,,,,,,,,,,創(chuàng)建和維護(hù),,SQL Server 2005客戶機(jī),SQL Server 2005數(shù)據(jù)庫(kù)引擎,SQL Server 2005數(shù)據(jù)庫(kù),SQL查詢,結(jié)果,,,,用戶,管理維護(hù)命令,,,結(jié)果,,DBA,數(shù)據(jù)字典,2.SQL Server 2005數(shù)據(jù)庫(kù)的體系結(jié)構(gòu)　　　　從物理角度和邏輯角度兩個(gè)方面考察SQ

3、L Server 2005數(shù)據(jù)庫(kù)分為系統(tǒng)數(shù)據(jù)庫(kù)和用戶數(shù)據(jù)庫(kù)兩大類系統(tǒng)數(shù)據(jù)庫(kù)：master、model、msdb、mssqlsystemresource和tempdb用戶數(shù)據(jù)庫(kù)：用戶根據(jù)需要在SQL Server 2005服務(wù)器上創(chuàng)建的數(shù)據(jù)管理單位,１．從邏輯角度看SQL Server 2005數(shù)據(jù)庫(kù)　　　從邏輯角度看SQL Server 2005數(shù)據(jù)庫(kù)呈現(xiàn)為各種數(shù)據(jù)對(duì)象。這些數(shù)據(jù)對(duì)象用于編寫數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)和管理數(shù)據(jù)庫(kù)的單位。　

4、　　　　　SQL Server 2005的數(shù)據(jù)庫(kù)對(duì)象主要包括：表、視圖、索引、數(shù)據(jù)類型、存儲(chǔ)過(guò)程、觸發(fā)器。,2.從物理角度看SQL Server 2005數(shù)據(jù)庫(kù)　　物理角度的SQL Server 2005數(shù)據(jù)庫(kù)呈現(xiàn)為各種操作系統(tǒng)文件．　　默認(rèn)的安裝路徑下　　SQL Server 2005的每個(gè)數(shù)據(jù)庫(kù)有兩類文件，缺一不可。 數(shù)據(jù)文件：用于存放所有的數(shù)據(jù)和數(shù)據(jù)庫(kù)中所建立的所有邏輯數(shù)據(jù)對(duì)象 主數(shù)據(jù)文件　數(shù)

5、據(jù)庫(kù)的關(guān)鍵文件，每個(gè)數(shù)據(jù)庫(kù)必須且僅能有一個(gè)主數(shù)據(jù)文件，默認(rèn)擴(kuò)展名為.mdf 輔數(shù)據(jù)文件　用于存儲(chǔ)未包含在主文件內(nèi)的其他數(shù)據(jù).ndf 日志文件:記錄了用戶對(duì)數(shù)據(jù)庫(kù)進(jìn)行的所有操作，每個(gè)數(shù)據(jù)庫(kù)至少有一個(gè)日志文件，.ldf,3.SQL Server 2005的數(shù)據(jù)文件(１)數(shù)據(jù)文件是如何存儲(chǔ)的　　數(shù)據(jù)文件可看成由若干標(biāo)準(zhǔn)64KB大小的區(qū)組成，每個(gè)區(qū)是由８?jìng)€(gè)８ＫＢ大小的標(biāo)準(zhǔn)頁(yè)組成的(２)數(shù)據(jù)頁(yè)的類型　 SQL

6、 Server 2005每個(gè)數(shù)據(jù)文件的基本存儲(chǔ)單位為８ＫＢ的連續(xù)空間，稱為一個(gè)數(shù)據(jù)頁(yè)．　　 數(shù)據(jù)頁(yè)　　　索引頁(yè)　　　文本／圖象頁(yè)　　　全局、共享全局分配映射頁(yè) 可用空間頁(yè) 索引分配映射表頁(yè) 大容量更改映射表頁(yè) 差異更改映射表頁(yè),4.SQL Server 2005的日志文件主要作用:(1).恢復(fù)個(gè)別事務(wù)(2).啟動(dòng)時(shí)恢復(fù)尚未完成的事務(wù)(3).將數(shù)據(jù)庫(kù)\文件回滾到故障點(diǎn)(4).事務(wù)復(fù)制(5

7、).備用服務(wù)器,文件組是SQL Server 2005數(shù)據(jù)文件的一種邏輯管理單位,若干個(gè)分布在不同的磁盤驅(qū)動(dòng)器上的數(shù)據(jù)文件可以組織成一個(gè)文件組.建立文件組的目的： （1）可以更好地分配和管理存儲(chǔ)空間，通過(guò)控制在特定磁盤驅(qū)動(dòng)器上單獨(dú)放置數(shù)據(jù)和索引來(lái)提高數(shù)據(jù)庫(kù)的性能 （2）擴(kuò)充存儲(chǔ)空間,五、SQL Server 2005系統(tǒng)數(shù)據(jù)庫(kù)1.Master系統(tǒng)數(shù)據(jù)庫(kù) 最重要的數(shù)據(jù)庫(kù),存儲(chǔ)的是SQL Ser

8、ver 2005的系統(tǒng)級(jí)信息,包括實(shí)例范圍的元數(shù)據(jù)(如登錄帳戶)端點(diǎn)、鏈接服務(wù)器和系統(tǒng)配置設(shè)置2．Model系統(tǒng)數(shù)據(jù)庫(kù)　　存儲(chǔ)了所有用戶數(shù)據(jù)庫(kù)和tempdb數(shù)據(jù)庫(kù)的模板．3. Msdb系統(tǒng)數(shù)據(jù)庫(kù)　　SQL Server 2005代理服務(wù)使用的數(shù)據(jù)庫(kù)，為警報(bào)、作業(yè)、任務(wù)調(diào)度和記錄操作員的操作提供存儲(chǔ)空間,4.Tempdb系統(tǒng)數(shù)據(jù)庫(kù)　　臨時(shí)數(shù)據(jù)庫(kù)，為所有的臨時(shí)表、臨時(shí)存儲(chǔ)過(guò)程，以及其他的臨時(shí)操作系統(tǒng)的空間。5. Mssqls

9、ystemresource(資源)系統(tǒng)數(shù)據(jù)庫(kù)　　只讀數(shù)據(jù)庫(kù)，它包含了SQL server 2005的所有系統(tǒng)對(duì)象。,5.2 界面創(chuàng)建用戶數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)預(yù)算數(shù)據(jù)庫(kù)用戶：?jiǎn)斡脩?、多用戶，用戶?quán)限數(shù)據(jù)庫(kù)使用中的數(shù)據(jù)容量：數(shù)據(jù)庫(kù)的初始大小、增長(zhǎng)方式、增長(zhǎng)極限數(shù)據(jù)庫(kù)性能:數(shù)據(jù)庫(kù)的大小與硬件的平衡，是否使用文件組、索引數(shù)據(jù)庫(kù)文件的存儲(chǔ)位置、路徑和屬性數(shù)據(jù)庫(kù)的維護(hù);數(shù)據(jù)庫(kù)的備份與恢復(fù),用戶數(shù)據(jù)庫(kù)的命名規(guī)則： 名稱長(zhǎng)度為1～128

10、 名稱的第一個(gè)字符必須是一個(gè)字母或者“－”、“@”和“?！敝械娜我庖粋€(gè)字符。 在中文版SQL SERVER 2005中，可以直接使用中文名稱 名稱中不能有空格，不允許使用SQL SERVER 2005的保留字,一、數(shù)據(jù)庫(kù)的創(chuàng)建、修改和刪除創(chuàng)建數(shù)據(jù)庫(kù)（1）通過(guò)管理器2. 修改數(shù)據(jù)庫(kù)（1）改變數(shù)據(jù)文件的大小和增長(zhǎng)方式（2）增加數(shù)據(jù)文件（3）刪除數(shù)據(jù)文件（4）增加和刪除文件組3 刪除數(shù)據(jù)庫(kù),5.3 命令方式創(chuàng)建數(shù)據(jù)庫(kù)

11、 除了使用管理器的 圖形界面 創(chuàng)建數(shù)據(jù)庫(kù)和表外，還可以使用T-SQL語(yǔ)句來(lái)創(chuàng)建數(shù)據(jù)庫(kù)和表，命令方式更為靈活。 一、使用CREATE DATABASE 創(chuàng)建數(shù)據(jù)庫(kù) 一個(gè)示例： 創(chuàng)建一個(gè)名為XSCJ1的數(shù)據(jù)庫(kù)，其初始大小為5MB，最大大小為50MB，允許數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，增長(zhǎng)方式為按10%比例增長(zhǎng)，日志文件初始大小為2MB，最大可增長(zhǎng)到5MB，按1MB增長(zhǎng)。 啟動(dòng)查詢分析器，進(jìn)入查詢分析器界面，將如下T-SQL語(yǔ)句輸入：,

12、CREATE DATABASE XSCJ1 ON (NAME=‘XSCJ_DATA’, FILENAME=‘d:\ljy\DATA\XSCJ.mdf’, SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=10%) LOG ON (NAME=‘XSCJ_LOG’, FILENAME=‘‘d:\ljy\DATA\XSCJ _LOG.ldf’,

13、 SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB)GO,2.CREATE DATABASE 的語(yǔ)法：CREATE DATABASE 數(shù)據(jù)庫(kù)名 /*指定數(shù)據(jù)庫(kù)名*/[ON 子句] /*指定數(shù)據(jù)庫(kù)文件和文件組屬性*/[LOG ON 子句]

14、 /*指定日志文件屬性*/,ON 子句的格式：ON [PRIMARY][ [,…n] ][, [,…n] ]PRIMARY 用來(lái)指定主文件，如不指定，則諸文件中的第一個(gè)文件將成為 主文件，數(shù)據(jù)文件的描述主要給出文件的邏輯名、存儲(chǔ)路徑、大小及增長(zhǎng)特性。LOG ON 子句的格式同ON 子句例：創(chuàng)建一個(gè)名為TEST1的數(shù)據(jù)庫(kù)。它有兩個(gè)數(shù)據(jù)文件，其中主數(shù)據(jù)文件為10MB，最大大小為20MB，按

15、2MB增長(zhǎng)；一個(gè)輔數(shù)據(jù)文件為2MB，最大大小不限，按10%比例增長(zhǎng)。它還有兩個(gè)日志文件，大小均為5MB，最大大小均為10MB，按1MB增長(zhǎng)。,CREATE DATABASE TEST1 ON PRIMARY(NAME=‘TEST1_data1’, FILENAME=‘D:\LJY\DATA\TEST1_data.mdf’, size=10MB, MAXSIZE=20MB, FILEGROWTH=2M

16、B),(NAME=‘TEST1_data2’, filename=‘D:\LJY\DATA\test1_data2.ndf’, size=2mb, maxsize=unlimited, filegrowth= 10%),Log on (name=‘ test1_log1’, filename=‘D:\LJY\data\test1_log1.ldf’, size=5mb, maxsize=

17、10mb,filegrowth=1mb),(name=‘test1_log2’, filename=‘D:\LJY\data\test1_log2.ldf’, size=5mb, maxsize=10mb, filegrowth=1mb)Go,例：創(chuàng)建一個(gè)具有3個(gè)文件組的數(shù)據(jù)庫(kù)TEST3.主文件組包括TEST3-dat1和TEST3-dat2，文件初始大小都為20MB，最大大小為60MB，按5MB增長(zhǎng)；

18、第二個(gè)文件組名為TEST3Group1，包括文件TEST3_dat3和TEST3_dat4，文件初始大小均為10MB，最大大小為30MB，按10%增長(zhǎng)，第 3個(gè)文件組名為TEST3Group2，包括文件TEST3_dat5，文件 初始大小為10MB,最大大小為30MB，按15%增長(zhǎng)。該數(shù)據(jù)庫(kù)只有一個(gè)日志文件，初始大小為5MB，最大大小為15MB，按2MB增長(zhǎng),Create database TEST3 ONPRIMARY(NA

19、ME=‘TEST3_dat1’, FILENAME=‘E:\SQL\data\t3\TEST3_dat1.mdf’, size=20MB, MAXSIZE=60MB, filegrowth=5mb),(name=‘test3_dat2’, filename=‘E:\SQL\data\t3\TEST3_dat2.ndf, size=20mb, maxsize=60mb, f

20、ilegrowth=5mb),Filegroup test3group1,(name=‘test3_dat3’, filename=‘E:\SQL\data\t3\TEST3_dat3.ndf’, size=10MB, maxsize=30mb, filegrowth=10%),(name=‘test3_dat4’, filename=‘E:\SQL\data\t3\TEST3_dat4.ndf’

21、, size=10MB, maxsize=30mb, filegrowth=10%),Filegroup test3group2(name=‘test3_dat5’, filename=‘E:\SQL\data\t3\TEST3_dat5.ndf’, size=10MB, maxsize=30mb, filegrowth=15%),Log on(name=‘test3_log’

22、, filename=‘E:\SQL\data\t3\TEST3_log.ldf’, size=5MB, maxsize=15mb, filegrowth=2MB)GO,二、使用 ALTER DATABASE 修改數(shù)據(jù)庫(kù)使用ALTER DATABASE語(yǔ)句一次只能修改數(shù)據(jù)文件的一個(gè)屬性。 ALTER DATABASE database_name { add file[,…n][

23、TO FILEGROUP filegroup_name] | add LOGFILE[,…n] | remove file logical_file_name | add filegroup filegroup_name | remove filegroup filegroup_name |modify file

24、 |modify name=new_dbname},1.為數(shù)據(jù)庫(kù)增加數(shù)據(jù)文件:ADD FILE 例：為數(shù)據(jù)庫(kù)XSCJ增加數(shù)據(jù)文件XSCJBAK ALTER DATABASE XSCJ ADD FILE （name=xscjbak, filename=‘e:\sql\data\mssql\data\xscjbak_bak.ndf’, size=1mb,

25、 maxsize=2mb, filegrowth=5%） go,例：修改數(shù)據(jù)文件的兩個(gè)屬性Alter database xscj modify file (name=XSCJ_data maxsize=unlimited) goAlter database xscj modify file (name=XSCJ_data filegowth=5MB) g

26、o,2 刪除數(shù)據(jù)文件:remove file 例：刪除數(shù)據(jù)文件XSCJBAK: alter database xscj remove file xscjbak go,3 添加文件組，并向文件組中添加文件 alter database xscj add filegroup fgroupGoAlter database xscj add file (name=xscj_data2,

27、filegname=‘…\...\xscj_data2.ndf’, size=10mb, filegrowth=1mb)To filegroup fgroupgo,4添加日志文件alter database xscj add log file(name=…,Filename=…,Size=…,Maxsize=…,Filegrowth=…) go,三、 刪除數(shù)據(jù)庫(kù)：DROP DATABASE例：刪

28、除數(shù)據(jù)庫(kù)TEST1 DROP DATABASE TEST1 GO,5.4 管理SQL Server 2005數(shù)據(jù)庫(kù)一、數(shù)據(jù)庫(kù)狀態(tài)查看數(shù)據(jù)庫(kù)狀態(tài)：Select name,state,state_desc from sys.databasesONLINEOFFLINERESTORINGRECOVERINGRECOVERING PENDINGSUSPECTEMERGENCY,二、 數(shù)據(jù)庫(kù)文件的狀態(tài) 1.查看

29、數(shù)據(jù)庫(kù)文件的狀態(tài)select name,physical_name,type,type_desc,state,state_desc from sys.master_files2. 含義ONLINEOFFLINERECOVERINGRECOVERING PENDINGSUSPECTDEFUNCT,5.5 配置用戶數(shù)據(jù)庫(kù)1.查看數(shù)據(jù)庫(kù)的基本信息2.更改數(shù)據(jù)庫(kù)的所有者,3.調(diào)整數(shù)據(jù)庫(kù)文件的增長(zhǎng)屬性4.限制用戶

30、對(duì)數(shù)據(jù)庫(kù)的訪問(wèn),5.配置數(shù)據(jù)庫(kù)的故障恢復(fù)模型6.配置數(shù)據(jù)庫(kù)的排序規(guī)則7.設(shè)置數(shù)據(jù)庫(kù)的統(tǒng)計(jì)信息選項(xiàng) 數(shù)據(jù)庫(kù)的統(tǒng)計(jì)信息是指表和索引的存儲(chǔ)數(shù)據(jù)的信息。,5.6 分離和附加用戶數(shù)據(jù)庫(kù)5.7 收縮用戶數(shù)據(jù)庫(kù)和文件,寫出產(chǎn)品銷售數(shù)據(jù)庫(kù)（庫(kù)名為CPXS）和表T-SQL 語(yǔ)句：數(shù)據(jù)庫(kù)初始大小為10MB，最大大小為20MB，數(shù)據(jù)自動(dòng)增長(zhǎng)，增長(zhǎng)方式是按10%比例增長(zhǎng)；日志文件初始大小為2MB，最大大小為5MB，按1MB增長(zhǎng)，其余參