实验目的
熟悉并掌握创建表，插入记录，查询记录，删除记录，修改记录。
创建索引，删除索引。
创建视图，使用视图，删除视图。
实验内容
仍然基于上次课程建立的小型图书借阅系统。如果使用实验室的机器完成实验，首先重做上次课的步骤1-4建立相应数据库。
实验步骤
（以用户CC的身份建立连接，并在此连接下执行后面的操作。）
1、 查询记录：在Reader表中查询直接上司是“李四”的员工的名字
SELECT rname from reader where rboss=’李四’;
2、 修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
update reader SET raddress='420' where rboss='李四';
3、 删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去
此处得先删除RB表中没有直接上司的员工的借书记录，才能删除Reader表中该员工记录！
delete from rb where rno in (select rno from reader where rboss is null);
delete from reader where Rboss is null;
4、 删去数据表：把整个Reader表删去
drop table reader cascade constraints;
5、 重新执行第一、二步，即建立数据表、插入数据。
DROP TABLE RB;
DROP TABLE BOOK;
create table Reader
(
RNO varchar2(4) primary key,
Rname varchar2(10) not null,
Rsex varchar2(3),
Rage integer,
Rboss varchar2(10),
Raddress varchar2(30)
) ;
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');
create table Book
(
BNO varchar2(4),
Bname varchar2(50) not null,
Bauthor varchar2(50),
Bpress varchar2(50),
Bprice numeric(6,2),
primary key(BNO)
);
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);
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)
);
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');
6、 创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
create table reader2 as select * from reader;
查看这两个数据表的结构、内容是否相同？分别执行：
desc reader;
desc reader2;
通过操作可知：两个表的内容是一样的，但是结构中reader2中属性rno没有非空约束。Reader中rno是主码，一定不能为null。
也可以在可视化界面查看这两个表所附带的约束,如截图所示：
7、 分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
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);
update reader set RNO='R001' where Rname='张三';
执行失败：因为违反了表的完整性约束。Reader表中rno是主码，主码能唯一标识一个元组，由于表中有已经有一个“张三“了，如果“张三”添加成功的话，那么rno就不再能唯一标识一个元组，违反了主码的性质。
update reader2 set RNO='R001' where Rname='张三';
执行成功：结果是两个张三的rno都变成：R001，reader2中rno不是主码，因此，两个员工的rno可以相同，所以可以成功执行。
insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress)
values(null,'lisi',null,null,null,null);
执行成功：由上面查看表的结构约束条件可知RNO的属性没有了非空的约束，或者说rno不是主码了，reader2表中仅有Rname属性有非空约束，因此此语句可以执行，成功插入数据。
8、 删除Reader2表格；
drop table reader2 cascade constraints;
以下为查询READER表中创建的约束类型，其中constraint_type为约束类型，该属性值的取值Type Code如下表所示。（可以根据上课所讲的实体完整性、参照完整性、用户自定义完整性理解以下约束类型；直接在SQL DEVELOPER中双击表格也可以查看在表上所建立的约束）
| 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 |
select table_name,constraint_name,constraint_type from user_constraints where table_name ='READER';
1、 执行
insert into RB(RNO,BNO) values('R010','B005');
2、 写出删除Reader表格中编号为’R010’的员工，如果执行错误，分析错误原因。
执行错误：原因：刚刚在RB表中添加了rno为“R010”的员工的借书记录， RB表中记录了员工的借阅书籍记录，RB表中的rno属性参照的是READER表中的rno列，如果删去READER表中编号为“R010”的员工，则会使RB表不满足其表级完整性约束条件。
3、 想办法删除Reader中的’R010’员工；
方法一：
先把RB中所有’R010’的借书记录都删掉
先把RB中所有’R010’的借书记录都删掉
执行以下语句：
delete from rb where Rno=’R010’；
delete from reader where Rno=’R010’;
方法二：修改数据表READER的结构，允许级联删除
alter table rb add constraint MYFK foreign key(rno) references reader(rno) on delete cascade;
查看rb约束条件：
可见，首先要先删去原来的约束，因建表时没给该约束命名，故应查这个约束的名字，才能删去。注意，这个名字由系统自动赋值，不同的环境下约束名可能不同）：
alter table rb drop constraint sys_c005544;
alter table rb add constraint MYFK foreign key(rno) references reader(rno) on delete cascade;
再执行：
delete from reader where rno='R010';
顺利删去一行，打开RB表，发现前面插入的(‘R010’,‘B005’)也不在了。
4、 为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数；
alter table reader add Rbirthday int;
5、 对于Reader表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday列，使关系模式符合第三范式要求；
alter table reader drop column Rbirthday;
6、 修改Reader表格的Raddress属性，使其长度为50，数据类型不变；
alter table reader modify(Raddress varchar2(50) );
7、 修改book表的Bprice属性，使其值得范围在10到100之间；
alter table book add constraint bp check (Bprice BETWEEN 10 and 100);
8、 修改Reader表的Rage属性，使其值得范围为16到60之间。
alter table reader add constraint bk
check(Rage between 16 and 60);
9、 试试是否可以删除Reader表，使用CASCADE是否可以删除？
drop table reader CASCADE;
删除Reader表，使用CASCADE不可以删除。
执行：
drop table reader CASCADE constraints;
才能成功删除！
10、 删除Reader、Book和RB表。
drop table rb;
drop table book;
索引的建立与删除
1、 重新执行前面的代码创建三个数据表并插入数据；
create table Reader
(
RNO varchar2(4) primary key,
Rname varchar2(10) not null,
Rsex varchar2(3),
Rage integer,
Rboss varchar2(10),
Raddress varchar2(30)
) ;
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');
create table Book
(
BNO varchar2(4),
Bname varchar2(50) not null,
Bauthor varchar2(50),
Bpress varchar2(50),
Bprice numeric(6,2),
primary key(BNO)
);
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);
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)
);
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');
2、 为Reader表格的Rname建立UNIQUE索引
create unique index myindex on reader(rname);
报告出错："cannot CREATE UNIQUE INDEX; duplicate keys found"
如何修改表格数据，再建索引？
把编号为“R001”或者“R002”员工名字改为其他不同名的就可以了，改动一个就好。或者两个都改成跟其他员工名字不同的，且修改的两个员工名字改完后名字也不相同。
先执行：
update reader set rname='李华' where Rno='R001';
再执行：
create unique index myindex on reader(rname);
myindex索引即创建成功！
3、 删除索引。
drop index myindex;
视图
1、 如果上面的运行是在CC的连接中，则需要回到在sysdba 的连接中，执行：
grant resource, connect, DBA to c##cc;
否则系统显示没有创建视图的权限。执行完之后再回到CC的连接：
在ORACLE SQL Developer 的左上方：
2、 建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息
CREATE VIEW V416 as select rno,rname,rage from reader where raddress='416'
确认该视图的存在：
select * from v416
3、 从V416中查询年龄大于30的员工信息
select * from v416 where rage>30;
4、 向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；
insert into v416( rno,rname,rage) values('R999','new reader',99);
select * from reader
select * from v416
插入成功了吗？为什么通过视图插入的纪录在视图中看不见？
答：插入成功了，但是，在reader表中可以看到新插入的数据，在v416中却看不到新插入的数据。
原因：刚插入的数据条件不满足视图v416的条件！
5、 建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；
CREATE VIEW V417 as select rno,rname,rsex,rage,raddress from reader where raddress='417' with check option;
select * from v417
6、 向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；
insert into v417( rno,rname,rage) values('R777','417 reader',66);
执行不成功。
drop view v417;
CREATE VIEW V417 as select rno,rname,rsex,rage,raddress from reader where raddress='417' with check option;
insert into v417( rno,rname,rage,raddress) values('R777','417 reader',66,'417');
执行成功。
7、 通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？
select * from v416;
delete from v416 where rname='王小倩';
select * from v416;
视图v416和基本表reader中均已无：王小倩，已删除成功！
8、 在视图V417上建立所有女员工信息的视图FV417，查询视图结果
CREATE VIEW FV417 as select * from V417 where RSEX='女';
select * from FV417;
9、 删除视图V417中没有借阅图书的员工信息
SELECT * FROM V417;
Delete from v417 where v417.rno not in (SELECT rno from rb);
SELECT * FROM V417;
10、 建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？
SELECT BNO,COUNT(*) FROM RB GROUP BY BNO;
CREATE VIEW GV(BNO,COUNTS) AS SELECT BNO,COUNT(*) FROM RB GROUP BY BNO;
SELECT * FROM GV;
不能向GV中插入数据！因为视图GV的内容并非数据，而是数据和相关数据统计结果。
11、 删除视图V417
drop view v417
4811
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
