药品管理系统数据库设计全流程：从ER模型到SQL优化与答辩指南

简介：这份数据库课程设计报告以药品管理系统为实战项目，完整记录了从需求分析、概念设计、逻辑设计到物理设计、实施与测试的数据库开发全过程，适合计算机相关专业学生作为课程设计参考，也可供初学者学习SQL Server关系数据库的设计思路。报告包含业务流程图、数据字典、分E-R图与全局E-R图、关系模式转换及表结构定义等核心内容，对药品信息的存储、查询、更新和删除等操作给出了较为完整的方案设计。资源共1个doc文档，压缩包大小约1.07MB，结构清晰、篇幅完整，已有1037人学习浏览。通过阅读该报告，读者可以了解数据库课程设计报告的撰写规范，掌握以SQL Server为平台进行系统需求分析、建模与实施的完整流程，为独立完成类似管理系统设计提供参考与范例。

1. 数据库课程设计报告-药品管理系统：一份能答辩、能改、能演示的完整方案

每到期末，数据库课程设计总能把一批人卡在同一个地方：系统功能想得太简单，报告写得太薄，一到老师提问“为什么这三张表要拆开”就露馅。药品管理系统作为最经典的课程设计题目，优点是业务闭环清晰——进货、库存、销售、过期预警、供应商管理全都能串起来，足够把数据库设计的核心知识点都覆盖一遍，但又不会像电商系统那样复杂到收不住。这篇笔记直接按“能交差、能演示、能扛住答辩提问”的标准，从 ER 模型、表结构、查询优化到报告排版，给你一条可以照着走完的路。适合正在做课程设计、或者想拿一份像样的数据库设计练手的人，不需要你有实习经验，但要会用 MySQL 和任意一种开发语言的基础语法。

2. 业务拆解与 ER 设计：药品管理系统到底要管哪几张表，为什么是这几张

2.1 先从业务流程倒推需求：没有业务分析的表结构都是空中楼阁

常见做法是先打开 MySQL 直接建表，建到哪算哪。我一般会反过来，先在纸上把药品的流转路径画出来：采购员向供应商下订单进货，药品入库后进入库存，药店前台销售时扣减库存，近效期的药品需要预警，过期的药品报损清点。这一个流程走下来，核心业务对象就全暴露了——药品、供应商、采购订单、库存记录、销售记录。

课程设计最容易踩的第一个坑就是表不够拆。比如有人把药品信息和库存数量放在同一张表里，觉得“反正每次查药品都要带出数量”，结果到了做“按月统计每种药品的销售毛利”时，发现销售记录里没有单价快照，历史数据全废了。记住一个原则：一张表只描述一种业务实体，一个业务动作单独建表记录流水。按这个原则拆出来的表数量不会少于六张，但每一张都讲得出存在理由，老师问起来你也不会慌。

2.2 ER 图怎么画才叫“完整”：从实体、属性到联系的标注细节

ER 图是报告里最容易被挑刺的部分，因为老师一眼就能看出你是真分析了还是拿工具自动生成的。画 ER 图时至少要有四个实体：药品（Drug）、供应商（Supplier）、采购订单（PurchaseOrder）、销售记录（SaleRecord），如果要拓展深度，可以加库存报损表（StockLoss）和用户表（User）。

实体之间的关系要明确标注基数。药品与供应商是多对多——一种药品可能从多个供应商进货，一个供应商供应多种药品，这个多对多关系必须通过中间表实现，通常在采购订单明细表（PurchaseItem）里去体现，而不是直接给药品表加一个供应商字段。药品和销售记录是一对多——一种药品可以被销售多次。采购订单和供应商是多对一。库存表与药品是一对一。画 ER 图的时候，联系上要标 1 或 n，属性用椭圆框挂到实体下，主键加下划线。报告里截 ER 图时，建议把 MySQL Workbench 的逆向工程图稍微整理一下再放进去，不要留满屏连线和重叠标签，那反而显得不认真。

2.3 表结构落地：一张字段设计表，把每个字段的类型、约束和存在理由写清楚

ER 图是给老师看逻辑的，建表语句是给系统跑起来的，两者必须完全对得上。以药品表为例，设计成下面这张表：

