數(shù)據(jù)庫基礎(chǔ)PPT第3章 SQL Server數(shù)據(jù)庫

第3章SQLServer數(shù)據(jù)庫3．1SQLServer數(shù)據(jù)庫概述3．2創(chuàng)建數(shù)據(jù)庫3．3管理和維護數(shù)據(jù)庫通過學(xué)習(xí)本章，讀者應(yīng)掌握以下內(nèi)容：了解數(shù)據(jù)庫及其對象熟練掌握用對象資源管理器創(chuàng)建和管理數(shù)據(jù)庫的方法熟練掌握用T-SQL語句創(chuàng)建和管理數(shù)據(jù)庫的方法3．1SQLServer數(shù)據(jù)庫概述SQLServer的數(shù)據(jù)庫是所涉及的對象以及數(shù)據(jù)的集合。它不僅反映數(shù)據(jù)本身的內(nèi)容，而且反映對象以及數(shù)據(jù)之間的聯(lián)系。SQLServer將數(shù)據(jù)庫映射為一組操作系統(tǒng)文件。數(shù)據(jù)和日志信息從不混合在相同的文件中，而且各文件僅在一個數(shù)據(jù)庫中使用。3.1.1數(shù)據(jù)庫文件1．?dāng)?shù)據(jù)庫文件分類

SQLServer2019數(shù)據(jù)庫具有以下3種類型的文件。(1)主數(shù)據(jù)文件每個數(shù)據(jù)庫都有一個主數(shù)據(jù)文件。主數(shù)據(jù)文件的推薦文件擴展名是.mdf。(2)次要數(shù)據(jù)文件除主數(shù)據(jù)文件以外的所有其他數(shù)據(jù)文件都是次要數(shù)據(jù)文件。某些數(shù)據(jù)庫可能不含有任何次要數(shù)據(jù)文件，而有些數(shù)據(jù)庫則含有多個次要數(shù)據(jù)文件。次要數(shù)據(jù)文件的推薦文件擴展名是.ndf。(3)事務(wù)日志文件日志文件包含著用于恢復(fù)數(shù)據(jù)庫的所有日志信息。每個數(shù)據(jù)庫必須至少有一個日志文件，當(dāng)然也可以有多個。日志文件的推薦文件擴展名是.ldf。在SQLServer2019中，數(shù)據(jù)庫中所有文件的位置都記錄在數(shù)據(jù)庫的主文件和master數(shù)據(jù)庫中。大多數(shù)情況下，數(shù)據(jù)庫引擎使用master數(shù)據(jù)庫中的文件位置信息。2．邏輯文件名和物理文件名

SQLServer2019的文件擁有兩個名稱，即邏輯文件名和物理文件名。(1)邏輯文件名（logical_file_name）

logical_file_name即邏輯文件名，是在所有Transact-SQL語句中引用物理文件時所使用的名稱。邏輯文件名必須符合SQLserver標(biāo)識符規(guī)則，而且在數(shù)據(jù)庫中的邏輯文件名中必須是唯一的。(2)物理文件名（os_file_name）

os_file_name是包括目錄路徑的物理文件名。它必須符合操作系統(tǒng)的文件命名規(guī)則。3．文件大?。?）初始大?。?）最大大?。梢允菬o限制的，根據(jù)磁盤空間而定）（3）自動增長（可以按MB增長，也可以按百分比增長）

SQLServer2019文件可以從它們最初指定的大小開始按給定的增長比例自動增長直至最大大小。3.1.2數(shù)據(jù)庫文件組

為便于分配和管理，可以將數(shù)據(jù)庫對象和文件一起分成文件組。SQLServer2019有以下兩種類型的文件組。（1）主文件組PRIMARY

