当前位置：首页 > news >正文

SQL 实战指南：校园图书管理系统 SQL 设计（借阅 / 归还 / 库存查询实现）——超全项目实战练习

news 2025/9/7 15:59:48

校园图书管理系统是大学日常高频使用的场景，核心需求包括 “图书信息管理”“读者借阅 / 归还操作”“库存实时查询” 等。这些功能的底层依赖 SQL 对多表数据的关联与逻辑处理。今天我们从零搭建系统核心数据表，完整实现 “图书入库 - 读者借阅 - 归还登记 - 库存统计” 全流程 SQL 操作，代码可直接复制运行，帮你掌握系统级 SQL 设计思路。

我整理了超全的学习资料合集，蕴含专业、考试、课程等资料，还有游戏和软件合集

学习资料合集文档https://www.kdocs.cn/l/cjchDXwklk1B

一、场景需求与表结构设计

校园图书管理系统的核心是 “人 - 书 - 借阅记录” 的关联，需满足 3 类核心需求：

管理图书信息（书名、作者、ISBN、库存等）；
管理读者信息（学号、姓名、学院、可借数量限制等）；
记录借阅 / 归还行为（借阅时间、应还时间、归还状态等）。

基于需求设计 3 张核心表，关联逻辑如下：

读者表（reader）与借阅表（borrow）通过 “读者学号” 关联，记录 “谁借的”；

图书表（book）与借阅表（borrow）通过 “图书 ID” 关联，记录 “借了什么书”；

借阅表作为中间表，串联起 “读者 - 图书” 的借阅关系。

表名	核心字段	作用
book（图书表）	图书 ID、ISBN、书名、作者、出版社、库存数量、可借数量	存储图书基础信息与库存状态
reader（读者表）	读者学号、姓名、学院、最大可借数量、已借数量	存储读者信息与借阅权限
borrow（借阅表）	借阅 ID、读者学号、图书 ID、借阅时间、应还时间、归还时间、归还状态	记录每笔借阅 / 归还记录

二、第一步：创建数据表并插入测试数据

先创建 3 张核心表，设置主键、外键及约束（保证数据完整性），再插入模拟数据。

1. 创建图书表（book）

-- 创建图书表：存储图书信息与库存
CREATE TABLE book (book_id INT PRIMARY KEY AUTO_INCREMENT,  -- 图书ID（自增主键）isbn CHAR(13) NOT NULL UNIQUE,  -- ISBN编号（唯一，图书身份标识）book_name VARCHAR(100) NOT NULL,  -- 书名author VARCHAR(50) NOT NULL,  -- 作者publisher VARCHAR(50) NOT NULL,  -- 出版社total_num INT NOT NULL CHECK (total_num >= 0),  -- 总库存数量（非负）available_num INT NOT NULL CHECK (available_num >= 0),  -- 可借数量（非负，≤总库存）category VARCHAR(20) NOT NULL  -- 图书分类（如计算机、文学、历史）
);

2. 创建读者表（reader）

-- 创建读者表：存储读者信息与借阅权限
CREATE TABLE reader (reader_id CHAR(10) PRIMARY KEY,  -- 读者学号（如2025001001，主键）reader_name VARCHAR(20) NOT NULL,  -- 读者姓名college VARCHAR(30) NOT NULL,  -- 所属学院max_borrow_num INT NOT NULL DEFAULT 5,  -- 最大可借数量（默认5本）current_borrow_num INT NOT NULL DEFAULT 0 CHECK (current_borrow_num >= 0)  -- 当前已借数量（非负）
);

3. 创建借阅表（borrow）

