MySQL实验三 数据查询二

第1关：多表查询

打开library数据库

第一题 根据读者(reader)和借阅(borrow)数据表，查询王颖珊的借阅记录，包括条形码txm、借阅日期jyrq、还书日期hsrq

第二题 根据图书(book)和借阅(borrow)数据表，查询李白全集被借阅的情况：包括读者证号dzzh、借阅日期jyrq、还书日期hsrq

第三题 根据读者(reader)、图书(book)和借阅(borrow)数据表查询没有被归还的借阅信息：包括读者证号dzzh、姓名xm、电话dhhm、条形码txm、书名sm、借阅日期jyrq 提示：通过isnull(表达式)可以判断表达式是否NULL值

 use library;
 #代码开始
 #第一题
 select txm,jyrq,hsrq from reader a, borrow b where a.dzzh=b.dzzh and xm='王颖珊';
 #第二题
 select dzzh,jyrq,hsrq from book join borrow on book.txm=borrow.txm where sm='李白全集';
 #第三题
 select a.dzzh,xm,dhhm,b.txm,sm,jyrq from reader a, borrow b, book c 
 where a.dzzh=b.dzzh and b.txm=c.txm and isnull(hsrq);
 #代码结束

 第2关：多表查询及统计分组

第一题： 统计每本书借阅的次数,显示书名和借阅次数（借阅次数命名为jycs),按借阅次数降序排列,借阅次数相同的按书名降序排列 （提示：borrow数据表的一条数据对应一次借阅）

第二题： 统计借阅次数在2次以上的图书的借阅的次数,显示书名和借阅次数，按借阅次数降序排列,借阅次数相同的按书名降序排列

第三题 统计每个出版社的图书的借阅次数,显示出版社的名称和借阅次数，按借阅次数降序排列,借阅次数相同的按出版社降序排列

第四题： 统计每位读者借阅的次数,显示姓名和借阅次数,按借阅次数降序排列，借阅次数相同的按姓名降序排列

第五题： 统计研究生读者借阅的次数,显示姓名和借阅次数，按借阅次数降序排列，借阅次数相同的按姓名降序排列

use library
 #代码开始
 #第一题
 select sm,count(*) as jycs from book,borrow where book.txm=borrow.txm
 group by sm order by jycs desc,sm desc;
 #第二题
 select sm,count(*) as jycs from book,borrow where book.txm=borrow.txm 
 group by sm having jycs>=2 order by jycs desc,sm desc;

 #第三题
 select cbs,count(*) as jycs from book,borrow where book.txm=borrow.txm 
 group by cbs order by jycs desc,sm desc;
 #第四题
 select xm,count(*) as jycs from reader,book,borrow 
 where book.txm=borrow.txm and reader.dzzh=borrow.dzzh
 group by xm order by jycs desc,xm desc;
 #第五题
 select xm,count(*) as jycs from reader,book,borrow 
 where book.txm=borrow.txm and reader.dzzh=borrow.dzzh and sf='研究生'
 group by xm order by jycs desc,xm desc;
 #代码结束

 第3关：子查询

第一题 查询与李白全集同一个出版社的图书的书名（不包括李白全集）

第二题 查询高于图书的平均售价(sj)的图书的书名和售价

第三题 查询售价最高的图书的条形码、书名和售价 第四题 查询售价最低的图书的条形码、书名和售价 

use library;
#代码开始
#答案1
select sm from book where cbs=(select cbs from book where sm='李白全集') and sm!='李白全集';
#答案2
select sm,sj from book where sj>(select avg(sj) from book);
#答案3
select txm,sm,sj from book where sj=(select max(sj) from book);
select txm,sm,sj from book where sj=(select min(sj) from book);
#代码结束

第4关：多表子查询

第一题 查询曾经借过图书的读者的读者证号和姓名

第二题 查询曾经没有被借阅的图书的条形码和书名

第三题 查询与孙思旺借过相同图书的读者的读者证号和姓名，按读者证号升序排列

第四题 查询借阅过李白全集的读者所借过的其他图书的书名 按书名升序排列

 use library;
#代码开始
#题目1
select dzzh,xm from reader where dzzh in (select dzzh from borrow);
#题目2
select txm,sm from book where txm not in (select txm from borrow);
#题目3
select dzzh,xm from reader where reader.dzzh in 
(select dzzh from borrow where txm in 
(select txm from borrow where borrow.dzzh=
(select dzzh from reader where xm='孙思旺')))
and xm!= '孙思旺' order by dzzh asc ;
#题目4
select sm from book where book.txm in 
(select txm from borrow where borrow.dzzh in
(select dzzh from borrow where borrow.txm=
(select txm from book where sm='李白全集')))
and sm!= '李白全集' order by sm asc;
 #代码结束