1、數(shù)據(jù)庫系統(tǒng)原理數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用技術(shù)與應(yīng)用技術(shù)主講：陳漫紅主講：陳漫紅 北京聯(lián)合大學(xué)師范學(xué)院北京聯(lián)合大學(xué)師范學(xué)院 電氣信息系電氣信息系第第9章章 數(shù)據(jù)庫操作數(shù)據(jù)庫操作n9.1 SQL Server2005體系結(jié)構(gòu)體系結(jié)構(gòu) n9.2 SQL Server2005數(shù)據(jù)庫數(shù)據(jù)庫 n9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫 n9.4 使用使用T-SQL管理數(shù)據(jù)庫管理數(shù)據(jù)庫 n9.5 本章小結(jié)本章小結(jié)9.1 SQL Server2005 體系結(jié)構(gòu)體系結(jié)構(gòu) Microsoft SQL Server 2005系統(tǒng)由系統(tǒng)由4個部分組成，這個部分組成，這4個部分個部分被稱為被稱

2、為4個服務(wù)，它們分別是數(shù)據(jù)庫引擎、個服務(wù)，它們分別是數(shù)據(jù)庫引擎、Analysis Services、Reporting Services和和Integration Services。 9.1 SQL Server2005 體系結(jié)構(gòu)體系結(jié)構(gòu)（1）數(shù)據(jù)庫引擎是）數(shù)據(jù)庫引擎是Microsoft SQL Server 2005系統(tǒng)的核心服務(wù)，負(fù)責(zé)完成數(shù)據(jù)的存儲系統(tǒng)的核心服務(wù)，負(fù)責(zé)完成數(shù)據(jù)的存儲、處理和安全管理。、處理和安全管理。（2）Analysis Services 的主要作用是提供聯(lián)機(jī)分析處理（Online Analytical Processing，OLAP）和數(shù)據(jù)挖掘功能。（3）Reporti

3、ng Services為用戶提供了支持web方式的企業(yè)級報表功能。（4）Integration Services是一個數(shù)據(jù)集成平臺，負(fù)責(zé)完成有關(guān)數(shù)據(jù)的提取、轉(zhuǎn)換和加載等操作。 9.2 SQL Server2005數(shù)據(jù)庫數(shù)據(jù)庫 9.2.1 SQL Server數(shù)據(jù)庫的存儲結(jié)構(gòu) 9.2.2 系統(tǒng)數(shù)據(jù)庫9.2.3 數(shù)據(jù)庫對象 9.2.1 SQL Server數(shù)據(jù)庫的存儲結(jié)構(gòu) 數(shù)據(jù)庫的存儲結(jié)構(gòu)分為邏輯存儲結(jié)構(gòu)和物理存儲結(jié)構(gòu)兩種 :1、數(shù)據(jù)庫的邏輯存儲結(jié)構(gòu)指的是數(shù)據(jù)庫是由哪些性質(zhì)的信息所組成，SQL Server的數(shù)據(jù)庫不僅僅只是數(shù)據(jù)的存儲，所有與數(shù)據(jù)處理操作相關(guān)的信息都存儲在數(shù)據(jù)庫中。實際上，SQL

4、Server的數(shù)據(jù)庫是由諸如表、視圖、索引等各種不同的數(shù)據(jù)庫對象所組成，它們分別用來存儲特定信息并支持特定功能，構(gòu)成數(shù)據(jù)庫的邏輯存儲結(jié)構(gòu)。 2、數(shù)據(jù)庫的物理存儲結(jié)構(gòu)則是討論數(shù)據(jù)庫文件是如何在磁盤上存儲的。9.2.1 SQL Server數(shù)據(jù)庫的存儲結(jié)構(gòu)數(shù)據(jù)庫在磁盤上是以文件為單位存儲的，由數(shù)據(jù)庫文件和事務(wù)日志文件組成，一個數(shù)據(jù)庫至少應(yīng)該包含一個數(shù)據(jù)庫文件和一個事務(wù)日志文件數(shù)據(jù)庫的物理存儲結(jié)構(gòu)數(shù)據(jù)庫的物理存儲結(jié)構(gòu)數(shù)據(jù)庫文件數(shù)據(jù)庫文件說明說明主數(shù)據(jù)庫文件該文件是數(shù)據(jù)庫的關(guān)鍵文件，包含了數(shù)據(jù)庫的啟動信息，并且存儲部分或者是全部數(shù)據(jù)。主文件是必選的，即一個數(shù)據(jù)庫有且只有一個主數(shù)據(jù)庫文件。其擴(kuò)展名為.m