-- 创建借阅表：记录借阅/归还记录（关联图书表与读者表）
CREATE TABLE borrow (borrow_id INT PRIMARY KEY AUTO_INCREMENT,  -- 借阅ID（自增主键）reader_id CHAR(10) NOT NULL,  -- 关联读者表的学号book_id INT NOT NULL,  -- 关联图书表的IDborrow_time DATETIME NOT NULL,  -- 借阅时间（默认当前时间）due_time DATETIME NOT NULL,  -- 应还时间（默认借阅后30天）return_time DATETIME,  -- 归还时间（未归还则为NULL）return_status VARCHAR(10) NOT NULL DEFAULT '未归还',  -- 归还状态（未归还/已归还）-- 外键约束：读者学号必须在读者表中存在FOREIGN KEY (reader_id) REFERENCES reader(reader_id),-- 外键约束：图书ID必须在图书表中存在FOREIGN KEY (book_id) REFERENCES book(book_id),-- 唯一约束：同一读者不能同时借阅同一本书（避免重复借阅）UNIQUE (reader_id, book_id, return_status)
);

4. 插入测试数据

-- 插入图书数据（8本图书，覆盖不同分类）
INSERT INTO book (isbn, book_name, author, publisher, total_num, available_num, category)
VALUES
('9787111641247', 'SQL必知必会', 'Ben Forta', '机械工业出版社', 5, 4, '计算机'),
('9787115546026', 'MySQL实战45讲', '林晓斌', '人民邮电出版社', 3, 3, '计算机'),
('9787020002207', '红楼梦', '曹雪芹', '人民文学出版社', 10, 8, '文学'),
('9787020024759', '三国演义', '罗贯中', '人民文学出版社', 8, 6, '文学'),
('9787030451592', '数据结构（C语言版）', '严蔚敏', '科学出版社', 6, 4, '计算机'),
('9787108063671', '人类简史', '尤瓦尔·赫拉利', '中信出版社', 4, 2, '历史'),
('9787508698888', '经济学原理', '曼昆', '中信出版社', 5, 5, '经济'),
('9787111544937', 'Python编程：从入门到实践', '埃里克·马瑟斯', '机械工业出版社', 7, 5, '计算机');-- 插入读者数据（5名读者，覆盖不同学院）
INSERT INTO reader (reader_id, reader_name, college, max_borrow_num, current_borrow_num)
VALUES
('2025001001', '张三', '计算机学院', 5, 1),
('2025002001', '李四', '文学院', 5, 2),
('2025003001', '王五', '历史学院', 5, 0),
('2025004001', '赵六', '经济学院', 5, 1),
('2025005001', '孙七', '计算机学院', 5, 3);-- 插入借阅数据（6笔借阅记录，含未归还/已归还状态）
INSERT INTO borrow (reader_id, book_id, borrow_time, due_time, return_time, return_status)
VALUES
-- 张三（2025001001）借阅《SQL必知必会》（book_id=1），未归还
('2025001001', 1, '2025-09-01 10:30:00', '2025-09-30 23:59:59', NULL, '未归还'),
-- 李四（2025002001）借阅《红楼梦》（book_id=3），已归还
('2025002001', 3, '2025-08-15 09:15:00', '2025-09-14 23:59:59', '2025-09-10 16:40:00', '已归还'),
-- 李四（2025002001）借阅《人类简史》（book_id=6），未归还
('2025002001', 6, '2025-09-05 14:20:00', '2025-10-04 23:59:59', NULL, '未归还'),
-- 赵六（2025004001）借阅《经济学原理》（book_id=7），未归还
('2025004001', 7, '2025-09-08 11:00:00', '2025-10-07 23:59:59', NULL, '未归还'),
-- 孙七（2025005001）借阅《数据结构》（book_id=5），已归还
('2025005001', 5, '2025-08-20 15:30:00', '2025-09-19 23:59:59', '2025-09-15 10:20:00', '已归还'),
-- 孙七（2025005001）借阅《Python编程》（book_id=8），未归还
('2025005001', 8, '2025-09-02 08:45:00', '2025-10-01 23:59:59', NULL, '未归还');-- 验证基础数据：查看三表关联的借阅信息
SELECT b.borrow_id,r.reader_id AS 读者学号,r.reader_name AS 读者姓名,bo.book_name AS 书名,b.borrow_time AS 借阅时间,b.due_time AS 应还时间,b.return_status AS 归还状态
FROM borrow b
JOIN reader r ON b.reader_id = r.reader_id
JOIN book bo ON b.book_id = bo.book_id
LIMIT 5;

