數(shù)據(jù)庫實驗報告3

1、數(shù)據(jù)庫原理及應(yīng)用實驗報告題目： 實驗二、三、五、七 專業(yè)： 軟件工程 班級： 軟件131 姓名： 陳萬全 學(xué)號：132852 指導(dǎo)教師： 張滿囤 完成日期： 2015.12.22 第2章 ：數(shù)據(jù)庫的完整性控制實驗2.1 實體完整性一、實驗?zāi)康膶W(xué)習(xí)實體完整性的建立，以及實踐違反實體完整性的結(jié)果。二、實驗題目1.在School數(shù)據(jù)庫中建立一張新表Class，包括Class_id（varchar（4），name（varchar（10），Department（varchar（20）三個列，并約束Class_id為主鍵create table Class(Class_id varchar(4),name

2、 varchar(10),Department varchar(20)constraint pk_Class primary key(Class_id)2、 創(chuàng)建事務(wù)T3，在事務(wù)中插入一個元組（00001,01CSC,CS）,并在T3中嵌套創(chuàng)建事務(wù)T4，T4也插入和T3一樣的元組，編寫代碼測試，查看結(jié)果。use schoolset xact_abort onbegin transaction T3insert into Classvalues('0001','01CSC','CS')set xact_abort offbegin transact

3、ion oninsert into Classvalues('0001','01CSC','CS')commit transaction T4commit transaction T3結(jié)果：(1 行受影響)消息2627，級別14，狀態(tài)1，第8 行違反了PRIMARY KEY 約束'pk_Class'。不能在對象'dbo.Class' 中插入重復(fù)鍵。開始時，T3中的insert執(zhí)行成功，而當(dāng)T4中的insert執(zhí)行不成功后滾回T3，導(dǎo)致T3出錯，后滾回所以整體上執(zhí)行不成功。分析：當(dāng)SET XACT_ABORT為ON時

4、，如果Transact-SQL產(chǎn)生運行錯誤，整個事物將終止并會滾。為OFF時，只回滾產(chǎn)生錯誤的Transact-SQL語句，而事物將繼續(xù)進(jìn)行處理。對于大多數(shù)OLEDB提供程序，隱性或顯式事物中的數(shù)據(jù)修改語句必須將XACT_ABORT設(shè)置為ON。唯一不需要該選項的情況是提供程序支持嵌套事務(wù)時。實驗2.2 參照完整性一、實驗?zāi)康?學(xué)習(xí)建立外鍵，以及利用FOREIGNKEY···REFERENCES子句以及各種約束保證參照完整性。二、實驗題目3、創(chuàng)建一個班里的學(xué)生互助表，規(guī)定：包括學(xué)生編號、學(xué)生姓名、學(xué)生的幫助對象，每個學(xué)生有且僅有一個幫助對象，幫助對象也必須是班里的學(xué)

