如何在mysql中实现图书借阅系统数据库

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(100),
    isbn VARCHAR(20) UNIQUE,
    publisher VARCHAR(100),
    publish_year YEAR,
    total_copies INT DEFAULT 1,
    available_copies INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE readers (
    reader_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    student_id VARCHAR(20) UNIQUE, -- 学号或工号
    phone VARCHAR(15),
    email VARCHAR(100),
    max_borrowing INT DEFAULT 5,   -- 最多可借数量
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    reader_id INT,
    borrow_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    due_date DATE NOT NULL,        -- 应还日期
    return_date TIMESTAMP NULL,    -- 实际归还时间
    status ENUM('borrowed', 'returned', 'overdue') DEFAULT 'borrowed',
    FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE,
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id) ON DELETE CASCADE,
    INDEX idx_book_id (book_id),
    INDEX idx_reader_id (reader_id),
    INDEX idx_status (status)
);

DELIMITER ;;
CREATE TRIGGER after_borrow_insert
AFTER INSERT ON borrow_records
FOR EACH ROW
BEGIN
    UPDATE books SET available_copies = available_copies - 1
    WHERE book_id = NEW.book_id;
END;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER after_return_update
AFTER UPDATE ON borrow_records
FOR EACH ROW
BEGIN
    IF NEW.return_date IS NOT NULL AND OLD.return_date IS NULL THEN
        UPDATE books SET available_copies = available_copies + 1
        WHERE book_id = NEW.book_id;
    END IF;
END;;
DELIMITER ;

SELECT b.title, b.author, br.borrow_date, br.due_date
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
WHERE br.reader_id = 1 AND br.status = 'borrowed';

SELECT r.name, b.title, br.due_date
FROM borrow_records br
JOIN readers r ON br.reader_id = r.reader_id
JOIN books b ON br.book_id = b.book_id
WHERE br.status = 'borrowed' AND br.due_date < CURDATE();

SELECT b.title, COUNT(*) AS borrow_count
FROM borrow_records br
JOIN books b ON br.book_id = b.book_id
GROUP BY b.book_id ORDER BY borrow_count DESC;