第3章 數據庫操作

SQLSERVER

應用與開發(fā)

計算機工程技術學院（軟件學院）

任淑美二00七年九月（三）本課主要內容掌握SQLServer2000數據庫的組成了解SQLServer2000存儲結構掌握使用T-SQL操縱數據庫熟練掌握使用“企業(yè)管理器”操縱數據庫本課核心知識點學習數據庫的建立方法學習數據表的建立方法學習修改數據庫和刪除數據庫的方法學習修改數據表和刪除數據表的方法本課重點難點SQLServer2000數據管理技術使用T-SQL操縱數據庫“企業(yè)管理器”對數據庫的操作本課教學目的掌握SQLServer2000數據庫的組成了解SQLServer2000存儲結構掌握使用“企業(yè)管理器”操縱數據庫掌握使用“查詢分析器”操縱數據庫第3章數據庫操作

3.1示例數據庫介紹以網絡論壇作為講授示例，通過論壇系統(tǒng)中的用戶管理、版塊管理、發(fā)帖管理等對數據庫相關概念和SQLServer2000數據管理技術進行解釋。在示例“論壇管理系統(tǒng)”中包含一個數據庫（bbsDB），內有四個關系表，表結構和記錄如表3-1，表3-2，表3-3，表3-4所示。

1．用戶信息表用戶信息表Users用來保存已經在論壇注冊的用戶基本信息。各字段分別為：用戶ID（主鍵）、用戶呢稱、密碼、Email、生日、性別、用戶級別、用戶備注、注冊日期、用戶狀態(tài)、用戶積分。2．版塊表論壇設有版塊表Section，每個版塊主要針對某個主題并設定版主來進行管理。各字段分別為：版塊號（主鍵）、版塊名稱、版主編號、版塊描述、點擊率、帖子數量。3．發(fā)帖表論壇設有發(fā)帖表Topic，用于保存用戶發(fā)帖。各字段分別為：發(fā)帖編號（主鍵）、版塊號、版主編號、跟帖數、發(fā)帖表情、主題、內容、發(fā)帖時間、點擊數、標志、最后回復時間。4．跟帖表論壇設有跟帖表Reply，用于保存用戶對主題帖的回復。各字段分別為：回帖編號（主鍵）、主帖編號、版塊號、用戶編號、回帖表情、題目、內容、回帖時間、點擊數。3.2SQLServer2000數據庫概述3.2.1

任務一熟悉SQLServer數據庫組成

1．概述在SQLServer2000中，數據庫由包含數據的基本表和其它對象（如視圖、索引、存儲過程和觸發(fā)器）組成。SQLServer2000能夠支持多個數據庫，每個數據庫可以存儲相關或不相關數據。在創(chuàng)建數據庫之前，我們需要理解數據庫的各組成部分的功能及其設計方法，這樣就可以確保實現數據庫的正常運行。2．文件和文件組

SQLServer2000使用一組操作系統(tǒng)文件映射數據庫。數據庫中的所有數據和對象（如表、存儲過程、觸發(fā)器和視圖）都存儲在文件中數據文件次要數據文件事務日志文件（1）主要數據庫文件主要數據庫文件包括數據庫對象，可用作存儲系統(tǒng)表和對象。該文件包含數據庫的啟動信息，并用于存儲數據。每個數據庫都有一個主要數據文件，主要數據庫文件的擴展名為.mdf。（2）次要數據庫文件次要數據庫文件可用作存儲用戶數據和對象。這些文件含有不能置于主要數據文件中的所有數據，如果主文件可以包含數據庫中的所有數據，那么數據庫就不需要次要數據文件。有些數據庫可能比較大，所以需要多個次要數據文件，或使用位于不同磁盤驅動器上的輔助文件將數據擴展到多個磁盤。并不是所有的數據庫都需要次要數據庫文件，次要數據庫文件的擴展名為.ndf。（3）事務日志這些文件包含用于恢復數據庫的日志信息。每個數據庫都必須至少有一個日志文件，事務日志文件最小為512K，事務日志文件的擴展名為.ldf。注意：一個數據庫必須包括一個主要數據庫文件和一個事務日志文件。例如，創(chuàng)建簡單的數據庫bbsDB時，可以只使用一個包含所有數據和對象的主文件和一個包含事務日志信息的日志文件。但如果數據庫bbsDB很復雜時，可以使用一個主要數據庫文件和五個次要數據庫文件，數據庫內的數據和對象擴展到所有的六個文件中，另外還可以有四個日志文件包含事務日志信息。文件組文件組允許對文件進行分組，這樣，就可以方便的管理數據的分配／放置。例如，可以分別在三個硬盤驅動器上創(chuàng)建三個文件（Data1.ndf、Data2.ndf和Data3.ndf），并將這三個文件指派到文件組fgroup1中。然后，可以在文件組fgroup1上創(chuàng)建一個表，對表中數據的查詢將分散到三個磁盤上，使性能得以很大提高。另外，如果數據庫超過單個MicrosoftWindowsNT文件的最大大小，則可以使用次要數據文件允許數據庫繼續(xù)增長。文件和文件組的設計規(guī)則：文件或文件組不能由一個以上的數據庫使用。例如，文件bbsDB_data.mdf

