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

1、第3章 創(chuàng)建和管理數(shù)據(jù)庫(kù),—— SQL Server 2005,SQL Server 2005安裝過(guò)程,使用有管理權(quán)限的帳戶登錄Windows服務(wù)器，確保沒(méi)有其他程序在前臺(tái)運(yùn)行。插入安裝光盤(pán)，啟動(dòng)安裝程序，系統(tǒng)顯示開(kāi)始界面。,,SQL Server 2005安裝過(guò)程,服務(wù)器組件、工具、聯(lián)機(jī)叢書(shū)和示例,SQL Server 2005安裝過(guò)程,,SQL Server 2005安裝過(guò)程,,實(shí)例是在計(jì)算機(jī)上安裝的一個(gè)“獨(dú)立”的服務(wù)器,SQL S

2、erver 2005安裝過(guò)程,SQL Server 實(shí)例 SQL Server 實(shí)例是在計(jì)算機(jī)上安裝的一個(gè)“獨(dú)立”的服務(wù)器，在同一臺(tái)計(jì)算機(jī)上可以安裝多個(gè)SQL Server實(shí)例，應(yīng)用程序可以同時(shí)連接同一個(gè)計(jì)算機(jī)上不同的實(shí)例。,SQL Server 2005安裝過(guò)程,SQL Server 實(shí)例實(shí)例以名稱(chēng)進(jìn)行區(qū)分，用戶可以指定實(shí)例名稱(chēng)，也可以使用默認(rèn)的實(shí)例名。因此SQL Server 有兩種實(shí)例：默認(rèn)實(shí)例和命名實(shí)例。默認(rèn)實(shí)

3、例：默認(rèn)情況下，系統(tǒng)通過(guò)計(jì)算機(jī)的網(wǎng)絡(luò)名稱(chēng)識(shí)別SQL Server數(shù)據(jù)庫(kù)的實(shí)例。SQL Server服務(wù)的默認(rèn)實(shí)例名稱(chēng)是MSSQLSERVER。 命名實(shí)例：計(jì)算機(jī)在網(wǎng)絡(luò)上的名字+給定的實(shí)例名字，即“計(jì)算機(jī)名\實(shí)例名”。,SQL Server 2005安裝過(guò)程,SQL Server 實(shí)例 每個(gè)實(shí)例可以獨(dú)立運(yùn)行，每個(gè)實(shí)例有自己的內(nèi)容包括一組該實(shí)例私有的程序和數(shù)據(jù)文件；不同的實(shí)例，可以設(shè)置不同的“排序規(guī)則”、“安全性”和其他選項(xiàng)； 不

4、同實(shí)例的目錄結(jié)構(gòu)、注冊(cè)表結(jié)構(gòu)、服務(wù)名稱(chēng)等都是以實(shí)例的名稱(chēng)進(jìn)行區(qū)分的。,SQL Server 2005安裝過(guò)程,SQL Server 實(shí)例不同的實(shí)例也有共享的內(nèi)容在“開(kāi)始”菜單中只有一個(gè)程序組只有一套管理工具,SQL Server 2005安裝過(guò)程,“文檔”組件窗口：可能包含查詢(xún)編輯器和瀏覽器窗口。默認(rèn)情況下，將顯示已與當(dāng)前計(jì)算機(jī)上的數(shù)據(jù)庫(kù)引擎實(shí)例連接的“摘要”頁(yè)。,SQL Server 2005安裝過(guò)程,SQL Server 20

5、05安裝過(guò)程,,,,對(duì)象資源管理器：是服務(wù)器中所有數(shù)據(jù)庫(kù)對(duì)象的樹(shù)視圖。包括與其連接的所有服務(wù)器的信息。,“已注冊(cè)的服務(wù)器”組件窗口：列出的是經(jīng)常管理的服務(wù)器?？梢栽诖肆斜碇刑砑雍蛣h除服務(wù)器。,“文檔”組件窗口：可能包含查詢(xún)編輯器和瀏覽器窗口。默認(rèn)情況下，將顯示已與當(dāng)前計(jì)算機(jī)上的數(shù)據(jù)庫(kù)引擎實(shí)例連接的“摘要”頁(yè)。,啟動(dòng)SQL Server管理器,,,,,2、手工注冊(cè)服務(wù)器,3.3.1 SQL Server數(shù)據(jù)庫(kù)的文件組成,3.1 系統(tǒng)數(shù)據(jù)庫(kù)

