數(shù)據(jù)庫完整性與安全性實(shí)驗(yàn)

1.實(shí)驗(yàn)五數(shù)據(jù)庫完整性與安全性實(shí)驗(yàn)1.1實(shí)驗(yàn)?zāi)康耐ㄟ^對(duì)完整性規(guī)則的定義實(shí)現(xiàn)，熟悉了解Mysql中完整性保證的規(guī)則和實(shí)現(xiàn)方法，加深對(duì)數(shù)據(jù)完整性的理解。通過對(duì)安全性相關(guān)內(nèi)容的定義，熟悉了解Mysql中安全性的內(nèi)容和實(shí)現(xiàn)方法，加深對(duì)數(shù)據(jù)庫安全性的理解1.2實(shí)驗(yàn)內(nèi)容1.2.1完整性實(shí)驗(yàn)（1）分別定義學(xué)生數(shù)據(jù)庫中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束（2）分別向?qū)W生表、課程表插入具有相同學(xué)號(hào)和相同課程編號(hào)的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗(yàn)證其實(shí)體完整性約束；（3）向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號(hào)是課程表中沒有的，驗(yàn)證參照完整性約束；（4）刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束；（5）定義存儲(chǔ)過程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行。（6）定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試。（7）用sql完成以上操作。1.2.2安全性實(shí)驗(yàn)1）定義一新的登陸帳號(hào)、數(shù)據(jù)庫用戶，并授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限；（2）分別用sa用戶和新定義的用戶訪問學(xué)生數(shù)據(jù)庫，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改;3）再次用此用戶訪問學(xué)生數(shù)據(jù)庫，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改。（4）用SQL語句分別完成以上內(nèi)容。1.3實(shí)驗(yàn)環(huán)境Window8操作系統(tǒng)Mysql8.0版本數(shù)據(jù)庫Mysqlworkbench8.0可視化工具M(jìn)ysql命令行編輯器1.4實(shí)驗(yàn)步驟及結(jié)果分析1.4.1完整性分別定義學(xué)生數(shù)據(jù)庫中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束1.首先因?yàn)樵谥皠?chuàng)建表的時(shí)候定義了主鍵，因此，我們需要先將所有表的主鍵撤銷掉，然后重新創(chuàng)建主鍵。Mysql語句(刪除主鍵)：altertablestudentdropprimarykey;

altertablecoursedropprimarykey;altertablestudentdropprimary:altertablestudentdropprimary:uei1?03(,54rowsaffectedCl_13sec^■Records:54Duplicates:0Uavnings■Records:54nysql>alter七ablecoursedropprimari/ke*/；QueryOK,5rowsaffected<0.S1sec>Records:5Duplicates:0Uarnings=0nijEql>altertablescdpopprimapykep；Queri/OK,142f-oweaffectedC0_8Gsee?Records:142Du.plic：a七?s：z0Warninas-02.重新創(chuàng)建主鍵2.Mysql語句(創(chuàng)建主鍵)：altertablestudentaddprimarykey(sno);altertablecourseaddprimarykey(cno);altertablescaddprimarykey(sno,cno);luepyOK-0rowsaffectedAcords:0Duplicates:luepyOK-0rowsaffectedAcords:0Duplicates:0i<^sql>iiFsql>altertablecourse!uerv0心0rowsaffected:ecords:SDuplicates:0iysql>altertablescadd!uepyOK,0rowsaffectediecovds:0Duiolicates:9Uarnings:0addprimarylte*;<cno>；<1.14sec>Marnings:0prinarykeyCsno,eno>；C0_S3eec>3.到此，我們已經(jīng)重新添加了各表的主鍵，接下來我們?yōu)镾C表添加外鍵。3.到此，我們已經(jīng)重新添加了各表的主鍵，接下來我們?yōu)镾C表添加外鍵。Mysql語句(創(chuàng)建外鍵)：altertablescaddconstraintforeignkey(sno)referencesstudent(sno);altertablescaddconstraintforeignkey(cno)referencescourse(cno);ysql>altertablescaddconstraint￡oreignkey<sno)referencesstudent<sno>；uepyOK,142rowsaffected<1.16sec>iecoi'ds:142Duplicates:0Warnings:0ysql>altertablescaddconstraint￡oreignkey<cno)referencescoui'se<cno>；uei'yOK,142rowsaffected<i.35sec>iecoi'ds:142Duplicates:0Warnings:0

