badEgg > 数据库实验 - oracle

数据库实验 - oracle

2019-10-23

数据库

数据库实验

选课的时候选了一门大型数据库，但是开学以来都没好好学习过 (当个人吧= =)，我本来以为以我原本的mysql与sqlserver的基础问题应该不大，但是oracle真是让我失策了，还有pl/sql令人头大，以下博客是自救过程中的学习与感悟

实验一

oracle安装

首先是Oracle的安装，用任何搜索引擎搜索oracle然后打开download页面找到11c以后的版本下载

但是下载之前需要注册登陆一系列吧啦吧啦的操作，所以我这个小机灵鬼发现一个小办法= =

把下载按钮看一下开发者模式的链接，然后把链接复制下来用迅雷，bittorrent之类的下载器下载

12c版本的，以下为链接

https://download.oracle.com/otn/nt/oracle12c/121020/winx64_12102_database_1of2.zip

https://download.oracle.com/otn/nt/oracle12c/121020/winx64_12102_database_2of2.zip

注意两个都要下载并解压到同一个文件夹，路径不带中文

双击setup.exe进行安装，后面按照自己的需要安装即可，有无法把握的细节可以自行搜索或者在留言栏留言

使用sqlplus

打开命令行控制台cmd，输入

1
sqlplus system/密码   在前面的安装过程中设置的密码，若未设置默认为manager

捕获

创建一个新用户：

1
create user username identified by password;

给用户授权：

1
grant resource, connect to username;

连接用户：

1
connect username/password

*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（专业编号）。

创建专业表代码如下

1
2
3
4
5
6
7
8
create table major_J314 (
    MajorNo char(20) primary key,
    MNAME varchar(50),
    loc varchar(20),
    mdean varchar(20),
    constraint mj_loc
    	check (loc IN ('主校区', '南校区', '新校区', '铁道校区', '湘雅校区'))
);

测试check语句是否生效

1
2
insert into major_J314 values('02', '计算机科学与技术', '123', '张祖平');   --违反条例
insert into major_J314 values('02', '计算机科学与技术', '南校区', '张祖平'); --创建成功

注意，在插入过程中如果发现对表空间 ‘USERS’ 无权限，那么在system的权限下执行语句

1
alter user username quota unlimited on users

创建学生表代码如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table stud_J314 (
    SNO varchar(20) primary key,
    SNAME varchar(20),
    SEX varchar(5),
    TEL varchar(20),
    E_MAIL varchar(20),
    birthday date,
    MNO char(20),
    MajorNo char(20),
    constraint sj_sex
    	check (sex IN ('男','女','其他')),
    constraint sj_E_MAIL
    	check (REGEXP_LIKE (E_MAIL,'[a-zA-Z0-9._%-]+@[a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}')),
    constraint sj_birthday
    	check (to_char(birthday, 'yyyymmdd') > '1999-07-31'),
    constraint sj_MNO
    	foreign key (MNO) references stud_J314(SNO),
    constraint sj_MajorNo
    	foreign key (MajorNo) references major_J314(MajorNo),
    constraint sj_sno
    	check (substr(SNO, 3, 2) = MajorNo)
);

测试成功用例

1
2
insert into major_J314 values('02', '计算机科学与技术', '南校区', '张祖平');
insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');

这个表的创建比上面的表明显困难，因为添加了更多的约束，我把约束与测试拿出来一个个分析

  1. SEX性别必须是’男’,’女’,’其他’中的一个，这个我们在上一个表中已经实现

    1
    2
    insert into stud_J314 values ('0902170314', '颜灿', '1', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');  --违反检查约束条件SJ_SEX
    insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');  --已创建一行

  2. E_MAIL需要约束成XXX@XXX.XXX的格式，为了更加精确，我用了oracle允许的正则表达式[a-zA-Z0-9.%-]+@[a-zA-Z0-9.%-]+.[a-zA-Z]{2,4}来对格式进行规范

    1
    insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');  --违反检查约束条件SJ_E_MAIL

  3. MNO与MajorNo需要添加外键，MajorNo直接与之前的Major表关联成为外键，但是MNO我思来想去还是觉得他应该成为自己的表的主键的外键。

    注意

    当MajorNo与自己的主键关联时，也就是说插入班级的每个数据之前必须先插入班长的数据！！！否则就会出现外键找不到父项关键字的报错！！！

    1
    insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170313', '02');  --未找到父项关键字

  4. 然后就是生日的约束，这里需要用到 date 的数据类型，以及char与date之间的转化to_char(birthday, ‘yyyymmdd’)，to_date(birthday, ‘YYYYMMDD’)。

    1
    insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq.com', to_date('19981010', 'YYYYMMDD'), '0902170314', '02');  --违反检查约束条件SJ_BIRTHDAY

  5. 学号的第3，4位为MajorNo，所以我们需要截断字符串，需要用到方法substr(字符串, 第几个字符, 截取几个字符)

    1
    insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '03');  --违反检查约束条件SJ_SNO

