数据库实验1

要求：

　　1.　建立如下表

　　　　定义基本表格major_????（专业）与stud_???(学生），关系模式如下(类型长度自定义)：

　　　　major_???(MNO专业编号 Primary key, MNAME 专业名称,loc 地址 in(主校区，南校区，新校区，铁道校区，湘雅校区),mdean 专业负责人)）

　　　　stud_???(SNO primary key, SNAME, SEX (男，女，其它，其他),TEL ,E-MAIL (基本格式判断), birthday >=’19990731’ ,MNO 班长学号  fk，MajorNo 专业编号 FK ),

　　　　其中MajorNo 专业编号为学号的第3，4位。

　　　　注意表的命名规则

 　　2.　插入样本数据

　　　　插入3个专业，如计算机科学与技术，物联网工程，数据科学与大数据技术，每个专业不小于10个人，其中包括自己的信息。

　　　　测试相关的完整性约束，并注意保留出错的提示，分析出错的原因

　　3．为每个学生建立相关用户，实现权限控制，每个学生可查询自己的信息，班长可查询本班所有学生信息； 为每个专业负责建立用户，每个专业负责可查询本专业所有学生信息。

　　4.　为每个学生建立相关用户，实现权限控制，每个学生可查询自己的信息，班长可查询本班所有学生信息； 为每个专业负责建立用户，每个专业负责可查询本专业所有学生信息。

 

建表，注意对应的完整性约束：

create user U_J122 identified by U_J123;
grant resource,connect to U_J122;
grant create view to U_J122;
connect U_J122/U_J123;

create table T_major_J122
( 
    mno char(2) primary key,
    mname varchar(20),
    loc varchar(20) check(loc in('主校区','南校区','新校区','铁道校区','湘雅校区')),
    mdean varchar(20)
);

create table T_stud_J122
(
    sno char(10) primary key,
    sname varchar(20),
    sex varchar(10) check(sex in('男','女','其它','其他')),
    tel varchar(15),
    email varchar(20) check(email like '%@%.%'),
    birthday date check((TO_CHAR('yyyymmdd'))>='19990731'),
    mno char(10) references T_stud_J122(sno),
    majorno char(2) references T_major_J122(mno)
);

插入数据，测试相关完整性的约束：

insert into T_major_J122 values ('02','计算机科学与技术','主校区','胡一超');
insert into T_major_J122 values ('19','大数据','南校区','胡二超');
insert into T_major_J122 values ('21','物联网','新校区','胡三超');

insert into T_stud_J122 values ('0902160122','周锐','男','18774894438','838567391@qq.com','11-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160121','胡一','男','18774894439','838567392@qq.com','12-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160120','胡二','女','18774894430','838567393@qq.com','13-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160119','胡三','男','18774894431','838567394@qq.com','14-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160118','胡四','女','18774894432','838567395@qq.com','15-11月-2000','0902160122','02');
insert into T_stud_J122 values ('0902160217','胡五','女','18774894433','838567396@qq.com','16-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160216','胡六','男','18774894434','838567397@qq.com','17-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160215','胡七','女','18774894435','838567398@qq.com','18-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160214','胡八','男','18774894436','838567399@qq.com','19-11月-2000','0902160217','02');
insert into T_stud_J122 values ('0902160213','胡九','女','18774894437','838567390@qq.com','20-11月-2000','0902160217','02');

insert into T_stud_J122 values ('0919160122','任一','女','18674894438','838564391@qq.com','21-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160121','任二','男','18574894438','838567491@qq.com','22-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160120','任三','女','18474894438','838567591@qq.com','23-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160119','任四','男','18274894438','838567691@qq.com','24-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160118','任五','男','18374894438','838567791@qq.com','25-12月-2000','0919160122','19');
insert into T_stud_J122 values ('0919160217','任六','女','18174794438','838567891@qq.com','26-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160216','任七','男','18074694438','838567991@qq.com','20-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160215','任八','女','18974594438','838567091@qq.com','27-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160214','任九','男','18764894438','838567191@qq.com','28-12月-2000','0919160217','19');
insert into T_stud_J122 values ('0919160213','任十','男','18714894438','838567291@qq.com','29-12月-2000','0919160217','19');

insert into T_stud_J122 values ('0921160122','刘一','男','11774844438','828567391@qq.com','22-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160121','刘二','女','12774874438','838667391@qq.com','23-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160120','刘三','男','17774884438','838767391@qq.com','24-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160119','刘四','男','14774834438','838867391@qq.com','25-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160118','刘五','女','19774821438','838967391@qq.com','26-11月-2000','0921160122','21');
insert into T_stud_J122 values ('0921160217','刘六','男','18774899438','838067391@qq.com','27-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160216','刘七','女','18774898438','838167391@qq.com','28-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160215','刘八','男','18774896438','838267391@qq.com','29-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160214','刘九','男','18774895438','838367391@qq.com','30-11月-2000','0921160217','21');
insert into T_stud_J122 values ('0921160213','刘十','女','18774893438','838467391@qq.com','21-11月-2000','0921160217','21');

给每个学生建立相关用户：

select 'create user U'||sno||' identified by P'||sno||';'from T_stud_J122;

connect system/zr13795;
create user U0902160118 identified by P0902160118;
create user U0902160119 identified by P0902160119;
create user U0902160120 identified by P0902160120;
create user U0902160121 identified by P0902160121;
create user U0902160122 identified by P0902160122;
create user U0902160213 identified by P0902160213;
create user U0902160214 identified by P0902160214;
create user U0902160215 identified by P0902160215;
create user U0902160216 identified by P0902160216;
create user U0902160217 identified by P0902160217;
create user U0919160118 identified by P0919160118;
create user U0919160119 identified by P0919160119;
create user U0919160120 identified by P0919160120;
create user U0919160121 identified by P0919160121;
create user U0919160122 identified by P0919160122;
create user U0919160213 identified by P0919160213;
create user U0919160214 identified by P0919160214;
create user U0919160215 identified by P0919160215;
create user U0919160216 identified by P0919160216;
create user U0919160217 identified by P0919160217;
create user U0921160118 identified by P0921160118;
create user U0921160119 identified by P0921160119;
create user U0921160120 identified by P0921160120;
create user U0921160121 identified by P0921160121;
create user U0921160122 identified by P0921160122;
create user U0921160213 identified by P0921160213;
create user U0921160214 identified by P0921160214;
create user U0921160215 identified by P0921160215;
create user U0921160216 identified by P0921160216;
create user U0921160217 identified by P0921160217;

select 'grant connect to U'||sno||';'from T_stud_J122;

grant connect to U0902160118;
grant connect to U0902160119;
grant connect to U0902160120;
grant connect to U0902160121;
grant connect to U0902160122;
grant connect to U0902160213;
grant connect to U0902160214;
grant connect to U0902160215;
grant connect to U0902160216;
grant connect to U0902160217;
grant connect to U0919160118;
grant connect to U0919160119;
grant connect to U0919160120;
grant connect to U0919160121;
grant connect to U0919160122;
grant connect to U0919160213;
grant connect to U0919160214;
grant connect to U0919160215;
grant connect to U0919160216;
grant connect to U0919160217;
grant connect to U0921160118;
grant connect to U0921160119;
grant connect to U0921160120;
grant connect to U0921160121;
grant connect to U0921160122;
grant connect to U0921160213;
grant connect to U0921160214;
grant connect to U0921160215;
grant connect to U0921160216;
grant connect to U0921160217;

select 'create user U'||mno||' identified by P'||mno||';'from T_major_J122;

create user U02 identified by P02;
create user U19 identified by P19;
create user U21 identified by P21;

select 'grant connect to U'||mno||';'from T_major_J122;

grant connect to U02;
grant connect to U19;
grant connect to U21;

建立视图，实现权限控制：

create view V_view1_J122
as
select * from T_stud_J122
where 'U'||sno=user or 'U'||mno=user or 'U'||majorno=user;

grant select on V_view1_J122 to public;

set linesize 200;
select * from U_J122.V_view1_J122;

conn U02/P02;
select * from U_J122.V_view1_J122;