这里有三点需要注意。第一，价格用 DECIMAL 不用 FLOAT，药品价格涉及金额计算，二进制浮点数会产生精度偏差，查“感冒灵多少钱”可能算出 18.899999。第二，expi_date 的有效期不要和批次混在一起，一次进货的过期时间应该挂在采购批次明细上，药品表里只留当前有效批次的信息。第三，库存数量不要做成负数，靠销售事务和检查约束去兜底。

供应商表相对简单：supplier_id 主键、supplier_name、contact_person、phone、address，其中 phone 要加唯一约束，否则同一个供应商录两遍，采购单统计就乱了。采购单主表和明细表必须拆开——主表存单号和供应商，明细表存每一条药品的进货数量与单价，这样才能支持“一单进五种药”的情况。

2.4 外键与约束：什么时候必须建外键，什么时候宁可不建

课程设计报告里的外键策略是个加分点。你在表上加上真正的外键约束，老师会认为你懂参照完整性。但有一个课堂里不讲的实际问题：一旦加了外键，测试数据的插入顺序就卡死了——必须先插供应商再插药品，再插采购单，最后插销售单，对课程设计的多轮改动来说非常烦。

我的做法是分两段：开发调试阶段不建物理外键，靠应用层逻辑保证引用一致；最终提交报告前补上外键约束，跑一遍插入流程验证完整性。报告里写外键策略时，直接把这两段写进去，说明你在开发和交付阶段的选择依据，比单纯堆外键看起来务实得多。药品表的外键可以有两个方向——关联分类表（drug_category）和关联供应商表，但供应商与药品的多对多关系必须通过采购明细中转，外键只加在明细表上，药品表不加 supplier_id。

3. 建库建表与基础 CRUD：从 SQL 脚本到药品数据增删改查的完整落库流程

3.1 建库命名与编码选择：字符集设置错了，插入中文直接变问号

建库这一步看着简单，踩坑的人却最多。直接用 MySQL 默认配置去建库，插入“阿莫西林胶囊”之后查出来全是乱码，然后开始怀疑代码有问题，折腾半天最后发现是库表字符集没设成 utf8mb4。utf8mb4 和 utf8 的差别在于前者能存 emoji 和生僻字，药品通用名里偶尔会有特殊字符，直接按下面的脚本建，一步到位。

