数据库基本操作总结 2

2021-12-31

一、授权（grant）
二、回收权限（revoke）
三、角色（权限的集合）
四、视图
五、完整性

操作：

（一）授权
1.创建用户”S学号u1”，S学号u2，S学号u3，S学号U4,并为其赋予connect角色。
其中，学号是指你的学号，比如你的学号为20130001，则你创建的用户应该是S20130001u1，S20130001u2，S20130001u3,S20130001u4
2.假设你的用户名是S20130001（做实验时请根据你的账号修改），把你在数据库中创建的Student表的查询权限授给用户”S学号u1”， ”S学号u1”执行相应的查询。
（1）查询S20130001用户的Student表中全体学生的详细记录。
（2）查询S20130001用户Student表中所有姓刘的学生的姓名、学号和性别。
（3）查询S20130001用户Student表中名字中第二字为“阳”字的学生的姓名和学号。
3.把S20130001用户的Student表和Course表的全部权限授予用户”S学号u2”， ”S学号u3”；然后让”S学号u2”用户修改S20130001的数据。
4.把S20130001用户的表Student的修改学生学号的权限赋予用户” S学号U4”，然后让S20130001用户修改S20130001的student表的SNO数据。
5.把S20130001用户的SC表的插入权限授予“S学号U5”用户，然后让“S学号U5”用户向SC表插入一条记录。
6.把对表SC的查询权限授予所有用户。
（1）让“S学号u2”用户查询S20130001用户的SC表中选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列
（2）让“S学号u2”用户查询S20130001用户的SC表中各个课程号与相应的选课人数。
（首先应该以新创建的用户的身份重新登陆数据库，然后再进行授权）
（二）回收权限
1.收回用户”S学号u2”修改学生学号的权限
2.收回所有用户对表sc的查询权限
3.收回用户”S学号U5”sc表的insert权限
4.在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录）
（三）角色
1.创建一个角色
2.给角色授予权限
3.将角色授予某一用户
4.检查用户是否具有相应的权限
（四）完整性
1．建立教师表Teacher，要求教师名称Tname列取值唯一，教师编号TNO列为主码。
2．建立学生登记表Student，要求学号在9000至9999之间，年龄<29,性别只能是’男’或’女’，姓名非空。
3．修改表Student的结构，由年龄小于29改为小于40。
4．建立课程表COURSE，要求课程表中的每门课程的学分不得超过7分，且主讲教师字段TNO参照Teacher表TNO字段，且当删除教师表中一行记录时，如果它被参照，则将Course表中相应记录中TNO的值设置为空。5．建立表SC，要求SNO参照STUDENT表的学号，且当删除Student表中的一个学生记录时，级联删除学生的选课记录。对上述新建立和修改定义的表，每个表输入3条数据，其中1条数据符合完整性约束，2条违反约束条件的，验证和体会Oracle的实体完整性和参照完整性。

1.创建用户“S学号u1”，“S学号u2”，“S学号u3”，“S学号u4”，并为其赋予connect角色。
其中，学号是指你的学号，比如你的学号为2019214349，则你创建的用户应该是S2019214349u1，S2019214349u2，S2019214349u3，S2019214349u4。

create user S2019214349u1 identified by abc; # identified by 设置密码
grant connect to S2019214349u1;
create user S2019214349u2 identified by abc;
grant connect to S2019214349u2;
create user S2019214349u3 identified by abc;
grant connect to S2019214349u3;
create user S2019214349u4 identified by abc;
grant connect to S2019214349u4;
create user S2019214349u5 identified by abc;
grant connect to S2019214349u5;

2.假设你的用户名是D2019214349，把你在数据库中创建的Student表的查询权限授给用户”S学号u1”， ”S学号u1”执行相应的查询（S2019214349u1用户下查询D2019214349的表）。

grant select on student to S2019214349u1;

（1）查询D2019214349用户的Student表中全体学生的详细记录。

select * from D2019214349.student;

（2）查询D2019214349用户Student表中所有姓刘的学生的姓名、学号和性别。

select sname,sno,ssex from D2019214349.student;

（3）查询D2019214349用户Student表中名字中第二字为“阳”字的学生的姓名和学号。

