简介《SQL数据库图书管理系统(完整代码).doc》是一份面向高校数据库课程设计、毕业设计入门及SQL初学者参考的完整文档型案例围绕图书馆借阅管理场景解决读者信息、图书库存、借还记录与超期罚款等数据组织混乱的问题。文档将系统分析、数据库存储设计、E-R图、数据字典、关系模式与关系实例串联起来并给出借书、还书、罚款等模块的查询描述、关系代数表达及SQL实现与结果便于对照课程要求理解从概念模型到关系模型的落地过程。压缩包内仅含1个doc文件大小约709KB单文件即可覆盖设计报告主体目录按设计目标、数据需求、方案图表和SQL实现组织查阅路径较清晰。目前已有2318人学习适合需要课程设计模板、数据库建模范例或SQL查询练习素材的读者参考。1. 从一份 Word 里粘出来的 SQL 说起这套图书管理系统到底能跑出什么上周有个同学发来一个压缩包里面就一份 .doc标题写着「SQL 数据库图书管理系统(完整代码)」。他原本的想法很简单要么直接交课程设计要么跑起来给自己毕设打个底。结果打开文档发现前半截是 E-R 图和数据字典后半截才是建库建表的脚本中间还夹着几张截图根本不知道从哪下手。我翻完之后的结论是这套东西的业务闭环是完整的——读者、图书、借阅、还书、罚款五块业务都有对应的表和 SQL但它毕竟是从 Word 里复制出来的直接粘进查询窗口会连着报几个错得先修三处才能真正跑通。它适合两类人一是要交数据库课程设计、手上缺一套能自圆其说的表结构和查询集合的二是刚学完 SELECT 和 JOIN想找个带真实业务约束的小库练手的。下面按我自己复现的顺序拆。2. 结构拆解六张表撑起的借还闭环和它的 E-R 关系拿到一个数据库项目我习惯先不看脚本先看它有几张表、表之间怎么连。这套系统的全部逻辑就压在六张表上三张存主数据三张存流水。搞清楚这个划分后面所有的 SQL 都是在它们之间搬数据。2.1 三张主数据表加三张流水表的分工先把六张表摆出来看表名定位主键关键字段book_style书籍类别字典bookstylenobookstyleno、bookstylesystem_readers读者主数据readeridreaderid、readername、readersex、readertype、regdatesystem_books图书主数据bookidbookid、bookname、bookstyleno、bookauthor、bookpub、bookpubdate、bookindate、isborrowedborrow_record在借流水bookid原版写法bookid、readerid、borrowdatereturn_record归还历史bookidbookid、readerid、returndatereader_fee罚款单bookidreaderid、readername、bookid、bookname、bookfee、borrowdate这个划分本身是合格的主数据表负责「有什么」流水表负责「发生了什么」查询的时候主数据提供名字流水表提供时间线。真正值得说的是每张表在做字段取舍时做的那些妥协。book_style用varchar(30)的类别编号当主键而不是自增 ID。好处很直接——灌测试数据的时候insert into book_style values(1,科幻小说)人工可读写 SQL 的时候也不用先去查一遍 ID 是多少。代价是编号一旦定下来就不太好改改一次要连带更新system_books里所有引用它的行而原脚本没有写ON UPDATE CASCADE所以实际改起来会直接报外键冲突。system_books里最容易被忽略的是isborrowed这个字段。它是个冗余状态位理论上「这本书在不在馆」完全可以用borrow_record里有没有这条记录来推导根本不需要单独存。但原作者还是加了原因也不难理解列表页要显示一整屏书如果每本书都去borrow_record做一次NOT EXISTS判断查询会长很多存一个两位的状态位where isborrowed 1一句就筛出来了。这是典型的用一点冗余换查询便利在数据量小的课程设计里完全划算但要求所有借还操作都必须同步更新这个字段——一旦漏更新状态位和流水表就对不上系统会开始说谎。system_readers里的readertype是区分学生、教师、管理员用的。字段在但脚本里没有对应的规则表借书上限、借期天数这些和身份挂钩的参数全都没有落地后面算罚款的时候只能写死。这也是从课程设计过渡到能用的小系统时最先要补的一块。三张流水表里borrow_record和return_record结构几乎一模一样本质是把「在借」的行搬到「已还」去形成一条历史。reader_fee则是明显的反范式它把readername和bookname又存了一遍。这么做不是设计失误而是为了打印罚款单的时候不用 join 三张表——罚款单是要给人看的凭证凭证上的名字应该定格在当时就算读者后来改名了那张单一动不动才对。2.2 实体关系落到主外键上方向、基数和一个埋雷E-R 图那部分文档里画了六张实体分别是类别、读者、图书、借阅记录、归还记录和罚款记录。把它们翻译成关系模式联系其实只有四条类别 1 : N 图书一本书只属于一个类别一个类别下可以有很多本书靠system_books.bookstyleno外键指向book_style实现。读者 1 : N 借阅一本书可以被不同读者在不同时间借一个读者也可以借多本。图书 1 : N 借阅同一本书在时间线上会被借很多次。借阅到归还、借阅到罚款都是一对一的衍生关系本质上罚款和归还都是某次借阅行为的后续结果。前三条在 DDL 里被写成了外键约束方向没问题。问题出在基数被主键意外地限制死了borrow_record把bookid设成了主键。主键意味着唯一而bookid唯一就意味着「一本书在整个系统里只能有一条借阅记录」。这本书被第一个人借走、还回来、第二个人再借第二条插入语句直接主键冲突报错。这就是整份脚本里最要命的一处我在第 5 章会把它单独拎出来讲透。外键的级联动作这里也要说一句。原脚本写的是foreign key (bookstyleno) references book_style(bookstyleno)没带ON DELETE和ON UPDATESQL Server 默认就是NO ACTION——想删一个还有书在用的类别直接拒绝。这在教学场景里是好事能防止误删但如果以后要做「删除类别时把下面的书一起归档」就得显式改成ON DELETE CASCADE或者干脆不用物理外键、改在应用层校验。我一般倾向后者因为外键的报错信息对前端很不友好用户看到的是「违反 FOREIGN KEY 约束」根本不知道自己做错了什么。还有个细节几张表的主键约束都是匿名创建的没写CONSTRAINT名字。这在开发期会带来一个隐形成本——想删掉或者改这个约束的时候得先去sys.key_constraints里查它叫PK__borrow_r__xxxxx这种自动生成的名字。字段多了以后这种名字完全记不住改一次表就得查一次。我在自己的项目里一定会把约束名显式写出来比如CONSTRAINT pk_borrow_record PRIMARY KEY后面维护的时候能省很多事。3. 建库建表实操从 CREATE DATABASE 到六张表灌数据结构清楚了就可以动手了。这一章按顺序走建库、建表、灌数据。全程在 SQL Server Management Studio 的新建查询窗口里执行粘一段跑一段不要一次全选。3.1 建库语句里三个参数必须改原脚本的建库部分是这样的思路连到 master然后CREATE DATABASE指定数据文件和日志文件的初始大小、上限、增长率。逻辑没错但文件路径写的是c:\这一行执行下去必定报错——FILENAME要的是带文件名的完整路径不是目录。我把它改成了下面这样USE master; GO -- 数据库不存在才创建重复执行不会报错 IF DB_ID(library_system) IS NULL BEGIN CREATE DATABASE library_system ON ( NAME library_data, -- 逻辑文件名 FILENAME D:\db\library_data.mdf, -- 物理路径目录必须先存在 SIZE 10MB, MAXSIZE 500MB, FILEGROWTH 5MB ) LOG ON ( NAME library_log, FILENAME D:\db\library_log.ldf, SIZE 5MB, MAXSIZE 100MB, FILEGROWTH 5MB ); END GO USE library_system; GOFILENAME指向的目录D:\db必须提前建好SQL Server 不会帮你创建目录只会在找不到时抛一个 Operating system error 出来这个报错看着像权限问题实际是路径不存在很容易排查跑偏。FILEGROWTH 5MB的意思是每次空间不够就扩 5MB。如果写成FILEGROWTH 10%就是按比例扩数据文件大了以后一次扩出来一大块反而容易卡住。小库我一般固定按 MB 扩行为更可预期。SIZE是初始大小日志文件别给太小事务一多会频繁增长影响写入。GO这个要单独说一句它不是 SQL 语句是 SSMS 的批处理分隔符。USE master之后必须跟一个GO才能保证后面的CREATE DATABASE在一个「已经连到 master」的会话里执行。不少人把GO当普通 SQL 发给驱动执行程序里就会直接报语法错误——应用代码里千万不要带GO。3.2 六张表的建表顺序和修正后的 DDL建表有顺序被引用的表要先建。book_style、system_readers、system_books三张是基础先建borrow_record、return_record、reader_fee引用了它们后建。顺序错了会报「对象名无效」。-- 1. 类别字典 CREATE TABLE dbo.book_style ( bookstyleno varchar(30) NOT NULL CONSTRAINT pk_book_style PRIMARY KEY, bookstyle nvarchar(30) NOT NULL ); -- 2. 读者主数据 CREATE TABLE dbo.system_readers ( readerid varchar(20) NOT NULL CONSTRAINT pk_system_readers PRIMARY KEY, readername nvarchar(20) NOT NULL, -- 中文姓名用 nvarchar别用 varchar readersex nvarchar(2) NOT NULL, readertype nvarchar(10) NULL, regdate datetime NULL ); -- 3. 图书主数据 CREATE TABLE dbo.system_books ( bookid varchar(20) NOT NULL CONSTRAINT pk_system_books PRIMARY KEY, bookname nvarchar(60) NOT NULL, bookstyleno varchar(30) NOT NULL, bookauthor nvarchar(30) NULL, bookpub nvarchar(50) NULL, bookpubdate date NULL, bookindate date NULL, isborrowed char(1) NOT NULL CONSTRAINT df_book_isborrowed DEFAULT 1, CONSTRAINT fk_book_style FOREIGN KEY (bookstyleno) REFERENCES dbo.book_style (bookstyleno) ); -- 4. 在借流水加自增代理键bookid 只做普通索引 CREATE TABLE dbo.borrow_record ( borrowid bigint IDENTITY(1,1) NOT NULL CONSTRAINT pk_borrow_record PRIMARY KEY, bookid varchar(20) NOT NULL, readerid varchar(20) NOT NULL, borrowdate datetime NOT NULL CONSTRAINT df_borrow_date DEFAULT GETDATE(), CONSTRAINT fk_borrow_book FOREIGN KEY (bookid) REFERENCES dbo.system_books (bookid), CONSTRAINT fk_borrow_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers (readerid) ); CREATE INDEX ix_borrow_book ON dbo.borrow_record (bookid); CREATE INDEX ix_borrow_reader ON dbo.borrow_record (readerid); -- 5. 归还历史 CREATE TABLE dbo.return_record ( returnid bigint IDENTITY(1,1) NOT NULL CONSTRAINT pk_return_record PRIMARY KEY, bookid varchar(20) NOT NULL, readerid varchar(20) NOT NULL, returndate datetime NOT NULL, CONSTRAINT fk_return_book FOREIGN KEY (bookid) REFERENCES dbo.system_books (bookid), CONSTRAINT fk_return_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers (readerid) ); -- 6. 罚款单保留姓名书名冗余为了打印凭证 CREATE TABLE dbo.reader_fee ( feeid bigint IDENTITY(1,1) NOT NULL CONSTRAINT pk_reader_fee PRIMARY KEY, readerid varchar(20) NOT NULL, readername nvarchar(20) NOT NULL, bookid varchar(20) NOT NULL, bookname nvarchar(60) NOT NULL, bookfee decimal(8,2) NOT NULL, -- 金额用 decimal别用 varchar borrowdate datetime NOT NULL, CONSTRAINT fk_fee_book FOREIGN KEY (bookid) REFERENCES dbo.system_books (bookid), CONSTRAINT fk_fee_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers (readerid) );和原脚本比我动了四个地方每一个后面都会出问题第一borrow_record和return_record加了IDENTITY自增主键把bookid降级成普通索引。原版拿bookid当主键一本书只能被借一次这在测试阶段就会撞墙。第二所有中文字段从varchar换成nvarchar。原版的readername varchar(9)在非 Unicode 排序规则下一个汉字占 2 字节9 字节最多存 4 个汉字五个字的名字会被静默截断不报错但数据就错了。第三金额字段bookfee从varchar(30)改成decimal(8,2)。用字符串存金额早晚会遇到1.5 2.3算出1.52.3这种事而且没法做求和统计。第四isborrowed从varchar(2)收成char(1)并给了默认值11 表示在馆0 表示借出。宽度收窄是为了让取值只有一个字符杜绝1 和1在WHERE里匹配不上的情况。提示如果你的排序规则是Chinese_PRC_CI_AS这类非 Unicode 规则varchar存中文虽然能存但跨库、跨版本迁移时极易出现乱码新表一律用nvarchar能省掉很多事。3.3 三批初始化数据类别、图书、读者建完表要灌数据否则后面查什么都是空。顺序还是老规矩先灌book_style再灌system_books和system_readers最后才是流水。-- 第一批书籍类别 INSERT INTO dbo.book_style (bookstyleno, bookstyle) VALUES (1, N科幻小说), (2, N历史小说), (3, N悬疑小说), (4, N都市小说), (5, N武侠小说), (6, N仙侠小说), (7, N言情小说); -- 第二批图书主数据isborrowed 默认 1 表示在馆 INSERT INTO dbo.system_books (bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed) VALUES (A001, N《星轨》, 1, N沈之远, N某文艺出版社, 2011-03-12, 2013-05-25, 1), (A002, N《归途》, 2, N陆青, N某综合出版社, 2009-07-01, 2013-05-26, 1), (A003, N《孤城笔记》,3, N白露, N某文艺出版社, 2008-11-20, 2013-05-27, 1), (A004, N《楼下咖啡》,4, N周南, N某大学出版社, 2010-04-08, 2013-05-28, 1), (A005, N《剑庐旧话》,5, N顾行舟, N某综合出版社, 2007-09-18, 2013-05-29, 1), (A006, N《云上九歌》,6, N林间客, N某文艺出版社, 2012-01-05, 2013-05-30, 1), (A007, N《初夏信笺》,7, N苏白, N某大学出版社, 2011-06-20, 2013-05-31, 1); -- 第三批读者 INSERT INTO dbo.system_readers (readerid, readername, readersex, readertype, regdate) VALUES (R2013001, N林小满, N女, N学生, 2013-01-18 12:20), (R2013002, N陈知远, N男, N学生, 2013-01-19 13:15), (R2013003, N周青, N女, N学生, 2013-01-20 13:33), (R2013004, N许南, N男, N学生, 2013-01-21 12:01), (T201001, N吴明, N男, N教师, 2013-01-23 18:50), (T201002, N陆行, N男, N教师, 2013-01-24 18:25), (G0001, N郑怀, N女, N管理, 2013-01-01 16:20);日期字面量我统一用了2011-03-12这种 ISO 格式。这一点很关键2011/03/12和03-12-2011在某些DATEFORMAT或者语言设置下会被解析成不同的日期而yyyy-mm-dd是唯一在任何设置下都不会歧义的写法。用date类型存出版日期和登记日期用datetime存借还时间是因为借还业务要精确到时分超期判定才有依据。一次性插入多行VALUES的写法较新版本的 SQL Server 都支持如果你的环境只认单行插入就把它拆成一条条INSERT逻辑一样。灌完之后跑一句SELECT COUNT(*) FROM dbo.system_books确认一下条数比在结果窗口里一行行数要靠谱。4. 业务 SQL 落地借书、还书、罚款和统计的写法表和数据有了可以开始写业务。这一章我不按「增删改查」这种教科书分类直接按真实的操作链路走一次借书发生什么、一次还书发生什么、罚款怎么算出来、报表怎么拉。每个操作我都会写成存储过程把校验和事务包进去因为这几步在原始脚本里是靠人手依次执行几条 SQL 完成的一旦中间断了数据就是错的。4.1 借书一条 INSERT 加一条 UPDATE 必须包在事务里借书这件事在系统里其实是两个动作往borrow_record里插一条流水同时把system_books的isborrowed改成0。这两步必须同生共死否则会出现「流水里有记录但书还显示在馆」或者反过来的幽灵状态。CREATE PROCEDURE dbo.usp_borrow_book bookid varchar(20), readerid varchar(20), borrowdate datetime NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 出任何错误自动回滚整个事务 IF borrowdate IS NULL SET borrowdate GETDATE(); BEGIN TRY BEGIN TRANSACTION; -- 1. 校验图书存在且在馆用 UPDLOCK 锁住这一行防并发重复借 IF NOT EXISTS ( SELECT 1 FROM dbo.system_books WITH (UPDLOCK, ROWLOCK) WHERE bookid bookid AND isborrowed 1 ) RAISERROR(N图书不存在或已借出无法借阅, 16, 1); -- 2. 校验读者存在 IF NOT EXISTS (SELECT 1 FROM dbo.system_readers WHERE readerid readerid) RAISERROR(N读者不存在请先登记, 16, 1); -- 3. 写借阅流水 INSERT INTO dbo.borrow_record (bookid, readerid, borrowdate) VALUES (bookid, readerid, borrowdate); -- 4. 同步状态位 UPDATE dbo.system_books SET isborrowed 0 WHERE bookid bookid AND isborrowed 1; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; -- 原样抛出保留原始错误号和信息 END CATCH END几个参数和写法要解释一下。borrowdate给了默认值NULL调用时不传就取当前时间这样补录历史借阅记录的时候可以指定过去的时间。SET XACT_ABORT ON让任何运行时错误都自动回滚事务比手动判断ERROR稳得多。WITH (UPDLOCK, ROWLOCK)是给这一行加更新锁防止两个管理员几乎同时点借书双双通过「在馆」校验后各插一条流水——这种问题在单人测试时根本发现不了上线第一天就会冒出来。BEGIN CATCH里用THROW原样抛出错误调用方拿到的还是原始的RAISERROR消息。老版本 SQL Server 不支持THROW那就换成RAISERROR(msg, 16, 1)手动拼一下效果差不多。4.2 还书用 OUTPUT 把流水一次性搬走还书的链路比借书长一步从borrow_record删掉这条在借记录、往return_record插一条归还记录、把书的状态改回在馆。最笨的写法是SELECT出来再INSERT然后DELETE三步都在事务里。更省事的是用DELETE ... OUTPUT删的同时把行输出到归还表一条语句同时完成搬迁。CREATE PROCEDURE dbo.usp_return_book bookid varchar(20), readerid varchar(20), returndate datetime NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF returndate IS NULL SET returndate GETDATE(); BEGIN TRY BEGIN TRANSACTION; -- 先把在借记录输出到归还表同时删除 DELETE FROM dbo.borrow_record OUTPUT deleted.bookid, deleted.readerid, returndate INTO dbo.return_record (bookid, readerid, returndate) WHERE bookid bookid AND readerid readerid; -- 一条都没删掉说明这本书压根没被这个读者借着 IF ROWCOUNT 0 RAISERROR(N未找到对应的在借记录请核对图书编号和读者编号, 16, 1); -- 恢复在馆状态 UPDATE dbo.system_books SET isborrowed 1 WHERE bookid bookid; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH ENDOUTPUT ... INTO的顺序要注意deleted.前缀在DELETE里代表被删掉的那行inserted.在DELETE里不可用。输出列的顺序必须和INTO目标表的列顺序完全对齐不能靠列名自动匹配这是这套语法最容易翻车的地方。另外ROWCOUNT必须紧跟在DELETE后面取值中间插一句别的它就变了。4.3 罚款超期天数怎么算才不扯皮罚款这块原脚本只留了一张表和一个bookfee字段规则没写。真实场景里得约定三个参数借期多长、超期每天罚多少、有没有封顶。我一般把借期设成 30 天、日罚金 0.20 元规则写在一个查询里先跑出来看确认没问题再往reader_fee里插。DECLARE limit_days int 30; -- 借期上限 DECLARE fee_per_day decimal(8,2) 0.20; -- 每天罚金 DECLARE max_fee decimal(8,2) 20.00; -- 单本封顶 SELECT b.readerid, r.readername, b.bookid, k.bookname, b.borrowdate, CAST(GETDATE() AS date) AS 今天, DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) AS 已借天数, CASE WHEN DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) limit_days THEN CASE WHEN (DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) - limit_days) * fee_per_day max_fee THEN max_fee ELSE (DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) - limit_days) * fee_per_day END ELSE 0 END AS 应罚金额 FROM dbo.borrow_record b JOIN dbo.system_readers r ON r.readerid b.readerid JOIN dbo.system_books k ON k.bookid b.bookid WHERE DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) limit_days;DATEDIFF(DAY, ...)是按「日期的天」算的跨过零点就算一天不管中间隔了几小时。所以两边都CAST(... AS date)把时分秒切掉算出来的天数是整数不会因为借书时间是 23:50、还书是 00:10 就产生「借了 0 天」这种怪结果。这个坑我在好几个项目里都见过——有人直接拿两个datetime相减得出个小数天数再四舍五入最后罚款金额每次都对不上。封顶那一层CASE是可选的加了以后好和读者解释「最多罚 20」。确认金额之后把整个SELECT改成INSERT INTO reader_fee (...) SELECT ...就落库了因为reader_fee里冗余了readername和bookname插入的时候顺手从JOIN结果里取一条语句搞定。4.4 统计查询三个最常被要的报表系统交付之后被问得最多的永远不是增删改是几张报表。这里放三个我最常写的-- 报表一每个类别的借阅次数排名 SELECT s.bookstyle AS 类别, COUNT(*) AS 借阅次数 FROM dbo.borrow_record b JOIN dbo.system_books k ON k.bookid b.bookid JOIN dbo.book_style s ON s.bookstyleno k.bookstyleno GROUP BY s.bookstyle ORDER BY COUNT(*) DESC; -- 报表二借阅量最高的前五位读者 SELECT TOP 5 r.readername AS 读者, COUNT(*) AS 借阅册次 FROM dbo.borrow_record b JOIN dbo.system_readers r ON r.readerid b.readerid GROUP BY r.readername ORDER BY COUNT(*) DESC; -- 报表三从未被借出过的图书用 NOT EXISTS不要用 NOT IN SELECT k.bookid, k.bookname FROM dbo.system_books k WHERE NOT EXISTS ( SELECT 1 FROM dbo.borrow_record b WHERE b.bookid k.bookid );报表二的TOP 5要加WITH TIES吗不用借阅册次相同的读者很少挤一挤无所谓。报表三特意用了NOT EXISTS而不是NOT IN——一旦子查询里出现NULLNOT IN会整条返回空集这个坑太经典了养成习惯直接用NOT EXISTS能省掉一次排查。5. 踩坑排查这份完整代码里藏着的五个坑下面五条是我从这份脚本里实际撞出来的每条都按「现象 → 原因 → 解决」写你照着自己跑一遍大概率会遇上其中三四个。5.1 图书建了第一条借阅记录后第二个人就借不了现象同一个bookid第一次借书成功还回去以后第二次借直接报「违反 PRIMARY KEY 约束不能在对象 dbo.borrow_record 中插入重复键」。原因borrow_record把bookid设成了主键。主键的唯一性约束在整张表上生效意味着这本物理书在系统生命周期里只能被借一次。这明显不是作者的意图只是写 DDL 时顺手把「业务上唯一的那本书」和「表里唯一的行」搞混了。解决加自增代理键borrowidbookid降级成普通列并建非唯一索引。第 3 章的 DDL 已经改好了。如果数据已经灌进去了改成ALTER TABLE dbo.borrow_record DROP CONSTRAINT pk_borrow_record; ALTER TABLE dbo.borrow_record ADD borrowid bigint IDENTITY(1,1) NOT NULL; ALTER TABLE dbo.borrow_record ADD CONSTRAINT pk_borrow_record PRIMARY KEY (borrowid); CREATE INDEX ix_borrow_book ON dbo.borrow_record (bookid);5.2 读者初始数据插到第二条就报主键冲突现象执行读者初始化的INSERT第一条成功第二条开始全部报重复键。原因原脚本里所有读者的readerid都写成了同一个值Q。偏偏readerid是主键。这是典型的复制粘贴没改干净八个读者共用了一个编号。解决给每个读者一个唯一编号规则自己定比如学生用R加入学年份加序号、教师用T开头、管理员用G开头。第 3 章的示例数据就是这么编的。写完之后检查一遍SELECT readerid, COUNT(*) FROM system_readers GROUP BY readerid HAVING COUNT(*) 1返回空才算干净。5.3 更新图书状态时明明只该改一行结果整张表都变了现象执行UPDATE system_books SET isborrowed 0 WHERE bookid 901 AND isborrowed 1影响行数是 0书的状态没变把1去掉引号后又变成影响了几百行。原因isborrowed是varchar类型存的是字符1。写isborrowed 1时 SQL Server 会做隐式转换把1转成数字 1 来比较这一列上原本就有的索引直接失效变成全表扫描如果数据里还混杂了1 带空格、01这类值比较结果会变得完全不可预期。原始的isborrowed varchar(2)宽度给到 2就是这种混乱的温床。解决宽度收成char(1)加DEFAULT 1查询时永远带引号写isborrowed 1。再狠一点可以加个CHECK (isborrowed IN (0,1))约束把脏值堵在入口。5.4 建库语句执行报操作系统错误看着像权限问题现象CREATE DATABASE报 Operating system error 3 (The system cannot find the path specified) 或者类似的路径错误。原因FILENAME写成了c:\。这个参数要求的是带扩展名的完整物理路径不是目录。另外SIZE、MAXSIZE不给单位时默认按 MB 算但FILEGROWTH如果想用百分比就必须写%混着写容易踩空。解决FILENAME D:\db\library_data.mdf先手动把D:\db建好别指望数据库引擎帮你建目录。日志文件也用LOG ON单独指定不要和数据文件放同一个物理磁盘如果只有一块盘那就无所谓了。5.5 中文姓名存进去变成了问号或者被截断现象插入林小满正常插入五个字的名字时后半截没了或者在某些环境里显示成???。原因字段类型是varchar(9)。非 Unicode 的varchar在中文排序规则下一个汉字占 2 字节9 字节最多存 4 个汉字超出部分静默截断连个警告都没有。跨实例迁移或者换排序规则以后还会直接显示成问号。解决所有存中文的字段一律用nvarchar长度按字符数给比如nvarchar(20)就是 20 个汉字。插入时字符串前面加N写成N林小满告诉 SQL Server 这是 Unicode 字面量不加的话仍然可能按本地代码页转换出问题。6. 二次开发把课程设计改成能用的借阅系统的改造路径这套脚本跑通之后离「能用」还有段距离。我的改造顺序是这样的按优先级排优先级改造项目的P0补代理键、换 nvarchar、金额改 decimal消除数据错误的根源P0借还操作全部封成存储过程并加事务保证状态位和流水一致P1抽 v_borrow_detail 视图应用层查询不用每次写三表 JOINP1加借阅上限、借期天数的规则表罚款规则不再写死在 SQL 里P2加操作日志表谁在什么时候改了哪条记录能查视图这一步收益最直接应用层拿书列表的时候直接SELECT * FROM v_borrow_detail WHERE isborrowed 1把三张表的 JOIN 和中文列名都封在数据库里CREATE VIEW dbo.v_borrow_detail AS SELECT b.borrowid, b.borrowdate, r.readerid, r.readername, r.readertype, k.bookid, k.bookname, s.bookstyle, DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) AS 已借天数 FROM dbo.borrow_record b JOIN dbo.system_readers r ON r.readerid b.readerid JOIN dbo.system_books k ON k.bookid b.bookid JOIN dbo.book_style s ON s.bookstyleno k.bookstyleno;验证改造有没有改坏我一般造三组数据跑一遍一本从没借过的书、一本借了又还的书、一本借了超期没还的书。三组分别验证「能不能借」「能不能还」「罚款算得对不对」只要这三条链路走得通其余的都是边角。从那以后我每次拿到这种从 Word 里粘出来的 SQL都是先整段丢进查询窗口看红色波浪线再一条条拆开单独跑跑通了才敢往应用里接。这套脚本的问题不在难度在于它被复制粘贴过太多次很多约束在途中就丢了。按上面的顺序改一遍它作为课程设计完全够用作为毕设的数据库底座也能撑住。希望帮到你。本文还有配套的精品资源点击获取
简介《SQL数据库图书管理系统(完整代码).doc》是一份面向高校数据库课程设计、毕业设计入门及SQL初学者参考的完整文档型案例围绕图书馆借阅管理场景解决读者信息、图书库存、借还记录与超期罚款等数据组织混乱的问题。文档将系统分析、数据库存储设计、E-R图、数据字典、关系模式与关系实例串联起来并给出借书、还书、罚款等模块的查询描述、关系代数表达及SQL实现与结果便于对照课程要求理解从概念模型到关系模型的落地过程。压缩包内仅含1个doc文件大小约709KB单文件即可覆盖设计报告主体目录按设计目标、数据需求、方案图表和SQL实现组织查阅路径较清晰。目前已有2318人学习适合需要课程设计模板、数据库建模范例或SQL查询练习素材的读者参考。1. 从一份 Word 里粘出来的 SQL 说起这套图书管理系统到底能跑出什么上周有个同学发来一个压缩包里面就一份 .doc标题写着「SQL 数据库图书管理系统(完整代码)」。他原本的想法很简单要么直接交课程设计要么跑起来给自己毕设打个底。结果打开文档发现前半截是 E-R 图和数据字典后半截才是建库建表的脚本中间还夹着几张截图根本不知道从哪下手。我翻完之后的结论是这套东西的业务闭环是完整的——读者、图书、借阅、还书、罚款五块业务都有对应的表和 SQL但它毕竟是从 Word 里复制出来的直接粘进查询窗口会连着报几个错得先修三处才能真正跑通。它适合两类人一是要交数据库课程设计、手上缺一套能自圆其说的表结构和查询集合的二是刚学完 SELECT 和 JOIN想找个带真实业务约束的小库练手的。下面按我自己复现的顺序拆。2. 结构拆解六张表撑起的借还闭环和它的 E-R 关系拿到一个数据库项目我习惯先不看脚本先看它有几张表、表之间怎么连。这套系统的全部逻辑就压在六张表上三张存主数据三张存流水。搞清楚这个划分后面所有的 SQL 都是在它们之间搬数据。2.1 三张主数据表加三张流水表的分工先把六张表摆出来看表名定位主键关键字段book_style书籍类别字典bookstylenobookstyleno、bookstylesystem_readers读者主数据readeridreaderid、readername、readersex、readertype、regdatesystem_books图书主数据bookidbookid、bookname、bookstyleno、bookauthor、bookpub、bookpubdate、bookindate、isborrowedborrow_record在借流水bookid原版写法bookid、readerid、borrowdatereturn_record归还历史bookidbookid、readerid、returndatereader_fee罚款单bookidreaderid、readername、bookid、bookname、bookfee、borrowdate这个划分本身是合格的主数据表负责「有什么」流水表负责「发生了什么」查询的时候主数据提供名字流水表提供时间线。真正值得说的是每张表在做字段取舍时做的那些妥协。book_style用varchar(30)的类别编号当主键而不是自增 ID。好处很直接——灌测试数据的时候insert into book_style values(1,科幻小说)人工可读写 SQL 的时候也不用先去查一遍 ID 是多少。代价是编号一旦定下来就不太好改改一次要连带更新system_books里所有引用它的行而原脚本没有写ON UPDATE CASCADE所以实际改起来会直接报外键冲突。system_books里最容易被忽略的是isborrowed这个字段。它是个冗余状态位理论上「这本书在不在馆」完全可以用borrow_record里有没有这条记录来推导根本不需要单独存。但原作者还是加了原因也不难理解列表页要显示一整屏书如果每本书都去borrow_record做一次NOT EXISTS判断查询会长很多存一个两位的状态位where isborrowed 1一句就筛出来了。这是典型的用一点冗余换查询便利在数据量小的课程设计里完全划算但要求所有借还操作都必须同步更新这个字段——一旦漏更新状态位和流水表就对不上系统会开始说谎。system_readers里的readertype是区分学生、教师、管理员用的。字段在但脚本里没有对应的规则表借书上限、借期天数这些和身份挂钩的参数全都没有落地后面算罚款的时候只能写死。这也是从课程设计过渡到能用的小系统时最先要补的一块。三张流水表里borrow_record和return_record结构几乎一模一样本质是把「在借」的行搬到「已还」去形成一条历史。reader_fee则是明显的反范式它把readername和bookname又存了一遍。这么做不是设计失误而是为了打印罚款单的时候不用 join 三张表——罚款单是要给人看的凭证凭证上的名字应该定格在当时就算读者后来改名了那张单一动不动才对。2.2 实体关系落到主外键上方向、基数和一个埋雷E-R 图那部分文档里画了六张实体分别是类别、读者、图书、借阅记录、归还记录和罚款记录。把它们翻译成关系模式联系其实只有四条类别 1 : N 图书一本书只属于一个类别一个类别下可以有很多本书靠system_books.bookstyleno外键指向book_style实现。读者 1 : N 借阅一本书可以被不同读者在不同时间借一个读者也可以借多本。图书 1 : N 借阅同一本书在时间线上会被借很多次。借阅到归还、借阅到罚款都是一对一的衍生关系本质上罚款和归还都是某次借阅行为的后续结果。前三条在 DDL 里被写成了外键约束方向没问题。问题出在基数被主键意外地限制死了borrow_record把bookid设成了主键。主键意味着唯一而bookid唯一就意味着「一本书在整个系统里只能有一条借阅记录」。这本书被第一个人借走、还回来、第二个人再借第二条插入语句直接主键冲突报错。这就是整份脚本里最要命的一处我在第 5 章会把它单独拎出来讲透。外键的级联动作这里也要说一句。原脚本写的是foreign key (bookstyleno) references book_style(bookstyleno)没带ON DELETE和ON UPDATESQL Server 默认就是NO ACTION——想删一个还有书在用的类别直接拒绝。这在教学场景里是好事能防止误删但如果以后要做「删除类别时把下面的书一起归档」就得显式改成ON DELETE CASCADE或者干脆不用物理外键、改在应用层校验。我一般倾向后者因为外键的报错信息对前端很不友好用户看到的是「违反 FOREIGN KEY 约束」根本不知道自己做错了什么。还有个细节几张表的主键约束都是匿名创建的没写CONSTRAINT名字。这在开发期会带来一个隐形成本——想删掉或者改这个约束的时候得先去sys.key_constraints里查它叫PK__borrow_r__xxxxx这种自动生成的名字。字段多了以后这种名字完全记不住改一次表就得查一次。我在自己的项目里一定会把约束名显式写出来比如CONSTRAINT pk_borrow_record PRIMARY KEY后面维护的时候能省很多事。3. 建库建表实操从 CREATE DATABASE 到六张表灌数据结构清楚了就可以动手了。这一章按顺序走建库、建表、灌数据。全程在 SQL Server Management Studio 的新建查询窗口里执行粘一段跑一段不要一次全选。3.1 建库语句里三个参数必须改原脚本的建库部分是这样的思路连到 master然后CREATE DATABASE指定数据文件和日志文件的初始大小、上限、增长率。逻辑没错但文件路径写的是c:\这一行执行下去必定报错——FILENAME要的是带文件名的完整路径不是目录。我把它改成了下面这样USE master; GO -- 数据库不存在才创建重复执行不会报错 IF DB_ID(library_system) IS NULL BEGIN CREATE DATABASE library_system ON ( NAME library_data, -- 逻辑文件名 FILENAME D:\db\library_data.mdf, -- 物理路径目录必须先存在 SIZE 10MB, MAXSIZE 500MB, FILEGROWTH 5MB ) LOG ON ( NAME library_log, FILENAME D:\db\library_log.ldf, SIZE 5MB, MAXSIZE 100MB, FILEGROWTH 5MB ); END GO USE library_system; GOFILENAME指向的目录D:\db必须提前建好SQL Server 不会帮你创建目录只会在找不到时抛一个 Operating system error 出来这个报错看着像权限问题实际是路径不存在很容易排查跑偏。FILEGROWTH 5MB的意思是每次空间不够就扩 5MB。如果写成FILEGROWTH 10%就是按比例扩数据文件大了以后一次扩出来一大块反而容易卡住。小库我一般固定按 MB 扩行为更可预期。SIZE是初始大小日志文件别给太小事务一多会频繁增长影响写入。GO这个要单独说一句它不是 SQL 语句是 SSMS 的批处理分隔符。USE master之后必须跟一个GO才能保证后面的CREATE DATABASE在一个「已经连到 master」的会话里执行。不少人把GO当普通 SQL 发给驱动执行程序里就会直接报语法错误——应用代码里千万不要带GO。3.2 六张表的建表顺序和修正后的 DDL建表有顺序被引用的表要先建。book_style、system_readers、system_books三张是基础先建borrow_record、return_record、reader_fee引用了它们后建。顺序错了会报「对象名无效」。-- 1. 类别字典 CREATE TABLE dbo.book_style ( bookstyleno varchar(30) NOT NULL CONSTRAINT pk_book_style PRIMARY KEY, bookstyle nvarchar(30) NOT NULL ); -- 2. 读者主数据 CREATE TABLE dbo.system_readers ( readerid varchar(20) NOT NULL CONSTRAINT pk_system_readers PRIMARY KEY, readername nvarchar(20) NOT NULL, -- 中文姓名用 nvarchar别用 varchar readersex nvarchar(2) NOT NULL, readertype nvarchar(10) NULL, regdate datetime NULL ); -- 3. 图书主数据 CREATE TABLE dbo.system_books ( bookid varchar(20) NOT NULL CONSTRAINT pk_system_books PRIMARY KEY, bookname nvarchar(60) NOT NULL, bookstyleno varchar(30) NOT NULL, bookauthor nvarchar(30) NULL, bookpub nvarchar(50) NULL, bookpubdate date NULL, bookindate date NULL, isborrowed char(1) NOT NULL CONSTRAINT df_book_isborrowed DEFAULT 1, CONSTRAINT fk_book_style FOREIGN KEY (bookstyleno) REFERENCES dbo.book_style (bookstyleno) ); -- 4. 在借流水加自增代理键bookid 只做普通索引 CREATE TABLE dbo.borrow_record ( borrowid bigint IDENTITY(1,1) NOT NULL CONSTRAINT pk_borrow_record PRIMARY KEY, bookid varchar(20) NOT NULL, readerid varchar(20) NOT NULL, borrowdate datetime NOT NULL CONSTRAINT df_borrow_date DEFAULT GETDATE(), CONSTRAINT fk_borrow_book FOREIGN KEY (bookid) REFERENCES dbo.system_books (bookid), CONSTRAINT fk_borrow_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers (readerid) ); CREATE INDEX ix_borrow_book ON dbo.borrow_record (bookid); CREATE INDEX ix_borrow_reader ON dbo.borrow_record (readerid); -- 5. 归还历史 CREATE TABLE dbo.return_record ( returnid bigint IDENTITY(1,1) NOT NULL CONSTRAINT pk_return_record PRIMARY KEY, bookid varchar(20) NOT NULL, readerid varchar(20) NOT NULL, returndate datetime NOT NULL, CONSTRAINT fk_return_book FOREIGN KEY (bookid) REFERENCES dbo.system_books (bookid), CONSTRAINT fk_return_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers (readerid) ); -- 6. 罚款单保留姓名书名冗余为了打印凭证 CREATE TABLE dbo.reader_fee ( feeid bigint IDENTITY(1,1) NOT NULL CONSTRAINT pk_reader_fee PRIMARY KEY, readerid varchar(20) NOT NULL, readername nvarchar(20) NOT NULL, bookid varchar(20) NOT NULL, bookname nvarchar(60) NOT NULL, bookfee decimal(8,2) NOT NULL, -- 金额用 decimal别用 varchar borrowdate datetime NOT NULL, CONSTRAINT fk_fee_book FOREIGN KEY (bookid) REFERENCES dbo.system_books (bookid), CONSTRAINT fk_fee_reader FOREIGN KEY (readerid) REFERENCES dbo.system_readers (readerid) );和原脚本比我动了四个地方每一个后面都会出问题第一borrow_record和return_record加了IDENTITY自增主键把bookid降级成普通索引。原版拿bookid当主键一本书只能被借一次这在测试阶段就会撞墙。第二所有中文字段从varchar换成nvarchar。原版的readername varchar(9)在非 Unicode 排序规则下一个汉字占 2 字节9 字节最多存 4 个汉字五个字的名字会被静默截断不报错但数据就错了。第三金额字段bookfee从varchar(30)改成decimal(8,2)。用字符串存金额早晚会遇到1.5 2.3算出1.52.3这种事而且没法做求和统计。第四isborrowed从varchar(2)收成char(1)并给了默认值11 表示在馆0 表示借出。宽度收窄是为了让取值只有一个字符杜绝1 和1在WHERE里匹配不上的情况。提示如果你的排序规则是Chinese_PRC_CI_AS这类非 Unicode 规则varchar存中文虽然能存但跨库、跨版本迁移时极易出现乱码新表一律用nvarchar能省掉很多事。3.3 三批初始化数据类别、图书、读者建完表要灌数据否则后面查什么都是空。顺序还是老规矩先灌book_style再灌system_books和system_readers最后才是流水。-- 第一批书籍类别 INSERT INTO dbo.book_style (bookstyleno, bookstyle) VALUES (1, N科幻小说), (2, N历史小说), (3, N悬疑小说), (4, N都市小说), (5, N武侠小说), (6, N仙侠小说), (7, N言情小说); -- 第二批图书主数据isborrowed 默认 1 表示在馆 INSERT INTO dbo.system_books (bookid, bookname, bookstyleno, bookauthor, bookpub, bookpubdate, bookindate, isborrowed) VALUES (A001, N《星轨》, 1, N沈之远, N某文艺出版社, 2011-03-12, 2013-05-25, 1), (A002, N《归途》, 2, N陆青, N某综合出版社, 2009-07-01, 2013-05-26, 1), (A003, N《孤城笔记》,3, N白露, N某文艺出版社, 2008-11-20, 2013-05-27, 1), (A004, N《楼下咖啡》,4, N周南, N某大学出版社, 2010-04-08, 2013-05-28, 1), (A005, N《剑庐旧话》,5, N顾行舟, N某综合出版社, 2007-09-18, 2013-05-29, 1), (A006, N《云上九歌》,6, N林间客, N某文艺出版社, 2012-01-05, 2013-05-30, 1), (A007, N《初夏信笺》,7, N苏白, N某大学出版社, 2011-06-20, 2013-05-31, 1); -- 第三批读者 INSERT INTO dbo.system_readers (readerid, readername, readersex, readertype, regdate) VALUES (R2013001, N林小满, N女, N学生, 2013-01-18 12:20), (R2013002, N陈知远, N男, N学生, 2013-01-19 13:15), (R2013003, N周青, N女, N学生, 2013-01-20 13:33), (R2013004, N许南, N男, N学生, 2013-01-21 12:01), (T201001, N吴明, N男, N教师, 2013-01-23 18:50), (T201002, N陆行, N男, N教师, 2013-01-24 18:25), (G0001, N郑怀, N女, N管理, 2013-01-01 16:20);日期字面量我统一用了2011-03-12这种 ISO 格式。这一点很关键2011/03/12和03-12-2011在某些DATEFORMAT或者语言设置下会被解析成不同的日期而yyyy-mm-dd是唯一在任何设置下都不会歧义的写法。用date类型存出版日期和登记日期用datetime存借还时间是因为借还业务要精确到时分超期判定才有依据。一次性插入多行VALUES的写法较新版本的 SQL Server 都支持如果你的环境只认单行插入就把它拆成一条条INSERT逻辑一样。灌完之后跑一句SELECT COUNT(*) FROM dbo.system_books确认一下条数比在结果窗口里一行行数要靠谱。4. 业务 SQL 落地借书、还书、罚款和统计的写法表和数据有了可以开始写业务。这一章我不按「增删改查」这种教科书分类直接按真实的操作链路走一次借书发生什么、一次还书发生什么、罚款怎么算出来、报表怎么拉。每个操作我都会写成存储过程把校验和事务包进去因为这几步在原始脚本里是靠人手依次执行几条 SQL 完成的一旦中间断了数据就是错的。4.1 借书一条 INSERT 加一条 UPDATE 必须包在事务里借书这件事在系统里其实是两个动作往borrow_record里插一条流水同时把system_books的isborrowed改成0。这两步必须同生共死否则会出现「流水里有记录但书还显示在馆」或者反过来的幽灵状态。CREATE PROCEDURE dbo.usp_borrow_book bookid varchar(20), readerid varchar(20), borrowdate datetime NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 出任何错误自动回滚整个事务 IF borrowdate IS NULL SET borrowdate GETDATE(); BEGIN TRY BEGIN TRANSACTION; -- 1. 校验图书存在且在馆用 UPDLOCK 锁住这一行防并发重复借 IF NOT EXISTS ( SELECT 1 FROM dbo.system_books WITH (UPDLOCK, ROWLOCK) WHERE bookid bookid AND isborrowed 1 ) RAISERROR(N图书不存在或已借出无法借阅, 16, 1); -- 2. 校验读者存在 IF NOT EXISTS (SELECT 1 FROM dbo.system_readers WHERE readerid readerid) RAISERROR(N读者不存在请先登记, 16, 1); -- 3. 写借阅流水 INSERT INTO dbo.borrow_record (bookid, readerid, borrowdate) VALUES (bookid, readerid, borrowdate); -- 4. 同步状态位 UPDATE dbo.system_books SET isborrowed 0 WHERE bookid bookid AND isborrowed 1; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; -- 原样抛出保留原始错误号和信息 END CATCH END几个参数和写法要解释一下。borrowdate给了默认值NULL调用时不传就取当前时间这样补录历史借阅记录的时候可以指定过去的时间。SET XACT_ABORT ON让任何运行时错误都自动回滚事务比手动判断ERROR稳得多。WITH (UPDLOCK, ROWLOCK)是给这一行加更新锁防止两个管理员几乎同时点借书双双通过「在馆」校验后各插一条流水——这种问题在单人测试时根本发现不了上线第一天就会冒出来。BEGIN CATCH里用THROW原样抛出错误调用方拿到的还是原始的RAISERROR消息。老版本 SQL Server 不支持THROW那就换成RAISERROR(msg, 16, 1)手动拼一下效果差不多。4.2 还书用 OUTPUT 把流水一次性搬走还书的链路比借书长一步从borrow_record删掉这条在借记录、往return_record插一条归还记录、把书的状态改回在馆。最笨的写法是SELECT出来再INSERT然后DELETE三步都在事务里。更省事的是用DELETE ... OUTPUT删的同时把行输出到归还表一条语句同时完成搬迁。CREATE PROCEDURE dbo.usp_return_book bookid varchar(20), readerid varchar(20), returndate datetime NULL AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF returndate IS NULL SET returndate GETDATE(); BEGIN TRY BEGIN TRANSACTION; -- 先把在借记录输出到归还表同时删除 DELETE FROM dbo.borrow_record OUTPUT deleted.bookid, deleted.readerid, returndate INTO dbo.return_record (bookid, readerid, returndate) WHERE bookid bookid AND readerid readerid; -- 一条都没删掉说明这本书压根没被这个读者借着 IF ROWCOUNT 0 RAISERROR(N未找到对应的在借记录请核对图书编号和读者编号, 16, 1); -- 恢复在馆状态 UPDATE dbo.system_books SET isborrowed 1 WHERE bookid bookid; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH ENDOUTPUT ... INTO的顺序要注意deleted.前缀在DELETE里代表被删掉的那行inserted.在DELETE里不可用。输出列的顺序必须和INTO目标表的列顺序完全对齐不能靠列名自动匹配这是这套语法最容易翻车的地方。另外ROWCOUNT必须紧跟在DELETE后面取值中间插一句别的它就变了。4.3 罚款超期天数怎么算才不扯皮罚款这块原脚本只留了一张表和一个bookfee字段规则没写。真实场景里得约定三个参数借期多长、超期每天罚多少、有没有封顶。我一般把借期设成 30 天、日罚金 0.20 元规则写在一个查询里先跑出来看确认没问题再往reader_fee里插。DECLARE limit_days int 30; -- 借期上限 DECLARE fee_per_day decimal(8,2) 0.20; -- 每天罚金 DECLARE max_fee decimal(8,2) 20.00; -- 单本封顶 SELECT b.readerid, r.readername, b.bookid, k.bookname, b.borrowdate, CAST(GETDATE() AS date) AS 今天, DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) AS 已借天数, CASE WHEN DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) limit_days THEN CASE WHEN (DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) - limit_days) * fee_per_day max_fee THEN max_fee ELSE (DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) - limit_days) * fee_per_day END ELSE 0 END AS 应罚金额 FROM dbo.borrow_record b JOIN dbo.system_readers r ON r.readerid b.readerid JOIN dbo.system_books k ON k.bookid b.bookid WHERE DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) limit_days;DATEDIFF(DAY, ...)是按「日期的天」算的跨过零点就算一天不管中间隔了几小时。所以两边都CAST(... AS date)把时分秒切掉算出来的天数是整数不会因为借书时间是 23:50、还书是 00:10 就产生「借了 0 天」这种怪结果。这个坑我在好几个项目里都见过——有人直接拿两个datetime相减得出个小数天数再四舍五入最后罚款金额每次都对不上。封顶那一层CASE是可选的加了以后好和读者解释「最多罚 20」。确认金额之后把整个SELECT改成INSERT INTO reader_fee (...) SELECT ...就落库了因为reader_fee里冗余了readername和bookname插入的时候顺手从JOIN结果里取一条语句搞定。4.4 统计查询三个最常被要的报表系统交付之后被问得最多的永远不是增删改是几张报表。这里放三个我最常写的-- 报表一每个类别的借阅次数排名 SELECT s.bookstyle AS 类别, COUNT(*) AS 借阅次数 FROM dbo.borrow_record b JOIN dbo.system_books k ON k.bookid b.bookid JOIN dbo.book_style s ON s.bookstyleno k.bookstyleno GROUP BY s.bookstyle ORDER BY COUNT(*) DESC; -- 报表二借阅量最高的前五位读者 SELECT TOP 5 r.readername AS 读者, COUNT(*) AS 借阅册次 FROM dbo.borrow_record b JOIN dbo.system_readers r ON r.readerid b.readerid GROUP BY r.readername ORDER BY COUNT(*) DESC; -- 报表三从未被借出过的图书用 NOT EXISTS不要用 NOT IN SELECT k.bookid, k.bookname FROM dbo.system_books k WHERE NOT EXISTS ( SELECT 1 FROM dbo.borrow_record b WHERE b.bookid k.bookid );报表二的TOP 5要加WITH TIES吗不用借阅册次相同的读者很少挤一挤无所谓。报表三特意用了NOT EXISTS而不是NOT IN——一旦子查询里出现NULLNOT IN会整条返回空集这个坑太经典了养成习惯直接用NOT EXISTS能省掉一次排查。5. 踩坑排查这份完整代码里藏着的五个坑下面五条是我从这份脚本里实际撞出来的每条都按「现象 → 原因 → 解决」写你照着自己跑一遍大概率会遇上其中三四个。5.1 图书建了第一条借阅记录后第二个人就借不了现象同一个bookid第一次借书成功还回去以后第二次借直接报「违反 PRIMARY KEY 约束不能在对象 dbo.borrow_record 中插入重复键」。原因borrow_record把bookid设成了主键。主键的唯一性约束在整张表上生效意味着这本物理书在系统生命周期里只能被借一次。这明显不是作者的意图只是写 DDL 时顺手把「业务上唯一的那本书」和「表里唯一的行」搞混了。解决加自增代理键borrowidbookid降级成普通列并建非唯一索引。第 3 章的 DDL 已经改好了。如果数据已经灌进去了改成ALTER TABLE dbo.borrow_record DROP CONSTRAINT pk_borrow_record; ALTER TABLE dbo.borrow_record ADD borrowid bigint IDENTITY(1,1) NOT NULL; ALTER TABLE dbo.borrow_record ADD CONSTRAINT pk_borrow_record PRIMARY KEY (borrowid); CREATE INDEX ix_borrow_book ON dbo.borrow_record (bookid);5.2 读者初始数据插到第二条就报主键冲突现象执行读者初始化的INSERT第一条成功第二条开始全部报重复键。原因原脚本里所有读者的readerid都写成了同一个值Q。偏偏readerid是主键。这是典型的复制粘贴没改干净八个读者共用了一个编号。解决给每个读者一个唯一编号规则自己定比如学生用R加入学年份加序号、教师用T开头、管理员用G开头。第 3 章的示例数据就是这么编的。写完之后检查一遍SELECT readerid, COUNT(*) FROM system_readers GROUP BY readerid HAVING COUNT(*) 1返回空才算干净。5.3 更新图书状态时明明只该改一行结果整张表都变了现象执行UPDATE system_books SET isborrowed 0 WHERE bookid 901 AND isborrowed 1影响行数是 0书的状态没变把1去掉引号后又变成影响了几百行。原因isborrowed是varchar类型存的是字符1。写isborrowed 1时 SQL Server 会做隐式转换把1转成数字 1 来比较这一列上原本就有的索引直接失效变成全表扫描如果数据里还混杂了1 带空格、01这类值比较结果会变得完全不可预期。原始的isborrowed varchar(2)宽度给到 2就是这种混乱的温床。解决宽度收成char(1)加DEFAULT 1查询时永远带引号写isborrowed 1。再狠一点可以加个CHECK (isborrowed IN (0,1))约束把脏值堵在入口。5.4 建库语句执行报操作系统错误看着像权限问题现象CREATE DATABASE报 Operating system error 3 (The system cannot find the path specified) 或者类似的路径错误。原因FILENAME写成了c:\。这个参数要求的是带扩展名的完整物理路径不是目录。另外SIZE、MAXSIZE不给单位时默认按 MB 算但FILEGROWTH如果想用百分比就必须写%混着写容易踩空。解决FILENAME D:\db\library_data.mdf先手动把D:\db建好别指望数据库引擎帮你建目录。日志文件也用LOG ON单独指定不要和数据文件放同一个物理磁盘如果只有一块盘那就无所谓了。5.5 中文姓名存进去变成了问号或者被截断现象插入林小满正常插入五个字的名字时后半截没了或者在某些环境里显示成???。原因字段类型是varchar(9)。非 Unicode 的varchar在中文排序规则下一个汉字占 2 字节9 字节最多存 4 个汉字超出部分静默截断连个警告都没有。跨实例迁移或者换排序规则以后还会直接显示成问号。解决所有存中文的字段一律用nvarchar长度按字符数给比如nvarchar(20)就是 20 个汉字。插入时字符串前面加N写成N林小满告诉 SQL Server 这是 Unicode 字面量不加的话仍然可能按本地代码页转换出问题。6. 二次开发把课程设计改成能用的借阅系统的改造路径这套脚本跑通之后离「能用」还有段距离。我的改造顺序是这样的按优先级排优先级改造项目的P0补代理键、换 nvarchar、金额改 decimal消除数据错误的根源P0借还操作全部封成存储过程并加事务保证状态位和流水一致P1抽 v_borrow_detail 视图应用层查询不用每次写三表 JOINP1加借阅上限、借期天数的规则表罚款规则不再写死在 SQL 里P2加操作日志表谁在什么时候改了哪条记录能查视图这一步收益最直接应用层拿书列表的时候直接SELECT * FROM v_borrow_detail WHERE isborrowed 1把三张表的 JOIN 和中文列名都封在数据库里CREATE VIEW dbo.v_borrow_detail AS SELECT b.borrowid, b.borrowdate, r.readerid, r.readername, r.readertype, k.bookid, k.bookname, s.bookstyle, DATEDIFF(DAY, CAST(b.borrowdate AS date), CAST(GETDATE() AS date)) AS 已借天数 FROM dbo.borrow_record b JOIN dbo.system_readers r ON r.readerid b.readerid JOIN dbo.system_books k ON k.bookid b.bookid JOIN dbo.book_style s ON s.bookstyleno k.bookstyleno;验证改造有没有改坏我一般造三组数据跑一遍一本从没借过的书、一本借了又还的书、一本借了超期没还的书。三组分别验证「能不能借」「能不能还」「罚款算得对不对」只要这三条链路走得通其余的都是边角。从那以后我每次拿到这种从 Word 里粘出来的 SQL都是先整段丢进查询窗口看红色波浪线再一条条拆开单独跑跑通了才敢往应用里接。这套脚本的问题不在难度在于它被复制粘贴过太多次很多约束在途中就丢了。按上面的顺序改一遍它作为课程设计完全够用作为毕设的数据库底座也能撑住。希望帮到你。本文还有配套的精品资源点击获取
