图书借阅管理系统数据库

-- 创建图书表
CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    publish_date DATE NOT NULL,
    total_copies INT NOT NULL,
    available_copies INT NOT NULL
);

-- 创建读者表
CREATE TABLE readers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    registration_date DATE NOT NULL
);

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

-- 示例查询：查找某本书的借阅记录
SELECT * FROM borrow_records br
JOIN books b ON br.book_id = b.id
JOIN readers r ON br.reader_id = r.id
WHERE b.title = '某本书的标题';