1、 第3章 SQL Server 數(shù)據(jù)庫的管理本章內容 3.1 SQL Server 2014數(shù)據(jù)庫概述 3.2 SQL Server數(shù)據(jù)庫的管理3.1 SQL Server 2014 數(shù)據(jù)庫概述一、數(shù)據(jù)庫的存儲結構 數(shù)據(jù)庫的邏輯組件數(shù)據(jù)庫的邏輯組件(數(shù)據(jù)庫對象數(shù)據(jù)庫對象) Sales數(shù)據(jù)庫數(shù)據(jù)庫SQL Server表表視圖視圖數(shù)據(jù)庫的物理實現(xiàn)數(shù)據(jù)庫的物理實現(xiàn)(數(shù)據(jù)庫文件數(shù)據(jù)庫文件)SalesDat1.mdfSalesDat2.ndfSalesLog1.ldf主數(shù)據(jù)文件主數(shù)據(jù)文件次數(shù)據(jù)文件次數(shù)據(jù)文件日志文件日志文件索引索引存儲過程存儲過程用戶視圖用戶視圖物理視圖物理視圖圖圖3-1 數(shù)據(jù)庫的用戶視

2、圖和物理視圖數(shù)據(jù)庫的用戶視圖和物理視圖1. SQL Server中的數(shù)據(jù)庫對象 SQL Server提供了很多邏輯組件，這些邏輯組件通常被稱為數(shù)據(jù)庫對象。 對象作用表數(shù)據(jù)庫中數(shù)據(jù)的實際存放處所。視圖定制復雜或常用的查詢，以便用戶使用；限定用戶只能查看表中的特定行或列；為用戶提供統(tǒng)計數(shù)據(jù)而不展示細節(jié)。索引加快從表或視圖中檢索數(shù)據(jù)的效率。存儲過程提高性能；封裝數(shù)據(jù)庫的部分或全部細節(jié)；幫助在不同的數(shù)據(jù)庫應用程序之間實現(xiàn)一致的邏輯。約束、規(guī)則、默認值和觸發(fā)器確保數(shù)據(jù)庫的數(shù)據(jù)完整性；強制執(zhí)行業(yè)務規(guī)則。登錄、用戶、角色和組保障數(shù)據(jù)安全的基礎。SQL Server中常用的數(shù)據(jù)庫對象及作用2、數(shù)據(jù)庫對象的標識