select sname,sno from D2019214349.student where sname like '_阳%';

3.把D2019214349用户的Student表和Course表的全部权限授予用户”S学号u2”， ”S学号u3”；然后让”S学号u2”用户修改D2019214349(简称’d用户’)的数据。

在d用户上：

grant all on student to S2019214349u2,S2019214349u3; # all表示全部权限
grant all on course to S2019214349u2,S2019214349u3;
在u2用户上：

update D2019214349.student set sname='王敏' where sno=200215129;

在d用户上提交更改，这样在D2019214349上的数据才真正被更改。

commit;
select * from D2019214349.student;

4.把D2019214349用户的表Student的修改学生学号的权限赋予用户” S学号U4”，然后让‘S学号U4’用户修改D2019214349的student表的SNO数据。

在d用户上：

grant update(sno) on student to S2019214349u4;

在u4用户上：

update D2019214349.student set sno=200215128 where sno=200215129;
commit;

5.把D2019214349用户的SC表的插入权限授予“S学号U5”用户，然后让“S学号U5”用户向SC表插入一条记录。

在d用户上：

grant insert on sc to S2019214349u5;

在u5用户上：

insert into D2019214349.sc(sno,cno,grade) values(200215128,1,99);
commit;

6.把对表SC的查询权限授予所有用户。

在d用户上：

grant select on sc to public; # public表示所有用户

（1）让“S学号u2”用户查询D2019214349用户的SC表中选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

select sno,grade from D2019214349.sc where cno=3 order by grade DESC;

（2）让“S学号u2”用户查询D2019214349用户的SC表中各个课程号与相应的选课人数。

select cno,count(*) from D2019214349.sc group by cno;
1.收回用户”S学号u2”修改学生学号的权限。

revoke update on student from S2019214349u2;

注意：UPDATE/REFERENCES may only be REVOKEd from the whole table, not by column
（不能只回收一列的update权限，只能回收一个表的）

2.收回所有用户对表sc的查询权限。

revoke select on D2019214349.sc from public;

3.收回用户”S学号U5”sc表的insert权限。

revoke insert on D2019214349.sc from S2019214349u5;

4.在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录）。
在u2用户上：update D2019214349.student set sname='王敏' where sno=200215121;
显示：“权限不足”
在u2用户上：select * from D2019214349.sc;
显示：“table or view does not exist”
在u5用户上：insert into D2019214349.sc(sno,cno,grade) values(200215121,7,99);
显示：“table or view does not exist”

说明回收权限成功。
1.创建一个角色。

create role testrole; # 角色名应该不能含数字，试了一下，role1无法创建

2.给角色授予权限。

grant select on sc to testrole;

3.将角色授予某一用户。

grant testrole to S2019214349u5;

4.检查此用户是否具有相应的权限。

select * from D2019214349.sc; # 在u5中查询


（1）建立信息系学生的视图。并查询此视图，观察结果。

create view CS_STUDENT as select * from student where sdept='CS';
select * from CS_STUDENT;

（2）在视图上建立信息系选修了1号课程的学生的视图。

create view CS_STUDENT_CNO1(sno,sname,sage,ssex,sdept) as
select sc.sno,sname,sage,ssex,sdept from CS_STUDENT,sc 
where sc.sno=CS_STUDENT.sno and sc.cno=1;

（3）将学生的学号及其平均成绩定义为一个视图。

create view GRADE_AVG(sno,grade_avg) as
select sno,avg(grade) from sc group by sno;

（4）将Student表中所有女生记录定义为一个视图F_stu（sno，sname，sdept，sex），并设置其更新限制with check option。

create view F_stu(sno,sname,sdept,sex) as
select sno,sname,sdept,ssex from student where ssex='女'
with check option;

（5）对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’插入视图中，结果如何？

insert into F_stu(sno,sname,sdept) values(200215129,'smith','MA');

结果显示：视图 WITH CHECK OPTIDN where 子句违规。

（6）对4中的视图进行insert操作，将sno为200215129，sname为‘smith’，sdept为‘MA’，sex为‘女’插入视图中，结果如何？

insert into F_stu(sno,sname,sdept,sex) values(200215129,'smith','MA','女');

