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

实验五数据库完整性与安全性实验一、实验内容1.1完整性实验：（1）分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；（2）分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；（3）向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；（4）删除学生表中的所有数据，验证参照完整性约束；（5）定义存储过程，完成查询某个学生的选课情况，并执行。（6）定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。（7）用sql完成以上操作。1.2安全性实验内容（1）定义一新的登陆帐号、数据库用户，并授予其访问学生数据库的读权限；（2）分别用sa用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；（3）再次用此用户访问学生数据库，并对其中的学生表数据进行修改。（4）用SQL语句分别完成以上内容。二、实验要求(1)用SQL语句完成以上操作(2)要求学生独立完成以上内容。(3)实验完成后完成要求的实验报告内容。三、实验环境系统：windows7软件：mysql5.6四、实验步骤及结果分析4.1完整性实验：1.1.1分别定义学生数据库中各基表的主键、外键，实现实体完整性约束和参照完整性约束；主键约束（建立表格时已经完成）：实现实体完整性约束。CREATETABLEcourse(cnovarchar(3)NOTNULL,cnamevarchar(12)DEFAULTNULL,lhourintDEFAULTNULL,creditintDEFAULTNULL,semestervarchar(2)DEFAULTNULL,PRIMARYKEY(cno));CREATETABLEsc(snovarchar(6)NOTNULL,cnovarchar(3)NOTNULL,gradeintDEFAULTNULL,PRIMARYKEY(sno,cno));CREATETABLEstudent(snovarchar(6)NOTNULL,snamevarchar(6)DEFAULTNULL,sexvarchar(2)DEFAULTNULL,bdatedatetimeDEFAULTNULL,deptvarchar(8)DEFAULTNULL,classnovarchar(4)DEFAULTNULL,PRIMARYKEY(sno));外键：实现参照完整性约束；altertablescaddconstraintsnoforeignkey(sno)referencesstudent(sno);altertablescaddconstraintcnoforeignkey(cno)referencescourse(cno);查看插入结果：1.1.2分别向学生表、课程表插入具有相同学号和相同课程编号的学生数据和课程数据，验证其实体完整性约束；由下图知，插入失败，失败原因：【17:36:25INSERTINTOstudentVALUES('31401','郭子敬','男','1980-1-200:00','计算机','3146')ErrorCode:1062.Duplicateentry'31401'forkey'PRIMARY'0.015sec】，说明主键能够标识表中每条信息的唯一性，不可以重复。1.1.3向学生选课表中插入一条数据，课程编号是课程表中没有的，验证参照完整性约束；删除学生表中的所有数据，验证参照完整性约束；插入数据失败，失败原因【17:43:04INSERTINTOscVALUES('31401','D03',NULL)ErrorCode:1452.Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`student`.`sc`,CONSTRAINT`cno`FOREIGNKEY(`cno`)REFERENCES`course`(`cno`))0.109sec】，因为课程号“D04”不存在于课程表中。1.1.4删除学生表中的所有数据：删除失败，受参照完整性约束的影响，失败原因【17:47:25deletefromstudentErrorCode:1175.YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumnTodisablesafemode,toggletheoptioninPreferences-SQLQueriesandreconnect.0.015sec】1.1.5定义存储过程，完成查询某个学生的选课情况，并执行。这里需要注意的是DELIMITER和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以;为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将;当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。delimiter$$createproceduretest()beginselectstudent.sno,student.sname,sc.cnofromstudent,scwherestudent.sno=sc.snoandstudent.sno='31401';end$$建立存储过程：运行存储过程：查看存储过程：1.1.6定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。需要建立一个新的table，然后通过对旧table的操作来触发对新table的操作，而不能通过一个表的insert触发对同一个表的操作。创建触发器：createtriggerAafterinsertonstudentforeachrowupdatestusetbdate=date_add(bdate,interval1day);注：DATE_ADD(date,INTERVALexprtype)函数向日期添加指定的时间间隔。事件没有触发前表的状态：测试：INSERTINTOstudentVALUES('31455','冯宇宁','男','1980-1-200:00','计算机','3146');4.2安全性实验内容(1)定义一新的登陆帐号、数据库用户“zyr”，，并授予其访问学生数据库的读权限；授权用户zyr访问学生数据库的读权利。查看：(2)分别用超级用户和新定义的用户访问学生数据库，并对其中的学生表数据进行修改；Root用户访问student数据库:Root用户修改：登陆新建数据库：新建用户zyr访问数据库：新建用户zyr修改数据库：因为用户‘zyr’只有查询权限，没有修改权限，所以修改失败。五、实验总结（1）设置触发器后，测试时，插入失败，失败原因：【10:12:11insertintostudentvalues('31430','张明','女','1980-1-200:00','计算机','3146')ErrorCode:1175.YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumnTodisablesafemode,toggletheoptioninPreferences-SQLQueriesandreconnect.0.078sec】解决方法:进过一番搜索之后发现原来是MySQLWorkbench的安全设置。当要执行的SQL语句是进行批量更新或者删除的时候就会提示这个错误。打开Workbench的菜单[Edit]-[Preferences...]切换到[SQLEditor]页面把[ForbidUPDATEandDELETEstatementswithoutaWHEREclause(safeupdates)]之前的对勾去掉点击[OK]按钮最后一步记得要重启一下Workbench，否则你仍然会得到这个错误提示（2）删除新建用户：@%表示对所有非本地主机授权，不包括localhost。删除账户及权限：dropuser用户名@'%';dropuser用户名@localhost;（3）本次试验遇到了很多问题，一是我对mysql了解不够，二是mysql的某些语句比较奇特。好好学习sql吧。