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

1、数据库完整性与安全性实验1. 实验五 数据库完整性与安全性实验1.1实验目的1.通过对完整性规则的定义实现，熟悉了解Mysql中完整性保证的规则和实现方法，加深对数据完整性的理解。2.通过对安全性相关内容的定义，熟悉了解Mysql中安全性的内容和实现方法，加深对数据库安全性的理解1.2实验内容1.2.1完整性实验（1）分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；（2）分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；（3）向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；（4）删除学生表中的所有

2、数据，验证参照完整性约束；（5）定义存储过程，完成查询某个学生的选课情况，并执行。（6）定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。（7）用sql完成以上操作。1.2.2安全性实验（1）定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；（2）分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；（3）再次用此用户访问学生数据库，并对其中的学生表数据进行修改。（4）用SQL语句分别完成以上内容。1.3实验环境Window8操作系统Mysql 版本数据库Mysql workbench 可视化工具Mysql命令行编辑器1.4实验

3、步骤及结果分析1.4.1完整性1.4.1.1分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束1.首先因为在之前创建表的时候定义了主键，因此，我们需要先将所有表的主键撤销掉，然后重新创建主键。Mysql语句（删除主键）：alter table student drop primary key;alter table course drop primary key;alter table sc drop primary key;2.重新创建主键Mysql语句（创建主键）：alter table student add primary key(sno);alter tabl

4、e course add primary key(cno);alter table sc add primary key(sno,cno);3.到此，我们已经重新添加了各表的主键，接下来我们为SC表添加外键。Mysql语句（创建外键）：alter table sc add constraint foreign key(sno) references student(sno);alter table sc add constraint foreign key(cno) references course(cno);4.主键和外键都添加完毕，我们观察一下目前各表的结构。Mysql语句（查看表定义）

5、：show create table student;show create table course;show create table sc; 由上面结果可知：我们成功创建了各表的主键，并成功为SC表创建了sno，cno两个外键。1.4.1.2分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束1.首先查找一下student表中的学生信息，以便下面添加重复学号的学生Mysql语言： select * from student;2.向student表中插入一个重复学号的学生，验证完整性约束。我选择了*学号，由上面的结果可知，该学号已经存在。Mysql语

6、句（添加一个新的学号为*的学生）：insert into student value(30203,茹兴康,男,1997-07-07 00:00:00,计算机,3174); 上面结果显示：添加失败，因为sno为student表的主键，不能重复，验证了其完整性约束。3.同上，我们向course表中添加一个具有相同cno的课程。Mysql语句：select *from course;insert into course value(C01,数据库,50,1,春);上面结果显示：成功验证了完成性约束。1.4.1.3向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束向学生表中添加一

7、条数据，其中课程编号在course中不存在，我选择了C06号。Mysql语句（向学号为*的学生选课中添加C06选课）：insert into sc values(30203,C06,100); 由结果来看：插入失败，因为course的cno是SC的外码，C06在course中不存在，由于参照完整性约束，所以添加失败，验证成功。1.4.1.4删除学生表中的所有数据，验证参照完整性约束删除学生表，验证参照完整性约束。Mysql语句：drop table student; 由结果来看：由于student中的sno是sc的一个外码，所以不能删除student，成功验证了参照完整性约束。1.4.1.5定

8、义存储过程，完成查询某个学生的选课情况，并执行1.首先定义一个根据学生学号搜索的存储过程。但是由于；是一个结束标志，因此存储过程定义失败，因为第一个遇到语句的；时候，该语句还没结束导致；后面的语句没有加工2.解决上面问题，将结束符号更换为其他字符，我替换为了#，使用delimiter语句Mysql语句：delimiter #create procedure search_sc_sno(id char (6)begin select sno,cno from sc where sno = id;end#存储过程定义成功。3.使用存储过程查看学生选课情况。Mysql语句（查询学号为*的学生选课情况

9、）：call search_sc_sno(30203)#1.4.1.6定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试1.定义触发器，当向学生表插入新记录时，将所有学生出生日期+1；Mysql语句：create trigger add1_bdate before insert on student for each rowbegin update student set bdate=date_add(bdate,interval 1 day);end#2.向学生表中插入新纪录，注意：必须插入sno无重复的新纪录Mysql语句：insert into studen

10、t value(12345,茹兴康,男,1997-07-07 00:00:00,计算机,3174);但是结果显示：在触发器中不能更新student表的内容。后来我在网上查找了一些资料：即当对该表进行插入操作时，由于触发器要将每一行进行修改，那么刚开始插入的新纪录可能会导致无限制的递归。因此，需要复制一个新的student表，由一个表的插入引起另一个表的触发。3.创建一个student复制表Mysql语句： create table student1 select * from student;4.重新创建触发器，让student1的插入引起student的触发(注意，在创建前把之前的删除掉)M

11、ysql语句：create trigger add1_bdate before insert on student1 for each rowbegin update student set bdate=date_add(bdate,interval 1 day);end#之后向student1中插入新纪录，发现插入成功。5.搜索student和student1表的记录，观察变化 从上两个表的结果来看，我们成功引起了触发，student的bdate都增多了一天。1.4.2安全性1.4.2.1定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限1.在root用户下定义一个新的用户aus

12、erMysql语句：create user auserlocalhost identified by 072312;2.赋予新用户auser对student表的select和update权限Mysql语句：grant select,update on to auserlocalhost; 上图显示：新用户创建成功； 权限赋予成功。1.4.2.2分别用root用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改1.登录root用户，进行student表的查询操作，此处我查询了*学号的学生信息Mysql语句：Select * from student where sno =30203;使

13、用root用户查询成功。2.用root用户修改student表的信息，此处我更改了30203学生的班级为3000；Mysql语句：Update student set classno=3000;使用root用户修改成功。3.登录新创建的auser用户，进行与root用户相同的操作。Mysql语句：Select * from student where sno =30203;Update student set classno=3022; 使用auser新用户查询student成功；使用auser新用户修改student成功。1.5实验总结1.5.1实验遇到的问题1.问题：在添加外键的时候发生错

14、误：不能添加外键解决：a. student的主键sno定义要和sc的外键sno定义需要完全一样，长度也要一样 b.两个数据表定义时的内码也要一样，之前因为student表的采用的是utf-8码，而sc表采用的是latin码，产生错误，在workbench中将sc码改变为utf-8，解决了问题。2.问题：在定义存储过程中，遇到第一个；便结束了语句，但实际上语句还没有结束。解决：使用delimiter语句，将结束符号改为其他符号，比如#，这样就解决了判定问题。3.问题：定义触发器遇到问题，在student中定义触发器，但是不能再student中进行触发操作解决：创建一个student的复制表，在新表中定义触发操作，触发改变另一个表的值。4.问题：在创建新的用户，为其赋予权限过程中遇到问题。遇到赋予权限失败。解决：语句使用错误，在赋予权限时必须制定host是什么，要写明userhost；1.5.2收获经过本次实验，首先让我对完整性约束有了更深的了解。同时，也对定义数据库表的内码进行了了解，使用不同的码表可能会导致很多错误，例如utf-8和latin的之间无法识别。让我印象最深刻的是，有关于数据表的了解，在实验过程中，我先后查阅了表内容，更改权限等操作，让我对mysql的user有了更深一步的了解。