第2章數據庫管理

2.1數據庫的構成2.2Transact-SQL語言2.3建立數據庫2.4發(fā)布和訂閱數據庫小結習題

2.1數據庫的構成

2.1.1數據庫基礎知識

1.數據庫技術的出現和發(fā)展

對現實世界的描述，最終都表現為數據。數據是用于載荷信息的物理符號。當用計算機處理這些數據時，需要對它們進行組織、存儲、加工、維護，即進行數據管理。數據管理是非常必要的，特別是當數據量非常大的時候。程序是如何處理數據的，或者說程序和數據間的關系是本章要討論的問題。隨著計算機技術的發(fā)展，數據管理技術也經歷了人工管理階段、文件管理階段和數據庫管理階段。

1)人工管理階段

圖2-1人工管理階段程序與數據間的關系人工管理階段程序與數據間的關系如圖2-1所示。圖2-1人工管理階段程序與數據間的關系

2)文件管理階段

文件管理階段程序與數據間的關系如圖2-2所示。圖2-2文件管理階段程序與數據間的關系

3)數據庫管理階段

數據庫管理階段程序與數據間的關系如圖2-3所示。圖2-3數據庫管理階段程序與數據間的關系

2.數據庫系統(tǒng)構成

1)數據庫系統(tǒng)術語

數據庫(DataBase，DB)是存儲在計算機內有組織的、統(tǒng)一管理的相關數據的集合。DB可以為各種用戶共享，具有最小冗余度和較高的數據獨立性。

數據庫管理系統(tǒng)(DataBaseManagementSystem，DBMS)是位于用戶和操作系統(tǒng)之間的數據管理軟件。DBMS為用戶或應用程序提供訪問DB的方法，包括DB的建立、查詢、更新和各種數據控制。

2)數據庫系統(tǒng)層次結構

數據庫系統(tǒng)是指采用數據庫技術的計算機系統(tǒng)，它包括數據庫、數據庫管理系統(tǒng)和構成這一計算機系統(tǒng)的其他部分(如計算機硬件、支撐軟件、操作人員等)，它們之間的關系如圖2-4所示。圖2-4數據庫系統(tǒng)結構圖2.1.2SQLServer數據庫類型

1.系統(tǒng)數據庫

1)?Master數據庫

Master數據庫為系統(tǒng)數據庫，是SQLServer2005最重要的數據庫，包含用戶登錄標識、系統(tǒng)配置信息、初始化等系統(tǒng)級信息，用于控制整個SQLServer2005系統(tǒng)的運行。

Master數據庫的內容十分豐富，其中包含6個系統(tǒng)表、284個系統(tǒng)視圖、1254個存儲過程和149個擴展存儲過程，在企業(yè)管理器中可以很方便地查看這些數據庫對象。表2-1列出了Master數據庫中的常用系統(tǒng)表，表2-2列出了Master數據庫中的常用存儲過程和擴展存儲過程。

2)?Model數據庫

Model數據庫包含19個系統(tǒng)表，另外還有一些視圖。表2-3列出了Model數據庫(因而也是用戶數據庫)中的幾個常用系統(tǒng)表。

3)?Msdb數據庫

SQLServer、SQL-MS和SQLServerAgent使用Msdb數據庫來儲存計劃信息、與備份和恢復相關的信息，尤其是SQLServerAgent需要使用Msdb數據庫安排工作和警報、記錄操作信息等。

4)?Resource數據庫

Resource數據庫是只讀數據庫，它包含了SQLServer2005中的所有系統(tǒng)對象。SQLServer系統(tǒng)對象(例如sys.objects)在物理上持續(xù)存在于Resource數據庫中，但在邏輯上，它們出現在每個數據庫的sys架構中。

5)?Tempdb

Tempdb為臨時數據庫，用于存儲用戶建立的臨時表、臨時觸發(fā)器、用戶聲明的全局變量和用戶通過游標(參見5.3節(jié))篩選出來的數據，并為數據排序提供一個臨時性工作空間等。

