```sql
创建图书表
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(100),
publish_date DATE,
category VARCHAR(50),
quantity INT
);
插入图书数据
INSERT INTO books (book_id, title, author, publish_date, category, quantity)
VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', '19250410', 'Novel', 10),
(2, 'To Kill a Mockingbird', 'Harper Lee', '19600711', 'Fiction', 8),
(3, '1984', 'George Orwell', '19490608', 'Science Fiction', 12),
(4, 'Pride and Prejudice', 'Jane Austen', '18130128', 'Classic', 5),
(5, 'The Catcher in the Rye', 'J.D. Salinger', '19510716', 'Fiction', 15);
创建借阅记录表
CREATE TABLE borrow_records (
record_id INT PRIMARY KEY,
book_id INT,
borrower_id INT,
borrow_date DATE,
due_date DATE,
return_date DATE
);
插入借阅记录数据
INSERT INTO borrow_records (record_id, book_id, borrower_id, borrow_date, due_date, return_date)
VALUES
(1, 1, 101, '20220105', '20220115', '20220114'),
(2, 3, 102, '20220110', '20220120', NULL),
(3, 2, 103, '20220115', '20220125', NULL),
(4, 5, 104, '20220120', '20220130', NULL);
查询某书籍的借阅状态（是否已借出）
SELECT
b.title AS book_title,
COUNT(br.record_id) AS times_borrowed,
b.quantity COUNT(br.record_id) AS available_copies
FROM
books b
LEFT JOIN borrow_records br ON b.book_id = br.book_id AND br.return_date IS NULL
WHERE
b.book_id = 2;
查询某用户的借阅记录
SELECT
b.title AS book_title,
br.borrow_date,
br.due_date,
br.return_date
FROM
books b
JOIN borrow_records br ON b.book_id = br.book_id
WHERE
br.borrower_id = 103;
借阅图书
INSERT INTO borrow_records (record_id, book_id, borrower_id, borrow_date, due_date, return_date)
VALUES
(5, 4, 105, '20220201', '20220214', NULL);
归还图书
UPDATE borrow_records
SET
return_date = '20220212'
WHERE
book_id = 2 AND borrower_id = 103 AND return_date IS NULL;
删除借阅记录
DELETE FROM borrow_records
WHERE
record_id = 5;
```
