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

1、第3章 創(chuàng)建和管理數據庫,—— SQL Server 2005,SQL Server 2005安裝過程,使用有管理權限的帳戶登錄Windows服務器，確保沒有其他程序在前臺運行。插入安裝光盤，啟動安裝程序，系統顯示開始界面。,,SQL Server 2005安裝過程,服務器組件、工具、聯機叢書和示例,SQL Server 2005安裝過程,,SQL Server 2005安裝過程,,實例是在計算機上安裝的一個“獨立”的服務器,SQL S

2、erver 2005安裝過程,SQL Server 實例 SQL Server 實例是在計算機上安裝的一個“獨立”的服務器，在同一臺計算機上可以安裝多個SQL Server實例，應用程序可以同時連接同一個計算機上不同的實例。,SQL Server 2005安裝過程,SQL Server 實例實例以名稱進行區(qū)分，用戶可以指定實例名稱，也可以使用默認的實例名。因此SQL Server 有兩種實例：默認實例和命名實例。默認實

3、例：默認情況下，系統通過計算機的網絡名稱識別SQL Server數據庫的實例。SQL Server服務的默認實例名稱是MSSQLSERVER。 命名實例：計算機在網絡上的名字+給定的實例名字，即“計算機名\實例名”。,SQL Server 2005安裝過程,SQL Server 實例 每個實例可以獨立運行，每個實例有自己的內容包括一組該實例私有的程序和數據文件；不同的實例，可以設置不同的“排序規(guī)則”、“安全性”和其他選項； 不

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

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

6、概述,1、文件,（1）數據文件 數據文件存放數據庫數據和數據庫對象的文件。 一個數據庫可以有一個或多個數據文件， 一個數據文件只屬于一個數據庫。 主數據文件：存儲數據庫的啟動信息和部分或全部數 據，擴展名為mdf。 次數據文件：存儲主文件沒存儲的其它數據， 擴展名為ndf。,（2）事務日志文件

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

8、er 數據庫,,SQL Server 數據庫 P51,（1）系統數據庫 Master、Model、Msdb和Tempdb Master：記錄了一個SQL Server系統的 所有系統信息 所有的登錄信息 系統設置信息 SQL Server初始化信息 系統中其它系統數據庫和用戶數據庫的相關信息,包 括其空間分配信息等,SQL Server 數據庫 P52,（1）系統數據庫

9、 Master、Model、Msdb和Tempdb Model：是所有用戶數據庫的創(chuàng)建模板。 當創(chuàng)建數據庫時，系統會將Model數據庫中的內容復制到新建的數據庫中去。,SQL Server 數據庫 P52,（1）系統數據庫 Master、Model、Msdb和Tempdb Msdb： 存儲計劃信息以及與備份和恢復相關的信息，尤其是SQL Server Agent 需要使用它來執(zhí)行安排工作和警報

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

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

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

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

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

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

16、為30MB，按1MB增長。,在SQL Server中創(chuàng)建數據庫,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)建數據庫

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

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

21、LEGROUP{filegroup_name|DEFAULT}] | ADD LOG FILE[, …n] | REMOVE FILE logical_file_name | MODIFY FILE},增加文件，修改文件,增加數據文件和日志文件,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%),增加數據文件,增加日志文件,ALTER DATABASE libraryADD FILE ( ),( ),( ),( ),(

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

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

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,設置文件組為只讀,設置該文件組為默認文件組,重命

26、名文件組名,,【例3.2 】 修改數據庫librarySQL，向其中添加一個包含librarySQL3_data(文件大小為2MB,按10%增長，最大為100MB)的文件組First，并將此文件組設置為默認文件組。,,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 重命名數據庫,1. ALTER DATABASE librarySQL MODIFY NAME=library

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

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

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

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

32、下操作：1.重命名數據庫名為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,數據庫引擎優(yōu)化顧問,使用Microsoft SQL Server 2005的數據庫引擎優(yōu)化顧問，用戶可以方便地選擇和創(chuàng)建索引、索引

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

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

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

38、）（2）刪除數據表,四、數據表,,修改：進入表設計器修改表結構,重命名：為表重新命名,刪除：刪除選中的表,新建列：添加新屬性,重命名：為該列重新命名,刪除：刪除選中的列,修改：修改該列的屬性設置,四、數據表,3、數據表的操作 用戶通過SQL Server Management Studio工具可以方便的瀏覽數據表的所有記錄，向數據表插入記錄、修改數據和刪除數據記錄。,,五、查詢和視圖,1、查詢設計器,“關系圖”窗格：以

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