#使用子查询 获得当前没有被读者借阅的图书信息。
#(图书名称 图书编号 作者 出版社 单价)
SELECT bName,author,pubComp,price FROM book WHERE bId NOT IN
(SELECT DISTINCT bId FROM borrow)
#使用子查询获得今年的所有缴纳罚款的读者清单,要求输出
#姓名 书名 罚款的日期 缴纳的罚金
SELECT rName,bName,pDate,ammount FROM(
SELECT rId,bId,pDate,ammount FROM penalty WHERE
YEAR(pDate)=YEAR(NOW())
)temp
INNER JOIN book ON book.`bId`=temp.`bId`
INNER JOIN reader ON reader.`rId`=temp.`rId`
#使用子查询获得地址为空的所有读者尚未归还的图书清单。
#按照读者编号降序 借书日期降序输出
# 姓名 编号 图书名字 借阅日期 应归还日期
SELECT rName,temp.`rId`,bName,lendDate,willDate FROM(
SELECT rId,bId,lendDate,willDate FROM borrow WHERE rId IN(
SELECT rId FROM reader WHERE rAddress='' OR rAddress IS NULL)
ORDER BY rId DESC,lendDate DESC
)temp
INNER JOIN reader ON reader.`rId`=temp.`rId`
INNER JOIN book ON book.`bId`=temp.`bId`
# 查询没有借阅信息的读者编号和读者姓名。
SELECT rid,rname FROM reader WHERE
EXISTS (
SELECT * FROM reader WHERE rid NOT IN
(
SELECT DISTINCT rid FROM borrow
)
)AND rid NOT IN
(SELECT DISTINCT rid FROM borrow)
#查询所有到今天为止应该还书 但 还未还书的读者姓名 书名 应归还的日期
SELECT rName,bName,willDate FROM(SELECT rId,bid,willDate FROM borrow
WHERE willDate<CURDATE())temp
INNER JOIN book ON book.`bId`=temp.`bId`
INNER JOIN reader ON reader.`rId`=temp.`rId`
#查询各种图书未借出的本数,显示书名和本数
SELECT bName,(bCount-cl)剩余数量 FROM book
LEFT JOIN (SELECT COUNT(bId)cl,bId FROM borrow GROUP BY bId)temp
ON book.`bId`=temp.`bId`
ORDER BY bCount-cl DESC
# 从已完成借阅的记录中,统计每位读者的借书次数 显示读者姓名和借书次数。
SELECT (SELECT rName FROM reader WHERE reader.`rId`=borrow.`rId`)读者姓名,COUNT(rId)借书次数 FROM borrow
WHERE returnDate IS NOT NULL
GROUP BY rId
#查询总罚款金额大于5元的读者姓名和总罚款金额
SELECT (SELECT rName FROM reader WHERE reader.`rId`=penalty.`rId`)读者姓名,SUM(ammount)总罚款金额 FROM penalty
GROUP BY rId
HAVING SUM(ammount)>5
#统计已完成借阅的记录中每本书的借阅次数,显示借阅次数排名在前5位的图书名称和
#借阅次数
SELECT (SELECT bName FROM book WHERE book.`bId`=borrow.`bId` )图书名称,COUNT(bId)借阅次数 FROM borrow
GROUP BY bId
ORDER BY COUNT(bId)DESC
LIMIT 5;
使用事务完成以下功能:
# 为读者"刘能"办理借阅MySQL书籍的手续,要求编码实现
# 向borrow表中插入一条记录
# 更新reader表中的lendNum