第3章數據庫操作本章學習導航在進行數據管理時，相關的信息要存放到數據庫中。數據庫就像是一個容器，其中可以容納表、視圖、索引、存儲過程和觸發器等數據庫對象。應用SQLServer2008進行數據管理之前，首先必須創建好數據庫，并指定數據庫的數據文件名和日志文件名以及數據庫的存放位置等屬性。本章學習導航本章任務描述任務編號子任務任務內容任務1了解SQLServer系統數據庫的基本情況、用戶數據庫中的各種對象的信息、數據庫在操作系統文件夾中的存放任務1-1在SSMS中，查看本書樣例數據庫WebShop的組成任務1-2在SSMS中，查看SQLServer2008安裝成功后系統數據庫的情況任務1-3查看SQLServer2008安裝成功后系統數據庫master數據庫的邏輯名稱與對應的物理文件的存儲情況任務2在SSMS中，創建用戶數據庫WebShop來對商城數據進行管理；在數據庫創建后，根據需要進行數據庫信息的修改、查看和刪除操作任務2-1在SSMS中，創建電子商城數據庫WebShop任務2-2在SSMS中，完成數據庫WebShop的修改任務2-3在SSMS中，查看數據庫WebShop的相關信息任務2-4在SSMS中，刪除數據庫WebShop任務2-5在SSMS中，由已創建好的數據庫WebShop生成創建數據庫的腳本本章任務描述任務編號子任務任務內容任務3使用T-SQL語句創建保存電子商城的會員和商品等信息的用戶數據庫WebShop以便對商城數據進行管理、使用T-SQL語句進行數據庫信息的修改、查看和刪除操作任務3-1使用T-SQL語句創建WebShop數據庫任務3-2使用T-SQL語句對已創建好WebShop數據庫進行指定的修改任務3-3使用T-SQL語句更改數據庫選項任務3-4使用T-SQL語句更改數據庫名稱任務3-5使用T-SQL語句查看指定的數據庫或所有的數據庫信息任務3-6使用T-SQL語句刪除指定的數據庫任務3-7使用T-SQL語句將tempdb移動到新位置任務1

SQLServer2008安裝成功后，用戶需要了解系統數據庫的基本情況；用戶也可以了解用戶數據庫中的各種對象的信息；還需要了解數據庫在操作系統文件夾中是怎樣存放的。SQLSever2008數據庫一、數據庫概述【任務1-1】

啟動SQLServerManagementStudio，查看本書樣例數據庫WebShop的組成。

主題說明數據庫說明如何使用數據庫表示、管理和訪問數據聯合數據庫服務器說明實現聯合數據庫層的設計指南和注意事項表說明如何使用表存儲數據行和定義多個表之間的關系索引說明如何使用索引提高訪問表中數據的速度已分區表和已分區索引說明如何分區可使大型表和索引更易于管理以及更具可縮放性視圖說明各種視圖及其用途（提供其他方法查看一個或多個表中的數據）存儲過程說明這些Transact-SQL程序如何將業務規則、任務和進程集中在服務器中DML觸發器說明作為特殊類型存儲過程的DML觸發器的功能，DML觸發器僅在修改表中的數據后執行DDL觸發器說明作為特殊觸發器的DDL觸發器的功能，DDL觸發器在響應數據定義語言(DDL)語句時激發一、數據庫概述主題說明登錄觸發器登錄觸發器將為響應LOGON事件而激發存儲過程。與SQLServer實例建立用戶會話時將引發此事件。事件通知說明作為特殊數據庫對象的事件通知，事件通知可以向ServiceBroker發送有關服務器和數據庫事件的信息用戶定義函數說明如何使用函數將任務和進程集中在服務器中程序集說明如何在SQLServer中使用程序集部署以Microsoft.NETFramework公共語言運行時(CLR)中駐留的一種托管代碼語言編寫的（不是以Transact-SQL編寫的）函數、存儲過程、觸發器、用戶定義聚合以及用戶定義類型同義詞說明如何使用同義詞引用基對象；同義詞是包含架構的對象的另一個名稱一、數據庫概述【提示】

