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

1.1實驗目的通過對完整性規(guī)則的定義實現(xiàn)，熟悉了解Mysql中完整性保證的規(guī)則和實現(xiàn)方法，加深對數(shù)據(jù)完整性的理解。通過對安全性相關內(nèi)容的定義，熟悉了解Mysql中安全性的內(nèi)容和實現(xiàn)方法，加深對數(shù)據(jù)庫安全性的理解1.2實驗內(nèi)容1.2.1完整性實驗（1） 分別定義學生數(shù)據(jù)庫中各基表的主鍵、外鍵，實現(xiàn)實體完整性約束和參照完整性約束；（2） 分別向?qū)W生表、課程表插入具有相同學號和相同課程編號的學生數(shù)據(jù)和課程數(shù)據(jù)，驗證其實體完整性約束；（3） 向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號是課程表中沒有的，驗證參照完整性約束；（4） 刪除學生表中的所有數(shù)據(jù)，驗證參照完整性約束；（5） 定義存儲過程，完成查詢某個學生的選課情況，并執(zhí)行。（6） 定義觸發(fā)器，當向?qū)W生表插入新的一條記錄時，將所有學生出生日期加1；并對其進行測試。（7） 用sql完成以上操作。1.2.2安全性實驗1）定義一新的登陸帳號、數(shù)據(jù)庫用戶，并授予其訪問學生數(shù)據(jù)庫的讀權限；（2）分別用sa用戶和新定義的用戶訪問學生數(shù)據(jù)庫，并對其中的學生表數(shù)據(jù)進行修改;3） 再次用此用戶訪問學生數(shù)據(jù)庫，并對其中的學生表數(shù)據(jù)進行修改。4） 用SQL語句分別完成以上內(nèi)容。1.3實驗環(huán)境Window8操作系統(tǒng)Mysql8.0版本數(shù)據(jù)庫Mysqlworkbench8.0可視化工具Mysql命令行編輯器1.4實驗步驟及結果分析1.4.1完整性分別定義學生數(shù)據(jù)庫中各基表的主鍵、外鍵，實現(xiàn)實體完整性約束和參照完整性約束Mysql語句(刪除主鍵)：altertablestudentdropprimarykey;altertablecoursedropprimarykey;Mysql語句(刪除主鍵)：altertablestudentdropprimarykey;altertablecoursedropprimarykey;altertablescdropprimarykey;fri^sql>altertablestudentdropprimaryHuer^OK,54rowsaffectedCl-13sec?Du.plicates:0Uapnings:0n?/sqlyalter七a.blecoursedropprimar*/ke；QueryOK,5rows&.￡fect&d<0.91sec>Records:5Duplicates:0Uarnings:0mysql>altertablescdpopprimarykep；Quev^OK,142powsaffectedC0-8Gsec^Records:142Du.pLi_cat：es-0Uat^ninqs-02.重新創(chuàng)建主鍵2.Mysql語句(創(chuàng)建主鍵)：altertablestudentaddprimarykey(sno);altertablecourseaddprimarykey(cno);altertablescaddprimarykey(sno,cno);!ueF</0K-Mrowsaffectediecovds:0Duplicates:!ueF</0K-Mrowsaffectediecovds:0Duplicates:0i<jsql>i5fsql>altertablecourseuer^OKj0rowsaffectedlecords:8Buplica.0iijsql>altertablescaddlueryOK,0rowsaffectediecovds:0Duplicates:0Uarnings:0addprimarylfe5F<cno>；<1-14sec>Harnings=0primarykey<sno,cno>；C0_83sec>3.到此，我們已經(jīng)重新添加了各表的主鍵，接下來我們?yōu)镾C表添加外鍵。3.到此，我們已經(jīng)重新添加了各表的主鍵，接下來我們?yōu)镾C表添加外鍵。Mysql語句(創(chuàng)建外鍵)：altertablescaddconstraintforeignkey(sno)referencesstudent(sno);altertablescaddconstraintforeignkey(cno)referencescourse(cno);ysql>altertablescaddconstraintforeignkey<sno)referencesstudent<sno>；ueryOK,142rowsaffected<1.16sec>iecoi'ds:142Duplicates:0Warnings:0ysql>altertablescaddconstraintforeignkey<cno)referencescoui'se<cno>；ueryOK,142rowsaffected.35sec^>iecoi'ds:142Duplicates:0Warnings:0