使用Tempdb數據庫不需要特殊的權限，不管SQLServer2005中安裝了多少個數據庫，Tempdb數據庫只有這一個。幾乎所有的查詢都有可能使用Tempdb數據庫，因此Tempdb數據庫是SQLServer2005中負擔最重的一個數據庫。

2.樣本數據庫

樣本數據庫實際也是用戶數據庫，不同的是樣本數據庫是SQLServer2005提供的數據庫實例。AdventureWorks示例數據庫所基于的虛構公司，是一家大型跨國生產公司。公司生產金屬和復合材料的自行車，產品遠銷北美、歐洲和亞洲市場。公司總部設在華盛頓州的伯瑟爾市，擁有299名雇員，而且擁有多個活躍在世界各地的地區(qū)性銷售團隊。2.1.3數據庫中的對象

1.表(Table)

表用來存放數據，數據庫中的數據實際上都是儲存在表中的。

2.視圖(View)

視圖實質是一張?zhí)摂M的表，用來存儲在數據庫中預先定義好的查詢。雖然視圖具有表的外觀，可以像表一樣進行訪問，但它并不占據物理存儲空間。

3.索引(Index)

索引是一個指向表中數據的指針，其形式和書籍的目錄相類似。建立索引是為了提高檢索表中數據的速度，但它要占用一定的物理空間。

4.存儲過程(StoredProcedure)

存儲過程也稱為函數或程序。它是存儲在數據庫中的一組相關的SQL語句，經過預編譯后，隨時可供用戶調用執(zhí)行。使用存儲過程主要是為了減少網絡流量，同時可以提高SQLServer編程的效率。

5.觸發(fā)器(Trigger)

觸發(fā)器是一種特殊的存儲過程。當對表執(zhí)行了某種操作后，就會觸發(fā)相應的觸發(fā)器的執(zhí)行。通常包括INSERT、DELETE和UPDATE三種觸發(fā)器。使用觸發(fā)器通常是為了維護數據的完整性、信息的自動統(tǒng)計等功能。2.1.4數據庫文件和文件組

1.數據庫文件(DatabaseFile)

SQLServer2005的數據庫文件包含三類文件。

(1)主數據文件。

(2)次數據文件。

(3)事務日志文件。

2.文件組(FileGroup)

文件組是文件的集合。當一個數據庫由多個文件組成時，使用文件組可以合理地組合、管理文件，將數據庫文件組織成文件組。當系統(tǒng)硬件上包含了多個硬盤時，可以把特定的文件分配到不同的磁盤上，以加快數據的讀/寫速度。

SQLServer2005的文件組包括主文件組(PrimaryFileGroup)、用戶自定義文件組等兩類文件組。其中主文件組包括系統(tǒng)表的主數據文件，用戶自定義文件組包括出于分配和管理目的而分組的數據文件。當創(chuàng)建一個數據庫時，主文件組自動成為默認的文件組。未指定存儲位置的數據庫對象將存儲在默認文件組中。

2.2Transact-SQL語言

2.2.1SQL概述

SQL是結構化查詢語言(StructuredQueryLanguage)的縮寫，是1974年IBM關系型數據庫原型SystemR的原型語言，主要應用于關系數據庫，實現了關系數據庫中的數據檢索。

1986年10月，美國國家標準局(ANSI)頒布了最早的ANSI標準ANSISQL-86。SQL標準幾經修改和完善，目前使用的SQL標準是ANSISQL-99，而其名字也已根本不再有任何字母縮寫的含義。2.2.2Transact-SQL的組成

T-SQL由四個部分組成：

(1)數據定義語句(DataDefinitionLanguage，DDL)。

(2)數據操縱語句(DataManipulationLanguage，DML)。

(3)數據控制語句(DataControlLanguage，DCL)。

(4)附加語言元素(AdditionalLanguageElements，ALE)。為了全書的整體結構，將該部分內容分散在4.1節(jié)和第5章中介紹。

T-SQL語句的數目、種類較多，其主體大約由40條語句組成，各類的T-SQL語句分別如表2-4～表2-8所示。2.2.3Transact-SQL的結構