一個SQLServer實例可以支持多個數據庫。每個數據庫可以存儲來自其他數據庫的相關數據或不相關數據。例如，SQLServer實例可以有一個數據庫用于存儲網站商品數據，另一個數據庫用于存儲內部員工的數據。

不能在master數據庫中創建任何用戶對象（例如表、視圖、存儲過程或觸發器）。master數據庫包含SQLServer實例使用的系統級信息（例如登錄信息和配置選項設置）。

表上有幾種類型的控制（例如約束、觸發器、默認值和自定義用戶數據類型），用于保證數據的有效性。可以向表中添加聲明性引用完整性(DRI)約束，以確保不同表中的相關數據保持一致。

表上可以有索引（與書中的索引相似），利用索引能夠快速找到行。數據庫還可以包含使用Transact-SQL或.NETFramework編程代碼的過程對數據庫中的數據執行操作。這些操作包括創建用于提供對表數據的自定義訪問的視圖，或創建用于對部分行執行復雜計算的用戶定義函數。二、系統數據庫【任務1-2】啟動SQLServerManagementStudio，查看SQLServer2008安裝成功后系統數據庫的情況。

三、文件和文件組【任務1-3】查看SQLServer2008安裝成功后系統數據庫master數據庫的邏輯名稱與對應的物理文件的存儲情況。三、文件和文件組(一)數據文件

文件說明主要數據文件主要數據文件包含數據庫的啟動信息，并指向數據庫中的其他文件；用戶數據和對象可存儲在此文件中，也可以存儲在次要數據文件中；每個數據庫有一個主要數據文件，主要數據文件的擴展名默認為.mdf次要數據文件次要數據文件是可選的，由用戶定義并存儲用戶數據；通過將每個文件放在不同的磁盤驅動器上，次要文件可用于將數據分散到多個磁盤上；另外，如果數據庫超過了單個Windows文件的最大值，可以使用次要數據文件，這樣數據庫就能繼續增長；次要數據文件的文件擴展名默認為.ndf事務日志文件事務日志文件保存用于恢復數據庫的日志信息；每個數據庫必須至少有一個日志文件，事務日志文件擴展名默認為.ldf三、文件和文件組(二)邏輯和物理文件名稱

【提示】SQLServer數據和日志文件可以保存在FAT或NTFS文件系統中。從安全性角度建議使用NTFS。可讀/寫數據文件組和日志文件不能保存在NTFS壓縮文件系統中。只有只讀數據庫和只讀次要文件組可以保存在NTFS壓縮文件系統中。默認情況下，數據和事務日志被放在同一個驅動器上的同一個路徑下。這是為處理單磁盤系統而采用的方法。但是，在實際應用環境中，建議將數據和日志文件放在不同的磁盤上。三、文件和文件組(三)文件組

每個數據庫有一個主要文件組。此文件組包含主要數據文件和未放入其他文件組的所有次要文件?？梢詣摻ㄓ脩舳x的文件組，用于將數據文件集合起來，以便于管理、數據分配和放置。SQLServer2008將數據庫映射為一組操作系統文件。數據和日志信息從不混合在相同的文件中，而且各文件僅在一個數據庫中使用。文件組是命名的文件集合，用于幫助數據布局和管理任務，例如備份操作和還原操作。三、文件和文件組(四)數據文件頁

SQLServer2008數據文件中的頁按順序編號，文件的首頁以0開始。數據庫中的每個文件都有一個唯一的文件ID號。若要唯一標識數據庫中的頁，需要同時使用文件ID和頁碼。如下圖所示。

數據文件頁結構數據文件頁三、文件和文件組(五)區

