SQL 实战：从0到1构建图书借阅系统，完成5个核心业务查询

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, 张三, zhangsanexample.com, 13800138001, 2022-01-10, 5), (102, 李四, lisiexample.com, 13800138002, 2022-02-15, 5), (103, 王五, wangwuexample.com, 13800138003, 2022-03-20, 3), (104, 赵六, zhaoliuexample.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_idnametitleborrow_datedue_dateoverdue_days101张三SQL必知必会2023-01-102023-02-10-52.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_idtitleborrow_countmonth1数据库系统概念212SQL必知必会113高性能MySQL114Python编程112.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_idnameemailtitleborrow_datedue_dateoverdue_days103王五wangwuexample.com数据库系统概念2023-01-202023-02-20152.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_idtitleauthortotal_borrowsavailable_copiesborrow_percentage1数据库系统概念Abraham Silberschatz2840.00%2SQL必知必会Ben Forta1320.00%3高性能MySQLBaron Schwartz1620.00%4Python编程Eric Matthes11220.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_idnametotal_borrowsreturned_bookscurrent_borrowsoverdue_bookslast_borrow_date101张三21102023-01-10102李四11002023-01-15103王五10012023-01-20104赵六11002023-01-253. 性能优化与索引建议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_adminlocalhost IDENTIFIED BY Admin123; CREATE USER library_stafflocalhost IDENTIFIED BY Staff123; CREATE USER library_readerlocalhost IDENTIFIED BY Reader123; -- 分配权限 GRANT ALL PRIVILEGES ON library_db.* TO library_adminlocalhost; GRANT SELECT, INSERT, UPDATE ON library_db.books TO library_stafflocalhost; GRANT SELECT, INSERT, UPDATE ON library_db.borrow_records TO library_stafflocalhost; GRANT SELECT ON library_db.books TO library_readerlocalhost; GRANT SELECT ON library_db.readers TO library_readerlocalhost WITH GRANT OPTION;