Oracle实验一熟悉ORACLE环境并练习SQL操作

实验一、熟悉ORALCE环境并练习SQL操作

一、目的与要求

本实验主要是熟悉ORACLE的运行环境，在CLIENT端进行联接设置与服务器联通，进入ORACLE的SQL*PLUS或SQL Developer的操作环境，进一步建立表格，并考虑主键、外键及值约束，进行相应的测试与查询等操作。

二、操作环境

硬件：主频2GHz以上服务器（内存2GB以上、硬件空闲2.2GB以上），主频1GHz以上微机，内存1GB以上。

软件：WINDOWS XP/2000/2003/或Win7/Win8/Win10等。如操作系统是 SERVER版，可安装ORACLE/9i/10g/11g/12C FOR NT/WINDOWS(注意有32位与64位的区别，可选企业版)；如果Windows非server如XP/win7等，安装时请选择个人版(PERSONAL)，注意安装时要有兼容性设置与用管理员运行。安装过程中需要关注系统预定义的账号SYS与SYSTEM的密码设置。

三、实验内容

1.上机步骤

在实验室上机时,一般安装时管理员已设置好.先找到windows中的oracle服务（一般为OracleServiceORCL，如下图1）,启动此服务,再找到sql*plus所在的位置（一般桌面上有如下图2的程序组有）,运行即可进行登录界面.

图1 Oracle在计算机系统中的服务

图2称SQL*Plus在系统程序组中的位置

实验室现有环境操作步骤：

通过桌面我的电脑找到管理服务的入口，如图3，或者通过资源管理器找到管理。

图3 通过桌面我的电脑找到管理服务的入口

然后找到服务，如图4。

图4 系统中的服务

再找启动oracle的服务如图1。

（1）从桌面或在程序中（图2）执行SQL*PLUS

（2）输入：system/manager(密码可能有变化)

（3）在SQL>状态下输入建立用户命令:

create user username identified by password;

其中username与password不要数字开头，用户名username中按规则加学号后3位作后缀。如：U_J101表示尾数为J101号的用户。

（4）给用户授权：

grant resource,connect to username;

(5) 连接用户：

connect username/password

在SQL>状态下，就可以执行建表等操作了。

2.根据要求建立如下表

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

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

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

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

注意表的命名规则。

3.插入样本数据

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

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

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

四、实验过程

1.建立用户u_j524,密码为p123456,并完成相关操作

drop user u_j524 cascade;
create user u_j524 identified by p123456;
grant resource,connect,dba to u_j524;
conn u_j524/p123456

