用mysql建立图书读者数据库

-- 创建数据库
CREATE DATABASE library;

-- 使用数据库
USE library;

-- 创建图书表
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    publish_date DATE,
    status ENUM('available', 'borrowed') DEFAULT 'available'
);

-- 创建读者表
CREATE TABLE readers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact VARCHAR(255),
    join_date DATE DEFAULT CURRENT_DATE
);

-- 创建借阅记录表
CREATE TABLE borrow_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    reader_id INT NOT NULL,
    borrow_date DATE DEFAULT CURRENT_DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (reader_id) REFERENCES readers(id)
);

SELECT * FROM borrow_records WHERE book_id = ?;

SELECT * FROM borrow_records WHERE reader_id = ?;

UPDATE books SET status = 'borrowed' WHERE id = ?;