主文件組包含主數(shù)據(jù)文件和任何沒有明確分配給其他文件組的其他文件。系統(tǒng)表的所有頁均分配在主文件組中。（2）用戶定義文件組用戶定義文件組是通過在CREATEDATABASE或ALTERDATABASE語句中使用FILEGROUP關(guān)鍵字指定的任何文件組。日志文件不包括在文件組內(nèi)。日志空間與數(shù)據(jù)空間分開管理。一個文件不可以是多個文件組的成員。每個數(shù)據(jù)庫中均有一個文件組被指定為默認(rèn)文件組。3.1.3數(shù)據(jù)庫對象SQLServer2019中有以下數(shù)據(jù)庫對象。(1)表：數(shù)據(jù)庫中的表與我們?nèi)粘Ｉ钪惺褂玫谋砀耦愃?，由列和行組成。其中每一列都代表一個相同類型的數(shù)據(jù)。每列又稱為一個字段，每列的標(biāo)題稱為字段名。每個表中通常都有一個主關(guān)鍵字，用于唯一標(biāo)識一條記錄。(2)索引：某個表中一列或若干列值的集合與相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。它提供了數(shù)據(jù)庫中編排表中數(shù)據(jù)的內(nèi)部方法。(3)視圖：視圖看上去同表相似，具有一組命名的字段和數(shù)據(jù)項，但它其實是一個虛擬的表，在數(shù)據(jù)庫中并不實際存在。視圖是由查詢數(shù)據(jù)庫表或其他視圖產(chǎn)生的，它限制了用戶能看到和修改的數(shù)據(jù)。(4)關(guān)系圖表：關(guān)系圖表其實就是數(shù)據(jù)庫表之間的關(guān)系示意圖。利用它可以編輯表與表之間的關(guān)系。(5)默認(rèn)值：默認(rèn)值是當(dāng)在表中創(chuàng)建列或插入數(shù)據(jù)時，為沒有指定具體值的列或列數(shù)據(jù)項賦予事先設(shè)定好的值。(6)約束：是SQLServer實施數(shù)據(jù)一致性和數(shù)據(jù)完整性的方法，或者說是一套機制，包括主鍵約束、外鍵約束、Unique約束、Check約束、默認(rèn)值和允許空六種機制。(7)規(guī)則：用來限制數(shù)據(jù)表中字段的有限范圍，以確保列中數(shù)據(jù)完整性的一種方式。(8)觸發(fā)器：一種特殊的存儲過程，與表格或某些操作相關(guān)聯(lián)。當(dāng)用戶對數(shù)據(jù)進行插入、修改、刪除或?qū)?shù)據(jù)庫表進行建立、修改、刪除時激活，并自動執(zhí)行。(9)存儲過程：一組經(jīng)過編譯的可以重復(fù)使用的T-SQL代碼的組合。它是經(jīng)過編譯存儲到數(shù)據(jù)庫中的，所以運行速度要比執(zhí)行相同的SQL語句快。(10)登錄：SQLServer訪問控制允許連接到服務(wù)器的賬戶。(11)用戶：用戶是指擁有一定權(quán)限的數(shù)據(jù)庫的使用者。(12)角色：數(shù)據(jù)庫操作權(quán)限的集合，可以將角色關(guān)聯(lián)到同一類級別的用戶。3.1.4系統(tǒng)數(shù)據(jù)庫1．Master數(shù)據(jù)庫master數(shù)據(jù)庫記錄SQLServer2019實例的所有系統(tǒng)級信息不能在master數(shù)據(jù)庫中執(zhí)行下列操作。(1)添加文件或文件組。(2)更改排序規(guī)則。默認(rèn)排序規(guī)則為服務(wù)器排序規(guī)則。(3)更改數(shù)據(jù)庫所有者。master數(shù)據(jù)庫歸dbo所有。(4)創(chuàng)建全文目錄或全文索引。(5)在數(shù)據(jù)庫的系統(tǒng)表上創(chuàng)建觸發(fā)器。(6)刪除數(shù)據(jù)庫。

(7)從數(shù)據(jù)庫中刪除guest用戶。(8)參與數(shù)據(jù)庫鏡像。(9)刪除主文件組、主數(shù)據(jù)文件或日志文件。(10)重命名數(shù)據(jù)庫或主文件組。(11)將數(shù)據(jù)庫設(shè)置為OFFLINE。(12)將數(shù)據(jù)庫或主文件組設(shè)置為READONLY。2．Model數(shù)據(jù)庫

