利用MYSQL做一个图书管理系统的课程设计,罚款单那一块总是出现了问题,最后找到了办法,可以使用datediff函数,注意借书时间和现在的时间间隔即为结束时间,下面是代码:
1)现在对已有借书证的读者进行查询借书是否超期
查询语句(这里规定60天):
select system_readers.readerid 读者借书证编号,readername 读者姓名,
system_books.bookid 书籍编号,bookname 书名,borrowdate 借书时间,
datediff(now(),borrowdate)-60
from borrow_record ,system_readers,system_books
where system_readers.readerid=borrow_record.readerid
and system_books.bookid=borrow_record.bookid
and datediff(now(),borrowdate)>=60
2)对超过天数的读者进行罚款,加入到罚款单里面,一天以0.1元扣除计算:
select system_readers.readerid 读者借书证编号,readername 读者姓名,
system_books.bookid 书籍编号,bookname 书名,borrowdate 借书时间,
0.1*(datediff(now(),borrowdate)-60) 罚金
from borrow_record ,system_readers,system_books
where system_readers.readerid=borrow_record.readerid
and system_books.bookid=borrow_record.bookid
and datediff(now(),borrowdate)>=60