每條T-SQL語句均由一個謂詞(Verb)開始，該謂詞描述了這條語句要產生的動作。謂詞后緊接著一個或多個子句(Clause)，子句中給出了被謂詞作用的數據或提供謂詞動作的詳細信息。每一條子句由一個關鍵字開始。

2.3建?立?數?據?庫

2.3.1創(chuàng)建數據庫

1.使用SQL語句

創(chuàng)建數據庫語句的基本語法格式如下：實例2-1

在D盤example文件夾下創(chuàng)建一個student數據庫，主文件名為student_data.mdf，事務日志文件名為student_log.ldf。

(1)啟動SQL-MS，單擊工具欄上的“新建查詢”按鈕，在查詢編輯器窗口中輸入SQL語句，如圖2-5所示。圖2-5創(chuàng)建數據庫student實例2-2

在D盤example文件夾下創(chuàng)建一個customer數據庫，包含一個數據文件和一個事務日志文件。主數據文件的邏輯文件名為customer，實際文件名為customer.mdf，初始容量為10MB，最大容量為50?MB，自動增長時的遞增量為

2MB。事務日志文件的邏輯文件名為customer_log,實際文件名為customer_log.ldf，初始容量為5MB，最大容量為30MB,自動增長時的遞增量為1MB。

在查詢編輯器窗口中輸入SQL語句并執(zhí)行，如圖2-6所示。圖2-6創(chuàng)建數據庫customer實例2-3

在D盤example文件夾下創(chuàng)建一個archive數據庫，包含三個數據文件和兩個事務日志文件。主數據文件的邏輯文件名為arch1，實際文件名為archdatl.mdf，兩個次數據文件的邏輯文件名分別為arch2和arch3，實際文件名分別為archdat2.ndf和archdat3.ndf。兩個事務日志文件的邏輯文件名分別為archlog1和archlog2，實際文件名分別為archklog1.ldf和archklog2.ldf。上述文件的初始容量均為5?MB，最大容量均為50MB，遞增量均為1MB。

在查詢編輯器窗口中輸入SQL語句并執(zhí)行，如圖2-7所示。圖2-7創(chuàng)建數據庫archive

2.使用SQL-MS

(1)啟動SQL-MS，指向左側對象資源管理器窗口的“數據庫”節(jié)點，單擊右鍵，打開快捷菜單，選擇“新建數據庫”命令，打開“新建數據庫”對話框的“常規(guī)”選項卡，如圖2-8所示。圖2-8“新建數據庫”對話框的“常規(guī)”選項卡

(2)在“數據庫名稱”框中輸入數據庫名，此處為“student”，在“所有者”框中可以輸入或選擇數據庫所有者，如sa。

(3)“數據庫文件”框中包含了所有構成該數據庫的數據文件的邏輯名稱、文件類型、文件組、初始大小、自動增長、存儲位置和文件名。

自動增長：單擊自動增長后的按鈕，打開“更改student_data的自動增長設置”對話框，可以設置自動增長的類型，如圖2-9所示。圖2-9“更改student_data的自動增長設置”對話框

(4)單擊“選項”選項卡，可以指定數據庫選項的詳細信息，如圖2-10所示。此處使用默認選項。圖2-10“新建數據庫”對話框的“選項”選項卡

(5)單擊“文件組”選項卡，可以添加或刪除文件組，如圖2-11所示。此處使用默認選項。圖2-11“新建數據庫”對話框的“文件組”選項卡

實例2-4

將實例2-2中的數據庫customer的主數據文件customer的大小由50MB調整為60?MB。

在查詢編輯器窗口中輸入如圖2-12所示的SQL語句并執(zhí)行。圖2-12修改數據庫customer1實例2-5

將實例2-2中的數據庫customer增加一個次數據文件customer_1.ndf。

在查詢編輯器窗口中輸入如圖2-13所示的SQL語句并執(zhí)行。圖2-13修改數據庫customer2實例2-6

