重生之我是带学生（2021.9.18）

ORACLE实验

1.根据要求建立如下表

定义基本表格major_d101与stud_d101，关系模式如下：

        major_d101(mno，mname ，loc地址，mdean专业负责人)）

        stud_d101(sno，sname，sex，tel ，email，birthday，mno 班长学号，mno，其中学号的第3，4位为mno。

2.插入样本数据

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

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

代码：(暂未完成)

drop user u_d101 cascade;    --级联删除用户
create user u_d101 identified by p123456;    --创建用户
grant resource,connect,dba to u_d101;    --授权
conn u_d101/p123456

-----建表-----
drop table t_major_d101;
create table t_major_d101(
    mno varchar2(32),
    mname varchar2(32),
    loc varchar2(32),
    mdean varchar2(32));

drop table t_stud_d101;
create table t_stud_d101(
    sno varchar2(32),
    sname varchar2(32),
    sex varchar2(32),
    tel varchar2(32),
    email varchar2(32),
    birthday varchar2(32),
    mno varchar2(32),
    majorno varchar2(32));
    
-----插入数据：每班12人-----
delete from t_stud_d101;
delete from t_major_d101;

insert into t_major_d101 values('01','计算机科学与技术','南校区','计大');
insert into t_major_d101 values('02','数据科学与大数据技术','南校区','数大');
insert into t_major_d101 values('03','物联网工程','南校区','物大');

-----计科12人-----
insert into t_stud_d101 values('8201190101','计大','男','11111010101','jida@163.com','20010101','11111010101','01');
insert into t_stud_d101 values('8201190102','计一二','男','11111010102','jiyier@163.com','20010102','11111010101','01');
insert into t_stud_d101 values('8201190103','计一三','男','11111010103','jiyisan@163.com','20010103','11111010101','01');
insert into t_stud_d101 values('8201110101','计二一','女','11111010201','jieryi@163.com','20010201','11111010201','01');
insert into t_stud_d101 values('8201110102','计二二','女','11111010202','jierer@163.com','20010202','11111010201','01');
insert into t_stud_d101 values('8201110103','计二三','女','11111010203','jiersan@163.com','20010203','11111010201','01');
insert into t_stud_d101 values('8201190301','计三一','男','11111010301','jisanyi@163.com','20010301','11111010301','01');
insert into t_stud_d101 values('8201190302','计三二','男','11111010302','jisaner@163.com','20010302','11111010301','01');
insert into t_stud_d101 values('8201190303','计三三','男','11111010303','jisasnan@163.com','20010303','11111010301','01');
insert into t_stud_d101 values('8201190401','计四一','女','11111010401','jisiyi@163.com','20010401','11111010401','01');
insert into t_stud_d101 values('8201190402','计四二','女','11111010402','jisier@163.com','20010402','11111010401','01');
insert into t_stud_d101 values('8201190403','计四三','女','11111010403','jisisan@163.com','20010403','11111010401','01');

-----大数据12人-----
insert into t_stud_d101 values('8202190101','数大','男','11111020101','shuda@163.com','20010101','11111020101','02');
insert into t_stud_d101 values('8202190102','数一二','男','11111020102','shuyier@163.com','20010102','11111020101','02');
insert into t_stud_d101 values('8202190103','数一三','男','11111020103','shuyisan@163.com','20010103','11111020101','02');
insert into t_stud_d101 values('8202110101','数二一','女','11111020201','shueryi@163.com','20010201','11111020201','02');
insert into t_stud_d101 values('8202110102','数二二','女','11111020202','shuerer@163.com','20010202','11111020201','02');
insert into t_stud_d101 values('8202110103','数二三','女','11111020203','shuersan@163.com','20010203','11111020201','02');
insert into t_stud_d101 values('8202190301','数三一','男','11111020301','shusanyi@163.com','20010301','11111020301','02');
insert into t_stud_d101 values('8202190302','数三二','男','11111020302','shusaner@163.com','20010302','11111020301','02');
insert into t_stud_d101 values('8202190303','数三三','男','11111020303','shusasnan@163.com','20010303','11111020301','02');
insert into t_stud_d101 values('8202190401','数四一','女','11111020401','shusiyi@163.com','20010401','11111020401','02');
insert into t_stud_d101 values('8202190402','数四二','女','11111020402','shusier@163.com','20010402','11111020401','02');
insert into t_stud_d101 values('8202190403','数四三','女','11111020403','shusisan@163.com','20010403','11111020401','02');

-----物联网12人-----
insert into t_stud_d101 values('8203190101','物大','男','11111030101','wuda@163.com','20010101','11111030101','03');
insert into t_stud_d101 values('8203190102','物一二','男','11111030102','wuyier@163.com','20010102','11111030101','03');
insert into t_stud_d101 values('8203190103','物一三','男','11111030103','wuyisan@163.com','20010103','11111030101','03');
insert into t_stud_d101 values('8203110101','物二一','女','11111030201','wueryi@163.com','20010201','11111030201','03');
insert into t_stud_d101 values('8203110102','物二二','女','11111030202','wuerer@163.com','20010202','11111030201','03');
insert into t_stud_d101 values('8203110103','物二三','女','11111030203','wuersan@163.com','20010203','11111030201','03');
insert into t_stud_d101 values('8203190301','物三一','男','11111030301','wusanyi@163.com','20010301','11111030301','03');
insert into t_stud_d101 values('8203190302','物三二','男','11111030302','wusaner@163.com','20010302','11111030301','03');
insert into t_stud_d101 values('8203190303','物三三','男','11111030303','wusasnan@163.com','20010303','11111030301','03');
insert into t_stud_d101 values('8203190401','物四一','女','11111030401','wusiyi@163.com','20010401','11111030401','03');
insert into t_stud_d101 values('8203190402','物四二','女','11111030402','wusier@163.com','20010402','11111030401','03');
insert into t_stud_d101 values('8203190403','物四三','女','11111030403','wusisan@163.com','20010403','11111030401','03');

-----删除用户-----
select 'drop user u'||sno||';' from t_stud_d101;
-----为每个学生新建用户-----
select 'create user u'||sno||' identified by p'||sno||';' from t_stud_d101;
-----授予connect权利-----
select 'grant connect to u'||sno||';' from t_stud_d101;
-----删除学生用户视图-----
select 'drop view vi'||sno||';' from t_stud_d101;
-----为每个用户建立各自的视图
select 'create view vi'||sno||' as select * from t_stud_d101 where sno='||sno||';' from t_stud_d101;
-----为每个用户授予查询视图的权利-----
select 'grant select on vi'||sno||' to u'||sno||';' from t_stud_d101;
-----删除班长用户视图-----
select 'drop view vi'||sno||';' from t_stud_d101 where sno=mno;
select 'drop view vib'||sno||';' from t_stud_d101 where sno=mno;
-----为班长建立班长视图-----
select 'create view vib'||sno||' as select * from t_stud_d101 where mno='||sno||';' from t_stud_d101 where sno=mno;
-----为班长授予查询班长视图的权利-----
select 'grant select on vib'||sno||' to u'||sno||';' from t_stud_d101 where sno=mno;