基础关联结果示例：

borrow_id	读者学号	读者姓名	书名	借阅时间	应还时间	归还状态
1	2025001001	张三	SQL 必知必会	2025-09-01 10:30:00	2025-09-30 23:59:59	未归还
2	2025002001	李四	红楼梦	2025-08-15 09:15:00	2025-09-14 23:59:59	已归还
3	2025002001	李四	人类简史	2025-09-05 14:20:00	2025-10-04 23:59:59	未归还
4	2025004001	赵六	经济学原理	2025-09-08 11:00:00	2025-10-07 23:59:59	未归还
5	2025005001	孙七	数据结构（C 语言版）	2025-08-20 15:30:00	2025-09-19 23:59:59	已归还

三、第二步：核心功能 SQL 实现（6 个高频场景）

围绕 “图书管理 - 借阅 - 归还 - 统计” 全流程，实现系统最常用的 6 个功能，每个功能包含 “需求描述→SQL 代码→逻辑说明”。

功能 1：查询图书库存（可借数量、总库存）

需求描述：查询指定图书（如《SQL 必知必会》）或指定分类（如 “计算机” 类）的总库存、可借数量，判断是否能借阅。

核心逻辑：通过图书名或分类筛选，直接查询图书表的库存字段。

-- 场景1：查询指定图书《SQL必知必会》的库存
SELECT book_name AS 书名,isbn AS ISBN,author AS 作者,total_num AS 总库存,available_num AS 可借数量,-- 显示库存状态（可借/无库存）CASE WHEN available_num > 0 THEN '可借' ELSE '无库存' END AS 库存状态
FROM book
WHERE book_name = 'SQL必知必会';-- 场景2：查询“计算机”类所有图书的库存，按可借数量降序
SELECT book_id AS 图书ID,book_name AS 书名,author AS 作者,total_num AS 总库存,available_num AS 可借数量
FROM book
WHERE category = '计算机'
ORDER BY available_num DESC;

场景 1 运行结果：

书名	ISBN	作者	总库存	可借数量	库存状态
SQL 必知必会	9787111641247	Ben Forta	5	4	可借

功能 2：读者借阅图书（关联更新三表数据）

需求描述：张三（学号 2025001001）借阅《MySQL 实战 45 讲》（book_id=2），需完成 3 件事：

新增借阅记录到 borrow 表；
减少 book 表中该图书的可借数量（available_num-1）；
增加 reader 表中张三的当前已借数量（current_borrow_num+1）。

核心逻辑：用事务保证三表更新的一致性（要么全成功，要么全失败，避免数据错乱）。

-- 开启事务：确保三表更新同步
START TRANSACTION;-- 1. 新增借阅记录（借阅时间默认当前时间，应还时间为30天后）
INSERT INTO borrow (reader_id, book_id, borrow_time, due_time)
VALUES ('2025001001', 2, NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY));-- 2. 更新图书表：《MySQL实战45讲》可借数量-1
UPDATE book
SET available_num = available_num - 1
WHERE book_id = 2;-- 3. 更新读者表：张三当前已借数量+1
UPDATE reader
SET current_borrow_num = current_borrow_num + 1
WHERE reader_id = '2025001001';-- 提交事务（所有操作成功后执行）
COMMIT;-- 验证借阅结果：查看张三的借阅记录和图书库存
SELECT r.reader_name AS 读者姓名,r.current_borrow_num AS 当前已借数量,bo.book_name AS 借阅图书,bo.available_num AS 图书可借数量
FROM borrow b
JOIN reader r ON b.reader_id = r.reader_id
JOIN book bo ON b.book_id = bo.book_id
WHERE b.reader_id = '2025001001' AND b.return_status = '未归还';

事务说明：如果中途报错（如图书可借数量已为 0），执行ROLLBACK;回滚所有操作，避免出现 “借阅记录已新增，但库存未减少” 的错乱数据。

