简介这份文档是辽宁工业大学软件学院SQL Server数据库技术课程设计报告题目为超市进销存管理系统面向软件工程专业学生及数据库初学者帮助读者理解从需求分析到程序实现的完整数据库系统设计流程。资源包内含1个doc文件约420KB内容涵盖需求分析、数据流图、数据字典、E-R图、关系模型、逻辑与物理设计、数据库实现及概要设计等章节并配有C#连接SQL Server 2005的简单调用示例。读者可借此掌握关系数据库设计方法、SQL Server 2005技术应用以及C/S模式结构获得一份规范的课程设计报告模板与可参考的超市进销存系统设计思路。目前已有418人学习适合需要完成数据库课程设计或学习管理信息系统开发的学生参考借鉴。1. 超市进销存系统为什么总在库存对不上时才发现表结构有问题做过超市零售系统的人大多有过这种经历白天收银正常晚上盘点时发现某款矿泉水的系统库存比货架实际少了三十多瓶查来查去不是收银漏扫而是进货单和销售单在两张表里各记各的中间没有一张表把「批次」这件事说清楚。SQL-SERVER 数据库技术配上超市进销存管理系统要解决的核心就是这件事——用关系型数据库把商品、供应商、进货、销售、库存这几条线用主外键和事务串起来让每一次入库出库都能追溯到具体单据而不是靠事后对账去猜。这套东西适合谁一是数据库课程设计阶段的学生需要一份能跑通、能演示、能讲清楚范式的完整方案二是中小超市或便利店的技术负责人想用一台普通服务器把进销存管起来不想上昂贵的 ERP三是刚接触 SQL-SERVER 的开发者想找一个业务闭环完整、表关系不复杂的练手项目。它不追求高并发秒杀追求的是数据一致性和可追溯性这两点恰恰是进销存系统的命根子。2. 从业务单据倒推表结构七张核心表怎么定主键和外键2.1 先画单据流再落表别一上来就建表很多人做课程设计或小系统习惯打开 SSMS 就开始建表建到一半发现销售表里不知道该不该存商品名称于是又加冗余字段最后数据一改就乱。正确的顺序是先把业务单据流画出来供应商供货产生进货单进货单审核后入库产生库存批次收银台卖出产生销售单销售单扣减对应批次库存退货则反向操作。这条流里每一个「单」就是一张主表每一个「动作」就是一张明细表或流水表。按这个思路超市进销存最少需要七张核心表商品分类表、商品信息表、供应商表、进货单主表、进货单明细表、销售单主表、销售单明细表。库存不单独建一张「当前库存表」去频繁更新而是用进货明细和销售明细的汇总来算或者建一张库存批次表记录每次入库的剩余量。前者查询稍慢但绝对准后者查询快但需要事务保证扣减不出错。中小超市数据量不大我一般推荐批次表方案因为盘点时能直接看到每一批货进了多少、剩多少、卖了多少。2.2 主键用自增还是业务编号这里有个坑商品表的主键用自增整数还是商品条码条码看起来天然唯一但超市里存在散称商品没有条码、条码重复贴错、供应商换包装改条码的情况。所以商品表主键用自增 ID条码单独建唯一索引允许为空但非空时必须唯一。进货单和销售单的主键同样用自增单号用单独字段存业务编号格式类似 JH20250101001方便人看但不做主键。外键关系这样定进货单明细表里存进货单 ID 和商品 ID销售单明细表里存销售单 ID 和商品 ID库存批次表里存商品 ID 和对应的进货单明细 ID。这样从任何一条销售记录都能反查到它扣的是哪一批进货从任何一批进货都能查到它卖给了哪些销售单。下面这段建表语句是核心几张表的骨架可以直接在 SSMS 里跑。-- 商品分类表 CREATE TABLE Category ( CategoryID INT IDENTITY(1,1) PRIMARY KEY, CategoryName NVARCHAR(50) NOT NULL, ParentID INT NULL -- 支持二级分类NULL 表示顶级 ); -- 商品信息表 CREATE TABLE Product ( ProductID INT IDENTITY(1,1) PRIMARY KEY, Barcode VARCHAR(30) NULL, ProductName NVARCHAR(100) NOT NULL, CategoryID INT NOT NULL, Unit NVARCHAR(10) NOT NULL DEFAULT 瓶, PurchasePrice DECIMAL(10,2) NOT NULL DEFAULT 0, SalePrice DECIMAL(10,2) NOT NULL DEFAULT 0, IsActive BIT NOT NULL DEFAULT 1, CONSTRAINT UQ_Product_Barcode UNIQUE (Barcode), CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID) ); -- 供应商表 CREATE TABLE Supplier ( SupplierID INT IDENTITY(1,1) PRIMARY KEY, SupplierName NVARCHAR(100) NOT NULL, Contact NVARCHAR(50) NULL, Phone VARCHAR(20) NULL, Address NVARCHAR(200) NULL ); -- 进货单主表 CREATE TABLE PurchaseOrder ( PurchaseID INT IDENTITY(1,1) PRIMARY KEY, PurchaseNo VARCHAR(30) NOT NULL UNIQUE, SupplierID INT NOT NULL, PurchaseDate DATETIME NOT NULL DEFAULT GETDATE(), TotalAmount DECIMAL(12,2) NOT NULL DEFAULT 0, Status TINYINT NOT NULL DEFAULT 0, -- 0待审核 1已入库 2已作废 CONSTRAINT FK_Purchase_Supplier FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID) ); -- 进货单明细表 CREATE TABLE PurchaseDetail ( DetailID INT IDENTITY(1,1) PRIMARY KEY, PurchaseID INT NOT NULL, ProductID INT NOT NULL, Quantity INT NOT NULL, UnitPrice DECIMAL(10,2) NOT NULL, BatchNo VARCHAR(30) NULL, CONSTRAINT FK_PD_Purchase FOREIGN KEY (PurchaseID) REFERENCES PurchaseOrder(PurchaseID), CONSTRAINT FK_PD_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID) );这段代码里几个参数值得说清楚。IDENTITY(1,1)是自增种子和步长SQL-SERVER 里建表时最常用。NVARCHAR而不是VARCHAR因为商品名和供应商名可能含中文用VARCHAR在某些排序规则下会丢字符。DECIMAL(10,2)存金额不要用FLOAT浮点数算钱会出现 0.30000000000000004 这种结果对账时能把你逼疯。Status用TINYINT而不是字符串省空间且索引快。外键约束FK_PD_Purchase保证明细不会挂到不存在的进货单上这是数据一致性的第一道闸。2.3 库存批次表是进销存的枢纽设计时多留一个字段库存批次表连接进货和销售设计时除了商品 ID、进货明细 ID、入库数量、剩余数量我建议多留一个「入库时间」和「成本单价」。入库时间用于先进先出扣减成本单价用于计算销售毛利。没有这两个字段后面做毛利报表时只能取商品表里的当前进价而进价是变动的算出来的毛利不准。CREATE TABLE StockBatch ( BatchID INT IDENTITY(1,1) PRIMARY KEY, ProductID INT NOT NULL, PurchaseDetailID INT NOT NULL, InQuantity INT NOT NULL, RemainQuantity INT NOT NULL, CostPrice DECIMAL(10,2) NOT NULL, InDate DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT FK_SB_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID), CONSTRAINT FK_SB_PD FOREIGN KEY (PurchaseDetailID) REFERENCES PurchaseDetail(DetailID) ); -- 销售单主表和明细表结构类似进货单此处省略主表给出明细表 CREATE TABLE SaleDetail ( SaleDetailID INT IDENTITY(1,1) PRIMARY KEY, SaleID INT NOT NULL, ProductID INT NOT NULL, Quantity INT NOT NULL, SalePrice DECIMAL(10,2) NOT NULL, BatchID INT NULL, -- 记录扣的哪一批便于追溯 CONSTRAINT FK_SD_Sale FOREIGN KEY (SaleID) REFERENCES SaleOrder(SaleID), CONSTRAINT FK_SD_Product FOREIGN KEY (ProductID) REFERENCES Product(ProductID), CONSTRAINT FK_SD_Batch FOREIGN KEY (BatchID) REFERENCES StockBatch(BatchID) );RemainQuantity这个字段是并发扣减的关键。收银台同时卖同一款商品时两个事务如果都先读后写会出现超卖。解决办法是在扣减时用UPDATE StockBatch SET RemainQuantity RemainQuantity - Qty WHERE BatchID BatchID AND RemainQuantity Qty然后检查ROWCOUNT是否为 1为 0 说明库存不足回滚事务。这个写法比先SELECT再UPDATE可靠得多是血泪经验换来的。3. 进货入库和销售出库的存储过程怎么写才不出错3.1 入库存储过程审核和写批次必须在一个事务里进货单审核这个动作业务上要同时做三件事把进货单状态改成已入库、往库存批次表插入记录、更新商品表的最近进价。这三件事必须在一个事务里完成任何一步失败都回滚否则会出现单子审核了但库存没加或者库存加了但单子还是待审核状态。下面这个存储过程是核心逻辑。CREATE PROCEDURE sp_ApprovePurchase PurchaseID INT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; -- 检查单据状态防止重复审核 IF NOT EXISTS (SELECT 1 FROM PurchaseOrder WHERE PurchaseID PurchaseID AND Status 0) BEGIN RAISERROR(单据不存在或已审核, 16, 1); ROLLBACK TRANSACTION; RETURN; END -- 逐条明细写入库存批次 INSERT INTO StockBatch (ProductID, PurchaseDetailID, InQuantity, RemainQuantity, CostPrice, InDate) SELECT ProductID, DetailID, Quantity, Quantity, UnitPrice, GETDATE() FROM PurchaseDetail WHERE PurchaseID PurchaseID; -- 更新商品最近进价 UPDATE P SET P.PurchasePrice D.UnitPrice FROM Product P INNER JOIN PurchaseDetail D ON P.ProductID D.ProductID WHERE D.PurchaseID PurchaseID; -- 更新单据状态 UPDATE PurchaseOrder SET Status 1 WHERE PurchaseID PurchaseID; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH ENDSET NOCOUNT ON减少网络往返存储过程里几乎必加。BEGIN TRY...BEGIN CATCH是 SQL-SERVER 里处理事务错误的标配THROW把原始错误抛给调用方方便上层定位。注意INSERT INTO StockBatch用的是SELECT批量插入比游标逐条插快很多而且天然保持原子性。RAISERROR那一段是防重复审核的后悔药没有它操作员手抖点两次审核库存就翻倍了。3.2 出库存储过程先进先出扣减和事务隔离级别销售出库比入库复杂因为要按先进先出原则扣减批次。假设某商品有三批库存入库时间分别是 1 月 1 日、1 月 5 日、1 月 10 日现在卖 5 个应该先扣 1 月 1 日那批不够再扣 1 月 5 日。这个逻辑用游标或者WHILE循环实现但必须放在事务里并且把隔离级别设为REPEATABLE READ或更高防止扣减过程中别的会话插进来。CREATE PROCEDURE sp_SaleOut ProductID INT, Quantity INT, SaleDetailID INT AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRY BEGIN TRANSACTION; DECLARE NeedQty INT Quantity; DECLARE BatchID INT, Remain INT, Cost DECIMAL(10,2); -- 按入库时间升序取批次 DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT BatchID, RemainQuantity, CostPrice FROM StockBatch WHERE ProductID ProductID AND RemainQuantity 0 ORDER BY InDate ASC; OPEN cur; FETCH NEXT FROM cur INTO BatchID, Remain, Cost; WHILE FETCH_STATUS 0 AND NeedQty 0 BEGIN IF Remain NeedQty BEGIN UPDATE StockBatch SET RemainQuantity RemainQuantity - NeedQty WHERE BatchID BatchID AND RemainQuantity NeedQty; SET NeedQty 0; END ELSE BEGIN UPDATE StockBatch SET RemainQuantity 0 WHERE BatchID BatchID AND RemainQuantity Remain; SET NeedQty NeedQty - Remain; END FETCH NEXT FROM cur INTO BatchID, Remain, Cost; END CLOSE cur; DEALLOCATE cur; IF NeedQty 0 BEGIN RAISERROR(库存不足扣减失败, 16, 1); ROLLBACK TRANSACTION; RETURN; END -- 记录扣减的批次到销售明细 UPDATE SaleDetail SET BatchID BatchID WHERE SaleDetailID SaleDetailID; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH ENDREPEATABLE READ保证在事务内多次读同一批数据结果一致防止幻读导致扣减错乱。游标用LOCAL FAST_FORWARD只进不退性能最好。每次UPDATE都带AND RemainQuantity NeedQty或 Remain条件这是乐观锁思路即使隔离级别不够也能靠条件更新避免扣成负数。最后那个UPDATE SaleDetail SET BatchID只记录了最后一批严格来说应该记录所有扣减批次实际项目里可以再建一张销售批次关联表这里为了简洁只做示意。3.3 库存查询视图把批次汇总成一眼能看懂的报表日常运营最常问的一句话是「这个商品还有多少库存」。直接查StockBatch汇总RemainQuantity就行但还要带上商品名、分类、最近进价所以建一个视图最方便。CREATE VIEW v_StockSummary AS SELECT P.ProductID, P.ProductName, C.CategoryName, P.Unit, ISNULL(SUM(SB.RemainQuantity), 0) AS TotalRemain, P.PurchasePrice, P.SalePrice FROM Product P LEFT JOIN Category C ON P.CategoryID C.CategoryID LEFT JOIN StockBatch SB ON P.ProductID SB.ProductID AND SB.RemainQuantity 0 WHERE P.IsActive 1 GROUP BY P.ProductID, P.ProductName, C.CategoryName, P.Unit, P.PurchasePrice, P.SalePrice;ISNULL处理没有批次的商品显示 0 而不是 NULL。LEFT JOIN保证即使某商品从没进过货也能列出来方便发现「建了商品档案但一直没进货」的情况。WHERE P.IsActive 1过滤掉停用商品避免报表里出现一堆不卖的货。4. 避坑超市进销存数据库最容易翻车的五个地方4.1 现象盘点时库存对不上差几个到几十个不等原因通常有三种。一是销售出库时没有用事务扣了库存但销售单没写成功或者反过来。二是并发扣减时用了先查后改的写法两个收银台同时卖同一商品都读到剩余 10各扣 3最后剩 7 而不是 4。三是退货没有反向加回批次或者加回到了错误的批次。解决方法是所有库存变动必须走存储过程存储过程里必须用事务扣减必须用条件更新WHERE RemainQuantity Qty并检查ROWCOUNT。4.2 现象进货单审核后商品最近进价没更新原因多半是UPDATE Product那一步写在了事务外面或者JOIN条件写错导致更新了 0 行。排查时先看PurchaseDetail里有没有对应记录再看Product.PurchasePrice是否真的变了。解决方法是把更新进价放进审核存储过程的事务里并且用ROWCOUNT打印受影响行数为 0 就抛错回滚。4.3 现象销售明细里的 BatchID 是 NULL追溯不到批次原因是出库存储过程里UPDATE SaleDetail SET BatchID只更新了最后一批如果一次销售跨多个批次前面的批次没记录。解决方法是建一张SaleBatch关联表每扣一个批次插一条记录字段包括SaleDetailID、BatchID、Quantity、CostPrice。这样毛利计算和批次追溯都准确。4.4 现象数据库文件越来越大查询越来越慢原因是没有定期清理作废单据和长期无变动的批次记录或者缺失索引。StockBatch表上ProductID和RemainQuantity应该建复合索引SaleDetail上SaleID和ProductID建索引。作废的进货单和销售单不要物理删除用Status标记但可以定期归档到历史表。索引不是越多越好写多的表索引多了拖慢插入一般三到五个关键索引足够。4.5 现象SSMS 里执行存储过程报「事务计数不匹配」原因是存储过程里开了事务但某条分支没有提交或回滚常见于IF判断后直接RETURN而忘了ROLLBACK。解决方法是每个RETURN前都检查TRANCOUNT大于 0 就回滚。更稳妥的写法是统一用BEGIN TRY...BEGIN CATCH包裹CATCH里无条件回滚TRY里最后提交。5. 用 SQL-SERVER 代理作业做每日库存快照和毛利报表5.1 为什么需要快照而不是每次实时算实时算库存汇总在数据量小的时候没问题但超市经营一年后StockBatch和SaleDetail动辄几十万行每次打开报表都全表扫描体验很差。更好的做法是每天凌晨用 SQL-SERVER 代理作业跑一次快照把当前库存和当日销售毛利存到汇总表里报表直接查汇总表。这样既快又能保留历史月底看趋势时不用重新算。-- 每日库存快照表 CREATE TABLE DailyStockSnapshot ( SnapshotDate DATE NOT NULL, ProductID INT NOT NULL, TotalRemain INT NOT NULL, TotalCost DECIMAL(12,2) NOT NULL, PRIMARY KEY (SnapshotDate, ProductID) ); -- 快照存储过程 CREATE PROCEDURE sp_DailyStockSnapshot AS BEGIN SET NOCOUNT ON; DECLARE Today DATE CAST(GETDATE() AS DATE); MERGE DailyStockSnapshot AS target USING ( SELECT Today AS SnapshotDate, SB.ProductID, SUM(SB.RemainQuantity) AS TotalRemain, SUM(SB.RemainQuantity * SB.CostPrice) AS TotalCost FROM StockBatch SB WHERE SB.RemainQuantity 0 GROUP BY SB.ProductID ) AS source ON target.SnapshotDate source.SnapshotDate AND target.ProductID source.ProductID WHEN MATCHED THEN UPDATE SET TotalRemain source.TotalRemain, TotalCost source.TotalCost WHEN NOT MATCHED THEN INSERT (SnapshotDate, ProductID, TotalRemain, TotalCost) VALUES (source.SnapshotDate, source.ProductID, source.TotalRemain, source.TotalCost); ENDMERGE语句在 SQL-SERVER 里做「存在则更新不存在则插入」很方便但要注意并发场景下MERGE有已知的锁问题代理作业是单线程跑所以安全。Today用CAST(GETDATE() AS DATE)取日期部分避免时间戳干扰主键。快照表主键是日期加商品 ID同一天重复跑会更新而不是重复插入。5.2 代理作业怎么配以及跑失败了看哪里在 SSMS 里展开「SQL Server 代理」右键「作业」新建步骤里填EXEC sp_DailyStockSnapshot计划设每天凌晨 2 点执行。配好后手动右键作业点「作业开始步骤」测试一次看历史记录里有没有报错。常见失败原因是代理服务没启动或者执行账户没有存储过程所在数据库的权限。跑失败先看作业历史里的错误消息再到 Windows 事件查看器里看 SQL-SERVER 相关日志多数是权限或磁盘空间问题。5.3 毛利报表的 SQL 怎么写才不把成本算错毛利等于销售收入减去销售成本收入从SaleDetail的SalePrice * Quantity来成本从扣减批次的CostPrice * Quantity来。如果前面建了SaleBatch关联表这里直接 JOIN 就行。没有关联表的话只能用商品当前进价近似误差在进价波动大时很明显。下面这个查询假设有SaleBatch表。SELECT CAST(SO.SaleDate AS DATE) AS SaleDate, P.ProductName, SUM(SD.Quantity) AS SaleQty, SUM(SD.Quantity * SD.SalePrice) AS Revenue, SUM(SB.Quantity * SB.CostPrice) AS Cost, SUM(SD.Quantity * SD.SalePrice) - SUM(SB.Quantity * SB.CostPrice) AS GrossProfit FROM SaleOrder SO INNER JOIN SaleDetail SD ON SO.SaleID SD.SaleID INNER JOIN SaleBatch SB ON SD.SaleDetailID SB.SaleDetailID INNER JOIN Product P ON SD.ProductID P.ProductID WHERE SO.SaleDate StartDate AND SO.SaleDate DATEADD(DAY, 1, EndDate) GROUP BY CAST(SO.SaleDate AS DATE), P.ProductName ORDER BY SaleDate, GrossProfit DESC;DATEADD(DAY, 1, EndDate)处理结束日期避免漏掉当天最后几秒的单子。GROUP BY里用CAST把时间截断到天否则同一天不同时刻会分成多行。这个查询在SaleBatch有索引的情况下几十万行数据也能秒出。5.4 一个我踩过的坑快照表主键顺序影响查询速度DailyStockSnapshot主键是(SnapshotDate, ProductID)查某一天全部商品很快查某个商品的历史趋势就慢因为ProductID在第二列。后来我把主键改成(ProductID, SnapshotDate)趋势查询快了但按天查全部商品又慢了。最终方案是保留(SnapshotDate, ProductID)主键额外建一个(ProductID, SnapshotDate)的非聚集索引。索引不是免费的插入时会慢一点但快照表每天只插几千行完全值得。这个取舍我纠结了一下午最后用执行计划对比才定下来希望帮到你。本文还有配套的精品资源点击获取