3、符 例如數(shù)據(jù)庫名、表名、視圖名、列名等。SQL Server標識符的命名遵循以下規(guī)則：(1)標識符包含的字符數(shù)必須在1-128之間。(2)標識符的第一個字符必須是字母、下劃線(_)、at符號()或者數(shù)字符號(#)。(3)標識符的后續(xù)字符可以為字母、數(shù)字或“”符號、“$”符號、數(shù)字符號或下劃線。 (4)標識符不能是Transact-SQL的保留字，也不能包含空格。 二、數(shù)據(jù)庫所有者和架構 數(shù)據(jù)庫所有者（DBO，Data Base Owner）：就是有權限訪問數(shù)據(jù)庫的用戶，即登錄數(shù)據(jù)庫的網(wǎng)絡用戶。 是唯一的，擁有該數(shù)據(jù)庫中的全部權限，并能夠提供給其他用戶訪問權限和功能。二、數(shù)據(jù)庫所有者和架構 架構

4、：將數(shù)據(jù)庫中的所有對象分成不同的集合，每一個集合就稱為一個架構。 數(shù)據(jù)庫中的每一個用戶都會有自己的默認架構，可以在建庫時設定，系統(tǒng)默認架構為dbo。 數(shù)據(jù)庫用戶只能對屬于自己架構中的數(shù)據(jù)庫對象執(zhí)行相應的數(shù)據(jù)操作。操作的權限則由數(shù)據(jù)庫角色決定。三、 數(shù)據(jù)庫文件行數(shù)據(jù)文件行數(shù)據(jù)文件*.mdf僅有一個僅有一個事務日志文件事務日志文件*.ldf一到多個一到多個 次數(shù)據(jù)文件次數(shù)據(jù)文件*.ndf零到多個零到多個次數(shù)據(jù)文件次數(shù)據(jù)文件*.ndf零到多個零到多個(僅有一個僅有一個)主文件組主文件組(零到多個零到多個)次文件組次文件組一到多個一到多個事務日志事務日志一個數(shù)據(jù)庫的文件集一個數(shù)據(jù)庫的文件集圖圖3-2

5、數(shù)據(jù)庫的文件組成數(shù)據(jù)庫的文件組成(1)數(shù)據(jù)庫文件 行文件：.mdf,包含數(shù)據(jù)庫的啟動信息、數(shù)據(jù)信息唯一的 事務日志：.ldf,包含恢復數(shù)據(jù)庫的所有日志信息至少有一個，最小為 512 KB 次要文件：.ndf,主文件中不包括的所有數(shù)據(jù)信息0個、一個或多個(2)文件組 SQL Server允許對文件進行分組，以便于管理和數(shù)據(jù)的分配放置。 文件組是將多個數(shù)據(jù)庫文件集合起來形成的一個整體。每個文件組有一個組名。 數(shù)據(jù)庫文件組分為主文件組（primary）、自定義文件組（user_defined）和默認文件組（default）三類。u主文件組（primary） ： 所有系統(tǒng)表、主要數(shù)據(jù)文件.mdf和未放

6、入其他文件組的所有次要文件都被分配到主文件組中。一個數(shù)據(jù)庫有1個主文件組。u用戶定義文件組（user_defined） ：是用戶首次創(chuàng)建數(shù)據(jù)庫時，或修改數(shù)據(jù)庫時自定義的，其目的在于數(shù)據(jù)分配，以提高表的讀寫效率。u默認文件組（default）:每個數(shù)據(jù)庫中均有一個文件組被指定為默認文件組.如果在數(shù)據(jù)庫中創(chuàng)建對象時沒有指定對象所屬的文件組，對象將被分配給默認文件組。在使用文件組時，應當注意以下幾個準則： 文件或文件組不能由一個以上的數(shù)據(jù)庫使用。 文件只能是一個文件組的成員。 數(shù)據(jù)和事務日志信息不能屬于同一文件或文件組。 事務日志文件不能屬于任何文件組。文件組的使用場合當有多個磁盤，希望把文件分布在

7、這些磁盤上以提高性能。13Student數(shù)據(jù)庫Student_1.mdfStudent_2.ndfStudent_3.ndfStudent_log.ldfC盤d盤e盤f盤primaryStudgrpStudgrp 文件組的作用： 當對數(shù)據(jù)庫對象寫操作時,數(shù)據(jù)庫會根據(jù)組內數(shù)據(jù)文件的大小,按比例寫入組內所有數(shù)據(jù)文件中. 當查詢數(shù)據(jù)時,系統(tǒng)會創(chuàng)建多個單獨的線程來并行讀取分配在不同物理磁盤上的每個文件, 提高了查詢速度.14四、數(shù)據(jù)庫分類151、系統(tǒng)數(shù)據(jù)庫master數(shù)據(jù)庫：記錄系統(tǒng)的所有系統(tǒng)級的信息model數(shù)據(jù)庫：模板數(shù)據(jù)庫msdb數(shù)據(jù)庫：用來存儲計劃信息、備份和恢復相關信息、SQL server

8、代理程序調度報警和作業(yè)調度等操作。tempdb數(shù)據(jù)庫：臨時數(shù)據(jù)庫，它為所有的臨時表、臨時存儲過程及其他臨時操作提供存儲空間。resource數(shù)據(jù)庫：隱藏的只讀數(shù)據(jù)庫，包括SQL server 2014中的所有系統(tǒng)對象，但不含用戶數(shù)據(jù)或用戶元數(shù)據(jù)。此文件位于2.示例數(shù)據(jù)庫 AdventureWorks/AdventureWorks DW是SQL Server 中的示例數(shù)據(jù)庫（如果在安裝過程中選擇安裝了的話）。此數(shù)據(jù)庫包含 Adventure Works 虛購的公司及其分公司的財務數(shù)據(jù)和自行車銷售數(shù)據(jù)，以簡單、易于理解的方式來展示SQL Server 的功能。3.用戶數(shù)據(jù)庫 用戶根據(jù)數(shù)據(jù)庫設計創(chuàng)建

9、的數(shù)據(jù)庫。如教學管理數(shù)據(jù)庫（jxgl），圖書管理數(shù)據(jù)庫（Library）。一、創(chuàng)建數(shù)據(jù)庫先規(guī)劃數(shù)據(jù)庫，規(guī)劃數(shù)據(jù)庫需要考慮的問題： 數(shù)據(jù)庫名稱、數(shù)據(jù)庫所有者； 數(shù)據(jù)文件和事務日志文件的邏輯名、物理名、初始大小、增長方式和最大容量； 數(shù)據(jù)庫用戶數(shù)量和用戶權限； 數(shù)據(jù)庫大小與硬件配置的平衡、是否使用文件組； 數(shù)據(jù)庫的備份與恢復。3.2 SQL Server 數(shù)據(jù)庫的管理【例3-1】在D:sqllx目錄下創(chuàng)建一個jxgl數(shù)據(jù)庫，該數(shù)據(jù)庫的主數(shù)據(jù)文件邏輯名稱為jxgl，物理文件名為teching.mdf，初始大小為5MB，最大為20 MB，增長速度為20%；數(shù)據(jù)庫的日志文件邏輯名稱為jxgl_log，物

10、理文件名為teching_log.ldf，初始大小為2MB，最大為10MB，增長速度為1MB。1、使用對象資源管理器 打開“SQL Server Management Studio”窗口，右擊“對象資源管理器”窗格中的“數(shù)據(jù)庫”結點，在彈出菜單中選擇“新建數(shù)據(jù)庫”命令，如圖3-3所示。3-3 “新建數(shù)據(jù)庫新建數(shù)據(jù)庫”命令命令 打開如圖3-4所示的“新建數(shù)據(jù)庫”對話框，它由“常規(guī)”、“選項”和“文件組”三個選項組成。 在各個選項中，可以指定它們的參數(shù)值，例如，在“常規(guī)”選項中，可以指定數(shù)據(jù)庫名稱、數(shù)據(jù)庫的邏輯名、文件組、初始容量、增長方式和文件存儲路徑等。圖圖3-4 “新建數(shù)據(jù)庫新建數(shù)據(jù)庫”對話

11、框對話框 數(shù)據(jù)庫名稱：在“數(shù)據(jù)庫名稱”文本框中，輸入數(shù)據(jù)庫名稱，【例3-1】中要求為jxgl，在應用程序中則可以通過jxgl來訪問和操作數(shù)據(jù)庫。 邏輯名稱：數(shù)據(jù)庫創(chuàng)建以后，T-SQL語句是通過引用邏輯名稱來實現(xiàn)對數(shù)據(jù)庫操作的。其默認值與數(shù)據(jù)庫名相同，但可以更改，每個邏輯名稱是唯一的，與物理文件名稱相對應?！纠?-1】中要求為jxgl、jxgl_log。 物理文件名稱：用于存儲數(shù)據(jù)庫中數(shù)據(jù)的物理文件的名稱?！纠?-1】中要求為teching.mdf、teching_log.ldf。 文件類型：該項用于標識數(shù)據(jù)庫文件的類型，表明該文件是數(shù)據(jù)文件還是日志文件。 文件組：表示數(shù)據(jù)文件隸屬于哪個文件組，

12、創(chuàng)建后不能更改。文件組僅適用于數(shù)據(jù)文件，而不適用于日志文件。所有數(shù)據(jù)庫都必須有一個主文件組。 初始大?。涸擁棻硎緦獢?shù)據(jù)庫文件所占的磁盤空間的大小，單位為MB，默認為5（數(shù)據(jù)庫的初始大小至少是MODEL數(shù)據(jù)庫的大小），日志文件為1。 自動增長：數(shù)據(jù)文件的大小能夠自動增長。設置方式如圖3-5所示。 路徑：數(shù)據(jù)庫文件存放的物理位置，默認的路徑是C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA 。 單擊右邊帶有省略號（）的命令按鈕，打開一個資源管理器風格的對話框，可以在該對話框中更改數(shù)據(jù)庫文件的位置。圖圖3-5 自動增長自

