数据库课设项目(上) 医院


任务


数据库课程有小组共同完成的大作业,我们小组抽取到的题目是关于疫情背景下的患者就医问题。在小组成员完成主体的设计后,我尝试运用学过的SQL语句实现基本的需求。
上篇实现医院信息的导入,下篇实现(虚拟)医护人员和患者数据的导入和相关操作。
最终会将完整项目代码上传至GitHub仓库

需求分析

  1. 导入地区数据
    1. https://github.com/kbdxbt/area_sql
  2. 注册医院
    1. 创建触发器生成默认科室
  1. 添加医护人员信息
  2. 患者就医(通过存储过程实现)
    1. 注册
    2. 充值
    3. 消费
    4. 注销
  • 其他更新:

根据疫情情况,可创建

  1. 医疗队
  2. 临时增援医护人员

我们可以建立一个个人信息表,将医生与患者的个人信息集中放置(医生也有做为患者的可能)

Resident

ID NAME XXX

模拟实际就医体验,患者可在多个医院就诊注册

  • 注册时产生医院唯一卡号

Patient

ID 卡号 注册时间 医院ID
  • 充值和消费以流水记录

流水

医院ID 卡号 金额 时间 柜台(充值处/科室)
  • 注销:删除注册记录,但流水依然会存在
    • 问题:如果患者选择了注销,医院是否保留患者的注册信息?

过程


CREATE DATABASE


数据库名称为medicalSystem

DECLARE @databaseName varchar(20);
 DECLARE @createSql varchar(40);
 set @databaseName = 'medicalSystem'
 if exists (SELECT * FROM sys.databases
    WHERE name = @databaseName)
    print 'database '+@databaseName+' already exists';
 else
 BEGIN
  set @createSql='CREATE DATABASE '+@databaseName
  exec(@createSql);
  print @createSql;
 END


导入地区数据


通过在GitHub上搜索,直接有SQL语句构建地区各种数据(邮编,经纬度等)现有代码。这里选用的是这一项目:https://github.com/kbdxbt/area_sql
语句是MYSQL的格式,在SQL SERVER中有诸多报错,因此有部分格式需要转化。


转义字符

` 是 MySQL 的转义符,避免和 mysql 的本身的关键字冲突,只要你不在列名、表名中使用 mysql 的保留字或中文,就不需要转义。

而SQL SERVER中是不支持这个字符的,通过替换,将所有的 ` 改为 “

同理,还需要把 ’ 改为 ‘’
此时基本错误消除完毕,剩下的是建表中不一致的语句

CREATE TABLE

  • 将COMMENT注释掉
  • AUTO_INCREMENT对应SQL SERVER中的IDENTITY(为了插入数据方便,我去掉了这个规则)
  • 注释掉建表最后的要求–ENGINE=InnoDB AUTO_INCREMENT=3750 DEFAULT CHARSET=utf8;
CREATE TABLE "hy_area" (
   "id" int NOT NULL ,-- COMMENT 'ID',--AUTO_INCREMENT
   "pid" int DEFAULT NULL ,--COMMENT '父id',
   "shortname" varchar(100) DEFAULT NULL ,--COMMENT '简称',
   "name" varchar(100) DEFAULT NULL ,--COMMENT '名称',
   "merger_name" varchar(255) DEFAULT NULL ,--COMMENT '全称',
   "level" tinyint DEFAULT NULL ,--COMMENT '层级 0 1 2 省市区县',
   "pinyin" varchar(100) DEFAULT NULL ,--COMMENT '拼音',
   "code" varchar(100) DEFAULT NULL ,--COMMENT '长途区号',
   "zip_code" varchar(100) DEFAULT NULL ,--COMMENT '邮编',
   "first" varchar(50) DEFAULT NULL ,--COMMENT '首字母',
   "lng" varchar(100) DEFAULT NULL ,--COMMENT '经度',
   "lat" varchar(100) DEFAULT NULL ,--COMMENT '纬度',
   PRIMARY KEY ("id")
 ) --ENGINE=InnoDB AUTO_INCREMENT=3750 DEFAULT CHARSET=utf8;
 INSERT INTO "hy_area" VALUES ('1', '0', '北京', '北京', '中国,北京', '1', 'beijing', '', '', 'B', '116.405285', '39.904989');
 INSERT INTO "hy_area" VALUES ('2', '1', '北京', '北京市', '中国,北京,北京市', '2', 'beijing', '010', '100000', 'B', '116.405285', '39.904989');
 INSERT INTO "hy_area" VALUES ('3', '2', '东城', '东城区', '中国,北京,北京市,东城区', '3', 'dongcheng', '010', '100010', 'D', '116.41005', '39.93157');
 INSERT INTO "hy_area" VALUES ('4', '2', '西城', '西城区', '中国,北京,北京市,西城区', '3', 'xicheng', '010', '100032', 'X', '116.36003', '39.9305');
 INSERT INTO "hy_area" VALUES ('5', '2', '朝阳', '朝阳区', '中国,北京,北京市,朝阳区', '3', 'chaoyang', '010', '100020', 'C', '116.48548', '39.9484');