區是八個物理上連續的頁的集合，用來有效地管理頁。所有頁都存儲在區中。區是管理空間的基本單位。一個區是八個物理上連續的頁（即64KB）。這意味著SQLServer數據庫中每MB有16個區。為了使空間分配更有效，SQLServer不會將所有區分配給包含少量數據的表。SQLServer有兩種類型的區：（1）統一區，由單個對象所有。區中的所有8頁只能由所屬對象使用。（2）混合區，最多可由八個對象共享。區中八頁的每頁可由不同的對象所有。三、文件和文件組混合區和統一區【課堂實踐1】操作要求：啟動“SQLServerManagementStudio”，查看有哪幾個系統數據庫。啟動“SQLServerManagementStudio”，查看temp數據庫的邏輯名稱。在操作系統文件夾中查看temp數據庫對應的操作系統文件名。任務2在SQLServer2008的SQLServerManagementStudio中，為了保存電子商城的會員和商品等信息，需要創建用戶數據庫WebShop來對商城數據進行管理；在數據庫創建后，需要進行數據庫信息的修改、查看和刪除操作。使用SSMS管理數據庫【任務2-1】在SQLServer2008的“SQLServerManagementStudio”中，創建電子商城數據庫WebShop。

一、創建數據庫

【提示】

創建數據庫時，必須確定數據庫的名稱、所有者、大小以及存儲該數據庫的文件和文件組。數據庫名稱必須遵循SQLServer標識符規則?？梢栽趧摻〝祿旄淖兤浯鎯ξ恢?，但一旦數據庫創建以后，存儲位置不能被修改。數據庫和事務日志文件的初始大小與為model數據庫指定的默認大小相同，主文件中包含數據庫的系統表。創建數據庫之后，構成該數據庫的所有文件都將用零填充，以重寫磁盤上以前的刪除文件所遺留的現有數據。一、創建數據庫

在創建數據庫時最好指定文件的最大允許增長的大小，這樣做可以防止文件在添加數據時無限制增大，以至用盡整個磁盤空間。創建數據庫之后，建議創建一個master數據庫的備份。對于一個SQLServer實例，最多可以創建32,767個數據庫。model數據庫中的所有用戶定義對象都將復制到所有新創建的數據庫中?？梢韵騧odel數據庫中添加任何對象（例如表、視圖、存儲過程和數據類型），以將這些對象包含到所有新創建的數據庫中。如果需要在數據庫節點中顯示新創建的數據庫，則需要在數據庫節點上單擊右鍵，再選擇【刷新】。一、創建數據庫

【任務2-2】在SQLServer2008的“SQLServerManagementStudio”中，完成數據庫WebShop的修改。

二、修改數據庫

修改的內容包括以下幾個方面：

1、擴充或收縮分配給數據庫的數據或事務日志空間。2、添加或刪除數據和事務日志文件。3、創建文件組。4、創建默認文件組。5、更改數據庫名稱。6、更改數據庫的所有者。二、修改數據庫

【任務2-3】在SQLServer2008的“SQLServerManagementStudio”中，查看數據庫WebShop的相關信息。

三、查看數據庫

【任務2-4】在SQLServer2008的“SQLServerManagementStudio”中，刪除數據庫WebShop。

四、刪除數據庫

【提示】當不再需要數據庫，或將數據庫移到另一數據庫或服務器時，即可刪除該數據庫。數據庫刪除之后，文件及其數據都從服務器上的磁盤中刪除。一旦刪除數據庫，它即被永久刪除，并且不能進行檢索，除非使用以前的備份。在數據庫刪除之前備份master數據庫，因為刪除數據庫將更新master中的系統表。如果master需要還原，則從上次備份master之后刪除的所有數據庫都將仍然在系統表中有引用，因而可能導致出現錯誤信息。必須將當前數據庫指定為其他數據庫，不能刪除當前打開的數據庫。四、刪除數據庫

【任務2-5】在SQLServer2008的“SQLServerManagementStudio”中，收縮數據庫WebShop。五、收縮數據庫五、收縮數據庫【任務2-6】在SQLServer2008的“SQLServerManagementStudio”中，由已創建好的數據庫WebShop生成創建數據庫的腳本。

六、由已有數據庫生成創建數據庫的腳本

【提示】生成的腳本中包含了許多設置信息。其它對象（表和視圖等）生成腳本的方法與此相同，不再詳述。六、由已有數據庫生成創建數據庫的腳本

