oracle图书操作、sql语句查询+授权、视图、索引、表操作
一、创建一张表book,表结构为(总编号,分类号,书名,作者,出版单位,单价),插入若干记录二、创建一张表reader,表结构为(借书证号,单位,姓名,性别,职称,地址)三、创建一张表borrow,表结构为(借书证号,总编号,借书日期)四、完成下面的查询1. 找出姓李的读者姓名及其所在单位2. 查找高等教育出版社的所有图书及单价,结果按单价降序排列3. 查找价格在10—20之间的图书各类(即去掉重复),结果按单价升序排列4. 查找藏书中,高等教育出版社和科学出版社的图书种类及作者5. 查找书名以“计算机”打头的所有图书及作者6. 查询图书中带有“基础”二字的图书及作者7. 检索同时借阅了总编号为112266和449901两本书的借书证号。(表的自连接的使用)==8. 查找所有借阅了图书的读者姓名及所在单位==9. 找出李某所借的所有图书的书名及借书日期10. 查找价格在22元以上已借出的图书,结果按单价升序排列11. 查询1997年10月以后借书的读者借书证号、姓名和单位 ==(to_char()的使用)==12. 找出借阅了“oracle应用”一书的借书证号13. 找出与赵正义在同一天借书的读者姓名,所在单位及日期14. 查询1997年7月以后没有借书的读者借书证号、姓名和单位 ==not in的使用==15. 找出藏书中比高等教育出版社的所有图书单价更高的书籍16. 找出藏书中所有与“数据库导论”或“数据库基础”在同一个出版单位出版的书17. 求图书馆所有藏书的总册数18. 求科学出版社图书的最高价格、最低价格、平均价格19. 求信息系当前借阅图书的读者人数20. 求出各个出版社图书的最高价格、最低价格和册数21. 分别找出各个单位当前借阅图书的读者人次22. 找出藏书中各个出版单位的册数、价值总额==23. 找出当前至少借阅了2本图书的读者及所在单位(关联子查询)====24. 分别找出借书人次超过1人的单位及人次数==25. 查询经济系是否还清所有借书。如果还清,显示该系所有读者的姓名、所在单位和职称26. 查询信息系没有借书的读者五、其它操作1. 将查询和更新图书表的权限给所有用户2. 建立计算机系的读者视图jsj_view3. 建立各个单位当前借阅图书情况的简单统计视图dr_view4. 为borrow表建立一个按借书证号升序的索引,索引名为jsno5. 修改总编号为554433图书的作者名和出版单位名称6. 将所有图书的单价上调5%7. 借书证号为112的读者所借总编号为446988的图书已归还,删除该借阅记录一、创建一张表book,表结构为(总编号,分类号,书名,作者,出版单位,单价),插入若干记录
create table book(bid number(6),classify char(6),bname varchar2(20),author varchar2(10),press varchar2(30),price number(4,2));insert into book values(445501,'TP3/12','数据库导论','王利','科学出版社',17.90);insert into book values(445502,'TP3/12','数据库导论','王利','科学出版社',17.90);insert into book values(445503,'TP3/12','数据库导论','王利','科学出版社',17.90);insert into book values(332211,'TP5/10','计算机基础','李伟','高等教育出版社',18.00);insert into book values(112266,'TP3/12','Oracle','张三','电子工业出版社',23.60);insert into book values(665544,'TS7/21','高等数学','刘明','高等教育出版社',20.00);insert into book values(114455,'TR9/12','线性代数','孙业','北京大学出版社',20.80);insert into book values(113388,'TR7/90','大学英语','胡玲','清华大学出版社',12.50);insert into book values(446601,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into book values(446602,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into book values(446603,'TP4/13','数据库基础','马凌云','人民邮电出版社',22.50);insert into book values(449901,'TP4/14','Oracle应用','周虹','科学出版社',32.70);insert into book values(449902,'TP4/14','Oracle应用','周虹','科学出版社',32.70);insert into book values(448801,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);insert into book values(448802,'TP4/15','计算机网络','黄力钧','高等教育出版社',21.80);
二、创建一张表reader,表结构为(借书证号,单位,姓名,性别,职称,地址)
create table reader(rid number(3),unit varchar2(15),rname varchar2(10),sex char(3),zhich varchar2(9),addr varchar2(12));insert into reader values(111,'信息系','王小得','女','教授','1号楼424');insert into reader values(112,'财会系','李立','男','副教授','2号楼316');insert into reader values(113,'经济系','张三','男','讲师','3号楼105');insert into reader values(114,'信息系','周华文','男','讲师','1号楼316');insert into reader values(115,'信息系','赵正义','男','工程师','1号楼224');insert into reader values(116,'信息系','李明','男','副教授','1号楼318');insert into reader values(117,'计算机系','李小峰','男','助教','1号楼214');insert into reader values(118,'计算机系','许鹏飞','男','助工','1号楼216');insert into reader values(119,'计算机系','刘大龙','男','教授','1号楼318');insert into reader values(120,'国际贸易','李雪','女','副教授','4号楼506');insert into reader values(121,'国际贸易','张小红','女','讲师','4号楼510');insert into reader values(122,'国际贸易','王纯','女','讲师','4号楼512');insert into reader values(123,'财会系','沈小霞','女','助教','2号楼202');insert into reader values(124,'财会系','朱海','男','讲师','2号楼210');insert into reader values(125,'财会系','马英明','男','副教授','2号楼212');
三、创建一张表borrow,表结构为(借书证号,总编号,借书日期)
create table borrow(rid number(3),bid number(6),bbdate date);alter session set nls_date_format='yy-mm--dd';insert into borrow values(112,445501,'97-03-19');insert into borrow values(125,332211,'97-02-12');insert into borrow values(111,445503,'95-08-21');insert into borrow values(112,112266,'07-03-14');insert into borrow values(114,665544,'97-10-21');insert into borrow values(120,114455,'97-11-02');insert into borrow values(120,118801,'97-10-18');insert into borrow values(119,446603,'87-12-12');insert into borrow values(112,449901,'97-10-23');insert into borrow values(115,449902,'10-03-17');insert into borrow values(118,118802,'97-09-10');
四、完成下面的查询
提示:做题之前,先理解好三个表之间的关系:通过borrow将book和reader连接起来
1. 找出姓李的读者姓名及其所在单位
select rname,unit from reader where rname like '李%';
2. 查找高等教育出版社的所有图书及单价,结果按单价降序排列
select bname,price,press from book where press='高等教育出版社' order by price desc;
3. 查找价格在10—20之间的图书各类(即去掉重复),结果按单价升序排列
select distinct classify,press,price from book where price between 10 and 20 order by price;
4. 查找藏书中,高等教育出版社和科学出版社的图书种类及作者
select distinct classify,author,press from book where press in('高等教育出版社','科学出版社') ;
5. 查找书名以“计算机”打头的所有图书及作者
select DISTINCT bname,author from book where bname like '计算机%';
6. 查询图书中带有“基础”二字的图书及作者
select distinct bname,author from book where bname like '%基础%';
7. 检索同时借阅了总编号为112266和449901两本书的借书证号。(表的自连接的使用)
select b1.rid,b1.bid,b2.bid from borrow b1 join borrow b2 on b1.rid=b2.rid where b1.bid=112266 and b2.bid=449901;
将borrow表通过借书证号自连接,然后查找两列总编号分别是112266和449901的
8. 查找所有借阅了图书的读者姓名及所在单位
select rname,unit from reader r where rid in(select rid from borrow where rid=r.rid);select DISTINCT rname,unit from reader r,borrow b where r.rid= b.rid;select rname,unit from reader where rid in(select rid from borrow);
9. 找出李某所借的所有图书的书名及借书日期
select rname,bname,bbdate from book b join borrow bw on b.bid= bw.bid join reader r on r.rid= bw.rid where rname like '李%';select rname,bname,bbdate from book b,borrow bw, reader r where b.bid=bw.bid and bw.rid= r.rid and rname like '李%';
10. 查找价格在22元以上已借出的图书,结果按单价升序排列
select rid,b.* from book b,borrow bw where b.bid=bw.bid and price>22 order by price;
11. 查询1997年10月以后借书的读者借书证号、姓名和单位(to_char()的使用)
select r.rid,rname,unit,bbdate from reader r join borrow bw on r.rid= bw.rid where to_char(bbdate,'yy-mm')>'97-10';
12. 找出借阅了“oracle应用”一书的借书证号
select rid,bname from borrow bw,book b where bw.bid=b.bid and bname='Oracle应用';
13. 找出与赵正义在同一天借书的读者姓名,所在单位及日期
update borrow set bbdate='97-10-03' where rid=111;select rname,unit,bbdate from borrow bw, reader r where bw.rid= r.rid and bbdate in(select bbdate from borrow bw, reader r where bw.rid= r.rid and rname='赵正义');
14. 查询1997年7月以后没有借书的读者借书证号、姓名和单位not in的使用
select r.rid,rname,unit from reader r join borrow bw on r.rid= bw.ridwhere r.rid not in(select rid from borrow where bbdate>'97-07-01' ); select rid,rname,unit from reader where rid not in(select rid from borrow where bbdate>'97-07-01');
15. 找出藏书中比高等教育出版社的所有图书单价更高的书籍
select * from book where price>all(select price from book where press='高等教育出版社');select * from book where price>(select max(price) from book where press='高等教育出版社');
16. 找出藏书中所有与“数据库导论”或“数据库基础”在同一个出版单位出版的书
select * from book where press in(select DISTINCT press from book where bname in('数据库导论','数据库基础'));
17. 求图书馆所有藏书的总册数
select count(*) 总册数from book;
18. 求科学出版社图书的最高价格、最低价格、平均价格
select max(price),min(price),avg(price) from book where press='科学出版社' ;
19. 求信息系当前借阅图书的读者人数
select count(*) from borrow bw, reader r where bw.rid= r.rid and unit='信息系';select count(*) from borrow where rid in(select rid from reader where unit='信息系');
20. 求出各个出版社图书的最高价格、最低价格和册数
select press,max(price),min(price),count(*) from book group by press;
21. 分别找出各个单位当前借阅图书的读者人次
select unit,count(*) from borrow bw, reader r where bw.rid= r.rid group by unit;
22. 找出藏书中各个出版单位的册数、价值总额
select press,count(*),sum(price) from book group by press;
23. 找出当前至少借阅了2本图书的读者及所在单位(关联子查询)
select rname,unit from reader r where 2<=(select count(rid) from borrow where rid=r.rid);
24. 分别找出借书人次超过1人的单位及人次数
select unit,count(*) from borrow bw join reader r on bw.rid= r.rid GROUP by unit HAVING count(*)>1;
25. 查询经济系是否还清所有借书。如果还清,显示该系所有读者的姓名、所在单位和职称
量词:exists 存在的意思。可以用在where子句中,如果子查询结果非空,则满足条件。
select rname,unit,zhich from reader r where unit='经济系' andnot exists(select rid from borrow where rid= r.rid and unit='经济系');
26. 查询信息系没有借书的读者
select r.* from reader r where unit='信息系' and rid not in(select rid from borrow);
五、其它操作
1. 将查询和更新图书表的权限给所有用户
先以管理员身份登录,给xiaohong授create view权限,再以xiaohong身份登录,创建视图
grant select,update on book to public;
2. 建立计算机系的读者视图jsj_view
create or replace jsj_view asselect * from reader where unit='计算机系' with CHECK OPTION;
3. 建立各个单位当前借阅图书情况的简单统计视图dr_view
create or replace view dr_view asselect unit,count(*) 借阅人次 from reader r,borrow bw where r.rid=bw.rid group by unit;
4. 为borrow表建立一个按借书证号升序的索引,索引名为jsno
create index jsno on borrow(rid);
5. 修改总编号为554433图书的作者名和出版单位名称
update book set bname='王为民', press='电子工业出版社' where bid=554433;
6. 将所有图书的单价上调5%
update book set price=price*1.05;
7. 借书证号为112的读者所借总编号为446988的图书已归还,删除该借阅记录
delete from borrow where bid=446988 and rid=112;