Mysql数据库第四章⾼级查询（⼆）简答--2查询没有借阅信息的读者编号和姓名
SELECT reader.rid,reader.rName FROM reader
where NOT EXISTS(
SELECT * FROM borrow WHERE reader.rid=borrow.rid
)
--3查询到今天为⽌应还书但还未还书的信息
mysql高级教程视频SELECT reader.rid,reader.rName,book.bid,book.bName,willDate FROM borrow
INNER JOIN reader ON borrow.rid=reader.rid
INNER JOIN book ON borrow.nif=book.bid
WHERE returnDate is NULL
--4查询各种图书未借出的本数，及书名和本数
SELECT bName,bCount FROM book
WHERE NOT EXISTS
(SELECT * FROM borrow WHERE borrow.nif=book.bid)
--5从已完成借阅的记录中，统计每位读者的借书次数，显⽰读者姓名和借书次数
SELECT  reader.rid,reader.rName as '读者姓名',COUNT(nif)as '借书次数' FROM borrow
INNER JOIN reader ON reader.rid=borrow.rid
urnDate IS NOT NULL
GROUP BY rid
--6查询总⾦额⼤于5元的读者姓名和总⾦额
SELECT  reader.rid,reader.rName,amount FROM penalty
INNER JOIN reader ON penalty.rid=reader.rid
WHERE amount>5
-- 统计已完成借阅的记录中每本书的借阅次数，显⽰借阅次数排名在前5位的图书名称和借阅次数
SELECT book.bName AS '书名',COUNT(nif) AS'借阅次数' FROM borrow
INNER JOIN book ON borrow.nif=book.bid
INNER JOIN reader ON reader.rid=borrow.rid
urnDate IS NOT NULL
GROUP BY nif
LIMIT 5
仅供参考

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。