数据库实验三

一、授权

1.创建用户”S2021224828u1”，S学号u2，S学号u3，S学号U4,并为其赋予connect角色。

create user S2021224828u1 identified by a123456;
grant connect to S2021224828u1;
create user S2021224828u2 identified by a123456;
grant connect to S2021224828u2;
create user S2021224828u3 identified by a123456;
grant connect to S2021224828u3;
create user S2021224828U4 identified by a123456;
grant connect to S2021224828u4;

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

grant select on student to S2021224828u1;

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

select *
from AL2021224828.student;

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

select sname,sno,ssex
from AL2021224828.student
where sname like '刘%';

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

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

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

grant all on student
to S2021224828u2,S2021224828u3;
grant all on course
to S2021224828u2,S2021224828u3;

S2021224828u2：

update AL2021224828.course
set ccredit=6
where cno=2;

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

grant update(sno) on student
to S2021224828U4;

S2021224828U4：

update AL2021224828.student
set sno='987654321'
where sno='123456789';

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

grant insert on sc
to S2021224828U5;

S2021224828U5：

insert into AL2021224828.sc
values ('987654321',1,100);

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

grant select on sc
to public;

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

select sno,grade
from AL2021224828.sc
where cno=3 order by grade desc;

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

select cno,count(*)
from AL2021224828.sc
group by cno;

（提醒：首先应该以新创建的用户的身份重新登陆数据库，然后再进行授权验证。）

二、回收权限

1.收回用户”S学号u2”修改学生学号的权限

revoke update(sno)
on student
from S2021224828u2;

注：不能单独回收修改一个属性的权限，此语句不可运行，仅作完成题目要求用。

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

revoke select
on sc
from public;

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

revoke insert
on sc
from S2021224828U5;

4.在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录）

S2021224828u2：

update AL2021224828.student
set sno='123456789'
where sno='987654321';

S2021224828u1/2/3/4/5：

select *
from AL2021224828.sc;

S2021224828U5：

insert into AL2021224828.sc
values ('987654321',1,100);

三、角色

1.创建一个角色

create role a2021224828;

2.给角色授予权限

grant select
on sc
to a2021224828;

3.将角色授予某一用户

grant a2021224828
to AL2021224853;

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

AL2021224853：

select *
from AL2021224828.sc;

四、完整性

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

Create table Teacher(
Tname varchar(9) constraint u_tname unique,
Tno number(20) constraint pk_t primary key 
);

2．建立学生登记表Student，要求学号在9000至9999之间，年龄<29,性别只能是’男’或’女’，姓名非空。

Create table student(
sno number(4) Constraint ck_sno check(sno>=9000 AND sno<=9999),
sname varchar(10) not null,
sage number(3) Constraint ck_sage check(sage<29),
sex varchar(2) constraint ck_sex check (sex in ('男','女'))
);

3．修改表Student的结构，由年龄小于29改为小于40。

注意，约束不能直接修改，可以先删后加。

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

4．建立课程表COURSE，要求课程表中的每门课程的学分不得超过7分，且主讲教师字段TNO参照Teacher表TNO字段，且当删除教师表中一行记录时，如果它被参照，则将Course表中相应记录中TNO的值设置为空。

CREATE TABLE Course(
Cno number,
Cname varchar(20),
Tno number(20) constraint fk_tno references teacher(tno)
on delete set null,
Ccredit number(4) Constraint ck_ccredit check(ccredit<=7)
);

5．建立表SC，要求SNO参照STUDENT表的学号，且当删除Student表中的一个学生记录时，级联删除学生的选课记录。

alter table student add constraint pk_s primary key(sno);

Create table sc(
sno number(4) constraint fk_sno references student(sno)
on delete cascade,
cno number
);

对上述新建立和修改定义的表，每个表输入3条数据，其中1条数据符合完整性约束，2条违反约束条件的，验证和体会Oracle的实体完整性和参照完整性。

insert into Teacher values ('李华','1234');
insert into Teacher values ('李华','5678');
insert into Teacher(tname) values ('张三');

insert into Student values (9876,'张三',20,'男');
insert into Student values (8888,null,20,'男');
insert into Student values (9999,'李四',40,'无');

insert into Course values (1,'数据库','1234',4);
insert into Course values (2,'数据分析','4321',4);
insert into Course values (3,'通信','1234',8);

insert into Sc values (9876,1);
insert into Sc values (9999,1);
insert into Sc values (8888,1);

五、数据库更新

（1）根据实验1所创建的表完成如下操作：

 1.修改数据

1）将王敏的同学的年龄改为20。

update student
set sage=20
where sname='王敏';

2）将全部同学的年龄加1。

update student
set sage=sage+1;

3）将’CS’系同学的选课信息中的成绩置0。

update sc
set grade=0
where sno in(
select sno
from student
where sdept='CS'
);

2.删除数据

1）删除和’ 刘晨’在同一个系的学生的信息。

delete
from student
where sdept in(
select sdept
from student
where sname='刘晨'
);

2）删除’CS’系同学的选课信息。

delete
from sc
where sno in(
select sno
from student
where sdept='CS'
);

（2）根据实验2所创建的表完成如下操作：

  1.修改数据

（1）将部门号为10的员工的工资增加一倍。

update emp
set sal=sal*2
where deptno=10;

（2）将empno为1004的员工的姓名改为你自己的姓名。

update emp
set ename='ddr'
where empno=1004;

2.删除数据

（1）将所属部门名为‘日月神教’的员工信息删除。

delete
from emp
where deptno in(
select deptno
from dept
where dname='日月神教'
);

（2）删除员工号大于7900的员工信息。

delete
from emp
where empno>7900;

 

 

修改过程中难免有纰漏，欢迎捉虫。

存在无法正确输出的语句，仅实验用。

禁止照搬。