数据库完整性与安全性实验(仅供参照)

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 valu e(‘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’)#。