5、生。create table HELP(sid char(10) primary key,cname varchar(12),helpid char(10)not null,)alter table HELP add constraint FK_help foreign key(helpid)references HELP(sid)4、 學(xué)校學(xué)生會的每個部門都有一個部長，每個部長領(lǐng)導(dǎo)多個部員，每個部只有一個部員有監(jiān)察評測部長的權(quán)力。請給出體現(xiàn)這兩種關(guān)系（即領(lǐng)導(dǎo)與評測）的兩張互參照的表的定義。create table leader(sid char(9),sname varchar(20),my

6、leader char(9),constraint PK_leader primary key(sid)create table monitor(sid char(9),sname varchar(20),mymonitor char(9)constraint PK_monitor primary key(sid)constraint PF_monitor foreign key(mymonitor)references leader(sid)alter table leaderadd constraint FK_leader foreign key(myleader) references

7、monitor(sid)實驗2.3 用戶自定義完整性一、實驗?zāi)康?#160; 學(xué)習(xí)用戶自定義約束，并實踐用戶自定義完整性，利用SQL查詢分析器用短語NOT NULL、UNIQUE、CHECK保證用戶定義完整性。二、實驗題目1、加入約束U3，令sage的值>=0。use schoolalter table Workeradd constraint U3 check(sage>=0)2、 加入規(guī)則R2、確保插入的記錄的sage的值在1到100之間，并綁定到sage的屬性上。use schoolgo create rule R3 as vale>1 and vale<

8、;100go exec sp_bindrule R3,'Worker.sage'第3章 ：數(shù)據(jù)庫的安全性控制實驗3.1 用戶標(biāo)識與鑒別1、 實驗?zāi)康谋緦嶒灥哪康氖峭ㄟ^實驗加深對數(shù)據(jù)安全性的理解，并掌握SQL Sever中有關(guān)用戶登錄認(rèn)證以及管理的方法二、實驗題目(1)在school數(shù)據(jù)庫中創(chuàng)建賬號“王二”，密碼是123，并向他授予數(shù)據(jù)庫訪問權(quán)1.因為不知道sa 的密碼，所以修改密碼：首先用windows身份登陸，在對象資源管理器中，選擇安全性-登陸名-sa-屬性-修改sa密碼為sa,然后用sa 登陸（即用sa用戶登陸進(jìn)入查詢分析器）2.或者以系統(tǒng)管理員身份登陸進(jìn)入查詢分析器（即

9、選擇windows身份驗證,服務(wù)器名稱輸入”.”）exec sp_addlogin '王二','123456','school'go use schoolgoexec sp_grantdbaccess '王二'(2) 撤銷“王二”這個賬號use schoolexec sp_revokedbaccess'王二'exec sp_droplogin'王二'實驗3.2 自主存取控制一、實驗?zāi)康耐ㄟ^實驗加深對數(shù)據(jù)庫存取控制機(jī)制的理解，通過自主存取控制進(jìn)行權(quán)限管理，熟悉SQL SERVER中的角色管理。二、實驗

10、結(jié)果1、以SA登錄查詢分析器，輸入下列代碼并執(zhí)行exec sp_addlogin '李勇','123456','school'use schoolexec sp_grantdbaccess '李勇','happyratd'grant select,insert,update on students to public;grant all on students to happyratd;revoke select on students from happyratd;deny update on students

11、to happyratd;ALL 權(quán)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對實體定義了ALL 權(quán)限。2、回答下列問題：第1行代碼新建了一個名為李勇的登陸賬戶，123456是什么？“李勇”這個登陸賬戶將映射為數(shù)據(jù)庫用戶名happyrat，為什么？將是哪個數(shù)據(jù)庫的用戶。分別解釋第4-7行代碼的作用。若以賬戶李勇登陸服務(wù)器，能否對school數(shù)據(jù)庫的表students進(jìn)行select 和update 操作，為什么？答： “123456”是新增用戶的登陸密碼。“李勇”登陸賬號將映射為數(shù)據(jù)庫用戶名happyrat，這是由第3行代碼的存儲過程決定的，將是school數(shù)據(jù)庫的用戶。第4行：把

12、students表上的select,insert,update三種權(quán)限授予全體用戶第5行：把students表的所有權(quán)限授予happyrat用戶第6行：取消happyrat用戶在students上的select權(quán)限第7行：拒絕happyrat用戶在students上的update操作。若以賬戶“李勇”登錄數(shù)據(jù)庫，則可以對school數(shù)據(jù)庫的表進(jìn)行select操作，但是update操作不行，因為update操作被第7行deny拒絕了，而happyrat用戶的select權(quán)限雖然被revoke取消了，但是它作為public用戶的select 權(quán)限仍在，所以可以進(jìn)行select操作。如果要使該用戶不

