1、李政偉數據庫應用數據庫應用第第3章章 數據庫的創建與管理數據庫的創建與管理v3.1 SQL Server3.1 SQL Server數據庫概述數據庫概述v3.2 3.2 創建數據庫創建數據庫v3.3 3.3 查看和設置數據庫選項查看和設置數據庫選項v3.4 3.4 修改數據庫修改數據庫v3.5 3.5 刪除數據庫刪除數據庫v3.6 3.6 分離和附加數據庫分離和附加數據庫v3.7 3.7 復制數據庫復制數據庫2/39v數據庫數據庫(Database(Database，DB)DB)：將數據按一定的數：將數據按一定的數據模型組織、描述和儲存，具有較小的冗據模型組織、描述和儲存，具有較小的冗余度，較

2、高的數據獨立性和易擴展性，并余度，較高的數據獨立性和易擴展性，并可被多用戶、多應用程序共享的可被多用戶、多應用程序共享的數據集合數據集合。（1 1）在）在計算機的存儲設備計算機的存儲設備上存放的，屬于計上存放的，屬于計算機領域的一個術語。算機領域的一個術語。（2 2）一個）一個數據集合數據集合。（3 3）這個數據集合是）這個數據集合是有結構有結構的，這是和文件的，這是和文件系統相比最大的特點之一。系統相比最大的特點之一。（4 4）這個數據集合是指）這個數據集合是指相關聯相關聯的數據集合。的數據集合。（5 5）這個數據集合是）這個數據集合是合理存放合理存放的。的。3.1 SQL Server數據

3、庫概述數據庫概述3.1 SQL Server數據庫概述數據庫概述v兩大類：兩大類： 系統數據庫系統數據庫：存放維護系統正常運行的信息。：存放維護系統正常運行的信息。 用戶數據庫用戶數據庫: :存放用戶自己的信息。存放用戶自己的信息。4/39系統數據庫系統數據庫v Master：最重要數據庫，用于記錄所有系統級信息。包括：最重要數據庫，用于記錄所有系統級信息。包括所有的登錄信息、系統設置信息、初始化信息和其他系統數所有的登錄信息、系統設置信息、初始化信息和其他系統數據庫及用戶數據庫的相關信息。如果據庫及用戶數據庫的相關信息。如果 master 數據庫不可數據庫不可用，則用，則 SQL Serve

4、r 無法啟動。無法啟動。v Model：用作所有數據庫的模板。當用戶創建數據庫時，：用作所有數據庫的模板。當用戶創建數據庫時，系統自動將系統自動將model數據庫中的內容復制到新建數據庫中。數據庫中的內容復制到新建數據庫中。當發出當發出 CREATE DATABASE（創建數據庫）時，將通過（創建數據庫）時，將通過復制復制 model 數據庫的內容來創建數據庫的第一部分，然數據庫的內容來創建數據庫的第一部分，然后用空頁填充新數據庫的剩余部分。后用空頁填充新數據庫的剩余部分。v Msdb：保存關于報警、作業、操作員等信息。：保存關于報警、作業、操作員等信息。5/39系統數據庫系統數據庫vTemp

5、db：臨時數據庫，用于存儲用戶創建的臨時：臨時數據庫，用于存儲用戶創建的臨時表、用戶聲明的變量以及用戶定義的游標數據等。表、用戶聲明的變量以及用戶定義的游標數據等。Tempdb數據庫由整個系統的所有數據庫使用，不數據庫由整個系統的所有數據庫使用，不管用戶使用哪個數據庫，他們所建立的所有臨時表和管用戶使用哪個數據庫，他們所建立的所有臨時表和存儲過程都存儲在存儲過程都存儲在tempdb上。上。SQL Server每次每次啟動時，啟動時，tempdb數據庫被重新建立。當用戶與數據庫被重新建立。當用戶與SQL Server斷開連接時，其臨時表和存儲過程自動斷開連接時，其臨時表和存儲過程自動被刪除。被刪

6、除。vResource ：一個只讀數據庫，包含一個只讀數據庫，包含 SQL Server 包括的系統對象。系統對象在物理上保留在包括的系統對象。系統對象在物理上保留在 Resource 數據庫中，但在邏輯上顯示在每個數據數據庫中，但在邏輯上顯示在每個數據庫的庫的 sys 架構中。架構中。SQL SERVER樣本數據庫樣本數據庫v AdventureWorks、AdventureWorksDW等示例等示例數據庫，示例數據基于一個虛擬的公司數據庫，示例數據基于一個虛擬的公司Adventure Works Cycles，這是一個大型的跨國制造公司，生產，這是一個大型的跨國制造公司，生產金屬和復合材料