和bbsDB_data.ndf

包含bbsDB

數據庫中的數據和對象，任何其它數據庫都不能使用這兩個文件。文件只能是一個文件組的成員。數據和事務日志信息不能屬于同一文件或文件組。事務日志文件不能屬于任何文件組。3．日志文件在MicrosoftSQLServer2000中，數據庫必須至少包含一個數據文件和一個事務日志文件。數據和事務日志信息不能放在同一文件中，并且每個日志文件只能由一個數據庫使用。SQLServer使用每一個數據庫的事務日志來記錄事務和恢復事務。事務日志是數據庫中已發(fā)生的所有修改和執(zhí)行每次修改的操作的連續(xù)記錄。事務日志記錄每個事務的開始，記錄了在每個事務期間，對數據的更改及撤消所做更改的信息。對于一些大的操作（如CREATEINDEX），事務日志則記錄該操作發(fā)生的情況，隨著對數據庫中記錄操作的增加，日志會不斷地增長。4．系統(tǒng)數據庫（1）master數據庫master數據庫是最重要的一個系統(tǒng)數據庫，其中記錄所有其它的數據庫及相關信息，其中包括數據庫文件的位置。同時，master數據庫記錄了所有SQLServer服務器特定的配置信息，包括授權用戶、數據庫、系統(tǒng)配置設置及遠程服務器等。由于在master數據庫中保存了控制SQLServer操作的大部分關鍵數據，從數據庫系統(tǒng)安全考慮，在master數據庫上不應給用戶任何權限，同時要經常保持對master的數據庫的備份操作，以反映在數據庫中發(fā)生的變化。注意：建議不要在master數據庫中創(chuàng)建任何用戶對象（如表、視圖、存儲過程或觸發(fā)器）。master數據庫包含系統(tǒng)表，這些系統(tǒng)表存儲SQLServer所用的系統(tǒng)信息（如配置選項設置）。（2）tempdb

數據庫tempdb

數據庫保存所有的臨時表和臨時存儲過程。它還滿足任何其它的臨時存儲要求，例如存儲SQLServer生成的工作表和自動解決復雜的或者嵌套的查詢。tempdb

數據庫是全局資源，所有連接到系統(tǒng)的用戶的臨時表和存儲過程都存儲在該數據庫中。tempdb

數據庫在SQLServer每次啟動時都重新創(chuàng)建，因此該數據庫在系統(tǒng)啟動時總是空的。默認情況下，在SQLServer在運行時tempdb

數據庫會根據需要自動增長。不過，與其它數據庫不同，每次啟動數據庫引擎時，它會重置為其初始大小。注意：所有由GROUPBY和DISTINCT子句產生的臨時表和結果都存儲在tempdb數據中。執(zhí)行游標的結果也保存在tempdb數據庫中。（3）model數據庫

model數據庫為模型數據庫，是系統(tǒng)創(chuàng)建新數據庫的模板或原型。當發(fā)出CREATEDATABASE語句時，新數據庫的第一部分通過復制model數據庫中的內容創(chuàng)建，剩余部分由空頁填充。由于SQLServer每次啟動時都要創(chuàng)建tempdb

