數(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管理和維護(hù)數(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ù)和日志信息從不混合在相同的文件中，而且各文件僅在一個(gè)數(shù)據(jù)庫中使用。3.1.1數(shù)據(jù)庫文件1．?dāng)?shù)據(jù)庫文件分類

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

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

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

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

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

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

主文件組包含主數(shù)據(jù)文件和任何沒有明確分配給其他文件組的其他文件。系統(tǒng)表的所有頁均分配在主文件組中。（2）用戶定義文件組用戶定義文件組是通過在CREATEDATABASE或ALTERDATABASE語句中使用FILEGROUP關(guān)鍵字指定的任何文件組。日志文件不包括在文件組內(nèi)。日志空間與數(shù)據(jù)空間分開管理。一個(gè)文件不可以是多個(gè)文件組的成員。每個(gè)數(shù)據(jù)庫中均有一個(gè)文件組被指定為默認(rèn)文件組。3.1.3數(shù)據(jù)庫對象SQLServer2019中有以下數(shù)據(jù)庫對象。(1)表：數(shù)據(jù)庫中的表與我們?nèi)粘Ｉ钪惺褂玫谋砀耦愃?，由列和行組成。其中每一列都代表一個(gè)相同類型的數(shù)據(jù)。每列又稱為一個(gè)字段，每列的標(biāo)題稱為字段名。每個(gè)表中通常都有一個(gè)主關(guān)鍵字，用于唯一標(biāo)識一條記錄。(2)索引：某個(gè)表中一列或若干列值的集合與相應(yīng)的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。它提供了數(shù)據(jù)庫中編排表中數(shù)據(jù)的內(nèi)部方法。(3)視圖：視圖看上去同表相似，具有一組命名的字段和數(shù)據(jù)項(xiàng)，但它其實(shí)是一個(gè)虛擬的表，在數(shù)據(jù)庫中并不實(shí)際存在。視圖是由查詢數(shù)據(jù)庫表或其他視圖產(chǎn)生的，它限制了用戶能看到和修改的數(shù)據(jù)。(4)關(guān)系圖表：關(guān)系圖表其實(shí)就是數(shù)據(jù)庫表之間的關(guān)系示意圖。利用它可以編輯表與表之間的關(guān)系。(5)默認(rèn)值：默認(rèn)值是當(dāng)在表中創(chuàng)建列或插入數(shù)據(jù)時(shí)，為沒有指定具體值的列或列數(shù)據(jù)項(xiàng)賦予事先設(shè)定好的值。(6)約束：是SQLServer實(shí)施數(shù)據(jù)一致性和數(shù)據(jù)完整性的方法，或者說是一套機(jī)制，包括主鍵約束、外鍵約束、Unique約束、Check約束、默認(rèn)值和允許空六種機(jī)制。(7)規(guī)則：用來限制數(shù)據(jù)表中字段的有限范圍，以確保列中數(shù)據(jù)完整性的一種方式。(8)觸發(fā)器：一種特殊的存儲過程，與表格或某些操作相關(guān)聯(lián)。當(dāng)用戶對數(shù)據(jù)進(jìn)行插入、修改、刪除或?qū)?shù)據(jù)庫表進(jìn)行建立、修改、刪除時(shí)激活，并自動(dòng)執(zhí)行。(9)存儲過程：一組經(jīng)過編譯的可以重復(fù)使用的T-SQL代碼的組合。它是經(jīng)過編譯存儲到數(shù)據(jù)庫中的，所以運(yùn)行速度要比執(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實(shí)例的所有系統(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實(shí)例上創(chuàng)建所有數(shù)據(jù)庫的模板。對model數(shù)據(jù)庫進(jìn)行的修改(如數(shù)據(jù)庫大小、排序規(guī)則、恢復(fù)模式和其他數(shù)據(jù)庫選項(xiàng))將應(yīng)用于以后創(chuàng)建的數(shù)據(jù)庫。3．Msdb數(shù)據(jù)庫msdb數(shù)據(jù)庫由SQLServer代理來計(jì)劃警報(bào)和作業(yè)以及與備份和恢復(fù)相關(guān)的信息，尤其是SQLServerAgent需要使用它來執(zhí)行安排工作和警報(bào)、記錄操作者等操作。4．Tempdb數(shù)據(jù)庫tempdb數(shù)據(jù)庫是連接到SQLServer2019實(shí)例的所有用戶都可以使用的全局資源，保存所有臨時(shí)表和臨時(shí)存儲過程。3．2創(chuàng)建數(shù)據(jù)庫若要?jiǎng)?chuàng)建數(shù)據(jù)庫，必須確定數(shù)據(jù)庫的名稱、所有者、大小以及存儲該數(shù)據(jù)庫的文件和文件組。創(chuàng)建數(shù)據(jù)庫的注意事項(xiàng)如下。（1）創(chuàng)建數(shù)據(jù)庫需要一定許可，在默認(rèn)情況下，只有系統(tǒng)管理員和數(shù)據(jù)庫擁有者可以創(chuàng)建數(shù)據(jù)庫。數(shù)據(jù)庫被創(chuàng)建后，創(chuàng)建數(shù)據(jù)庫的用戶自動(dòng)成為該數(shù)據(jù)庫的所有者。（2）創(chuàng)建數(shù)據(jù)庫的過程實(shí)際上就是為數(shù)據(jù)庫設(shè)計(jì)名稱、設(shè)計(jì)所占用的存儲空間和存放文件位置的過程等，數(shù)據(jù)庫名字必須遵循SQLServer命名規(guī)范。（3）所有的新數(shù)據(jù)庫都是系統(tǒng)樣本數(shù)據(jù)庫model的副本。（4）單個(gè)數(shù)據(jù)庫可以存儲在單個(gè)文件上，也可以跨越多個(gè)文件存儲。（5）數(shù)據(jù)庫的大小可以被增大或者收縮。（6）當(dāng)新的數(shù)據(jù)庫創(chuàng)建時(shí)，SQLServer自動(dòng)更新“sysdatabases”系統(tǒng)表。3.2.1用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫使用SQLServerManagementStudio創(chuàng)建數(shù)據(jù)庫比使用T-SQL語句來得更容易。具體操作如下。從桌面上依次選擇“開始”→SQLServerManagementStudio，打開SQLServerManagementStudio窗口，設(shè)置好服務(wù)器類型、服務(wù)器名稱、身份驗(yàn)證、用戶名和密碼，并單擊“連接”按鈕。3.2.2用SQL命令創(chuàng)建數(shù)據(jù)庫其語法格式如下。CREATEDATABASEdatabase_name[ON[PRIMARY][<filespec>[,…n]][,<filegroupspec>[,…n]]][LOGON{<filespec>[,…n]}][COLLATEcollation_name][FORATTACH]