5、df。輔助數(shù)據(jù)庫文件用于存儲主文件中未包含的剩余數(shù)據(jù)和數(shù)據(jù)庫對象，輔助數(shù)據(jù)文件不是必選的，即一個數(shù)據(jù)庫有一個或多個輔助數(shù)據(jù)文件，也可以沒有輔助數(shù)據(jù)文件。其擴(kuò)展名為.ndf。事物日志文件用于存儲恢復(fù)數(shù)據(jù)庫所需的事物日志信息，是用來記錄數(shù)據(jù)庫更新情況的文件。事物日志文件也是必選的，即一個數(shù)據(jù)庫可以有一個或多個事物日志文件。其擴(kuò)展名為.ldf。9.2.1 SQL Server數(shù)據(jù)庫的存儲結(jié)構(gòu)nSQL Server允許將多個文件歸納為同一組，允許將多個文件歸納為同一組，并賦予此組一個名稱，這就是文件組。并賦予此組一個名稱，這就是文件組。 n與數(shù)據(jù)庫文件一樣，文件組也分為主文件組（與數(shù)據(jù)庫文件一樣，文件

6、組也分為主文件組（Primary File Group）和次文件組（）和次文件組（Secondary File Group）。）。 9.2.2 系統(tǒng)數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫 （1）Master數(shù)據(jù)庫是SQL Server系統(tǒng)最重要的數(shù)據(jù)庫，它記錄了SQL Server系統(tǒng)的所有系統(tǒng)信息。包括：n所有的登錄信息n系統(tǒng)配置信息nSQL Server的初始化信息n其它系統(tǒng)數(shù)據(jù)庫及用戶數(shù)據(jù)庫的相關(guān)信息。（2）model 數(shù)據(jù)庫用作在 SQL Server 實例上創(chuàng)建的所有數(shù)據(jù)庫的模板。 （3）Msdb數(shù)據(jù)庫是代理服務(wù)數(shù)據(jù)庫，為其報警、任務(wù)調(diào)度和記錄操作員的操作提供存儲空間。（4）Tempdb是一個臨時數(shù)據(jù)庫，

7、它為所有的臨時表、臨時存儲過程及其他臨時操作提供存儲空間。 9.2.3 數(shù)據(jù)庫對象 數(shù)據(jù)庫對象就是存儲、管理和使用數(shù)據(jù)的不同結(jié)構(gòu)形式。在SQL Server2005中，主要的數(shù)據(jù)庫對象包括數(shù)據(jù)庫關(guān)系圖、表、視圖、存儲過程、函數(shù)、觸發(fā)器、程序集、類型、規(guī)則和默認(rèn)值等。1.表表 是包含數(shù)據(jù)庫中所有數(shù)據(jù)的數(shù)據(jù)庫對象，它由行和列組成，用于組織和存儲數(shù)據(jù)，每一行稱為一個記錄。2.字段字段 表中每列稱為一個字段，字段具有自己的屬性。3.索引索引 是一個單獨的數(shù)據(jù)結(jié)構(gòu)，它是依賴于表建立的，不能脫離關(guān)聯(lián)表而單獨存在。4.視圖視圖 是從一個或多個表中導(dǎo)出的表（也稱虛表），是用戶查看數(shù)據(jù)表中數(shù)據(jù)的一種方式。5.可

8、編程性可編程性 是一個邏輯集合，它包括存儲過程、函數(shù)、觸發(fā)器、程序集、類型、規(guī)則和默認(rèn)值等對象。6.存儲過程存儲過程 是一組為了完成特定功能的SQL語句集合（包含查詢、插入、刪除和更新等操作），經(jīng)編譯后以名稱的形式存儲在SQL Server服務(wù)器端的數(shù)據(jù)庫中，由用戶通過指定存儲過程的名稱來執(zhí)行。當(dāng)這個存儲過程被調(diào)用執(zhí)行時，其包含的操作也會同時執(zhí)行。7.觸發(fā)器觸發(fā)器 是一種特殊的存儲過程，它能夠在某個規(guī)定的事件發(fā)生時觸發(fā)執(zhí)行。 在某種程度上可以說，設(shè)計數(shù)據(jù)庫的過程實際上就是設(shè)計和實現(xiàn)數(shù)據(jù)庫對象的過程。 n數(shù)據(jù)庫結(jié)構(gòu)如下圖所示：數(shù)據(jù)庫物理上表現(xiàn)為數(shù)據(jù)庫文件邏輯上表現(xiàn)為數(shù)據(jù)庫對象數(shù)據(jù)文件日志文件主數(shù)

