BookManager数据文件

最新推荐文章于 2026-06-26 04:25:29 发布

原创最新推荐文章于 2026-06-26 04:25:29 发布 · 563 阅读

本内容遵循CC 4.0 BY-SA版权协议

本文介绍了一个图书管理系统的SQL查询案例，包括图书分类、出版社、图书和读者四个部分的查询操作，如判断图书分类中是否有图书、查询出版社下图书借阅状态、实现图书分类搜索等。

----------------------------------------图书分类部分---------------------------------------------------
//判断BookType(图书分类)中有无图书
select COUNT(*) as num from (Select Book.Book_Name,Book.Book_Id from Book inner join BookType on BookType.BType_Id = Book.Book_Type_id where BookType.BType_Id = 5) t1

//删除图书分类 需要解除图书和分类的绑定关系
//先用update 再用delete

----------------------------------------出版社部分---------------------------------------------------------
//判断Publisher下的图书是否被借出
//0表示没有被借出 大于0表示被借出
select COUNT(*) as num from (select Book.Book_Id from Book inner join BookBorrow on BookBorrow.BBorrow_Book_id = Book.Book_Id where Book.Book_Id = '1') t1

//为0的话还要删除 出版社下的图书
delete 出版社
delete 出版社下面的图书


----------------------------------------图书部分---------------------------------------------------------
//图书的分类搜索功能
select * from Book where Book.Book_Name like '%%' and Book.Book_Type_id like '%%' and Book.Book_Publisher_id like '%%'

//
select t2.Book_Id,t2.Book_Author,t2.Book_Name,t2.num from 
	(
	select t1.Book_Id,t1.Book_Author,t1.Book_Name,count(*) as num from 
		(
			select Book.Book_Id,Book.Book_Author,Book.Book_Name from Book RIGHT  OUTER  JOIN BookBorrow on Book.Book_Id = BookBorrow.BBorrow_Book_id 
				where Book.Book_Id = BookBorrow.BBorrow_Book_id 
		)
	t1 GROUP BY t1.Book_Id,t1.Book_Author,t1.Book_Name
	) t2
inner join Book on Book.Book_Id = t2.Book_Id

//全部借出
select Book.Book_Id,Book.Book_Name,Book.Book_Num,
case 
when Book.Book_Num -
(
	select COUNT(*) as num from (select Book.Book_Id,Book.Book_Name,Book.Book_Author from Book inner join BookBorrow
	on Book.Book_Id = BookBorrow.BBorrow_Book_id where Book.Book_Id = '10')t1
)= 0 then '全部借出'
when Book.Book_Num -
(
	select COUNT(*) as num from (select Book.Book_Id,Book.Book_Name,Book.Book_Author from Book inner join BookBorrow
	on Book.Book_Id = BookBorrow.BBorrow_Book_id where Book.Book_Id = '10')t1
)= Book.Book_Num then '未借出'
else '部分借出'
end 
from Book where Book.Book_Id = '10'

----------------------------------------读者部分---------------------------------------------------
//判断读者已经借了几本书
select COUNT(*) from BookBorrow inner join Book 
on BookBorrow.BBorrow_Book_id = Book.Book_Id 
where BookBorrow.BBorrow_Reader_Id = '3'
//如果读者借阅的数目=0 显示可以借阅操作 但不显示可还书
//如果读者借阅的数目=3 不显示可以借阅 显示可以还书操作

//显示读者借阅的书籍信息，这个应该保存Book的id 用来还书
select Book.Book_Name,Book.Book_Author,Book.Book_Price,Book.Book_Isbn,
BookBorrow.BBorrow_Borrow_date from Book inner join BookBorrow on Book.Book_Id = BookBorrow.BBorrow_Book_id
where BookBorrow.BBorrow_Reader_Id = '1'

//获取某本书已经被借出了多少本
select COUNT(*) from BookBorrow inner join Book on 
Book.Book_Id = BookBorrow.BBorrow_Book_id where Book.Book_Id = '1'
//再用Book.Book_Id获取书籍一共有多少本，并减去已经借出的数量，就得到还剩的数量
//如果还剩的数量大于0 就显示还可以借
//在每一次点击借阅之前一定要判断读者已经借阅了多少本，如果读者借阅的数量为3,则不能再借书