图书馆管理系统数据库

-- 创建图书信息表
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
);

-- 创建读者信息表
CREATE TABLE readers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    contact_info VARCHAR(255),
    borrow_history TEXT
);

-- 创建借阅记录表
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)
);

-- 插入示例数据
INSERT INTO books (title, author, isbn, publish_date) VALUES
('Book1', 'Author1', '1234567890123', '2020-01-01'),
('Book2', 'Author2', '9876543210987', '2021-02-02');

INSERT INTO readers (name, contact_info) VALUES
('Reader1', 'reader1@example.com'),
('Reader2', 'reader2@example.com');

INSERT INTO borrow_records (book_id, reader_id, borrow_date) VALUES
(1, 1, '2023-01-01'),
(2, 2, '2023-02-01');