.實(shí)驗(yàn)五數(shù)據(jù)庫(kù)完整性與安全性實(shí)驗(yàn)實(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ù)安全性的理解實(shí)驗(yàn)內(nèi)容完整性實(shí)驗(yàn)分別定義學(xué)生數(shù)據(jù)庫(kù)中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束;分別向?qū)W生表、課程表插入具有相同學(xué)號(hào)和相同課程編號(hào)的學(xué)生數(shù)據(jù)和課程數(shù)據(jù)，驗(yàn)證其實(shí)體完整性約束；向?qū)W生選課表中插入一條數(shù)據(jù)，課程編號(hào)是課程表中沒(méi)有的，驗(yàn)證參照完整性約束；刪除學(xué)生表中的所有數(shù)據(jù)，驗(yàn)證參照完整性約束；定義存儲(chǔ)過(guò)程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行。定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試。用sql完成以上操作。安全性實(shí)驗(yàn)定義一新的登陸帳號(hào)、數(shù)據(jù)庫(kù)用戶，并授予其訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)的讀權(quán)限；分別用sa用戶和新定義的用戶訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改;再次用此用戶訪問(wèn)學(xué)生數(shù)據(jù)庫(kù)，并對(duì)其中的學(xué)生表數(shù)據(jù)進(jìn)行修改。用SQL語(yǔ)句分別完成以上內(nèi)容。實(shí)驗(yàn)環(huán)境Window8操作系統(tǒng)Mysql8.0版本數(shù)據(jù)庫(kù)Mysqlworkbench8.0可視化工具M(jìn)ysql命令行編輯器實(shí)驗(yàn)步驟及結(jié)果分析1.4.1完整性分別定義學(xué)生數(shù)據(jù)庫(kù)中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束1.首先因?yàn)樵谥皠?chuàng)建表的時(shí)候定義了主鍵，因此，我們需要先將所有表的主鍵撤銷掉，然后重新創(chuàng)建主鍵。Mysql語(yǔ)句（刪除主鍵）：1.首先因?yàn)樵谥皠?chuàng)建表的時(shí)候定義了主鍵，因此，我們需要先將所有表的主鍵撤銷掉，然后重新創(chuàng)建主鍵。Mysql語(yǔ)句（刪除主鍵）：altertablestudentdropprimarykey;altertablecoursedropprimarykey;altertablescdropprimarykey; ysql>altertwryOK,54ecords：54tablestudentdropprimaryk￡8；rowsaffectedCl_13sec?Duplicates：0Uapnings：0?/sqly?/sqlyalterucry0K-5rowsaffected<0.31sec>ecords：5Duplica_tes：0Warnin^s：0SFsql>altertablescdpoppi*imapykey：tieryOK,142powsaffectedC0.8Gsec>ecords~142Du-plicates-0Wat'ninqs：02.重新創(chuàng)建主鍵2.Mysql語(yǔ)句(創(chuàng)建主鍵)：altertablestudentaddprimarykey(sno);altertablecourseaddprimarykey(cno);altertablescaddprimarykey(sno,cno);i<jsql>altertablestudentaddprimarykep<sno^；Liei't/OK-回rowsaffectedlecords：0Duplicates：9C0-95sec>Uarnings：0i<jsql>Liei't/OK-回rowsaffectedlecords：0Duplicates：9C0-95sec>Uarnings：0i<jsql>i5fsql>altertablecourseUeryON』6rowsaffectedlecords；8Duplicates；0addprimarykey《on。)；<1-14sec>Harnings=0iijsql>altertablescaddlueryOK,陰rowsaffectediecovds：0Duplicates：0primarykey<sno,cno5；C0.83sec>Uarnings：03.到此，我們已經(jīng)重新添加了各表的主鍵，接下來(lái)我們?yōu)?.到此，我們已經(jīng)重新添加了各表的主鍵，接下來(lái)我們?yōu)镾C表添加外鍵。Mysql語(yǔ)句(創(chuàng)建外鍵)：altertablescaddconstraintforeignkey(sno)referencesstudent(sno);altertablescaddconstraintforeignkey(cno)referencescourse(cno);ysql>altertablescaddconstraintforeignkey<sno)referencesstudent<sno>；LieryOK,142rowsaffected<1.16sec>iecoi'ds：142Duplicates：0Warnings：0ysql>altertablescaddconstraintforeignkey<cno)referencescoui'se<cno>；LieryOK,142rowsaffected .35sec^>iecoi'ds：142Duplicates：0Warnings：04.主鍵和外鍵都添加完畢，我們觀察一下目前各表的結(jié)構(gòu)。Mysql語(yǔ)句（查看表定義）:showcreatetablestudent;showcreatetablecourse;showcreatetablesc;studentICREATETABLE'student1<lsnolvarchar<&>NOTHULL,coursesnamesexshowcreatetablestudent;showcreatetablecourse;showcreatetablesc;studentICREATETABLE'student1<lsnolvarchar<&>NOTHULL,coursesnamesex1varchar<&>DEFAULTNULL.varchai'<2>DEFAULTNULL.eno!CREATETABLE'courseuarchar(3>NOTNULL,'bdate1datetimeDEFAULTNULL.'dept1uarcharC10>DEFAULTNULL.'classno1uai-chai"C4>DEFAULTNULL.PRIMARYKEYC"sno">)ENGINE=InnoDBDEFAULTCHfiBSET=utf8"cnane"uapchap<12^DEFAULTNULL'Ihniw' DEFAULTNULL,Credit1 DEFAULTNULL.tsemesteF'uai?chaF<2^DEFAULTNULLPRIMARYKEVC'cno'^ENGINE=InnoDBDEFAULTCHARSET=utf8!sciCREATETABLEkc'<'sno1uarchar<6>NOTNULL,'eno1uapchapC3>NOTNULL,'grade1 DEFAULTNULL,PHIMfiUVKE￥('sno\lcno'>,KEYlcnolUcno'3CONSTRAINTlsc_ihfk_llFOREIGNKEV('sno">REFERENCES'student1('sno'CONSTRAINTlsc_ibfk_2lFOREIGNKEY('cno">REFERENCES'course1<lcnol>〉ENGINE=InnoDBDEFAULTCHfiHSET=iitf8!由上面結(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!3626130202362633626436266m「:.3626130202362633626436266m「:.p_l:.nt—bl—_

目丁目J-目-目乜1980-01-021980-01-021980-01-021980-01-021980-01-0200:00:0000：00：0000：00：0000：00：0000：00：00=H一亙一亙一亙一|口.向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（‘302037茹興康‘,‘男‘,‘1997-07-0700:00:00',‘計(jì)算機(jī)’,‘3174’）；ysql>insertintostudentualuet'30203’，‘茹興康'，'男'，'19卬7—07—0700:00:0。'.'計(jì)裁機(jī)3174，〉；RROR1062C23000>：Duplicateentry,30203,forkey'PRIMARY'上面結(jié)果顯示：添加失敗，因?yàn)閟no為student表的主鍵，不能重復(fù)，驗(yàn)證了其完整性約束。.同上，我們向course表中添加一個(gè)具有相同cno的課程。Mysql語(yǔ)句：select*fromcourse;insertintocoursevalue（'C01’,‘?dāng)?shù)據(jù)庫(kù)‘,‘50‘,’1‘,‘春’）; ysql>select*froncourse；!cno!cnane !lliour!credit!semester!!C01!C02!!C01!C02!C03!C04!C05rousinset<Q_Q0secJ5Fsql>insertintocourse丫曰lueUC盟」/數(shù)據(jù)庫(kù),/ /春,〉；RR0R1062<230Q0>=DuplicateentryJCB1Jforkey」PRIMARY」上面結(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>insertintoscvaluesCHS'10D'1；iRROR1452<23000)：Cannotaddopupdateachildrou：aforeignkeyconstraintftils<ltestll.'sc\CONSTRAINTlsc_ibfk_2lFOREIGNKEY<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;ysql>droptablestudent；JIROR1217(23000>：Cannotdeleteopupdateaparentrow：aforeignkeyconstraitfails由結(jié)果來(lái)看：由于student中的sno是sc的一個(gè)外碼，所以不能刪除student，成功驗(yàn)證了參照完整性約束。定義存儲(chǔ)過(guò)程，完成查詢某個(gè)學(xué)生的選課情況，并執(zhí)行.首先定義一個(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->beginselectsnoenofromscwhepesno=id；ERROR1064<42000>：VolihaueaneppopinyourSQLsyntax；checkthemanualthatcorrespondstoyourMySQLsei'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’)#callsearch_sc_3noCf30203>>ltTOC\o"1-5"\h\z-+ h +isnoienoi-+ h +i30203iC03ii30263iC04ii30263iC05i+ H +3rousinset<0.00sec>Query0K-陰rowsaffectedC0.01sec>定義觸發(fā)器，當(dāng)向?qū)W生表插入新的一條記錄時(shí)，將所有學(xué)生出生日期加1；并對(duì)其進(jìn)行測(cè)試.定義觸發(fā)器，當(dāng)向?qū)W生表插入新記錄時(shí)，將所有學(xué)生出生日期+1；Mysql語(yǔ)句：createtriggeradd1_bdatebeforeinsertonstudentforeachrowbeginupdatestudentsetbdate=date_add(bdate,interval1day);end#

.向?qū)W生表中插入新紀(jì)錄，注意：必須插入sno無(wú)重復(fù)的新紀(jì)錄Mysql語(yǔ)句：insertintostudentvalue（‘12345’「茹興康‘,‘男‘,‘1997-07-0700:00:00',‘計(jì)算機(jī)’,‘3174'）;ysql>insertintostudentvalue<J12345J,f fJ1997-07-0700：00：00f,Jt]-章機(jī)四RROR1442<H￥000>：CanJtupdatetableJstiident1instoredfunction/tpiggerbecaseitisalreadyusedbystatementuhichinvokedthisstoredfunctionZtrigger-但是結(jié)果顯示：在觸發(fā)器中不能更新student表的內(nèi)容。后來(lái)我在網(wǎng)上查找了一些資料：即當(dāng)對(duì)該表進(jìn)行插入操作時(shí)，由于觸發(fā)器要將每一行進(jìn)行修改，那么剛開始插入的新紀(jì)錄可能會(huì)導(dǎo)致無(wú)限制的遞歸。因此，需要復(fù)制一個(gè)新的student表，由一個(gè)表的插入引起另一個(gè)表的觸發(fā)。.創(chuàng)建一個(gè)student復(fù)制表Mysql語(yǔ)句：Mysql語(yǔ)句：createtablestudentlselect*fromstudent;4.4.重新創(chuàng)建觸發(fā)器，讓student1的插入引起student的觸發(fā)(注意，在創(chuàng)建前把之前的刪除掉)Mysql語(yǔ)句：createtriggeradd1_bdatebeforeinsertonstudent1foreachrowbeginupdatestudentsetbdate=date_add(bdate,interval1day);end#之后向student1中插入新紀(jì)錄，發(fā)現(xiàn)插入成功。mysql>createtriggeraddl_bdate>afterinsertonstudentl>fopeachrowbegin>updatestudentsetbdate=date_add<bdate,interua11day>；>endttQueryOK,0powsaffectedC0.09sec> |mysql>insertintostudentlualuc<‘12345',‘茹興康'，』男'，'1997—07—0700:00:00J,J汗算機(jī)」3174，〉*QueryOK,1rouaffected<0.24sec>

5.搜索student和studentl表的記錄，觀察變化mysql>select*fpoidstudentlK 1 1 rr -1rr■sno 11sname1!sex11hd曷七公111dept 1!c：lassno! 1 1 rr -1rr1 l:30201:30202!3S2S3：30204：30206：30207：30208春熾芨千石詈雷霏洪金廣吳袁唐吳連王蘇男男男男男男男1980-01-021980-01-021980-01-021980-01-021980-81-021980-81-021980-01-0200:00:0000:00:9000:00：0000：00：0000：00：0000：00：00=口=旦一回一回一一呈巨一一口3822 !3922 :3022 :3922 ：3022 ：3022 ：3822 ：iy菩ql》select*fr-onstud&ntlt1.4.2安全性定義一新的登陸帳號(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'auser’@’localhost’；iF￡q;L>cpeateuser1a.u.sep,localho￡t,identifled.by1072312J；Uev-iFOK,0p-owsaffected.(0.14sec>iysq;l>grantselect,updateontest1.studentto1auser-1(?fLoc^lhostJ；!u.eryOK,0rowsaffected.(0.14sec>上圖顯示：新用戶創(chuàng)建成功;-Fsnosname t sexIbdateidepticlaasno302013020230203302043020730208春熾芟

石￡.星田霏曲金廣

S-Fsnosname t sexIbdateidepticlaasno302013020230203302043020730208春熾芟

石￡.星田霏曲金廣

S品f=k國(guó)壬蘇從上兩個(gè)表的結(jié)果來(lái)看，1?80-01-031980-01-031980-01-031980-01-031980-01-031J&0-01-031780-01-0300:00:0000:00:0000:00:0000:00:0000=00=0000=00=0000=00=00亙一一口：3022!3022!3022!3022!3022!3022：3022我們成功引起了觸發(fā)，student的bdate都增多了天。分別用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-口3：\Useps\RXK>nysql-uroot-pEnterpassword：xmckm*JelcametotheM51GQLmon±tov.Commandsendwitli9or，牙■^curMyRQLconnectionidis14Seruevtiersion：8.0.11My&QLCommunitySet'uer-GPLCopyright<c>20Q0,2018,Ovacleand/oritsaffiliates.Allrightsreserued.Jracleisaregisteredtrademai'kofOi'acleCarpDi'ationand/oritsiffiliates.Othernanesmayhetrademarksoftheirrespectiyef^pefhelp；fcrfXlif￡opJieIp.Typef\cftoclearthecurrentinputstatementnysql〉usetestlDatabasechangednysfql}select*fi'omstudentwhei'e&00=-30203；isno1sname isex ?hdate idept iclassno i!30203!唐雷 !男 !1980-01-02 00:00:00!電信!3022 !Li'owinset<0.Q8sec>使用root用戶查詢成功。2.用root用戶修改student表的信息，此處我更改了30203學(xué)生的班級(jí)為3000；Mysql語(yǔ)句：Updatestudentsetclassno='3000’;mpsql>updatestudentset