7、自行車，產品遠銷北美、歐洲和亞洲市場金屬和復合材料自行車，產品遠銷北美、歐洲和亞洲市場。v AdventureWorks中的某些表的結構和內容與中的某些表的結構和內容與SQL Server 2000中中Pubs和和Northwind數據庫的表結構數據庫的表結構和內容相似，對照聯機幫助上的對應關系可以很方便的將和內容相似，對照聯機幫助上的對應關系可以很方便的將SQL Server 2000中使用中使用Northwind和和Pubs數據庫數據庫的相關查詢轉換到的相關查詢轉換到AdventureWorks中來。中來。3.1.2 SQL Server數據庫的組成數據庫的組成vSQL Server將數據

8、庫映射為一組操作系將數據庫映射為一組操作系統文件，這些文件被劃分為兩類：數據文件統文件，這些文件被劃分為兩類：數據文件和日志文件。和日志文件。v數據文件數據文件包含數據和對象，例如表、索引包含數據和對象，例如表、索引、存儲過程和視圖等。、存儲過程和視圖等。v日志文件日志文件包含恢復數據庫中的所有事務需包含恢復數據庫中的所有事務需要的信息。要的信息。v數據和日志信息不混合在同一個文件中，數據和日志信息不混合在同一個文件中，一個文件只由一個數據庫使用。一個文件只由一個數據庫使用。8/391.數據文件數據文件v 用于存放數據庫數據。數據文件又分為：用于存放數據庫數據。數據文件又分為：v 主要數據文件

9、主要數據文件： 推薦擴展名是推薦擴展名是.mdf。 包含數據庫的系統信息，也可存放用戶數據。包含數據庫的系統信息，也可存放用戶數據。 每個數據庫都有且只能有一個主要數據文件。每個數據庫都有且只能有一個主要數據文件。 是為數據庫創建的第一個數據文件。是為數據庫創建的第一個數據文件。 不能小于不能小于3MB。v 次要數據文件次要數據文件： 推薦擴展名是推薦擴展名是.ndf。 一個數據庫可以包含一個數據庫可以包含0n個次要數據文件。個次要數據文件。 可與主要數據文件存放在不同地方?？膳c主要數據文件存放在不同地方。v 所有文件的使用對用戶也是透明的。所有文件的使用對用戶也是透明的。9/392.日志文件

10、日志文件v推薦擴展名為推薦擴展名為.ldf。v用于存放恢復數據庫的所有日志信息。用于存放恢復數據庫的所有日志信息。v每個數據庫有每個數據庫有1n個日志文件。個日志文件。10/393關于數據的存儲分配關于數據的存儲分配v數據庫中數據的存儲分配單位是數據頁。數據庫中數據的存儲分配單位是數據頁。一頁是一塊一頁是一塊8KB（其中用（其中用8060個字節存個字節存放數據，另外放數據，另外132個字節存放系統信息）個字節存放系統信息）的連續磁盤空間。的連續磁盤空間。v頁是存儲數據的最小空間分配單位。頁是存儲數據的最小空間分配單位。v不允許表中的一行數據存儲在不同頁上（不允許表中的一行數據存儲在不同頁上（v

11、archar(max)、nvarchar(max)、text、ntext、varbinary(max)和和image數據類型除外），即數據類型除外），即行不能跨頁存行不能跨頁存儲儲。v日志文件不包含頁，而是包含一系列日志日志文件不包含頁，而是包含一系列日志記錄記錄。11/39示例示例v假設某數據表有假設某數據表有10000行數據，每行行數據，每行3000字節，估算其需要的空間字節，估算其需要的空間: (10000/2) * 8KB = 40MBv其中，每頁中有其中，每頁中有6000字節用于存儲數據，有字節用于存儲數據，有2060個字節是浪費的。個字節是浪費的。12/393.1.3 數據庫文件組

