oracle实验内容.doc

课程名称：数据库原理实验
要求：每个同学按时并且独立完成实验报告，前几个实验按照实验指导书容易完成，有些实验不需要2个学时完成，所以各位同学完成一个实验以后，立刻做下一个实验。最后一个实验涉及到数据库应用程序开发，4个学时肯定不能完成，所以尽快完成前面的实验，多留些时间做实验八。
编号
实验名称
学时
实验一
Oracle数据库的创建与删除和数据表及对象的创建
2
实验二
SQL数据查询
2
实验三
SQL语言
2
实验四
事务管理（按照实验指导书实验七的内容完成），该实验可以和实验五、实验六一起完成；实验过程中，首先找到D盘的oracle安装程序安装oracle，然后进行实验。
10g三个用户的默认密码
sys：change_on_install
system：manager
scott：tiger（默认是lock的，需解锁）
2
实验五
用户权限管理（按照实验指导书实验八做）
2
实验六
数据备份与恢复（按照实验指导书实验九做）
2
实验七
Oracle数据库对象（按照实验指导书实验十做）
2
实验八
数据库系统设计综合实验（自选项目及开发语言）
采用C/S或B/S开发模式，开发语言任选，后台数据库可以采用Oracle、Sql server或MySQL。要求能够完成基本的数据查找、插入、删除和修改功能。
4
Oracle连接方法
使用SQL developer方法
1. 从开始菜单进入SQL developer，选择路径D:\Winapps\jdk160\bin\java.exe
2. 新建连接，连接名称自定义，内容任意。
3. 用户名称为：jsj+班级号+二位的顺序号，密码为大写的JSJ+班级号+二位的顺序号。例如084班序号为01的同学，用户名称是jsj08401，口令为JSJ08401
4. 主机名填写IP地址：202.192.35.252，端口号默认为1521，SID：orcl
5. 测试连接成功，选择连接。
6. 输入SQL并运行。
使用SQLPLUS的方法（其中参数是一样的）
从开始菜单中，运行cmd命令提示符，在命令提示符下输入以下信息：
Sqlplus 用户名/密码@服务器IP:端口号/数据库名称
例如：Sqlplus jsj08401/JSJ08401@202.192.35.252:1521/orcl

实验（实验二、实验三）前准备
现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，每个单位成员可以借多本书，单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。
1、 拷贝代码运行，建立表格Reader
create table Reader
(
RNO varchar2(4) primary key,
Rname varchar2(10) not null,
Rsex varchar2(2),
Rage integer,
Rboss varchar2(10),
Raddress varchar2(30)
)
2、 拷贝代码运行，向Reader表格中插入十条数据
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R001','张三',20,'男','李四','416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R002','张三',35,'女',null,'417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R003','李四',30,'男',null,'416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R004','王五',20,'男',null,'417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R005','马六',40,'男',null,'416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R006','刘三',20,'男',null,'417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R007','王四',40,'男','李四','416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R008','李小龙',20,'男','李四','417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R009','王小倩',40,'男','李四','416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R010','王一小',20,'男','李四','417');
3、 拷贝代码运行，建立表格Book
create table Book
(
BNO varchar2(4),
Bname varchar2(50) not null,
Bauthor varchar2(50),
Bpress varchar2(50),
Bprice numeric(6,2),
primary key(BNO)
)
4、 拷贝代码运行，向Book表格中插入5条数据
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B001','严蔚敏','数据结构','清华大学出版社',null);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B002','唐发根','数据结构','北航出版社',24);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B003','王珊','数据库原理','高等教育出版社',40);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B004','张飞','数据库原理','清华大学出版社',30);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B005','王珊','数据库原理','清华大学出版社',null);
5、 拷贝代码运行，建立表格RB
create table RB
(
RNO varchar2(4),
BNO varchar2(4),
RBdate date default sysdate,
primary key(RNO,BNO),
foreign key (RNO) references Reader(RNO),
foreign key (BNO) references Book(BNO)
)
6、 拷贝代码运行，向RB表格中插入13条数据
insert into RB (RNO,BNO) values ('R001','B001');
insert into RB (RNO,BNO) values ('R001','B002');
insert into RB (RNO,BNO) values ('R001','B004');
insert into RB (RNO,BNO) values ('R002','B001');
insert into RB (RNO,BNO) values ('R003','B001');
insert into RB (RNO,BNO) values ('R004','B001');
insert into RB (RNO,BNO) values ('R004','B002');
insert into RB (RNO,BNO) values ('R005','B001');
insert into RB (RNO,BNO) values ('R006','B001');
insert into RB (RNO,BNO) values ('R006','B003');
insert into RB (RNO,BNO) values ('R006','B005');
insert into RB (RNO,BNO) values ('R006','B002');
insert into RB (RNO,BNO) values ('R006','B004');
预备知识
以下为查询READER表中创建的约束类型，其中constraint_type为约束类型，该属性值的取值Type Code如下表所示。
select table_name,constraint_name,constraint_type from user_constraints where table_name =’READER’
Type Code
Type Description
Acts On Level
C
Check on a table
Column
O
Read Only on a view
Object
P
Primary Key
Object
R
Referential AKA Foreign Key
Column
U
Unique Key
Column
V
Check Option on a view
Object