4.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結(jié)構(gòu)。Mysql4.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結(jié)構(gòu)。Mysql語句（查看表定義）：showcreatetablestudent;showcreatetablecourse;showcreatetablesc;由上面結(jié)果可知：我們成功創(chuàng)建了各表的主鍵，并成功為SC表創(chuàng)建了sno，cno兩個(gè)外鍵。分別向?qū)W生表、課程表插入具有相同學(xué)號(hào)和相同課程編號(hào)的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗(yàn)證其實(shí)體完整性約束首先查找一下student表中的學(xué)生信息，以便下面添加重復(fù)學(xué)號(hào)的學(xué)生Mysql語言：select*fromstudent;iipsql>select*fromstudent；sno!snane!sex!bdate!dept!classno!3626130202362633626436266春熾石$同雷霏洪昊袁唐吳3626130202362633626436266春熾石$同雷霏洪昊袁唐吳am「:.p_l:.nt—=?bl—??一1980-01-021980-01-021980-01-021980-01-021980-01-0200:00:0000:00:0000：00：0000：00：W00：00：00=口亠__口亠一一S一亙呂2.向student表中插入一個(gè)重復(fù)學(xué)號(hào)的學(xué)生，驗(yàn)證完整性約束。我選擇了30203學(xué)號(hào)，由上面的結(jié)果可知，該學(xué)號(hào)已經(jīng)存在。Mysql語句（添加一個(gè)新的學(xué)號(hào)為30203的學(xué)生）：insertintostudentvalue（‘30203','茹興康','男','1997-07-0700:00:00','計(jì)算機(jī)','3174'）;ysql>insertintostudentualg「迪陰'「茹興康'「男'/即-即00:00:00J,Jy|-韋機(jī)J,J31740;RROR1062<23000>：Duplicateentry,30203,forkeyJPRIMAB￥,上面結(jié)果顯示：添加失敗，因?yàn)閟no為student表的主鍵，不能重復(fù)，驗(yàn)證了其完整性約束。3.同上，我們向course表中添加一個(gè)具有相同cno的課程。Mysql語句：select*fromcourse;iiysql>select*fponcoupse；!eno!cname!lhoup!credit!semester!!CS1!CS2!C03!C04!CS1!CS2!C03!C04!C0551!51!51!40!30!iitr；JAUA程序謨計(jì)!計(jì)算機(jī)組成原理!rowsinset^Q_Q0seci5Fsql>insertintocoursevalue<JC01JJ數(shù)據(jù)庫JJ5BJJ1JJ春」〉；RROR1062<230Q0>=DuplicateentryJC01JforIwyJPRIMARVJ上面結(jié)果顯示：成功驗(yàn)證了完成性約束。向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號(hào)是課程表中沒有的，驗(yàn)證參照完整性約束向?qū)W生表中添加一條數(shù)據(jù)，其中課程編號(hào)在course中不存在，我選擇了C06號(hào)。Mysql語句（向?qū)W號(hào)為30203的學(xué)生選課中添加C06選課）：insertintoscvalues（‘30203','C06','100'）;tysql>insertintoscualues<J3Q2Q3*,*CBSJ,*100J>；1RROR1452<23000>:Cannotaddopupdateachildrou：aforeignkeyconstraintfLils<ltestll.'sc\CONSTRAINTlsc_ibfk_2lFOREIGNKE￥<lcno'>REFERENCES1coups'<lcno'>>由結(jié)果來看：插入失敗，因?yàn)閏ourse的eno是SC的外碼，C06在course中不存在，由于參照完整性約束，所以添加失敗，驗(yàn)證成功。刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束刪除學(xué)生表，驗(yàn)證參照完整性約束。Mysql語句：droptablestudent;iysql>droptablestudent；TtROR1217<23000>:Cannotdeleteorupdateaparentrow：aforeignkeyconstraitfails由結(jié)果來看：由于student中的sno是sc的一個(gè)外碼，所以不能刪除student，成功驗(yàn)證了參照完整性約束。定義存儲(chǔ)過程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行1.首先定義一個(gè)根據(jù)學(xué)生學(xué)號(hào)搜索的存儲(chǔ)過程。但是由于‘；'是一個(gè)結(jié)束標(biāo)志，因此存儲(chǔ)過程定義失敗，因?yàn)榈谝粋€(gè)遇到語句的‘；時(shí)候，該語句還沒結(jié)束導(dǎo)致‘；'后面的語句沒有加工nysql>createproceduresearch_sc_sno<idcbar<6>>->beginselectsno□.enofromscwhepesno=id；ERROR1064<42000>:￥ouhaveanepropinyourSQLsyntax；checkthemanualthatcorrespondstoyourMySQLsei'uei*uersionfoftherightsyntaxtousenearJJatlin巳2解決上面問題，將結(jié)束符號(hào)更換為其他字符，我替換為了#，使用delimiter語句Mysql語句：delimiter#createproceduresearch_sc_sno(idchar(6))beginselectsno,cnofromscwheresno=id;end#delimiterItiysql>createproceduresearch_sc_snoCidcharC6>>->beginselectsno^cnoffoihecuhere￡no=id；->endSmeryOK,0pousaffected(0.20sec>存儲(chǔ)過程定義成功。使用存儲(chǔ)過程查看學(xué)生選課情況。Mysql語句(查詢學(xué)號(hào)為30203的學(xué)生選課情況):callsearch_sc_sno(‘30203')#mi/sql?call3earch_sc_3no<f30303*>ttTOC\o"1-5"\h\z-+h+IsnoIenoI-+h+i30203iC03ii302B3iC04ii302B3iC05i+H+3rousinset(0.00sec>Queri/0K-0rowsaffectedC0-01sec>定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試1.定義觸發(fā)器，當(dāng)向?qū)W生表插入新記錄時(shí)，將所有學(xué)生出生日期+1；Mysql語句：createtriggeradd1_bdatebeforeinsertonstudentforeachrowbeiyeql>createtpiggepaddl_bdate—>beforeinser七onstudent->foreachrowbegin->updatestudentsetbdate=date_add<bdate,interval1day)；->endtt?eryOK,0rowsaffected<0.12sec>