6、概述,1、文件,（1）數(shù)據(jù)文件 數(shù)據(jù)文件存放數(shù)據(jù)庫(kù)數(shù)據(jù)和數(shù)據(jù)庫(kù)對(duì)象的文件。 一個(gè)數(shù)據(jù)庫(kù)可以有一個(gè)或多個(gè)數(shù)據(jù)文件， 一個(gè)數(shù)據(jù)文件只屬于一個(gè)數(shù)據(jù)庫(kù)。 主數(shù)據(jù)文件：存儲(chǔ)數(shù)據(jù)庫(kù)的啟動(dòng)信息和部分或全部數(shù) 據(jù)，擴(kuò)展名為mdf。 次數(shù)據(jù)文件：存儲(chǔ)主文件沒(méi)存儲(chǔ)的其它數(shù)據(jù)， 擴(kuò)展名為ndf。,（2）事務(wù)日志文件

7、 用來(lái)記錄數(shù)據(jù)庫(kù)更新情況的文件，擴(kuò)展名為ldf。一個(gè)數(shù)據(jù)庫(kù)可以有一個(gè)或多個(gè)事務(wù)日志文件。文件最小值為512KB.（3）文件組 為了有效地管理和分配數(shù)據(jù)，通常將數(shù)據(jù)文件組織在一起形成文件組。 ！注：事務(wù)日志文件不屬于任何文件組。,,,文件組可分為以下兩類(lèi)：（1）主文件組：包含主要文件的文件組，所有系統(tǒng)表都被分配到該文件組中。（2）用戶自定義文件組：用戶首次創(chuàng)建數(shù)據(jù)庫(kù)或修改數(shù)據(jù)庫(kù)時(shí)明確創(chuàng)建的文件組。,SQL Serv

8、er 數(shù)據(jù)庫(kù),,SQL Server 數(shù)據(jù)庫(kù) P51,（1）系統(tǒng)數(shù)據(jù)庫(kù) Master、Model、Msdb和Tempdb Master：記錄了一個(gè)SQL Server系統(tǒng)的 所有系統(tǒng)信息 所有的登錄信息 系統(tǒng)設(shè)置信息 SQL Server初始化信息 系統(tǒng)中其它系統(tǒng)數(shù)據(jù)庫(kù)和用戶數(shù)據(jù)庫(kù)的相關(guān)信息,包 括其空間分配信息等,SQL Server 數(shù)據(jù)庫(kù) P52,（1）系統(tǒng)數(shù)據(jù)庫(kù)

9、 Master、Model、Msdb和Tempdb Model：是所有用戶數(shù)據(jù)庫(kù)的創(chuàng)建模板。 當(dāng)創(chuàng)建數(shù)據(jù)庫(kù)時(shí)，系統(tǒng)會(huì)將Model數(shù)據(jù)庫(kù)中的內(nèi)容復(fù)制到新建的數(shù)據(jù)庫(kù)中去。,SQL Server 數(shù)據(jù)庫(kù) P52,（1）系統(tǒng)數(shù)據(jù)庫(kù) Master、Model、Msdb和Tempdb Msdb： 存儲(chǔ)計(jì)劃信息以及與備份和恢復(fù)相關(guān)的信息，尤其是SQL Server Agent 需要使用它來(lái)執(zhí)行安排工作和警報(bào)