实验二 SQL数据查询
SQL查询实验（以下查询在Reader、Book、RB表中完成）
实验目的
熟练掌握使用SQL查询语言。完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）。
实验内容
单表查询
1、 查询全体员工的姓名和出生年份；
2、 查询工作在416房间的员工的所有信息；
3、 查询年龄在30到50岁之间的员工姓名、年龄；
4、 查询借了书的员工的编号，排除相同的元素；
5、 查询名字中包含字“小”的员工姓名、办公地点；
6、 查询名字中第二个字为“小”的员工姓名、办公地点；
7、 查询所有不姓“李”的员工姓名、性别；
8、 查询Book表中价格不为空值的书名、出版社；
9、 查询清华大学出版社和高等教育出版社出版的所有书籍，按照出版社降序、书名升序排列；
10、 查询员工的总人数
11、 查询借了书的员工的人数；
12、 查询“张三”所借图书的数量；
13、 查询最贵的书籍的作者姓名；
14、 查询Book表中书籍的平均价格，查询结果说明了什么；
15、 查询book中包含的各个出版社及其出版书籍的数量；
复合查询
1、 查询每个员工及其借书情况，列出员工编号、姓名和借书日期
2、 查询每个员工及其借书所有字段（没有借书的员工也列出来）；（用左外连接）
3、 查询与“李小龙”工作在同一个办公室的员工信息（用两种方法：自身连接、子查询）；
4、 查询借阅了“数据库原理”并且年龄在30岁以上的员工所有信息（两种方法：连接查询、子查询）
5、 查询小于或等于同一办公室中成员的平均年龄的员工编号、姓名、年龄。
6、 查询比所有数据库原理价格都高、并且不是清华大学出版社出版的书籍的信息；
7、 查询借阅了B001的员工的编号、姓名、办公室；
8、 查询没有借阅B001的员工的编号、姓名、办公室；
9、 查询借阅了所有书籍的员工的姓名；
10、 查询至少借阅了编号为R004的员工借阅的全部书籍的员工姓名、性别、办公室
11、 查询年龄大于30岁或者工作在416的员工信息（用集合查询完成）
12、 查询年龄大于30岁并且工作不在416的员工信息（用集合查询完成）
实验报告要求
写出实验步骤及相关的SQL语句即可。
实验三 SQL语言
实验目的
熟悉基本表的修改及删除、熟悉索引的创建和删除、熟悉视图的创建、更新和删除。熟悉各类更新操作（插入数据，修改数据，删除数据）。
实验内容：
表结构修改、数据插入和删除
1、 创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
create table reader2 as select * from reader; --创建表格reader2
desc reader; desc reader2;--查看两个表格的结构是否相同
2、 分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
update reader set RNO='R001' where Rname='张三';
update reader2 set RNO='R001' where Rname='张三';
insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,'lisi',null,null,null,null);
3、 用以下语句查看Reader、Reader2中的约束是否相同
select table_name,constraint_name,constraint_type from user_constraints where table_name like 'REA%';--注意REA必须为大写
4、 删除Reader2表格；
5、 用语句在Reader中测试是否可以把Rboss建立成外码，引用Reader中的Rname属性；
6、 修改Reader表结构，把Rboss建立成外码，引用Reader中的RNO属性；（首先用update语句设置Reader的Rboss属性为空值，然后修改Rboss属性，使其与RNO具有相同的数据类型和长度）；
7、 更新Reader表中的Rboss属性。记录R001的Rboss保持为空，R002、R003的Rboss设置为R001，R004、R005的Rboss属性设置为R002，依此类推，形成类似完全二叉树结构；
8、 为表Reader添加check约束，使性别的取值只能为“男”或者“女”；
9、 执行以下语句，创建表格reader2；
create table reader2 as select * from reader; --创建表格reader2
10、 用以下语句查看Reader、Reader2中的约束是否相同，如有不同，把Reader2修改成与Reader具有相同约束的结构；
select table_name,constraint_name,constraint_type from user_constraints where table_name like 'REA%';--注意REA必须为大写
11、 直接Copy前面Book和RB的定义，建立Book2和RB2表（注意RB2的外码引用的表格为Book2和Reader2）。把Book和RB中的数据分别插入Book2和RB2表，至此，实验中有两份同样的数据。
12、 执行insert into RB2(RNO,BNO) values(‘R010’,’B005’);
13、 写出删除Reader2表格中编号为R010的员工，如果执行错误，分析错误原因
14、 想办法删除Reader2中的R010员工；
15、 为Reader2表添加一个属性列“出生年份”，名为Rbirthday，整数；
16、 对于Reader2表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday，使关系模式符合第三范式要求；
17、 修改Reader2表格的Raddress属性，使其长度为50，数据类型不变；
18、 修改book表的Bprice属性，使其值得范围在10到100之间；
19、 修改Reader表的Rage属性，使其值得范围为16到60之间。
20、 试试是否可以删除Reader2表，使用CASCADE是否可以删除？
21、 删除Reader2、Book2和RB2表。
索引的建立与删除
1、 为Reader表格的Rname建立UNIQUE索引（可能需要先修改表格数据，再建索引）
2、 用select语句查看Book表的记录顺序，为Bname建立cluster索引，比较索引建立前后记录顺序是否有差异；
3、 为RB表格的RBdate建立普通索引。
4、 删除为RB表格建立的普通索引。
触发器（可不做，时间多再做）
参考PLSQL帮助文档，试试建立一个触发器。在用户执行删除某个员工的时候触发，该触发器的功能是先删除RB中该员工的借书记录，然后删除Reader中该员工记录。
视图
1、 建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息
2、 从V416中查询年龄大于30的员工信息
3、 向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；
4、 建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；
5、 向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；
6、 通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？
7、 在视图V417上建立所有女员工信息的视图FV417，查询视图结果
8、 删除视图FV417中没有借阅图书的员工信息
9、 建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？
10、 删除视图V417
实验报告要求
写出实验步骤及相关的SQL语句即可。