數據庫，model數據庫必須一直存在于SQLServer系統(tǒng)中。注意：每當創(chuàng)建數據庫時，model數據庫的內容就被拷貝到新的數據庫中。（4）msdb

數據庫msdb

數據庫供SQLServer代理程序調度警報、作業(yè)以及記錄操作員時使用。msdb數據庫包含任務調度、異常處理和報警處理等，msdb

還包含歷史記錄表，例如備份和還原歷史記錄表。此外，SQLServer還自帶了兩個示例數據庫Pubs和Notrhwind。數據庫Pubs是一個圖書出版公司的數據庫模型，包含了大量的樣本表和樣本數據。數據庫Northwind是一個貿易公司的數據庫模型，包含了與公司經營有關的大多數數據。3.2.2任務二了解SQLServer數據庫存儲結構

。1．數據庫存儲結構SQLServer數據庫包括數據庫文件和日志文件，在創(chuàng)建數據庫時，SQLServer會使用特定的數據結構給數據對象分配空間。SQLServer的數據庫文件由盤區(qū)組成，而盤區(qū)由頁面組成，盤區(qū)與頁面、數據庫與文件間的關系如圖3-1所示。盤區(qū)是由8個連續(xù)頁面組成的數據存儲結構，大小為64KB。創(chuàng)建數據庫對象時，SQLServer會自動以盤區(qū)為單位分配空間，并且每個盤區(qū)只能包含一個數據庫對象。同時，盤區(qū)也是表或索引分配空間的單位，每個表或索引占有一個或多個盤區(qū)的空間（64KB）。

SQLServer中所有的信息都存儲在頁面上，頁面是數據庫中使用的最小數據存儲單元。每一個頁面存儲8KB（8192字節(jié)）的信息。因為所有的頁面都包含一個132字節(jié)的頁面頭，頁面頭用來地唯一標識存儲在頁面中的數據,因此每個頁面存儲的信息不超過8060字節(jié)。由于SQLServer不允許跨頁面存儲數據信息，因此，每一數據行的最大值也為8060個字節(jié)。2．數據庫文件存儲位置在缺省情況下，SQLServer用戶創(chuàng)建的數據庫存儲在應用程序文件夾下的MSSQL\Data文件下，如master文件的存儲位置為：數據文件：c:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\master.mdf日志文件：c:\ProgramFiles\MicrosoftSQLServer\MSSQL\Data\mastlog.ldf用戶可以在創(chuàng)建數據庫時指定數據庫對應的物理文件的位置。了解SQLServer2000數據庫文件的位置對于SQLServer2000數據庫應用程序非常重要。如要在應用程序中實現“數據備份”操作，首先可以利用后面要學習到數據庫分離功能將數據庫文件和數據庫服務分離，再利用程序設計語言提供的文件復制方法完成數據庫文件從一個位置到另一個位置的復制，最后應用數據庫附加功能重新將數據庫文件附加到指定的數據庫上，從而完成應用程序中的“數據備份”操作。3.3創(chuàng)建和修改數據庫。3.3.1任務一應用企業(yè)管理器創(chuàng)建和修改數據庫

1．在“企業(yè)管理器”中創(chuàng)建數據庫要創(chuàng)建數據庫，必須先確定數據庫的名稱、所有者（創(chuàng)建數據庫的用戶）、大小，以及用于存儲該數據庫的文件和文件組。創(chuàng)建數據庫之后，構成該數據庫的所有文件都將用零填充，以重寫磁盤上以前的刪除文件所遺留的所有數據。雖然這意味著創(chuàng)建文件所需的時間更長，但在完成此操作之后，在正常的數據庫操作過程中，當數據第一次寫入到文件中時，操作系統(tǒng)將無須再用零填充這些文件。這提高了日常操作的性能。在創(chuàng)建數據庫時最好指定文件的最大允許增長的大小，這樣做可以防止文件在添加數據時無限制增大，以至用盡整個磁盤空間。要指定文件的最大大小，在使用SQLServer企業(yè)管理器內的“屬性”對話框創(chuàng)建數據庫時，使用CREATEDATABASE語句的MAXSIZE參數或者“將文件增長限制為（MB）”選項。同時，創(chuàng)建數據庫之后，建議創(chuàng)建一個master數據庫的備份。注意：