model數(shù)據(jù)庫是在SQLServer2019實例上創(chuàng)建所有數(shù)據(jù)庫的模板。對model數(shù)據(jù)庫進行的修改(如數(shù)據(jù)庫大小、排序規(guī)則、恢復(fù)模式和其他數(shù)據(jù)庫選項)將應(yīng)用于以后創(chuàng)建的數(shù)據(jù)庫。3．Msdb數(shù)據(jù)庫msdb數(shù)據(jù)庫由SQLServer代理來計劃警報和作業(yè)以及與備份和恢復(fù)相關(guān)的信息，尤其是SQLServerAgent需要使用它來執(zhí)行安排工作和警報、記錄操作者等操作。4．Tempdb數(shù)據(jù)庫tempdb數(shù)據(jù)庫是連接到SQLServer2019實例的所有用戶都可以使用的全局資源，保存所有臨時表和臨時存儲過程。3．2創(chuàng)建數(shù)據(jù)庫若要創(chuàng)建數(shù)據(jù)庫，必須確定數(shù)據(jù)庫的名稱、所有者、大小以及存儲該數(shù)據(jù)庫的文件和文件組。創(chuàng)建數(shù)據(jù)庫的注意事項如下。（1）創(chuàng)建數(shù)據(jù)庫需要一定許可，在默認(rèn)情況下，只有系統(tǒng)管理員和數(shù)據(jù)庫擁有者可以創(chuàng)建數(shù)據(jù)庫。數(shù)據(jù)庫被創(chuàng)建后，創(chuàng)建數(shù)據(jù)庫的用戶自動成為該數(shù)據(jù)庫的所有者。（2）創(chuàng)建數(shù)據(jù)庫的過程實際上就是為數(shù)據(jù)庫設(shè)計名稱、設(shè)計所占用的存儲空間和存放文件位置的過程等，數(shù)據(jù)庫名字必須遵循SQLServer命名規(guī)范。（3）所有的新數(shù)據(jù)庫都是系統(tǒng)樣本數(shù)據(jù)庫model的副本。（4）單個數(shù)據(jù)庫可以存儲在單個文件上，也可以跨越多個文件存儲。（5）數(shù)據(jù)庫的大小可以被增大或者收縮。（6）當(dāng)新的數(shù)據(jù)庫創(chuàng)建時，SQLServer自動更新“sysdatabases”系統(tǒng)表。3.2.1用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫比使用T-SQL語句來得更容易。具體操作如下。從桌面上依次選擇“開始”→SQLServerManagementStudio，打開SQLServerManagementStudio窗口，設(shè)置好服務(wù)器類型、服務(wù)器名稱、身份驗證、用戶名和密碼，并單擊“連接”按鈕。3.2.2用SQL命令創(chuàng)建數(shù)據(jù)庫其語法格式如下。CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[,…n]][,<filegroupspec>[,…n]]][LOGON{<filespec>[,…n]}][COLLATEcollation_name][FORATTACH]