4.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結構。Mysql4.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結構。Mysql語句（查看表定義）：showcreatetablestudent;showcreatetablecourse;showcreatetablesc;由上面結果可知：我們成功創(chuàng)建了各表的主鍵，并成功為SC表創(chuàng)建了sno，cno兩個外鍵。分別向?qū)W生表、課程表插入具有相同學號和相同課程編號的學生數(shù)據(jù)和課程數(shù)據(jù)，驗證其實體完整性約束首先查找一下student表中的學生信息，以便下面添加重復學號的學生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表中插入一個重復學號的學生，驗證完整性約束。我選擇了30203學號，由上面的結果可知，該學號已經(jīng)存在。Mysql語句（添加一個新的學號為30203的學生）：insertintostudentvalue（‘30203','茹興康男','1997-07-0700:00:00','計算機','3174'）;ysql>insertintostudentualue<f30203f,J ,J1997-07-0700:00:00J,Jy|-辜機J,J3174f>；RROR1062C23000>：DuplicateEntpy,30203,forkevJPRIMAn￥,上面結果顯示：添加失敗，因為sno為student表的主鍵，不能重復，驗證了其完整性約束。3.同上，我們向course表中添加一個具有相同eno的課程。Mysql語句：select*fromcourse;i ysql>select*froncourse；!eno!cnane !lliour!credit!semester!!C01!C02!C03!!C01!C02!C03!C04!C0551!51!51!40!30!JfiUfi程序謨計!計算機組成原理!rousinset<Q_Q0secJ5Fsql>insertintocoursevalue<JCB1JJ數(shù)據(jù)庫JJ5BJJ1JJ春」〉；RROR1062<230Q0>=Duplicateentr^JCB1Jfor心號JPEIMAEVJ上面結果顯示：成功驗證了完成性約束。向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號是課程表中沒有的，驗證參照完整性約束向?qū)W生表中添加一條數(shù)據(jù)，其中課程編號在course中不存在，我選擇了C06號。Mysql語句（向?qū)W號為30203的學生選課中添加C06選課）：insertintoscvalues（'30203','C06','100‘）；tysql>insertintoscualues<J3Q2Q3*,*CBSJ,*100J>；1RROR1452<23000>:Cannotaddopupdateachildrou：aforeignkeyconstraintfLils<ltestll.'sc\CONSTRAINTlsc_ibfk_2lFOREIGNKE￥<lcno>REFERENCES1coups'<lcno'>>由結果來看：插入失敗，因為course的cno是SC的外碼，C06在course中不存在，由于參照完整性約束，所以添加失敗，驗證成功。刪除學生表中的所有數(shù)據(jù)，驗證參照完整性約束刪除學生表，驗證參照完整性約束。Mysql語句：droptablestudent;iysql>droptablestudent；TIROR1217 :Cannotdeleteopupdateaparentrou:aforeignkeyconstraitfails由結果來看：由于student中的sno是se的一個外碼，所以不能刪除student，成功驗證了參照完整性約束。

定義存儲過程，完成查詢某個學生的選課情況，并執(zhí)行1.首先定義一個根據(jù)學生學號搜索的存儲過程。但是由于‘；'是一個結束標志，因此存儲過程定義失敗，因為第一個遇到語句的‘；時候，該語句還沒結束導致‘；后面的語句沒有加工mysql>createproceduresearch_sc_sno<idcliar<6>>->beginselectsno□.enofromscwhepesno=id；ERROR1064<42000>:VouhaveaneppopinyourSQLsyntax；checkthemanualthatcorrespondstoyourMvSQLsei'uei*uersion￡optherightsyntaxtouseneap11atline2解決上面問題，將結束符號更換為其他字符，我替換為了#，使用delimiter語句Mysql語句：delimiter#createproceduresearch_sc_sno(idchar(6))beginselectsno,cnofromscwheresno=id;end#iysql>delimiterItiysql>createprocedureaearch_sc_snoCidcharC6>>->beginselectsno^cnofromscwhepesna=id；->endttlueryOH,0fousaffected(0.20sec>存儲過程定義成功。使用存儲過程查看學生選課情況。Mysql語句(查詢學號為30203的學生選課情況)callsearch_sc_sno(‘30203')#m</sql>callsearch_sc_3noCf30203*>ltTOC\o"1-5"\h\z-+ h +isnoienoi-+ h +i30203iC03ii30263iC04ii30263iC05i+ H +3rousinset<0.00sec>1.4.1.6定義觸發(fā)器，當向?qū)W生表插入新的一條記錄時，將所有學生出生日期加1.4.1.6定義觸發(fā)器，當向?qū)W生表插入新的一條記錄時，將所有學生出生日期加1；并對其進行測試1.定義觸發(fā)器，當向?qū)W生表插入新記錄時，將所有學生出生日期+1；Mysql語句：createtriggeradd1_bdatebeforeinsertonstudentforeachrowbe iyeql>epeatetviggepaddl_bdate—>beforeinset?七onstudent->foreachrowbegin->updatestudentsetbdate=date_add<bdate,interval1day)；->endttMeryOK,0rowsaffected<0.12sec>

