SQL实现图书管理完整代码

以下是一个简单的 SQL 实现图书管理系统的完整代码：

创建书籍信息表：

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255) NOT NULL,
  publisher VARCHAR(255) NOT NULL,
  publish_date DATE NOT NULL,
  price DECIMAL(7, 2) NOT NULL,
  isbn VARCHAR(20) NOT NULL,
  summary TEXT,
  status ENUM('available', 'borrowed') NOT NULL DEFAULT 'available'
);

创建借阅记录表：

CREATE TABLE borrow_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  book_id INT NOT NULL,
  borrower_name VARCHAR(255) NOT NULL,
  borrow_date DATE NOT NULL,
  return_date DATE,
  FOREIGN KEY (book_id) REFERENCES books(id)
);

插入一些书籍信息：

INSERT INTO books (title, author, publisher, publish_date, price, isbn, summary)
VALUES 
  ('The Great Gatsby', 'F. Scott Fitzgerald', 'Charles Scribner\'s Sons', '1925-04-10', 12.99, '978-0743273565', 'The story primarily concerns the young and mysterious millionaire Jay Gatsby and his quixotic passion and obsession for the beautiful former debutante Daisy Buchanan.'),
  ('To Kill a Mockingbird', 'Harper Lee', 'J. B. Lippincott & Co.', '1960-07-11', 10.99, '978-0061120084', 'The novel is renowned for its warmth and humor, despite dealing with serious issues of rape and racial inequality.'),
  ('1984', 'George Orwell', 'Secker and Warburg', '1949-06-08', 9.99, '978-0451524935', 'The novel is set in Airstrip One, a province of the superstate Oceania in a world of perpetual war, omnipresent government surveillance, and public manipulation.'),
  ('Pride and Prejudice', 'Jane Austen', 'T. Egerton', '1813-01-28', 7.99, '978-0486284736', 'The novel follows the character development of Elizabeth Bennet, the dynamic protagonist of the book who learns about the repercussions of hasty judgments and comes to appreciate the difference between superficial goodness and actual goodness.'),
  ('One Hundred Years of Solitude', 'Gabriel García Márquez', 'Harper & Row', '1967-05-30', 14.99, '978-0060883287', 'The novel tells the multi-generational story of the Buendía family, whose patriarch, José Arcadio Buendía, founds the town of Macondo, the metaphoric Colombia.');

借阅一本书：

INSERT INTO borrow_records (book_id, borrower_name, borrow_date)
VALUES 
  (1, 'John Smith', '2021-01-01');

归还一本书：

UPDATE borrow_records
SET return_date = '2021-01-10'
WHERE book_id = 1;

查找所有可借阅的书籍：

SELECT * FROM books
WHERE status = 'available';

查找某个作者的所有书籍：

SELECT * FROM books
WHERE author = 'Jane Austen';