SQL图书管理系统课程设计：表结构、事务与存储过程实战指南

简介：一份面向数据库课程设计的完整doc文档，围绕SQL图书管理系统展开，涵盖系统分析、E-R图、数据字典、关系模式、关系实例、查询描述及SQL实现语言，适合计算机相关专业学生完成课程设计或复习数据库应用技术时参考。文档以图书管理为业务场景，覆盖读者、馆员、管理员三类角色，以及读者信息、图书信息、借阅、还书、罚款等基础与业务数据，重点演示了从需求分析到表结构设计、SQL增删改查实现与系统测试的全过程。整份资源仅1个doc文件，压缩包约739KB。已有6481人学习下载，对需要完成相似课设或理解关系型数据库设计方法的读者具有直接参考价值。文档还包括书籍类别、读者、书籍、借阅、还书、罚款六个关系表的字段定义和E-R图设计，可直接用于报告撰写或二次开发。

1. SQL数据库图书管理系统课程设计，先从业务规则倒推表结构

数据库课程设计里“图书管理系统”是出现频率最高的题目，但大部分提交物都停在同一个水平：建了三张表，写了一段增删改查，再把 SQL 贴进 Word，完事。真正被扣分的地方往往不是语法，而是表结构有没有表达清楚业务规则：一本书有多个副本时怎么建模，借阅中的“未还”状态由谁记录，库存数字是真实数据还是可以被 UPDATE 改坏的口令。

“SQL数据库图书管理系统课程设计.doc”这个标题里的.doc，决定了交付物是一份能解释设计过程的文档，而不是一个能点按钮的界面。评审老师看的是 ER 图和数据字典是否一致、借书和还书有没有事务保护、逾期记录能否用一条 SQL 查出来。这篇文章从 SQL Server 2008 R2 及以上版本环境出发，把表结构、核心 SQL、存储过程和触发器一次说透，适合正在做课程设计的在校生，也适合要快速接手同类管理系统的开发人员。

2. 数据库设计：借阅规则定了，books、readers、borrow_records 三张表的字段才有依据

2.1 图书管理系统的 ER 图核心：把“副本库存”画进 Books 实体

课程设计里画 ER 图，最常见的问题是实体划分过细或者过粗。过粗的做法是只画“图书”和“读者”两个实体，中间拉一条线写上“借阅”，外键语义完全丢失；过细则把每一本物理书都建成一行，导致查询“《数据库原理》还有几本”的时候要聚合很多行，反而不直观。

我在做这类系统时习惯用“一种书一行”的模型：Books表的一行代表一个书目，total_copies表示馆藏册数，available_copies表示当前可借册数。借阅记录表BorrowRecords关联书目和读者，一行代表一次借出行为。这样 ER 图里有三个实体，其中BorrowRecords是联系实体，既关联Books又关联Readers，同时自身承载借出日期、应还日期、归还日期和状态字段。

业务规则在画图之前就必须明确。这里采用课程设计最常见的规则集：读者最多同时借 5 本，默认借期 30 天，逾期按每天 0.1 元计算罚金，读者状态为停借时不能借书。每一条规则最终都要落到表字段或存储过程校验上，ER 图中的属性就是这些字段的来源。

2.2 建表脚本：三张表完整定义及约束说明

CREATE DATABASE LibraryDB; GO USE LibraryDB; GO CREATE TABLE Books ( book_id INT IDENTITY(1,1) PRIMARY KEY, isbn VARCHAR(20) NOT NULL UNIQUE, title NVARCHAR(200) NOT NULL, author NVARCHAR(100) NOT NULL, publisher NVARCHAR(100) NULL, category NVARCHAR(50) NULL, total_copies INT NOT NULL DEFAULT 1 CHECK (total_copies > 0), available_copies INT NOT NULL DEFAULT 1 ); CREATE TABLE Readers ( reader_id INT IDENTITY(1,1) PRIMARY KEY, reader_name NVARCHAR(50) NOT NULL, phone VARCHAR(20) NULL, register_date DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE), status TINYINT NOT NULL DEFAULT 1 ); CREATE TABLE BorrowRecords ( record_id INT IDENTITY(1,1) PRIMARY KEY, book_id INT NOT NULL REFERENCES Books(book_id), reader_id INT NOT NULL REFERENCES Readers(reader_id), borrow_date DATE NOT NULL DEFAULT CAST(GETDATE() AS DATE), due_date DATE NOT NULL, return_date DATE NULL, status TINYINT NOT NULL DEFAULT 0 ); GO

