@[TOC]

注:(一)到(四)的所有操作在Oracle数据库下进行,(五)的所有操作在MySQL下进行。

本文上篇:数据库基本操作总结(上)【数据定义、数据操纵、数据查询】

一、授权(grant)

1.创建用户“S学号u1”,“S学号u2”,“S学号u3”,“S学号u4”,并为其赋予connect角色。
其中,学号是指你的学号,比如你的学号为2018214184,则你创建的用户应该是S2018214184u1,S2018214184u2,S2018214184u3,S2018214184u4。

create user S2018214184u1 identified by abc; # identified by 设置密码
grant connect to S2018214184u1;
create user S2018214184u2 identified by abc;
grant connect to S2018214184u2;
create user S2018214184u3 identified by abc;
grant connect to S2018214184u3;
create user S2018214184u4 identified by abc;
grant connect to S2018214184u4;
create user S2018214184u5 identified by abc;
grant connect to S2018214184u5;

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

grant select on student to S2018214184u1;

(1)查询D2018214184用户的Student表中全体学生的详细记录。

select * from D2018214184.student;

(2)查询D2018214184用户Student表中所有姓刘的学生的姓名、学号和性别。

select sname,sno,ssex from D2018214184.student;

(3)查询D2018214184用户Student表中名字中第二字为“阳”字的学生的姓名和学号。

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

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

在d用户上:

grant all on student to S2018214184u2,S2018214184u3; # all表示全部权限
grant all on course to S2018214184u2,S2018214184u3;

在u2用户上:

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

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

commit;
select * from D2018214184.student;

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

在d用户上:

grant update(sno) on student to S2018214184u4;

在u4用户上:

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

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

在d用户上:

grant insert on sc to S2018214184u5;

在u5用户上:

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

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

在d用户上:

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

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

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

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

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

二、回收权限(revoke)

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

revoke update on student from S2018214184u2;

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

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

revoke select on D2018214184.sc from public;

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

revoke insert on D2018214184.sc from S2018214184u5;

4.在回收权限之后验证用户是否真正丧失了该权限(查询表,插入记录)。
在u2用户上:update D2018214184.student set sname='王敏' where sno=200215121;
显示:“权限不足”
在u2用户上:select * from D2018214184.sc;
显示:"table or view does not exist"
在u5用户上:insert into D2018214184.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 S2018214184u5;

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

select * from D2018214184.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;

在这里插入图片描述