DROP DATABASE IF EXISTS drug_management; CREATE DATABASE drug_management DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE drug_management; CREATE TABLE supplier ( supplier_id INT PRIMARY KEY AUTO_INCREMENT, supplier_name VARCHAR(50) NOT NULL, contact_person VARCHAR(20), phone VARCHAR(20) UNIQUE, address VARCHAR(100) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

字符集是在数据库、表、连接三个层面都要对齐的。MySQL 的配置文件 my.cnf 里把 character-set-server 和 collation-server 也设上，然后用 JDBC 或 Python 连接时在连接串上加 characterEncoding=utf8mb4，三层都对上了，中文才稳定不乱码。InnoDB 是必须的，你这个系统后面要演示事务，MyISAM 不支持事务，选了它后面会卡死在“销售同时扣库存”这个环节。

3.2 药品表与库存表拆不拆：查询性能和三范式之间的取舍

很多课程设计的参考例子里，药品表直接带了 stock_quantity 字段，你要说它错倒也不是，但它把“药品信息”和“当前库存状态”混在了一张表里。库存是一个随时间变化的状态量，药品名称、规格、价格是不变量或低频变化量。混在一起的问题在于：每一次库存变动都要 UPDATE 药品表，报表统计库存流水时没有依据，未来想查“这批过期药是从哪个供应商进的”就查不到了。

我倾向于拆出三张表：drug 表只存静态信息，drug_stock 表按批次存数量，stock_log 表存每一笔出入库记录。但课程设计报告的系统规模不大，拆成三张表会显得表很多、每张表内容很薄。折中方案是：保留 drug 表的 stock_quantity 作为冗余字段用于快速展示，同时建 stock_log 表记录每笔变动，日志表通过事务更新 drug 表的冗余库存。这个方案在报告里可以写一段“反范式设计说明”，解释你是为了减少查询时的多表关联才保留冗余字段，并且用事务保证一致性——这比死磕三范式更能体现你理解数据建模的取舍。

3.3 药品增删改查的 SQL 脚本：插入、修改、删除、模糊查询的完整写法

这个系统不管用哪种界面方案，最终提供的核心服务都是对这六张表的增删改查。下面是几个最关键的 SQL 操作，直接抄进你的系统代码里就能用。

-- 新增药品 INSERT INTO drug ( drug_name, spec, unit, purchase_price, sale_price, expire_date, stock_quantity ) VALUES ( '阿莫西林胶囊', '0.25g*24粒', '盒', 8.50, 15.00, '2026-03-01', 200 ); -- 修改药品价格 UPDATE drug SET sale_price = 14.50, purchase_price = 8.00 WHERE drug_id = 1; -- 删除药品（先检查是否有关联销售记录） DELETE FROM drug WHERE drug_id = 1 AND NOT EXISTS (SELECT 1 FROM sale_item WHERE drug_id = 1); -- 药品名模糊查询 SELECT drug_id, drug_name, spec, stock_quantity, expire_date FROM drug WHERE drug_name LIKE CONCAT('%', '阿莫西林', '%') ORDER BY expire_date ASC;

最后这条查询语句要重点说。它同时用到了 LIKE 模糊匹配和按有效期排序，是演示查询功能时的亮点。删除语句里的 NOT EXISTS 子查询是防止外键级联报错的，很多同学直接在界面点删除，报错 “Cannot delete or update a parent row” 然后不知道怎么处理，其实就是没做关联检查。

3.4 给系统配数据库连接池：没有池化的连接在课程设计演示时必崩

如果课程设计要求你用 Java 写界面，那连接池是躲不开的。演示现场最尴尬的事情是弹窗显示 “Too many connections”——你把连接写成了每次操作都新建一个 Connection，用完没有归还，演示到第三次操作就把 MySQL 默认的 151 个连接耗尽了。用连接池的意义在于复用连接、控制并发上限。

常见做法是用 Druid 或者 HikariCP，核心配置如下。

HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:mysql://localhost:3306/drug_management?useUnicode=true&characterEncoding=utf8mb4&serverTimezone=Asia/Shanghai"); config.setUsername("root"); config.setPassword("123456"); config.setMaximumPoolSize(10); config.setMinimumIdle(2); config.setConnectionTimeout(30000); HikariDataSource dataSource = new HikariDataSource(config);

连接串里的 serverTimezone 是另一个坑。MySQL 8.x 默认时区跟中国本地时间不一致，不设这个参数的话查询出来的时间字段会差八个小时，药品销售记录里的时间就变成凌晨了。最大连接池大小设 10 就够课程设计用了，不要设大，设大了反而会让 MySQL 端连接数紧张。每个查询执行完记得调用 conn.close()，连接池会自动把连接归还而不是真的关闭。

3.5 界面和数据库之间的“最后一公里”：控制台、Swing 还是 Web 页面

课程设计用哪种界面，直接影响报告篇幅和工作量，也影响老师对你的第一印象。三种方案都可行，但我直接说利弊。

控制台程序最安全，代码量最小，报告里放几张运行截图就能说明白，缺点是看起来不够“系统”。Java Swing 或 JavaFX 做桌面端是最常见的课程设计形态，代码量可控，窗口截图放进报告比较像样，但 Swing 的布局代码要花不少时间调。Web 页面（Spring Boot + Thymeleaf 或 Flask + Bootstrap）是加分项，毕竟现在写代码的主流形态就是 Web，但工作量明显大，需要处理前端页面和后端接口两层。我给的建议是：如果你是数据库课程设计，不是软件工程课设，时间又紧，就用 Swing 或 Python 的 Tkinter，把精力放在 SQL 设计和报告上；如果你还想这门课的成果以后写进简历里，就选 Web 方案，把增删改查封装成 REST 接口。

4. 核心业务 SQL 与事务处理：药品销售、库存扣减和过期预警怎么写出让老师点头的效果

4.1 销售开单的事务脚本：一个 UPDATE 和一个 INSERT 如何保证不丢数据

药品销售是整个系统里最体现数据库功底的地方。一次销售动作包含两步：往 sale 表和 sale_item 表插入记录，同时扣减 drug 表的库存数量。这两步要么全成功、要么全失败，必须包在事务里。

START TRANSACTION; INSERT INTO sale (sale_no, sale_date, operator, total_amount) VALUES ('XS20240101001', NOW(), 'admin', 45.00); SET @sale_id = LAST_INSERT_ID(); INSERT INTO sale_item (sale_id, drug_id, quantity, price) VALUES (@sale_id, 1, 3, 15.00); UPDATE drug SET stock_quantity = stock_quantity - 3 WHERE drug_id = 1 AND stock_quantity >= 3; IF ROW_COUNT() = 0 THEN ROLLBACK; ELSE COMMIT; END IF;

这段脚本有两个关键设计。LAST_INSERT_ID() 在事务内取到的是当前连接刚插入的自增值，不会受其他连接干扰，所以取出的是正确的 sale_id。UPDATE 语句里带上 stock_quantity >= 3 这个条件，是经典的“乐观锁扣库存”思路，防止超卖——如果库存不够，影响行数是 0，直接回滚。没有学过并发控制的同学很难想到这一步，写进报告能让老师看出你理解了“数据库并发锁”的核心矛盾。

4.2 过期预警查询：DATE 函数和参数化时间的边界条件

药品管理系统区别于图书管理系统的特色功能就是有效期预警。一个实用的查询是找出未来 30 天内过期的药品，同时把已经过期的也列出来。

SELECT drug_id, drug_name, spec, expire_date, stock_quantity, DATEDIFF(expire_date, CURDATE()) AS days_left FROM drug WHERE DATEDIFF(expire_date, CURDATE()) BETWEEN 0 AND 30 OR expire_date < CURDATE() ORDER BY days_left ASC;

DATEDIFF 计算的是过期日期与今天的间隔天数，BETWEEN 0 AND 30 覆盖未来 30 天即将到期的，expire_date < CURDATE() 覆盖已经过期的。要注意 BETWEEN 的边界是包含的，如果你想排除今天刚好过期的批次，就把 0 改成 1。这个查询是报告里的功能截图亮点，放在系统演示环节能直观展示你对日期函数的掌握。

4.3 多表关联统计：按药品分组汇总销售额，这一条 SQL 能顶半页报告

课程设计的报告里最好有一个“综合查询”章节，展示三表以上关联的 SQL 写法，这直接呼应“数据库设计”这个课程名本身。按药品名称统计销量和销售额就是这样一个例子。

SELECT d.drug_name, SUM(si.quantity) AS total_quantity, SUM(si.quantity * si.price) AS total_sales FROM sale_item si JOIN drug d ON si.drug_id = d.drug_id JOIN sale s ON si.sale_id = s.sale_id WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-12-31' GROUP BY d.drug_id, d.drug_name ORDER BY total_sales DESC LIMIT 10;

这里有个常见错误是 GROUP BY 只写 d.drug_name 不写 d.drug_id。如果药品表里有两个同名但不同规格的药品，它们会被合并成一组。ONLY_FULL_GROUP_BY 模式下 SQL 会直接报错，很多同学不理解为什么自己写的语句报 “this is incompatible with sql_mode=only_full_group_by”，原因就在这——GROUP BY 的列必须包含所有非聚合查询列，最稳妥的办法是把主键也加进 GROUP BY。

4.4 报表输出怎么优化：给销售流水表加索引、避免全表扫

课程设计在演示时数据量小，查询快是理所当然的；但报告里如果能有一小节写索引优化，就是明显的加分项。核心表的索引建议如下：

要注意，LIKE '%阿莫西林%' 这种前置通配符的模糊查询，普通的 B+ 树索引是用不上的。报告里别写“加了索引所有查询都变快”，懂行的老师一看就知道你在背概念。准确的说法是：索引对精确匹配和范围查询有效，对前置模糊查询无效，你在设计时用前缀匹配或考虑全文索引。能写出这个边界，答辩时的提问环节你会很从容。

5. 避坑与常见问题排查：药品管理系统从开发到交报告的 5 个典型翻车现场

5.1 插入药品数据报“Duplicate entry”，都是唯一约束惹的祸

现象：插入一个新药品时，明明主键没有重复，却报 “Duplicate entry '138' for key 'drug.phone'”。原因是你在 supplier 表给 phone 加了 UNIQUE 约束，而测试数据里两条供应商记录的手机号都是空字符串或者同一个占位数字，空字符串在 MySQL 唯一约束里只能存一个。解决：把手机号的可空策略改一下——允许为 NULL，NULL 在唯一约束中不参与重复判断，多条记录的手机号为空也没有冲突。如果必须用空字符串，就别在 phone 上加唯一约束，改由应用层去判断。

5.2 外键导入数据顺序混乱导致 “Cannot add or update a child row”

现象：把写好的建表脚本发给同学测试，他先往 sale_item 表插数据，直接报外键关联错误。原因：sale_item 表的外键关联 sale 表和 drug 表，插入子表记录时它所引用的父表记录必须已经存在。解决：养成按“父表 → 子表”顺序插入的习惯，药品和供应商先插，然后是采购主单和明细，最后是销售主单和明细。更省事的方案是准备一个完整的测试数据 SQL 脚本，按依赖顺序排好 INSERT 语句，一口气执行完。

5.3 MySQL 8.x 连接报错 “Public Key Retrieval is not allowed”

现象：用 Navicat 或者 JDBC 连接本地 MySQL 8.x 时能连上，但程序运行时抛异常。原因：MySQL 8.x 默认的 caching_sha2_password 认证插件要求客户端在首次连接时获取服务器公钥，某些连接工具默认不允许自动获取。解决：连接串上加上 allowPublicKeyRetrieval=true 和 useSSL=false，后者只建议在本地开发环境用，生产环境必须启用 SSL。课程设计全是本地环境，这样配没有任何问题。

5.4 药品数量的 DECIMAL 类型被 Java 读出来变成浮点误差

现象：Java 端用 getFloat 读药品单价，15.00 变成 15.0 还能接受，但 8.50 变成 8.499999 就完全没法看。原因：DECIMAL 在数据库里是定点数，但你用浮点类型接收时已经被转换成二进制浮点格式了。解决：Java 端用 BigDecimal 接收，MyBatis 里把 resultType 对应的字段类型设为 BigDecimal，Python 端则用 Decimal 类型处理。这是课程设计代码里很容易忽略的一环，但价格显示错乱直接拉低演示观感。

5.5 报告里贴的 SQL 脚本和运行截图对不上，老师一追问就穿帮

现象：报告的“核心代码”章节复制了网上某套完整脚本，但系统演示截图里运行出来的表结构跟代码并不是同一套。原因：写报告的人偷懒，图和代码来自两个地方。解决：从建表到查询，全部统一成项目里实际运行的那一份。报告里每个截图对应的 SQL 要先在 MySQL 里跑一遍，把结果核对好再贴。答辩老师不一定认真读每行代码，但会随机挑一张表结构和一段代码比对，穿帮的直接后果就是整篇报告的可信度崩塌。

6. 让课程设计从“能运行”到“能加分”：备份恢复、并发验证和报告里值得多写的一页

代码功能跑通之后，建议你花一个下午把下面三件事做掉，它们不会增加太多工作量，但会让你的设计报告和答辩表现明显超出平均水平。

第一件事是备份与恢复。用 mysqldump 写一个备份脚本，把核心命令和结果放进报告里，这正好对应“数据库管理员”的日常职责。命令很简单：mysqldump -u root -p drug_management > drug_backup.sql，恢复时再执行 mysql -u root -p drug_management < drug_backup.sql。报告里专门写一节“数据备份与恢复策略”，配上备份文件的文件列表截图，这是很多同学完全不会提到的模块，属于性价比最高的加分项。

第二件事是验证事务与并发的基础表现。开两个 MySQL 命令行窗口，同时执行针对同一种药品库存的扣减脚本，观察后执行的那条是否能正确判断库存不足并回滚。这个实验能在报告“事务隔离性验证”一节里写一小段：一个连接扣库存成功，另一个连接等待锁释放后看到库存不足而失败。写这段的意义是证明你理解“数据库死锁”和“并发锁”不只在概念层面，而是亲手测过。

第三件事是设计一个简单的数据库监控或自查脚本。一个十几行的 SQL 批处理，每天检查过期药品数量、库存低于安全线的药品列表、本月销售额，把结果输出成 CSV。不需要做成定时任务，只要把脚本放在项目里，报告里截图展示输出结果即可，显得你有“用数据驱动管理”的意识。

最后说一下报告排版里最值得多写的一页：数据库设计总结与改进方向。不要写“本系统实现了基本功能”，而是写“当前系统在药品批次追溯上只做了单批次管理，未来可通过引入批次表和批次库存表实现进销存全程追溯；在销售分析层面，当前只统计销售额和销量，后续可增加按供应商贡献度分析的功能”。这种写法既坦诚展示了系统的边界，又显示出你理解数据库设计不是一个静态过程，而是会随着业务需求持续演进的。答辩被问到“系统有什么不足”时，你就可以直接引用这页内容，而不是临时编。希望这份从建表到答辩的完整路径能帮到你，按这个思路走完，你的药品管理系统在课程设计里应该就不只是“能运行”而已了。

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