13、能進(jìn)行select操作，則可以用deny或?qū)ublic的select權(quán)限進(jìn)行revoke操作。實驗3.3 視圖機(jī)制在自主存取控制上的應(yīng)用一、實驗?zāi)康耐ㄟ^實驗加深對數(shù)據(jù)庫安全性的理解，熟悉視圖機(jī)制在自主存取控制上的應(yīng)用。二、實驗題目1、在數(shù)據(jù)庫School上創(chuàng)建用戶“王二”，具體操作參見實驗3.1的實驗步驟中（2），在students表上創(chuàng)建視圖grade2000，把年級為2000的學(xué)生元組放入視圖。use schoolgocreate view grade2000 asselect * from STUDENTS where grade='2000'2、授予用戶王二在試圖gr

14、ade2000的select權(quán)限use schoolgogrant select on grade2000 to 王二第五章：數(shù)據(jù)庫的備份與還原實驗5.1 SQL Server 數(shù)據(jù)庫的備份一、實驗?zāi)康?理解數(shù)據(jù)庫的故障類型，并學(xué)會制定合適的備份計劃，掌握多種備份數(shù)據(jù)庫的方法。二、實驗題目1、作為數(shù)據(jù)庫管理員，如何為School數(shù)據(jù)庫制定一個完整的數(shù)據(jù)庫備份計劃？并討論一下具體的方案作為數(shù)據(jù)庫管理員，在制定備份計劃時需要作全面考慮，并在實踐中調(diào)整方案，如可以為School數(shù)據(jù)庫在每個月開始時做一次完全備份，然后每個星期做一次差異備份，每天做一個日志備份。1）完整性備份打開SQL Server

15、Management Studio，右鍵點擊School數(shù)據(jù)庫，選擇任務(wù)->備份，打開如下頁面：備份類型選擇完整，在目標(biāo)項選擇備份文件存儲路徑，點擊確定，提示備份成功：2.差異備份操作同完整性備份，只不過在備份類型選項選擇差異，如下圖：點擊確定，提示備份成功。3.事務(wù)日志備份由于簡單恢復(fù)模式不允許備份事務(wù)日志文件，所以必須先更改恢復(fù)模式，右鍵School數(shù)據(jù)庫，選擇屬性->選項，將恢復(fù)模式更改為完整，如下圖所示：點擊確定即可。接下來按前兩次備份操作，同樣在備份選項選擇事務(wù)日志，點擊確定。實驗5.2 SQL Server 數(shù)據(jù)庫的還原一、實驗?zāi)康?數(shù)據(jù)庫恢復(fù)是數(shù)據(jù)庫系統(tǒng)發(fā)生故障后，數(shù)

16、據(jù)庫管理員或用戶利用先前建立的數(shù)據(jù)庫的備份，將數(shù)據(jù)庫從故障中恢復(fù)過來的過程。二、實驗題目（1）作為數(shù)據(jù)庫管理員，針對在實驗5.1的實驗步驟中提出的School數(shù)據(jù)庫方案，假設(shè)數(shù)據(jù)庫在星期三發(fā)生了故障，應(yīng)該采取怎樣的恢復(fù)措施？利用本月初建立的數(shù)據(jù)庫完全備份恢復(fù)數(shù)據(jù)庫，然后再利用上一個星期的差異數(shù)據(jù)庫的備份，將數(shù)據(jù)庫恢復(fù)到上一星期的狀態(tài)。然后再利用周一和周二的日志備份，將數(shù)據(jù)庫恢復(fù)到星期二的狀態(tài)。刪除School數(shù)據(jù)庫所有視圖，如下所示：進(jìn)行數(shù)據(jù)庫還原，右鍵School數(shù)據(jù)庫，選擇任務(wù)->還原->數(shù)據(jù)庫，進(jìn)入如下窗口：點擊確定，即完成數(shù)據(jù)庫的還原：還原后的數(shù)據(jù)庫如下所示：實驗5.3 系