13、動增長圖圖3-6 新創(chuàng)建的新創(chuàng)建的jxgl數(shù)據(jù)庫數(shù)據(jù)庫單擊“確定”按鈕，在“數(shù)據(jù)庫”的樹形結構中，就可看到剛創(chuàng)建的jxgl數(shù)據(jù)庫，如圖3-6所示。其中：database_name是新數(shù)據(jù)庫的名稱。數(shù)據(jù)庫名稱在 SQL Server 的實例中必須惟一，并且必須符合標識符規(guī)則。最長為128個字符。每個服務器管理的數(shù)據(jù)庫最多為32767個。ON：指定顯式定義用來存儲數(shù)據(jù)庫數(shù)據(jù)部分的磁盤文件(數(shù)據(jù)文件)。PRIMARY：指定關聯(lián)的 列表定義主文件。LOG ON ：指定顯式定義用來存儲數(shù)據(jù)庫日志的磁盤文件(日志文件)。2、使用T-SQL語句 命令格式：CREATE DATABASE database_n

14、ame ON PRIMARY ,n , ,n LOG ON ,n COLLATE collation_nameFOR ATTACH ：控制文件屬性。 NAME=logical_file_name：指定文件的邏輯名稱。 FILENAME= os_file_name ：指定操作系統(tǒng)使用的路徑和文件名.該操作系統(tǒng)文件名和NAME的邏輯名稱一一對應。 SIZE size：指定文件的初始大小。至少為模板Model數(shù)據(jù)庫大小，Model數(shù)據(jù)庫的主數(shù)據(jù)文件和日志文件的大小分別為3M和1M。 MAXSIZE max_size：指定文件可增大到的最大大小。 UNLIMITED：指定文件將增長到磁盤已滿。 FIL