2.向?qū)W生表中插入新紀錄，注意：必須插入sno無重復的新紀錄Mysql語句：insertintostudentvalue（T2345','茹興康男','1997-07-0700:00:00','計算機','3174'）;ysql>insertintostudentvalue<J123452.向?qū)W生表中插入新紀錄，注意：必須插入sno無重復的新紀錄Mysql語句：insertintostudentvalue（T2345','茹興康男','1997-07-0700:00:00','計算機','3174'）;韋機J^3174J>#RROR1442<H￥000>:CanJtupdatetableJstiident1instoredfunction/tpiggerbecaseitisalreadyusedbystatementuhichinvokedthisstoredfunctionZtrigger-但是結果顯示：在觸發(fā)器中不能更新student表的內(nèi)容。后來我在網(wǎng)上查找了一些資料：即當對該表進行插入操作時，由于觸發(fā)器要將每一行進行修改，那么剛開始插入的新紀錄可能會導致無限制的遞歸。因此，需要復制一個新的student表，由一個表的插入引起另一個表的觸發(fā)。3.創(chuàng)建一個student復制表Mysql語句：createtablestudentlselect*fromstudent;iyscreatetablew七udentlselect*fromstuden七；!u.eryOK,54rowsaffected<0_66sec^!ecov-ds：:54Duplicates:3Uapnin￡f￡:B4.重新創(chuàng)建觸發(fā)器，讓student1的插入引起student的觸發(fā)(注意，在創(chuàng)建前把之前的刪除掉)Mysql語句：createtriggeradd1_bdatebeforeinsertonstudent1foreachrowbeginupdatestudentsetbdate=date_add(bdate,interval1day);end#之后向student1之后向student1中插入新紀錄，發(fā)現(xiàn)插入成功。->afterinsertonstudentl->foreachrowbegin->updatestudentsetbdate=date_add<bdate,interua11day〉；->endttQiievyOK,0powsaffectedC0.09sec> |mysql>insertinto就udentlualT'1234『「茹興康'「男'「1.站即-即00:00:00J,J訐算機J^3174^>#QueryOK,1rouaffected<0.24sec>5.搜索student和student1表的記錄，觀察變化iysql>select*fpoidstudentlK!d.ept!c：lassno!snosnamesex!lid-ate男男男男男男男!30201!30202!3S2S3:30204:30206:30207:30208春熾奩量fiB!d.ept!c：lassno!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)5Fsql>select*fr-onstud&ntlt卜 + H H snosnamesexIbdateidepticlaaano:30201!30202!30203!30204!3020G!30207isnosnamesexIbdateidepticlaaano:30201!30202!30203!30204!3020G!30207i30208春熾克右￡■冃雷霏洪金廣吳袁唐昊連王蘇mp.BpBrvBTlBnBPTnr?一BTKS..-?S..?-SJS直■.尋」:1?80-01-03!1980-01-03!1980-01-03!1980-01-03!1980-01-03!1J&0-01-03i1780-01-0300:00:0000:00:0000:00:0000:00:0000=00=0000=00=0000=00=00亠=H亠一一H=e一一口=e_e__日:3022!3022!3022!3022!3022!3022:3022從上兩個表的結果來看，我們成功引起了觸發(fā)，student的bdate都增多了一天。1.4.2安全性1.4.2.1定義一新的登陸帳號、數(shù)據(jù)庫用戶，并授予其訪問學生數(shù)據(jù)庫的讀權限在root用戶下定義一個新的用戶auserMysql語句：createuser‘a(chǎn)user'@'localhost'identifiedby‘072312';賦予新用戶auser對student表的select和update權限Mysql語句：grantselect,updateontest1.studentto‘a(chǎn)user'@'localhost';iF￡q;L>cpeateuser1au.selocalho￡t,identified.by1072312J；Uev-iFOK,0p-owsaffected.(0.14sec>iysq;l>grantselect,updateontest1?studentto1auser-1(?fLoc^lhostJ；!u.eryOK,0rowsaffected.(0.14sec>上圖顯示：新用戶創(chuàng)建成功權限賦予成功。1?4?2?2分別用root用戶和新定義的用戶訪問學生數(shù)據(jù)庫，并對其中的學生表數(shù)據(jù)進行修改1.登錄root用戶，進行student表的查詢操作，此處我查詢了30203學號的學生信息Mysql語句：Select*fromstudentwheresno=30203;國 C:^WINDOWS\system32\cTid.exe-nysql-ur^ot-p_n使用root用戶查詢成功。2.用root用戶修改student表的信息，此處我更改了30203學生的班級為3000Mysql語句：Updatestudentsetclassno二'3000';TOC\o"1-5"\h\zmpsql>updatestudentsetclassno=J ；QueFyOK,54rowsaffected<0-07secZ>Rowsnatclied：54Changed：54Warnincfs:6mi/sql>select