北邮大三下第5次数据库实验报告mysql

1、catherine2015/6/22实验五 数据库完整性与安全性实验一、 实验内容1.1完整性实验：（1） 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；（2） 分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；（3） 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；（4） 删除学生表中的所有数据，验证参照完整性约束；（5） 定义存储过程，完成查询某个学生的选课情况，并执行。（6） 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。（7） 用sql完成以上操作。1

2、.2安全性实验内容（1） 定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；（2） 分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；（3） 再次用此用户访问学生数据库，并对其中的学生表数据进行修改。（4） 用sql语句分别完成以上内容。二、实验要求(1) 用sql语句完成以上操作(2) 要求学生独立完成以上内容。(3) 实验完成后完成要求的实验报告内容。三、实验环境系统：windows 7软件：mysql5.6四、实验步骤及结果分析4.1完整性实验：1.1.1 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；主键约束（建立表格

3、时已经完成）：实现实体完整性约束。create table course ( cno varchar(3) not null, cname varchar(12) default null, lhour int default null, credit int default null, semester varchar(2) default null, primary key (cno);create table sc ( sno varchar(6) not null, cno varchar(3) not null, grade int default null, primary key

4、 (sno,cno);create table student ( sno varchar(6) not null, sname varchar(6) default null, sex varchar(2) default null, bdate datetime default null, dept varchar(8) default null, classno varchar(4) default null, primary key (sno);外键：实现参照完整性约束；alter table sc add constraint snoforeign key (sno)referenc

5、es student(sno);alter table sc add constraint cnoforeign key (cno)references course(cno);查看插入结果：1.1.2分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；由下图知，插入失败，失败原因：【17:36:25insert into student values('31401','郭子敬','男','1980-1-2 00:00','计算机','3146')error

6、 code: 1062. duplicate entry '31401' for key 'primary'0.015 sec】，说明主键能够标识表中每条信息的唯一性，不可以重复。1.1.3 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；删除学生表中的所有数据，验证参照完整性约束；插入数据失败，失败原因【17:43:04insert into sc values('31401','d03', null)error code: 1452. cannot add or update a child row:

7、 a foreign key constraint fails (student.sc, constraint cno foreign key (cno) references course (cno)0.109 sec】，因为课程号“d04”不存在于课程表中。1.1.4 删除学生表中的所有数据：删除失败，受参照完整性约束的影响，失败原因【17:47:25delete from studenterror code: 1175. you are using safe update mode and you tried to update a table without a where that

8、uses a key column to disable safe mode, toggle the option in preferences -> sql queries and reconnect.0.015 sec】1.1.5 定义存储过程，完成查询某个学生的选课情况，并执行。这里需要注意的是delimiter 和delimiter ;两句，delimiter是分割符的意思，因为mysql默认以""为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成sql语句进行处理，则存储过程的编译过程会报错，所以要事先用delimiter关键字申明当前段分隔符，这

9、样mysql才会将""当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。delimiter $create procedure test() beginselect student.sno,student.sname,ofrom student ,sc where student.sno=sc.sno and student.sno='31401'end $建立存储过程：运行存储过程：查看存储过程：1.1.6 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。 需要建立一个新的table，然后通过对旧table

10、的操作来触发对新table的操作，而不能通过一个表的insert触发对同一个表的操作。创建触发器：create trigger a after insert on student for each rowupdate stuset bdate=date_add(bdate,interval 1 day);注：date_add(date,interval expr type) 函数向日期添加指定的时间间隔。事件没有触发前表的状态：测试：insert into student values('31455','冯宇宁','男','1980-1-

11、2 00:00','计算机','3146');4.2安全性实验内容(1)定义一新的登陆帐号、数据库用户“zyr”，并授予其访问学生数据库的读权限；授权用户zyr访问学生数据库的读权利 。查看： (2)分别用超级用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；root用户访问student数据库:root用户修改：登陆新建数据库：新建用户zyr访问数据库：新建用户zyr修改数据库：因为用户zyr只有查询权限，没有修改权限，所以修改失败。五、实验总结（1）设置触发器后，测试时，插入失败，失败原因：【10:12:11insert in

12、to student values('31430','张明','女','1980-1-2 00:00','计算机','3146')error code: 1175. you are using safe update mode and you tried to update a table without a where that uses a key column to disable safe mode, toggle the option in preferences -> sql queries and reconnect.0.078 sec】解决方法: 进过一番搜索之后发现原来是mysql workbench的安全设置。当要执行的sql语句是进行批量更新或者删除的时候就会提示这个错误。· 打开workbench的菜单edit->preferences.· 切换到sql editor页面· 把forbid update and delete statements without a where clause (safe updates)之前的对勾去掉· 点击ok按钮· 最后一步记得要重启一下work