首先創(chuàng)建一個名為test的數據庫，其主數據文件的邏輯文件名和實際文件名分別為testdat1和tdat1.mdf。然后向該數據庫中添加一個次數據文件，其邏輯文件名和實際文件名分別為testdat2和tdat2.ndf。兩個數據庫文件的初始容量均為5MB，最大容量均為10MB，遞增量均為20%。

在查詢編輯器窗口中輸入如圖2-14所示的SQL語句并執(zhí)行。圖2-14創(chuàng)建并修改數據庫test

2.使用SQL-MS

(1)啟動SQL-MS，展開左側對象資源管理器窗口中的“數據庫”文件夾，指向修改的數據庫節(jié)點，單擊右鍵，打開快捷菜單，選擇“屬性”命令，打開“數據庫屬性”對話框，如圖2-15所示。

(2)單擊“文件”選項卡，可以對構成該數據庫的數據文件、事務日志文件進行修改。其他選項卡的使用與此類似。

(3)單擊“確定“按鈕，完成對指定數據庫的修改。圖2-15“數據庫屬性”對話框2.3.3刪除數據庫

數據庫一旦被刪除，它的所有信息，包括文件和數據均從磁盤上被物理刪除。在SQLServer2005中，可以使用SQL語句、SQL-MS等方式刪除數據庫。

1.使用SQL語句

刪除數據庫語句的基本語法格式如下：

DROPDATABASE<數據庫名>[，…n]實例2-7

刪除數據庫test。

在查詢編輯器窗口中輸入如圖2-16所示的SQL語句并執(zhí)行。圖2-16刪除數據庫test

2.使用SQL-MS

(1)啟動SQL-MS，指向對象資源管理器左側窗口要刪除的數據庫節(jié)點，單擊右鍵，打開快捷菜單，選擇“刪除”命令，打開“刪除對象”對話框，如圖2-17所示。

(2)單擊“確定”按鈕，指定數據庫將被刪除。圖2-17“刪除對象”對話框2.3.4重新命名數據庫

重新命名數據庫可以通過執(zhí)行系統(tǒng)存儲過程sp_renamedb來實現。其基本語法格式如下：

sp_renamedb‘<舊數據庫名>’?，‘<新數據庫名>‘

實例2-8

將數據庫archive名修改為arch。

在查詢編輯器窗口中輸入如圖2-18所示的SQL語句并執(zhí)行。圖2-18重新命名數據庫archive

2.4發(fā)布和訂閱數據庫

2.4.1使用向導配置發(fā)布和分發(fā)

在復制數據庫以前，首先需要對與復制相關的分發(fā)服務器、分發(fā)數據庫和訂閱服務器進行配置。

(1)啟動SQL-MS，單擊選中對象資源管理器左側窗口中指定的數據庫服務器，如圖2-19所示。

(2)指向右側窗口中的“復制”圖標，單擊右鍵，打開快捷菜單，選擇“配置發(fā)布”命令，打開“配置分發(fā)向導”對話框，如圖2-20所示。圖2-19選擇“配置發(fā)布、訂閱服務器和分發(fā)”命令圖2-20“配置分發(fā)向導”對話框

(3)單擊“下一步”按鈕，打開“分發(fā)服務器”對話框，如圖2-21所示。圖2-21“分發(fā)服務器”對話框

(4)如果要指定本地服務器作為分發(fā)服務器，則可以選擇“‘JSJX/SJK’將充當自己的分發(fā)服務器；SQLServer將創(chuàng)建分發(fā)數據庫和日志”選項。如果要指定遠程服務器作為分發(fā)服務器，則可以選擇“使用以下服務器作為分發(fā)服務器(注意：您選擇的服務器必須已配置為分發(fā)服務器)”選項，然后從可用分發(fā)服務器列表中單擊服務器名，使該遠程服務器成為分發(fā)服務器。在這種情況下，應確保遠程服務器已配置為分發(fā)服務器，并且本地服務器已在該分發(fā)服務器上啟用為發(fā)布服務器。如果要用做分發(fā)服務器的服務器沒有列出，則可以單擊“添加”按鈕，在SQL-MS中注冊該服務器并將它添加到可用分發(fā)服務器列表中。單擊“下一步”按鈕，打開“快照文件夾”對話框，如圖2-22所示。圖2-22“快照文件夾”對話框