17、統(tǒng)數(shù)據(jù)庫的備份與恢復(fù)一、實驗?zāi)康?讀者通過對系統(tǒng)數(shù)據(jù)庫的備份與還原，深入了解系統(tǒng)數(shù)據(jù)庫Master，Model，Msdb的作用，并制定系統(tǒng)數(shù)據(jù)庫的備份與恢復(fù)策略，以保證數(shù)據(jù)庫系統(tǒng)的可靠性與正確性。二、實驗題目右鍵點擊School數(shù)據(jù)庫，選擇任務(wù)->導(dǎo)出數(shù)據(jù)，進(jìn)入如下窗體：點擊數(shù)據(jù)源的下拉按鈕，選擇SQL Server Native Client 11.0,點擊下一步，進(jìn)入如下窗體：目標(biāo)選擇平面文件目標(biāo)，選擇所要導(dǎo)出的目標(biāo)文件D:SourceCourse.xlsx，點擊下一步,選擇復(fù)制一個或多個表或視圖的數(shù)據(jù)，點擊下一步：選擇Course表，點擊下一步勾選立即運行，點擊下一步：點擊完成，結(jié)

18、果如下：數(shù)據(jù)導(dǎo)入右鍵School數(shù)據(jù)庫，選擇任務(wù)->導(dǎo)出數(shù)據(jù)，在數(shù)據(jù)源選項選擇平面文件，文件名選擇D:SourceAddress.xlsx:點擊下一步，進(jìn)入選擇目標(biāo)窗體，選擇SQL Server Native Client 11.0：一直點下一步直到完成。第7章 ：物理優(yōu)化實驗7.1 建立索引一、實驗?zāi)康膶W(xué)會在SQL Sever中建立索引。二、實驗題目自己動手試一試在students表上為sname字段建立聚簇索引，為email建立非聚簇索引：use schoolgocreate index ix_email on students(email)/*建立非聚簇索引*/declare na

19、me varchar(20)select name=name from sysindexes where id=object_id('students')exec('alter table students drop constraint'+name)goalter table students with nocheck add constraint pk_id primary key nonclustered(sid)gocreate clustered index ix_sname on students(sname)go實驗7.2 覆蓋索引的作用一、實驗?zāi)?/p>

20、的通過本實驗體會覆蓋索引的作用，在以后的實踐中，能夠適時地使用覆蓋索引來提高數(shù)據(jù)庫的性能。二、實驗題目假設(shè)經(jīng)常會有這類查詢:查詢某學(xué)生選課的課程號.該如何建立索引,自己試試看建立索引前后的查詢效率的區(qū)別沒有建立索引時的查詢效率：use Schoolgoset statistics io ongoset statistics time ongoselect cid from CHOICES where sid like '846806971'go建立索引時的查詢效率：create index ix_sid_cid on choices(sid,cid)goselect cid f

