对图书表进行多表查询

 -- 提升训练
SELECT booktype.typeid,book.bookid FROM booktype CROSS JOIN book ORDER BY booktype.typeid,book.bookid;
-- 查询图书分类表中的类别号和图书表中的类别号交叉连接后的结果

SELECT typename,booktype.typeid,bookid,title FROM booktype INNER JOIN book ON booktype.typeid=book.typeid; -- 连接条件
-- 查询所有图书的类别号、类别名称、图书编号和图书名称

SELECT book.bookid,record.readerid FROM book LEFT OUTER JOIN record ON book.bookid=record.readerid ORDER BY bookid;
-- 查询图书编号以及读者编号，如果没有借出的图书也要列出图书编号

SELECT bookid,title,press,price FROM book WHERE press='陕西师范大学出版社' OR price>'25';
-- 查询所有出版社是“陕西师范大学出版社“或者价格超过25的图书编号、图书名称、价格和出版社

SELECT DISTINCT reader.readername FROM reader RIGHT OUTER JOIN record ON record.readerid=reader.readerid; 
-- 查询借阅了图书的读者姓名。

SELECT DISTINCT reader.readername FROM reader LEFT OUTER JOIN record ON record.recordid=reader.readerid WHERE bookid IS NULL; 
-- 查询没有借阅过图书的读者姓名。

SELECT reader.readername FROM reader JOIN record ON record.readerid=reader.readerid JOIN book ON book.bookid=record.bookid  WHERE book.title='不抱怨的世界';
-- 查询借阅过《不抱怨的世界》的读者姓名

-- 扩展训练 
SELECT book.title FROM reader JOIN record ON record.readerid=reader.readerid JOIN book ON record.bookid=book.bookid WHERE reader.readername='郭玉娇';
-- 查询“郭玉姣”借阅的图书名称

SELECT reader.readerid,COUNT(*),AVG(price) FROM reader JOIN record ON record.readerid=reader.readerid JOIN book ON book.bookid=record.bookid  WHERE book.title='不抱怨的世界' GROUP BY reader.readerid ORDER BY COUNT(*) DESC;
-- 统计借阅了图书“不抱怨的世界”的每个读者借阅的图书总数和平均价格，按照图书总数降序排序

SELECT DISTINCT book.bookid,book.title FROM book LEFT JOIN record ON record.recordid=record.bookid WHERE record.recordid IS NULL; 
-- 查询所有读者没有借阅过的图书编号和图书名

SELECT reader.* FROM reader JOIN record ON record.readerid=reader.readerid JOIN book ON book.bookid=record.bookid  WHERE title IN ('李开复自传','不抱怨的世界');
-- 查询借阅了“李开复自传”和“不抱怨的世界”的读者