数据库完整性与安全性实验

1.1实验目的通过对完整性规则的定义实现，熟悉了解Mysql中完整性保证的规则和实现方法，加深对数据完整性的理解。通过对安全性相关内容的定义，熟悉了解Mysql中安全性的内容和实现方法，加深对数据库安全性的理解1.2实验内容1.2.1完整性实验（1） 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；（2） 分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；（3） 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；（4） 删除学生表中的所有数据，验证参照完整性约束；（5） 定义存储过程，完成查询某个学生的选课情况，并执行。（6） 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。（7） 用sql完成以上操作。1.2.2安全性实验1）定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；（2）分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改;3） 再次用此用户访问学生数据库，并对其中的学生表数据进行修改。4） 用SQL语句分别完成以上内容。1.3实验环境Window8操作系统Mysql8.0版本数据库Mysqlworkbench8.0可视化工具Mysql命令行编辑器1.4实验步骤及结果分析1.4.1完整性分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束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.重新创建主键2.Mysql语句(创建主键)：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.到此，我们已经重新添加了各表的主键，接下来我们为SC表添加外键。3.到此，我们已经重新添加了各表的主键，接下来我们为SC表添加外键。Mysql语句(创建外键)：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;由上面结果可知：我们成功创建了各表的主键，并成功为SC表创建了sno，cno两个外键。分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束首先查找一下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学号，由上面的结果可知，该学号已经存在。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数据库JJ5BJJ1JJ春」〉；RROR1062<230Q0>=Duplicateentr^JCB1Jfor心号JPEIMAEVJ上面结果显示：成功验证了完成性约束。向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束向学生表中添加一条数据，其中课程编号在course中不存在，我选择了C06号。Mysql语句（向学号为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中不存在，由于参照完整性约束，所以添加失败，验证成功。删除学生表中的所有数据，验证参照完整性约束删除学生表，验证参照完整性约束。Mysql语句：droptablestudent;iysql>droptablestudent；TIROR1217 :Cannotdeleteopupdateaparentrou:aforeignkeyconstraitfails由结果来看：由于student中的sno是se的一个外码，所以不能删除student，成功验证了参照完整性约束。

定义存储过程，完成查询某个学生的选课情况，并执行1.首先定义一个根据学生学号搜索的存储过程。但是由于‘；'是一个结束标志，因此存储过程定义失败，因为第一个遇到语句的‘；时候，该语句还没结束导致‘；后面的语句没有加工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定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1.4.1.6定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试1.定义触发器，当向学生表插入新记录时，将所有学生出生日期+1；Mysql语句：createtriggeradd1_bdatebeforeinsertonstudentforeachrowbe iyeql>epeatetviggepaddl_bdate—>beforeinset?七onstudent->foreachrowbegin->updatestudentsetbdate=date_add<bdate,interval1day)；->endttMeryOK,0rowsaffected<0.12sec>

2.向学生表中插入新纪录，注意：必须插入sno无重复的新纪录Mysql语句：insertintostudentvalue（T2345','茹兴康男','1997-07-0700:00:00','计算机','3174'）;ysql>insertintostudentvalue<J123452.向学生表中插入新纪录，注意：必须插入sno无重复的新纪录Mysql语句：insertintostudentvalue（T2345','茹兴康男','1997-07-0700:00:00','计算机','3174'）;韋机J^3174J>#RROR1442<H¥000>:CanJtupdatetableJstiident1instoredfunction/tpiggerbecaseitisalreadyusedbystatementuhichinvokedthisstoredfunctionZtrigger-但是结果显示：在触发器中不能更新student表的内容。后来我在网上查找了一些资料：即当对该表进行插入操作时，由于触发器要将每一行进行修改，那么刚开始插入的新纪录可能会导致无限制的递归。因此，需要复制一个新的student表，由一个表的插入引起另一个表的触发。3.创建一个student复制表Mysql语句：createtablestudentlselect*fromstudent;iyscreatetablew七udentlselect*fromstuden七；!u.eryOK,54rowsaffected<0_66sec^!ecov-ds：:54Duplicates:3Uapnin£f£:B4.重新创建触发器，让student1的插入引起student的触发(注意，在创建前把之前的删除掉)Mysql语句：createtriggeradd1_bdatebeforeinsertonstudent1foreachrowbeginupdatestudentsetbdate=date_add(bdate,interval1day);end#之后向student1之后向student1中插入新纪录，发现插入成功。->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从上两个表的结果来看，我们成功引起了触发，student的bdate都增多了一天。1.4.2安全性1.4.2.1定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限在root用户下定义一个新的用户auserMysql语句：createuser‘auser'@'localhost'identifiedby‘072312';赋予新用户auser对student表的select和update权限Mysql语句：grantselect,updateontest1.studentto‘auser'@'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>上图显示：新用户创建成功权限赋予成功。1・4・2・2分别用root用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改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