21、rom CHOICES where sid like '846806971'go實驗7.3 聚簇索引一、實驗?zāi)康耐ㄟ^實驗體會聚簇索引的優(yōu)缺點，學(xué)會根據(jù)具體情況創(chuàng)建聚簇索引。2、 實驗題目分別試試在三種情況下(無索引、有非聚簇索引、有聚簇索引)向一個表中插入記錄的耗時情況，看看是否與分析的結(jié)論一致。1.建立一個數(shù)據(jù)庫表use schoolgocreate table test_insert( id uniqueidentifier not null rowguidcol default(newid() primary key nonclustered,name varchar(5

22、0) not null, age int, rent int not null)-2.往表中加入數(shù)據(jù)declare i intset i=1while i<=100000begin insert into test_insert(name,age,rent) values(newid(),i,rand()*10000000) set i=i+1endgo用時32秒-3刪除所有記錄，添加一個非聚簇索引delete from test_insertcreate index ix_name on test_insert(name)go-4向表中加入記錄declare i intset i=1w

23、hile i<=100000begin insert into test_insert(name,age,rent) values(newid(),i,rand()*10000000) set i=i+1endgo用時2分36秒-5刪除所有記錄，更改非聚簇索引為聚簇索引delete from test_insertgodrop index test_insert.ix_namegocreate clustered index ix_name on test_insert(name)go -6向表添加記錄declare i intset i=1while i<=100000begin

24、 insert into test_insert(name,age,rent) values(newid(),i,rand()*10000000) set i=i+1endgo用時3分58秒結(jié)果分析：沒有索引時插入數(shù)據(jù)最快，有非聚簇索引時插入慢些，因為要修改索引，而有聚簇索引時最慢，因為數(shù)據(jù)插入前要根據(jù)索引找到插入的位置，有可能還要把其他數(shù)據(jù)移位，插入后要修改索引（但是在并行狀況下，沒有索引或有非聚簇索引的插入可能會造成爭索而延遲，對性能影響大，因為這兩種都是在隨后數(shù)據(jù)頁面插入數(shù)據(jù)。而聚簇索引則不會。）實驗7.4 維護(hù)索引的代價一、實驗?zāi)康乃饕龑τ诓樵兊炔僮魇呛苡袔椭模墙⒑途S護(hù)索引也是

25、需要額外開銷的，空間和時間上都需要，本實驗幫助讀者理解聚簇索引的開銷，由此可以看到，索引并不都是有用的，索引不是建得越多越好，不必要的索引會給系統(tǒng)帶來負(fù)擔(dān)。二、實驗題目試試在choices表上建立幾個非聚簇索引，看看建立和刪除非聚簇索引所需時間。再比較有非聚簇索引時和沒有時更新的速度。建立索引：set statistics time oncreate index ix_sidon choices(sid); SQL Server 分析和編譯時間: CPU 時間= 93 毫秒，占用時間= 2118 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 78 毫秒，占用時間= 459 毫秒。

26、SQL Server 執(zhí)行時間: CPU 時間= 187 毫秒，占用時間= 2647 毫秒。刪除索引：set statistics time ondrop index ix_sidon choices;SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 22 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 92 毫秒。建立索引前，進(jìn)行更新：set statistics time onupdate choicesset score=score-1; SQL

27、 Server 分析和編譯時間: CPU 時間= 15 毫秒，占用時間= 363 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 63 毫秒，占用時間= 3023 毫秒。(9613 行受影響)建立索引IX_sid，進(jìn)行更新：set statistics time oncreate index ix_sidon choices(sid);update choicesset score=score-1;SQL Serv

28、er 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 1 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 47 毫秒，占用時間= 45 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 47 毫秒，占用時間= 129 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。

29、SQL Server 執(zhí)行時間: CPU 時間= 15 毫秒，占用時間= 1300 毫秒。(9613 行受影響)實驗7.5 冗余索引一、實驗?zāi)康?如實驗7.4中所說，索引雖然是一種很有用的查詢結(jié)構(gòu)，但會給系統(tǒng)帶來額外的負(fù)擔(dān)，而且有的情況下所有的索引是冗余的，對提高查詢速度也沒有幫助，本實驗通過一個例子說明了這個問題，并非任意創(chuàng)建索引都是有用的。2、 實驗題目 在choices表里建立score和cid字段上的非聚簇索引，并執(zhí)行查詢試一試看對查詢有沒有性能的提升。沒有建立索引時的查詢效率：use Schoolgoset statistics io ongoset statistics time

30、ongoselect score from CHOICES where sid like '846806971'goSQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 76 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 10 毫秒。 SQL Server 執(zhí)行時間

31、: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 16 毫秒，占用時間= 23 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 16 毫秒，占用時間= 8 毫秒。建立索引后的查詢效率：use Schoolcreate index ix_score on choices(score)goselect score from CHOICES where sid like '846806971'GoSQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 28 毫秒。 SQL Server 執(zhí)行時間