12、數據庫文件組v為便于分配和管理為便于分配和管理數據文件數據文件，將相關的數據，將相關的數據文件組織起來，放置到一個文件組中。文件組織起來，放置到一個文件組中。v文件組的概念類似于操作系統中的文件夾。文件組的概念類似于操作系統中的文件夾。v在一個數據庫中可以定義多個文件組，然后在一個數據庫中可以定義多個文件組，然后將數據文件放置到不同的文件組中。將數據文件放置到不同的文件組中。vSQL Server有兩種類型的文件組：有兩種類型的文件組： 主文件組主文件組 用戶定義的文件組用戶定義的文件組13/39主文件組（主文件組（PRIMARY）v系統預先定義好的一個文件組，系統預先定義好的一個文件組，v包

13、含主要數據文件和任何沒有明確分配給其他包含主要數據文件和任何沒有明確分配給其他文件組的其他文件。文件組的其他文件。v系統表的所有頁均分配在主文件組中。系統表的所有頁均分配在主文件組中。14/39用戶定義文件組用戶定義文件組v用戶可以創建自己的文件組，以將相關數據文件組織起用戶可以創建自己的文件組，以將相關數據文件組織起來，便于管理和數據分配。來，便于管理和數據分配。v通過在通過在CREATE DATABASE或或ALTER DATABASE語句中使用語句中使用FILEGROUP 關鍵字定義。關鍵字定義。v說明：說明： 日志文件不包括在文件組內，日志文件不包括在文件組內， 一個文件一個文件不不可

14、以是多個文件組的成員。可以是多個文件組的成員。v 文件組用途文件組用途：例如，可以分別在：例如，可以分別在三個磁盤驅動器三個磁盤驅動器上創建三個上創建三個文件文件 Data1.ndf、Data2.ndf 和和 Data3.ndf，然后將，然后將它們分配給文件組它們分配給文件組 fgroup1。然后，可以明確地在文件組。然后，可以明確地在文件組 fgroup1 上創建一個表。對表中數據的查詢將分散到三個上創建一個表。對表中數據的查詢將分散到三個磁盤上，從而提高了性能。磁盤上，從而提高了性能。15/39使用文件和文件組的一般建議使用文件和文件組的一般建議v使用文件組將對象放置在特定的物理磁盤使用文

15、件組將對象放置在特定的物理磁盤上。上。v為性能最大化，應在盡可能多的不同可用為性能最大化，應在盡可能多的不同可用物理磁盤上創建文件和文件組，將爭奪空物理磁盤上創建文件和文件組，將爭奪空間最激烈的對象置于不同的文件組中。間最激烈的對象置于不同的文件組中。v將同一連接查詢中使用的不同表置于不同將同一連接查詢中使用的不同表置于不同文件組中。文件組中。v將最經常訪問的表和屬于這些表的非聚集將最經常訪問的表和屬于這些表的非聚集索引置于不同的文件組中。索引置于不同的文件組中。v不要將事務日志文件和其他文件或文件組不要將事務日志文件和其他文件或文件組置于同一磁盤上。置于同一磁盤上。3.1.4 數據庫文件的屬

16、性數據庫文件的屬性v文件名及其位置文件名及其位置：每個文件都有一個邏輯文件：每個文件都有一個邏輯文件名和物理文件名及物理存儲位置名和物理文件名及物理存儲位置v初始大小初始大?。嚎梢灾付總€文件的初始大小。：可以指定每個文件的初始大小。v增長方式增長方式：可以指定文件是否自動增長：可以指定文件是否自動增長v最大大小最大大?。嚎梢灾付ㄎ募鲩L的最大空間限制：可以指定文件增長的最大空間限制17/393.2 創建數據庫創建數據庫v創建用戶數據庫前，須先確定創建用戶數據庫前，須先確定數據庫名數據庫名、所有者所有者、文件名文件名、數據文件大小數據文件大小、數據庫數據庫字符集字符集、是否自動增長是否自動增長

17、以及以及如何自動增長如何自動增長等信息。在一個等信息。在一個SQL Server實例中，最實例中，最多可創建多可創建32767個數據庫。數據庫名稱必個數據庫。數據庫名稱必須滿足標識符規則。在命名數據庫時，一須滿足標識符規則。在命名數據庫時，一定要保證數據庫名稱簡短并有一定的含義定要保證數據庫名稱簡短并有一定的含義。v3.2.1 用圖形化方法創建數據庫用圖形化方法創建數據庫v3.2.2 用用T-SQL語句創建數據庫語句創建數據庫18/393.2.1 用圖形化方法創建數據庫用圖形化方法創建數據庫19/393.2.2 用用T-SQL語句創建數據庫語句創建數據庫CREATE DATABASE data