9、據(jù)文件次數(shù)據(jù)文件1次數(shù)據(jù)文件2主文件組次文件組次數(shù)據(jù)文件1次數(shù)據(jù)文件2日志文件1日志文件29.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫 1.利用SQL Server2005管理平臺創(chuàng)建數(shù)據(jù)庫具體步驟如下：（1）選擇“開始 | 所有程序 | Microsoft SQL Server 2005 | SQL Server Management Studio”啟動SQL Server管理控制器，出現(xiàn)“連接到服務(wù)器”對話框 9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫（2）在“連接到服務(wù)器”對話框中選擇“服務(wù)器類型”為“數(shù)據(jù)庫引擎”，“服務(wù)器名稱”

10、為“chmh”,“身份驗證”為“SQL Server身份驗證”，并輸入正確的登錄名（sa）和密碼，單擊“連接”按鈕，即連接到指定的服務(wù)器（3）在左邊的“對象資源管理器”窗口中選中“數(shù)據(jù)庫”節(jié)點，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“新建數(shù)據(jù)庫”命令。（4）進(jìn)入“新建數(shù)據(jù)庫”窗口。 （5）設(shè)置完成后單擊“確定”按鈕，數(shù)據(jù)庫school即創(chuàng)建完成。 9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫2.利用利用SQL Server管理平臺修改數(shù)據(jù)庫管理平臺修改數(shù)據(jù)庫在SQL Server管理平臺中，右擊所要修改的數(shù)據(jù)庫，從彈出的快捷菜單中選擇“屬性”選項，出現(xiàn)數(shù)據(jù)庫屬性設(shè)置對

11、話框。 9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫3.重新命名數(shù)據(jù)庫n（1）啟動SQL Server管理控制器，在“對象資源管理器”中展開服務(wù)器節(jié)點，展開數(shù)據(jù)庫節(jié)點。n（2）選中要重命名的數(shù)據(jù)庫，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“重命名”命令，n（3）此時數(shù)據(jù)庫名稱變?yōu)榭删庉嫚顟B(tài)，直接將其修改。9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫4. 分離數(shù)據(jù)庫和附加數(shù)據(jù)庫分離數(shù)據(jù)庫和附加數(shù)據(jù)庫 若希望將一個數(shù)據(jù)庫從一臺服務(wù)器移到另臺服務(wù)器上，可通過對數(shù)據(jù)庫進(jìn)行分離和附加操作來完成該操作。（1）分離數(shù)據(jù)庫首先確保沒有任何用戶登錄到數(shù)據(jù)庫中，

12、展開“數(shù)據(jù)庫”節(jié)點，用鼠標(biāo)右鍵單擊所要分離的數(shù)據(jù)庫，從彈出的快捷菜單中選擇“任務(wù)|分離”命令。這時將打開“分離數(shù)據(jù)庫”對話框單擊“消息”選項，將打開“活動監(jiān)視器”對話框。進(jìn)程終止后，關(guān)閉“活動監(jiān)視器”對話框，返回“分離數(shù)據(jù)庫”對話框 9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫（2）附加數(shù)據(jù)庫 用鼠標(biāo)右鍵單擊“數(shù)據(jù)庫”節(jié)點，從彈出的快捷菜單中選擇“附加”命令，此時打開“附加數(shù)據(jù)庫”對話框 想要添加數(shù)據(jù)庫，單擊“添加”按鈕。此時打開“定位數(shù)據(jù)庫文件”對話框 這時可象使用Windows資源管理器一樣找到要附加數(shù)據(jù)庫的MDF文件 這時將返回到“附加數(shù)據(jù)庫”對話框中，并在其

13、中顯示細(xì)節(jié)文件，單擊“確定”按鈕，附加數(shù)據(jù)庫完成。9.3 使用使用SQL Server管理平臺管理數(shù)據(jù)庫管理平臺管理數(shù)據(jù)庫5. 刪除數(shù)據(jù)庫 在要刪除的數(shù)據(jù)庫上單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“刪除”命令，在刪除對象對話框中，選中需要刪除的數(shù)據(jù)庫，并選擇“關(guān)閉現(xiàn)有連接”選項，單擊“確定”按鈕，則數(shù)據(jù)庫將被永久刪除。9.4 使用T-SQL管理數(shù)據(jù)庫n9.4.1創(chuàng)建數(shù)據(jù)庫 n9.4.2 管理數(shù)據(jù)庫 9.4.1 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫 n創(chuàng)建數(shù)據(jù)庫的語句是CREATE DATABASE ，其語法格式為：nCREATE DATABASE database_namen ON ,.n n , ,.n n