2.向?qū)W生表中插入新紀(jì)錄，注意：必須插入sno無重復(fù)的新紀(jì)錄Mysql語句：insertintostudentvalue（‘12345','茹興康','男','1997-07-0700:00:00','計(jì)算機(jī)','3174'）;ysql>insertintostudentualue<J123452.向?qū)W生表中插入新紀(jì)錄，注意：必須插入sno無重復(fù)的新紀(jì)錄Mysql語句：insertintostudentvalue（‘12345','茹興康','男','1997-07-0700:00:00','計(jì)算機(jī)','3174'）;辜機(jī)J,f3174JRROR1442<H￥000>:CanJtupdatetableJstudent1instoredfunction/triggepbecaseitisalreadyusedbystatementwhichinuokedthisstoredfunctionZtrigger.但是結(jié)果顯示：在觸發(fā)器中不能更新student表的內(nèi)容。后來我在網(wǎng)上查找了一些資料：即當(dāng)對(duì)該表進(jìn)行插入操作時(shí)，由于觸發(fā)器要將每一行進(jìn)行修改，那么剛開始插入的新紀(jì)錄可能會(huì)導(dǎo)致無限制的遞歸。因此，需要復(fù)制一個(gè)新的student表，由一個(gè)表的插入引起另一個(gè)表的觸發(fā)。3.創(chuàng)建一個(gè)student復(fù)制表Mysql語句：createtablestudentlselect*fromstudent;iysq；l^ci*eatetablew七udentlselect*fromstuden七；|u.eryOK,54rowsaffected<0_66sec^:ecov-ds：:54Duplicates:3Uapnin￡f￡:04.重新創(chuàng)建觸發(fā)器，讓studentl的插入引起student的觸發(fā)(注意，在創(chuàng)建前把之前的刪除掉)Mysql語句：createtriggeraddl_bdatebeforeinsertonstudentlforeachrowbeginupdatestudentsetbdate=date_add(bdate,intervallday);end#之后向studentl中插入新紀(jì)錄，發(fā)現(xiàn)插入成功。mysql>createtriggeraddl_bdate->afterinsertonstudentl->foreachrowbegin->updatestudentsetbdate=date_add<bdate,intepua11day>；->end#QueryOK,0rowsaffectedC0.09sec>|mysql>insertintostudent：Lvalue<J12345J,JJn7(f1997-07-0700=00=00','訐算機(jī)31740#QueryOK,1rowaffected<0.24sec>5.搜索student和student1表的記錄，觀察變化ysql>select*ffopistudentllt!d.ept!classno!snosnamesex!lid-ate男男男男男男男!30201!30202!3S2S3:30204:30206:30207:30208春熾奩量fiB非洪金廣昊士疑唐吳連王蘇!!d.ept!classno!snosnamesex!lid-ate男男男男男男男!30201!30202!3S2S3:30204:30206:30207:30208春熾奩量fiB非洪金廣昊士疑唐吳連王蘇!1980-01-0200:00!1980-01-02盹!1980-61-0200：00i1980-01-0200:00i1980-01-0200:00i1980-01-0200:00i1980-01-0200:00!3922!3022)</sql>select*fr-onstud&nttt卜+HHsnosnamesex!bdateidepticlassno30201302023020330204302063020730208春熾芟于石?-冃雷霏洪金廣吳土靈唐昊連王蘇.Bsnosnamesex!bdateidepticlassno30201302023020330204302063020730208春熾芟于石?-冃雷霏洪金廣吳土靈唐昊連王蘇.B「=.BmrBTlBnrTl『Br.一HtKs..-?s..?-sJs直■.尋」:1?80-01-0300!1980-01-0300!1980-01-0300!1980-01-0300!1980-01-0303:1J80-01-0300:1￥80-01-030000000000300000亠一E亠一豆一一e一一口=口亠__口亠__|=|B000B0BB000B0B:3022i3022i3022i3022i3022!3022:3022從上兩個(gè)表的結(jié)果來看，我們成功引起了觸發(fā)，student的bdate都增多了一天。1.4.2安全性定義一新的登陸帳號(hào)、數(shù)據(jù)庫用戶，并授予其訪問學(xué)生數(shù)據(jù)庫的讀權(quán)限在root用戶下定義一個(gè)新的用戶auserMysql語句：createuser‘a(chǎn)user'@'localhost'identifiedby‘072312';賦予新用戶auser對(duì)student表的select和update權(quán)限Mysql語句：grantselect,updateontest1.studentto‘a(chǎn)user'@'localhost';ysq;L>cpeateuser1au.seLocalhost,identified.by1072312J；iueF-ifOK,0f-owsaffected.(0.14sec>ysq;l>srr-antselect,updateontest1?studentto1au.ser-11?11qc^lhost1；UeriFOK,0rowsaffected.(0.14sec>上圖顯示：新用戶創(chuàng)建成功權(quán)限賦予成功。

分別用root用戶和新定義的用戶訪問學(xué)生數(shù)據(jù)庫，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改使用root用戶查詢成功。登錄root用戶，進(jìn)行使用root用戶查詢成功。登錄root用戶，進(jìn)行student表的查詢操作，此處我查詢了30203學(xué)號(hào)的學(xué)生信息Mysql語句：Select*fromstudentwheresno=30203;2.用root用戶修改student表的信息，此處我更改了30203學(xué)生的班級(jí)為3000；3.登錄新創(chuàng)建的auser用戶，進(jìn)行與root用戶相同的操作。Mysql語句：3.Select*fromstudentwheresno=30203;Updatestudentsetclassno='3022';C:\WIND0WS\system32\cmd.exe-mysql-uauser-p:：\Useps\RXK>mysql-uauser-pnterpassword：■図尺図覧貳'elcometotheMuGQLmonitor.Commandsendwith:opXg.ourMySQLconnectionidis15epuepuepsion：8.M.llHy￡QLGormunityServer一GPL：opyright<