mySQL图书馆数据库怎么做

CREATE DATABASE library;

USE library;

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255),
    publication_year INT,
    isbn VARCHAR(13) UNIQUE
);

CREATE TABLE readers (
    reader_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(15)
);

CREATE TABLE borrow_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    reader_id INT,
    borrow_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (reader_id) REFERENCES readers(reader_id)
);

INSERT INTO borrow_records (book_id, reader_id, borrow_date)
VALUES (1, 1, '2023-04-01');  -- 确保book_id=1和reader_id=1在books和readers表中存在

CREATE INDEX idx_book_title ON books(title);

-- 插入书籍数据
INSERT INTO books (title, author, publication_year, isbn)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 1925, '9780743273565');

-- 插入读者数据
INSERT INTO readers (name, email, phone)
VALUES ('John Doe', 'john.doe@example.com', '1234567890');

-- 插入借阅记录
INSERT INTO borrow_records (book_id, reader_id, borrow_date)
VALUES (1, 1, '2023-04-01');

-- 查询所有借阅记录
SELECT * FROM borrow_records;