以下是一个简单的 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'
);
```
创建借阅记录表：
```sql
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)
);
```
插入一些书籍信息：
```sql
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.');
```
借阅一本书：
```sql
INSERT INTO borrow_records (book_id, borrower_name, borrow_date)
VALUES
(1, 'John Smith', '2021-01-01');
```
归还一本书：
```sql
UPDATE borrow_records
SET return_date = '2021-01-10'
WHERE book_id = 1;
```
查找所有可借阅的书籍：
```sql
SELECT * FROM books
WHERE status = 'available';
```
查找某个作者的所有书籍：
```sql
SELECT * FROM books
WHERE author = 'Jane Austen';
```
Macondo是一款由芯钬子公司开发的高级仿真软件，结合时域有限差分等技术，助力PDA等领域设备设计和优化，通过AI驱动的逆向设计提升效率，与NuwaTCAD和Rayzen协同，提供全面的光通信器件特性分析解决方案。
扫一扫
4105
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