SQLServer2000數據和事務日志文件不能放在壓縮文件系統(tǒng)或遠程網絡驅動器上（如共享的網絡目錄）。在企業(yè)管理器中創(chuàng)建數據庫的步驟如下：（1）啟動SQLServer2000企業(yè)管理器，依次展開“SQLServer組”和“SQLServer注冊”（這里是ABC（WindowsNT））。右擊“數據庫”文件夾，在彈出的快捷菜單上選擇“新建數據庫”命令；（2）在打開的“數據庫屬性”對話框中的“名稱”文本框中鍵入新數據庫的名稱。（3）更改新建主數據庫文件的默認值，單擊【數據文件】選項卡。在此選項卡中，可以設置數據文件的名稱、位置和大小。也可以設置文件的增長方式。（4）若要更改新建事務日志文件的默認值，單擊【事務日志】選項卡。在此選項卡上可以設置事務日志文件的名稱、位置和大小。（5）單擊【確定】按鈕，即可創(chuàng)建“論壇數據庫”。注意：

數據庫大小的最大值由可用磁盤空間量決定，許可限制則由所使用的MicrosoftSQLServer版本決定?？梢栽趧?chuàng)建數據庫改變其存儲位置，但一旦數據庫創(chuàng)建以后，存儲位置不能被修改。通過向導功能建立數據庫，其步驟如下：（1）在企業(yè)管理器中，選擇【工具】，再選擇【向導】，即可打開“選擇向導”對話框，展開【數據庫】文件夾，然后選擇【創(chuàng)建數據庫向導】；（2）單擊【確定】按鈕，打開“創(chuàng)建數據庫向導”對話框。（3）依次單擊【下一步】按鈕，并依照提示輸入數據庫名、數據庫文件名、數據庫文件的增長信息、事務日志文件信息及其增長信息，即可創(chuàng)建一個新的數據庫。2．修改數據庫在創(chuàng)建數據庫后，可以根據數據庫管理的實際需要調整數據庫的屬性。調整的內容包括以下幾個方面：擴充或收縮分配給數據庫的數據或事務日志空間。添加或刪除數據和事務日志文件。創(chuàng)建文件組。創(chuàng)建默認文件組。更改數據庫名稱。更改數據庫的所有者。在企業(yè)管理器中設置數據庫屬性的步驟如下：（1）打開企業(yè)管理器，展開服務器組，然后展開SQLServer服務器；（2）在“數據庫”文件夾中，右擊要更改的數據庫（如：bbsDB），如圖3-8所示，然后單擊【屬性】命令，打開數據庫屬性對話框；打開“文件組”選項卡，可以進行文件組相關屬性的設置；打開“選項”選項卡，該選項卡中的各選項影響著數據庫的工作方式和性能；打開“權限”選項卡，可用于設置用戶/角色的權限。3．數據庫大小估算和收縮數據庫在設置數據庫的大小時，應盡量精確估計數據庫的大小。要估計數據庫的大小，請分別估計每個表的大小，然后累加所得的值。表的大小取決于表是否有索引，如果有，還取決于是哪種類型的索引。下面是一個用來估算每個表所需頁面數的公式：頁數=表的行數/（8080/行的長度）其中行的長度是指表的每一筆記錄所占的字節(jié)數。為了避免造成數據庫中數據的丟失，在更改數據庫屬性時，要更改數據文件或者日志文件的大小，SQLServer2000只允許增加文件的大小，而不允許減小文件的大小。但是，SQLServer2000允許收縮數據庫中的每個文件以回收未使用的頁。數據和事務日志文件都可以收縮，數據庫文件可以作為組或單獨地進行手工收縮。數據庫也可設置為按給定的時間間隔自動收縮，該活動在后臺進行，并且不影響數據庫內的用戶活動。收縮數據庫的操作步驟如下：（1）打開企業(yè)管理器，展開服務器組，然后展開SQLServer服務器。（2）在“數據庫”文件夾中，右擊要更改的數據庫，然后單擊【所有任務】，再單擊【收縮數據庫】命令，如圖3-10所示。打開“收縮數據庫”對話框，如圖3-11所示。（3）如果要收縮個別的數據庫文件，單擊“收縮文件”欄中的【文件】按鈕，打開“ShrinkFile”對話框，（4）設置好各種選項，單擊【確定】按鈕，即可對選擇的文件進行收縮，完成后彈出一個對話框提示完成。3.3.2任務二應用T-SQL創(chuàng)建數據庫