15、EGROWTH growth_increment：指定需要新空間時為文件添加的空間量。該參數(shù)不能超過MAXSIZE的值。當指定數(shù)據(jù)為0時，表示文件不增長。增加量可為以KB、MB作后綴的字節(jié)數(shù)或以%作后綴的被增加容量文件的百分比來表示。 默認單位為MB。如果沒有指定FILEGROWTH，則默認值為10%，每次擴容的最小值為64KB。28 ：控制文件組屬性。不能對數(shù)據(jù)庫快照指定文件組。 FILEGROUP filegroup_name：文件組的邏輯名稱. filegroup_name 必須在數(shù)據(jù)庫中惟一，不能是系統(tǒng)提供的名稱 PRIMARY 和 PRIMARY_LOG。 DEFAULT：指定命名文

16、件組為數(shù)據(jù)庫中的默認文件組。關于T-SQL語句格式約定： SQL語句不區(qū)分大小寫，為了清晰，一般都用大寫表示系統(tǒng)保留字，用小寫表示用戶自定義的名稱。 “”表示該項可省略，省略時各參數(shù)取默認值； a|b表示a或b任選其一。 “，n”表示大括號括起來的內容可以重復寫多次。 【例3-2】用代碼實現(xiàn)，在D:sqllx目錄下創(chuàng)建一個jxgl數(shù)據(jù)庫，該數(shù)據(jù)庫的主數(shù)據(jù)文件邏輯名稱為jxgl，物理文件名為teching.mdf，初始大小為5MB，最大為20 MB，增長速度為20%；數(shù)據(jù)庫的日志文件邏輯名稱為jxgl _log，物理文件名為teching _log.ldf，初始大小為2MB，最大尺寸為10MB，

17、增長速度為1MB。CREATE DATABASE jxgl ON PRIMARY ( NAME=jxgl, FILENAME=D:sqllxteching.mdf, SIZE=5MB, MAXSIZE=20MB, FILEGROWTH=20%) LOG ON ( NAME=jxgl _log, FILENAME=D:sqllxteching_log.ldf, SIZE=2MB, MAXSIZE=10MB, FILEGROWTH=1MB )【補例2】 最簡形式的創(chuàng)建數(shù)據(jù)庫(不指定文件)語句。CREATE DATABASE sales補例3 不指定SIZE創(chuàng)建數(shù)據(jù)庫。CREATE DATABASE