18、base_name ON PRIMARY ,.n LOG ON ,.n := ( NAME = logical_file_name , FILENAME = os_file_name | filestream_path , SIZE = size KB | MB | GB | TB , MAXSIZE =max_sizeKB|MB|GB|TB| UNLIMITED , FILEGROWTH =growth_incrementKB|MB|GB|TB|% ) ,.n 20/39示例示例1(P66)v例例1創建一個名為創建一個名為“mytest”的數據庫，的數據庫，其他選項均采用默認設置。其他選項均

19、采用默認設置。CREATE DATABASE mytest21/39示例示例2(P66)CREATE DATABASE RShDBON ( NAME = RShDB, FILENAME = D:RShDB_DataRShDB.mdf , SIZE = 10, MAXSIZE = 30, FILEGROWTH = 5 )LOG ON( NAME = RShDB_log, FILENAME = D:RShDB_DataRShDB_log.ldf , SIZE = 3, MAXSIZE = 12, FILEGROWTH = 2 )22/39示例示例3(P67)CREATE DATABASE stud

20、entsON PRIMARY ( NAME = students, FILENAME = F:Datastudents.mdf, SIZE = 3MB, MAXSIZE = UNLIMITED ), ( NAME = students_data1, FILENAME = D:Datastudents_data1.ndf, SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB )LOG ON ( NAME = students_log, FILENAME = F:Datastudents_log.ldf, SIZE = 2MB, MAXSIZE = 6MB,

21、FILEGROWTH = 10% )23/393.3 查看和設置數據庫選項查看和設置數據庫選項v展開展開“數據庫數據庫”節點，在要查看屬性的數據庫節點，在要查看屬性的數據庫上右擊鼠標，上右擊鼠標，v然后在彈出的快捷菜單中選擇然后在彈出的快捷菜單中選擇“屬性屬性”命令。命令。24/393.4 修改數據庫修改數據庫v3.4.1 擴大數據庫空間擴大數據庫空間v3.4.2 收縮數據庫空間收縮數據庫空間v3.4.3 添加和刪除數據庫文件添加和刪除數據庫文件v3.4.4 創建和更改文件組創建和更改文件組25/393.4.1 擴大數據庫空間擴大數據庫空間ALTER DATABASE database_nam

22、e := ADD FILE ,.n TO FILEGROUP filegroup_name | DEFAULT | ADD LOG FILE ,.n | MODIFY FILE 26/39示例示例v 例例1為為“RShDB”數據庫添加一個新的數據文件，邏輯數據庫添加一個新的數據文件，邏輯文件名為：文件名為：RShDB_Data2，物理存儲位置為：，物理存儲位置為：E:Data文件夾下，物理文件名為：文件夾下，物理文件名為：RShDB_Data2.ndf,初始大小為初始大小為6MB，不自動增長。，不自動增長。ALTER DATABASE RShDBADD FILE ( NAME = RShDB_

23、Data2, FILENAME = E:DataRShDB_Data2.ndf, SIZE = 6MB, FILEGROWTH = 0)27/39示例示例v例例2修改修改Students數據庫中主數據文件數據庫中主數據文件Students的初始大小，將其初始大小改為的初始大小，將其初始大小改為8MB。ALTER DATABASE Students MODIFY FILE( NAME = students, SIZE = 8MB)28/39示例示例v 例例3為為Students數據庫加添加一個新的日志文件，邏輯數據庫加添加一個新的日志文件，邏輯文件名為：文件名為：students_log1，物理

24、存儲位置為：，物理存儲位置為：D:Data文件夾下，物理文件名為：文件夾下，物理文件名為：students_log1.ldf,初始大小為初始大小為2MB，每次增加，每次增加1MB，最多增加到最多增加到10MB。ALTER DATABASE Students ADD LOG FILE ( NAME = students_log1, FILENAME = D:Datastudents_log1.ldf, SIZE = 2MB, FILEGROWTH = 1MB, MAXSIZE = 10MB )29/393.4.2 收縮數據庫空間收縮數據庫空間v自動收縮自動收縮v手工收縮手工收縮 用圖形化方法用圖

