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

1、第5章 數(shù)據(jù)庫創(chuàng)建和管理,創(chuàng)建數(shù)據(jù)庫和表是SQL Server最基本的功能,5.1 SQL SERVER 2005的體系結(jié)構(gòu),5.1.1SQL Server 2005服務(wù)器的組成,,,,,,,,SQLServer2005客戶機(jī),網(wǎng)絡(luò),存儲引擎關(guān)系引擎,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,1.數(shù)據(jù)庫引擎的體系

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

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

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

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

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

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

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

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

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

11、 除了使用管理器的 圖形界面 創(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，最大可增長到5MB，按1MB增長。 啟動查詢分析器，進(jìn)入查詢分析器界面，將如下T-SQL語句輸入：,

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 的語法：CREATE DATABASE 數(shù)據(jù)庫名 /*指定數(shù)據(jù)庫名*/[ON 子句] /*指定數(shù)據(jù)庫文件和文件組屬性*/[LOG ON 子句]

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

15、2MB增長；一個輔數(shù)據(jù)文件為2MB，最大大小不限，按10%比例增長。它還有兩個日志文件，大小均為5MB，最大大小均為10MB，按1MB增長。,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)建一個具有3個文件組的數(shù)據(jù)庫TEST3.主文件組包括TEST3-dat1和TEST3-dat2，文件初始大小都為20MB，最大大小為60MB，按5MB增長；

18、第二個文件組名為TEST3Group1，包括文件TEST3_dat3和TEST3_dat4，文件初始大小均為10MB，最大大小為30MB，按10%增長，第 3個文件組名為TEST3Group2，包括文件TEST3_dat5，文件 初始大小為10MB,最大大小為30MB，按15%增長。該數(shù)據(jù)庫只有一個日志文件，初始大小為5MB，最大大小為15MB，按2MB增長,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ù)庫使用ALTER DATABASE語句一次只能修改數(shù)據(jù)文件的一個屬性。 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ù)庫增加數(shù)據(jù)文件:ADD FILE 例：為數(shù)據(jù)庫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ù)文件的兩個屬性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ù)庫：DROP DATABASE例：刪

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

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

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