10、、記錄操作者等操作,SQL Server 數(shù)據(jù)庫(kù) P52,（1）系統(tǒng)數(shù)據(jù)庫(kù) Master、Model、Msdb和Tempdb Tempdb：用作系統(tǒng)的臨時(shí)存儲(chǔ)空間，用來(lái)存儲(chǔ)臨時(shí)信息。存儲(chǔ)用戶建立的臨時(shí)表和臨時(shí)存儲(chǔ)過(guò)程存儲(chǔ)用戶說(shuō)明的全局變量值為數(shù)據(jù)排序創(chuàng)建臨時(shí)表！注：所有在tempdb中創(chuàng)建的臨時(shí)表都會(huì)在SQL Server停止和重啟時(shí)刪除。,SQL Server 數(shù)據(jù)庫(kù),（2）用戶數(shù)據(jù)庫(kù) 作為數(shù)據(jù)庫(kù)擁

11、有者（DBO）的用戶自己創(chuàng)建的數(shù)據(jù)庫(kù)。他擁有對(duì)數(shù)據(jù)庫(kù)的所有訪問(wèn)權(quán)限。,在SQL Server中，創(chuàng)建數(shù)據(jù)庫(kù)需要確定數(shù)據(jù)庫(kù)名、所有者、數(shù)據(jù)庫(kù)大小和存儲(chǔ)數(shù)據(jù)庫(kù)的文件。,在SQL Server中創(chuàng)建數(shù)據(jù)庫(kù),3.2 創(chuàng)建數(shù)據(jù)庫(kù),3.2.1 使用管理器創(chuàng)建數(shù)據(jù)庫(kù)【示例】 使用SQL Server Management Studio工具創(chuàng)建圖書(shū)館數(shù)據(jù)庫(kù)Library。,在SQL Server中創(chuàng)建數(shù)據(jù)庫(kù),,,,,,,,3.2.1 使用創(chuàng)建數(shù)

12、據(jù)庫(kù),（2）使用命令語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE database_name [ ON [ PRIMARY ]  [ [ ,…n ] ] [ , [ , …n ] ] ] [ LOG ON { [ , ...n ] } ] [ [COLLATE collation_name] [FOR LOAD|FOR ATTACH]

13、 ],定義主文件組,定義文件組,定義日志文件,,（2）使用命令語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù) ::= (NAME = logical_file_name , FILENAME =‘os_file_name’ [,SIZE = size ] [,MAXSIZE = { max_size | UNLIMITED } ] [,FILEGROWTH = growth_increment ] ) [,...n],文件列表,3

14、、在SQL Server中創(chuàng)建數(shù)據(jù)庫(kù),（2）使用命令語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù) CREATE DATABASE database_name [ ON [ PRIMARY ]  [ [ ,…n ] ] [ , [ , …n ] ] ] [ LOG ON { [ , ...n ] } ] ::= FILEGROUP filegroup_name [,...n ],在S

15、QL Server中創(chuàng)建數(shù)據(jù)庫(kù),（2）使用命令語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)【例3.1 】 建一個(gè)名為librarySQL的數(shù)據(jù)庫(kù)。該數(shù)據(jù)庫(kù)有3個(gè)數(shù)據(jù)庫(kù)文件，2個(gè)文件組。 主文件組包括主數(shù)據(jù)庫(kù)文件librarySQL_data,文件大小為5MB，按15%增長(zhǎng)，最大為80MB； 第二個(gè)文件組名為Secondary，包括文件librarySQL2_data，文件初始大小為2MB,按10%增長(zhǎng)，最大為100MB； 日志文件大小為3MB，最大

16、為30MB，按1MB增長(zhǎng)。,在SQL Server中創(chuàng)建數(shù)據(jù)庫(kù),CREATE DATABASE libraryON PRIMARY( NAME = librarySQL_data, FILENAME =‘C:\Program Files\ Microsoft SQL Server\MSSQL.1\MSSQL\Data\librarySQL_data.MDF', SIZE = 5MB, MAXSIZE =80M

17、B, FILEGROWTH =15% ),FILEGROUP Secondary ( NAME = librarySQL2_data, FILENAME =‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\librarySQL2_data.NDF', SIZE = 2MB, MAXSIZE =100MB, FILEGROW

18、TH =10% ),,LOG ON ( NAME = librarySQL_log, FILENAME =' C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\librarySQL_log.LDF ', SIZE = 3MB, MAXSIZE = 30MB, FILEGROWTH = 1MB ),在SQL Server中創(chuàng)建數(shù)據(jù)庫(kù)