【課堂實踐2】操作要求：（1）啟動“SQLServerManagementStudio”，創建數據庫WebShop，并要求進行如下設置：數據庫文件和日志文件的邏輯名稱分別為：WebShop_data和WebShop_log；物理文件存放在E:\data文件夾中；數據文件的增長方式為“按MB”自動增長，初始大小為5MB，文件增長量為2MB；日志文件的增長方式為“按百分比”自動增長，初始大小為2MB，文件增長量為15%；（2）在操作系統文件夾中查看WebShop數據庫對應的操作系統文件。（3）對WebShop數據庫進行以下修改：添中一個日志文件WebShop_log1；將主數據庫文件的增長上限修改為500MB；將主日志文件的增長上限修改為300MB。（4）刪除所創建的數據庫文件WebShop?！菊n堂實踐2】任務3使用T-SQL語句創建保存電子商城的會員和商品等信息的用戶數據庫WebShop以便對商城數據進行管理；在數據庫創建后，使用T-SQL語句進行數據庫信息的修改、查看和刪除操作。使用T-SQL管理數據庫一、創建數據庫(一)CREATEDATABASE語句格式CREATEDATABASE<數據庫文件名>[ON

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

FILENAME='<物理文件名>'

[,SIZE=<大小>]

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

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

FILENAME='<物理文件名>'

[,SIZE=<大小>]

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

[,FILEGROWTH=<增長比例>])一、創建數據庫(二)SQLServerManagementStudio中使用T-SQL語句一、創建數據庫【提示】

如果在查詢語句編輯區域選定了語句，則對指定語句執行檢查和執行操作，否則執行所有語句。在以后章節中的T-SQL的編寫和執行的步驟與此相同。用戶編寫的T-SQL腳本可以以文件（.sql）形式保存，在需要時執行。一、創建數據庫(三)使用CREATEDATABASE語句創建數據庫【任務3-1】使用T-SQL語句創建WebShop數據庫。

【完成語句1】使用默認方式創建數據庫。CREATEDATABASEWebShop【提示】

