《数据库完整性与安全性实验.doc》由会员分享,可在线阅读,更多相关《数据库完整性与安全性实验.doc(10页珍藏版)》请在金锄头文库上搜索。
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 8.0版本数据库Mysql workbench 8.0可视化工具Mysq
3、l命令行编辑器1.4 实验步骤及结果分析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 ke
4、y(sno);alter table 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. 主键和外键都添加完毕,我们观察一下目前各
5、表的结构。Mysql语句(查看表定义):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表中插入一个重复学号的学生,验证完整性约束。我选择了30203学
6、号,由上面的结果可知,该学号已经存在。Mysql语句(添加一个新的学号为30203的学生):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语句(向学号为30203的学生选课中添加C06选课):insert into sc values(30203,C06,100);由结果来看:插入失败,因为course的cno是SC的外码,C06在course中不存在,由于参照完整性约束,所以添加失败,验证成功。1.4.1.4 删除学生表中的所有数据,验证参照完整性约束删除学生表,验证参照完整性约束。Mysql语句:drop table student;由结果来看:由于student中的sno是sc的一个外码,所以
8、不能删除student,成功验证了参照完整性约束。1.4.1.5 定义存储过程,完成查询某个学生的选课情况,并执行1. 首先定义一个根据学生学号搜索的存储过程。但是由于;是一个结束标志,因此存储过程定义失败,因为第一个遇到语句的;时候,该语句还没结束导致;后面的语句没有加工2. 解决上面问题,将结束符号更换为其他字符,我替换为了#,使用delimiter语句Mysql语句:delimiter #create procedure search_sc_sno(id char (6)begin select sno,cno from sc where sno = id;end#存储过程定义成功。3.
9、 使用存储过程查看学生选课情况。Mysql语句(查询学号为30203的学生选课情况):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. 向学生表中插入新纪录
10、,注意:必须插入sno无重复的新纪录Mysql语句:insert into student value(12345,茹兴康,男,1997-07-07 00:00:00,计算机,3174);但是结果显示:在触发器中不能更新student表的内容。后来我在网上查找了一些资料:即当对该表进行插入操作时,由于触发器要将每一行进行修改,那么刚开始插入的新纪录可能会导致无限制的递归。因此,需要复制一个新的student表,由一个表的插入引起另一个表的触发。3. 创建一个student复制表Mysql语句:create table student1 select * from student;4. 重新创建
11、触发器,让student1的插入引起student的触发(注意,在创建前把之前的删除掉)Mysql语句: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 定义一新的
12、登陆帐号、数据库用户,并授予其访问学生数据库的读权限1. 在root用户下定义一个新的用户auserMysql语句:create user auserlocalhost identified by 072312;2. 赋予新用户auser对student表的select和update权限Mysql语句:grant select,update on test1.student to auserlocalhost;上图显示:新用户创建成功; 权限赋予成功。1.4.2.2 分别用root用户和新定义的用户访问学生数据库,并对其中的学生表数据进行修改1. 登录root用户,进行student表的查询操
13、作,此处我查询了30203学号的学生信息Mysql语句:Select * from student where sno =30203;使用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新用户
14、查询student成功;使用auser新用户修改student成功。1.5 实验总结1.5.1 实验遇到的问题1. 问题:在添加外键的时候发生错误:不能添加外键解决: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 收获经过本次实验,首先让我对完整性约束有了更深的了解。同时,也对定义数据库表的内码进行了了解,