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

实验五 数据库完整性及安全性实验
一、 实验内容
1.1完整性实验：
（1） 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；
（2） 分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；
（3） 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；
（4） 删除学生表中的所有数据，验证参照完整性约束；
（5） 定义存储过程，完成查询某个学生的选课情况，并执行。
（6） 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。
（7） 用sql完成以上操作
1.2安全性实验内容
（1） 定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；
（2） 分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；
（3） 再次用此用户访问学生数据库，并对其中的学生表数据进行修改。
（4） 用SQL语句分别完成以上内容。
二、实验要求
(1) 用SQL语句完成以上操作
(2) 要求学生独立完成以上内容。
(3) 实验完成后完成要求的实验报告内容。
三、实验环境
系统：windows 7
软件：mysql5.6
四、实验步骤及结果分析
4.1完整性实验：
1.1.1 分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；
主键约束（建立表格时已经完成）：实现实体完整性约束。
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 (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 sno
foreign key (sno)
references student(sno);
alter table sc add constraint cno
foreign key (cno)
references course(cno);
查看插入结果：
1.1.2分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；
由下图知，插入失败，失败原因：【17:36:25 INSERT INTO student VALUES('31401','郭子敬','男','1980-1-2 00:00','计算机','3146') Error Code: 1062. Duplicate entry '31401' for key 'PRIMARY' 0.015 sec】，说明主键能够标识表中每条信息的唯一性，不可以重复。
1.1.3 向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；
删除学生表中的所有数据，验证参照完整性约束；
插入数据失败，失败原因【17:43:04 INSERT INTO sc VALUES('31401','D03', NULL) Error Code 1452. Cannot add or update a child row: a foreign key constraint fails (`student`.`sc`, CONSTRAINT `cno` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)) 0.109 sec】，因为课程号“D04”不存在于课程表中。
1.1.4 删除学生表中的所有数据：
删除失败，受参照完整性约束的影响，失败原因【17:47:25 delete from student 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.015 sec】
1.1.5 定义存储过程，完成查询某个学生的选课情况，并执行。
这里需要注意的是DELIMITER 和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
delimiter $$
create procedure test()
begin
select student.sno,student.sname,sco
from student ,sc
where student.sno=sc.sno and student.sno='31401';
end $$
建立存储过程：
运行存储过程：
查看存储过程：
1.1.6 定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。
需要建立一个新的table，然后通过对旧table的操作来触发对新table的操作，而不能通过一个表的insert触发对同一个表的操作。
创建触发器：
create trigger A after insert on student
for each row
update stu
set bdate=date_add(bdate,interval 1 day);
注：DATE_ADD(date,INTERVAL expr type) 函数向日期添加指定的时间间隔。
事件没有触发前表的状态：
测试：
INSERT INTO student VALUES('31455','冯宇宁','男','1980-1-2 00:00','计算机','3146');
4.2安全性实验内容
(1)定义一新的登陆帐号、数据库用户“zyr”，，并授予其访问学生数据库的读权限；
授权用户zyr访问学生数据库的读权利 。
查看：
(2)分别用超级用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；
Root用户访问student数据库:
Root用户修改：
登陆新建数据库：
新建用户zyr访问数据库：
新建用户zyr修改数据库：
因为用户‘zyr’只有查询权限，没有修改权限，所以修改失败。
五、实验总结
（1）设置触发器后，测试时，插入失败，失败原因：【10:12:11 insert into 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]按钮
· 最后一步记得要重启一下Workbench，否则你仍然会得到这个错误提示
（2）删除新建用户：
@"%" 表示对所有非本地主机授权，不包括localhost。
删除账户及权限：>drop user 用户名@'%';
　　　　　　　　>drop user 用户名@ localhost; 
（3）本次试验遇到了很多问题，一是我对mysql了解不够，二是mysql的某些语句比较奇特。好好学习sql吧。
第 8 页