SQL Server 2022 医院管理系统数据库实战从设计到查询的完整指南1. 医院数据库系统概述在现代医疗信息化建设中数据库系统扮演着核心角色。一个设计良好的医院管理系统数据库能够高效管理患者信息、医疗资源、医护人员数据等关键业务要素。SQL Server 2022作为微软最新的数据库管理系统提供了强大的数据管理能力和性能优化特性非常适合用于构建医院信息管理系统。本实战指南将带您从零开始构建一个完整的医院管理系统数据库包含以下核心内容6张核心数据表的设计与实现完整的数据库创建脚本15个典型业务查询示例视图、存储过程和触发器的实战应用适用对象计算机相关专业学生数据库课程设计需求者SQL Server初学者医疗信息化系统开发者2. 数据库设计与表结构2.1 数据库创建脚本-- 创建医院信息管理系统数据库 CREATE DATABASE 医院信息管理系统 ON PRIMARY ( NAME 医院信息管理系统_data, FILENAME C:\Data\医院信息管理系统.mdf, SIZE 100MB, FILEGROWTH 20MB ) LOG ON ( NAME 医院信息管理系统_log, FILENAME C:\Data\医院信息管理系统.ldf, SIZE 50MB, FILEGROWTH 10MB ); GO USE 医院信息管理系统; GO2.2 核心表结构设计医院管理系统通常包含以下6张核心表科室表存储医院各部门信息医生表记录医生详细信息病人表管理患者就诊信息病房表记录病房资源情况护士表存储护士人员信息分配表管理护士与病房的分配关系2.2.1 科室表设计CREATE TABLE 科室表 ( 科室号 CHAR(3) NOT NULL PRIMARY KEY, 科室名 VARCHAR(20) NOT NULL, 科室地址 VARCHAR(30), 科室电话 VARCHAR(15), 科室主任 VARCHAR(10) ); GO2.2.2 医生表设计CREATE TABLE 医生表 ( 医生编号 CHAR(6) NOT NULL PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(2) CHECK (性别 IN (男,女)), 年龄 INT CHECK (年龄 BETWEEN 20 AND 70), 职称 VARCHAR(20), 科室号 CHAR(3) REFERENCES 科室表(科室号) ); GO -- 设置职称默认值 ALTER TABLE 医生表 ADD CONSTRAINT df_职称 DEFAULT 医师 FOR 职称; GO2.2.3 病房表设计CREATE TABLE 病房表 ( 病房号 CHAR(3) NOT NULL PRIMARY KEY, 床位数 INT DEFAULT 0, 病房地址 VARCHAR(30), 科室号 CHAR(3) REFERENCES 科室表(科室号) ); GO2.2.4 病人表设计CREATE TABLE 病人表 ( 病历号 CHAR(5) NOT NULL PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(2) CHECK (性别 IN (男,女)), 年龄 INT CHECK (年龄 0), 病房号 CHAR(3) REFERENCES 病房表(病房号), 医生姓名 VARCHAR(20), 血型 CHAR(2) CHECK (血型 IN (A,B,AB,O)), 确诊结果 VARCHAR(50), 科室号 CHAR(3) REFERENCES 科室表(科室号) ); GO2.2.5 护士表设计CREATE TABLE 护士表 ( 护士编号 CHAR(6) NOT NULL PRIMARY KEY, 姓名 VARCHAR(20) NOT NULL, 性别 CHAR(2) CHECK (性别 IN (男,女)), 年龄 INT CHECK (年龄 BETWEEN 18 AND 65), 科室号 CHAR(3) REFERENCES 科室表(科室号) ); GO2.2.6 分配表设计CREATE TABLE 分配表 ( 病房号 CHAR(3) NOT NULL REFERENCES 病房表(病房号), 护士编号 CHAR(6) NOT NULL REFERENCES 护士表(护士编号), PRIMARY KEY (病房号, 护士编号) ); GO3. 数据初始化与示例数据3.1 科室数据插入INSERT INTO 科室表 VALUES (101,内科,A栋1楼101室,1001,张明), (102,外科,A栋1楼102室,1002,李华), (103,儿科,A栋1楼103室,1003,王芳), (104,妇产科,A栋1楼104室,1004,刘丽), (105,眼科,A栋1楼105室,1005,陈强); GO3.2 医生数据插入INSERT INTO 医生表 VALUES (10101,张明,男,45,主任医师,101), (10102,李伟,男,38,副主任医师,101), (10201,李华,男,50,主任医师,102), (10202,王强,男,32,主治医师,102), (10301,王芳,女,40,副主任医师,103), (10401,刘丽,女,35,主治医师,104), (10501,陈强,男,48,主任医师,105); GO3.3 病房数据插入INSERT INTO 病房表 VALUES (101,4,B栋1楼101室,101), (102,3,B栋1楼102室,101), (201,2,B栋2楼201室,102), (301,5,B栋3楼301室,103), (401,3,B栋4楼401室,104), (501,2,B栋5楼501室,105); GO3.4 病人数据插入INSERT INTO 病人表 VALUES (10001,张三,男,35,101,张明,A,高血压,101), (10002,李四,男,28,201,李华,B,阑尾炎,102), (10003,王五,女,42,301,王芳,O,肺炎,103), (10004,赵六,女,25,401,刘丽,AB,妇科检查,104), (10005,钱七,男,60,501,陈强,A,白内障,105); GO3.5 护士数据插入INSERT INTO 护士表 VALUES (1101,周梅,女,25,101), (1102,吴兰,女,28,101), (1201,郑竹,女,30,102), (1301,王菊,女,26,103), (1401,冯兰,女,29,104), (1501,陈菊,女,27,105); GO3.6 分配数据插入INSERT INTO 分配表 VALUES (101,1101), (101,1102), (201,1201), (301,1301), (401,1401), (501,1501); GO4. 核心业务查询实战4.1 基础查询4.1.1 查询所有科室信息SELECT * FROM 科室表;4.1.2 查询医生及其所属科室SELECT d.姓名, d.职称, d.年龄, d.性别, k.科室名 FROM 医生表 d JOIN 科室表 k ON d.科室号 k.科室号;4.1.3 查询病人及其主治医生信息SELECT p.病历号, p.姓名, p.性别, p.年龄, p.确诊结果, d.姓名 AS 主治医生, d.职称 FROM 病人表 p JOIN 医生表 d ON p.医生姓名 d.姓名;4.2 统计查询4.2.1 各科室医生人数统计SELECT k.科室名, COUNT(d.医生编号) AS 医生人数 FROM 科室表 k LEFT JOIN 医生表 d ON k.科室号 d.科室号 GROUP BY k.科室名;4.2.2 各科室病房数量统计SELECT k.科室名, COUNT(b.病房号) AS 病房数量 FROM 科室表 k LEFT JOIN 病房表 b ON k.科室号 b.科室号 GROUP BY k.科室名;4.2.3 各科室病人数量统计SELECT k.科室名, COUNT(p.病历号) AS 病人数量 FROM 科室表 k LEFT JOIN 病人表 p ON k.科室号 p.科室号 GROUP BY k.科室名;4.3 复杂查询4.3.1 查询护士负责的病房及病人信息SELECT n.护士编号, n.姓名 AS 护士姓名, b.病房号, b.病房地址, p.病历号, p.姓名 AS 病人姓名, p.确诊结果 FROM 护士表 n JOIN 分配表 a ON n.护士编号 a.护士编号 JOIN 病房表 b ON a.病房号 b.病房号 LEFT JOIN 病人表 p ON b.病房号 p.病房号;4.3.2 查询各科室平均住院年龄SELECT k.科室名, AVG(p.年龄) AS 平均住院年龄 FROM 科室表 k JOIN 病人表 p ON k.科室号 p.科室号 GROUP BY k.科室名;4.3.3 查询床位使用率SELECT b.病房号, b.床位数, COUNT(p.病历号) AS 当前病人数, CAST(COUNT(p.病历号) AS FLOAT)/b.床位数 * 100 AS 使用率 FROM 病房表 b LEFT JOIN 病人表 p ON b.病房号 p.病房号 GROUP BY b.病房号, b.床位数;4.4 视图应用4.4.1 创建病人详细信息视图CREATE VIEW v_PatientDetail AS SELECT p.病历号, p.姓名 AS 病人姓名, p.性别, p.年龄, p.血型, p.确诊结果, b.病房号, b.病房地址, d.姓名 AS 主治医生, d.职称 AS 医生职称, k.科室名, k.科室地址 FROM 病人表 p JOIN 病房表 b ON p.病房号 b.病房号 JOIN 医生表 d ON p.医生姓名 d.姓名 JOIN 科室表 k ON p.科室号 k.科室号; GO -- 使用视图查询 SELECT * FROM v_PatientDetail;4.4.2 创建科室资源统计视图CREATE VIEW v_DepartmentStats AS SELECT k.科室号, k.科室名, COUNT(DISTINCT d.医生编号) AS 医生人数, COUNT(DISTINCT n.护士编号) AS 护士人数, COUNT(DISTINCT b.病房号) AS 病房数量, SUM(b.床位数) AS 总床位数, COUNT(p.病历号) AS 当前病人数 FROM 科室表 k LEFT JOIN 医生表 d ON k.科室号 d.科室号 LEFT JOIN 护士表 n ON k.科室号 n.科室号 LEFT JOIN 病房表 b ON k.科室号 b.科室号 LEFT JOIN 病人表 p ON b.病房号 p.病房号 GROUP BY k.科室号, k.科室名; GO -- 使用视图查询 SELECT * FROM v_DepartmentStats;4.5 存储过程应用4.5.1 按科室查询病人信息CREATE PROCEDURE sp_GetPatientsByDepartment DeptID CHAR(3) AS BEGIN SELECT p.病历号, p.姓名, p.性别, p.年龄, p.确诊结果, d.姓名 AS 主治医生, b.病房号 FROM 病人表 p JOIN 医生表 d ON p.医生姓名 d.姓名 JOIN 病房表 b ON p.病房号 b.病房号 WHERE p.科室号 DeptID; END; GO -- 执行存储过程 EXEC sp_GetPatientsByDepartment 101;4.5.2 添加新病人记录CREATE PROCEDURE sp_AddPatient PatientID CHAR(5), Name VARCHAR(20), Gender CHAR(2), Age INT, RoomNo CHAR(3), DoctorName VARCHAR(20), BloodType CHAR(2), Diagnosis VARCHAR(50), DeptID CHAR(3) AS BEGIN -- 检查病房是否存在 IF NOT EXISTS (SELECT 1 FROM 病房表 WHERE 病房号 RoomNo) BEGIN RAISERROR(病房号不存在, 16, 1); RETURN; END -- 检查医生是否存在 IF NOT EXISTS (SELECT 1 FROM 医生表 WHERE 姓名 DoctorName) BEGIN RAISERROR(医生姓名不存在, 16, 1); RETURN; END -- 检查科室是否存在 IF NOT EXISTS (SELECT 1 FROM 科室表 WHERE 科室号 DeptID) BEGIN RAISERROR(科室号不存在, 16, 1); RETURN; END -- 插入新记录 INSERT INTO 病人表 VALUES (PatientID, Name, Gender, Age, RoomNo, DoctorName, BloodType, Diagnosis, DeptID); -- 更新病房床位数 UPDATE 病房表 SET 床位数 床位数 - 1 WHERE 病房号 RoomNo; PRINT 病人记录添加成功; END; GO -- 执行存储过程添加新病人 EXEC sp_AddPatient 10006, 孙八, 男, 40, 101, 张明, B, 糖尿病, 101;4.6 触发器应用4.6.1 病人出院自动更新床位数CREATE TRIGGER tr_PatientDischarge ON 病人表 AFTER DELETE AS BEGIN -- 更新病房床位数 UPDATE b SET b.床位数 b.床位数 1 FROM 病房表 b JOIN deleted d ON b.病房号 d.病房号; PRINT 病人出院处理完成床位已释放; END; GO -- 测试触发器 DELETE FROM 病人表 WHERE 病历号 10001;4.6.2 防止病房超员CREATE TRIGGER tr_PreventOvercrowding ON 病人表 INSTEAD OF INSERT AS BEGIN -- 检查每个插入记录的病房是否还有空床 IF EXISTS ( SELECT 1 FROM inserted i JOIN 病房表 b ON i.病房号 b.病房号 WHERE b.床位数 ( SELECT COUNT(*) FROM 病人表 p WHERE p.病房号 i.病房号 ) ) BEGIN RAISERROR(病房已满无法接收更多病人, 16, 1); RETURN; END -- 如果没有超员则执行插入 INSERT INTO 病人表 SELECT * FROM inserted; -- 更新病房床位数 UPDATE b SET b.床位数 b.床位数 - 1 FROM 病房表 b JOIN inserted i ON b.病房号 i.病房号; END; GO -- 测试触发器 INSERT INTO 病人表 VALUES (10007, 测试病人, 男, 30, 101, 张明, O, 测试, 101);5. 数据库维护与优化5.1 索引优化建议为提高查询性能建议在以下字段上创建索引-- 在病人表的病房号和科室号上创建索引 CREATE INDEX idx_Patient_Room ON 病人表(病房号); CREATE INDEX idx_Patient_Dept ON 病人表(科室号); -- 在医生表的科室号上创建索引 CREATE INDEX idx_Doctor_Dept ON 医生表(科室号); -- 在病房表的科室号上创建索引 CREATE INDEX idx_Room_Dept ON 病房表(科室号);5.2 定期维护脚本-- 数据库备份 BACKUP DATABASE 医院信息管理系统 TO DISK C:\Backups\医院信息管理系统.bak WITH INIT, NAME 医院信息管理系统-完整备份, DESCRIPTION 医院数据库定期完整备份; -- 更新统计信息 UPDATE STATISTICS 病人表; UPDATE STATISTICS 医生表; UPDATE STATISTICS 病房表;5.3 性能监控查询-- 查询执行最频繁的SQL语句 SELECT TOP 10 qs.execution_count, qs.total_logical_reads/qs.execution_count AS avg_logical_reads, qs.total_elapsed_time/qs.execution_count AS avg_elapsed_time, SUBSTRING(qt.text, (qs.statement_start_offset/2)1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)1) AS query_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.execution_count DESC;6. 实际应用案例6.1 疫情时期病床调度-- 创建临时表存储疫情高风险病人 CREATE TABLE #HighRiskPatients ( 病历号 CHAR(5) PRIMARY KEY, 姓名 VARCHAR(20), 确诊结果 VARCHAR(50), 病房号 CHAR(3) ); -- 识别高风险病人并隔离 INSERT INTO #HighRiskPatients SELECT 病历号, 姓名, 确诊结果, 病房号 FROM 病人表 WHERE 确诊结果 IN (新冠肺炎,疑似新冠肺炎); -- 为高风险病人分配隔离病房 UPDATE p SET p.病房号 999 -- 假设999是隔离病房号 FROM 病人表 p JOIN #HighRiskPatients h ON p.病历号 h.病历号 WHERE EXISTS (SELECT 1 FROM 病房表 WHERE 病房号 999); -- 清理临时表 DROP TABLE #HighRiskPatients;6.2 医护人员排班管理-- 创建排班表 CREATE TABLE 排班表 ( 排班ID INT IDENTITY(1,1) PRIMARY KEY, 员工编号 CHAR(6) NOT NULL, 员工类型 CHAR(1) CHECK (员工类型 IN (D,N)), -- D:医生, N:护士 班次日期 DATE NOT NULL, 班次类型 CHAR(1) CHECK (班次类型 IN (A,B,C)), -- A:早班, B:午班, C:晚班 FOREIGN KEY (员工编号) REFERENCES 医生表(医生编号), FOREIGN KEY (员工编号) REFERENCES 护士表(护士编号) ); -- 生成下周排班 INSERT INTO 排班表 (员工编号, 员工类型, 班次日期, 班次类型) SELECT 医生编号, D, DATEADD(DAY, number, GETDATE()) AS 班次日期, CASE WHEN number % 3 0 THEN A WHEN number % 3 1 THEN B ELSE C END AS 班次类型 FROM 医生表 CROSS JOIN master.dbo.spt_values WHERE type P AND number BETWEEN 1 AND 7; -- 查询某天各科室值班医生 SELECT k.科室名, d.姓名, s.班次类型 FROM 排班表 s JOIN 医生表 d ON s.员工编号 d.医生编号 JOIN 科室表 k ON d.科室号 k.科室号 WHERE s.班次日期 CAST(GETDATE() AS DATE) AND s.员工类型 D ORDER BY k.科室名, s.班次类型;