功能 3：读者归还图书（同步更新状态与数量）

需求描述：李四（学号 2025002001）归还《人类简史》（book_id=6），需完成 3 件事：

更新 borrow 表：设置归还时间为当前时间，状态改为 “已归还”；
更新 book 表：该图书可借数量 + 1；
更新 reader 表：李四当前已借数量 - 1。

-- 开启事务
START TRANSACTION;-- 1. 更新借阅记录：设置归还时间和状态（续前文代码）
UPDATE borrow
SET return_time = NOW(),return_status = '已归还'
WHERE reader_id = '2025002001' AND book_id = 6 AND return_status = '未归还';-- 2. 更新图书表：《人类简史》可借数量+1
UPDATE book
SET available_num = available_num + 1
WHERE book_id = 6;-- 3. 更新读者表：李四当前已借数量-1
UPDATE reader
SET current_borrow_num = current_borrow_num - 1
WHERE reader_id = '2025002001';-- 提交事务
COMMIT;-- 验证归还结果：查看李四的已借数量和图书可借数量
SELECT r.reader_name AS 读者姓名,r.current_borrow_num AS 当前已借数量,bo.book_name AS 归还图书,bo.available_num AS 图书可借数量
FROM reader r, book bo
WHERE r.reader_id = '2025002001' AND bo.book_id = 6;

运行结果：

读者姓名	当前已借数量	归还图书	图书可借数量
李四	1	人类简史	3

逻辑说明：归还后李四的 “当前已借数量” 从 2 减为 1，《人类简史》的 “可借数量” 从 2 增为 3，数据同步更新，避免库存与借阅状态不一致。

功能 4：查询逾期未还的图书（提醒催还）

需求描述：图书馆需要定期统计 “超过应还时间仍未归还” 的图书，显示读者信息、图书信息及逾期天数，方便催还。

核心逻辑：筛选 “归还状态 = 未归还” 且 “当前时间 > 应还时间” 的记录，计算逾期天数（当前时间 - 应还时间）。

SELECT r.reader_id AS 读者学号,r.reader_name AS 读者姓名,r.college AS 所属学院,bo.book_name AS 图书名称,bo.author AS 作者,b.borrow_time AS 借阅时间,b.due_time AS 应还时间,-- 计算逾期天数：当前时间-应还时间（DATEDIFF函数：后日期-前日期）DATEDIFF(NOW(), b.due_time) AS 逾期天数,r.reader_phone AS 读者手机号  -- 新增手机号字段（前文读者表可补充该字段，便于联系）
FROM borrow b
JOIN reader r ON b.reader_id = r.reader_id
JOIN book bo ON b.book_id = bo.book_id
WHERE b.return_status = '未归还'  -- 未归还的图书AND NOW() > b.due_time;  -- 当前时间超过应还时间（已逾期）

假设补充读者手机号后，运行结果：

读者学号	读者姓名	所属学院	图书名称	作者	借阅时间	应还时间	逾期天数	读者手机号
2025002001	李四	文学院	红楼梦	曹雪芹	2025-08-15 09:15:00	2025-09-14 23:59:59	22	13987654321

逻辑说明：通过DATEDIFF(NOW(), b.due_time)计算逾期天数，若结果为正数则表示已逾期，负数或 0 表示未逾期，帮助图书馆精准定位需要催还的读者。

功能 5：统计图书借阅排行（热门图书分析）

需求描述：统计所有图书的总借阅次数，按借阅次数降序排列，找出热门图书（借阅次数多的），为图书馆采购提供参考。

核心逻辑：按 “图书 ID / 名称” 分组，统计借阅次数（COUNT (borrow_id)），排除已取消的借阅记录（若系统有 “取消借阅” 状态）。

