头歌MYSQL——课后作业4 数据查询（二）

第1关：多表查询

任务描述

本关任务：查询来自借阅、图书、读者数据表的数据 为了完成本关任务，你需要掌握：

如何多表查询

相关知识

查询多个数据表

在实际应用中，查询经常会涉及到几个数据表。 基于多个相关联的数据表进行的查询称为连接查询。

命令格式： select * from 数据表 from 数据表1，数据表2 where 连接条件

对于连接查询，在FROM短语后多个数据表的名称之间用逗号隔开，在WHERE短语中须指定数据表之间进行连接的条件 在连接查询中引用两个表的公共字段时，必须在字段前添加表名作为前缀，否则系统会提示出错。 对于只在一个数据表中出现的字段，则无需指定前缀。

第二种方法 from 数据表1 join 数据表2 on 连接条件 数据表1和数据表2为需要连接的数据表 on后面是连接的条件 通常是关键字段的值相同

三个表的查询 如果要实现三个表的查询，可以 From 数据表1 ,数据表2 ,数据表3 where 连接条件1 and 连接条件2

任务要求

打开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 borrow join reader on reader.dzzh=borrow.dzzh where xm="王颖珊";
 #第二题
 select dzzh,jyrq,hsrq from borrow join book on borrow.txm=book.txm where sm="李白全集";
 #第三题
 select reader.dzzh,xm,dhhm,book.txm,sm,jyrq from book join borrow  on borrow.txm=book.txm join reader on borrow.dzzh=reader.dzzh where ISNULL(hsrq);
 #代码结束 

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

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

 #第三题
select cbs,count(cbs) as jycs from borrow left join book on book.txm=borrow.txm 
group by cbs
order by jycs desc,cbs desc;

 #第四题
select xm,count(xm)jycs from borrow left join reader on borrow.dzzh=reader.dzzh 
group by xm
order by jycs desc,xm desc;


 #第五题
 select xm,count(xm) as jycs from borrow left join reader on borrow.dzzh=reader.dzzh 
where sf='研究生'
group by xm
order by jycs desc,xm desc;
 
 
 #代码结束

 第3关：子查询

use library;
#代码开始
#答案1
select sm from book where cbs="上海古籍出版社" and sm!="李白全集";
#答案2
select sm,sj from book where sj>(select avg(sj) from book);
#答案2
select txm,sm,sj from book where sj=(select max(sj) from book);

#答案3
select txm,sm,sj from book where sj=(select min(sj) from book);



 

 #代码结束

 第4关：多表子查询

use library;
#代码开始
#题目1
select dzzh,xm from reader where reader.dzzh in (select dzzh from borrow);
#题目2
select txm,sm from book where book.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 dzzh!='006' 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;
 
 #代码结束