14、LOG ON ,.n n := PRIMARY n( NAME = logical_file_name , nFILENAME = os_file_namen , SIZE = size n , MAXSIZE = max_size | UNLIMITED n , FILEGROWTH = growth_increment ) ,.n n :=FILEGROUP filegroup_name ,.n 9.4.1 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫參數(shù)說明：ndatabase_name：新創(chuàng)建的數(shù)據(jù)庫的名稱。n ON：指出用來存儲數(shù)據(jù)庫中數(shù)據(jù)部分的磁盤文件（數(shù)據(jù)文件）。n PRIMARY：指定主文件組中的主文

15、件。n LOG ON：指定用來存儲數(shù)據(jù)庫日志的磁盤文件。n NAME = logical_file_name：指定數(shù)據(jù)文件或日志文件的邏輯名。n FILENAME = os_file_name：指定數(shù)據(jù)文件或日志文件的操作系統(tǒng)文件名，包括文件名和路徑。n SIZE = size：指定數(shù)據(jù)文件或日志文件的初始大小，默認(rèn)單位為MB。nMAXSIZE = max_size | UNLIMITED ：指定數(shù)據(jù)文件或日志文件可以增長到的最大容量，默認(rèn)單位為MB。 n FILEGROWTH = growth_increment：指定數(shù)據(jù)文件或日志文件的增長幅度，默認(rèn)單位為MB。9.4.1 創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)

16、據(jù)庫【例9-1】使用默認(rèn)方式創(chuàng)建數(shù)據(jù)庫web1。命令如下：CREATE DATABASE web1【例9-2】創(chuàng)建一個Test數(shù)據(jù)庫，該數(shù)據(jù)庫的主數(shù)據(jù)文件邏輯名稱為Test_data，物理文件名為Test.mdf，初始大小為10MB，最大尺寸為無限大，增長速度為10%；數(shù)據(jù)庫的日志文件邏輯名稱為Test_log，物理文件名為Test.ldf，初始大小為1MB，最大尺寸為5MB，增長速度為1MB。命令如下：CREATE DATABASE test ON PRIMARY ( NAME = test, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL

17、datatest.mdf, SIZE=10240KB, MAXSIZE = UNLIMITED,FILEGROWTH = 10%) LOG ON ( NAME=test_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLdatatest_log.ldf,SIZE=1024KB,MAXSIZE = 5120KB,FILEGROWTH = 1024KB )GO9.4.2 管理數(shù)據(jù)庫管理數(shù)據(jù)庫 n1.查看數(shù)據(jù)庫查看數(shù)據(jù)庫使用系統(tǒng)存儲過程sp_helpdb查看指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息。該存儲過程的基本格式為： sp_helpdb 數(shù)據(jù)庫名需要

18、查看當(dāng)前數(shù)據(jù)庫服務(wù)器中所有數(shù)據(jù)庫的信息，則使用命令： sp_helpdb 9.4.2 管理數(shù)據(jù)庫管理數(shù)據(jù)庫2.修改數(shù)據(jù)庫修改數(shù)據(jù)庫使用ALTER DATABASE語句修改數(shù)據(jù)庫，ALTER DATABASE語法形式如下： ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY

19、 FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name 9.4.2 管理數(shù)據(jù)庫管理數(shù)據(jù)庫參數(shù)說明：nADD FILE ,.n TO FILEGROUP filegroup_name ：表示向指定的文件組添加新的數(shù)據(jù)文件。nADD LOG FILE ,.n ：添加新的事務(wù)日志文件。nREMOVE FILE logical_file_name：刪除某一文件。nADD FILEGROUP filegroup_name：添加一個文件

20、組。nREMOVE FILEGROUP filegroup_name：刪除某一文件組。nMODIFY FILE ：修改某個文件的屬性。nMODIFY NAME = new_dbname：修改數(shù)據(jù)庫的名字 nMODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name ：修改某一文件組的屬性。 9.4.2 管理數(shù)據(jù)庫管理數(shù)據(jù)庫【例9-3】將兩個數(shù)據(jù)文件和一個事務(wù)日志文件添加到test數(shù)據(jù)庫中。程序清單如下：ALTER DATABASE Test ADD FILE (NAME = Test1, FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatest1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),(NAME = Te