SELECT bo.book_id AS 图书ID,bo.book_name AS 图书名称,bo.category AS 图书分类,-- 统计总借阅次数（包含已归还和未归还，排除取消状态）COUNT(b.borrow_id) AS 总借阅次数,bo.available_num AS 当前可借数量
FROM book bo
LEFT JOIN borrow b ON bo.book_id = b.book_id AND b.return_status IN ('已归还', '未归还')  -- 只统计有效借阅（排除取消）
GROUP BY bo.book_id, bo.book_name, bo.category, bo.available_num
ORDER BY 总借阅次数 DESC  -- 按借阅次数降序，热门图书在前
LIMIT 10;  -- 显示前10本热门图书

运行结果：

图书 ID	图书名称	图书分类	总借阅次数	当前可借数量
3	红楼梦	文学	2	8
1	SQL 必知必会	计算机	1	4
5	数据结构（C 语言版）	计算机	1	4
6	人类简史	历史	1	3
...	...	...	...	...

逻辑说明：《红楼梦》借阅次数最多（2 次），属于热门图书，图书馆可考虑增加该图书的库存；计算机类图书整体借阅次数较多，符合学生学习需求。

功能 6：查询读者的借阅历史（个人借阅记录）

需求描述：读者张三（学号 2025001001）想查询自己所有的借阅记录，包括已归还和未归还的，显示图书信息、借阅时间、归还状态。

核心逻辑：按 “读者学号” 筛选，关联三表获取完整信息，按 “借阅时间降序” 排列（最新借阅在前）。

SELECT b.borrow_id AS 借阅编号,bo.book_name AS 图书名称,bo.author AS 作者,b.borrow_time AS 借阅时间,b.due_time AS 应还时间,b.return_time AS 归还时间,b.return_status AS 归还状态,-- 显示逾期状态（未归还且已逾期则标注“已逾期”）CASE WHEN b.return_status = '未归还' AND NOW() > b.due_time THEN '已逾期'ELSE '正常' END AS 借阅状态
FROM borrow b
JOIN book bo ON b.book_id = bo.book_id
WHERE b.reader_id = '2025001001'  -- 筛选张三的借阅记录
ORDER BY b.borrow_time DESC;  -- 按借阅时间降序

运行结果：

借阅编号	图书名称	作者	借阅时间	应还时间	归还时间	归还状态	借阅状态
7	MySQL 实战 45 讲	林晓斌	2025-09-10 16:30:00	2025-10-10 16:30:00	NULL	未归还	正常
1	SQL 必知必会	Ben Forta	2025-09-01 10:30:00	2025-09-30 23:59:59	NULL	未归还	正常

逻辑说明：张三当前有 2 本未归还图书，且均未逾期；若后续超过应还时间，“借阅状态” 会自动变为 “已逾期”，方便读者自查。

功能 7：图书入库（新增图书或补充库存）

需求描述：图书馆采购了 5 本《Python 编程：从入门到实践》（book_id=8），需要补充库存；同时新增 1 本新图书《Java 编程思想》，完成入库操作。

核心逻辑：补充库存用UPDATE（更新 total_num 和 available_num），新增图书用INSERT（插入新记录）。

场景 1：补充已有图书库存

-- 补充《Python编程：从入门到实践》库存5本
UPDATE book
SET total_num = total_num + 5,  -- 总库存+5available_num = available_num + 5  -- 可借数量同步+5
WHERE book_id = 8;-- 验证库存补充结果
SELECT book_name AS 图书名称, total_num AS 总库存, available_num AS 可借数量
FROM book WHERE book_id = 8;

运行结果：

图书名称	总库存	可借数量
Python 编程：从入门到实践	12	10

场景 2：新增图书入库

-- 新增图书《Java编程思想》
INSERT INTO book (isbn, book_name, author, publisher, total_num, available_num, category)
VALUES ('9787111213826',  -- ISBN'Java编程思想',    -- 书名'Bruce Eckel',    -- 作者'机械工业出版社',  -- 出版社3,                -- 总库存3,                -- 可借数量（新入库均为可借）'计算机'          -- 分类
);-- 验证新增结果
SELECT * FROM book WHERE book_name = 'Java编程思想';

运行结果：