18、 sales2ON ( NAME=Sales2_dat, FILENAME=E:DataBasesales2.mdf)二、查看數(shù)據(jù)庫信息 1.使用對象資源管理器 在“對象資源管理器”窗格中展開“數(shù)據(jù)庫”結點，右擊所要查看信息的數(shù)據(jù)庫，在彈出的快捷菜單中選擇“屬性”命令，出現(xiàn)如圖3-7所示的數(shù)據(jù)庫屬性設置對話框。圖圖3-7 數(shù)據(jù)庫屬性對話框數(shù)據(jù)庫屬性對話框2. 使用T-SQL語句語法格式為：sp_helpdb database_name 【例3-4】查看jxgl數(shù)據(jù)庫的基本信息。代碼如下：EXEC sp_helpdb jxgl如圖3-8所示,顯示了jxgl數(shù)據(jù)庫的所有者、狀態(tài)、創(chuàng)建時間、文件尺寸

19、、文件增長屬性等數(shù)據(jù)庫信息。 ：圖圖3-8數(shù)據(jù)庫數(shù)據(jù)庫jxgl文件的基本信息文件的基本信息1、 使用 “對象資源管理器” 展開“數(shù)據(jù)庫”結點，選中要打開的數(shù)據(jù)庫，右鍵，在出現(xiàn)的菜單中可以針對當前的數(shù)據(jù)庫進行相關操作，如圖3-9所示。圖圖3-9 在在“對象資源管理器對象資源管理器”窗格中打開數(shù)據(jù)庫窗格中打開數(shù)據(jù)庫三、打開或切換數(shù)據(jù)庫2. 使用T-SQL語句語法格式為： USE database_name例：use jxgl也可以直接通過數(shù)據(jù)庫下拉列表打開并切換，在查詢分析器中打開或切換數(shù)據(jù)庫，如圖3-10所示。圖3-10 在“查詢分析器”窗格中切換數(shù)據(jù)庫 四、修改數(shù)據(jù)庫1、 使用 “對象資源管理

20、器” 選中要修改的數(shù)據(jù)庫，右鍵，在彈出菜單中選擇“屬性”，選擇屬性中的“文件”選項，可以修改“初始大小”、“自動增長”中的“文件增長”和“最大文件大小”選項。如圖3-11所示。重新設定的數(shù)據(jù)庫分配空間必須大于現(xiàn)有空間。3738圖3-11 修改數(shù)據(jù)庫2、使用T-SQL語句修改數(shù)據(jù)庫命令格式：ALTER DATABASE database_nameADD FILE . TO FILEGROUP 文件組名 /*在文件組中增加數(shù)據(jù)文件*/ | ADD LOG FILE . /*增加日志文件*/ | REMOVE FILE 邏輯文件名 /*刪除數(shù)據(jù)文件*/ | ADD FILEGROUP 文件組名 /*

21、增加文件組*/ | REMOVE FILEGROUP 文件組名 /*刪除文件組*/ | MODIFY FILE /*更改文件屬性*/ | MODIFY NAME = 新數(shù)據(jù)庫名 /*數(shù)據(jù)庫更名*/ | MODIFY FILEGROUP 文件組名 | DEFAULT| NAME = 新文件組名 /*更改文件組屬性*/ | SET . WITH /*設置數(shù)據(jù)庫屬性*/ | COLLATE 排序名 /*指定數(shù)據(jù)庫排序規(guī)則*/39【例3-5】將兩個數(shù)據(jù)文件和一個事務日志文件添加到test數(shù)據(jù)庫中。代碼如下：ALTER DATABASE test ADD FILE -添加兩個次數(shù)據(jù)文件(NAME=test1,FILENAME=d:sqllxtest1.ndf,SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=5MB),(NAME=test2, FILENAME=d:sqllxtest2.ndf, SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB)40GOALTER DATABASE testADD LOGFILE (NAME=testlog1, -添加一個次日志文件FILENAME=d:sqllxtestlog1.ldf, SIZE=5MB, MAXSIZE=100MB,