第9章 數(shù)據(jù)庫操作

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管理平臺(tái)管理數(shù)據(jù)庫管理平臺(tái)管理數(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個(gè)部分組成，這個(gè)部分組成，這4個(gè)部分個(gè)部分被稱為被稱

2、為4個(gè)服務(wù)，它們分別是數(shù)據(jù)庫引擎、個(gè)服務(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ù)的存儲(chǔ)系統(tǒng)的核心服務(wù)，負(fù)責(zé)完成數(shù)據(jù)的存儲(chǔ)、處理和安全管理。、處理和安全管理。（2）Analysis Services 的主要作用是提供聯(lián)機(jī)分析處理（Online Analytical Processing，OLAP）和數(shù)據(jù)挖掘功能。（3）Reporti

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

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

5、df。輔助數(shù)據(jù)庫文件用于存儲(chǔ)主文件中未包含的剩余數(shù)據(jù)和數(shù)據(jù)庫對(duì)象，輔助數(shù)據(jù)文件不是必選的，即一個(gè)數(shù)據(jù)庫有一個(gè)或多個(gè)輔助數(shù)據(jù)文件，也可以沒有輔助數(shù)據(jù)文件。其擴(kuò)展名為.ndf。事物日志文件用于存儲(chǔ)恢復(fù)數(shù)據(jù)庫所需的事物日志信息，是用來記錄數(shù)據(jù)庫更新情況的文件。事物日志文件也是必選的，即一個(gè)數(shù)據(jù)庫可以有一個(gè)或多個(gè)事物日志文件。其擴(kuò)展名為.ldf。9.2.1 SQL Server數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu)nSQL Server允許將多個(gè)文件歸納為同一組，允許將多個(gè)文件歸納為同一組，并賦予此組一個(gè)名稱，這就是文件組。并賦予此組一個(gè)名稱，這就是文件組。 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 實(shí)例上創(chuàng)建的所有數(shù)據(jù)庫的模板。 （3）Msdb數(shù)據(jù)庫是代理服務(wù)數(shù)據(jù)庫，為其報(bào)警、任務(wù)調(diào)度和記錄操作員的操作提供存儲(chǔ)空間。（4）Tempdb是一個(gè)臨時(shí)數(shù)據(jù)庫，

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

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

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

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

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

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

13、中顯示細(xì)節(jié)文件，單擊“確定”按鈕，附加數(shù)據(jù)庫完成。9.3 使用使用SQL Server管理平臺(tái)管理數(shù)據(jù)庫管理平臺(tái)管理數(shù)據(jù)庫5. 刪除數(shù)據(jù)庫 在要?jiǎng)h除的數(shù)據(jù)庫上單擊鼠標(biāo)右鍵，從彈出的快捷菜單中選擇“刪除”命令，在刪除對(duì)象對(duì)話框中，選中需要?jiǎng)h除的數(shù)據(jù)庫，并選擇“關(guān)閉現(xiàn)有連接”選項(xiàng)，單擊“確定”按鈕，則數(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：指出用來存儲(chǔ)數(shù)據(jù)庫中數(shù)據(jù)部分的磁盤文件（數(shù)據(jù)文件）。n PRIMARY：指定主文件組中的主文

15、件。n LOG ON：指定用來存儲(chǔ)數(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)建一個(gè)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)存儲(chǔ)過程sp_helpdb查看指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息。該存儲(chǔ)過程的基本格式為： 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：添加一個(gè)文件

20、組。nREMOVE FILEGROUP filegroup_name：刪除某一文件組。nMODIFY FILE ：修改某個(gè)文件的屬性。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】將兩個(gè)數(shù)據(jù)文件和一個(gè)事務(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