25、形化方法 用用T-SQL語句語句30/39收縮數據庫收縮數據庫DBCC SHRINKDATABASE ( database_name | 0 ,target_percent , NOTRUNCATE | TRUNCATEONLY )31/39收縮文件收縮文件DBCC SHRINKFILE ( file_name , EMPTYFILE | , target_size , NOTRUNCATE | TRUNCATEONLY )32/39示例示例v例例4收縮收縮Students數據庫，使該數據庫中數據庫，使該數據庫中所有的文件都有所有的文件都有20%的可用空間。的可用空間。DBCC SHRINKD

26、ATABASE（students, 20）v例例5將將Students數據庫中的數據庫中的students_data1文件收縮到文件收縮到4MB。 DBCC SHRINKFILE (students_data1, 4)33/39數據庫文件使用情況數據庫文件使用情況34/39添加和刪除數據庫文件添加和刪除數據庫文件v添加數據庫文件添加數據庫文件 使用使用ALTER DATABASE語句。語句。v刪除數據庫文件刪除數據庫文件ALTER DATABASE database_name REMOVE FILE logical_file_namev只有當文件中沒有數據或事務日志信息，文件只有當文件中沒有數

27、據或事務日志信息，文件完全為空時，才可以從數據庫中刪除該文件。完全為空時，才可以從數據庫中刪除該文件。35/39示例示例v例例 6 刪 除 刪 除 S t u d e n t s 數 據 庫 中 的數 據 庫 中 的Students_data1文件。文件。 ALTER DATABASE Students REMOVE FILE Students_data1v例例 7 刪 除 刪 除 S t u d e n t s 數 據 庫 中 的數 據 庫 中 的Students_log1文件。文件。 ALTER DATABASE Students REMOVE FILE Students_log136/3

28、93.4.4 創建和更改文件組創建和更改文件組v用圖形化方法：在數據庫屬性窗口中；用圖形化方法：在數據庫屬性窗口中；v用用T-SQL語句。語句。37/39創建和更改文件組語句創建和更改文件組語句ALTER DATABASE database_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILEGROUP filegroup_name | DEFAULT | NAME = new_filegroup_name := READ_ONLY | READ_WRITE 38/39示例示例v例

29、例1為為students數據庫定義一個新文件組，文件組名為：數據庫定義一個新文件組，文件組名為：NewFileGroup1，同時，同時在該文件組中新添加兩個數據文件，邏輯名分別為：在該文件組中新添加兩個數據文件，邏輯名分別為：students_dat1和和students_dat2，初始大小分別為：，初始大小分別為：4MB和和6MB，均存放在，均存放在D:Data文件夾下，不文件夾下，不自動增長。自動增長。v（1）創建文件組）創建文件組ALTER DATABASE StudentsADD FILEGROUP NewFileGroup1v（2）添加新數據文件）添加新數據文件ALTER DATAB

30、ASE students ADD FILE ( NAME = students_dat1, FILENAME = D:Datastudents_dat1.ndf, SIZE = 4MB, FILEGROWTH = 0 ),( NAME = students_dat2, FILENAME = D:Datastudents_dat2.ndf, SIZE = 6MB, FILEGROWTH = 0 ) TO FILEGROUP NewFileGroup139/39示例示例v例例 2 將 將 S t u d e n t s 數 據 庫 中 的數 據 庫 中 的NewFileGroup1文件組設為默認文

31、件組。文件組設為默認文件組。ALTER DATABASE Students M O D I F Y F I L E G R O U P NewFileGroup1 DEFAULT40/393.5 刪除數據庫刪除數據庫DROP DATABASE database_name , n v例例1刪除單個數據庫：刪除刪除單個數據庫：刪除Sudnets數據庫數據庫。 DROP DATABASE studentsv例例2同時刪除多個數據庫：刪除同時刪除多個數據庫：刪除“學生數據庫學生數據庫”和和“RShDB”數據庫。數據庫。 DROP DATABASE 學生數據庫學生數據庫, RShDB41/393.6 分離和附加數據庫分離和附加數據庫v分離和附加數據庫可以將數據庫從一臺計算機分離和附加數據庫可以將數據庫從一臺計算機移動到另一臺計算機。移動到另一臺計算機。v數據庫被分離后，其所包含的數據文件和日志數據庫被分離后，其所包含的數據文件和日志文件不再受數據庫管理系統的管理，因此用戶文件不再受數據庫管理系統的管理