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

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

《北邮大三下第5次数据库实验报告mysql.docx》由会员分享，可在线阅读，更多相关《北邮大三下第5次数据库实验报告mysql.docx（13页珍藏版）》请在冰豆网上搜索。

北邮大三下第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）

）;

外键：

实现参照完整性约束；

altertablescaddconstraintsno

foreignkey（sno）

referencesstudent（sno）;

altertablescaddconstraintcno

foreignkey（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（）

begin

selectstudent.sno,student.sname,o

fromstudent,sc

wherestudent.sno=sc.snoandstudent.sno='31401';

end$$

建立存储过程：

运行存储过程：

查看存储过程：

1.1.6定义触发器，当向学生表插入新的一条记录时，将所有学生出生日期加1；并对其进行测试。

需要建立一个新的table，然后通过对旧table的操作来触发对新table的操作，而不能通过一个表的insert触发对同一个表的操作。

创建触发器：

createtriggerAafterinsertonstudent

foreachrow

updatestu

setbdate=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-2 00:

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