结果显示：成功插入。

完整性
进入MySQL后先建立数据库tmps并启用：create database tmps; use tmps;

1.建立教师表Teacher，要求教师名称TNAME列取值唯一，教师编号TNO列为主码。

create table Teacher
(
    TNO int primary key,
    TNAME varchar(20) unique
);
2.建立学生登记表Student，要求学号在9000至9999之间，年龄<29,性别只能是’男’或’女’，姓名非空。

create table Student 
(
    SNO int primary key,
    SAGE int,
    SSEX char(2), 
    SNAME varchar(20) not null,
    constraint sno_ck check(SNO>=9000 and SNO<=9999),
    constraint sage_ck check(SAGE<29),
    constraint ssex_ck check(SSEX in ('男','女'))
);
3.修改表Student的结构，由年龄小于29改为小于40。

alter table student drop constraint sage_ck; 
alter table student add constraint sage_ck check(sage<40);

修改约束不能直接修改，只能删除再添加。
尝试用alter table student modify sage int constraint sage_ck1 check(sage<40);
实际上也是加了一个约束，写成sage_ck1是因为加的约束不能和sage_ck重复

由于在MySQL中，并不检查check约束（check约束无效），所以以上语句在MySQL中运行错误，但是在Oracle数据库中能够成功运行。

MySQL中check约束无效的处理方式：https://blog.csdn.net/ldx19980108/article/details/79921853

4.建立课程表Course，要求课程表中的每门课程的学分不得超过7分，且主讲教师字段TNO参照Teacher表的TNO字段，且当删除教师表中一行记录时，如果它被参照，则将Course表中相应记录中TNO的值设置为空。建立表SC，要求SNO参照Student表的SNO字段，且当删除Student表中的一个学生记录时，级联删除学生的选课记录。对上述新建立和修改定义的表，每个表输入3条数据，其中2条数据符合完整性约束，1条违反约束条件，验证和体会数据库的实体完整性和参照完整性。

（1）建表和插入数据。

create table Course
(
    CNO int primary key,
    TNO int,
    CREDIT int check(CREDIT<=7),
    foreign key (TNO) references Teacher(TNO) on delete set null
);

create table SC
(
    SNO int,
    CNO int,
    primary key (SNO,CNO),
    foreign key (SNO) references Student(SNO) on delete cascade,
    foreign key (CNO) references Course(CNO) on delete cascade
);

insert into Teacher(TNO,TNAME) values(101,'刘晨');
insert into Teacher(TNO,TNAME) values(102,'张勇'); 
insert into Course(CNO,TNO,CREDIT) values(001,101,3);
insert into Course(CNO,TNO,CREDIT) values(002,101,2);
insert into Student(SNO,SAGE,SSEX,SNAME) values(9001,20,'男','张三');
insert into Student(SNO,SAGE,SSEX,SNAME) values(9002,21,'男','李四');
insert into SC(SNO,CNO) values(9001,001);
insert into SC(SNO,CNO) values(9001,002);

# 违反主码约束：ERROR 1062 (23000): Duplicate entry '101' for key 'PRIMARY'
insert into Teacher(TNO,TNAME) values(101,'李苏');

# 违反外键约束：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmps`.`Course`, CONSTRAINT `Course_ibfk_1` FOREIGN KEY (`TNO`) REFERENCES `Teacher` (`TNO`) ON DELETE SET NULL)
insert into Course(CNO,TNO,CREDIT) values(003,105,3);

# 违反主码约束：ERROR 1062 (23000): Duplicate entry '9002' for key 'PRIMARY'
insert into Student(SNO,SAGE,SSEX,SNAME) values(9002,20, '男','王五');

# 违反外键约束：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmps`.`SC`, CONSTRAINT `SC_ibfk_1` FOREIGN KEY (`SNO`) REFERENCES `Student` (`SNO`) ON DELETE CASCADE)
insert into SC(SNO,CNO) values(8001,001);

（2）删除Couse表中的某个TNO，则Course表中的对应TNO被置为空。
delete from Teacher where TNO=101;
（3）删除Student表中的某个SNO，则SC表中的对应SNO被级联删除。
delete from Student where SNO=9001;