19、,CREATE DATABASE libraryON PRIMARY －－創(chuàng)建主文件組(), (), (), (),FILEGROUP Second?。瓌?chuàng)建次文件組(), (), (), (),FILEGROUP Third?。瓌?chuàng)建次文件組(), () ,(), ()LOG ON　 －－創(chuàng)建日志文件(), (), (), (),3.3 管理數(shù)據(jù)庫(kù),3.3.1 數(shù)據(jù)庫(kù)屬性設(shè)置1.使用SQL Server Mana

20、gement Studio修改數(shù)據(jù)庫(kù)屬性。2.使用ALTER DATABASE命令修改數(shù)據(jù)庫(kù)屬性。,,ALTER DATABASE database_name { | |MODIFY NAME=new_database_name |COLLATE collation_name},增加文件，修改文件,增加文件組,修改文件組,重命名數(shù)據(jù)庫(kù),定義數(shù)據(jù)庫(kù)排序規(guī)則,,::={ ADD FILE[, …n] [TO FI

21、LEGROUP{filegroup_name|DEFAULT}] | ADD LOG FILE[, …n] | REMOVE FILE logical_file_name | MODIFY FILE},增加文件，修改文件,增加數(shù)據(jù)文件和日志文件,ALTER DATABASE library ADD FILE (NAME=‘A’,FILENAME=‘C:\A.MDF’,　SIZE=3MB,MAXSIZE=10

22、MB,　FILEGROWTH=10%) TO FILEGROUP SECONDARYALTER DATABASE library ADD LOG FILE(NAME=‘B’,FILENAME=‘C:\B.LDF’,SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=10%),增加數(shù)據(jù)文件,增加日志文件,ALTER DATABASE libraryADD FILE ( ),( ),( ),( ),(

23、 )[TO FILEGROUP SECONDARY],刪除數(shù)據(jù)文件和日志文件,ALTER DATABASE library REMOVE FILE A,,修改數(shù)據(jù)庫(kù)中的文件的屬性：ALTER DATABASE library MODIFY　FILE(NAME=‘A’,NEWNAME=‘B’)ALTER DATABASE library MODIFY FILE(NAME=‘A’,size=5MB),重命名文件

24、名,重新設(shè)置文件A的大小,,::={ ADD FILEGROUP filegroup_name| REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name { |DEFAULT | NAME=new_filegroup_name }},增加文件組,修改文件組,read_only | read_write,,修改數(shù)據(jù)庫(kù)中文件組的屬

25、性：1.ALTER DATABASE library MODIFY FILEGROUP Second READ_ONLY2.ALTER DATABASE library MODIFY FILEGROUP Second DEFAULT3.ALTER DATABASE library MODIFY FILEGROUP Second NAME=Third,設(shè)置文件組為只讀,設(shè)置該文件組為默認(rèn)文件組,重命

26、名文件組名,,【例3.2 】 修改數(shù)據(jù)庫(kù)librarySQL，向其中添加一個(gè)包含librarySQL3_data(文件大小為2MB,按10%增長(zhǎng)，最大為100MB)的文件組First，并將此文件組設(shè)置為默認(rèn)文件組。,,ALTER DATABASE librarySQL ADD FILEGROUP FirstALTER DATABASE librarySQL ADD FILE (NAME=‘librarySQL3_

27、data’, FILENAME=‘’,SIZE=2MB, MAXSIZE=100MB, FILEGROWTH=10%) TO FILEGROUP FirstALTER DATABASE librarySQL MODIFY FILEGROUP First DEFAULT,3.3.2 重命名數(shù)據(jù)庫(kù),1. ALTER DATABASE librarySQL MODIFY NAME=library

28、SQL22. EXEC SP_RENAMEDB librarySQL,librarySQL2,小結(jié),1. 重命名數(shù)據(jù)庫(kù)名ALTER DATABASE libraryMODIFY NAME=新數(shù)據(jù)庫(kù)名2. 重命名文件組名ALTER DATABASE libraryMODIFY FILEGROUP 原文件組名 NAME=新文件組名 3. 重命名文件名ALTER DATABASE libraryMODIFY FILE 文