插入样本数据

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

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

根据上面分析的约束，我们需要先插入没有外键的表 major_U314 

1
2
3
insert into major_J314 values('02', '计算机科学与技术', '南校区', '张祖平');
insert into major_J314 values('03', '物联网工程', '南校区', '张祖平');
insert into major_J314 values('04', '数据科学与大数据技术', '南校区', '张祖平');

下一个插入的就是学生表，但是注意了，学生表中由于约束到的是自己的主键，需要先插入班长的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
--班长学生
insert into stud_J314 values ('0902170314', '颜灿', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0903170314', 'a', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '03');
insert into stud_J314 values ('0904170314', 'b', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
--非班长学生
insert into stud_J314 values ('0902170101', '1', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170102', '2', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170103', '3', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170104', '4', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170105', '5', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170106', '6', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170107', '7', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170108', '8', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');
insert into stud_J314 values ('0902170109', '9', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0902170314', '02');

insert into stud_J314 values ('0903170101', '1', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170102', '2', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170103', '3', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170104', '4', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170105', '5', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170106', '6', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170107', '7', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170108', '8', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');
insert into stud_J314 values ('0903170109', '9', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0903170314', '03');

insert into stud_J314 values ('0904170101', '1', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170102', '2', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170103', '3', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170104', '4', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170105', '5', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170106', '6', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170107', '7', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170108', '8', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
insert into stud_J314 values ('0904170109', '9', '男', '1', '123@qq.com', to_date('20001010', 'YYYYMMDD'), '0904170314', '04');
--其他以此类推

实现权限控制

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

首先我要先把用户建立好

1
2
3
4
5
6
7
select MajorNo from major_J314;
select MNAME from major_J314;                           //以上两行用来看= =
select 'create user u'||MajorNo||' identified by p'||MajorNo||';' from major_J314;
select 'drop user u'||MajorNo||';' from major_J314;        //删除用户，这里是写了备用
select SNO from stud_J314;
select 'create user u'||SNO||' identified by p'||SNO||';' from stud_J314;
select 'drop user u'||SNO||';' from stud_J314;        //删除用户，这里是写了备用

划重点 这里的select ‘’语句只有选择出所有合适语句的作用而不会运行

那么，我们就要先把语句选出来存起来再全部运行

先建立一个txt或sql文件，写入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
clear screen
conn U_J314/YcYyj1234 连接用户名与密码
spool off
set heading off
set echo off
set feedback off
spool d:\blog\user.txt选出后存储的地址文件，可以用txt
---
选择语句，注意，只有选择语句会留下记录
---
spool off
set heading on
set echo on
set feedback on
spool d:\blog\demo.txt运行过程的记录，同样用令一个文件记录
@d:\blog\user.txt   运行user的内容
spool off

但是，在我实际创建的过程中发现一个问题 — 权限不足= =

创建用户就需要系统管理员的权限了

1
grant dba to U_J314(这里用自己的用户名);

下一步是每个人的权限

1
2
select 'grant connect to u'||MajorNo||';' from major_J314;
select 'grant connect to u'||SNO||';' from stud_J314;

建立一个视图并给予视图的权限

1
2
3
4
5
6
create view V_J314 
as 
select * from stud_J314 where 'u'||SNO=user or 'u'||MNO=user or 'u'||majorNo=user;
grant select on V_J314 to public;
--测试数据
select * from U_J314.V_J314

心态崩了，真就没办法过啊

补充

之前和个沙雕一样，想一想不如每个人建立一个视图

代码如下

1
2
3
4
5
6
7
8
select 'drop view V_'||SNO||';' from stud_J314;
select 'create view V_'||SNO||' as select * from stud_J314 where SNO = '||SNO||' or MNO = '||SNO||';' from stud_J314;
select 'grant select on V_'||SNO||' to u'||SNO||';' from stud_J314;
select 'drop view V_'||MajorNo||';' from major_J314;
select 'create view V_'||MajorNo||' as select * from stud_J314 where MajorNo='||MajorNo||';' from major_J314;
select 'grant select on V_'||MajorNo||' to u'||MajorNo||';' from major_J314;
测试数据
select * from U_J314.V_(学号或年级号)

测试通过，任务完成: - )