Books表用IDENTITY(1,1)生成自增主键，UNIQUE约束保证 ISBN 不重复，CHECK约束控制总册数不能为负数。Readers的status字段用 1 和 0 表示正常和停借，比删除读者记录更安全。BorrowRecords是核心表，两个外键分别指向Books和Readers，return_date允许为空，空值表示未归还；status用 0、1、2 分别表示借出中、已归还、逾期未还。

三张表字段含义和常见错误总结如下：

2.3 可借数量字段的取舍：冗余但值得保留

available_copies是一个冗余字段，因为从BorrowRecords表完全可以统计出某本书当前被借出几本，用total_copies减去这个数字就能得到可借数量。正经的线上系统通常会避免这种冗余，而是在查询时聚合，但课程设计的数据量和演示场景决定了，保留这个字段的收益远大于维护成本。

冗余字段的核心风险是不一致，比如有人直接 UPDATE 了available_copies却没有改 BorrowRecords，或者借书事务中途失败造成数量只减不加。应对办法是把所有对available_copies的修改收敛到存储过程和触发器里，禁止在应用层直接写 UPDATE。第 4 章会给出具体实现，这里先记住一个原则：涉及库存变动的操作必须走数据库事务。

3. 图书管理系统核心 SQL：多条件查询、借书还书事务、逾期天数计算

3.1 多条件组合查询，参数化写法同时解决拼串和效率问题

图书查询页面通常允许按书名、分类、是否可借多个条件组合筛选。如果直接拼接字符串，关键词里带单引号就会破坏 SQL 结构，这就是搜索热词里“SQL注入”在图书管理系统中最常见的出现位置。课程设计里用存储过程配合参数化查询，既能让前端代码干净，也能挡住这种注入方式。

CREATE PROCEDURE sp_SearchBooks @keyword NVARCHAR(100) = '', @category NVARCHAR(50) = NULL, @onlyAvailable BIT = 0 AS BEGIN SET NOCOUNT ON; SELECT book_id, title, author, publisher, category, total_copies, available_copies FROM Books WHERE (@keyword = '' OR title LIKE '%' + @keyword + '%') AND (@category IS NULL OR category = @category) AND (@onlyAvailable = 0 OR available_copies > 0) ORDER BY book_id; END GO

这个存储过程把三个条件都用@变量 IS NULL或空串判断来“绕过”，也就是条件不传时不做筛选。参数由调用方传入，SQL Server 会把它当作变量而不是可执行代码，不存在单引号逃逸问题。LIKE '%' + @keyword + '%'实现了模糊匹配，代价是前导通配符会放弃索引，但图书表数据量通常在几千行以内，课程设计完全够用。

调用方式是EXEC sp_SearchBooks @keyword = N'数据库', @category = N'计算机', @onlyAvailable = 1。如果希望检索效率更高，可以改成title LIKE @keyword + '%'，但那样就搜不到书名中间的关键词了，属于业务需求取舍，不是技术对错。

3.2 借书和还书事务：UPDATE 和 INSERT 的先后顺序不能乱

借书操作需要完成两件事：减少Books表的可借数量，插入一条BorrowRecords记录。任何一件成功而另一件失败，都会让数据失去一致性，所以必须放进同一个事务。这里的核心技巧是，用带条件的 UPDATE 把“库存检查”和“库存扣减”合并成一条语句。

BEGIN TRANSACTION; IF (SELECT COUNT(*) FROM BorrowRecords WHERE reader_id = @reader_id AND return_date IS NULL) >= 5 BEGIN ROLLBACK TRANSACTION; RAISERROR('该读者借阅数量已达上限', 16, 1); RETURN; END UPDATE Books SET available_copies = available_copies - 1 WHERE book_id = @book_id AND available_copies > 0; IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION; RAISERROR('图书已无可借副本', 16, 1); RETURN; END INSERT INTO BorrowRecords (book_id, reader_id, due_date) VALUES (@book_id, @reader_id, DATEADD(DAY, 30, GETDATE())); COMMIT TRANSACTION;

UPDATE ... WHERE available_copies > 0这一句是关键。如果可借数量已经为 0，条件不成立，@@ROWCOUNT返回 0，说明扣减失败，直接回滚。这比先用 SELECT 查再判断的做法安全，因为 SELECT 和 UPDATE 之间可能被其他会话插入操作，条件 UPDATE 是一条原子语句，锁粒度更小，课程设计答辩时这个写法可以单独拿出来讲。

