【项目实训】图书馆借还书系统的数据查询

（1）查询“人民邮电出版社”的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

mysql> select bid as 图书编号,bname as 书名,author as 作者, publisher as 出版社, price as 价格
    -> from book
    -> where publisher='人民邮电出版社';

（2）查询女性读者的相关信息，结果包含读者姓名、性别和所属院系。

mysql> select rname as 姓名,sex as 性别, dept as 所属院系
    -> from reader
    -> where sex = '女';

（3）查询 2017 年至 2019 年出版的相关图书信息，结果包含图书编号、书名、作者、出版日期和定价。

mysql> select bid as 图书编号,bname as 书名,author as 作者,publishdate as 出版日期,price as 定价
    -> from book
    -> where publishdate between "2017-01-01" and "2019-12-31";

（4）查询图书定价打7折后的图书编号、书名和打折后价格。

mysql> select bid as 书名, bname as 书名, price*0.7 as 打折后价格
    -> from book;

（5）查询所有馆存图书的总类别数量和总库存数量。

mysql> select count(bid) as 总类别数量, sum(total) as 总库存数量
    -> from book;

（6）查询借阅的书名称中包含“数据”的读者的借阅信息，结果包含读者姓名和书名。

mysql> select rname as 读者姓名, bname as 书名
    -> from borrow join reader on reader.rid= borrow.rid
    -> join book on borrow.bid = book.bid
    -> where bname like "%数据%";

（7）查询读者为“教师”的借阅信息，结果包含读者姓名、书名和借阅日期。

mysql> select rname as 读者姓名,bname as 书名,borrowtime as 借阅日期
    -> from borrow join reader on reader.rid= borrow.rid
    -> join book on borrow.bid = book.bid
    -> where typeno in(select typeno from readertype where typename="教师");

（8）查询尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。

mysql> select borrow.rid as 读者编号,rname as 读者姓名,bname as 书名, borrowtime as 借阅日期
    -> from borrow join reader on reader.rid= borrow.rid
    -> join book on borrow.bid = book.bid
    -> where returntime is null;

（9）查询每本图书的借阅次数，将结果按照借阅次数降序排列，结果包含图书编号、书名、借阅次数、作者和出版社。

mysql> select borrow.bid as 图书编号,bname as 书名,count(borrowtime) as 借阅次数,
    -> author as 作者,publisher as 出版社
    -> from borrow,book where borrow.bid=book.bid
    -> group by borrow.bid
    -> order by count(borrowtime) desc;

（10）查询每个院系的借阅次数，将结果按照借阅次数的降序排序，结果包含院系名称和借阅次数。

mysql> select reader.dept as 院系名称, count(borrowtime) as 借阅次数
    -> from borrow,reader where borrow.rid=reader.rid
    -> group by reader.dept
    -> order by count(borrowtime) desc;

（11）查询借阅了书名为“大数据技术基础”的图书但尚未还书的相关读者信息，结果包含读者编号、读者姓名、书名和借阅日期。

mysql> select a.rid as 读者编号,a.rname as 读者姓名, b.bname as 书名,
    -> c.borrowtime as 借阅日期
    -> from borrow c join reader a on c.rid=a.rid
    -> join book b on b.bid=c.bid
    -> where returntime is null and c.bid=
    -> (select d.bid from book d where bname="大数据技术基础");

（12）查询定价高于平均定价的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

mysql> select bid as 图书编号, bname as 书名, author as 作者,
    -> publisher as 出版社, price as 定价
    -> from book where price >
    -> (select avg(price)  from book);

（13）查询从未被读者借阅的相关图书信息，结果包含图书编号、书名、作者、出版社和定价。

mysql> select bid as 图书编号, bname as 书名, author as 作者,
    -> publisher as 出版社, price as 定价
    -> from book where bid not in
    -> (select bid from borrow);