(5)在“快照文件夾”文本框中輸入一個路徑，用做正在其上創(chuàng)建發(fā)布的發(fā)布服務器的快照文件夾，也可以單擊瀏覽按鈕來指定要存儲快照文件的位置。如果在分發(fā)服務器上使用的文件夾位置要求登錄有管理特權(如文件夾位于C：上)，則在其他計算機上運行的代理程序(如用于請求訂閱的代理程序)可能不能訪問共享。在執(zhí)行復制之前，測試復制代理程序能否通過在代理程序要使用的安全帳戶下登錄的方式連接到快照文件夾，然后嘗試訪問快照文件夾。單擊“下一步”按鈕，打開“分發(fā)數據庫”對話框，如圖2-23所示。圖2-23“分發(fā)數據庫”對話框

(6)在“分發(fā)數據庫名稱”欄中輸入分發(fā)數據庫名稱，默認名稱為“distribution”。如果指定名稱，則名稱最多可包含128個字符，在MicrosoftSQLServer實例中必須是唯一的，并且必須符合標識符的規(guī)則。

在“分發(fā)數據庫文件的文件夾”與“分發(fā)數據庫日志文件的文件夾”中輸入路徑或單擊“瀏覽”以指定分發(fā)數據庫文件和日志文件的位置。這些路徑必須指向分發(fā)服務器的本地磁盤，并以本地驅動器號和冒號(如C：)開頭。映射的驅動器號和網絡路徑無效。單擊“下一步”按鈕，打開“發(fā)布服務器”對話框，如圖2-24所示。圖2-24“發(fā)布服務器”對話框

(7)“發(fā)布服務器”框用于選擇允許使用此分發(fā)服務器的服務器。單擊發(fā)布服務器旁邊的屬性按鈕(...)可以查看和設置其他屬性。

如果允許的服務器沒有列出，可以單擊“添加”按鈕，向可用發(fā)布服務器列表中添加一個MicrosoftSQLServer發(fā)布服務器或Oracle發(fā)布服務器。單擊“下一步”按鈕，打開“向導操作”對話框，如圖2-25所示。

(8)單擊“下一步”按鈕，打開“完成該向導”對話框，如圖2-26所示。圖2-25“向導操作”對話框圖2-26“完成該向導”對話框

(9)單擊“完成”按鈕，完成發(fā)布和分發(fā)的配置過程，如圖2-27所示。圖2-27“正在配置”對話框2.4.2創(chuàng)建發(fā)布

一個發(fā)布可以包含源數據庫中的一個或多個表，其內容可以是整個表或部分行、列的子集。在創(chuàng)建發(fā)布向導的幫助下，可以很輕松地完成創(chuàng)建發(fā)布內容的任務。

(1)啟動SQL-MS，單擊展開左側對象資源管理器窗口中指定的數據庫服務器“復制”文件夾，如圖2-28所示。圖2-28選擇“新建發(fā)布”命令

(2)指向右側窗口中的“本地發(fā)布”圖標，單擊右鍵，打開快捷菜單，選擇“新建發(fā)布”命令，打開“新建發(fā)布向導”對話框，如圖2-29所示。圖2-29“新建發(fā)布向導”對話框

(3)單擊“下一步”按鈕，打開“發(fā)布數據庫”對話框，如圖2-30所示。圖2-30“發(fā)布數據庫”對話框

(4)選擇包含要發(fā)布數據或對象的數據庫，單擊“下一步”按鈕，打開“發(fā)布類型”對話框，如圖2-31所示。圖2-31“發(fā)布類型”對話框

(5)選擇下列發(fā)布類型之一：

①快照發(fā)布：發(fā)布服務器定期使用已更新的快照替換訂閱服務器的數據。該選項適用于訂閱服務器的數據不需要始終保持為最新的情形。

②事務性發(fā)布：通常在發(fā)布服務器上更新數據并將更改內容以增量的方式發(fā)送到訂閱服務器，同時對訂閱服務器的更新將保持事務的一致性和原子性。