2.定义基本表格t_major_j432（专业）与t_stud_j432(学生），关系模式如下：

t_major_j432(

MNO专业编号 Primary key,

MNAME 专业名称,

loc 地址 in(主校区，南校区，新校区，铁道校区，湘雅校区),

mdean 专业负责人)）

t_stud_j432(SNO primary key,

SNAME,

SEX (男，女，其它，其他),

TEL ,E-MAIL (基本格式判断),

birthday >=’19990731’ ,

MNO 班长学号 fk，

MajorNo 专业编号 fk ),

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

create table t_major_j524(
    mno varchar2(2),
    mname varchar2(32),
    loc varchar2(32),
    mdean varchar2(32),
    constraint pk_major_mno primary key(mno),
    constraint ck_major_mname check(loc in('主校区','南校区','新校区','铁道校区','湘雅校区')));

create table t_stud_j524(
    sno varchar2(10),
    sname varchar2(32),
    sex varchar2(32),
    tel varchar2(32),
    email varchar2(32),
    birthday date,
    mno varchar2(10),
    majorno varchar2(2),
    constraint pk_stud_sno primary key(sno),
    constraint ck_stud_sex check(sex in('男','女','其它')),
    constraint ck_stud_email check(email like '%@%.%'),
    constraint ck_stud_birthday check(birthday>to_date('19990731','yyyymmdd'))，    
    constraint ck_stud_majorno check(majorno in substr(sno,3,2)),    
    constraint fk_stud_mno foreign key(mno) references t_stud_j524(sno),
    constraint fk_stud_majorno foreign key(majorno) references t_major_j524(mno));

3.插入样本数据：

插入3个专业的6个班级，如：计算机科学与技术，物联网工程，数据科学与大数据技术，每个专业不小于10个人。

------插入实验数据：3个专业6个班共计36人---------
delete from t_stud_j524;
delete from t_major_j524;

insert into t_major_j524 values('21','数据科学与大数据技术','主校区','张三');
insert into t_major_j524 values('02','计算机科学与技术','主校区','许宁');
insert into t_major_j524 values('19','物联网工程','主校区','李四');
--数据科学与大数据技术13名同学
insert into t_stud_j524 values('0921170114','李一','男','15390451131','liyi@163.com',to_date('20000122','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170115','李二','男','18726450331','lier@163.com',to_date('20001010','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170117','李三','男','13260450831','lisan@163.com',to_date('20001220','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170120','李四','男','18655450232','lisi@163.com',to_date('20000525','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170122','李五','女','15342450031','liwu@163.com',to_date('20000214','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170123','李六','女','18790332061','liliu@163.com',to_date('20000323','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170124','李七','男','13250450031','liqi@163.com',to_date('20000930','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170207','张一','男','18790450032','zhangyi@163.com',to_date('20001130','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170225','张二','女','15256350531','zhanger@163.com',to_date('20000722','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170226','张三','男','18796650031','zhangsan@163.com',to_date('20001023','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170227','张四','女','18390455631','zhangsi@163.com',to_date('20000504','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170209','张五','男','18790460031','zhangwu@163.com',to_date('20010130','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170228','张六','女','18592450551','zhangliu@163.com',to_date('20000101','yyyymmdd'),'0921170207','21');

--计算机科学与技术12名同学
insert into t_stud_j524 values('0902170516','王二','男','18790450032','wanger@163.com',to_date('20001130','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170520','王三','男','18790460031','wangsan@163.com',to_date('20010130','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170521','王四','男','15390451131','wangsi@163.com',to_date('20000122','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170523','杜豪','男','18726450331','duhao@163.com',to_date('20001010','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170524','宋海磊','男','13278882876','songhailei@163.com',to_date('20001220','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170525','翟树杰','男','13278886638','zhaishujie@163.com',to_date('20000525','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170526','胡旭东','男','15342450031','huxudong@163.com',to_date('20000214','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170601','嬴政','男','18790332061','yingzheng@163.com',to_date('20000323','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170602','刘邦','男','13250450031','liubang@163.com',to_date('20000930','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170604','李广','男','15256350531','liguang@163.com',to_date('20000722','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170605','李世民','女','18796650031','lishimin@163.com',to_date('20001023','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170625','赵匡胤','女','18796650031','zhaokuangyin@163.com',to_date('20001023','yyyymmdd'),'0902170601','02');

--物联网工程11名同学
insert into t_stud_j524 values('0919170112','周一','女','18790450032','zhouyi@163.com',to_date('20001130','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170120','周二','男','18790460031','zhouer@163.com',to_date('20010130','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170107','周三','男','15390451131','zhousan@163.com',to_date('20000122','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170109','周四','男','18726450331','zhousi@163.com',to_date('20001010','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170110','周五','男','13260450831','zhouwu@163.com',to_date('20001220','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170115','周六','女','18655450232','zhouliu@163.com',to_date('20000525','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170214','陈一','男','15342450031','chenyi@163.com',to_date('20000214','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170215','陈二','女','18790332061','chener@163.com',to_date('20000323','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170217','陈三','女','13250450031','chensan@163.com',to_date('20000930','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170220','陈四','女','15256350531','chensi@163.com',to_date('20000722','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170225','陈五','男','18796650031','chenwu@163.com',to_date('20001023','yyyymmdd'),'0919170214','19');

4.测试相关的完整性约束，并注意保留出错的提示，分析出错的原因 (建议把4放在3前面)

-----测试约束
---------t_major_j524约束
 
column mno format a3;
column mname format a32;
column loc format a10;
column mdean format a12;
column sno format a11;
column sname format a7;
column sex format a4;
column tel format a12;
column email format a32;
column birthday format a11;
column mno format a11;
column majorno format a3;
set linesize 150;
set pagesize 100;

       
--1.主键不为空
insert into t_major_j524(mno) values('');
--2.主键不能重复
insert into t_major_j524(mno) values('34');
insert into t_major_j524(mno) values('34');
delete from t_major_j524;
--3.约束：loc in('主校区','南校区','新校区','铁道校区','湘雅校区')
insert into t_major_j524(loc) values('北校区');
--4.成功插入几条完整数据
insert into t_major_j524 values('34','计算机科学与技术','主校区','许宁');
insert into t_major_j524 values('35','大数据','主校区','许宁');
 
select * from t_major_j524;

--t_stud_j524约束
--1.主键不为空
insert into t_stud_j524(sno) values('');
--2.主键不能重复
insert into t_stud_j524(sno) values('1234567890');
insert into t_stud_j524(sno) values('1234567890');
delete from t_stud_j524;

--3.约束：sex in('男','女','其它')
insert into t_stud_j524(sno,sex) values('1234567890','不知道');
--4.约束：email like '%@%.%'
insert into t_stud_j524(sno,email) values('1234567890','123456@789');
--5.约束：birthday>to_date('19990731','yyyymmdd')
insert into t_stud_j524(sno,birthday) values('1234567890',to_date('19951130','yyyymmdd'));
--6.约束：majorno in substr(sno,3,2)
insert into t_stud_j524(sno,majorno) values('1234567890','35');
--7.外键约束：foreign key(mno) references t_stud_j524(sno)
insert into t_stud_j524(sno,mno) values('1234567890','1234567891');
--8.外键约束：foreign key(majorno) references t_major_j524(mno)
insert into t_stud_j524(sno,majorno) values('0987654321','87');
--9.成功插入一条完整数据
insert into t_stud_j524 values('1234567890','余东','男','18790450031','ydduong@163.com',to_date('20001130','yyyymmdd'),'1234567890','34');

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

---权限管理：为学生、班长、年级长分别建立视图并赋予相应查看权限---
set heading off
set feedback off
set echo off
set termout off
spool d:\users.txt

--删除用户
select 'drop user u'||sno||';' from t_stud_j524;
--新建用户（为每个学生）
select 'create user u'||sno||' identified by p'||sno||';' from t_stud_j524;
--授予connect权利
select 'grant connect to u'||sno||';' from t_stud_j524;
--删除学生用户视图
select 'drop view vi'||sno||';' from t_stud_j524;
--为每个用户建立各自的视图
select 'create view vi'||sno||' as select * from t_stud_j524 where sno='||sno||';' from t_stud_j524;
--为每个用户授予查询视图的权利
select 'grant select on vi'||sno||' to u'||sno||';' from t_stud_j524;
--删除班长用户视图
select 'drop view vi'||sno||';' from t_stud_j524 where sno=mno;
select 'drop view vib'||sno||';' from t_stud_j524 where sno=mno;
--为班长建立班长视图
select 'create view vib'||sno||' as select * from t_stud_j524 where mno='||sno||';' from t_stud_j524 where sno=mno;
--为班长授予查询班长视图的权利
select 'grant select on vib'||sno||' to u'||sno||';' from t_stud_j524 where sno=mno;
--删除年级负责人用户视图
select 'drop view vi'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
select 'drop view vib'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
select 'drop view viz'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
--为年级负责人创建年级视图
select 'create view viz'||t_stud_j524.sno||' as select * from t_stud_j524 where majorno='||t_stud_j524.majorno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
--为年级负责人授予查询年级视图的权利
select 'grant select on viz'||t_stud_j524.sno||' to u'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
spool off
set heading on
set feedback on
set echo on
set termout on
------------------------

@d:\users.txt
 
--普通学生仅查询自己
conn u0919170225/p0919170225
select * from u_j524.vi0919170225;
--班长可以查询自己和班级同学
conn u0919170112/p0919170112
select * from u_j524.vib0919170112;
--年级长可以查询自己和年同学
conn u0921170226/p0921170226
select * from u_j524.viz0921170226;


set heading off
set feedback off
set echo off
set termout off
spool d:\users.txt

实验源码

drop user u_j524 cascade;
create user u_j524 identified by p123456;
grant resource,connect,dba to u_j524;
conn u_j524/p123456

---------------建表----------------
create table t_major_j524(
    mno varchar2(2),
    mname varchar2(32),
    loc varchar2(32),
    mdean varchar2(32),
    constraint pk_major_mno primary key(mno),
    constraint ck_major_mname check(loc in('主校区','南校区','新校区','铁道校区','湘雅校区')));

create table t_stud_j524(
    sno varchar2(10),
    sname varchar2(32),
    sex varchar2(32),
    tel varchar2(32),
    email varchar2(32),
    birthday date,
    mno varchar2(10),
    majorno varchar2(2),
    constraint pk_stud_sno primary key(sno),
    constraint ck_stud_sex check(sex in('男','女','其它')),
    constraint ck_stud_email check(email like '%@%.%'),
    constraint ck_stud_birthday check(birthday>to_date('19990731','yyyymmdd'))，    
    constraint ck_stud_majorno check(majorno in substr(sno,3,2)),    
    constraint fk_stud_mno foreign key(mno) references t_stud_j524(sno),
    constraint fk_stud_majorno foreign key(majorno) references t_major_j524(mno));

-----测试约束
---------t_major_j524约束
 
column mno format a3;
column mname format a32;
column loc format a10;
column mdean format a12;
column sno format a11;
column sname format a7;
column sex format a4;
column tel format a12;
column email format a32;
column birthday format a11;
column mno format a11;
column majorno format a3;
set linesize 150;
set pagesize 100;

       
--1.主键不为空
insert into t_major_j524(mno) values('');
--2.主键不能重复
insert into t_major_j524(mno) values('34');
insert into t_major_j524(mno) values('34');
delete from t_major_j524;
--3.约束：loc in('主校区','南校区','新校区','铁道校区','湘雅校区')
insert into t_major_j524(loc) values('北校区');
--4.成功插入几条完整数据
insert into t_major_j524 values('34','计算机科学与技术','主校区','许宁');
insert into t_major_j524 values('35','大数据','主校区','许宁');
 
select * from t_major_j524;

--t_stud_j524约束
--1.主键不为空
insert into t_stud_j524(sno) values('');
--2.主键不能重复
insert into t_stud_j524(sno) values('1234567890');
insert into t_stud_j524(sno) values('1234567890');
delete from t_stud_j524;

--3.约束：sex in('男','女','其它')
insert into t_stud_j524(sno,sex) values('1234567890','不知道');
--4.约束：email like '%@%.%'
insert into t_stud_j524(sno,email) values('1234567890','123456@789');
--5.约束：birthday>to_date('19990731','yyyymmdd')
insert into t_stud_j524(sno,birthday) values('1234567890',to_date('19951130','yyyymmdd'));
--6.约束：majorno in substr(sno,3,2)
insert into t_stud_j524(sno,majorno) values('1234567890','35');
--7.外键约束：foreign key(mno) references t_stud_j524(sno)
insert into t_stud_j524(sno,mno) values('1234567890','1234567891');
--8.外键约束：foreign key(majorno) references t_major_j524(mno)
insert into t_stud_j524(sno,majorno) values('0987654321','87');
--9.成功插入一条完整数据
insert into t_stud_j524 values('1234567890','余东','男','18790450031','ydduong@163.com',to_date('20001130','yyyymmdd'),'1234567890','34');
 


------插入实验数据：3个专业6个班共计36人---------
delete from t_stud_j524;
delete from t_major_j524;

insert into t_major_j524 values('21','数据科学与大数据技术','主校区','张三');
insert into t_major_j524 values('02','计算机科学与技术','主校区','许宁');
insert into t_major_j524 values('19','物联网工程','主校区','李四');

--数据科学与大数据技术13名同学
insert into t_stud_j524 values('0921170114','李一','男','15390451131','liyi@163.com',to_date('20000122','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170115','李二','男','18726450331','lier@163.com',to_date('20001010','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170117','李三','男','13260450831','lisan@163.com',to_date('20001220','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170120','李四','男','18655450232','lisi@163.com',to_date('20000525','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170122','李五','女','15342450031','liwu@163.com',to_date('20000214','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170123','李六','女','18790332061','liliu@163.com',to_date('20000323','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170124','李七','男','13250450031','liqi@163.com',to_date('20000930','yyyymmdd'),'0921170114','21');
insert into t_stud_j524 values('0921170207','张一','男','18790450032','zhangyi@163.com',to_date('20001130','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170225','张二','女','15256350531','zhanger@163.com',to_date('20000722','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170226','张三','男','18796650031','zhangsan@163.com',to_date('20001023','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170227','张四','女','18390455631','zhangsi@163.com',to_date('20000504','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170209','张五','男','18790460031','zhangwu@163.com',to_date('20010130','yyyymmdd'),'0921170207','21');
insert into t_stud_j524 values('0921170228','张六','女','18592450551','zhangliu@163.com',to_date('20000101','yyyymmdd'),'0921170207','21');

--计算机科学与技术12名同学
insert into t_stud_j524 values('0902170516','王二','男','18790450032','wanger@163.com',to_date('20001130','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170520','王三','男','18790460031','wangsan@163.com',to_date('20010130','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170521','王四','男','15390451131','wangsi@163.com',to_date('20000122','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170523','杜豪','男','18726450331','duhao@163.com',to_date('20001010','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170524','宋海磊','男','13278882876','songhailei@163.com',to_date('20001220','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170525','翟树杰','男','13278886638','zhaishujie@163.com',to_date('20000525','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170526','胡旭东','男','15342450031','huxudong@163.com',to_date('20000214','yyyymmdd'),'0902170516','02');
insert into t_stud_j524 values('0902170601','嬴政','男','18790332061','yingzheng@163.com',to_date('20000323','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170602','刘邦','男','13250450031','liubang@163.com',to_date('20000930','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170604','李广','男','15256350531','liguang@163.com',to_date('20000722','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170605','李世民','女','18796650031','lishimin@163.com',to_date('20001023','yyyymmdd'),'0902170601','02');
insert into t_stud_j524 values('0902170625','赵匡胤','女','18796650031','zhaokuangyin@163.com',to_date('20001023','yyyymmdd'),'0902170601','02');

--物联网工程11名同学
insert into t_stud_j524 values('0919170112','周一','女','18790450032','zhouyi@163.com',to_date('20001130','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170120','周二','男','18790460031','zhouer@163.com',to_date('20010130','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170107','周三','男','15390451131','zhousan@163.com',to_date('20000122','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170109','周四','男','18726450331','zhousi@163.com',to_date('20001010','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170110','周五','男','13260450831','zhouwu@163.com',to_date('20001220','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170115','周六','女','18655450232','zhouliu@163.com',to_date('20000525','yyyymmdd'),'0919170112','19');
insert into t_stud_j524 values('0919170214','陈一','男','15342450031','chenyi@163.com',to_date('20000214','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170215','陈二','女','18790332061','chener@163.com',to_date('20000323','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170217','陈三','女','13250450031','chensan@163.com',to_date('20000930','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170220','陈四','女','15256350531','chensi@163.com',to_date('20000722','yyyymmdd'),'0919170214','19');
insert into t_stud_j524 values('0919170225','陈五','男','18796650031','chenwu@163.com',to_date('20001023','yyyymmdd'),'0919170214','19');

---权限管理：为学生、班长、年级长分别建立视图并赋予相应查看权限---
set heading off
set feedback off
set echo off
set termout off
spool d:\users.txt

--删除用户
select 'drop user u'||sno||';' from t_stud_j524;
--新建用户（为每个学生）
select 'create user u'||sno||' identified by p'||sno||';' from t_stud_j524;
--授予connect权利
select 'grant connect to u'||sno||';' from t_stud_j524;
--删除学生用户视图
select 'drop view vi'||sno||';' from t_stud_j524;
--为每个用户建立各自的视图
select 'create view vi'||sno||' as select * from t_stud_j524 where sno='||sno||';' from t_stud_j524;
--为每个用户授予查询视图的权利
select 'grant select on vi'||sno||' to u'||sno||';' from t_stud_j524;
--删除班长用户视图
select 'drop view vi'||sno||';' from t_stud_j524 where sno=mno;
select 'drop view vib'||sno||';' from t_stud_j524 where sno=mno;
--为班长建立班长视图
select 'create view vib'||sno||' as select * from t_stud_j524 where mno='||sno||';' from t_stud_j524 where sno=mno;
--为班长授予查询班长视图的权利
select 'grant select on vib'||sno||' to u'||sno||';' from t_stud_j524 where sno=mno;
--删除年级负责人用户视图
select 'drop view vi'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
select 'drop view vib'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
select 'drop view viz'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
--为年级负责人创建年级视图
select 'create view viz'||t_stud_j524.sno||' as select * from t_stud_j524 where majorno='||t_stud_j524.majorno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
--为年级负责人授予查询年级视图的权利
select 'grant select on viz'||t_stud_j524.sno||' to u'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;

/*
select 'drop user u'||sno||';' from t_stud_j524;
select 'create user u'||sno||' identified by p'||sno||';' from t_stud_j524;
select 'grant connect to u'||sno||';' from t_stud_j524;
select 'drop view vi'||sno||';' from t_stud_j524;
select 'create view vi'||sno||' as select * from t_stud_j524 where sno='||sno||';' from t_stud_j524;
select 'grant select on vi'||sno||' to u'||sno||';' from t_stud_j524;

select 'drop view vi'||sno||';' from t_stud_j524 where sno=mno;
select 'drop view vib'||sno||';' from t_stud_j524 where sno=mno;

select 'create view vib'||sno||' as select * from t_stud_j524 where mno='||sno||';' from t_stud_j524 where sno=mno;

select 'grant select on vib'||sno||' to u'||sno||';' from t_stud_j524 where sno=mno;

select 'drop view vi'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
select 'drop view vib'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;
select 'drop view viz'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;

select 'create view viz'||t_stud_j524.sno||' as select * from t_stud_j524 where majorno='||t_stud_j524.majorno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;

select 'grant select on viz'||t_stud_j524.sno||' to u'||t_stud_j524.sno||';' from t_stud_j524,t_major_j524 where t_stud_j524.majorno=t_major_j524.mno and t_stud_j524.sname=t_major_j524.mdean;

*/




spool off
set heading on
set feedback on
set echo on
set termout on
------------------------

@d:\users.txt
 
--普通学生仅查询自己
conn u0919170225/p0919170225
select * from u_j524.vi0919170225;
--班长可以查询自己和班级同学
conn u0919170112/p0919170112
select * from u_j524.vib0919170112;
--年级长可以查询自己和年同学
conn u0921170226/p0921170226
select * from u_j524.viz0921170226;


set heading off
set feedback off
set echo off
set termout off
spool d:\users.txt

--删除用户
select 'drop user u'||sno||' cascade;' from t_stud_j524;
spool off
set heading on
set feedback on
set echo on
set termout on

posted @ 2019-12-27 19:14 StarHai 阅读(1912) 评论(0) 编辑收藏举报

刷新页面返回顶部

StarHai

Oracle实验一 熟悉ORACLE环境并练习SQL操作