進一步把<filespec>定義為：[PRIMARY]([NAME=logical_file_name,]FILENAME=‘OS_file_name’

[,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n]把<filesgroup>定義為：FILEGROUPfilegroup_name<filespec>[,...n]1．語法中的符號及參數(shù)說明(1)

[]：表示可選語法項，省略時各參數(shù)取默認(rèn)值。(2)

[,...n]：表示該選項的內(nèi)容可以重復(fù)多次。(3)

{}：表示必選項。有相應(yīng)子句時，{}中的內(nèi)容是必選的。(4)

<>：表示在實際的語句中要用相應(yīng)的內(nèi)容替代。(5)文字大寫：說明該文字是T-SQL的關(guān)鍵字。(6)文字小寫：說明該文字是用戶提供的T-SQL語法的參數(shù)。(7)database_name：用戶所要創(chuàng)建的數(shù)據(jù)庫名稱，最長不能超過128個字符，在一個SQLServer實例中，數(shù)據(jù)庫名稱是唯一的。(8)

ON：指定存放數(shù)據(jù)庫的數(shù)據(jù)文件信息，說明數(shù)據(jù)庫是根據(jù)后面的參數(shù)創(chuàng)建的。(9)

PRIMARY：用于指定主文件組中的文件。主文件組中的第一個由<filespec>指定的文件是主數(shù)據(jù)文件。若不指定PRIMARY關(guān)鍵字，則在命令中列出的第一個文件將被默認(rèn)為主數(shù)據(jù)文件。(10)LOGON：指定日志文件的明確定義。如沒有此項，系統(tǒng)會自動創(chuàng)建一個為所有數(shù)據(jù)文件總和1/4大小或512KB大小的日志文件。(11)

COLLATEcollation_name：指定數(shù)據(jù)庫默認(rèn)排序規(guī)則。規(guī)則名稱可以是Windows排序規(guī)則名稱，也可以是SQL排序規(guī)則名稱。(12)

<filespec>：指定文件的屬性。NAME=logical_file_name：定義數(shù)據(jù)文件的邏輯名稱，此名稱在數(shù)據(jù)庫中必須唯一。FILENAME='os_file_name'：定義數(shù)據(jù)文件的物理名稱，包括物理文件使用的路徑名和文件名。SIZE=size：文件屬性中定義文件的初始值，指定為整數(shù)。MAXSIZE=max_size：文件屬性中定義文件可以增長到的最大值，可以使用KB、MB、GB或TB單位，默認(rèn)值是MB，指定為整數(shù)。如果沒有指定或?qū)憺閡nlimited，那么文件將增長到磁盤變滿為止。FILEGROWTH=growth_increment：定義文件的自動增長，growth_increment定義每次增長的大小。(13)FILEGROUP<filegroup_name>：定義文件組的控制。filegroup_name：必須是數(shù)據(jù)庫中唯一的，不能是系統(tǒng)提供的名稱PRIMARY。DEFAULT：指定命名文件組為數(shù)據(jù)庫中的默認(rèn)文件組。2．注意事項（1）創(chuàng)建用戶數(shù)據(jù)庫后，要備份master數(shù)據(jù)庫。（2）所有數(shù)據(jù)庫都至少包含一個主文件組。所有系統(tǒng)表都分配在主文件組中。數(shù)據(jù)庫還可以包含用戶定義的文件組。（3）每個數(shù)據(jù)庫都有一個所有者，可在數(shù)據(jù)庫中執(zhí)行某些特殊的活動。數(shù)據(jù)庫所有者是創(chuàng)建數(shù)據(jù)庫的用戶，也可以使用sp_changedbowner更改數(shù)據(jù)庫所有者。（4）創(chuàng)建數(shù)據(jù)庫的權(quán)限默認(rèn)地授予sysadmin和dbcreator固定服務(wù)器角色的成員?！纠?.1】創(chuàng)建一個名為“jxgl”的數(shù)據(jù)庫。其中主數(shù)據(jù)文件為10MB，最大大小不受限制，每次增長1MB；事務(wù)日志文件大小為1MB，最大大小不受限制，文件每次增長10%。