該方案以默認方式創建名為WebShop的數據庫。創建數據庫的過程分兩步完成：SQLServer使用model數據庫的副本初始化數據庫及其元數據。SQLServer使用空頁填充數據庫的剩余部分，除了包含記錄數據庫中空間使用情況以外的內部數據頁?！就瓿烧Z句2】指定數據庫對應的物理文件的存儲位置。CREATEDATABASEWebShopON(NAME=WebShop_dat,FILENAME=‘d:\data\WebShop.mdf’）創建名為WebShop的數據庫。同時指定WebShop_dat為主文件，大小等于model數據庫中主文件的大小。事務日志文件會自動創建，其大小為主文件大小的25%或512KB中的較大值。因為沒有指定MAXSIZE，文件可以增長到填滿所有可用的磁盤空間為止。一、創建數據庫【完成語句3】創建數據庫時指定數據庫文件和日志文件的屬性。CREATEDATABASEWebShopON(NAME=WebShop_dat,FILENAME='d:\data\WebShop_dat.mdf',SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOGON(NAME='WebShop_log',

FILENAME='d:\data\WebShop_log.ldf',SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)一、創建數據庫【提示】沒有使用關鍵字PRIMARY，則第一個文件(WebShop_dat)成為主文件。因為WebShop_dat文件的SIZE參數沒有指定MB或KB，因此默認為MB，以兆字節為單位進行分配。WebShop_log文件以兆字節為單位進行分配，因為SIZE參數中顯式聲明了MB后綴。一、創建數據庫（一）ALTERDATABASE語句格式基本語句格式：ALTERDATABASE<數據庫名稱>{ADDFILE<數據文件>|ADDLOGFILE<日志文件>|REMOVEFILE<邏輯文件名>|ADDFILEGROUP<文件組名>|REMOVEFILEGROUP<文件組名>|MODIFYFILE<文件名>|MODIFYNAME=<新數據庫名稱>|MODIFYFILEGROUP<文件組名>|SET<選項>}二、修改數據庫【任務3-2】使用T-SQL語句對已創建好WebShop數據庫進行指定的修改。

添加次要數據庫文件。ALTERDATABASEWebShopADDFILE(NAME=WebShop_dat2,FILENAME='d:\Data\WebShop_dat2.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)（二）使用ALTERDATABASE修改數據庫格式二、修改數據庫更改指定文件。ALTERDATABASEWebShopMODIFYFILE(NAME=WebShop_dat2,SIZE=20MB)刪除指定文件。ALTERDATABASEWebShopREMOVEFILEWebShop_dat2二、修改數據庫【任務3-3】使用T-SQL語句更改數據庫選項。

【提示】

系統存儲過程是指存儲在數據庫內，可由應用程序（或查詢分析器）調用執行的一組語句的集合，其目的是用來執行數據庫的管理和信息活動。存儲過程詳細內容可參閱“存儲過程”章節和“SQLServer聯機叢書”。執行存儲過程中的EXEC關鍵字可選。本書中系統存儲過程的存儲過程的執行與前面所述的T-SQL語句的執行相同。不能在master或tempdb數據庫上使用sp_dboption。（三）使用存儲過程修改數據庫二、修改數據庫【任務3-4】使用T-SQL語句更改數據庫名稱。

基本語句格式：

sp_renamedb[當前數據庫名稱],[數據庫新名稱]二、修改數據庫【任務3-5】使用T-SQL語句查看指定的數據庫或所有的數據庫信息。

存儲過程sp_helpdb基本語句格式如下：

sp_helpdb[數據庫名稱]

【語句1】查看當前數據庫服務器中所有數據庫的信息。

sp_helpdb【語句2】查看當前數據庫服務器中

WebShop數據庫的信息。

sp_helpdbWebShop三、查看數據庫三、查看數據庫三、查看數據庫名稱含義name數據庫名稱db_size數據庫大小owner數據庫所有者（例如sa）dbid數據庫IDcreated數據庫創建的日期status以逗號分隔的值的列表，這些值是當前在數據庫上設置的數據庫選項的值name邏輯文件名fileid文件標識符filename操作系統文件名（物理文件名稱）filegroup文件所屬的組；為便于分配和管理，可以將數據庫文件分成文件組；日志文件不能作為文件組的一部分size文件大小maxsize文件可達到的最大值，此字段中的UNLIMITED值表示文件可以一直增大直到磁盤滿為止growth文件的增量，表示每次需要新的空間時給文件增加的空間大小usage文件用法；數據文件的用法是dataonly（僅數據），而日志文件的用法是logonly（僅日志）【語句3】查看所有數據庫的基本信息。

SELECT*FROMsys.databases三、查看數據庫【語句4】查看數據文件的信息。

SELECT*FROMsys.database_files三、查看數據庫【任務3-6】使用T-SQL語句刪除指定的數據庫。

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

DROPDATABASE<數據庫名稱>【完成語句】考慮到不再需要數據庫WebShop，現在要刪除數據庫WebShop。

DROPDATABASEWebShop四、刪除數據庫【任務3-7】使用DBCCSHRINKDATABASE收縮數據庫。

五、收縮數據庫和數據庫文件使用DBCCSHRINKDATABASE收縮數據庫： DBCCSHRINKDATABASE（數據庫名|數據庫ID|0[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])[WITHNO_INFOMSGS]<數據庫名稱>收縮WebShop數據庫，剩余可用空間10%，代碼如下：DBCCSHRINKDATABASE(WebShop,10)【任務3-8】使用DBCCSHRINKFILE收縮數據文件。

五、收縮數據庫和數據庫文件使用DBCCSHRINKFILE收縮數據文件： DBCCSHRINKFILE ( {文件名|文件ID} {[,EMPTYFILE] |[[,收縮后文件的大小][,{NOTRUNCATE| TRUNCATEONLY}]] } )

[WITHNO_INFOMSGS]【任務3-9】使用T-SQL語句將tempdb移動到新位置。

（1）確定tempdb數據庫的邏輯文件名稱以及在磁盤上的當前位置。

SELECTname,physical_name FROMsys.master_files WHEREdatabase_id=DB_ID('tempdb'); GO六、移動數據庫文件（2）使用ALTERDATABASE更改每個文件的位置。 USEmaster; GO ALTERDATABASEtempdb MODIFYFILE(NAME=tempdev,FILENAME= 'E:\SQLData\tempdb.mdf'); GO ALTERDATABASEtempdb MODIFYFILE(NAME=templog,FILENAME= 'E:\SQLData\templog.ldf')