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

1.1實(shí)驗(yàn)?zāi)康耐ㄟ^(guò)對(duì)完整性規(guī)則的定義實(shí)現(xiàn)，熟悉了解Mysql中完整性保證的規(guī)則和實(shí)現(xiàn)方法，加深對(duì)數(shù)據(jù)完整性的理解。通過(guò)對(duì)安全性相關(guān)內(nèi)容的定義，熟悉了解Mysql中安全性的內(nèi)容和實(shí)現(xiàn)方法，加深對(duì)數(shù)據(jù)庫(kù)安全性的理解1.2實(shí)驗(yàn)內(nèi)容1.2.1完整性實(shí)驗(yàn)（1） 分別定義學(xué)生數(shù)據(jù)庫(kù)中各基表的主鍵、外鍵，實(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)是課程表中沒(méi)有的，驗(yàn)證參照完整性約束；（4） 刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束；（5） 定義存儲(chǔ)過(guò)程，完成查詢某個(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ù)庫(kù)用戶，并授予其訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)的讀權(quán)限；（2）分別用sa用戶和新定義的用戶訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改;3） 再次用此用戶訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改。4） 用SQL語(yǔ)句分別完成以上內(nèi)容。1.3實(shí)驗(yàn)環(huán)境Window8操作系統(tǒng)Mysql8.0版本數(shù)據(jù)庫(kù)Mysqlworkbench8.0可視化工具M(jìn)ysql命令行編輯器1.4實(shí)驗(yàn)步驟及結(jié)果分析1.4.1完整性分別定義學(xué)生數(shù)據(jù)庫(kù)中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束Mysql語(yǔ)句(刪除主鍵)：altertablestudentdropprimarykey;altertablecoursedropprimarykey;Mysql語(yǔ)句(刪除主鍵)：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語(yǔ)句(創(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)重新添加了各表的主鍵，接下來(lái)我們?yōu)镾C表添加外鍵。3.到此，我們已經(jīng)重新添加了各表的主鍵，接下來(lái)我們?yōu)镾C表添加外鍵。Mysql語(yǔ)句(創(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.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結(jié)構(gòu)。Mysql4.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結(jié)構(gòu)。Mysql語(yǔ)句（查看表定義）：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語(yǔ)言：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語(yǔ)句（添加一個(gè)新的學(xué)號(hào)為30203的學(xué)生）：insertintostudentvalue（‘30203','茹興康男','1997-07-0700:00:00','計(jì)算機(jī)','3174'）;ysql>insertintostudentualue<f30203f,J ,J1997-07-0700:00:00J,Jy|-辜機(jī)J,J3174f>；RROR1062C23000>：DuplicateEntpy,30203,forkevJPRIMAn￥,上面結(jié)果顯示：添加失敗，因?yàn)閟no為student表的主鍵，不能重復(fù)，驗(yàn)證了其完整性約束。3.同上，我們向course表中添加一個(gè)具有相同eno的課程。Mysql語(yǔ)句：select*fromcourse;i ysql>select*froncourse；!eno!cnane !lliour!credit!semester!!C01!C02!C03!!C01!C02!C03!C04!C0551!51!51!40!30!JfiUfi程序謨計(jì)!計(jì)算機(jī)組成原理!rousinset<Q_Q0secJ5Fsql>insertintocoursevalue<JCB1JJ數(shù)據(jù)庫(kù)JJ5BJJ1JJ春」〉；RROR1062<230Q0>=Duplicateentr^JCB1Jfor心號(hào)JPEIMAEVJ上面結(jié)果顯示：成功驗(yàn)證了完成性約束。向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號(hào)是課程表中沒(méi)有的，驗(yàn)證參照完整性約束向?qū)W生表中添加一條數(shù)據(jù)，其中課程編號(hào)在course中不存在，我選擇了C06號(hào)。Mysql語(yǔ)句（向?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é)果來(lái)看：插入失敗，因?yàn)閏ourse的cno是SC的外碼，C06在course中不存在，由于參照完整性約束，所以添加失敗，驗(yàn)證成功。刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束刪除學(xué)生表，驗(yàn)證參照完整性約束。Mysql語(yǔ)句：droptablestudent;iysql>droptablestudent；TIROR1217 :Cannotdeleteopupdateaparentrou:aforeignkeyconstraitfails由結(jié)果來(lái)看：由于student中的sno是se的一個(gè)外碼，所以不能刪除student，成功驗(yàn)證了參照完整性約束。

定義存儲(chǔ)過(guò)程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行1.首先定義一個(gè)根據(jù)學(xué)生學(xué)號(hào)搜索的存儲(chǔ)過(guò)程。但是由于‘；'是一個(gè)結(jié)束標(biāo)志，因此存儲(chǔ)過(guò)程定義失敗，因?yàn)榈谝粋€(gè)遇到語(yǔ)句的‘；時(shí)候，該語(yǔ)句還沒(méi)結(jié)束導(dǎo)致‘；后面的語(yǔ)句沒(méi)有加工mysql>createproceduresearch_sc_sno<idcliar<6>>->beginselectsno□.enofromscwhepesno=id；ERROR1064<42000>:VouhaveaneppopinyourSQLsyntax；checkthemanualthatcorrespondstoyourMvSQLsei'uei*uersion￡optherightsyntaxtouseneap11atline2解決上面問(wèn)題，將結(jié)束符號(hào)更換為其他字符，我替換為了#，使用delimiter語(yǔ)句Mysql語(yǔ)句：delimiter#createproceduresearch_sc_sno(idchar(6))beginselectsno,cnofromscwheresno=id;end#iysql>delimiterItiysql>createprocedureaearch_sc_snoCidcharC6>>->beginselectsno^cnofromscwhepesna=id；->endttlueryOH,0fousaffected(0.20sec>存儲(chǔ)過(guò)程定義成功。使用存儲(chǔ)過(guò)程查看學(xué)生選課情況。Mysql語(yǔ)句(查詢學(xué)號(hào)為30203的學(xué)生選課情況)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ā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1.4.1.6定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試1.定義觸發(fā)器，當(dāng)向?qū)W生表插入新記錄時(shí)，將所有學(xué)生出生日期+1；Mysql語(yǔ)句：createtriggeradd1_bdatebeforeinsertonstudentforeachrowbe iyeql>epeatetviggepaddl_bdate—>beforeinset?七onstudent->foreachrowbegin->updatestudentsetbdate=date_add<bdate,interval1day)；->endttMeryOK,0rowsaffected<0.12sec>

2.向?qū)W生表中插入新紀(jì)錄，注意：必須插入sno無(wú)重復(fù)的新紀(jì)錄Mysql語(yǔ)句：insertintostudentvalue（T2345','茹興康男','1997-07-0700:00:00','計(jì)算機(jī)','3174'）;ysql>insertintostudentvalue<J123452.向?qū)W生表中插入新紀(jì)錄，注意：必須插入sno無(wú)重復(fù)的新紀(jì)錄Mysql語(yǔ)句：insertintostudentvalue（T2345','茹興康男','1997-07-0700:00:00','計(jì)算機(jī)','3174'）;韋機(jī)J^3174J>#RROR1442<H￥000>:CanJtupdatetableJstiident1instoredfunction/tpiggerbecaseitisalreadyusedbystatementuhichinvokedthisstoredfunctionZtrigger-但是結(jié)果顯示：在觸發(fā)器中不能更新student表的內(nèi)容。后來(lái)我在網(wǎng)上查找了一些資料：即當(dāng)對(duì)該表進(jìn)行插入操作時(shí)，由于觸發(fā)器要將每一行進(jìn)行修改，那么剛開(kāi)始插入的新紀(jì)錄可能會(huì)導(dǎo)致無(wú)限制的遞歸。因此，需要復(fù)制一個(gè)新的student表，由一個(gè)表的插入引起另一個(gè)表的觸發(fā)。3.創(chuàng)建一個(gè)student復(fù)制表Mysql語(yǔ)句：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語(yǔ)句：createtriggeradd1_bdatebeforeinsertonstudent1foreachrowbeginupdatestudentsetbdate=date_add(bdate,interval1day);end#之后向student1之后向student1中插入新紀(jì)錄，發(fā)現(xiàn)插入成功。->afterinsertonstudentl->foreachrowbegin->updatestudentsetbdate=date_add<bdate,interua11day〉；->endttQiievyOK,0powsaffectedC0.09sec> |mysql>insertinto就udentlualT'1234『「茹興康'「男'「1.站即-即00:00:00J,J訐算機(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從上兩個(gè)表的結(jié)果來(lái)看，我們成功引起了觸發(fā)，student的bdate都增多了一天。1.4.2安全性1.4.2.1定義一新的登陸帳號(hào)、數(shù)據(jù)庫(kù)用戶，并授予其訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)的讀權(quán)限在root用戶下定義一個(gè)新的用戶auserMysql語(yǔ)句：createuser‘a(chǎn)user'@'localhost'identifiedby‘072312';賦予新用戶auser對(duì)student表的select和update權(quán)限Mysql語(yǔ)句：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)建成功權(quán)限賦予成功。1?4?2?2分別用root用戶和新定義的用戶訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改1.登錄root用戶，進(jìn)行student表的查詢操作，此處我查詢了30203學(xué)號(hào)的學(xué)生信息Mysql語(yǔ)句：Select*fromstudentwheresno=30203;國(guó) C:^WINDOWS\system32\cTid.exe-nysql-ur^ot-p_n使用root用戶查詢成功。2.用root用戶修改student表的信息，此處我更改了30203學(xué)生的班級(jí)為3000Mysql語(yǔ)句：Updatestudentsetclassno二'3000';TOC\o"1-5"\h\zmpsql>updatestudentsetclassno=J ；QueFyOK,54rowsaffected<0-07secZ>Rowsnatclied：54Changed：54Warnincfs:6mi/sql>select