29、件名　(NAME=原文件名，NEWNAME=新文件名 ),3.3.3 刪除數(shù)據(jù)庫(kù),DROP DATABASE librarySQL,3.3.4 導(dǎo)入和導(dǎo)出數(shù)據(jù),1. 數(shù)據(jù)的導(dǎo)入,2. 數(shù)據(jù)的導(dǎo)出,3.3.5 生成腳本,【例3-6 】 對(duì)數(shù)據(jù)庫(kù)student 中的表創(chuàng)建腳本文件。,3.3.6 分離和附加數(shù)據(jù)庫(kù),SQL Server 2005中用戶數(shù)據(jù)庫(kù)可以從服務(wù)器上分離出來(lái)，然后再方便地附加到其他服務(wù)器上，從而實(shí)現(xiàn)在不同服務(wù)器上移植

30、數(shù)據(jù)庫(kù)。,3.3.6 分離和附加數(shù)據(jù)庫(kù),1. 分離數(shù)據(jù)庫(kù)分離數(shù)據(jù)庫(kù)將從 SQL Server 刪除數(shù)據(jù)庫(kù)，但是保持組成該數(shù)據(jù)庫(kù)的數(shù)據(jù)和事務(wù)日志文件中的數(shù)據(jù)庫(kù)完好無(wú)損。2. 附加數(shù)據(jù)庫(kù) 與分離對(duì)應(yīng)的是附加數(shù)據(jù)庫(kù)操作。附加數(shù)據(jù)庫(kù)可以很方便地在SQL Server 2005服務(wù)器之間利用分離后的數(shù)據(jù)文件和日志文件組織成新的數(shù)據(jù)庫(kù)。,3.3.7 收縮日志文件,SQL Server 采取預(yù)先分配空間的方法來(lái)建立數(shù)據(jù)庫(kù)的數(shù)據(jù)文件

31、或者日志文件，比如數(shù)據(jù)文件的空間分配了100MB，而實(shí)際上只占用了50MB空間，這樣造成存儲(chǔ)空間的浪費(fèi)。 為此，SQL Server 2005提供了收縮數(shù)據(jù)庫(kù)的功能，允許對(duì)數(shù)據(jù)庫(kù)中的每個(gè)文件進(jìn)行收縮。,練習(xí),修改學(xué)生數(shù)據(jù)庫(kù)STUENT，向其中添加一個(gè)文件組Third，并向該文件組中添加兩個(gè)數(shù)據(jù)文件，文件A1大小為2MB,按10%增長(zhǎng)，最大為50MB；文件A2文件大小為3MB,按1MB增長(zhǎng)，最大為50MB 。再完成以

32、下操作：1.重命名數(shù)據(jù)庫(kù)名為STU 。2.重命名文件組名Third為Second。3.重命名文件名A2為AA24.修改文件A1的大小為4MB，文件最大值為100MB 。5.刪除文件AA2.6.刪除文件組Third。,,ALTER DATABASE STUDENT ADD FILEGROUP THIRDALTER DATABASE STUDENT ADD FILE ( ),( ) TO FILEGROUP

33、 First1. ALTER DATABASE STUDENT MODIFY NAME=STU2. ALTER DATABASE STUDENT MODIFY FILENAME THIRD NAME=SECOND3. ALTER DATABASE STUDENT MODIFY FILE(NAME=‘A2’, NEWNAME=‘AA2’),,4. ALTER DATABASE STUDENT MODI

34、FY FILE (NAME=‘A1’,SIZE=4MB, MAXSIZE=100MB)5. ALTER DATABASE STUDENT REMOVE FILE AA26. ALTER DATABASE STUDENT REMOVE FILEGROUP Second,數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn),使用Microsoft SQL Server 2005的數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)，用戶可以方便地選擇和創(chuàng)建索引、索引

