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

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

2、。1.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 K

4、EY (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)refere

5、nces student(sno);alter table sc add constraint cnoforeign 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

6、 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 cour

7、se (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

8、 reconnect. 0.015 sec】1.1.5 定义存储过程，完成查询某个学生的选课情况，并执行。这里需要注意的是DELIMITER 和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以;为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将;当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。delimiter $create procedure test() beginselect student.sno,st

9、udent.sname,ofrom 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 rowupdate stuset bdate=da

10、te_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用户修改：

11、登陆新建数据库：新建用户zyr访问数据库：新建用户zyr修改数据库：因为用户zyr只有查询权限，没有修改权限，所以修改失败。五、实验总结（1）设置触发器后，测试时，插入失败，失败原因：【10:12:11 insert into student values(31430,张明,女,1980-1-200: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

12、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吧。