進(jìn)一步把<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)

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

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

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

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

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

PRIMARY：用于指定主文件組中的文件。主文件組中的第一個(gè)由<filespec>指定的文件是主數(shù)據(jù)文件。若不指定PRIMARY關(guān)鍵字，則在命令中列出的第一個(gè)文件將被默認(rèn)為主數(shù)據(jù)文件。(10)LOGON：指定日志文件的明確定義。如沒有此項(xiàng)，系統(tǒng)會自動(dòng)創(chuàng)建一個(gè)為所有數(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：定義文件的自動(dòng)增長，growth_increment定義每次增長的大小。(13)FILEGROUP<filegroup_name>：定義文件組的控制。filegroup_name：必須是數(shù)據(jù)庫中唯一的，不能是系統(tǒng)提供的名稱PRIMARY。DEFAULT：指定命名文件組為數(shù)據(jù)庫中的默認(rèn)文件組。2．注意事項(xiàng)（1）創(chuàng)建用戶數(shù)據(jù)庫后，要備份master數(shù)據(jù)庫。（2）所有數(shù)據(jù)庫都至少包含一個(gè)主文件組。所有系統(tǒng)表都分配在主文件組中。數(shù)據(jù)庫還可以包含用戶定義的文件組。（3）每個(gè)數(shù)據(jù)庫都有一個(gè)所有者，可在數(shù)據(jù)庫中執(zhí)行某些特殊的活動(dòng)。數(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)建一個(gè)名為“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ù)庫，包含一個(gè)主文件組和兩個(gè)次文件組。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ù)庫的時(shí)候，會同時(shí)創(chuàng)建事務(wù)日志文件。事務(wù)日志是存放恢復(fù)數(shù)據(jù)所需的所有信息，是數(shù)據(jù)庫中已發(fā)生的所有修改和執(zhí)行每次修改的事務(wù)的一連串記錄。當(dāng)數(shù)據(jù)庫損壞時(shí)，管理員可以使用事務(wù)日志還原數(shù)據(jù)庫。每一個(gè)數(shù)據(jù)庫必須至少擁有一個(gè)事務(wù)日志文件，允許擁有多個(gè)日志文件。事務(wù)日志文件的擴(kuò)展名為.ldf，日志文件的大小至少是512KB。1.事務(wù)日志文件和數(shù)據(jù)文件必須分開存放，優(yōu)點(diǎn)如下。（1）事務(wù)日志文件可以單獨(dú)備份。（2）有可能從服務(wù)器失效的事件中將服務(wù)器恢復(fù)到最近的狀態(tài)。（3）事務(wù)日志不會搶占數(shù)據(jù)庫的空間。（4）可以很容易地監(jiān)測到事務(wù)日志的空間。（5）在向數(shù)據(jù)文件和事務(wù)日志文件寫入數(shù)據(jù)時(shí)會產(chǎn)生較少的沖突，這有利于提高SQL的性能。2.事務(wù)日志的工作過程（1）應(yīng)用程序發(fā)出一個(gè)修改數(shù)據(jù)庫中的對象的事務(wù)。（2）當(dāng)這個(gè)事務(wù)開始時(shí)，事務(wù)日志會記錄一個(gè)事務(wù)開始標(biāo)志，并將被影響的數(shù)據(jù)頁從磁盤讀入緩沖區(qū)。（3）事務(wù)中每個(gè)數(shù)據(jù)更改語句都被記錄在日志文件中，日志文件將記錄一個(gè)提交事務(wù)的標(biāo)記。每一個(gè)事務(wù)都會以這種方式記錄在事務(wù)日志中并被立即寫到硬盤上。（4）在緩沖區(qū)中修改相應(yīng)的數(shù)據(jù)。這些數(shù)據(jù)一直在緩沖區(qū)中，在檢查點(diǎn)進(jìn)程發(fā)生時(shí)，檢查點(diǎn)進(jìn)程把所有修改過的數(shù)據(jù)頁寫到數(shù)據(jù)庫中，并在事務(wù)日志中寫入一個(gè)檢查點(diǎn)標(biāo)志，這個(gè)標(biāo)志用于在數(shù)據(jù)庫恢復(fù)過程中確定事務(wù)的起點(diǎn)和終點(diǎn)，以及哪些事務(wù)已經(jīng)作用于數(shù)據(jù)庫了。3.3管理和維護(hù)數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫后，我們開始使用數(shù)據(jù)庫。通常有打開、查看、修改、刪除等幾種方式對數(shù)據(jù)庫進(jìn)行管理和維護(hù)。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）啟動(dòng)SQLServerManagementStudio，連接上數(shù)據(jù)庫實(shí)例，展開“對象資源管理器”里的樹型目錄，定位到要修改的數(shù)據(jù)庫上。（2）右擊要修改的數(shù)據(jù)庫，例如我們選擇“jxgl”，彈出快捷菜單，選擇“屬性”選項(xiàng)，其余操作與創(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：增加一個(gè)文件組。(5)REMOVEFILEGROUPfilegroup_name：刪除指定的文件組。(6)MODIFYFILE<filespec>：修改物理文件。(7)MODIFYNAME=new_database_name：重命名數(shù)據(jù)庫。(8)MODIFYFIL