創(chuàng)建數據庫的基本語句格式如下：CREATEDATABASE<數據庫文件名>[ON

<數據文件>]([NAME=<邏輯文件名>,]

FILENAME='<物理文件名>'

[,SIZE=<大小>]

[,MAXSIZE=<可增長的最大大小>]

[,FILEGROWTH=<增長比例>])[LOGON<日志文件>]([NAME=<邏輯文件名>,]

FILENAME='<物理文件名>'

[,SIZE=<大小>]

[,MAXSIZE=<可增長的最大大小>]

[,FILEGROWTH=<增長比例>])1．創(chuàng)建數據庫到默認位置【例3-1】創(chuàng)建論壇數據庫。CREATEDATABASEbbsDB本例創(chuàng)建名為“bbsDB”的數據庫，并指定單個文件。指定的文件成為主文件，并會自動創(chuàng)建一個1MB的事務日志文件。因為主文件的SIZE參數中沒有指定MB或KB，所以主文件將以兆字節(jié)為單位進行分配。因為沒有為事務日志文件指定<filespec>，所以事務日志文件沒有MAXSIZE，可以增長到填滿所有可用的磁盤空間為止。創(chuàng)建數據庫的過程分兩步完成：（1）SQLServer使用Model數據庫的副本初始化數據庫及其元數據。（2）SQLServer使用空頁填充數據庫的剩余部分，除了包含記錄數據庫中空間使用情況以外的內部數據頁。2．創(chuàng)建數據庫到指定位置【例3-2】創(chuàng)建數據庫到指定文件夾。CREATEDATABASEbbsDBON(NAME=bbsDB,FILENAME='e:\project\bbsDB.mdf')本例在e:\project文件夾中創(chuàng)建名為“bbsDB”的論壇數據庫。創(chuàng)建過程如圖3-13所示。第一個bbsDB為數據庫名，name=bbsDB

中的bbsDB為數據文件名（也可以取不同的文件名）。事務日志文件（bbsDB_log）會自動創(chuàng)建。數據文件與日志文件都存放在用戶指定的文件夾中（如例中的e:\project，文件夾應預先建立）。因為沒有指定MAXSIZE，文件可以增長到填滿所有可用的磁盤空間為止。3．指定文件屬性【例3-3】創(chuàng)建帶文件屬性的數據庫bbsDB。CREATEDATABASEbbsDBON(NAME=bbsDB_dat,FILENAME='e:\project\bbsDB_dat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='bbsDB_log',FILENAME='e:\project\bbsDB_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)本例創(chuàng)建名為bbsB的數據庫。創(chuàng)建過程如圖3-14所示。因為沒有使用關鍵字PRIMARY，第一個文件(bbsDB_dat)成為主文件。“bbsDB_dat”文件的SIZE參數沒有指定MB或KB，因此默認為MB，以兆字節(jié)為單位進行分配?！癰bsDB_log”日志文件以兆字節(jié)為單位進行分配，因為SIZE參數中顯式聲明了MB后綴。3.3.3任務三應用T-SQL修改數據庫

使用ALTERDATABASE命令可以在數據庫中添加或刪除文件和文件組。也可以更改文件和文件組的屬性，例如更改文件的名稱和大小。ALTERDATABASE提供了更改數據庫名稱、文件組名稱以及數據文件和日志文件的邏輯名稱的能力，但不能改變數據庫的存儲位置。修改數據庫的基本語句格式如下：ALTERDATABASE<數據庫名稱>{ADDFILE<數據文件>|ADDLOGFILE<日志文件>|REMOVEFILE<邏輯文件名>|ADDFILEGROUP<文件組名>|REMOVEFILEGROUP<文件組名>|MODIFYFILE<文件名>|MODIFYNAME=<新數據庫名稱>|MODIFYFILEGROUP<文件組名>|SET<選項>}1．添加文件和文件組【例3-4】在數據庫“bbsDB”中增加文件“bbsDB_dat2”。本例創(chuàng)建數據庫，并更改該數據庫以添加一個5MB大小的新數據文件。打開數據庫屬性中“數據文件”選項可以看到如圖3-15的結果。ALTERDATABASEbbsDB