还书事务逻辑类似，方向相反：

BEGIN TRANSACTION; UPDATE BorrowRecords SET return_date = GETDATE(), status = 1 WHERE record_id = @record_id AND return_date IS NULL; IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION; RAISERROR('借阅记录不存在或已归还', 16, 1); RETURN; END UPDATE Books SET available_copies = available_copies + 1 WHERE book_id = @book_id; COMMIT TRANSACTION;

先更新借阅记录，再恢复库存，是为了让return_date IS NULL条件能挡住重复还书操作。一个读者在还书接口上连点两次，第二次会因为@@ROWCOUNT = 0而被拒绝，不会出现库存被多加一次的情况。

3.3 DATEDIFF 计算逾期天数，视图固定逾期清单

逾期查询是课程设计必备功能，SQL Server 里用DATEDIFF(DAY, due_date, GETDATE())计算从应还日期到当前日期的天数差值。只有当return_date为空并且due_date早于今天时，才算逾期未还。

SELECT br.record_id, r.reader_name, b.title, br.due_date, DATEDIFF(DAY, br.due_date, GETDATE()) AS overdue_days, DATEDIFF(DAY, br.due_date, GETDATE()) * 0.1 AS fine_amount FROM BorrowRecords br JOIN Readers r ON br.reader_id = r.reader_id JOIN Books b ON br.book_id = b.book_id WHERE br.return_date IS NULL AND br.due_date < GETDATE() ORDER BY overdue_days DESC;

DATEDIFF的日期单位参数写成DAY时返回整天数差异。罚金按 0.1 元每天计算，这里直接写成表达式，而不是存一个字段。这样设计的好处是，如果调整罚金标准只需要改查询语句，不需要 UPDATE 大量历史数据，不会出现“还书日期改了但罚金没重算”的问题。

这个查询在系统中会被重复使用，直接建视图：

CREATE VIEW v_OverdueRecords AS SELECT br.record_id, r.reader_name, b.title, br.due_date, DATEDIFF(DAY, br.due_date, GETDATE()) AS overdue_days FROM BorrowRecords br JOIN Readers r ON br.reader_id = r.reader_id JOIN Books b ON br.book_id = b.book_id WHERE br.return_date IS NULL AND br.due_date < GETDATE(); GO

有了视图，前端页面只需要SELECT * FROM v_OverdueRecords，不需要感知背后的连接关系。课程设计文档里把这条 SQL 单独放一节，再加上执行结果截图，逾期管理的完整性就很清晰了。

4. 进阶实现：存储过程封装借书流程，触发器维护库存并对账

4.1 将借书业务做成存储过程，把参数校验放到数据库层

第 3 章给出的借书事务可以直接执行，但更好的做法是封装成存储过程，让前端 UI 只传@book_id和@reader_id两个参数。存储过程比应用层拼 SQL 多一层好处：所有借阅规则都在数据库这一侧能被复核，评审打开 SQL 文件就能看懂业务约束，不需要翻代码。

CREATE PROCEDURE sp_BorrowBook @book_id INT, @reader_id INT AS BEGIN SET NOCOUNT ON; BEGIN TRANSACTION; IF NOT EXISTS (SELECT 1 FROM Readers WHERE reader_id = @reader_id AND status = 1) BEGIN ROLLBACK TRANSACTION; RAISERROR('读者不存在或已被停借', 16, 1); RETURN; END IF (SELECT COUNT(*) FROM BorrowRecords WHERE reader_id = @reader_id AND return_date IS NULL) >= 5 BEGIN ROLLBACK TRANSACTION; RAISERROR('超出最大借阅数量', 16, 1); RETURN; END UPDATE Books SET available_copies = available_copies - 1 WHERE book_id = @book_id AND available_copies > 0; IF @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION; RAISERROR('图书已无可借副本', 16, 1); RETURN; END INSERT INTO BorrowRecords (book_id, reader_id, due_date) VALUES (@book_id, @reader_id, DATEADD(DAY, 30, GETDATE())); COMMIT TRANSACTION; END GO

执行方式为EXEC sp_BorrowBook @book_id = 1, @reader_id = 1001。三个校验按代价从小到大排列：先查读者状态和借阅数量，这两个是轻量 SELECT，把不合格请求挡在库存扣减之前；最后执行条件 UPDATE，把并发场景下的库存竞争缩到最短。这种顺序体现出对事务粒度的理解，是答辩中很加分的细节。

4.2 触发器自动维护 available_copies，与存储过程方案二选一

