藥信息管理系統(tǒng)數據庫設計：從E-R圖到事務扣庫存的完整實戰(zhàn))
簡介面向數據庫課程設計或醫(yī)藥行業(yè)信息化入門學習者的完整項目資料包主題為醫(yī)藥信息管理系統(tǒng)。系統(tǒng)圍繞基本信息、進貨、庫房、銷售與財務統(tǒng)計五大模塊展開覆蓋藥品/員工/客戶/供應商維護、入庫盤點、銷售退貨和日/月報表等典型業(yè)務適合用 MySQL 完成課程設計或理解 Java Web 項目結構。壓縮包共 335 個文件、約 4.05MB以 54 個 Java 源碼文件、38 個 HTML 頁面、44 個 JS 腳本和 12 個 CSS 樣式為主并含 SQL 數據庫腳本、Maven 配置及說明文檔150 個 GIF 動圖可輔助查看界面效果與操作流程。已有 71 人學習。通過該項目可借鑒模塊劃分、數據庫表設計、前后端組織方式和 Maven 工程搭建思路進而快速改造為符合自身選題的課設作品。1. 數據庫課設醫(yī)藥信息管理系統(tǒng)先想清楚這三點再開寫已經把選題盯在醫(yī)藥信息管理系統(tǒng)上的人多半是因為它業(yè)務貼近生活有藥、有供應商、有入庫出庫、有銷售和處方字段多但是不抽象做出來能演示的頁面也多。但真的動手后你會發(fā)現90%的課設翻車案例不是不會寫增刪改查而是把表設計成了“藥品表一張、銷售表一張”的玩具結構答辯時老師問一句“一張?zhí)幏嚼锇鄠€藥品怎么查”就卡住。這個課設的核心考點從來不是界面上有多少個按鈕而是三件事進銷存的庫存怎么在不丟流水的前提下扣減、處方明細和主單怎么用外鍵關聯、以及多人同時開單時怎么保證庫存不為負。這篇筆記會按我踩過的坑來拆解整套方案適合正在做數據庫課設、以及準備把它當作品集項目去優(yōu)化的同學。先從業(yè)務模型說起因為表結構錯了后面所有代碼都是在給錯誤打補丁。2. 從業(yè)務到E-R圖把醫(yī)藥庫存、供應商和處方單拆成能交差的模型圖2.1 醫(yī)藥信息管理系統(tǒng)的核心業(yè)務閉環(huán)任何進銷存系統(tǒng)都繞不開“采購、入庫、建檔、在庫、出庫、銷售”這條線。醫(yī)藥信息管理系統(tǒng)比普通商品管理特殊在兩點藥品有“批準文號”和“有效期”出庫的時候必須先進先出不能把快過期的藥壓在庫底銷售環(huán)節(jié)涉及處方一張?zhí)幏娇梢蚤_多種藥品還要記錄醫(yī)生、藥房和數量。所以你在課設里至少要覆蓋下面這個閉環(huán)藥品錄入基礎檔案 → 供應商供貨生成入庫單 → 入庫單明細增加庫存 → 銷售/處方開單 → 扣減庫存 → 生成銷售流水。任何一個環(huán)節(jié)斷了比如入庫時只改了庫存表而沒寫入庫明細或者銷售時只寫了銷售單而沒扣庫存后面的統(tǒng)計就會對不上。建模的第一件事不是打開工具畫圖而是把業(yè)務規(guī)則列成清單。我習慣先寫出來“藥品必須屬于某個分類”“同一供應商可以供應多種藥品”“一張入庫單包含多條入庫明細每個明細對應一種藥品和一個庫存變動”“一張銷售單包含多條明細每條明細對應一種藥品和銷售數量”“不允許銷售庫存為0的藥品”。這些規(guī)則直接決定了實體和關系也決定了外鍵加在哪里。數據庫增刪改查只是最后一步業(yè)務邊界定了表結構就自然出來了。2.2 從業(yè)務規(guī)則反推實體與關系先定主鍵和外鍵按上述規(guī)則實體可以拆成藥品信息、藥品分類、供應商、入庫單、入庫單明細、銷售單處方、銷售單明細、用戶。其中“銷售單”在醫(yī)藥場景里通常就是“處方”可以復用一張表增加字段區(qū)分是柜臺銷售還是處方銷售。主鍵的選擇遵守一個原則能用業(yè)務編號就用業(yè)務編號但業(yè)務編號不穩(wěn)定時就用自增ID。比如藥品表的“藥品ID”可以用自增而“批準文號”雖然唯一但不同劑型可能同號不能當主鍵。入庫單、銷售單建議用單號字段作為邏輯主鍵同時再加一個自增內部ID原因后面在避坑章節(jié)細說。外鍵關系要畫成四條主線。一是“藥品分類”和“藥品”之間是一對多分類ID是藥品表的外鍵二是“供應商”和“入庫單”之間是一對多供應商ID是入庫單外鍵三是“入庫單”和“入庫單明細”是一對多入庫單ID是明細表外鍵四是“銷售單”和“銷售單明細”是一對多銷售單ID是明細表外鍵。藥品和供應商之間不直接給外鍵而是通過入庫單明細建立的“多對多”關聯。這樣設計的好處是你以后想查“哪個供應商是某種藥的主要來源”時只要對入庫明細做GROUP BY不需要用逗號拼接多值字段。2.3 從業(yè)務規(guī)則反推E-R圖的畫法要點E-R圖在課設報告里占比很高但很多人畫成了“一堆表連一堆表”這沒有把關系表達清楚。正確的做法是先畫最重要的關系在“銷售單明細”實體上畫兩個菱形一邊連“銷售單”一邊連“藥品”中間標注“包含N種”。在“入庫單明細”實體上同樣連“入庫單”和“藥品”。然后單獨抽出一張“用戶”實體與“銷售單”之間畫“審核/開單”的連線。最后才是“供應商”和“分類”這類附屬實體從外向里連接到對應的主實體。畫E-R圖的技巧是用“實體關系實體”的三元組去自檢從銷售單出發(fā)經過“包含關系”到銷售單明細再到藥品這叫“主表-明細-基礎檔案”是關系模式里最普遍也最容易考的一對多結構。老師很喜歡問“為什么不把明細直接塞進銷售單表”答案是因為一張?zhí)幏娇梢詫懚鄠€藥塞進去就要用重復字段或者逗號分隔既違反第一范式也查不了“某種藥賣了多少錢”這種聚合SQL。把這句話寫進課設報告分數基本穩(wěn)了。2.4 范式級別的取舍第三范式夠用反范式看場景教科書讓你規(guī)范到第三范式但實際設計里我會給自己留兩個后門。第一個是“庫存數量”字段。嚴格說庫存可以由“入庫明細合計 - 銷售明細合計”推導出來屬于冗余不滿足第二范式。但課設階段如果每查一次庫存都要匯總一次明細表數據量一大就慢所以我保留“藥品表.庫存數量”字段同時用事務保證每次入庫和銷售都同步更新它。這個冗余在答辯現場反而能展開講“用空間換查詢時間”屬于主動反范式。第二個后門是“藥品分類”在藥品表里存了個分類名稱的冗余字段。理論上應該只存分類ID通過JOIN去查名稱但為了方便頁面表格展示和減少JOIN次數我在藥品表里冗余了“分類名稱”。代價是分類改名時要做同步更新這在課設場景基本不發(fā)生。其余所有表嚴格按第三范式來沒有重復字段組所有包含非主屬性的字段都依賴主鍵而且依賴的是完整主鍵而不是部分依賴。中間張表“入庫單明細”用的聯合主鍵入庫單ID藥品ID注意明細里不能出現貨物的“倉庫位置”這種只依賴入庫單的字段否則就是部分依賴需要拆出去。3. 建庫建表與基礎數據一套可直接跑的MySQL腳本3.1 建庫與字符集選擇utf8mb4和InnoDB是默認答案寫腳本第一步是先定字符集和存儲引擎。字符集我固定用utf8mb4不要用utf8因為utf8在MySQL里最多存3字節(jié)遇到生僻藥品名或特殊符號會出現亂碼。排序規(guī)則用utf8mb4_unicode_ci它對中文搜索比較友好。存儲引擎選InnoDB這是為了保證事務、外鍵約束和行級鎖可用。MyISAM雖然查詢快一點但不支持事務和外鍵在這個項目里沒有任何優(yōu)勢。數據庫創(chuàng)建語句如下CREATE DATABASE IF NOT EXISTS pharma_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE pharma_db;這里有兩個容易被忽略的點。一是“IF NOT EXISTS”在課設里很實用重新跑腳本不會報錯二是COLLATE如果不下發(fā)后續(xù)建表時字段默認排序規(guī)則各不相同做WHERE查詢比較字符串時容易報“Illegal mix of collations”。建議把所有表、字段統(tǒng)一到同一個COLLATE這是血淚經驗。3.2 藥品表、供應商表、入庫表、銷售表的完整建表語句核心表一共五張再加上兩張輔助表。先建被依賴的基礎表再建主表最后建明細表避免外鍵引用錯誤。下面是藥品表、供應商表和用戶表的示例CREATE TABLE drug_category ( category_id INT AUTO_INCREMENT PRIMARY KEY, category_name VARCHAR(50) NOT NULL UNIQUE, remark VARCHAR(200) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE drug ( drug_id INT AUTO_INCREMENT PRIMARY KEY, drug_code VARCHAR(30) NOT NULL UNIQUE COMMENT 藥品編碼, drug_name VARCHAR(100) NOT NULL, category_id INT NOT NULL, specification VARCHAR(50) COMMENT 規(guī)格, unit VARCHAR(20) DEFAULT 盒, purchase_price DECIMAL(10,2) NOT NULL, sale_price DECIMAL(10,2) NOT NULL, stock_quantity INT NOT NULL DEFAULT 0, expire_date DATE NOT NULL, supplier_id INT COMMENT 默認供應商, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_drug_category FOREIGN KEY (category_id) REFERENCES drug_category(category_id), CONSTRAINT fk_drug_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE supplier ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, supplier_code VARCHAR(20) NOT NULL UNIQUE, supplier_name VARCHAR(100) NOT NULL, contact_person VARCHAR(30), phone VARCHAR(20), address VARCHAR(200) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE sys_user ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(64) NOT NULL, real_name VARCHAR(30), role VARCHAR(20) NOT NULL DEFAULT cashier COMMENT admin/manager/cashier ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;建表順序很重要先建drug_category再建supplier然后建drug否則drug表外鍵指向的表還不存在。注意drug表里的supplier_id設為“默認供應商”表示一種藥可以對應多個供應商時默認選擇其中一個真正的供應商和藥品的多對多關系要由入庫單明細來體現。price字段用DECIMAL而不是FLOAT后面避坑章節(jié)會專門解釋。接下來是入庫單和入庫明細。主表記錄這次進貨的整體信息明細表記錄每一種藥品進了多少、單價多少CREATE TABLE stock_in ( stock_in_id INT AUTO_INCREMENT PRIMARY KEY, stock_in_no VARCHAR(30) NOT NULL UNIQUE, supplier_id INT NOT NULL, operator_id INT NOT NULL, stock_in_date DATETIME NOT NULL, total_amount DECIMAL(12,2) NOT NULL, remark VARCHAR(200), CONSTRAINT fk_in_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id), CONSTRAINT fk_in_operator FOREIGN KEY (operator_id) REFERENCES sys_user(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE stock_in_detail ( stock_in_id INT NOT NULL, drug_id INT NOT NULL, quantity INT NOT NULL, cost_price DECIMAL(10,2) NOT NULL, production_date DATE, expire_date DATE, PRIMARY KEY (stock_in_id, drug_id), CONSTRAINT fk_detail_in FOREIGN KEY (stock_in_id) REFERENCES stock_in(stock_in_id), CONSTRAINT fk_detail_drug FOREIGN KEY (drug_id) REFERENCES drug(drug_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;銷售單和銷售明細與入庫結構類似但多了一個“處方醫(yī)生”和“客戶姓名”的字段用來貼近醫(yī)藥管理場景CREATE TABLE sale_order ( sale_id INT AUTO_INCREMENT PRIMARY KEY, sale_no VARCHAR(30) NOT NULL UNIQUE, customer_name VARCHAR(50), doctor_name VARCHAR(30), cashier_id INT NOT NULL, sale_date DATETIME NOT NULL, total_amount DECIMAL(12,2) NOT NULL, status VARCHAR(20) DEFAULT completed, CONSTRAINT fk_sale_cashier FOREIGN KEY (cashier_id) REFERENCES sys_user(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE sale_order_detail ( sale_id INT NOT NULL, drug_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (sale_id, drug_id), CONSTRAINT fk_detail_sale FOREIGN KEY (sale_id) REFERENCES sale_order(sale_id), CONSTRAINT fk_detail_sale_drug FOREIGN KEY (drug_id) REFERENCES drug(drug_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;銷售明細表里的price字段保存的是“成交時賣的單價”不要直接去關聯藥品表的sale_price因為以后藥品調價了歷史銷售記錄還要保留當時的金額。這個字段叫“快照字段”在庫存類系統(tǒng)里非常常見。入庫明細里的cost_price同理也必須是入庫那一刻的實際成本價。3.3 約束設計主鍵、外鍵、CHECK與默認值怎么配合主鍵設計上所有自增主鍵都用INT如果覺得數據量會很大可以換成BIGINT但課設階段沒必要。唯一鍵用在業(yè)務單號上比如stock_in_no、sale_no用UNIQUE約束確保不會重復生成。外鍵約束必須有這是課設評分能直觀看到的數據庫知識點但不要在明細表上對明細記錄建“無用的級聯刪除”。正確的是主單刪除時明細應該同時刪除所以外鍵用ON DELETE CASCADE藥品和分類之間分類不能刪要限制所以用ON DELETE RESTRICT。CHECK約束很多同學會寫比如庫存不能為負、價格大于0但MySQL 8.0.16之前的版本不強制執(zhí)行CHECK只能在應用層校驗。我在建表腳本里仍然寫CHECK因為報告里可以寫“數據庫層面也有約束”同時應用層再攔一道。默認值盡量給全創(chuàng)建時間用DEFAULT CURRENT_TIMESTAMP銷售狀態(tài)用DEFAULT completed數量用DEFAULT 0。這樣INSERT語句可以少寫很多字段而且不會因為漏填導致空指針。3.4 初始化數據與視圖讓演示狀態(tài)更合理建完表還要造一批演示數據不能用網上的“學生表”隨便填。我一般造5個藥品分類、20個藥品、5個供應商、3個用戶再造3張入庫單和若干銷售單。一個重要技巧是讓部分藥品庫存低于“預警線”比如10盒部分藥品的過期時間在3個月內這樣后面的“有效期預警”和“庫存不足查詢”視圖就有數據可演示。INSERT語句我就不全貼了給個示例INSERT INTO drug_category (category_name) VALUES (心腦血管類), (消化系統(tǒng)類), (呼吸系統(tǒng)類); INSERT INTO supplier (supplier_code, supplier_name) VALUES (SP001, 華東醫(yī)藥), (SP002, 華北制藥); INSERT INTO sys_user (username, password_hash, real_name, role) VALUES (admin, hashed_value, 管理員, admin), (cashier01, hashed_value, 張收銀, cashier); INSERT INTO drug (drug_code, drug_name, category_id, unit, purchase_price, sale_price, stock_quantity, expire_date) VALUES (DRUG001, 阿司匹林腸溶片, 1, 盒, 8.50, 15.00, 100, 2026-05-30), (DRUG002, 奧美拉唑膠囊, 2, 盒, 12.00, 20.00, 8, 2026-01-15);這里故意把奧美拉唑的庫存設為8低于預警線演示時直接跑查詢就能出效果。視圖建議建兩個一個是“庫存預警_含分類名”一個是“銷售統(tǒng)計_按日按月”。視圖的好處是答辯時不用現場敲很長的SQL讓評委看視圖定義更直觀CREATE VIEW low_stock_view AS SELECT d.drug_id, d.drug_name, d.stock_quantity, d.expire_date FROM drug d WHERE d.stock_quantity 10 OR d.expire_date DATE_ADD(CURDATE(), INTERVAL 90 DAY);4. 核心業(yè)務SQL寫法增刪改查之外的加分項4.1 庫存扣減與流水記錄一個銷售事務怎么寫醫(yī)藥信息管理系統(tǒng)的核心不是“藥品增刪改查”而是“銷售完成后銷售單、明細、庫存、臺賬要同時更新”。建議把這個過程寫成一個存儲過程或者事務模板答辯時展示數據庫并發(fā)鎖和事務的一致性控制。下面是我常用的銷售開單事務寫法START TRANSACTION; -- 1. 插入銷售主單 INSERT INTO sale_order (sale_no, customer_name, doctor_name, cashier_id, sale_date, total_amount) VALUES (SO20250601001, 張三, 王醫(yī)生, 2, NOW(), 0); SET sale_id LAST_INSERT_ID(); -- 2. 插入銷售明細同時計算總金額 INSERT INTO sale_order_detail (sale_id, drug_id, quantity, price, amount) SELECT sale_id, d.drug_id, 2, d.sale_price, d.sale_price * 2 FROM drug d WHERE d.drug_code DRUG001; -- 3. 更新藥品庫存并且強制要求庫存不能為負 UPDATE drug SET stock_quantity stock_quantity - 2 WHERE drug_id 1 AND stock_quantity 2; -- 4. 檢查是否有行被更新如果沒有表示庫存不足 SELECT ROW_COUNT() AS affected_rows; -- 5. 如果affected_rows為0則回滾 -- 注意在存儲過程中可以用IF判斷這里僅演示SQL順序 COMMIT;這個事務的寫法和純增刪改查的區(qū)別在第三步UPDATE語句里帶了AND stock_quantity 2這在MySQL里是“條件更新”相當于數據庫層幫你做了一次庫存校驗。執(zhí)行UPDATE后如果影響行數為0說明庫存不夠就應該回滾。更規(guī)范的寫法是用SELECT ... FOR UPDATE給藥品行加鎖再判斷庫存量但因為課設單機環(huán)境下并發(fā)不高用條件更新已經足夠。這種寫法能避免“庫存被扣成負數”的經典錯誤。4.2 藥品有效期預警和庫存下限查詢用視圖與條件查詢實現預警功能很出效果而且只要一條SQL。除了前面建的低庫存視圖還需要按有效期排序的查詢。MySQL的DATEDIFF函數可以直接算出過期距離天數SELECT drug_name, expire_date, DATEDIFF(expire_date, CURDATE()) AS remain_days FROM drug WHERE DATEDIFF(expire_date, CURDATE()) 90 ORDER BY remain_days ASC;這條查詢放在“庫存管理”頁面按照剩余天數從少到多排序就是很直觀的效期提醒。如果你想升級成“每批藥品獨立效期管理”就要在入庫明細里增加生產日期和效期字段出庫時按照“先入先出”的原則選擇最早批次的庫存。課設里掛在藥品表上的expire_date一個字段就夠演示但你在報告里如果能寫清楚“藥品表上的效期是最新批次的效期更嚴格的做法是把效期放明細”老師會覺得你想過這個問題。4.3 存儲過程與觸發(fā)器課程設計的加分項很多學校的數據庫課設評分點里有“存儲過程”和“觸發(fā)器”這兩個數據庫對象。存儲過程建議封裝兩個一個是“藥品采購入庫”在insert入庫單和明細后循環(huán)更新藥品的庫存和最近入庫價另一個是“按時間范圍統(tǒng)計銷售排名”用GROUP BY生成報表。下面是一個簡單入庫存儲過程的模板DELIMITER $$ CREATE PROCEDURE sp_stock_in( IN p_supplier_id INT, IN p_operator_id INT, IN p_drug_code VARCHAR(30), IN p_quantity INT, IN p_cost_price DECIMAL(10,2) ) BEGIN DECLARE v_drug_id INT; DECLARE v_stock_in_id INT; SELECT drug_id INTO v_drug_id FROM drug WHERE drug_code p_drug_code; INSERT INTO stock_in (stock_in_no, supplier_id, operator_id, stock_in_date, total_amount) VALUES (CONCAT(SI, DATE_FORMAT(NOW(), %Y%m%d%H%i%s)), p_supplier_id, p_operator_id, NOW(), p_quantity * p_cost_price); SET v_stock_in_id LAST_INSERT_ID(); INSERT INTO stock_in_detail (stock_in_id, drug_id, quantity, cost_price, expire_date) VALUES (v_stock_in_id, v_drug_id, p_quantity, p_cost_price, DATE_ADD(NOW(), INTERVAL 2 YEAR)); UPDATE drug SET stock_quantity stock_quantity p_quantity, purchase_price p_cost_price WHERE drug_id v_drug_id; END$$ DELIMITER ;注意DELIMITER的使用在命令行和Navicat里存儲過程體中多個分號會被MySQL當成語句結束DELIMITER $$就是為了臨時把終止符改成$$避免過程體內插值報錯。參數名前面的p_后綴是為了避免和列名沖突這算是個小習慣。觸發(fā)器可以作為“庫存不足自動提醒”的補充。比如在銷售明細表上做一個AFTER INSERT觸發(fā)器自動更新藥品表的庫存觸發(fā)器代碼不長但它會隱藏業(yè)務邏輯讓日后排錯變難。我的建議是課設里寫一個觸發(fā)器證明你會用就行核心業(yè)務邏輯放存儲過程或應用層不要把大量規(guī)則都壓進觸發(fā)器。觸發(fā)器一旦出錯SQL很難調試而且觸發(fā)器里的SELECT不能往臨時表以外的地方返回結果容易被繞暈。4.4 權限控制不同角色看到不同的菜單和記錄數據庫層的權限控制要做到基于角色的訪問控制。我的做法是在sys_user表里加role字段應用層根據角色選擇查詢語句。比如管理員可以看到所有用戶的銷售記錄收銀員只能看到自己的。SQL層面可以用WHERE條件拼角色入參-- 應用層傳遞當前用戶的 role 和 user_id SELECT so.sale_no, so.sale_date, so.total_amount, u.real_name AS cashier FROM sale_order so JOIN sys_user u ON so.cashier_id u.user_id WHERE ( role admin OR so.cashier_id user_id ) ORDER BY so.sale_date DESC;這個SP里用變量role和user_id表示從應用層傳入的會話變量。數據庫課上講視圖時也可以給一個“我的銷售記錄”視圖視圖定義里帶上DATABASE用戶函數但MySQL里做行級安全不太方便所以我在視圖和存儲過程之間選擇了存儲過程作為主力。數據庫連接池場景下每個請求建立連接后要執(zhí)行SET role ?才能保證不同人的數據隔離。5. 課設避坑數據庫設計階段的五個高頻問題5.1 藥品和供應商關系做成單表導致供應商覆蓋現象藥品表里有一個supplier_name字段一次給同一個藥品維護了兩個供應商后來發(fā)現只能存一個另一個被覆蓋了。原因把多對多關系硬塞進基礎檔案表藥品表根本沒法表達“同一藥品由多個供應商供貨”。解決拆中間表或依賴入庫明細表讓每次進貨同時記錄“供應商藥品批次”這樣既能查某個藥品的歷史供應商又能在報表里按供應商聚合采購金額。課設報告里明確寫出“供應商和藥品是多對多關系通過入庫單明細表實現”這一句話就能證明你懂數據庫關系。5.2 金額用FLOAT存儲對賬差了幾分錢現象錄入采購金額后列表頁顯示108.60導出的報表卻是108.599999。原因FLOAT和DOUBLE是浮點數二進制不能精確表示十進制小數拿來做金額會產生舍入誤差。解決所有金額字段一律改成DECIMAL(10,2)或DECIMAL(12,2)。DECIMAL是按數字存儲的加減乘除都精確。這個坑在醫(yī)藥系統(tǒng)里很致命因為進銷存對賬差一分都會引起麻煩。如果已經建錯了表可以用ALTER TABLE來改字段類型但要暴露給所有相關表和存儲過程。5.3 銷售后沒有流水記錄庫存和報表對不上現象演示時先做一筆銷售然后打開庫存查詢發(fā)現庫存沒變。原因只往sale_order表插了一條記錄沒有同步更新drug表的stock_quantity也沒有在sale_order_detail里記錄明細。解決把銷售流程收斂到同一個事務里或者在應用層用一個Service方法把“插入主表”“插入明細”“更新庫存”三步包在Transactional里。數據庫課設推薦用存儲過程因為課堂環(huán)境里前端語言可能沒法演示事務存儲過程能直接把事務行為展示在數據庫客戶端。別忘了最后再查一次庫存把扣減后的值顯示在界面上。5.4 外鍵濫用導致插入失敗考場手忙腳亂現象在明細表里插入數據時報“Cannot add or update a child row”然后整個銷售流程中斷。原因外鍵約束要求外鍵值必須在主表中存在。常見情況是先在明細分錄里引用了sale_id但sale_order還沒來得及提交自動生成的ID。解決先INSERT主表用LAST_INSERT_ID()拿到新ID再插入明細表。另一個典型錯誤是給“藥品分類”表外鍵加了ON DELETE CASCADE導致刪除一個分類時把所有藥品全刪了。分類屬于“被引用數據”應該用RESTRICT禁止級聯刪除只有明細表才算“依賴數據”才適合用CASCADE。5.5 并發(fā)銷售時庫存變成負數現象兩個窗口同時賣最后一件藥兩個窗口都顯示庫存為1也都扣減成功最后庫存變成-1。原因先查庫存再更新庫存是“非原子操作”兩個會話在“查詢”階段讀到相同值后面各自做了減一導致數據庫并發(fā)鎖沒有真正保住庫存。解決把“判斷庫存大于0”和“扣減庫存”合并到一條UPDATE語句里也就是前面寫的UPDATE ... WHERE stock_quantity n。InnoDB默認行級鎖這條UPDATE會鎖住該藥品行第二個會話必須等第一個提交或回滾才能繼續(xù)這樣就避免了負庫存。如果有意演示悲觀鎖可以用SELECT stock_quantity FROM drug WHERE drug_id ? FOR UPDATE但注意事務結束后要立即提交否則鎖會一直占著這就是數據庫死鎖的高發(fā)場景。6. 把數據量做大用索引和執(zhí)行計劃給系統(tǒng)加分課設答辯到后期老師通常會問“如果這張表有幾十萬條記錄你的查詢還會快嗎”。這個問題別空談索引直接在MySQL里做一做往sale_order_detail表里用存儲過程灌入10萬條模擬數據再跑兩條等價的統(tǒng)計SQL對比執(zhí)行計劃里的掃描行數。我給一個可跑的模擬數據生成腳本DROP PROCEDURE IF EXISTS sp_generate_sale_data; DELIMITER $$ CREATE PROCEDURE sp_generate_sale_data(IN p_loop_count INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE v_sale_id INT; SET AUTOCOMMIT0; WHILE i p_loop_count DO INSERT INTO sale_order (sale_no, customer_name, cashier_id, sale_date, total_amount) VALUES (CONCAT(BIG, DATE_FORMAT(NOW(), %Y%m%d%H%i%s), LPAD(i, 6, 0)), 批量客戶, 1, NOW(), 0); SET v_sale_id LAST_INSERT_ID(); INSERT INTO sale_order_detail (sale_id, drug_id, quantity, price, amount) VALUES (v_sale_id, FLOOR(1 RAND() * 20), FLOOR(1 RAND() * 5), 10.00, 0); SET i i 1; END WHILE; COMMIT; END$$ DELIMITER ; CALL sp_generate_sale_data(100000);跑完后再執(zhí)行EXPLAINEXPLAIN SELECT so.sale_date, so.total_amount FROM sale_order so WHERE so.sale_date BETWEEN 2025-01-01 AND 2025-12-31;在沒有索引時type列通常是ALL行數是全表統(tǒng)計隨后你在sale_date上建立索引再跑一遍EXPLAINtype會變成range。這就是一個很有說服力的現場調優(yōu)演示。接著做一個更貼近業(yè)務的口徑統(tǒng)計每一種藥賣了多少錢。SELECT d.drug_name, SUM(sod.amount) AS total_sale_amount FROM sale_order_detail sod JOIN drug d ON sod.drug_id d.drug_id GROUP BY d.drug_id, d.drug_name ORDER BY total_sale_amount DESC LIMIT 10;這條SQL在數據量上去以后要保證drug_id是主鍵、sale_order_detail的drug_id上有索引不然JOIN慢。這也是“數據庫同步工具”概念里經常提到的索引問題如果兩張表關聯字段忘了建索引數據同步和實時查詢都會卡住。我的個人習慣是給每條核心查詢寫“優(yōu)化前”和“優(yōu)化后”兩版并把EXPLAIN結果截圖貼進課設報告。老師其實很清楚學生做的數據量不大他更在意你有沒有驗證的意識和排查手段。你只要做一次并不復雜的索引對比就能和其他交了個增刪改查的課設拉開差距。最后再說一句實在話數據庫課設的分數不是靠堆功能堆出來的而是靠表結構合理、事務不丟數據、查詢有據可查這三個維度撐起來的。醫(yī)藥信息管理系統(tǒng)是個很好的載體把進銷存和中小型業(yè)務數據庫的常見問題都覆蓋了值得你多花兩個晚上把細節(jié)打磨好。希望這篇筆記能幫你在做課設和答辯的路上少踩幾個坑。本文還有配套的精品資源點擊獲取 醫(yī)藥信息管理系統(tǒng)數據庫設計：從E-R圖到事務扣庫存的完整實戰(zhàn)
簡介面向數據庫課程設計或醫(yī)藥行業(yè)信息化入門學習者的完整項目資料包主題為醫(yī)藥信息管理系統(tǒng)。系統(tǒng)圍繞基本信息、進貨、庫房、銷售與財務統(tǒng)計五大模塊展開覆蓋藥品/員工/客戶/供應商維護、入庫盤點、銷售退貨和日/月報表等典型業(yè)務適合用 MySQL 完成課程設計或理解 Java Web 項目結構。壓縮包共 335 個文件、約 4.05MB以 54 個 Java 源碼文件、38 個 HTML 頁面、44 個 JS 腳本和 12 個 CSS 樣式為主并含 SQL 數據庫腳本、Maven 配置及說明文檔150 個 GIF 動圖可輔助查看界面效果與操作流程。已有 71 人學習。通過該項目可借鑒模塊劃分、數據庫表設計、前后端組織方式和 Maven 工程搭建思路進而快速改造為符合自身選題的課設作品。1. 數據庫課設醫(yī)藥信息管理系統(tǒng)先想清楚這三點再開寫已經把選題盯在醫(yī)藥信息管理系統(tǒng)上的人多半是因為它業(yè)務貼近生活有藥、有供應商、有入庫出庫、有銷售和處方字段多但是不抽象做出來能演示的頁面也多。但真的動手后你會發(fā)現90%的課設翻車案例不是不會寫增刪改查而是把表設計成了“藥品表一張、銷售表一張”的玩具結構答辯時老師問一句“一張?zhí)幏嚼锇鄠€藥品怎么查”就卡住。這個課設的核心考點從來不是界面上有多少個按鈕而是三件事進銷存的庫存怎么在不丟流水的前提下扣減、處方明細和主單怎么用外鍵關聯、以及多人同時開單時怎么保證庫存不為負。這篇筆記會按我踩過的坑來拆解整套方案適合正在做數據庫課設、以及準備把它當作品集項目去優(yōu)化的同學。先從業(yè)務模型說起因為表結構錯了后面所有代碼都是在給錯誤打補丁。2. 從業(yè)務到E-R圖把醫(yī)藥庫存、供應商和處方單拆成能交差的模型圖2.1 醫(yī)藥信息管理系統(tǒng)的核心業(yè)務閉環(huán)任何進銷存系統(tǒng)都繞不開“采購、入庫、建檔、在庫、出庫、銷售”這條線。醫(yī)藥信息管理系統(tǒng)比普通商品管理特殊在兩點藥品有“批準文號”和“有效期”出庫的時候必須先進先出不能把快過期的藥壓在庫底銷售環(huán)節(jié)涉及處方一張?zhí)幏娇梢蚤_多種藥品還要記錄醫(yī)生、藥房和數量。所以你在課設里至少要覆蓋下面這個閉環(huán)藥品錄入基礎檔案 → 供應商供貨生成入庫單 → 入庫單明細增加庫存 → 銷售/處方開單 → 扣減庫存 → 生成銷售流水。任何一個環(huán)節(jié)斷了比如入庫時只改了庫存表而沒寫入庫明細或者銷售時只寫了銷售單而沒扣庫存后面的統(tǒng)計就會對不上。建模的第一件事不是打開工具畫圖而是把業(yè)務規(guī)則列成清單。我習慣先寫出來“藥品必須屬于某個分類”“同一供應商可以供應多種藥品”“一張入庫單包含多條入庫明細每個明細對應一種藥品和一個庫存變動”“一張銷售單包含多條明細每條明細對應一種藥品和銷售數量”“不允許銷售庫存為0的藥品”。這些規(guī)則直接決定了實體和關系也決定了外鍵加在哪里。數據庫增刪改查只是最后一步業(yè)務邊界定了表結構就自然出來了。2.2 從業(yè)務規(guī)則反推實體與關系先定主鍵和外鍵按上述規(guī)則實體可以拆成藥品信息、藥品分類、供應商、入庫單、入庫單明細、銷售單處方、銷售單明細、用戶。其中“銷售單”在醫(yī)藥場景里通常就是“處方”可以復用一張表增加字段區(qū)分是柜臺銷售還是處方銷售。主鍵的選擇遵守一個原則能用業(yè)務編號就用業(yè)務編號但業(yè)務編號不穩(wěn)定時就用自增ID。比如藥品表的“藥品ID”可以用自增而“批準文號”雖然唯一但不同劑型可能同號不能當主鍵。入庫單、銷售單建議用單號字段作為邏輯主鍵同時再加一個自增內部ID原因后面在避坑章節(jié)細說。外鍵關系要畫成四條主線。一是“藥品分類”和“藥品”之間是一對多分類ID是藥品表的外鍵二是“供應商”和“入庫單”之間是一對多供應商ID是入庫單外鍵三是“入庫單”和“入庫單明細”是一對多入庫單ID是明細表外鍵四是“銷售單”和“銷售單明細”是一對多銷售單ID是明細表外鍵。藥品和供應商之間不直接給外鍵而是通過入庫單明細建立的“多對多”關聯。這樣設計的好處是你以后想查“哪個供應商是某種藥的主要來源”時只要對入庫明細做GROUP BY不需要用逗號拼接多值字段。2.3 從業(yè)務規(guī)則反推E-R圖的畫法要點E-R圖在課設報告里占比很高但很多人畫成了“一堆表連一堆表”這沒有把關系表達清楚。正確的做法是先畫最重要的關系在“銷售單明細”實體上畫兩個菱形一邊連“銷售單”一邊連“藥品”中間標注“包含N種”。在“入庫單明細”實體上同樣連“入庫單”和“藥品”。然后單獨抽出一張“用戶”實體與“銷售單”之間畫“審核/開單”的連線。最后才是“供應商”和“分類”這類附屬實體從外向里連接到對應的主實體。畫E-R圖的技巧是用“實體關系實體”的三元組去自檢從銷售單出發(fā)經過“包含關系”到銷售單明細再到藥品這叫“主表-明細-基礎檔案”是關系模式里最普遍也最容易考的一對多結構。老師很喜歡問“為什么不把明細直接塞進銷售單表”答案是因為一張?zhí)幏娇梢詫懚鄠€藥塞進去就要用重復字段或者逗號分隔既違反第一范式也查不了“某種藥賣了多少錢”這種聚合SQL。把這句話寫進課設報告分數基本穩(wěn)了。2.4 范式級別的取舍第三范式夠用反范式看場景教科書讓你規(guī)范到第三范式但實際設計里我會給自己留兩個后門。第一個是“庫存數量”字段。嚴格說庫存可以由“入庫明細合計 - 銷售明細合計”推導出來屬于冗余不滿足第二范式。但課設階段如果每查一次庫存都要匯總一次明細表數據量一大就慢所以我保留“藥品表.庫存數量”字段同時用事務保證每次入庫和銷售都同步更新它。這個冗余在答辯現場反而能展開講“用空間換查詢時間”屬于主動反范式。第二個后門是“藥品分類”在藥品表里存了個分類名稱的冗余字段。理論上應該只存分類ID通過JOIN去查名稱但為了方便頁面表格展示和減少JOIN次數我在藥品表里冗余了“分類名稱”。代價是分類改名時要做同步更新這在課設場景基本不發(fā)生。其余所有表嚴格按第三范式來沒有重復字段組所有包含非主屬性的字段都依賴主鍵而且依賴的是完整主鍵而不是部分依賴。中間張表“入庫單明細”用的聯合主鍵入庫單ID藥品ID注意明細里不能出現貨物的“倉庫位置”這種只依賴入庫單的字段否則就是部分依賴需要拆出去。3. 建庫建表與基礎數據一套可直接跑的MySQL腳本3.1 建庫與字符集選擇utf8mb4和InnoDB是默認答案寫腳本第一步是先定字符集和存儲引擎。字符集我固定用utf8mb4不要用utf8因為utf8在MySQL里最多存3字節(jié)遇到生僻藥品名或特殊符號會出現亂碼。排序規(guī)則用utf8mb4_unicode_ci它對中文搜索比較友好。存儲引擎選InnoDB這是為了保證事務、外鍵約束和行級鎖可用。MyISAM雖然查詢快一點但不支持事務和外鍵在這個項目里沒有任何優(yōu)勢。數據庫創(chuàng)建語句如下CREATE DATABASE IF NOT EXISTS pharma_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE pharma_db;這里有兩個容易被忽略的點。一是“IF NOT EXISTS”在課設里很實用重新跑腳本不會報錯二是COLLATE如果不下發(fā)后續(xù)建表時字段默認排序規(guī)則各不相同做WHERE查詢比較字符串時容易報“Illegal mix of collations”。建議把所有表、字段統(tǒng)一到同一個COLLATE這是血淚經驗。3.2 藥品表、供應商表、入庫表、銷售表的完整建表語句核心表一共五張再加上兩張輔助表。先建被依賴的基礎表再建主表最后建明細表避免外鍵引用錯誤。下面是藥品表、供應商表和用戶表的示例CREATE TABLE drug_category ( category_id INT AUTO_INCREMENT PRIMARY KEY, category_name VARCHAR(50) NOT NULL UNIQUE, remark VARCHAR(200) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE drug ( drug_id INT AUTO_INCREMENT PRIMARY KEY, drug_code VARCHAR(30) NOT NULL UNIQUE COMMENT 藥品編碼, drug_name VARCHAR(100) NOT NULL, category_id INT NOT NULL, specification VARCHAR(50) COMMENT 規(guī)格, unit VARCHAR(20) DEFAULT 盒, purchase_price DECIMAL(10,2) NOT NULL, sale_price DECIMAL(10,2) NOT NULL, stock_quantity INT NOT NULL DEFAULT 0, expire_date DATE NOT NULL, supplier_id INT COMMENT 默認供應商, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_drug_category FOREIGN KEY (category_id) REFERENCES drug_category(category_id), CONSTRAINT fk_drug_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE supplier ( supplier_id INT AUTO_INCREMENT PRIMARY KEY, supplier_code VARCHAR(20) NOT NULL UNIQUE, supplier_name VARCHAR(100) NOT NULL, contact_person VARCHAR(30), phone VARCHAR(20), address VARCHAR(200) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE sys_user ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password_hash VARCHAR(64) NOT NULL, real_name VARCHAR(30), role VARCHAR(20) NOT NULL DEFAULT cashier COMMENT admin/manager/cashier ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;建表順序很重要先建drug_category再建supplier然后建drug否則drug表外鍵指向的表還不存在。注意drug表里的supplier_id設為“默認供應商”表示一種藥可以對應多個供應商時默認選擇其中一個真正的供應商和藥品的多對多關系要由入庫單明細來體現。price字段用DECIMAL而不是FLOAT后面避坑章節(jié)會專門解釋。接下來是入庫單和入庫明細。主表記錄這次進貨的整體信息明細表記錄每一種藥品進了多少、單價多少CREATE TABLE stock_in ( stock_in_id INT AUTO_INCREMENT PRIMARY KEY, stock_in_no VARCHAR(30) NOT NULL UNIQUE, supplier_id INT NOT NULL, operator_id INT NOT NULL, stock_in_date DATETIME NOT NULL, total_amount DECIMAL(12,2) NOT NULL, remark VARCHAR(200), CONSTRAINT fk_in_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id), CONSTRAINT fk_in_operator FOREIGN KEY (operator_id) REFERENCES sys_user(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE stock_in_detail ( stock_in_id INT NOT NULL, drug_id INT NOT NULL, quantity INT NOT NULL, cost_price DECIMAL(10,2) NOT NULL, production_date DATE, expire_date DATE, PRIMARY KEY (stock_in_id, drug_id), CONSTRAINT fk_detail_in FOREIGN KEY (stock_in_id) REFERENCES stock_in(stock_in_id), CONSTRAINT fk_detail_drug FOREIGN KEY (drug_id) REFERENCES drug(drug_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;銷售單和銷售明細與入庫結構類似但多了一個“處方醫(yī)生”和“客戶姓名”的字段用來貼近醫(yī)藥管理場景CREATE TABLE sale_order ( sale_id INT AUTO_INCREMENT PRIMARY KEY, sale_no VARCHAR(30) NOT NULL UNIQUE, customer_name VARCHAR(50), doctor_name VARCHAR(30), cashier_id INT NOT NULL, sale_date DATETIME NOT NULL, total_amount DECIMAL(12,2) NOT NULL, status VARCHAR(20) DEFAULT completed, CONSTRAINT fk_sale_cashier FOREIGN KEY (cashier_id) REFERENCES sys_user(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; CREATE TABLE sale_order_detail ( sale_id INT NOT NULL, drug_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (sale_id, drug_id), CONSTRAINT fk_detail_sale FOREIGN KEY (sale_id) REFERENCES sale_order(sale_id), CONSTRAINT fk_detail_sale_drug FOREIGN KEY (drug_id) REFERENCES drug(drug_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;銷售明細表里的price字段保存的是“成交時賣的單價”不要直接去關聯藥品表的sale_price因為以后藥品調價了歷史銷售記錄還要保留當時的金額。這個字段叫“快照字段”在庫存類系統(tǒng)里非常常見。入庫明細里的cost_price同理也必須是入庫那一刻的實際成本價。3.3 約束設計主鍵、外鍵、CHECK與默認值怎么配合主鍵設計上所有自增主鍵都用INT如果覺得數據量會很大可以換成BIGINT但課設階段沒必要。唯一鍵用在業(yè)務單號上比如stock_in_no、sale_no用UNIQUE約束確保不會重復生成。外鍵約束必須有這是課設評分能直觀看到的數據庫知識點但不要在明細表上對明細記錄建“無用的級聯刪除”。正確的是主單刪除時明細應該同時刪除所以外鍵用ON DELETE CASCADE藥品和分類之間分類不能刪要限制所以用ON DELETE RESTRICT。CHECK約束很多同學會寫比如庫存不能為負、價格大于0但MySQL 8.0.16之前的版本不強制執(zhí)行CHECK只能在應用層校驗。我在建表腳本里仍然寫CHECK因為報告里可以寫“數據庫層面也有約束”同時應用層再攔一道。默認值盡量給全創(chuàng)建時間用DEFAULT CURRENT_TIMESTAMP銷售狀態(tài)用DEFAULT completed數量用DEFAULT 0。這樣INSERT語句可以少寫很多字段而且不會因為漏填導致空指針。3.4 初始化數據與視圖讓演示狀態(tài)更合理建完表還要造一批演示數據不能用網上的“學生表”隨便填。我一般造5個藥品分類、20個藥品、5個供應商、3個用戶再造3張入庫單和若干銷售單。一個重要技巧是讓部分藥品庫存低于“預警線”比如10盒部分藥品的過期時間在3個月內這樣后面的“有效期預警”和“庫存不足查詢”視圖就有數據可演示。INSERT語句我就不全貼了給個示例INSERT INTO drug_category (category_name) VALUES (心腦血管類), (消化系統(tǒng)類), (呼吸系統(tǒng)類); INSERT INTO supplier (supplier_code, supplier_name) VALUES (SP001, 華東醫(yī)藥), (SP002, 華北制藥); INSERT INTO sys_user (username, password_hash, real_name, role) VALUES (admin, hashed_value, 管理員, admin), (cashier01, hashed_value, 張收銀, cashier); INSERT INTO drug (drug_code, drug_name, category_id, unit, purchase_price, sale_price, stock_quantity, expire_date) VALUES (DRUG001, 阿司匹林腸溶片, 1, 盒, 8.50, 15.00, 100, 2026-05-30), (DRUG002, 奧美拉唑膠囊, 2, 盒, 12.00, 20.00, 8, 2026-01-15);這里故意把奧美拉唑的庫存設為8低于預警線演示時直接跑查詢就能出效果。視圖建議建兩個一個是“庫存預警_含分類名”一個是“銷售統(tǒng)計_按日按月”。視圖的好處是答辯時不用現場敲很長的SQL讓評委看視圖定義更直觀CREATE VIEW low_stock_view AS SELECT d.drug_id, d.drug_name, d.stock_quantity, d.expire_date FROM drug d WHERE d.stock_quantity 10 OR d.expire_date DATE_ADD(CURDATE(), INTERVAL 90 DAY);4. 核心業(yè)務SQL寫法增刪改查之外的加分項4.1 庫存扣減與流水記錄一個銷售事務怎么寫醫(yī)藥信息管理系統(tǒng)的核心不是“藥品增刪改查”而是“銷售完成后銷售單、明細、庫存、臺賬要同時更新”。建議把這個過程寫成一個存儲過程或者事務模板答辯時展示數據庫并發(fā)鎖和事務的一致性控制。下面是我常用的銷售開單事務寫法START TRANSACTION; -- 1. 插入銷售主單 INSERT INTO sale_order (sale_no, customer_name, doctor_name, cashier_id, sale_date, total_amount) VALUES (SO20250601001, 張三, 王醫(yī)生, 2, NOW(), 0); SET sale_id LAST_INSERT_ID(); -- 2. 插入銷售明細同時計算總金額 INSERT INTO sale_order_detail (sale_id, drug_id, quantity, price, amount) SELECT sale_id, d.drug_id, 2, d.sale_price, d.sale_price * 2 FROM drug d WHERE d.drug_code DRUG001; -- 3. 更新藥品庫存并且強制要求庫存不能為負 UPDATE drug SET stock_quantity stock_quantity - 2 WHERE drug_id 1 AND stock_quantity 2; -- 4. 檢查是否有行被更新如果沒有表示庫存不足 SELECT ROW_COUNT() AS affected_rows; -- 5. 如果affected_rows為0則回滾 -- 注意在存儲過程中可以用IF判斷這里僅演示SQL順序 COMMIT;這個事務的寫法和純增刪改查的區(qū)別在第三步UPDATE語句里帶了AND stock_quantity 2這在MySQL里是“條件更新”相當于數據庫層幫你做了一次庫存校驗。執(zhí)行UPDATE后如果影響行數為0說明庫存不夠就應該回滾。更規(guī)范的寫法是用SELECT ... FOR UPDATE給藥品行加鎖再判斷庫存量但因為課設單機環(huán)境下并發(fā)不高用條件更新已經足夠。這種寫法能避免“庫存被扣成負數”的經典錯誤。4.2 藥品有效期預警和庫存下限查詢用視圖與條件查詢實現預警功能很出效果而且只要一條SQL。除了前面建的低庫存視圖還需要按有效期排序的查詢。MySQL的DATEDIFF函數可以直接算出過期距離天數SELECT drug_name, expire_date, DATEDIFF(expire_date, CURDATE()) AS remain_days FROM drug WHERE DATEDIFF(expire_date, CURDATE()) 90 ORDER BY remain_days ASC;這條查詢放在“庫存管理”頁面按照剩余天數從少到多排序就是很直觀的效期提醒。如果你想升級成“每批藥品獨立效期管理”就要在入庫明細里增加生產日期和效期字段出庫時按照“先入先出”的原則選擇最早批次的庫存。課設里掛在藥品表上的expire_date一個字段就夠演示但你在報告里如果能寫清楚“藥品表上的效期是最新批次的效期更嚴格的做法是把效期放明細”老師會覺得你想過這個問題。4.3 存儲過程與觸發(fā)器課程設計的加分項很多學校的數據庫課設評分點里有“存儲過程”和“觸發(fā)器”這兩個數據庫對象。存儲過程建議封裝兩個一個是“藥品采購入庫”在insert入庫單和明細后循環(huán)更新藥品的庫存和最近入庫價另一個是“按時間范圍統(tǒng)計銷售排名”用GROUP BY生成報表。下面是一個簡單入庫存儲過程的模板DELIMITER $$ CREATE PROCEDURE sp_stock_in( IN p_supplier_id INT, IN p_operator_id INT, IN p_drug_code VARCHAR(30), IN p_quantity INT, IN p_cost_price DECIMAL(10,2) ) BEGIN DECLARE v_drug_id INT; DECLARE v_stock_in_id INT; SELECT drug_id INTO v_drug_id FROM drug WHERE drug_code p_drug_code; INSERT INTO stock_in (stock_in_no, supplier_id, operator_id, stock_in_date, total_amount) VALUES (CONCAT(SI, DATE_FORMAT(NOW(), %Y%m%d%H%i%s)), p_supplier_id, p_operator_id, NOW(), p_quantity * p_cost_price); SET v_stock_in_id LAST_INSERT_ID(); INSERT INTO stock_in_detail (stock_in_id, drug_id, quantity, cost_price, expire_date) VALUES (v_stock_in_id, v_drug_id, p_quantity, p_cost_price, DATE_ADD(NOW(), INTERVAL 2 YEAR)); UPDATE drug SET stock_quantity stock_quantity p_quantity, purchase_price p_cost_price WHERE drug_id v_drug_id; END$$ DELIMITER ;注意DELIMITER的使用在命令行和Navicat里存儲過程體中多個分號會被MySQL當成語句結束DELIMITER $$就是為了臨時把終止符改成$$避免過程體內插值報錯。參數名前面的p_后綴是為了避免和列名沖突這算是個小習慣。觸發(fā)器可以作為“庫存不足自動提醒”的補充。比如在銷售明細表上做一個AFTER INSERT觸發(fā)器自動更新藥品表的庫存觸發(fā)器代碼不長但它會隱藏業(yè)務邏輯讓日后排錯變難。我的建議是課設里寫一個觸發(fā)器證明你會用就行核心業(yè)務邏輯放存儲過程或應用層不要把大量規(guī)則都壓進觸發(fā)器。觸發(fā)器一旦出錯SQL很難調試而且觸發(fā)器里的SELECT不能往臨時表以外的地方返回結果容易被繞暈。4.4 權限控制不同角色看到不同的菜單和記錄數據庫層的權限控制要做到基于角色的訪問控制。我的做法是在sys_user表里加role字段應用層根據角色選擇查詢語句。比如管理員可以看到所有用戶的銷售記錄收銀員只能看到自己的。SQL層面可以用WHERE條件拼角色入參-- 應用層傳遞當前用戶的 role 和 user_id SELECT so.sale_no, so.sale_date, so.total_amount, u.real_name AS cashier FROM sale_order so JOIN sys_user u ON so.cashier_id u.user_id WHERE ( role admin OR so.cashier_id user_id ) ORDER BY so.sale_date DESC;這個SP里用變量role和user_id表示從應用層傳入的會話變量。數據庫課上講視圖時也可以給一個“我的銷售記錄”視圖視圖定義里帶上DATABASE用戶函數但MySQL里做行級安全不太方便所以我在視圖和存儲過程之間選擇了存儲過程作為主力。數據庫連接池場景下每個請求建立連接后要執(zhí)行SET role ?才能保證不同人的數據隔離。5. 課設避坑數據庫設計階段的五個高頻問題5.1 藥品和供應商關系做成單表導致供應商覆蓋現象藥品表里有一個supplier_name字段一次給同一個藥品維護了兩個供應商后來發(fā)現只能存一個另一個被覆蓋了。原因把多對多關系硬塞進基礎檔案表藥品表根本沒法表達“同一藥品由多個供應商供貨”。解決拆中間表或依賴入庫明細表讓每次進貨同時記錄“供應商藥品批次”這樣既能查某個藥品的歷史供應商又能在報表里按供應商聚合采購金額。課設報告里明確寫出“供應商和藥品是多對多關系通過入庫單明細表實現”這一句話就能證明你懂數據庫關系。5.2 金額用FLOAT存儲對賬差了幾分錢現象錄入采購金額后列表頁顯示108.60導出的報表卻是108.599999。原因FLOAT和DOUBLE是浮點數二進制不能精確表示十進制小數拿來做金額會產生舍入誤差。解決所有金額字段一律改成DECIMAL(10,2)或DECIMAL(12,2)。DECIMAL是按數字存儲的加減乘除都精確。這個坑在醫(yī)藥系統(tǒng)里很致命因為進銷存對賬差一分都會引起麻煩。如果已經建錯了表可以用ALTER TABLE來改字段類型但要暴露給所有相關表和存儲過程。5.3 銷售后沒有流水記錄庫存和報表對不上現象演示時先做一筆銷售然后打開庫存查詢發(fā)現庫存沒變。原因只往sale_order表插了一條記錄沒有同步更新drug表的stock_quantity也沒有在sale_order_detail里記錄明細。解決把銷售流程收斂到同一個事務里或者在應用層用一個Service方法把“插入主表”“插入明細”“更新庫存”三步包在Transactional里。數據庫課設推薦用存儲過程因為課堂環(huán)境里前端語言可能沒法演示事務存儲過程能直接把事務行為展示在數據庫客戶端。別忘了最后再查一次庫存把扣減后的值顯示在界面上。5.4 外鍵濫用導致插入失敗考場手忙腳亂現象在明細表里插入數據時報“Cannot add or update a child row”然后整個銷售流程中斷。原因外鍵約束要求外鍵值必須在主表中存在。常見情況是先在明細分錄里引用了sale_id但sale_order還沒來得及提交自動生成的ID。解決先INSERT主表用LAST_INSERT_ID()拿到新ID再插入明細表。另一個典型錯誤是給“藥品分類”表外鍵加了ON DELETE CASCADE導致刪除一個分類時把所有藥品全刪了。分類屬于“被引用數據”應該用RESTRICT禁止級聯刪除只有明細表才算“依賴數據”才適合用CASCADE。5.5 并發(fā)銷售時庫存變成負數現象兩個窗口同時賣最后一件藥兩個窗口都顯示庫存為1也都扣減成功最后庫存變成-1。原因先查庫存再更新庫存是“非原子操作”兩個會話在“查詢”階段讀到相同值后面各自做了減一導致數據庫并發(fā)鎖沒有真正保住庫存。解決把“判斷庫存大于0”和“扣減庫存”合并到一條UPDATE語句里也就是前面寫的UPDATE ... WHERE stock_quantity n。InnoDB默認行級鎖這條UPDATE會鎖住該藥品行第二個會話必須等第一個提交或回滾才能繼續(xù)這樣就避免了負庫存。如果有意演示悲觀鎖可以用SELECT stock_quantity FROM drug WHERE drug_id ? FOR UPDATE但注意事務結束后要立即提交否則鎖會一直占著這就是數據庫死鎖的高發(fā)場景。6. 把數據量做大用索引和執(zhí)行計劃給系統(tǒng)加分課設答辯到后期老師通常會問“如果這張表有幾十萬條記錄你的查詢還會快嗎”。這個問題別空談索引直接在MySQL里做一做往sale_order_detail表里用存儲過程灌入10萬條模擬數據再跑兩條等價的統(tǒng)計SQL對比執(zhí)行計劃里的掃描行數。我給一個可跑的模擬數據生成腳本DROP PROCEDURE IF EXISTS sp_generate_sale_data; DELIMITER $$ CREATE PROCEDURE sp_generate_sale_data(IN p_loop_count INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE v_sale_id INT; SET AUTOCOMMIT0; WHILE i p_loop_count DO INSERT INTO sale_order (sale_no, customer_name, cashier_id, sale_date, total_amount) VALUES (CONCAT(BIG, DATE_FORMAT(NOW(), %Y%m%d%H%i%s), LPAD(i, 6, 0)), 批量客戶, 1, NOW(), 0); SET v_sale_id LAST_INSERT_ID(); INSERT INTO sale_order_detail (sale_id, drug_id, quantity, price, amount) VALUES (v_sale_id, FLOOR(1 RAND() * 20), FLOOR(1 RAND() * 5), 10.00, 0); SET i i 1; END WHILE; COMMIT; END$$ DELIMITER ; CALL sp_generate_sale_data(100000);跑完后再執(zhí)行EXPLAINEXPLAIN SELECT so.sale_date, so.total_amount FROM sale_order so WHERE so.sale_date BETWEEN 2025-01-01 AND 2025-12-31;在沒有索引時type列通常是ALL行數是全表統(tǒng)計隨后你在sale_date上建立索引再跑一遍EXPLAINtype會變成range。這就是一個很有說服力的現場調優(yōu)演示。接著做一個更貼近業(yè)務的口徑統(tǒng)計每一種藥賣了多少錢。SELECT d.drug_name, SUM(sod.amount) AS total_sale_amount FROM sale_order_detail sod JOIN drug d ON sod.drug_id d.drug_id GROUP BY d.drug_id, d.drug_name ORDER BY total_sale_amount DESC LIMIT 10;這條SQL在數據量上去以后要保證drug_id是主鍵、sale_order_detail的drug_id上有索引不然JOIN慢。這也是“數據庫同步工具”概念里經常提到的索引問題如果兩張表關聯字段忘了建索引數據同步和實時查詢都會卡住。我的個人習慣是給每條核心查詢寫“優(yōu)化前”和“優(yōu)化后”兩版并把EXPLAIN結果截圖貼進課設報告。老師其實很清楚學生做的數據量不大他更在意你有沒有驗證的意識和排查手段。你只要做一次并不復雜的索引對比就能和其他交了個增刪改查的課設拉開差距。最后再說一句實在話數據庫課設的分數不是靠堆功能堆出來的而是靠表結構合理、事務不丟數據、查詢有據可查這三個維度撐起來的。醫(yī)藥信息管理系統(tǒng)是個很好的載體把進銷存和中小型業(yè)務數據庫的常見問題都覆蓋了值得你多花兩個晚上把細節(jié)打磨好。希望這篇筆記能幫你在做課設和答辯的路上少踩幾個坑。本文還有配套的精品資源點擊獲取