----------------------------------------图书分类部分--------------------------------------------------- //判断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,则不能再借书
BookManager数据文件
最新推荐文章于 2022-01-13 09:49:35 发布