一. 实验内容、步骤以及结果
1. 利用图形用户界面对实验一中所创建的Student库中,增加以下的约束和索引,并验证违反相关完整性时的数据库系统的违约处理。(10分)
略
2. 利用图形用户界面针对实验一中所创建的Student库,给SC表添加外键约束,将sno,cno设置为外键,其引用表分别是S表和C表,外键名分别为:sc_fk_sno和sc_fk_cno。尝试不同的违反外键约束的策略并进行验证NO Action/restrict/cascade/set null。(10分)
1) cascade(级联):
在父表上update/delete记录时,同步update/delete掉子表的匹配记录
2) set null:
在父表上update/delete记录时,将子表上匹配记录的列设为null(要注意子表的外键列不能为not null)
3) No action:
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
4) Restrict:
同no action,,都是立即检查外键约束
3. 删除student库中的三张表,用SQL语言重新创建有以下约束的表。(10分)
1) S表:基本约束要求同第1小题。
CREATE TABLE s (Sno INT ,Sname CHAR (20) unique,Sgender CHAR (5)check (sgender in ('男','女')) default '男',Birth CHAR (20) not null,Sdept CHAR (5),PRIMARY KEY (Sno));
2) C表:cpno是c表的外键,引用自C表的cno。
CREATE TABLE c (Cno INT ,Cname CHAR (20),Cpno INT,Credit INT,PRIMARY KEY (Cno),FOREIGN KEY (Cpno) REFERENCES c(Cno));
3) SC表:外键约束如第2小题,同时要求成绩grade的有效值为[0,100]。另外,SC表增加新列id:将id设为主键,且自增字段,每次插入一条选课记录自动+1。
CREATE TABLE sc (id int not null auto_increment,Sno INT ,Cno INT ,Grade INTcheck (grade between 0 and 100),PRIMARY KEY (id),FOREIGN KEY (Sno) REFERENCES s(Sno),FOREIGN KEY (Cno) REFERENCES c(Cno));
4. 用SQL语言添加或者删除以下的完整性约束。(10分)
1) 增加约束:S表中的sgender取值只能是“男”或“女”。
alter table s add constraint c2 check(sgender in ('男','女'));
2) 删除S表中所创建的sgender取值约束。
alter table s drop constraint c2;
3) 删除SC表的外键约束。
alter table sc drop foreign key sc_ibfk_1;alter table sc drop foreign key sc_ibfk_2;
4) 学生表S中增加新列tname(表示该学生的导师姓名),且要求导师名必须全部是大写或者小写字母、且长度不能低于8个字符(提示:可以使用char_length()函数和正则表达式)。
alter table s add tname char(20);alter table s add constraint c1check (char_length(tname) >= 8 and tname like '^[A-Za-z]+$');
授权部分的实验,涉及创建用户等操作,请连接本地MySQL服务器完成。
5. 在图形用户界面中创建新用户并进行赋权。(20分)
略
6. 用SQL语句授权和收回权限并对权限加以验证。(40分)
要求:创建相关用户和指定的数据库表,针对每个小题先完成授权并进行权限验证,然后再收回权限并进行权限验证。
1) 用户王明对两个表有查询权限。
create user 'wangming'@'localhost';grant select on table apartment to 'wangming'@'localhost';grant select on table employee to 'wangming'@'localhost';revoke select on table apartment from 'wangming'@'localhost';revoke select on table employee from 'wangming'@'localhost';
2) 用户李勇对两个表有insert和delete权限。
create user 'liyong'@'localhost';grant insert,delete on table apartment to 'liyong'@'localhost';grant insert,delete on table employee to 'liyong'@'localhost';revoke insert,delete on table apartment from 'liyong'@'localhost';revoke insert,delete on table employee from 'liyong'@'localhost';
3) 每个职工只对自己的记录有查询权限。
create view view_empoyee as select * from employee where concat(name,'@localhost') = user();grant select on view_empoyee to 'wangming'@'localhost' , 'liyong'@'localhost' , 'liuxing'@'localhost' , 'zhangxin'@'localhost' , 'zhouping'@'localhost' , 'yanglan'@'localhost';revoke all on view_empoyee from 'wangming'@'localhost' , 'liyong'@'localhost' , 'liuxing'@'localhost' , 'zhangxin'@'localhost' , 'zhouping'@'localhost' , 'yanglan'@'localhost';show grants for 'yanglan'@'localhost';
4) 用户刘星对职工表有查询权限,对工资字段具有更新权限。
create user 'liuxing'@'localhost';grant select on table employee to 'liuxing'@'localhost';grant update(salary) on table employee to 'liuxing'@'localhost';show grants for 'liuxing'@'localhost';revoke all on table employee from 'liuxing'@'localhost';show grants for 'liuxing'@'localhost';
5) 用户张新具有修改这两个表的结构的权限。
create user 'zhangxin'@'localhost';grant alter on table apartment to 'zhangxin'@'localhost';grant alter on table employee to 'zhangxin'@'localhost';revoke alter on table apartment from 'zhangxin'@'localhost';revoke alter on table employee from 'zhangxin'@'localhost';
6) 用户周平具有对两个表的所有权限,并具有给其他用户授权的权限。create user ‘zhouping’@‘localhost’;
grant all on table apartment to 'zhouping'@'localhost' with grant option;grant all on table employee to 'zhouping'@'localhost' with grant option;show grants for 'zhouping'@'localhost';revoke all on table apartment from 'zhouping'@'localhost';revoke all on table employee from 'zhouping'@'localhost';show grants for 'zhouping'@'localhost';
7) 用户杨兰具有从每个部门职工中查询最高工资,最低工资,平均工资的权限,他不能查看每个人的工资。
create view message_yanglanas select max(salary),min(salary),avg(salary) from employeegroup by ano;create user 'yanglan'@'localhost';grant select on message_yanglan to 'yanglan'@'localhost';show grants for 'yanglan'@'localhost';revoke all on message_yanglan from 'yanglan'@'localhost';show grants for 'yanglan'@'localhost';
提示:
创建用户SQL:create user 'wangming'@'localhost' identified by '123456';
用户名:wangming@localhost;
密码:123456
如果是在所有主机上(%为通配符)的用户:wangming@%可以使用show grants for 用户名查看每个用户的权限。如:Show grants for ‘wangming’@’localhost’
部分小题可创建合适的视图,针对视图进行授权。系统当前用户:系统函数user()
二. 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)
对于完整性约束而言,在用户图形界面设置较为方便,使用命令行则相对比较复杂。
对于不同用户权限设置,在设置权限后难切换用户验证较为复杂,使用show grants可以看到某用户所有的权限,作为验证方法较为简便。