32、: CPU 時間= 0 毫秒，占用時間= 0 毫秒。SQL Server 分析和編譯時間: CPU 時間= 31 毫秒，占用時間= 40 毫秒。表'CHOICES'。掃描計數(shù)1，邏輯讀取2416 次，物理讀取0 次，預(yù)讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預(yù)讀0 次。 SQL Server 執(zhí)行時間: CPU 時間= 0 毫秒，占用時間= 16 毫秒。 SQL Server 執(zhí)行時間: CPU 時間= 31 毫秒，占用時間= 60 毫秒。SQL Server 分析和編譯時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。 SQL Server 執(zhí)

33、行時間: CPU 時間= 0 毫秒，占用時間= 0 毫秒。分析：沒有性能提升，因為score最多100個值，而計算知一個數(shù)據(jù)頁大概有200來條記錄，也就是說每個數(shù)據(jù)頁里平均都會有兩個不同的score值，所以不管什么查詢都是表掃描，所以系統(tǒng)不會用score上的非聚簇索引。同理，cid只有50個不同的值實驗7.6 表中多個索引的選擇一、實驗?zāi)康耐ㄟ^實驗了解單表多索引在執(zhí)行查詢時對索引的選擇規(guī)律，便于以后創(chuàng)建更加有用的索引。2、 實驗題目建立索引：if not exists(select name from sysindexes where name='ix_sid')create

34、index ix_sid on choices(sid);if not exists(select name from sysindexes where name='ix_sid')create index ix_sid on choices(sid);執(zhí)行查詢，查看執(zhí)行計劃：set showplan_all ongoset statistics io ongoset statistics time ongoselect *from choices where sid between '880000000' and '889000000'and

35、tid between '228950000' and '229900000'go實驗7.7 索引與表的連接查詢一、實驗?zāi)康谋淼倪B接查詢是很多數(shù)據(jù)庫操作中涉及的一大類查詢，而且連接查詢是比較費時的一類查詢。本實驗通過幾個例子讓讀者簡單了解如何提高連接查詢的效率。二、實驗題目1、試一試如果上面實驗choices表中沒有sid的主外鍵約束（但還是都有聚簇索引），執(zhí)行此連接查詢時系統(tǒng)默認(rèn)使用什么連接在企業(yè)管理器中把choices表的SID的主外鍵約束刪掉后執(zhí)行連接查詢use Schoolgoset showplan_all ongoselect score from

36、STUDENTS,CHOICES where STUDENTS.grade=2000 and STUDENTS.sid=CHOICES.sidgo分析：沒有主外鍵約束情況下，系統(tǒng)使用的是哈希連接2、如果在choices表的sid字段上沒有索引（students表sid上還是有聚簇索引），或者只有非聚簇索引，在執(zhí)行以上連接查詢時系統(tǒng)的執(zhí)行計劃和執(zhí)行效率如何？a.沒有聚簇索引的情況，執(zhí)行查詢：use Schoolgoset statistics io ongoset statistics time ongoset showplan_all ongoselect score from STUDENT

37、S,CHOICES where STUDENTS.grade=2000 and STUDENTS.sid=CHOICES.sidgo 查詢計劃：b.有非聚簇索引的情況，執(zhí)行查詢：create index ix_sid on choices(sid)goselect score from STUDENTS,CHOICES where STUDENTS.grade=2000 and STUDENTS.sid=CHOICES.sidgo查詢計劃：分析：Choices表SID有非聚簇索引和沒有索引時都是table scan，且用的都是哈希連接。效率沒有SID上建立聚簇索引時高。實驗7.8 填充因子一、實驗?zāi)康睦斫馓畛湟蜃拥淖饔茫鶕?jù)實際情況設(shè)置合適的填充因子2、 實驗題目2）用聚簇索引做一次上面的實驗，看看有什么異同use school GO CREATE TABLE test2 (id uniqueidentifier NOT NULL rowguidcol default (newid() primary key nonclustered, name varcha