Mysql数据库第四章高级查询(二)简答

--2查询没有借阅信息的读者编号和姓名

SELECT reader.rid,reader.rName FROM reader
where NOT EXISTS(
  SELECT * FROM borrow WHERE reader.rid=borrow.rid 
)
--3查询到今天为止应还书但还未还书的信息

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
WHERE borrow.returnDate 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
WHERE borrow.returnDate IS NOT NULL
GROUP BY nif 
LIMIT 5
仅供参考