數(shù)據(jù)庫實驗三數(shù)據(jù)更新等

1、實驗五實驗報告一、實驗?zāi)康? 掌握實體完整性約束、參照完整性約束;2. 靈活應(yīng)用完整性約束3. 了解觸發(fā)器在的應(yīng)用4. 理解索引的功能和運用并掌握索引的建立方法;二、實驗內(nèi)容5.1實體完整性1）在數(shù)據(jù)庫school中建立表stu_union,進行主鍵約束，在沒有違反實體完整性的前提下 插入并更新一條記錄 建立表stu_uiiion,進行主鍵約束catl-27.school - sqlqueryl.sql*-511create 匸stu_union(sno char(10) primary keyf snaitie char (20) not null, ssex char(10)在沒有違反實體

2、完整性的前提下插入一條記錄bn消息<1行受影響）結(jié)果肖息在沒有違反實體完整性的前提下更新一條記錄彳川sh消息行受影響）itt5.1.1更新update stu union set sname= nikla where sno=110000use2）演示違反實體完整性的插入操作s結(jié)果d消息snosnamessex110000niklause 男馬消息消息技加，級別14,狀態(tài)1,第3亍違反了 primary key 約束 * pk_ _stu_union32e091sf1。 不能在對象語句已終止。3）演示違反實體完整性的更新操作|>5/5.1.3update 3tu_union set

3、 sno=,100001 where sno=110001匚川看消息消息斫級別s狀態(tài)切第2行違反了 primary key 約束 f pk_ _stu_union_ _32e0915f%不能在對象 語句已終止。4）演示事務(wù)的處理，包括事務(wù)的建立，處理以及出錯時的事務(wù)冋滾，演示事務(wù)處理和批處理 的區(qū)別。提示：sql2005相關(guān)語句為begin tranrollback trancommit tran可以這樣演示：新建一個包含兩條語句的事務(wù)，使第一條成功而第二條失敗，然后查看 整個事務(wù)是否回滾。重要提示：sql默認(rèn)只回滾出錯的語句，要回滾整個事務(wù)，需要預(yù)先執(zhí)行以下語句：set xact_abort

4、 ona.先執(zhí)行如下sql語句(可執(zhí)行成功)5.1.4.1|set xact_abort onbegin tran testl insertinto stu_unionvalues(1100021f 1 caroline','女')；insertinto stu_unionvalues ('looos1, 'jini1 j男;select * from stu_union;coirirat tran testlnr曲結(jié)果的消息b.再執(zhí)行如下語句，此時為執(zhí)行第一句insert語句成功，第二句insert語句失敗snosnamessex1i 10000dam

5、on210001niklause男310002caroli ne女410003jim男set xact ai>qrt on>egin tran test2 insertinto stu unionvalues(110004 selectinto stu unioninsertfrom stu unionkrystal男'）hos 匸 cmvalues(110001 commit tran test24rn國結(jié)果出消息行受影響）«5行受影響）消息2627,級別14,狀態(tài)切第7行違反了 primary key 約束 1 pk_stu_union32e0915f1 o不

6、能在對象fdbo .stu_kc.查詢事物是否回滾5.1.4.3查詢是否回滾i select 疫 from stu unionj結(jié)果4l 消息由上可知，表為b步驟即事物出錯前的狀態(tài)，所以整個事物回滾。5）通過建立scholarship表，插入一些數(shù)據(jù)。演示當(dāng)與現(xiàn)有的數(shù)據(jù)環(huán)境不等時，無法建立實 體完整性以及參照完整性。提示：即演示不能為表scholarship生成主鍵和外鍵的情況。a.create table scholarship(ranking char (10) zst.-j_ric criar (13) ,s ey c?iar(19)二： 5 巳二二values('i1,1100

7、041 j 80001)m 二二二二二二5 1.5.2不能b.不能為表scholarship牛成主鍵nujluu丄丄 uuu oiioxcl 丄丄 pvalues('!','10005 * j 6000 *) select * from scholarship< irn曲結(jié)果-j消息rankingstu_nosjnoney1 11j 1000480002 110005goodaddconstraint pk scholarship primary key(ranking)hf消息皿纟及別16,狀態(tài)巧第站亍無法在表scholarship*中可為空的列上走義prima

8、ry key約束。 消息1750,纟及別16,狀態(tài)。，第2行無法創(chuàng)建約束。請參閱前面的錯誤消息。c.不能為表scholarship生成外鍵二二二二二二一二addconstraint fk scholarship foreign key(stu no) references students(sno)彳川 j消息消息1770,級別1,狀蕊0,第2行外鍵'fscholarship'引用了位于被引用表* students *中的無效列* sno o消息17s0,級別1,狀態(tài)0,第2行無法創(chuàng)建約束。請參閱前面的錯誤消息。52參照完整性i)為演示參照完整性，建立表course,令eno為

9、其主鍵，并在stu_union中插入數(shù)據(jù)。為 下面的實驗步驟做預(yù)先準(zhǔn)備。在stu_union中插入數(shù)據(jù)insertinto stu_nnionvalues (»10004 fada insertinto stu_unionvalues (110005 1, 1 krystal伊'女')select * from stu uniol屮 習(xí)結(jié)果由消息snosnamessex1j 10000! damon>10001n iklause男10002caroli ne女110003jim男>10004ada女 >10005krystal女建立表course,令

10、eno為其主鍵enocname1|01db202c#303java404cs505ci2)建立表sc,另sno和eno分別為參照stu_union表以及course表的外鍵，設(shè)定為級連刪 除，并令(sno, eno)為其主鍵。在不違反參照完整性的前提下，插入數(shù)據(jù)。5.2.2create table sc(sno char(10).eno char(15).primary key (snoz eno)fforeign key(sno) references stu_union(sno) on delete cascadeforeign key(eno) references course(eno

11、) on delete cascadeinsert into scvalues(10004,insert into scvalues(10002')insert into scvalues(10005，03')三insert into scvalues(1000304)select * from sc亍riri曲結(jié)果的消息3）演示違反參照完整性的插入數(shù)據(jù)一523違反參照完整性插入 insert into scvalues ( hill1, 'll1)4）在stu_union中刪除數(shù)據(jù)，演示級連刪除。一5 2 4stm_union級連刪除delete wheresno=l

12、100031from stu union彳unrsa消息524*u_union級連刪除 delete from stu_xinion where sno=1100031結(jié)果一工消息<1行受影響)snoeno1i 10001i 0221000401310005035) course中刪除數(shù)據(jù)，演示級連刪除。5.2.5 course級連刪除delete f rcrr. ccurseh7/?iere cric=, 3 3 1w. ocoursejjjy delete from course where cno=1031select * from sdrn6）為了演示多重級連刪除，建立sui_c

13、anl表,令stud為參照stu_union表的外鍵，令card_id 為其主鍵，并插入數(shù)據(jù)。二1結(jié)果一/肖息cardjdstud1j 2302000 *100012230200110005323020021000242302003100047）為了演示多重級連刪除，建立icbc.card表，令stu_card_id為參照stu_card表的外鍵, 令bank_id為其主鍵，并插入數(shù)據(jù)。 5.2.7create table icbc_card( bank_id char(20) primary keyf stu_card_id char(10),foreign key(stu_card_id)

14、 references stu_card(card_id) on delete cascadeinsert into icbc_cardvalues(632711480271 j 23020001)insert into icbc_cardvalues(9632711480251 j 23020011)insert into icbc_cardvalues(163271148029 r12302002')insert into icbc_cardvalues(963271148030'r123020031)select * from icbc_cardb3nkdstucandd

15、1! 63271148025 ! 23020012632711480272 咒 20003632刀48029230200246327114803023020038）通過刪除stu_union表小的一條記錄，演示三個表的多重級連刪除。未刪除時selectfromicbc cardselectfromstu cardselectfromstu unior 4nnil fzi：-.iircardjdstud1i 2302000二j 10001223020011000532302002100024230200310004snosn amessex1! oddo jdamon210001niklause

16、男310002caroli ne女410004ada女bankjdstu_cardd1i 63271148025 i 2302001263271148027230200036327114802923020024632711480302302003刪除 stu_union 記錄 sno=10004select * from icbc_card select * from stu_card select * from stu_union 一528多重級連刪除delete from stu_unionwhere sno=l 10004（1行受影響）結(jié)果:彳3結(jié)果由消息bank idstu card

17、id12302001i 632711480252'"63271'1'48027select * from icbc_card select * from stu_card i select * from stu uniorjcardjdstud12302000! 100012230200110d053230200210002snosnamessex110000 idamon男210001niklause男310002caroline女ft410005krystal女230200023020023632711480299）演示事務(wù)屮進行多重級連刪除失敗的處理。修

18、改icbc_card表的外鍵屬性，使其變?yōu)?on delete no action,演示事務(wù)中通過刪除stu_union表中的一條記錄，多重級連刪除失敗， 整個事務(wù)回滾到事務(wù)的初始狀態(tài)。修改外鍵屬性：-5.2.嶺示事務(wù)巾進廳多重級連刪除失敗的處理:5 1. 一二二二二二 1.二 1. : ；： : _ -二.1 ?add constraint fk_icbc_card_stu_ici foreign key (stu_card_ici) references sr*j card (card id) cn delete nc 己me;< |nr面消息命令已成功完成。0 sf pk_icbc

19、_card_44ff419a f fk_icbc_card_studra 匕事務(wù)中多重級連刪除失敗references stu_card(card_id) on delete no action除失敗selectfrom stu union where sno=,100011;*fromstu unionselectfromstu cardselectfrom±cbc cardcommittran delel的消息消息54?,纟及別16,狀態(tài)。，第2行,delete 語句與 reference 約束"fk_iubu_grd. ."u_idr沖突。該乳査詢事務(wù)回滾s

20、elect * from stu_union select * from stu_card i select * from icbc card結(jié)果匕消息snosn amessex110000 idamon男210001niklause男310002caroli ne女410005krystal女cardjdstud12302000j 1000122302001100053230200210002bankjdstu-cardd163271148025 i 2s020012 63271148027 2s02d003 63271148029 2s02d0253索引的建立和作用1. 實驗?zāi)康膶W(xué)會在sq

21、l server中建立索引通過本實驗體會覆蓋索引的作用，在以后的實踐中，能適時地使用覆蓋索引來提高數(shù)據(jù) 庫的性能。通過實驗體會聚簇索引的優(yōu)缺點，學(xué)會根據(jù)具體情況創(chuàng)建聚簇索引2. 實驗內(nèi)容1) students(sid,sname,email,grade)在sname上建立聚簇索引，grade上建立非聚簇索 弓i，并分析所遇到的問題create clustered index stusname on students(snaimcreate index stugrade cn students(grade)4 川ej消息&)?rs ，畫；：;；犧/檯鐸引。請在創(chuàng)建新聚集素引i分析問題：l

22、ecluster后要ed2要刪除本來存在的聚集索引，在刪除前應(yīng)先移除主鍵0 口索引pk_students (聚集)日圍 sid (char(10), not null) 這sname (char(3o), not null 圍 email (char(30), null) 國 grade (int, null)i+)ri5.3.11create clustered index stusname on students(sname);create index srugrade cn srudenrs (grade)命令已成功完成。索引出stugrade (不唯一r非懿i為stusname陳坍s

23、統(tǒng)計信息2)數(shù)據(jù)庫school的選課表choices有如下結(jié)構(gòu):choices(no, sid, tid, cid, score)假設(shè)選課表集中用于查詢分析，經(jīng)常執(zhí)行統(tǒng)計某課程修讀的學(xué)生人數(shù)查詢訪問要求：a. 首先執(zhí)行沒有索引的實驗（設(shè)數(shù)據(jù)庫choices表在cid列上沒有索引）5.3.2select coxint (*) from choices where cid=, 10010irr結(jié)果一丄消息60*27select count(*) from choices where cid=,10010nr亙結(jié)果怡消息sql server分析和編淫時間：_cpu時間=1108 <秒，占用時間

24、=h92毫秒。sql server分析#匡扁譯時間：亠cpu時間=0毫秒，占用時間=1毫秒。<1行受影響）sql server執(zhí)行時回：亠cpu時間=78至秒，占用時間=7至秒。sql server分析#嗓譯時間：亠cpu時間=0至秒，占用時間=1至秒。b. 然后做有索引的實驗索引c.>41)choicid (聚集)4 | nr>國結(jié)果島消息sql seer分析和編譯時間:5.3.2select count(*) from choices where cid=,100101create clustered index choicid on choices(cid)cpu時間=

25、0毫秒，占用時間=i毫秒。 sql seer分析稅編譯時洵：亠cpu時間=0毫秒，占用時間=1毫秒。 sql seer分析稅編譯時洵：亠cpu時間=0毫秒，占用時間=1毫秒。<1行受影響）sql server 扌丸行亠cpu時間=0毫秒，占用時間=1毫秒。 sql server分析#唆譯時洵二亠cpu時間=0毫秒，占用時間=1毫秒。d.對比試驗結(jié)果，并進行分析對比查詢同一條指令占用時間可得，建立索引后査詢快得多。3）以數(shù)據(jù)庫school中choices表為例，設(shè)建表時考慮到以后經(jīng)常有一個用sid查 詢此學(xué)生所有選課信息的查詢，考慮到一般學(xué)生不止選一門課，且要詢問這些記錄 的所有信息，故在

26、sid上建立索引，使相同sid的記錄存在一起，取數(shù)據(jù)頁面時能一 起取出來，減少數(shù)據(jù)頁面的存取次數(shù)要求：a.首先執(zhí)行沒有任何索引的情況曲結(jié)果的消息（無列名）12select count(*) from choices where sid=l8377362775.3.3select count (*) froir. choices where sid=l 8377362774j結(jié)果爾消息sql server分析和編淫時間： cpu時間=118毫秒，占用時間=1211毫秒。 sql server分析#哇扁譯時間：_cpu時間=0毫秒，占用時間=1毫秒。<1行受影響）sql server執(zhí)行時回

27、：亠cpu時間=62毫秒，占用時間=1毫秒。sql server分析汞世扁譯時間：亠cpu時間=0毫秒，占用時間=1毫秒。b.在sid上建有非聚簇索引的情況索引uj i 一日出choisid （不唯一 r m嘿集）aexeuu uumiu ) 丄丄mu uuuxuea wne丄匕 axu. oo / / coz / /非聚簇索引create index choisid on choices(sid);waj a 廠仟廠 cm, t 祝 -f-rntn 廠bc*i 廠 aa wha"ra wi r?= i 2w2 伍 *7 w 魚消息sql server分析無唆譯時間：亠cpu時間=0

28、毫秒，占用時間=i毫秒。sql server分析汞嗓譯時間：亠cpu時間=0毫秒，占用時間=2毫秒。sql server執(zhí)行時間_cpu時間=546毫秒，占用時間=544毫秒。sql server執(zhí)行時間j亠cpu時間=s46毫秒，占用時間=549毫秒。sql server分析汞世扁譯時間：亠cpu時間=0毫秒，占用時間=1毫秒。非聚簇索引create index choisid on choices(sid);select count(*) from choices where sid=,837736277r |川結(jié)果i幽消息sql server分析無唆譯時間二亠cpu時間=0毫秒，占用時間

29、=1毫秒。sql server分析不矽譯時洵二亠cpu時間=0毫秒，占用時間=1毫秒。sql server分析不矽譯時洵二亠cpu時間=0毫秒，占用時間=1毫秒。4行受影響）sql server執(zhí)行日頂：亠cpu時間=0毫秒，占用時間=1毫秒。sql server分析稅編譯時間：亠cpu時間=0毫秒，占用時間=1毫秒。c. 在sid上建有聚簇索引的情況索引曰choicsid 陳集） 缶choisid （不唯一,勻障集）聚簇索引create clustered index choicsid on choices(sid);ma j ar十1- i ft err r'nn i cam &q

30、uot;"haya a i h= 11sql server分析#喙譯時間：亠cpu時間=0毫秒，占用時間=3毫秒。sql server執(zhí)行時間_cpu時間=1045至秒占用時間=430至秒。sql server分析和縮譯時間：亠cpu時間=432至秒，占用時間=432至秒。sql server執(zhí)行時間亠cpu時間=499毫秒,占用時間=541毫秒。sql server執(zhí)行時間_cpu時間=1560至秒占用時間=978至秒。sql server分析#喙譯時間：亠cpu時間=0至秒，占用時間=1至秒。聚簇索引create clustered index choicsid on choic

31、es(sidi;* 1川j結(jié)果由消息sql server分析和編譯時間二select count(*) from choices where sid=,837736277cpu時間=0毫秒，占用時間=1至秒。 sql server分析不唆譯時洵二亠cpu時間=0毫秒，占用時間=1至秒。 sql server分析不唆譯時洵二亠cpu時間=0毫秒，占用時間=1至秒。<1行受影響)sql server 執(zhí)行_cpu時間=0毫秒，占用時間=1至秒。sql server分析租編譯時洵：_cpu時間=0毫秒，占用時間=1至秒。d. 對比實驗結(jié)果，并進行分析答：建立索引比沒有建立索引查詢要來得快，理論上聚簇索引應(yīng)比非聚簇索引快, 但由以上實驗中，兩者并無多大差別。5.4觸發(fā)器的建立和使用實驗?zāi)康模和ㄟ^實驗使學(xué)生加深對數(shù)據(jù)完整性的理解，學(xué)會創(chuàng)建和使用觸發(fā)器 實驗內(nèi)容：1)創(chuàng)建 teacher (tno, tname> tsex, tage, tdept)表，并自定義 2 個約束 u1 和 u2,其 中u1規(guī)定tname字段唯一，u2規(guī)定tage上限是2&為teacher表建立觸發(fā)器t1,當(dāng) 插入或更新表中的數(shù)據(jù)時，保證所操作的記錄的tage值大于0,并插入幾條數(shù)據(jù).建表tnarr.e char (10) consmnt; u丄 umq tsex(10) ,tage char(