成功导入

在这里插入图片描述

注册医院


在GitHub中有通过爬虫获取全国医院数据的项目,但我暂时目标是熟悉SQL语句,便只从网页中复制了少量医院数据做为测试。


CREATE TABLE

 CREATE TABLE [dbo].[Hospital](
  医院名称 nvarchar(40) NOT NULL UNIQUE,
  医院等级 nvarchar(4) NULL,
 医院类型 nvarchar(4) NULL,
 省 nvarchar(10) NULL,
 市 nvarchar(10) NULL,
 县 nvarchar(20) NULL,
 床位数 int NULL,
 医院地址 nvarchar(60) NULL,
 邮编 varchar(100) NULL,
 constraint pk_Hospital PRIMARY KEY
    NONCLUSTERED(医院名称)
 )
 INSERT INTO Hospital( 医院名称,医院等级,医院类型,省,市,县,床位数, 医院地址) VALUES
 ('河北大学附属医院','三级甲等','综合医院','河北省','保定市','莲池区',2300,'保定市莲池区裕华东路212号'),
 ('保定市第一中心医院','三级甲等','综合医院','河北省','保定市','莲池区',2100,'保定市莲池区长城北大街320号'),
 ('沧州市人民医院','三级甲等','综合医院','河北省','沧州市','新华区',3000,'沧州市清池大道7号'),
 ('沧州市中心医院','三级甲等','综合医院','河北省','沧州市','新华区', 4700,'沧州市新华中路201号'),
 ('承德医学院附属医院','三级甲等','综合医院','河北省','承德市','双桥区',2400,'承德市南营子大街36号')

导入结果

触发器创建默认科室


默认科室意味着每增添一个新的医院,就为其创建几个默认的科室。原本想到通过游标遍历表的方法实现,但在网上看到人们推荐SQL使用集合形式实现,而减少使用游标。
因此我首先创建了一个初始科室表:


初始科室

CREATE TABLE defaultDepartment(
 number int NOT NULL IDENTITY(001,1),
 name nvarchar(20) NOT NULL
 CONSTRAINT pk_defaultDepartment PRIMARY KEY( number)
 )
 END
 INSERT INTO defaultDepartment VALUES
  ('门诊部'),
  ('住院部'),
  ('急诊部'),
  ('药房'),
  ('收费室'),
  ('化验室'),
  ('放射科'),
  ('手术室'),
  ('B超室'),
  ('行政楼'),
  ('后勤科室')


科室表

 CREATE TABLE Department(
 number int NOT NULL,
 name nvarchar(20) NOT NULL,
 hospitalName  nvarchar(40) NOT NULL
 CONSTRAINT pk_department PRIMARY KEY( number,hospitalName),
 CONSTRAINT fk_Hospital FOREIGN KEY(hospitalName)
   REFERENCES Hospital(医院名称) ON DELETE CASCADE
 )
 END


CREATE TRIGGER


触发器例题分析:数据库每日一题(3)触发器

CREATE TRIGGER  trigger_defaultDepartment ON Hospital
 AFTER INSERT
 AS
 BEGIN
  INSERT INTO Department
   SELECT D.number,D.name,I.医院名称 FROM defaultDepartment D, inserted I
 END


实现展示

存储过程找到医院对应邮编


存储过程例题分析:数据库每日一题 (4)存储过程


CREATE PROC

 CREATE PROC findZipCode
 AS
 BEGIN
  UPDATE Hospital
  SET 邮编= zip_code
  FROM Hospital left join hy_area
   on 市=name
 END
 GO


实现思路


通过对应医院信息中的市名与地区信息中的name

另:可能会出现没有匹配结果的情况,可以通过EXCEPT找到相应条目并手动更新,或通过游标遍历,使用算法寻找。


结果展示

Leave a comment

Your email address will not be published. Required fields are marked *