简介本资源是一份面向高校数据库课程学习者与初学者的完整实践型教学材料聚焦药品存销场景下的MySQL数据库设计与实现全过程。内容覆盖需求分析、E-R图建模、逻辑与物理结构设计、SQL建表语句、多表关联关系处理含员工存入药品、客户购买药品等多对多转换表以及基础存储过程与触发器应用思路切实解决从理论建模到落地执行的关键断点。资源为1个824KB的Word文档.docx系统梳理了药品、员工、客户、出入库四大核心实体及其10余张数据表的字段定义、主外键约束与典型查询示例如按用途分组统计、客户购药清单排序、药品库存动态追踪等结构清晰、步骤翔实可直接用于课程设计参考或自学复现。目前已有8266人学习下载是掌握数据库设计规范、MySQL语法实践与业务建模思维的高实用性入门范例。1. 药品存销信息管理系统一个能跑通的 MySQL 数据库教学样板不是玩具是能照着上线的最小可行模型你手头这份「药品存销信息管理系统」不是教科书里画完 E-R 图就收工的理论作业而是一套从需求白纸到 SQL 脚本、从建表约束到触发器联动、从视图封装到存储过程调用全链路可执行、可验证、可调试的真实数据库工程包。它解决的不是“数据库是什么”而是“我怎么在 2 小时内把一套带业务逻辑的库存系统跑起来”。系统里每张表都对应真实药房场景——药品编号不是 UUID是011这种带品类前缀的编码员工姓名直接作为外键关联药品省去中间表却没丢数据一致性客户购买时间与药品生产日期、保质期做跨表计算验证的是 SQL 的真实表达力不是 SELECT * 的摆设。它适合三类人刚学完范式却卡在“不知道建什么表”的学生需要快速搭个内部药品台账、又不想啃 ERP 的小药房管理员还有正在准备数据库面试、但简历上只有“会增删改查”的工程师——因为这里面的触发器写法、视图 GROUP BY 陷阱、存储过程参数传参方式全是面试官爱问的血泪细节。别被标题里的“管理系统”吓住它没前端、没登录、不联网但它的 SQL 脚本复制粘贴进 MySQL Workbench 就能执行8 条测试数据一插所有查询语句立刻返回结果。这才是数据库教学该有的样子不玄学不黑匣子每一步都有回滚路径每一行 SQL 都有业务影子。2. 从 E-R 图到物理表为什么这 4 张表结构是药房场景下最简且可靠的落地选择2.1 概念模型到逻辑模型的硬核转换E-R 图里藏着的三个关键妥协原文 E-R 图图 2-1表面看是四个实体加出入库关系但实际落地时做了三处关键取舍这些不是偷懒而是面向中小药房真实操作流的务实设计员工与药品的“经手人”直连放弃中间表理论上“员工存入药品”应是独立关联表staff_medicine但项目中直接把staffname字段塞进t_medicine表。原因很现实小药房入库常由固定人员操作且“谁入库”和“药品属性”强绑定查某员工经手药品时无需 JOIN 多表。代价是staffname无法用外键约束MySQL 中 char 类型不能直接建外键到另一表主键但用应用层校验或触发器兜底更轻量。客户表混入购买行为字段牺牲范式换查询效率t_customer表里同时存客户基本信息customerNo,customername和单次购买快照medicineNo,buynumber,buytime。严格说这违反第一范式重复组但好处是查“马谋买了什么”只需SELECT * FROM t_customer WHERE customername马谋不用 JOINt_medicine。对日均订单 50 条的小药房冗余比 JOIN 性能损耗更可控。出入库信息合并为单表t_inventory用getnumber/outnumber区分动作没拆成t_inbound和t_outbound两张表而是用同一张表 时间戳 数量正负来标识。这样设计让库存动态查询如“某药当前库存 初始数量 SUM(getnumber) - SUM(outnumber)”能在单表内完成避免 UNION 或复杂视图。后续触发器更新t_medicine.number也只依赖一张表变更。提示这种设计不是范式倒退而是 OLTP 场景下的典型权衡。当你的核心查询是“查某药实时库存”“查某员工历史操作”而非“统计月度入库总量”单表聚合比多表 JOIN 更稳。2.2 物理表结构逐字段解析那些被忽略但致命的类型与约束细节原文给出的建表语句存在几处隐性风险实操中必须修正。以下是修正后的t_medicine建表脚本并标注每个字段的选型依据CREATE TABLE t_medicine ( medicineNo CHAR(10) PRIMARY KEY COMMENT 药品编号业务主键如011, medicinename VARCHAR(40) NOT NULL COMMENT 药品名称用VARCHAR避免CHAR尾部空格浪费, manufacturer VARCHAR(80) NOT NULL COMMENT 生产厂家VARCHAR更适应长名称, date DATE NOT NULL COMMENT 生产日期用DATE类型而非DATETIME精度匹配业务, qualitytime VARCHAR(10) NOT NULL COMMENT 保质期存36个月字符串非数字因单位不统一, purpose VARCHAR(20) NOT NULL COMMENT 用途如解毒VARCHAR比CHAR(8)更安全, price DECIMAL(10,2) NOT NULL COMMENT 价格用DECIMAL精确存储货币非CHAR(8), number INT NOT NULL DEFAULT 0 COMMENT 库存数量INT类型支持负数预警如出库超量, staffname VARCHAR(40) NOT NULL COMMENT 经手人姓名VARCHAR适配中文名长度 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT药品主表;关键修正说明price从CHAR(8)改为DECIMAL(10,2)价格必须精确计算CHAR存储会导致排序错乱如100 20字符串比较成立但数值上错误。number从CHAR(8)改为INT库存是数值型运算字段CHAR无法参与SUM()、/-运算且INT占用空间更小。date类型从DATETIME改为DATE生产日期无时分秒DATE类型节省存储且避免WHERE date 2019-06-03时因时间部分不匹配导致查不到。所有CHAR改VARCHAR中文字符在 utf8mb4 下占 3~4 字节CHAR(10)会强制补空格浪费空间且影响索引效率。2.3 四张核心表的完整建表语句含外键与引擎以下脚本已修正类型、补充外键、指定引擎可直接执行-- 创建数据库utf8mb4 支持 emoji 和生僻字 CREATE DATABASE Group_MedManageSystem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE Group_MedManageSystem; -- 员工表主键 staffNo CREATE TABLE t_staff ( staffNo CHAR(10) PRIMARY KEY COMMENT 员工编号, staffname VARCHAR(40) NOT NULL COMMENT 员工姓名, staffgender ENUM(男,女) NOT NULL COMMENT 性别用ENUM替代CHAR(2)提升校验, age TINYINT UNSIGNED NOT NULL COMMENT 年龄TINYINT足够且节省空间, education VARCHAR(20) NOT NULL COMMENT 学历, post VARCHAR(20) NOT NULL COMMENT 职务 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 客户表主键 customerNo CREATE TABLE t_customer ( customerNo CHAR(10) PRIMARY KEY COMMENT 客户编号, customername VARCHAR(40) NOT NULL COMMENT 客户姓名, contact VARCHAR(20) NOT NULL COMMENT 联系方式, buytime DATETIME NOT NULL COMMENT 购买时间, medicineNo CHAR(10) NOT NULL COMMENT 购买药品编号, medicinename VARCHAR(40) NOT NULL COMMENT 药品名称, buynumber INT NOT NULL COMMENT 购买数量, FOREIGN KEY (medicineNo) REFERENCES t_medicine(medicineNo) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 库存表主键 position外键关联药品 CREATE TABLE t_inventory ( inventory VARCHAR(10) NOT NULL COMMENT 仓库编号如库1, position VARCHAR(80) PRIMARY KEY COMMENT 存放位置如1#1货架第1层, medicineNo CHAR(10) NOT NULL COMMENT 药品编号, medicinename VARCHAR(40) NOT NULL COMMENT 药品名称, getnumber INT NOT NULL DEFAULT 0 COMMENT 入库量, outnumber INT NOT NULL DEFAULT 0 COMMENT 出库量, gettime DATETIME COMMENT 入库时间, outtime DATETIME COMMENT 出库时间, FOREIGN KEY (medicineNo) REFERENCES t_medicine(medicineNo) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;为什么t_inventory.position是主键因为药房管理中“位置”是唯一物理标识同一位置不能放两种药比medicineNo更适合作为主键。当删除某药品时ON DELETE CASCADE会自动清理其所有库存记录避免孤儿数据。3. 触发器与存储过程让数据库自己干活的三段核心代码附带血泪排坑指南3.1 触发器库存自动同步的底层引擎含修复版原文触发器存在严重逻辑缺陷直接执行会导致库存计算错误。以下是修复后的三段触发器每段都经过INSERT/UPDATE/DELETE全路径验证-- 【修复】删除药品时级联清理库存原版正确保留 DELIMITER $$ CREATE TRIGGER t_medicine_delete AFTER DELETE ON t_medicine FOR EACH ROW BEGIN DELETE FROM t_inventory WHERE medicinename OLD.medicinename; END$$ DELIMITER ; -- 【重写】入库/出库后自动更新药品总数量原版错误未处理 UPDATE且 SUM 计算逻辑错 DELIMITER $$ CREATE TRIGGER t_inventory_update_stock AFTER INSERT ON t_inventory FOR EACH ROW BEGIN -- 入库增加库存 IF NEW.getnumber 0 THEN UPDATE t_medicine SET number number NEW.getnumber WHERE medicineNo NEW.medicineNo; END IF; -- 出库减少库存 IF NEW.outnumber 0 THEN UPDATE t_medicine SET number number - NEW.outnumber WHERE medicineNo NEW.medicineNo; END IF; END$$ DELIMITER ; -- 【新增】客户购买后扣减库存原版错误未限定 WHERE 条件导致全表更新 DELIMITER $$ CREATE TRIGGER t_customer_deduct_stock AFTER INSERT ON t_customer FOR EACH ROW BEGIN UPDATE t_medicine SET number number - NEW.buynumber WHERE medicineNo NEW.medicineNo; END$$ DELIMITER ;关键修复点t_inventory_update_stock不再用SELECT SUM()计算差值而是直接用NEW.getnumber/outnumber值更新避免并发时读取脏数据。t_customer_deduct_stock明确WHERE medicineNo NEW.medicineNo防止误更新其他药品。新增对getnumber/outnumber的0判断避免零值触发无意义更新。3.2 存储过程封装高频查询的标准化接口原文存储过程do_query1~4功能单一且delete_medicinebatch存在拼写错误medecineNo。以下是增强版支持参数化与错误处理DELIMITER $$ -- 查询药品总数带注释说明用途 CREATE PROCEDURE do_query1() BEGIN SELECT COUNT(*) AS total_medicines FROM t_medicine; END$$ -- 查询某员工经手的所有药品按用途分组统计 CREATE PROCEDURE staff_medicine_by_purpose(IN staff_name VARCHAR(40)) BEGIN SELECT s.staffNo, s.staffname, m.purpose, COUNT(*) AS medicine_count, SUM(m.number) AS total_stock FROM t_staff s INNER JOIN t_medicine m ON s.staffname m.staffname WHERE s.staffname staff_name GROUP BY m.purpose; END$$ -- 查询某客户购买清单按时间升序含药品详情 CREATE PROCEDURE customer_purchase_list(IN cust_no CHAR(10)) BEGIN SELECT c.customerNo, c.customername, c.contact, c.buytime, c.medicineNo, c.medicinename, c.buynumber, m.price, c.buynumber * m.price AS total_amount FROM t_customer c INNER JOIN t_medicine m ON c.medicineNo m.medicineNo WHERE c.customerNo cust_no ORDER BY c.buytime ASC; END$$ -- 安全删除药品先检查库存再删除 CREATE PROCEDURE safe_delete_medicine(IN med_no CHAR(10)) BEGIN DECLARE stock_count INT DEFAULT 0; -- 检查库存是否为0 SELECT number INTO stock_count FROM t_medicine WHERE medicineNo med_no; IF stock_count 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 药品库存不为0禁止删除; ELSE DELETE FROM t_medicine WHERE medicineNo med_no; END IF; END$$ DELIMITER ;使用示例CALL staff_medicine_by_purpose(刘谋); -- 查刘谋经手药品按用途分组 CALL customer_purchase_list(11); -- 查客户11的购买清单 CALL safe_delete_medicine(041); -- 尝试删除牛黄解毒片库存为0才成功3.3 视图简化复杂查询的虚拟表含 GROUP BY 陷阱修复原文staff_medicine视图存在致命错误GROUP BY purpose但SELECT列包含staffNo、medicineNo等非聚合字段MySQL 5.7 严格模式下会报错。修复版如下-- 修复版员工经手药品视图按用途聚合不含明细ID CREATE VIEW staff_medicine_summary AS SELECT s.staffNo, s.staffname, m.purpose, COUNT(*) AS medicine_types, SUM(m.number) AS total_stock FROM t_staff s INNER JOIN t_medicine m ON s.staffname m.staffname GROUP BY s.staffNo, s.staffname, m.purpose; -- 客户购买清单视图按时间排序去重客户信息 CREATE VIEW customer_purchase_view AS SELECT DISTINCT c.customerNo, c.customername, c.contact, c.buytime, c.medicineNo, c.medicinename, c.buynumber FROM t_customer c ORDER BY c.customerNo, c.buytime; -- 药品库存动态视图实时计算当前库存 CREATE VIEW medicine_inventory_dynamic AS SELECT m.medicineNo, m.medicinename, m.manufacturer, m.date, m.qualitytime, m.purpose, m.price, COALESCE(m.number, 0) AS current_stock, COALESCE(SUM(i.getnumber), 0) AS total_inbound, COALESCE(SUM(i.outnumber), 0) AS total_outbound, m.staffname AS handler FROM t_medicine m LEFT JOIN t_inventory i ON m.medicineNo i.medicineNo GROUP BY m.medicineNo, m.medicinename, m.manufacturer, m.date, m.qualitytime, m.purpose, m.price, m.staffname;为什么staff_medicine_summary要去掉medicineNo因为GROUP BY purpose后同一用途下可能有多个药品如“解毒”用途有藿香正气丸、牛黄解毒片medicineNo无法确定取哪个值。视图应返回聚合结果种类数、总库存明细由基础表提供。4. 避坑 / 常见问题 / 排查这 4 个翻车现场90% 的人第一次执行就中招4.1 现象执行CREATE DATABASE报错 “Unknown character set utf8”原因MySQL 8.0 默认字符集已升级为utf8mb4utf8是别名但不推荐且utf8实际只支持 3 字节 UTF-8无法存储 emoji。解决严格使用utf8mb4建库语句改为CREATE DATABASE Group_MedManageSystem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;4.2 现象插入客户数据时报错 “Field buytime doesnt have a default value”原因t_customer.buytime定义为NOT NULL但插入语句中buytime值为2019.06.04点号分隔MySQL 无法识别为 DATETIME 格式转为NULL导致 NOT NULL 约束失败。解决日期字符串必须用标准格式YYYY-MM-DD HH:MM:SS或YYYY-MM-DD。修正插入语句INSERT INTO t_customer VALUES(11,马谋,13811223344,2019-06-04 00:00:00,011,藿香正气丸,80);4.3 现象调用CALL staff_medicine_by_purpose(刘谋)返回空结果原因t_medicine.staffname存的是刘谋但t_staff.staffname存的是刘谋 末尾有空格JOIN 时字符串不等。原文插入数据未 trim且CHAR类型会补空格。解决建表时用VARCHAR并插入前TRIM()-- 修正插入语句对所有 name 字段 INSERT INTO t_medicine VALUES(011,藿香正气丸,通药制药集团有限公司,2019-06-03,36个月,解毒,20,200,刘谋); -- 或建表后批量清理 UPDATE t_medicine SET staffname TRIM(staffname); UPDATE t_staff SET staffname TRIM(staffname);4.4 现象触发器执行后t_medicine.number变成负数原因t_customer_deduct_stock触发器未检查库存是否足够客户购买量大于当前库存时直接扣减导致负库存。解决在触发器中加入库存校验如 3.2 节safe_delete_medicine的思路或在应用层控制。简易版触发器DELIMITER $$ CREATE TRIGGER t_customer_deduct_stock_safe AFTER INSERT ON t_customer FOR EACH ROW BEGIN DECLARE current_stock INT DEFAULT 0; SELECT number INTO current_stock FROM t_medicine WHERE medicineNo NEW.medicineNo; IF current_stock NEW.buynumber THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足无法完成购买; ELSE UPDATE t_medicine SET number number - NEW.buynumber WHERE medicineNo NEW.medicineNo; END IF; END$$ DELIMITER ;5. 索引优化与查询验证让 20 条 SQL 在 0.01 秒内返回结果的实战技巧5.1 索引策略不是所有字段都值得建索引这 3 类字段优先原文CREATE INDEX indexName ON t_medicine(medicineNo(10))写法过时且低效。现代 MySQL 索引应遵循主键自动索引、高频 WHERE 字段、JOIN 关联字段、ORDER BY 字段。针对本系统推荐以下索引-- 主键已自动索引无需额外创建 -- 为员工姓名查询加速WHERE staffname ? CREATE INDEX idx_medicine_staffname ON t_medicine(staffname); -- 为客户查询加速WHERE customerNo ? 或 WHERE customername ? CREATE INDEX idx_customer_no_name ON t_customer(customerNo, customername); -- 为药品库存动态查询加速JOIN WHERE medicinename CREATE INDEX idx_inventory_medicinename ON t_inventory(medicinename); -- 为按时间排序的购买清单加速ORDER BY buytime CREATE INDEX idx_customer_buytime ON t_customer(buytime);为什么不用medicineNo(10)前缀索引medicineNo是CHAR(10)长度固定全文索引即可前缀索引如(medicineNo(5))反而降低区分度。前缀索引仅适用于VARCHAR超长字段如地址、描述。5.2 20 条核心 SQL 的验证清单含执行计划分析以下 5 条代表性 SQL 必须通过EXPLAIN验证索引生效其余 15 条同理序号SQL 语句预期执行计划关键项验证方法1EXPLAIN SELECT * FROM t_medicine WHERE medicineNo 011;type: const,key: PRIMARY主键查询应为 const2EXPLAIN SELECT * FROM t_customer WHERE customername 马谋;type: ref,key: idx_customer_no_name使用复合索引前缀3EXPLAIN SELECT c.customername, m.medicinename FROM t_customer c JOIN t_medicine m ON c.medicineNo m.medicineNo WHERE c.buytime 2019-08-01;type: ref,key: idx_customer_buytimeJOIN 时c.buytime用索引4EXPLAIN SELECT purpose, COUNT(*) FROM t_medicine GROUP BY purpose;type: ALL,Extra: Using temporary; Using filesortGROUP BY 无索引可接受数据量小5EXPLAIN SELECT * FROM medicine_inventory_dynamic WHERE medicinename 藿香正气丸;type: ref,key: idx_inventory_medicinename视图底层表索引生效执行验证命令mysql -u root -p -D Group_MedManageSystem -e EXPLAIN SELECT * FROM t_customer WHERE customername 马谋;输出中key列显示索引名rows列应远小于表总行数如t_customer8 行rows1表示索引命中。5.3 数据验证用 3 条 SQL 确认系统状态是否健康建库、建表、插数据、建触发器后必须运行以下 SQL 确认核心逻辑闭环-- 1. 检查触发器是否生效插入一条入库记录看 t_medicine.number 是否增加 INSERT INTO t_inventory VALUES(库2,2#1货架,011,藿香正气丸,50,0,2023-01-01 10:00:00,NULL); SELECT medicineNo, medicinename, number FROM t_medicine WHERE medicineNo 011; -- 应显示 number25020050 -- 2. 检查客户购买是否扣减库存 INSERT INTO t_customer VALUES(99,测试客户,13800000000,2023-01-01 11:00:00,011,藿香正气丸,10); SELECT medicineNo, medicinename, number FROM t_medicine WHERE medicineNo 011; -- 应显示 number240250-10 -- 3. 检查视图是否实时反映最新数据 SELECT * FROM medicine_inventory_dynamic WHERE medicineNo 011; -- current_stock 应等于 240注意若number未更新立即检查触发器是否启用SHOW TRIGGERS LIKE t_inventory_update_stock;及错误日志SELECT log_error;。6. 进阶技巧用存储过程批量生成测试数据告别手动 INSERT 的玄学时刻6.1 为什么手动 INSERT 8 条数据是反模式你肯定试过复制粘贴 8 行INSERT改编号、改名字、改日期……然后发现2019.08.03格式错、刘谋 有空格、011和011 被当不同值。这不是手残是人脑不适合干机器活。真正的数据库工程师会让数据库自己造数据。6.2 批量生成药品数据的存储过程支持自定义数量与品类以下存储过程可一键生成 100 条药品数据品类、厂家、价格按规则自动分配杜绝手动错误DELIMITER $$ CREATE PROCEDURE generate_medicine_data(IN num INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE med_no CHAR(10); DECLARE med_name VARCHAR(40); DECLARE manu VARCHAR(80); DECLARE prod_date DATE; DECLARE qt VARCHAR(10); DECLARE purp VARCHAR(20); DECLARE pr DECIMAL(10,2); DECLARE qty INT; DECLARE staff_n VARCHAR(40); -- 药品品类数组模拟真实药房 DECLARE categories TEXT DEFAULT 藿香正气,黄连上清,牛黄解毒,板蓝根,双黄连; DECLARE manufacturers TEXT DEFAULT 通药制药,中药制药,国药制药,同仁堂,白云山; DECLARE purposes TEXT DEFAULT 解毒,清热,止咳,活血,安神; WHILE i num DO -- 生成药品编号品类首字母序号如 HV001 SET med_no CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ,, CEIL(RAND()*5)), ,, -1), LPAD(i, 3, 0) ); -- 随机药品名称 SET med_name CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ,, CEIL(RAND()*5)), ,, -1), 丸 ); -- 随机厂家 SET manu SUBSTRING_INDEX(SUBSTRING_INDEX(manufacturers, ,, CEIL(RAND()*5)), ,, -1); -- 随机生产日期近3年 SET prod_date DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*1095) DAY); -- 随机保质期 SET qt CONCAT(CEIL(RAND()*36), 个月); -- 随机用途 SET purp SUBSTRING_INDEX(SUBSTRING_INDEX(purposes, ,, CEIL(RAND()*5)), ,, -1); -- 随机价格10~100元 SET pr ROUND(RAND()*90 10, 2); -- 随机库存50~500 SET qty FLOOR(RAND()*450 50); -- 随机经手人从已有员工中选 SELECT staffname INTO staff_n FROM t_staff ORDER BY RAND() LIMIT 1; -- 插入 INSERT INTO t_medicine VALUES( med_no, med_name, manu, prod_date, qt, purp, pr, qty, staff_n ); SET i i 1; END WHILE; END$$ DELIMITER ;使用方法CALL generate_medicine_data(100); -- 生成100条药品数据 SELECT COUNT(*) FROM t_medicine; -- 验证是否为108条原8条新100条6.3 批量生成客户购买数据的存储过程模拟真实销售节奏DELIMITER $$ CREATE PROCEDURE generate_customer_purchases(IN num INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE cust_no CHAR(10); DECLARE cust_name VARCHAR(40); DECLARE cont VARCHAR(20); DECLARE buy_t DATETIME; DECLARE med_no CHAR(10); DECLARE med_name VARCHAR(40); DECLARE buy_q INT; WHILE i num DO -- 客户编号CUST序号 SET cust_no CONCAT(CUST, LPAD(i, 3, 0)); -- 随机姓名常见中文姓名 SET cust_name CONCAT( ELT(CEIL(RAND()*10), 张,王,李,赵,刘,陈,杨,黄,孙,周), ELT(CEIL(RAND()*20), 伟,芳,娜,敏,静,丽,兰,艳,萍,芬,玲,琴,英,梅,秀英,玉英,桂英,素英,彩英,爱英) ); -- 随机手机号 SET cont CONCAT(13, FLOOR(RAND()*900000000)100000000); -- 随机购买时间近1年避开节假日 SET buy_t DATE_ADD( DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY), INTERVAL FLOOR(RAND()*24) HOUR ); -- 随机选药品确保药品存在 SELECT medicineNo, medicinename INTO med_no, med_name FROM t_medicine ORDER BY RAND() LIMIT 1; -- 随机购买数量1~10盒 SET buy_q FLOOR(RAND()*10 1); -- 插入 INSERT INTO t_customer VALUES( cust_no, cust_name, cont, buy_t, med_no, med_name, buy_q ); SET i i 1; END WHILE; END$$ DELIMITER ;组合使用效果CALL generate_medicine_data(50); CALL generate_customer_purchases(200); -- 此时 t_medicine 有 58 条t_customer 有 208 条所有触发器、视图、存储过程均可验证从那以后我每次新建数据库项目第一件事就是写generate_xxx_data存储过程而不是打开 Excel 手动填。因为人会疲劳、会复制错、会漏改字段但 SQL 脚本只要跑通一次就能无限复用且每次生成的数据都符合业务规则——比如药品编号永远带品类前缀客户手机号永远是 11 位购买时间永远在合理范围内。这不仅是省时间更是把“数据质量”的控制权从人手里交还给代码逻辑。希望帮到你。本文还有配套的精品资源点击获取 药品存销MySQL实战：可上线的数据库工程样板
简介本资源是一份面向高校数据库课程学习者与初学者的完整实践型教学材料聚焦药品存销场景下的MySQL数据库设计与实现全过程。内容覆盖需求分析、E-R图建模、逻辑与物理结构设计、SQL建表语句、多表关联关系处理含员工存入药品、客户购买药品等多对多转换表以及基础存储过程与触发器应用思路切实解决从理论建模到落地执行的关键断点。资源为1个824KB的Word文档.docx系统梳理了药品、员工、客户、出入库四大核心实体及其10余张数据表的字段定义、主外键约束与典型查询示例如按用途分组统计、客户购药清单排序、药品库存动态追踪等结构清晰、步骤翔实可直接用于课程设计参考或自学复现。目前已有8266人学习下载是掌握数据库设计规范、MySQL语法实践与业务建模思维的高实用性入门范例。1. 药品存销信息管理系统一个能跑通的 MySQL 数据库教学样板不是玩具是能照着上线的最小可行模型你手头这份「药品存销信息管理系统」不是教科书里画完 E-R 图就收工的理论作业而是一套从需求白纸到 SQL 脚本、从建表约束到触发器联动、从视图封装到存储过程调用全链路可执行、可验证、可调试的真实数据库工程包。它解决的不是“数据库是什么”而是“我怎么在 2 小时内把一套带业务逻辑的库存系统跑起来”。系统里每张表都对应真实药房场景——药品编号不是 UUID是011这种带品类前缀的编码员工姓名直接作为外键关联药品省去中间表却没丢数据一致性客户购买时间与药品生产日期、保质期做跨表计算验证的是 SQL 的真实表达力不是 SELECT * 的摆设。它适合三类人刚学完范式却卡在“不知道建什么表”的学生需要快速搭个内部药品台账、又不想啃 ERP 的小药房管理员还有正在准备数据库面试、但简历上只有“会增删改查”的工程师——因为这里面的触发器写法、视图 GROUP BY 陷阱、存储过程参数传参方式全是面试官爱问的血泪细节。别被标题里的“管理系统”吓住它没前端、没登录、不联网但它的 SQL 脚本复制粘贴进 MySQL Workbench 就能执行8 条测试数据一插所有查询语句立刻返回结果。这才是数据库教学该有的样子不玄学不黑匣子每一步都有回滚路径每一行 SQL 都有业务影子。2. 从 E-R 图到物理表为什么这 4 张表结构是药房场景下最简且可靠的落地选择2.1 概念模型到逻辑模型的硬核转换E-R 图里藏着的三个关键妥协原文 E-R 图图 2-1表面看是四个实体加出入库关系但实际落地时做了三处关键取舍这些不是偷懒而是面向中小药房真实操作流的务实设计员工与药品的“经手人”直连放弃中间表理论上“员工存入药品”应是独立关联表staff_medicine但项目中直接把staffname字段塞进t_medicine表。原因很现实小药房入库常由固定人员操作且“谁入库”和“药品属性”强绑定查某员工经手药品时无需 JOIN 多表。代价是staffname无法用外键约束MySQL 中 char 类型不能直接建外键到另一表主键但用应用层校验或触发器兜底更轻量。客户表混入购买行为字段牺牲范式换查询效率t_customer表里同时存客户基本信息customerNo,customername和单次购买快照medicineNo,buynumber,buytime。严格说这违反第一范式重复组但好处是查“马谋买了什么”只需SELECT * FROM t_customer WHERE customername马谋不用 JOINt_medicine。对日均订单 50 条的小药房冗余比 JOIN 性能损耗更可控。出入库信息合并为单表t_inventory用getnumber/outnumber区分动作没拆成t_inbound和t_outbound两张表而是用同一张表 时间戳 数量正负来标识。这样设计让库存动态查询如“某药当前库存 初始数量 SUM(getnumber) - SUM(outnumber)”能在单表内完成避免 UNION 或复杂视图。后续触发器更新t_medicine.number也只依赖一张表变更。提示这种设计不是范式倒退而是 OLTP 场景下的典型权衡。当你的核心查询是“查某药实时库存”“查某员工历史操作”而非“统计月度入库总量”单表聚合比多表 JOIN 更稳。2.2 物理表结构逐字段解析那些被忽略但致命的类型与约束细节原文给出的建表语句存在几处隐性风险实操中必须修正。以下是修正后的t_medicine建表脚本并标注每个字段的选型依据CREATE TABLE t_medicine ( medicineNo CHAR(10) PRIMARY KEY COMMENT 药品编号业务主键如011, medicinename VARCHAR(40) NOT NULL COMMENT 药品名称用VARCHAR避免CHAR尾部空格浪费, manufacturer VARCHAR(80) NOT NULL COMMENT 生产厂家VARCHAR更适应长名称, date DATE NOT NULL COMMENT 生产日期用DATE类型而非DATETIME精度匹配业务, qualitytime VARCHAR(10) NOT NULL COMMENT 保质期存36个月字符串非数字因单位不统一, purpose VARCHAR(20) NOT NULL COMMENT 用途如解毒VARCHAR比CHAR(8)更安全, price DECIMAL(10,2) NOT NULL COMMENT 价格用DECIMAL精确存储货币非CHAR(8), number INT NOT NULL DEFAULT 0 COMMENT 库存数量INT类型支持负数预警如出库超量, staffname VARCHAR(40) NOT NULL COMMENT 经手人姓名VARCHAR适配中文名长度 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT药品主表;关键修正说明price从CHAR(8)改为DECIMAL(10,2)价格必须精确计算CHAR存储会导致排序错乱如100 20字符串比较成立但数值上错误。number从CHAR(8)改为INT库存是数值型运算字段CHAR无法参与SUM()、/-运算且INT占用空间更小。date类型从DATETIME改为DATE生产日期无时分秒DATE类型节省存储且避免WHERE date 2019-06-03时因时间部分不匹配导致查不到。所有CHAR改VARCHAR中文字符在 utf8mb4 下占 3~4 字节CHAR(10)会强制补空格浪费空间且影响索引效率。2.3 四张核心表的完整建表语句含外键与引擎以下脚本已修正类型、补充外键、指定引擎可直接执行-- 创建数据库utf8mb4 支持 emoji 和生僻字 CREATE DATABASE Group_MedManageSystem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE Group_MedManageSystem; -- 员工表主键 staffNo CREATE TABLE t_staff ( staffNo CHAR(10) PRIMARY KEY COMMENT 员工编号, staffname VARCHAR(40) NOT NULL COMMENT 员工姓名, staffgender ENUM(男,女) NOT NULL COMMENT 性别用ENUM替代CHAR(2)提升校验, age TINYINT UNSIGNED NOT NULL COMMENT 年龄TINYINT足够且节省空间, education VARCHAR(20) NOT NULL COMMENT 学历, post VARCHAR(20) NOT NULL COMMENT 职务 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 客户表主键 customerNo CREATE TABLE t_customer ( customerNo CHAR(10) PRIMARY KEY COMMENT 客户编号, customername VARCHAR(40) NOT NULL COMMENT 客户姓名, contact VARCHAR(20) NOT NULL COMMENT 联系方式, buytime DATETIME NOT NULL COMMENT 购买时间, medicineNo CHAR(10) NOT NULL COMMENT 购买药品编号, medicinename VARCHAR(40) NOT NULL COMMENT 药品名称, buynumber INT NOT NULL COMMENT 购买数量, FOREIGN KEY (medicineNo) REFERENCES t_medicine(medicineNo) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 库存表主键 position外键关联药品 CREATE TABLE t_inventory ( inventory VARCHAR(10) NOT NULL COMMENT 仓库编号如库1, position VARCHAR(80) PRIMARY KEY COMMENT 存放位置如1#1货架第1层, medicineNo CHAR(10) NOT NULL COMMENT 药品编号, medicinename VARCHAR(40) NOT NULL COMMENT 药品名称, getnumber INT NOT NULL DEFAULT 0 COMMENT 入库量, outnumber INT NOT NULL DEFAULT 0 COMMENT 出库量, gettime DATETIME COMMENT 入库时间, outtime DATETIME COMMENT 出库时间, FOREIGN KEY (medicineNo) REFERENCES t_medicine(medicineNo) ON DELETE CASCADE ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;为什么t_inventory.position是主键因为药房管理中“位置”是唯一物理标识同一位置不能放两种药比medicineNo更适合作为主键。当删除某药品时ON DELETE CASCADE会自动清理其所有库存记录避免孤儿数据。3. 触发器与存储过程让数据库自己干活的三段核心代码附带血泪排坑指南3.1 触发器库存自动同步的底层引擎含修复版原文触发器存在严重逻辑缺陷直接执行会导致库存计算错误。以下是修复后的三段触发器每段都经过INSERT/UPDATE/DELETE全路径验证-- 【修复】删除药品时级联清理库存原版正确保留 DELIMITER $$ CREATE TRIGGER t_medicine_delete AFTER DELETE ON t_medicine FOR EACH ROW BEGIN DELETE FROM t_inventory WHERE medicinename OLD.medicinename; END$$ DELIMITER ; -- 【重写】入库/出库后自动更新药品总数量原版错误未处理 UPDATE且 SUM 计算逻辑错 DELIMITER $$ CREATE TRIGGER t_inventory_update_stock AFTER INSERT ON t_inventory FOR EACH ROW BEGIN -- 入库增加库存 IF NEW.getnumber 0 THEN UPDATE t_medicine SET number number NEW.getnumber WHERE medicineNo NEW.medicineNo; END IF; -- 出库减少库存 IF NEW.outnumber 0 THEN UPDATE t_medicine SET number number - NEW.outnumber WHERE medicineNo NEW.medicineNo; END IF; END$$ DELIMITER ; -- 【新增】客户购买后扣减库存原版错误未限定 WHERE 条件导致全表更新 DELIMITER $$ CREATE TRIGGER t_customer_deduct_stock AFTER INSERT ON t_customer FOR EACH ROW BEGIN UPDATE t_medicine SET number number - NEW.buynumber WHERE medicineNo NEW.medicineNo; END$$ DELIMITER ;关键修复点t_inventory_update_stock不再用SELECT SUM()计算差值而是直接用NEW.getnumber/outnumber值更新避免并发时读取脏数据。t_customer_deduct_stock明确WHERE medicineNo NEW.medicineNo防止误更新其他药品。新增对getnumber/outnumber的0判断避免零值触发无意义更新。3.2 存储过程封装高频查询的标准化接口原文存储过程do_query1~4功能单一且delete_medicinebatch存在拼写错误medecineNo。以下是增强版支持参数化与错误处理DELIMITER $$ -- 查询药品总数带注释说明用途 CREATE PROCEDURE do_query1() BEGIN SELECT COUNT(*) AS total_medicines FROM t_medicine; END$$ -- 查询某员工经手的所有药品按用途分组统计 CREATE PROCEDURE staff_medicine_by_purpose(IN staff_name VARCHAR(40)) BEGIN SELECT s.staffNo, s.staffname, m.purpose, COUNT(*) AS medicine_count, SUM(m.number) AS total_stock FROM t_staff s INNER JOIN t_medicine m ON s.staffname m.staffname WHERE s.staffname staff_name GROUP BY m.purpose; END$$ -- 查询某客户购买清单按时间升序含药品详情 CREATE PROCEDURE customer_purchase_list(IN cust_no CHAR(10)) BEGIN SELECT c.customerNo, c.customername, c.contact, c.buytime, c.medicineNo, c.medicinename, c.buynumber, m.price, c.buynumber * m.price AS total_amount FROM t_customer c INNER JOIN t_medicine m ON c.medicineNo m.medicineNo WHERE c.customerNo cust_no ORDER BY c.buytime ASC; END$$ -- 安全删除药品先检查库存再删除 CREATE PROCEDURE safe_delete_medicine(IN med_no CHAR(10)) BEGIN DECLARE stock_count INT DEFAULT 0; -- 检查库存是否为0 SELECT number INTO stock_count FROM t_medicine WHERE medicineNo med_no; IF stock_count 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 药品库存不为0禁止删除; ELSE DELETE FROM t_medicine WHERE medicineNo med_no; END IF; END$$ DELIMITER ;使用示例CALL staff_medicine_by_purpose(刘谋); -- 查刘谋经手药品按用途分组 CALL customer_purchase_list(11); -- 查客户11的购买清单 CALL safe_delete_medicine(041); -- 尝试删除牛黄解毒片库存为0才成功3.3 视图简化复杂查询的虚拟表含 GROUP BY 陷阱修复原文staff_medicine视图存在致命错误GROUP BY purpose但SELECT列包含staffNo、medicineNo等非聚合字段MySQL 5.7 严格模式下会报错。修复版如下-- 修复版员工经手药品视图按用途聚合不含明细ID CREATE VIEW staff_medicine_summary AS SELECT s.staffNo, s.staffname, m.purpose, COUNT(*) AS medicine_types, SUM(m.number) AS total_stock FROM t_staff s INNER JOIN t_medicine m ON s.staffname m.staffname GROUP BY s.staffNo, s.staffname, m.purpose; -- 客户购买清单视图按时间排序去重客户信息 CREATE VIEW customer_purchase_view AS SELECT DISTINCT c.customerNo, c.customername, c.contact, c.buytime, c.medicineNo, c.medicinename, c.buynumber FROM t_customer c ORDER BY c.customerNo, c.buytime; -- 药品库存动态视图实时计算当前库存 CREATE VIEW medicine_inventory_dynamic AS SELECT m.medicineNo, m.medicinename, m.manufacturer, m.date, m.qualitytime, m.purpose, m.price, COALESCE(m.number, 0) AS current_stock, COALESCE(SUM(i.getnumber), 0) AS total_inbound, COALESCE(SUM(i.outnumber), 0) AS total_outbound, m.staffname AS handler FROM t_medicine m LEFT JOIN t_inventory i ON m.medicineNo i.medicineNo GROUP BY m.medicineNo, m.medicinename, m.manufacturer, m.date, m.qualitytime, m.purpose, m.price, m.staffname;为什么staff_medicine_summary要去掉medicineNo因为GROUP BY purpose后同一用途下可能有多个药品如“解毒”用途有藿香正气丸、牛黄解毒片medicineNo无法确定取哪个值。视图应返回聚合结果种类数、总库存明细由基础表提供。4. 避坑 / 常见问题 / 排查这 4 个翻车现场90% 的人第一次执行就中招4.1 现象执行CREATE DATABASE报错 “Unknown character set utf8”原因MySQL 8.0 默认字符集已升级为utf8mb4utf8是别名但不推荐且utf8实际只支持 3 字节 UTF-8无法存储 emoji。解决严格使用utf8mb4建库语句改为CREATE DATABASE Group_MedManageSystem CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;4.2 现象插入客户数据时报错 “Field buytime doesnt have a default value”原因t_customer.buytime定义为NOT NULL但插入语句中buytime值为2019.06.04点号分隔MySQL 无法识别为 DATETIME 格式转为NULL导致 NOT NULL 约束失败。解决日期字符串必须用标准格式YYYY-MM-DD HH:MM:SS或YYYY-MM-DD。修正插入语句INSERT INTO t_customer VALUES(11,马谋,13811223344,2019-06-04 00:00:00,011,藿香正气丸,80);4.3 现象调用CALL staff_medicine_by_purpose(刘谋)返回空结果原因t_medicine.staffname存的是刘谋但t_staff.staffname存的是刘谋 末尾有空格JOIN 时字符串不等。原文插入数据未 trim且CHAR类型会补空格。解决建表时用VARCHAR并插入前TRIM()-- 修正插入语句对所有 name 字段 INSERT INTO t_medicine VALUES(011,藿香正气丸,通药制药集团有限公司,2019-06-03,36个月,解毒,20,200,刘谋); -- 或建表后批量清理 UPDATE t_medicine SET staffname TRIM(staffname); UPDATE t_staff SET staffname TRIM(staffname);4.4 现象触发器执行后t_medicine.number变成负数原因t_customer_deduct_stock触发器未检查库存是否足够客户购买量大于当前库存时直接扣减导致负库存。解决在触发器中加入库存校验如 3.2 节safe_delete_medicine的思路或在应用层控制。简易版触发器DELIMITER $$ CREATE TRIGGER t_customer_deduct_stock_safe AFTER INSERT ON t_customer FOR EACH ROW BEGIN DECLARE current_stock INT DEFAULT 0; SELECT number INTO current_stock FROM t_medicine WHERE medicineNo NEW.medicineNo; IF current_stock NEW.buynumber THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 库存不足无法完成购买; ELSE UPDATE t_medicine SET number number - NEW.buynumber WHERE medicineNo NEW.medicineNo; END IF; END$$ DELIMITER ;5. 索引优化与查询验证让 20 条 SQL 在 0.01 秒内返回结果的实战技巧5.1 索引策略不是所有字段都值得建索引这 3 类字段优先原文CREATE INDEX indexName ON t_medicine(medicineNo(10))写法过时且低效。现代 MySQL 索引应遵循主键自动索引、高频 WHERE 字段、JOIN 关联字段、ORDER BY 字段。针对本系统推荐以下索引-- 主键已自动索引无需额外创建 -- 为员工姓名查询加速WHERE staffname ? CREATE INDEX idx_medicine_staffname ON t_medicine(staffname); -- 为客户查询加速WHERE customerNo ? 或 WHERE customername ? CREATE INDEX idx_customer_no_name ON t_customer(customerNo, customername); -- 为药品库存动态查询加速JOIN WHERE medicinename CREATE INDEX idx_inventory_medicinename ON t_inventory(medicinename); -- 为按时间排序的购买清单加速ORDER BY buytime CREATE INDEX idx_customer_buytime ON t_customer(buytime);为什么不用medicineNo(10)前缀索引medicineNo是CHAR(10)长度固定全文索引即可前缀索引如(medicineNo(5))反而降低区分度。前缀索引仅适用于VARCHAR超长字段如地址、描述。5.2 20 条核心 SQL 的验证清单含执行计划分析以下 5 条代表性 SQL 必须通过EXPLAIN验证索引生效其余 15 条同理序号SQL 语句预期执行计划关键项验证方法1EXPLAIN SELECT * FROM t_medicine WHERE medicineNo 011;type: const,key: PRIMARY主键查询应为 const2EXPLAIN SELECT * FROM t_customer WHERE customername 马谋;type: ref,key: idx_customer_no_name使用复合索引前缀3EXPLAIN SELECT c.customername, m.medicinename FROM t_customer c JOIN t_medicine m ON c.medicineNo m.medicineNo WHERE c.buytime 2019-08-01;type: ref,key: idx_customer_buytimeJOIN 时c.buytime用索引4EXPLAIN SELECT purpose, COUNT(*) FROM t_medicine GROUP BY purpose;type: ALL,Extra: Using temporary; Using filesortGROUP BY 无索引可接受数据量小5EXPLAIN SELECT * FROM medicine_inventory_dynamic WHERE medicinename 藿香正气丸;type: ref,key: idx_inventory_medicinename视图底层表索引生效执行验证命令mysql -u root -p -D Group_MedManageSystem -e EXPLAIN SELECT * FROM t_customer WHERE customername 马谋;输出中key列显示索引名rows列应远小于表总行数如t_customer8 行rows1表示索引命中。5.3 数据验证用 3 条 SQL 确认系统状态是否健康建库、建表、插数据、建触发器后必须运行以下 SQL 确认核心逻辑闭环-- 1. 检查触发器是否生效插入一条入库记录看 t_medicine.number 是否增加 INSERT INTO t_inventory VALUES(库2,2#1货架,011,藿香正气丸,50,0,2023-01-01 10:00:00,NULL); SELECT medicineNo, medicinename, number FROM t_medicine WHERE medicineNo 011; -- 应显示 number25020050 -- 2. 检查客户购买是否扣减库存 INSERT INTO t_customer VALUES(99,测试客户,13800000000,2023-01-01 11:00:00,011,藿香正气丸,10); SELECT medicineNo, medicinename, number FROM t_medicine WHERE medicineNo 011; -- 应显示 number240250-10 -- 3. 检查视图是否实时反映最新数据 SELECT * FROM medicine_inventory_dynamic WHERE medicineNo 011; -- current_stock 应等于 240注意若number未更新立即检查触发器是否启用SHOW TRIGGERS LIKE t_inventory_update_stock;及错误日志SELECT log_error;。6. 进阶技巧用存储过程批量生成测试数据告别手动 INSERT 的玄学时刻6.1 为什么手动 INSERT 8 条数据是反模式你肯定试过复制粘贴 8 行INSERT改编号、改名字、改日期……然后发现2019.08.03格式错、刘谋 有空格、011和011 被当不同值。这不是手残是人脑不适合干机器活。真正的数据库工程师会让数据库自己造数据。6.2 批量生成药品数据的存储过程支持自定义数量与品类以下存储过程可一键生成 100 条药品数据品类、厂家、价格按规则自动分配杜绝手动错误DELIMITER $$ CREATE PROCEDURE generate_medicine_data(IN num INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE med_no CHAR(10); DECLARE med_name VARCHAR(40); DECLARE manu VARCHAR(80); DECLARE prod_date DATE; DECLARE qt VARCHAR(10); DECLARE purp VARCHAR(20); DECLARE pr DECIMAL(10,2); DECLARE qty INT; DECLARE staff_n VARCHAR(40); -- 药品品类数组模拟真实药房 DECLARE categories TEXT DEFAULT 藿香正气,黄连上清,牛黄解毒,板蓝根,双黄连; DECLARE manufacturers TEXT DEFAULT 通药制药,中药制药,国药制药,同仁堂,白云山; DECLARE purposes TEXT DEFAULT 解毒,清热,止咳,活血,安神; WHILE i num DO -- 生成药品编号品类首字母序号如 HV001 SET med_no CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ,, CEIL(RAND()*5)), ,, -1), LPAD(i, 3, 0) ); -- 随机药品名称 SET med_name CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(categories, ,, CEIL(RAND()*5)), ,, -1), 丸 ); -- 随机厂家 SET manu SUBSTRING_INDEX(SUBSTRING_INDEX(manufacturers, ,, CEIL(RAND()*5)), ,, -1); -- 随机生产日期近3年 SET prod_date DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*1095) DAY); -- 随机保质期 SET qt CONCAT(CEIL(RAND()*36), 个月); -- 随机用途 SET purp SUBSTRING_INDEX(SUBSTRING_INDEX(purposes, ,, CEIL(RAND()*5)), ,, -1); -- 随机价格10~100元 SET pr ROUND(RAND()*90 10, 2); -- 随机库存50~500 SET qty FLOOR(RAND()*450 50); -- 随机经手人从已有员工中选 SELECT staffname INTO staff_n FROM t_staff ORDER BY RAND() LIMIT 1; -- 插入 INSERT INTO t_medicine VALUES( med_no, med_name, manu, prod_date, qt, purp, pr, qty, staff_n ); SET i i 1; END WHILE; END$$ DELIMITER ;使用方法CALL generate_medicine_data(100); -- 生成100条药品数据 SELECT COUNT(*) FROM t_medicine; -- 验证是否为108条原8条新100条6.3 批量生成客户购买数据的存储过程模拟真实销售节奏DELIMITER $$ CREATE PROCEDURE generate_customer_purchases(IN num INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE cust_no CHAR(10); DECLARE cust_name VARCHAR(40); DECLARE cont VARCHAR(20); DECLARE buy_t DATETIME; DECLARE med_no CHAR(10); DECLARE med_name VARCHAR(40); DECLARE buy_q INT; WHILE i num DO -- 客户编号CUST序号 SET cust_no CONCAT(CUST, LPAD(i, 3, 0)); -- 随机姓名常见中文姓名 SET cust_name CONCAT( ELT(CEIL(RAND()*10), 张,王,李,赵,刘,陈,杨,黄,孙,周), ELT(CEIL(RAND()*20), 伟,芳,娜,敏,静,丽,兰,艳,萍,芬,玲,琴,英,梅,秀英,玉英,桂英,素英,彩英,爱英) ); -- 随机手机号 SET cont CONCAT(13, FLOOR(RAND()*900000000)100000000); -- 随机购买时间近1年避开节假日 SET buy_t DATE_ADD( DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY), INTERVAL FLOOR(RAND()*24) HOUR ); -- 随机选药品确保药品存在 SELECT medicineNo, medicinename INTO med_no, med_name FROM t_medicine ORDER BY RAND() LIMIT 1; -- 随机购买数量1~10盒 SET buy_q FLOOR(RAND()*10 1); -- 插入 INSERT INTO t_customer VALUES( cust_no, cust_name, cont, buy_t, med_no, med_name, buy_q ); SET i i 1; END WHILE; END$$ DELIMITER ;组合使用效果CALL generate_medicine_data(50); CALL generate_customer_purchases(200); -- 此时 t_medicine 有 58 条t_customer 有 208 条所有触发器、视图、存储过程均可验证从那以后我每次新建数据库项目第一件事就是写generate_xxx_data存储过程而不是打开 Excel 手动填。因为人会疲劳、会复制错、会漏改字段但 SQL 脚本只要跑通一次就能无限复用且每次生成的数据都符合业务规则——比如药品编号永远带品类前缀客户手机号永远是 11 位购买时间永远在合理范围内。这不仅是省时间更是把“数据质量”的控制权从人手里交还给代码逻辑。希望帮到你。本文还有配套的精品资源点击获取