ADDFILE(NAME=bbsDB_dat2,FILENAME='e:\project\bbsDB_dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)2．修改文件【例3-5】更改數據庫bbsDB中的“bbsDB_dat2”文件的分配空間。ALTERDATABASEbbsDBMODIFYFILE(NAME=bbsDB_dat2,SIZE=20MB)本例將bbsDB_dat2”文件增加大小到20MB（注意：文件分配空間只能增加，不能減少）。修改過程如圖3-16所示。3．刪除文件【例3-6】刪除數據庫bbsDB中的“bbsDB_dat2”文件。ALTERDATABASEbbsDBREMOVEFILEbbsDB_dat2本例將添加到數據庫bbsDB中的“bbsDB_dat2”文件刪除，刪除過程如圖3-17所示。4．修改數據庫選項使用系統(tǒng)存儲過程sp_dboption可以顯示或更改數據庫選項。不能在master或tempdb

數據庫上使用sp_dboption。存儲過程sp_dboption的基本語句格式如下：sp_dboption[數據庫名稱]

[,[要設置的選項的名稱]]

[,[新設置]]【例3-7】將數據庫bbsDB

設置為只讀。EXECsp_dboption'bbsDB','readonly','TRUE'注意：系統(tǒng)存儲過程是指存儲在數據庫內，可由應用程序（或查詢分析器）調用執(zhí)行的一組語句的集合，其目的是用來執(zhí)行數據庫的管理和信息活動。存儲過程詳細內容可參閱“存儲過程”章節(jié)和“SQLServer聯機叢書”，執(zhí)行存儲過程中的EXEC關鍵字可選。5．修改數據庫名稱通過使用系統(tǒng)存儲過程sp_renamedb可以更改數據庫名稱。存儲過程sp_renamedb基本語句格式：sp_renamedb[當前數據庫名稱],[數據庫新名稱]【例3-8】將數據庫bbsDB改名為“bbsDB_bak”。EXECsp_renamedb'bbsDB','bbsDB_bak'3.4查看和刪除數據庫3.4.1任務一應用企業(yè)管理器查看和刪除數據庫

1．查看數據庫數據庫建立以后，如果需要查看所建數據庫的相關信息，可以通過以下步驟實現：（1）打開企業(yè)管理器，展開服務器組，然后展開SQLServer服務器。（2）在“數據庫”文件夾中，右擊要更改的數據庫（如：bbsDB），然后單擊“屬性”命令，打開數據庫屬性對話框，可以查看數據庫的相關屬性。2．刪除數據庫當不再需要數據庫或它被移到另一數據庫服務器時，即可刪除該數據庫。數據庫刪除之后，文件及其數據都從服務器上的磁盤中刪除。一旦刪除數據庫，它即被永久刪除，并且不能進行檢索，除非使用以前的備份。系統(tǒng)數據庫msdb、master、model和tempdb不能進行刪除與分離操作。注意：在數據庫刪除之后備份master數據庫，因為刪除數據庫將更新master中的系統(tǒng)表。如果master需要還原，則從上次備份master之后刪除的所有數據庫都將仍然在系統(tǒng)表中有引用，因而可能導致出現錯誤信息。使用“企業(yè)管理器”刪除數據庫的步驟：（1）打開企業(yè)管理器，展開服務器組，然后展開SQLServer服務器。（2）展開“數據庫”文件夾，右擊要刪除的數據庫，然后單擊“刪除”命令。（3）確認刪除。3.4.2任務二應用T-SQL查看和刪除數據庫

數據對任何企事業(yè)單位來說是極為重要的資源，保證數據庫的安全以防止數據遭到有意或無意的泄露、盜取、篡改與破壞是數據庫管理員的責任。SQLServer2000提供了有效的數據安全性機制。1．應用“查詢分析器”查看數據庫使用系統(tǒng)存儲過程sp_helpdb查看指定數據庫或所有數據庫的信息。存儲過程sp_helpdb基本語句格式如下：sp_helpdb[數據庫名稱]【例3-9】查看所有數據庫的信息。使用該命令可以查看所有數據庫的信息，如圖3-19所示?！纠?/p>