SQL 实战：从0到1构建图书借阅系统，完成5个核心业务查询
1. 系统架构设计与数据准备
图书借阅系统的核心在于构建合理的数据库结构并填充真实可用的测试数据。我们先从基础表结构开始，逐步构建完整的业务模型。
1.1 数据库表设计
图书借阅系统通常包含以下核心表：
-- 创建数据库 CREATE DATABASE library_db; USE library_db; -- 图书表 CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(100) NOT NULL, publisher VARCHAR(100) NOT NULL, publish_date DATE NOT NULL, isbn VARCHAR(20) UNIQUE, total_copies INT DEFAULT 1, available_copies INT DEFAULT 1, CHECK (available_copies <= total_copies) ); -- 读者表 CREATE TABLE readers ( reader_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, phone VARCHAR(20), membership_date DATE NOT NULL, max_borrow_limit INT DEFAULT 5 ); -- 借阅记录表 CREATE TABLE borrow_records ( record_id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, reader_id INT NOT NULL, borrow_date DATE NOT NULL, due_date DATE NOT NULL, return_date DATE, status ENUM('借出', '已还', '逾期') DEFAULT '借出', FOREIGN KEY (book_id) REFERENCES books(book_id), FOREIGN KEY (reader_id) REFERENCES readers(reader_id), CHECK (due_date > borrow_date), CHECK (return_date IS NULL OR return_date >= borrow_date) );1.2 模拟数据插入
-- 插入图书数据 INSERT INTO books VALUES (1, '数据库系统概念', 'Abraham Silberschatz', '机械工业出版社', '2019-01-15', '9787111634315', 10, 8), (2, 'SQL必知必会', 'Ben Forta', '人民邮电出版社', '2020-05-01', '9787115517044', 5, 3), (3, '高性能MySQL', 'Baron Schwartz', '电子工业出版社', '2018-11-01', '9787121384101', 8, 6), (4, 'Python编程：从入门到实践', 'Eric Matthes', '人民邮电出版社', '2020-07-01', '9787115546081', 15, 12), (5, '算法导论', 'Thomas H.Cormen', '机械工业出版社', '2013-01-01', '9787111407010', 7, 4); -- 插入读者数据 INSERT INTO readers VALUES (101, '张三', 'zhangsan@example.com', '13800138001', '2022-01-10', 5), (102, '李四', 'lisi@example.com', '13800138002', '2022-02-15', 5), (103, '王五', 'wangwu@example.com', '13800138003', '2022-03-20', 3), (104, '赵六', 'zhaoliu@example.com', '13800138004', '2022-04-25', 5); -- 插入借阅记录 INSERT INTO borrow_records (book_id, reader_id, borrow_date, due_date, return_date, status) VALUES (1, 101, '2023-01-05', '2023-02-05', '2023-01-20', '已还'), (2, 101, '2023-01-10', '2023-02-10', NULL, '借出'), (3, 102, '2023-01-15', '2023-02-15', '2023-02-10', '已还'), (1, 103, '2023-01-20', '2023-02-20', NULL, '逾期'), (4, 104, '2023-01-25', '2023-02-25', '2023-02-20', '已还');2. 核心业务查询实现
2.1 查询读者当前借阅情况
SELECT r.reader_id, r.name, b.title, br.borrow_date, br.due_date, DATEDIFF(CURRENT_DATE, br.due_date) AS overdue_days FROM readers r JOIN borrow_records br ON r.reader_id = br.reader_id JOIN books b ON br.book_id = b.book_id WHERE br.status = '借出' AND r.reader_id = 101;执行结果示例：
| reader_id | name | title | borrow_date | due_date | overdue_days |
|---|---|---|---|---|---|
| 101 | 张三 | SQL必知必会 | 2023-01-10 | 2023-02-10 | -5 |
2.2 统计每月借阅量Top 5的图书
SELECT b.book_id, b.title, COUNT(*) AS borrow_count, MONTH(br.borrow_date) AS month FROM books b JOIN borrow_records br ON b.book_id = br.book_id WHERE YEAR(br.borrow_date) = YEAR(CURRENT_DATE) GROUP BY b.book_id, b.title, MONTH(br.borrow_date) ORDER BY month, borrow_count DESC LIMIT 5;执行结果示例：
| book_id | title | borrow_count | month |
|---|---|---|---|
| 1 | 数据库系统概念 | 2 | 1 |
| 2 | SQL必知必会 | 1 | 1 |
| 3 | 高性能MySQL | 1 | 1 |
| 4 | Python编程 | 1 | 1 |
2.3 查询逾期未还的图书及读者信息
SELECT r.reader_id, r.name, r.email, b.title, br.borrow_date, br.due_date, DATEDIFF(CURRENT_DATE, br.due_date) AS overdue_days FROM borrow_records br JOIN readers r ON br.reader_id = r.reader_id JOIN books b ON br.book_id = b.book_id WHERE br.status = '借出' AND br.due_date < CURRENT_DATE;执行结果示例：
| reader_id | name | title | borrow_date | due_date | overdue_days | |
|---|---|---|---|---|---|---|
| 103 | 王五 | wangwu@example.com | 数据库系统概念 | 2023-01-20 | 2023-02-20 | 15 |
2.4 图书借阅热度分析
SELECT b.book_id, b.title, b.author, COUNT(br.record_id) AS total_borrows, b.available_copies, CONCAT(ROUND(COUNT(br.record_id) / (SELECT COUNT(*) FROM borrow_records) * 100, 2), '%') AS borrow_percentage FROM books b LEFT JOIN borrow_records br ON b.book_id = br.book_id GROUP BY b.book_id, b.title, b.author, b.available_copies ORDER BY total_borrows DESC;执行结果示例：
| book_id | title | author | total_borrows | available_copies | borrow_percentage |
|---|---|---|---|---|---|
| 1 | 数据库系统概念 | Abraham Silberschatz | 2 | 8 | 40.00% |
| 2 | SQL必知必会 | Ben Forta | 1 | 3 | 20.00% |
| 3 | 高性能MySQL | Baron Schwartz | 1 | 6 | 20.00% |
| 4 | Python编程 | Eric Matthes | 1 | 12 | 20.00% |
2.5 读者借阅行为分析
SELECT r.reader_id, r.name, COUNT(br.record_id) AS total_borrows, SUM(CASE WHEN br.status = '已还' THEN 1 ELSE 0 END) AS returned_books, SUM(CASE WHEN br.status = '借出' AND br.due_date >= CURRENT_DATE THEN 1 ELSE 0 END) AS current_borrows, SUM(CASE WHEN br.status = '借出' AND br.due_date < CURRENT_DATE THEN 1 ELSE 0 END) AS overdue_books, MAX(br.borrow_date) AS last_borrow_date FROM readers r LEFT JOIN borrow_records br ON r.reader_id = br.reader_id GROUP BY r.reader_id, r.name ORDER BY total_borrows DESC;执行结果示例：
| reader_id | name | total_borrows | returned_books | current_borrows | overdue_books | last_borrow_date |
|---|---|---|---|---|---|---|
| 101 | 张三 | 2 | 1 | 1 | 0 | 2023-01-10 |
| 102 | 李四 | 1 | 1 | 0 | 0 | 2023-01-15 |
| 103 | 王五 | 1 | 0 | 0 | 1 | 2023-01-20 |
| 104 | 赵六 | 1 | 1 | 0 | 0 | 2023-01-25 |
3. 性能优化与索引建议
3.1 关键索引设计
-- 为频繁查询的字段创建索引 CREATE INDEX idx_borrow_reader ON borrow_records(reader_id); CREATE INDEX idx_borrow_book ON borrow_records(book_id); CREATE INDEX idx_borrow_dates ON borrow_records(borrow_date, due_date, return_date); CREATE INDEX idx_reader_email ON readers(email); -- 复合索引优化热门查询 CREATE INDEX idx_book_popularity ON books(title, author);3.2 查询优化技巧
- 避免全表扫描：确保WHERE条件使用索引列
- 合理使用JOIN：小表驱动大表原则
- 限制返回数据量：使用LIMIT分页
- **避免SELECT ***：只查询需要的列
优化示例：
-- 优化前 SELECT * FROM borrow_records WHERE reader_id = 101; -- 优化后 SELECT br.record_id, b.title, br.borrow_date, br.due_date FROM borrow_records br JOIN books b ON br.book_id = b.book_id WHERE br.reader_id = 101;4. 扩展功能实现
4.1 图书预约功能
-- 新增预约表 CREATE TABLE reservations ( reservation_id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, reader_id INT NOT NULL, reserve_date DATETIME DEFAULT CURRENT_TIMESTAMP, status ENUM('等待', '可借', '取消') DEFAULT '等待', FOREIGN KEY (book_id) REFERENCES books(book_id), FOREIGN KEY (reader_id) REFERENCES readers(reader_id) ); -- 预约存储过程 DELIMITER // CREATE PROCEDURE reserve_book( IN p_book_id INT, IN p_reader_id INT, OUT p_result VARCHAR(100) ) BEGIN DECLARE v_available INT; -- 检查图书是否可借 SELECT available_copies INTO v_available FROM books WHERE book_id = p_book_id; IF v_available > 0 THEN SET p_result = '图书可直接借阅，无需预约'; ELSE -- 检查是否已有预约 IF NOT EXISTS (SELECT 1 FROM reservations WHERE book_id = p_book_id AND reader_id = p_reader_id AND status = '等待') THEN INSERT INTO reservations (book_id, reader_id) VALUES (p_book_id, p_reader_id); SET p_result = '预约成功'; ELSE SET p_result = '您已预约过该图书'; END IF; END IF; END // DELIMITER ;4.2 自动逾期处理
-- 逾期处理存储过程 DELIMITER // CREATE PROCEDURE process_overdue_records() BEGIN -- 更新逾期状态 UPDATE borrow_records SET status = '逾期' WHERE status = '借出' AND due_date < CURRENT_DATE; -- 记录逾期通知 INSERT INTO notifications (reader_id, message, create_date) SELECT br.reader_id, CONCAT('您借阅的图书《', b.title, '》已逾期，请尽快归还'), CURRENT_DATE FROM borrow_records br JOIN books b ON br.book_id = b.book_id WHERE br.status = '逾期' AND br.due_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY); END // DELIMITER ;5. 系统安全与维护
5.1 数据备份策略
-- 创建备份表 CREATE TABLE books_backup LIKE books; INSERT INTO books_backup SELECT * FROM books; -- 定期备份脚本示例 DELIMITER // CREATE PROCEDURE daily_backup() BEGIN DECLARE backup_date VARCHAR(20); SET backup_date = DATE_FORMAT(NOW(), '%Y%m%d'); SET @sql = CONCAT('CREATE TABLE borrow_records_backup_', backup_date, ' LIKE borrow_records'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @sql = CONCAT('INSERT INTO borrow_records_backup_', backup_date, ' SELECT * FROM borrow_records'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END // DELIMITER ;5.2 权限管理
-- 创建不同角色用户 CREATE USER 'library_admin'@'localhost' IDENTIFIED BY 'Admin@123'; CREATE USER 'library_staff'@'localhost' IDENTIFIED BY 'Staff@123'; CREATE USER 'library_reader'@'localhost' IDENTIFIED BY 'Reader@123'; -- 分配权限 GRANT ALL PRIVILEGES ON library_db.* TO 'library_admin'@'localhost'; GRANT SELECT, INSERT, UPDATE ON library_db.books TO 'library_staff'@'localhost'; GRANT SELECT, INSERT, UPDATE ON library_db.borrow_records TO 'library_staff'@'localhost'; GRANT SELECT ON library_db.books TO 'library_reader'@'localhost'; GRANT SELECT ON library_db.readers TO 'library_reader'@'localhost' WITH GRANT OPTION;
