数据库实验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;