35、視圖和分區(qū)的最佳集合。數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)分析一個(gè)或多個(gè)數(shù)據(jù)庫(kù)的工作負(fù)荷和實(shí)現(xiàn)，其中工作負(fù)荷是對(duì)要優(yōu)化的一個(gè)或多個(gè)數(shù)據(jù)庫(kù)執(zhí)行的一組Transact-SQL語(yǔ)句。數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)的輸入是由SQL Server Profiler生成的跟蹤文件、指定的跟蹤表或工作負(fù)荷。數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)的輸出是修改數(shù)據(jù)庫(kù)的物理設(shè)計(jì)結(jié)構(gòu)的建議，其中物理設(shè)計(jì)結(jié)構(gòu)包括聚集索引、非聚集索引、索引視圖、分區(qū)等。,4.7 習(xí)題,1．在一個(gè)SQL Ser

36、ver實(shí)例中含有哪些系統(tǒng)數(shù)據(jù)庫(kù)？其作用分別是什么？2．?dāng)?shù)據(jù)庫(kù)文件中包含哪些內(nèi)容？6．創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)名為test2的數(shù)據(jù)庫(kù)，邏輯文件名為test2database，并用ALTER DATABASE語(yǔ)句添加一個(gè)新的database2數(shù)據(jù)庫(kù)，大小為10MB，然后將test2database中的數(shù)據(jù)庫(kù)database2數(shù)據(jù)文件大小修改為20MB，最后向test2database數(shù)據(jù)庫(kù)中添加一個(gè)日志文件test2database_log。7

37、．創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)名為school的數(shù)據(jù)庫(kù)，邏輯文件名為school，物理文件名為school.mdf。,四、數(shù)據(jù)表,1、數(shù)據(jù)表的創(chuàng)建【例4.4】使用SQL Server Management Studio工具為圖1.7所示的大學(xué)教學(xué)管理數(shù)據(jù)庫(kù)“JXGL”創(chuàng)建學(xué)生關(guān)系表S。,四、數(shù)據(jù)表,2、數(shù)據(jù)表維護(hù)（1）修改數(shù)據(jù)表 ① 更改表名； ② 添加新的列； ③ 刪除已有的列 ④ 修改已有列的屬性（列名、數(shù)據(jù)類(lèi)型、長(zhǎng)度、默認(rèn)值以及約束

38、）（2）刪除數(shù)據(jù)表,四、數(shù)據(jù)表,,修改：進(jìn)入表設(shè)計(jì)器修改表結(jié)構(gòu),重命名：為表重新命名,刪除：刪除選中的表,新建列：添加新屬性,重命名：為該列重新命名,刪除：刪除選中的列,修改：修改該列的屬性設(shè)置,四、數(shù)據(jù)表,3、數(shù)據(jù)表的操作 用戶通過(guò)SQL Server Management Studio工具可以方便的瀏覽數(shù)據(jù)表的所有記錄，向數(shù)據(jù)表插入記錄、修改數(shù)據(jù)和刪除數(shù)據(jù)記錄。,,五、查詢(xún)和視圖,1、查詢(xún)?cè)O(shè)計(jì)器,“關(guān)系圖”窗格：以

39、可視化圖形的方式顯示數(shù)據(jù)表、視圖以及表間關(guān)系等數(shù)據(jù)對(duì)象,“網(wǎng)格”窗格：對(duì)可用的數(shù)據(jù)表、列、視圖以及別名等信息進(jìn)行設(shè)置的操作界面,“SQL”窗格：展示了通過(guò)操作界面處理而自動(dòng)生成的T-SQL語(yǔ)句,“結(jié)果”窗格：用于以表格的形式顯示視圖或查詢(xún)執(zhí)行的結(jié)果,五、查詢(xún)和視圖,2、查詢(xún)【例4.11】查詢(xún)所有學(xué)習(xí)了數(shù)據(jù)結(jié)構(gòu)課（課程號(hào)為C401001）的學(xué)生的學(xué)號(hào)和姓名。輸出結(jié)果：學(xué)號(hào)S＃和姓名SNAME選擇條件：C＃＝’ C401001’涉