如何使用mysql实现图书借阅系统_mysql图书借阅管理方法

CREATE TABLE books (
  book_id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  author VARCHAR(50),
  isbn VARCHAR(20) UNIQUE,
  total_copies INT DEFAULT 1,
  available_copies INT DEFAULT 1
);
<p>CREATE TABLE readers (
reader_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
phone VARCHAR(15),
borrow_limit INT DEFAULT 5
);</p><p>CREATE TABLE borrow_records (
record_id INT PRIMARY KEY AUTO_INCREMENT,
book_id INT,
reader_id INT,
borrow_date DATE NOT NULL,
return_date DATE,
FOREIGN KEY (book_id) REFERENCES books(book_id),
FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);</p>

-- 检查图书是否可借
SELECT available_copies FROM books WHERE book_id = 1;
<p>-- 检查读者已借数量
SELECT COUNT(*) FROM borrow_records 
WHERE reader_id = 101 AND return_date IS NULL;</p><p>-- 插入借阅记录（假设通过检查）
INSERT INTO borrow_records (book_id, reader_id, borrow_date) 
VALUES (1, 101, CURDATE());</p><p>-- 更新可借数量
UPDATE books SET available_copies = available_copies - 1 WHERE book_id = 1;</p>

-- 更新归还日期
UPDATE borrow_records 
SET return_date = CURDATE() 
WHERE book_id = 1 AND reader_id = 101 AND return_date IS NULL 
LIMIT 1;
<p>-- 增加可借数量
UPDATE books SET available_copies = available_copies + 1 WHERE book_id = 1;</p>

SELECT r.name, br.borrow_date, br.return_date 
FROM borrow_records br
JOIN readers r ON br.reader_id = r.reader_id
WHERE br.book_id = 1;

SELECT b.title, r.name, br.borrow_date 
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
JOIN readers r ON br.reader_id = r.reader_id
WHERE br.return_date IS NULL;

SELECT b.title, br.borrow_date 
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
WHERE br.reader_id = 101 AND br.return_date IS NULL;