数据库完整性与安全性实验
- 格式：doc
- 大小：165.07 KB
- 文档页数：10
.
. 1. 实验五 数据库完整性与安全性实验
1.1 实验目的
1. 通过对完整性规则的定义实现，熟悉了解Mysql中完整性保证的规则和实现方法，加深对数据完整性的理解。
2. 通过对安全性相关内容的定义，熟悉了解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操作系统
Mysql 8.0版本数据库
Mysql workbench 8.0可视化工具
Mysql命令行编辑器
.
. 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 key(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. 主键和外键都添加完毕，我们观察一下目前各表的结构。
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学号，由上面的结果可知，该学号已经存在。
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 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束
向学生表中添加一条数据，其中课程编号在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的一个外码，所以不能删除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. 使用存储过程查看学生选课情况。
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 row
begin update student set bdate=date_add(bdate,interval 1 day);end# .
. 2. 向学生表中插入新纪录，注意：必须插入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. 重新创建触发器，让student1的插入引起student的触发(注意，在创建前把之前的删除掉)
Mysql语句：
create trigger add1_bdate before insert on student1 for each row
begin 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用户下定义一个新的用户auser
Mysql语句：
create user ‘auser’@’localhost’ identified by ‘072312’;
2. 赋予新用户auser对student表的select和update权限
Mysql语句：
grant select,update on test1.student to ‘auser’@’localhost’;
上图显示：新用户创建成功；
权限赋予成功。
