数据库大作业超市管理系统：从表设计到事务并发与查询优化实战

简介这份数据库大作业文档面向高校计算机相关专业学生提供一套小型超市管理系统的完整课程设计参考方案适合正在完成数据库原理或程序设计大作业、需要项目实战案例的学习者。资源包内含1个docx文档约249KB内容涵盖项目简介、需求分析、编程环境、数据库基本表与E-R图、数据库框架介绍、源代码段分析及问题解决等模块。文档以C语言和SQL为开发语言基于Visual Studio 2013与MySQL数据库配合Navicat可视化工具详细阐述了顾客、员工、管理员三部分功能的设计与实现并给出员工表、商品表、货架表、进货表、日销售量表等实体结构及一对多关联关系。读者可从中获取完整的需求分析思路、E-R图设计方法、数据库连接与查询的API调用示例以及建表、MFC界面调试等常见问题的排错经验。目前已有1727人学习下载适合作为课程设计参考或数据库综合实践的学习材料。1. 从一份 docx 到能跑起来的超市管理系统数据库大作业到底在考什么很多人拿到「数据库大作业–超市管理系统.docx」这个题目第一反应是去网上找一份现成代码改改交差。我带过几届课程设计见过太多这样的翻车现场表建了七八张外键一个没加库存扣减靠前端算完再写回答辩时老师一句「并发下超卖了算谁的」直接问穿。这个题目的本质不是让你写一个收银界面而是用超市这个业务场景把数据库设计、增删改查、事务与并发控制、查询优化这几件事串起来落地。它适合数据库课程设计阶段的学生也适合想用一个完整小项目把 SQL 手感捡回来的开发者。下面我按「先立住设计、再动手建库、最后压测排错」的顺序把一份能过答辩、也能真跑的系统讲清楚中间会带上数据库课程设计里最常被忽略的参数和坑。2. 需求拆解与表结构设计超市管理系统该建哪几张表2.1 先画业务流再定实体别一上来就写 CREATE TABLE超市管理系统的业务其实就四条主线商品进货入库、商品销售出库、库存盘点、会员与收银。把这四条线画成流程实体自然就出来了。我一般会先在纸上列实体清单再决定哪些是主表、哪些是流水表。核心实体有这些商品product、商品分类category、供应商supplier、员工employee、会员member、进货单purchase_order及其明细purchase_item、销售单sale_order及其明细sale_item、库存流水stock_log。注意这里我把「库存」拆成了两个东西商品表上存一个当前库存冗余字段同时用 stock_log 记录每一次增减。这是超市系统里最关键的一个设计决策后面讲并发时会用到。为什么要有明细表因为一张进货单可以进多种商品这是典型的一对多。很多同学图省事把商品直接塞进订单表的一个字段里用逗号分隔这种设计在数据库课程设计里是硬伤老师一眼就能看出来你不懂第一范式。2.2 表结构落地字段、类型与约束怎么定下面是我常用的建表脚本以 MySQL 8.0 为例。字段类型的选择有讲究金额一律用 DECIMAL 而不是 FLOAT因为浮点数算钱会出现 0.10.2 这种玄学问题库存数量用 INT时间用 DATETIME。-- 商品分类表 CREATE TABLE category ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE COMMENT 分类名唯一 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 商品表 CREATE TABLE product ( id INT PRIMARY KEY AUTO_INCREMENT, barcode VARCHAR(32) NOT NULL UNIQUE COMMENT 条形码业务主键, name VARCHAR(100) NOT NULL, category_id INT NOT NULL, supplier_id INT, price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 售价, cost DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 进价, stock INT NOT NULL DEFAULT 0 COMMENT 当前库存冗余字段, status TINYINT NOT NULL DEFAULT 1 COMMENT 1上架 0下架, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES category(id), INDEX idx_category (category_id), INDEX idx_name (name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有几个参数必须说清楚。ENGINEInnoDB是硬性要求因为只有 InnoDB 支持事务和行级锁MyISAM 在这个项目里直接出局。utf8mb4而不是utf8是因为商品名里可能出现生僻字或 emojiutf8在 MySQL 里其实是三字节的残缺实现。DECIMAL(10,2)表示总共 10 位、小数 2 位最大能存到九千多万对超市单品足够。外键fk_product_category保证了不会出现「分类不存在」的脏数据。但要注意外键在高并发写入时会有额外锁开销如果后面做压测发现插入慢可以考虑在应用层保证一致性、去掉物理外键这是常见的取舍。2.3 订单与流水表把「一次交易」拆成主表加明细销售单和进货单结构类似我用销售单举例。主表存单据头信息明细表存每一行商品。-- 销售单主表 CREATE TABLE sale_order ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL UNIQUE COMMENT 业务单号, member_id INT, employee_id INT NOT NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, pay_type TINYINT NOT NULL DEFAULT 1 COMMENT 1现金 2扫码 3会员卡, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_created (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 销售明细表 CREATE TABLE sale_item ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL COMMENT 成交单价快照, CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES sale_order(id), INDEX idx_order (order_id), INDEX idx_product (product_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;sale_item里的unit_price是成交时的价格快照不能靠关联 product 表实时取因为商品调价后历史订单金额会变这是财务上的大忌。order_no加唯一索引防止重复提交产生两张单。idx_created索引是为了后面按日期做销售统计查询时能走索引不然全表扫描在数据量上来后会很慢。3. 增删改查与事务把进货、销售、退货写成能扛住并发的 SQL3.1 进货入库一条事务里同时改库存和写流水进货的核心动作是插入进货单主表、插入明细、增加商品库存、写一条库存流水。这四步必须在一个事务里任何一步失败都要回滚否则会出现「单子建了但库存没加」这种对不上账的情况。START TRANSACTION; INSERT INTO purchase_order (order_no, supplier_id, employee_id, total_amount) VALUES (PO20240101001, 1, 1, 500.00); SET order_id LAST_INSERT_ID(); INSERT INTO purchase_item (order_id, product_id, quantity, unit_price) VALUES (order_id, 1, 100, 5.00); -- 增加库存 UPDATE product SET stock stock 100 WHERE id 1; -- 写库存流水change_type1 表示入库 INSERT INTO stock_log (product_id, change_type, change_qty, ref_order_id) VALUES (1, 1, 100, order_id); COMMIT;LAST_INSERT_ID()拿到刚插入的主键避免再查一次。UPDATE product SET stock stock 100这种写法是原子的数据库层面保证不会丢更新千万不要写成「先 SELECT 出库存在应用里加 100再 UPDATE 回去」那种写法在并发下必然出错。3.2 销售出库用条件更新防超卖销售比进货多一个约束库存不能卖成负数。最稳的写法是在 UPDATE 的 WHERE 里带上库存判断。START TRANSACTION; -- 扣库存只有库存足够时才更新成功 UPDATE product SET stock stock - 5 WHERE id 1 AND stock 5; -- 检查影响行数如果为 0 说明库存不足 -- 应用层判断 affected_rows 0 则 ROLLBACK INSERT INTO sale_order (order_no, employee_id, total_amount) VALUES (SO20240101001, 1, 25.00); SET sale_id LAST_INSERT_ID(); INSERT INTO sale_item (order_id, product_id, quantity, unit_price) VALUES (sale_id, 1, 5, 5.00); INSERT INTO stock_log (product_id, change_type, change_qty, ref_order_id) VALUES (1, 2, -5, sale_id); COMMIT;关键在WHERE id 1 AND stock 5。这条语句在 InnoDB 行锁下是串行执行的两个并发请求同时扣库存第二个会等第一个提交后再判断库存不够就更新 0 行。应用层拿到affected_rows为 0 就回滚并提示「库存不足」。这就是防超卖的标准做法比在应用层加锁可靠得多。3.3 退货与库存回滚别把退货写成负数的销售退货是很多同学容易写乱的地方。常见错误是直接插一条数量为负的销售明细这样统计销售额时会把退货算成负销售逻辑上勉强能用但报表会很难看。正确做法是单独建退货单或者用change_type3的库存流水区分。START TRANSACTION; -- 退货库存加回 UPDATE product SET stock stock 5 WHERE id 1; -- 写退货流水change_type3 INSERT INTO stock_log (product_id, change_type, change_qty, ref_order_id) VALUES (1, 3, 5, return_id); -- 更新原销售单状态为已退货如果整单退 UPDATE sale_order SET status 2 WHERE id sale_id; COMMIT;change_type用枚举值区分入库、销售、退货、盘点、报损这样库存流水表就是一本完整的账任何时候都能通过SUM(change_qty)对出当前库存和 product.stock 做核对。如果两者对不上说明有代码绕过了流水直接改库存这是排查数据问题的第一入口。3.4 常用查询销售统计与库存预警怎么写才走索引课程设计答辩最爱问的就是「你这个统计查询怎么优化的」。下面两条是高频查询。-- 查询某天的销售额和单数 SELECT COUNT(*) AS order_cnt, SUM(total_amount) AS total FROM sale_order WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00; -- 库存预警低于安全库存的商品 SELECT p.id, p.name, p.stock, c.name AS category FROM product p JOIN category c ON p.category_id c.id WHERE p.stock 10 AND p.status 1 ORDER BY p.stock ASC;第一条能走idx_created索引注意用和而不是BETWEEN或对字段做函数运算因为DATE(created_at) 2024-01-01这种写法会让索引失效这是数据库优化里最经典的坑。第二条的 JOIN 走idx_categorystock 10是范围条件走不了索引但商品表数据量小问题不大如果商品上万可以考虑给 stock 建索引或做定时任务预计算。4. 避坑与排查数据库课程设计里最容易翻车的五件事4.1 中文乱码建库时没指定字符集现象插入「可乐」显示成问号或乱码。原因数据库、表、连接三处字符集不一致常见是建库用了默认的 latin1。解决建库时显式指定CREATE DATABASE supermarket DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;连接串里加characterEncodingutf8并且确认 MySQL 配置文件里character-set-serverutf8mb4。三处都对了才不会乱码。4.2 外键报错 1452插入顺序搞反了现象插入商品时报Cannot add or update a child row。原因商品引用的 category_id 在分类表里不存在或者你先插商品后插分类。解决按依赖顺序插入先 category、supplier再 product。批量导入数据时尤其要注意用SET FOREIGN_KEY_CHECKS0临时关闭外键检查是应急手段但导完必须开回来否则脏数据会悄悄进去。4.3 死锁两个事务更新顺序不一致现象并发下报Deadlock found when trying to get lock。原因事务 A 先锁商品 1 再锁商品 2事务 B 反过来互相等对方释放。解决约定所有事务按 product_id 升序更新把要改的商品先排序再依次 UPDATE。另外事务里不要做网络请求或长时间计算锁持有时间越短越好。数据库死锁是并发编程的必修课出现不可怕可怕的是不知道为什么。4.4 库存对不上有人绕过了流水直接改 stock现象product.stock 和 stock_log 汇总值不一致。原因某段代码直接UPDATE product SET stock 100覆盖没写流水。解决把 stock 字段设为只允许通过增减语句修改代码评审时重点查有没有绝对赋值的写法。定期跑一条核对 SQLSELECT p.id, p.stock, IFNULL(SUM(s.change_qty),0) FROM product p LEFT JOIN stock_log s ON p.id s.product_id GROUP BY p.id HAVING p.stock IFNULL(SUM(s.change_qty),0);能对不上的就是有问题。4.5 查询慢统计报表把库拖垮现象月底跑销售报表时整个系统卡住。原因报表 SQL 全表扫描大表还和业务查询抢锁。解决给统计字段建索引报表走从库或定时预计算到汇总表。如果只是课程设计至少要做到统计查询走索引别在 WHERE 里对时间字段用函数。数据库优化不是玄学先看执行计划 EXPLAINtype 是 ALL 就是全表扫描得改。5. 从能跑到能答辩用 EXPLAIN 和压测数据证明你的设计系统能跑起来只是及格线答辩时老师想看的是你有没有想过「数据量大了会怎样」。我一般会做两件事来兜底。第一件是用 EXPLAIN 验证关键查询。拿库存预警那条 SQL 举例前面加EXPLAIN看输出。如果type是ref或range说明走了索引如果是ALL就得考虑加索引或改写。销售统计那条重点看key是不是idx_createdrows扫描行数是不是接近实际命中行数。这一步花十分钟能挡掉答辩时一半的追问。第二件是造数据压一压。用存储过程批量插入十万条销售明细然后跑统计查询看耗时。-- 造测试数据插入10万条销售明细 DELIMITER $$ CREATE PROCEDURE gen_test_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i 100000 DO INSERT INTO sale_item (order_id, product_id, quantity, unit_price) VALUES (FLOOR(1 RAND() * 1000), FLOOR(1 RAND() * 50), FLOOR(1 RAND() * 5), 9.90); SET i i 1; END WHILE; END$$ DELIMITER ; CALL gen_test_data();RAND()生成随机数模拟真实分布FLOOR(1 RAND() * 1000)保证 order_id 落在已有订单范围内。造完数据再跑统计查询如果从毫秒级掉到秒级就说明索引或查询写法有问题这时候优化才有说服力。压测数据不用多漂亮能说明「我加索引前后差了 20 倍」就够了。最后说个我自己的习惯每次改完表结构或 SQL我都会把建表脚本和关键查询单独存一份.sql文件用版本号命名。数据库课程设计最怕的就是改着改着把能跑的版本覆盖了回头想找回滚都找不到。留一份后悔药比什么都强。希望帮到你。本文还有配套的精品资源点击获取