存储过程方案要求所有开发人员都必须走sp_BorrowBook，如果有人直接在 SSMS 里 INSERT 一条借阅记录，库存不会被扣减。触发器可以强制这种一致性，它把“每次新增借阅记录时自动扣库存”变成数据库自身的约束力。

CREATE TRIGGER trg_Borrow_Insert ON BorrowRecords AFTER INSERT AS BEGIN SET NOCOUNT ON; UPDATE b SET b.available_copies = b.available_copies - 1 FROM Books b INNER JOIN inserted i ON b.book_id = i.book_id; END GO

inserted是 SQL Server 触发器中的虚拟表，保存被插入的新行。这个触发器在借阅记录新增后自动把对应书目的available_copies减 1，不需要应用层手动调用。逻辑上，只要BorrowRecords表里多了一条未归还记录，库存就必须减少。

需要注意一个关键点：如果存储过程里已经写了UPDATE Books SET available_copies = available_copies - 1，再叠加这个触发器，库存会被扣两次。这两种方案必须二选一：要么用存储过程显式扣库存，要么只插入借阅记录、由触发器负责扣减。我一般建议课程设计采用触发器方案，因为演示时直接往表里插入测试数据也能看到库存变化，对评审来说更直观。

4.3 索引设计与触发器连用的审计日志

BorrowRecords表的数据量不大，但查询条件集中在reader_id + return_date和due_date上，建两个索引就能覆盖所有核心查询：

CREATE NONCLUSTERED INDEX idx_borrow_reader ON BorrowRecords(reader_id, return_date); CREATE NONCLUSTERED INDEX idx_borrow_due ON BorrowRecords(due_date) WHERE return_date IS NULL; GO

第二个是筛选索引，只索引未归还的记录，体积更小。配合第 3 章的逾期视图，这类索引能明显加速due_date < GETDATE()的查询。

用触发器记录借还操作的审计日志，是比索引更直观的加分项。先建一张日志表，再挂一个 UPDATE 触发器：

CREATE TABLE BorrowLog ( log_id INT IDENTITY(1,1) PRIMARY KEY, record_id INT NOT NULL, action_type VARCHAR(10) NOT NULL, action_time DATETIME NOT NULL DEFAULT GETDATE() ); GO CREATE TRIGGER trg_Borrow_Log ON BorrowRecords AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT INTO BorrowLog (record_id, action_type) SELECT i.record_id, 'RETURN' FROM inserted i INNER JOIN deleted d ON i.record_id = d.record_id WHERE d.return_date IS NULL AND i.return_date IS NOT NULL; END GO

触发器通过对比deleted和inserted两张虚拟表，能判断哪些记录的return_date从 NULL 变成了有值，也就是真正发生了归还行为。审计日志记录了每次归还的时间，课程设计文档里写一节“数据审计与安全”，这个触发器就是最实在的支撑材料。

5. 课程设计报告打磨：把 .doc 里的 ER 图和 SQL 代码对齐成一套材料

5.1 数据字典：让表结构与代码一一对应

.doc报告里的核心评审依据是数据字典。很多同学提交的文档里，表名是book和books混用，字段注释和实际 SQL 对不上，评审一执行脚本就报错。我在整理课程设计文档时，会先建一张数据字典表，把字段名、类型、默认值、约束逐一列清楚，再去对照建表脚本。

文档里放这张表，数据字典和三范式分析就都有了素材。每个字段的业务含义要写清楚，尤其是return_date的空值约定和status的枚举值，这两个位置最容易在答辩时被追问。

5.2 演示脚本顺序与报告截图技巧

演示时要按“查询 → 借书 → 验证库存减少 → 还书 → 验证库存恢复 → 逾期查询”的顺序走，每一步都截图插入报告。借书前后各查一次available_copies，两张截图放在一起最有说服力。

验证逾期功能时，不要等 30 天，直接在 SSMS 里UPDATE BorrowRecords SET borrow_date = DATEADD(DAY, -40, GETDATE()), due_date = DATEADD(DAY, -10, GETDATE())造一条假数据，再查v_OverdueRecords视图。这条 UPDATE 语句本身也要写进报告的测试用例部分，说明逾期计算的边界值已经验证过。

报告最后附上建库建表脚本和两张关键视图的 SQL，并标注测试数据量。评审真正关心的是你的代码能一次跑通，以及你能否讲清楚每条规则背后对应的数据库机制。能做到这一层，这个课程设计就完整了。

本文还有配套的精品资源，点击获取