③合并發(fā)布：可以在發(fā)布服務器或任何訂閱服務器上更新數據。更改內容會定期在發(fā)布服務器上進行合并。它支持移動的、偶爾連接的訂閱服務器。

(6)選擇發(fā)布類型，單擊“下一步”按鈕，打開“項目”對話框，如圖2-32所示。圖2-32“項目”對話框

(7)指定要發(fā)布的項目，既可以是表，也可以是其他數據庫對象(如存儲過程、視圖和用戶自定義函數等)。單擊“下一步”按鈕，打開“篩選表行”對話框，如圖2-33所示。圖2-33“篩選表行”對話框

(8)“篩選的表”窗格中所填充的篩選器與添加到發(fā)布表項目中的篩選器一樣。帶行篩選器的表在窗格中顯示為頂級節(jié)點。

單擊“添加”按鈕，將啟動一個用于對表項目進行篩選的對話框。對于快照發(fā)布或事務性發(fā)布，單擊“添加”按鈕，將會立即啟動一個對話框。對于合并發(fā)布，單擊“添加”按鈕，將會顯示“添加篩選器”、“添加聯接以擴展所選篩選器”和“自動生成篩選器”三個選項。單擊“下一步”按鈕，打開“快照代理”對話框，如圖2-34所示。圖2-34“快照代理”對話框

(9)在“快照代理”框中可以創(chuàng)建包含發(fā)布架構和數據(用于初始化新訂閱)的文件。在默認情況下，在新建發(fā)布向導中創(chuàng)建發(fā)布之后，快照代理將立即運行。單擊“下一步”按鈕，打開“代理安全性”對話框，如圖2-35所示。

(10)使用“代理安全性”頁，可以指定運行以下代理的帳戶，并連接到復制拓撲中的計算機。單擊“安全設置”按鈕，打開“快照代理安全性”對話框，如圖2-36所示。

(11)指定進程帳戶和連接到發(fā)布服務器，單擊“確定”按鈕，單擊“下一步”按鈕，打開“向導操作”對話框，如圖2-37所示。圖2-35“代理安全性”對話框圖2-36“快照代理安全性”對話框圖2-37“向導操作”對話框

(12)單擊“下一步”按鈕，打開“完成該向導”對話框，如圖2-38所示。圖2-38“完成該向導”對話框

(13)在“發(fā)布名稱”文本框中輸入發(fā)布名稱，單擊“完成”按鈕，打開“正在創(chuàng)建發(fā)布”對話框，如圖2-39所示。圖2-39打開“正在創(chuàng)建發(fā)布”對話框

(14)單擊“關閉”按鈕，完成該發(fā)布的創(chuàng)建過程。如果在SQL-MS展開“本地發(fā)布”文件夾，則可以看到剛剛創(chuàng)建的這個發(fā)布，如圖2-40所示。圖2-40在SQL-MS中查看新建發(fā)布2.4.3訂閱發(fā)布

(1)啟動SQL-MS，單擊展開左側對象資源管理器窗口中指定的數據庫服務器“復制”文件夾，如圖2-41所示。

(2)指向右側窗口中的“本地訂閱”圖標，單擊右鍵，打開快捷菜單，選擇“新建訂閱”命令，打開“新建訂閱向導”對話框，如圖2-42所示。

(3)單擊“下一步”按鈕，打開“發(fā)布”對話框，如圖2-43所示。圖2-41選擇“新建訂閱”命令圖2-42“新建訂閱向導”對話框圖2-43“發(fā)布”對話框

(4)選擇發(fā)布服務器，并選擇數據庫和要訂閱的發(fā)布，單擊“下一步”按鈕，打開“分發(fā)代理位置”對話框，如圖2-44所示。圖2-44“分發(fā)代理位置”對話框

(5)單擊“下一步”按鈕，打開“訂閱服務器”對話框，如圖2-45所示。圖2-45“訂閱服務器”對話框

(6)指定訂閱服務器和訂閱數據