CREATEDATABASEjxgl /*數(shù)據(jù)庫名*/ONPRIMARY /*主文件組*/(NAME='jxgl', /*主數(shù)據(jù)文件邏輯名*/FILENAME='D:\Data\jxgl.mdf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)LOGON(NAME='jxgl_log',FILENAME='D:\Data\jxgl_log.ldf',SIZE=1MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)【例3.2】創(chuàng)建test數(shù)據(jù)庫，包含一個主文件組和兩個次文件組。CREATEDATABASEtestONPRIMARY/*定義在主文件組上的文件*/(NAME=pri_file1,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\pri_file1.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=pri_file2,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\pri_file2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=15%),FILEGROUPGrp1/*定義在次文件組Grp1上的文件*/(NAME=Grp1_file1,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\Grp1_file1.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=Grp1_file2,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\Grp1_file2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUPGrp2/*定義在次文件組Grp2上的文件*/(NAME=Grp2_file1,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\Grp2_file1.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=Grp2_file2,FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\Grp2_file2.ndf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON/*定義事務(wù)日志文件*/(NAME='test_log',FILENAME='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\test_log.ldf',SIZE=5,MAXSIZE=25,FILEGROWTH=5)GO3.2.3事務(wù)日志SQL創(chuàng)建數(shù)據(jù)庫的時候，會同時創(chuàng)建事務(wù)日志文件。事務(wù)日志是存放恢復(fù)數(shù)據(jù)所需的所有信息，是數(shù)據(jù)庫中已發(fā)生的所有修改和執(zhí)行每次修改的事務(wù)的一連串記錄。當(dāng)數(shù)據(jù)庫損壞時，管理員可以使用事務(wù)日志還原數(shù)據(jù)庫。每一個數(shù)據(jù)庫必須至少擁有一個事務(wù)日志文件，允許擁有多個日志文件。事務(wù)日志文件的擴展名為.ldf，日志文件的大小至少是512KB。1.事務(wù)日志文件和數(shù)據(jù)文件必須分開存放，優(yōu)點如下。（1）事務(wù)日志文件可以單獨備份。（2）有可能從服務(wù)器失效的事件中將服務(wù)器恢復(fù)到最近的狀態(tài)。（3）事務(wù)日志不會搶占數(shù)據(jù)庫的空間。（4）可以很容易地監(jiān)測到事務(wù)日志的空間。（5）在向數(shù)據(jù)文件和事務(wù)日志文件寫入數(shù)據(jù)時會產(chǎn)生較少的沖突，這有利于提高SQL的性能。2.事務(wù)日志的工作過程（1）應(yīng)用程序發(fā)出一個修改數(shù)據(jù)庫中的對象的事務(wù)。（2）當(dāng)這個事務(wù)開始時，事務(wù)日志會記錄一個事務(wù)開始標(biāo)志，并將被影響的數(shù)據(jù)頁從磁盤讀入緩沖區(qū)。（3）事務(wù)中每個數(shù)據(jù)更改語句都被記錄在日志文件中，日志文件將記錄一個提交事務(wù)的標(biāo)記。每一個事務(wù)都會以這種方式記錄在事務(wù)日志中并被立即寫到硬盤上。（4）在緩沖區(qū)中修改相應(yīng)的數(shù)據(jù)。這些數(shù)據(jù)一直在緩沖區(qū)中，在檢查點進程發(fā)生時，檢查點進程把所有修改過的數(shù)據(jù)頁寫到數(shù)據(jù)庫中，并在事務(wù)日志中寫入一個檢查點標(biāo)志，這個標(biāo)志用于在數(shù)據(jù)庫恢復(fù)過程中確定事務(wù)的起點和終點，以及哪些事務(wù)已經(jīng)作用于數(shù)據(jù)庫了。3.3管理和維護數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫后，我們開始使用數(shù)據(jù)庫。通常有打開、查看、修改、刪除等幾種方式對數(shù)據(jù)庫進行管理和維護。3.3.1打開或切換數(shù)據(jù)庫使用Transact-SQL命令方式打開并切換數(shù)據(jù)庫的語法格式如下。USEdatabase_namedatabase_name：要打開并切換的數(shù)據(jù)庫名。3.3.2查看數(shù)據(jù)庫信息1．使用SQLServerManagementStudio查看數(shù)據(jù)庫信息2．使用Transact-SQL語句查看數(shù)據(jù)庫信息使用系統(tǒng)存儲過程sp_helpdb可以查看數(shù)據(jù)庫信息，其語法格式如下。[EXECUTE]sp_helpdb[數(shù)據(jù)庫名]3.3.3用SQLServerManagementStudio修改數(shù)據(jù)庫配置（1）啟動SQLServerManagementStudio，連接上數(shù)據(jù)庫實例，展開“對象資源管理器”里的樹型目錄，定位到要修改的數(shù)據(jù)庫上。（2）右擊要修改的數(shù)據(jù)庫，例如我們選擇“jxgl”，彈出快捷菜單，選擇“屬性”選項，其余操作與創(chuàng)建數(shù)據(jù)庫的過程相似。3.3.4用T-SQL命令修改數(shù)據(jù)庫配置

ALTERDATABASEdatabase_name{ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUPfilegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_database_name|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}}語法中的各參數(shù)說明如下。(1)ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]：向指定的文件組添加新的數(shù)據(jù)文件。(2)ADDLOGFILE<filespec>[,...n]：增加新的日志文件。(3)REMOVEFILElogical_file_name：從數(shù)據(jù)庫系統(tǒng)表中刪除文件描述和物理文件。(4)ADDFILEGROUPfilegroup_name：增加一個文件組。(5)REMOVEFILEGROUPfilegroup_name：刪除指定的文件組。(6)MODIFYFILE<filespec>：修改物理文件。(7)MODIFYNAME=new_database_name：重命名數(shù)據(jù)庫。(8)MODIFYFIL