首先创建数据库，并建立各个表之间的主外键约束等，并插入数据。

create database 医院信息管理系统
on(
 name=医院信息管理系统,
 filename='D:\数据库课程设计\医院信息管理系统\医院信息管理系统.mdf',
 size=5,
 filegrowth=1
 )
 log on
 ( name=医院信息管理系统_log,
  filename='D:\数据库课程设计\医院信息管理系统\医院信息管理系统_log.ldf',
  size=2,
  maxsize=30,
  filegrowth=10%
  )
  use 医院信息管理系统
  go
  create table 医生表
  (医生编号 char(15) not null primary key,
   姓名 char(10) not null unique,
   性别 char(5) null,
   年龄 int null,
   职称 char(11) null,
   科室号 char(20) null)
   go
   alter table 医生表
   add constraint ys_zc default '医师' for 职称
   go
   alter table 医生表
   add constraint fk_ks foreign key(科室号) references 科室表(科室号)
   go
   create table 科室表
   (科室号 char(20) not null primary key,
    科室名 char(10)  not null ,
    科室地址 char(20) null,
    科室电话 char(10) null,
    科室主任 char(10) null)
    go
    create table 病人表
    (病历号 char(15) not null primary key,
     姓名  char(10) not null,
     性别 char(5) null,
     年龄 int null,
     病房号 char(15) null,
     医生姓名 char(10) null,
     血型 char(4) null,
     确诊结果 char(10)null,
     科室号 char(20) null)
    go
    alter table 病人表
    add constraint fk_ksh foreign key(科室号) references 科室表(科室号)
    go
    create table 病房表
    (病房号 char(15)  not null primary key,
     床位数 int null, 
     病房地址 char(20) null,
     科室号 char(20) null)
    go
    alter table 病人表
    add constraint fk_bfh foreign key(病房号) references 病房表(病房号)
    go
    alter table 病房表
    add constraint fk_sk foreign key(科室号) references 科室表(科室号)
    go
    create table 护士表
    (护士编号 char(15) not null primary key,
     姓名  char(10) not null,
     性别 char(5) null,
     年龄 int null,
     科室号 char(20) null)
     go
     alter table 护士表
     add constraint fk_skh foreign key(科室号) references 科室表(科室号)
     go
     create table 分配表
     ( 病房号 char(15) not null,
       护士编号 char(15) not null,
       primary key (病房号,护士编号),
       foreign key(病房号) references 病房表(病房号),
       foreign key(护士编号) references 护士表(护士编号)) 
      go
insert into 科室表(科室号,科室名,科室地址,科室电话,科室主任)
values('101','内科','A1101','10001','高飞'),
('102','外科','A1102','10002','李想'),
('103','儿科','A1103','10003','邓杨'),
('104','妇科','A1104','10004','刘宇'),
('105','神经科','A1105','10005','白皓'),
('201','脑外科','A1201','20001','宋思琪'),
('202','泌尿科','A1202','20002','孙怡'),
('203','骨科','A1203','20003','杨子龙'),
('204','内分泌科','A1204','20004','张子怡'),
('205','口腔科','A1205','20005','郭易曼'),
('301','眼科','A1301','30001','王陆明'),
('302','耳喉鼻科','A1302','30002','韩子琦'),
('303','皮肤科','A1303','30003','徐若鸿'),
('304','心脏外科','A1304','30004','李丽'),
('305','胸外科','A1305','30005','陈若玉')
go
insert into 医生表(医生编号,姓名,性别,年龄,职称,科室号)
values('101001','高飞','男','30','主任医师','101'),
('101002','杨蕾','女','24','住院医师','101'),
('101003','齐风','男','27','主治医师','101'),
('101004','李想','女','45','主任医师','102'),
('101005','朱珠','女','30','主治医师','102'),
('101006','陆风','男','35','主治医师','102'),
('101007','邓杨','男','30','主治医师','103'),
('101008','程悦','女','28','副主治医师','103'),
('101009','刘宇','女','40','副主任医师','104'),
('101010','刘子怡','女','34','主治医师','104'),
('101011','白皓','男','58','主任医师','105'),
('101012','苏楠','女','32','主治医师','105'),
('101013','宋思琪','女','50','主任医师','201'),
('101014','陆大明','男','24','住院医师','201'),
('101015','孙怡','女','33','主治医师','202'),
('101016','刘楠','男','33','主治医师','202'),
('101017','杨子龙','男','35','主治医师','203'),
('101018','焦丹','女','23','住院医师','203'),
('101019','张子怡','女','38','副主任医师','204'),
('101020','李娟','女','32','主治医师','204'),
('101021','郭易曼','女','35','主治医师','205'),
('101022','刘晓','男','22','住院医师','205'),
('101023','王陆明','男','36','主治医师','301'),
('101024','张珍','女','29','副主治医师','301'),
('101025','韩子琦','男','31','主治医师','302'),
('101026','乔芳','女','31','主治医师','302'),
('101027','徐若鸿','男','49','主任医师','303'),
('101028','陆磊','男','31','主治医师','303'),
('101029','李丽','女','51','主任医师','304'),
('101030','高涛','男','31','主治医师','304'),
('101031','陈若玉','女','39','副主任医师','305'),
('101032','刘思雨','女','30','主治医师','305')
go
insert into 病房表(病房号,床位数,病房地址,科室号)
values('001','4','B1001','101'),
('002','5','B1002','101'),
('003','1','B1003','102'),
('004','2','B1004','102'),
('005','0','B1005','103'),
('006','3','B1006','103'),
('007','2','B1007','104'),
('008','3','B1008','105'),
('009','1','B1009','105'),
('010','2','B1010','201'),
('011','1','B1011','201'),
('012','3','B1012','202'),
('013','2','B1013','203'),
('014','2','B1014','204'),
('015','1','B1015','204'),
('016','5','B1016','205'),
('017','2','B1017','301'),
('018','1','B1018','302'),
('019','3','B1019','303'),
('020','1','B1020','303'),
('021','2','B1021','304'),
('022','1','B1022','304'),
('023','3','B1023','305'),
('024','2','B1024','305')
go
select *
from 科室表
go
select *
from 医生表
go
select 医生表.姓名 , 科室名
from 医生表 join 科室表
on 医生表.科室号=科室表.科室号
go

use 医院信息管理系统
go
insert into 病人表(病历号,姓名,性别,年龄,病房号,医生姓名,血型,确诊结果,科室号)
values('21101','王胜安','男','31','001','齐风','A','肺炎','101'),
('21102','蔡壮保','男','54','002','高飞','B','慢性气管炎','101'),
('21103','易江伟','男','28','003','陆风','O','自发性气胸','102'),
('21104','张顺谷','男','32','004','朱珠','AB','胆道结石','102'),
('21105','李鑫灏','男','8','006','邓杨','A','细菌性痢疾','103'),
('21106','梁澄静','女','8','006','程悦','A','诺如腹泻','103'),
('21107','时党舒','女','26','007','刘宇','B','宫颈炎','104'),
('21108','何刚名','男','66','008','白皓','O','脑梗塞','105'),
('21109','严席华','男','58','008','苏楠','B','脑出血','105'),
('21110','刘鲜发','男','32','010','宋思琪','A','脑积水','201'),
('21111','何毅','男','35','010','陆大明','O','脑脓肿','201'),
('21112','唐宸禹','男','38','011','宋思琪','B','颅脑外伤','201'),
('21113','张顺联','男','50','012','刘楠','A','泌尿结石','202'),
('21114','汤青','女','29','012','孙怡','B','急性肾炎','202'),
('21115','柯纤栩','女','34','013','焦丹','AB','腰间盘突出','203'),
('21116','夏莱','女','30','014','张子怡','B','电解质紊乱','204'),
('21117','杜鹃','女','28','015','李娟','A','内分泌紊乱','204'),
('21118','张洪赫','男','25','016','郭易曼','B','慢性牙周炎','205'),
('21119','池慕颖','女','26','016','刘晓','AB','口腔白斑','205'),
('21120','齐芬霞','女','69','017','王陆明','O','白内障','301'),
('21121','卢全旭','男','66','017','张珍','A','青光眼','301'),
('21122','卓互知','男','45','018','韩子琦','AB','中耳炎','302'),
('21123','元感奇','男','16','019','徐若鸿','O','扁平疣','303'),
('21124','秦长乐','男','32','020','陆磊','B','皮肤癣','303'),
('21125','丘寸心','男','48','021','李丽','O','血胸','304'),
('21126','路从风','男','18','022','高涛','B','漏斗胸','304'),
('21127','王浩','男','29','023','陈若玉','A','胸部异物','305'),
('21128','潘奕','女','30','024','刘思雨','AB','胸腔积液','305')
go
insert into 护士表(护士编号,姓名,性别,年龄,科室号)
values('110101','夏一柳','女','25','101'),
('110102','席梦荣','女','28','101'),
('110103','李文倩','女','22','102'),
('110104','绕詹林','男','24','102'),
('110105','吴申萌','女','29','103'),
('110106','田甜','女','23','103'),
('110107','高悦然','女','25','104'),
('110108','刘子诺','女','21','104'),
('110109','张以诺','女','22','105'),
('110110','赵佳宇','女','22','105'),
('110111','张怡铭','女','35','201'),
('110112','蒋欣芹','女','26','201'),
('110113','马航钰','女','30','202'),
('110114','苏芷璇','女','25','202'),
('110115','王晓甜','女','24','203'),
('110116','陈子媛','女','26','203'),
('110117','董璇','女','21','204'),
('110118','孙玉','女','23','204'),
('110119','李婉婷','女','28','205'),
('110120','宋雨涵','女','31','205'),
('110121','邓梦琪','女','24','301'),
('110122','崔佳玉','女','26','301'),
('110123','郭星瞳','女','24','302'),
('110124','郭思羽','女','23','302'),
('110125','王雨橙','女','28','303'),
('110126','刘子琳','女','27','303'),
('110127','李雨霏','女','29','304'),
('110128','陆嘉晨','女','24','304'),
('110129','张馨予','女','23','305'),
('110130','刘欣悦','女','27','305')
go
insert into 分配表(病房号,护士编号)
values('001','110101'),
('002','110102'),
('003','110103'),
('004','110104'),
('005','110105'),
('006','110106'),
('007','110107'),
('007','110108'),
('008','110109'),
('009','110110'),
('010','110111'),
('011','110112'),
('012','110113'),
('012','110114'),
('013','110115'),
('013','110116'),
('014','110117'),
('015','110118'),
('016','110119'),
('016','110120'),
('017','110121'),
('017','110122'),
('018','110123'),
('018','110124'),
('019','110125'),
('020','110126'),
('021','110127'),
('022','110128'),
('023','110129'),
('024','110130')
go

此时数据库已经建立好了，下面进行数据库的基本操作。

use 医院信息管理系统
insert into 病人表(病历号,姓名,性别,年龄,病房号,医生姓名,血型,确诊结果)
values('21129','张淦','男','25','024','刘思雨','O','胸腔积液')
go
delete
from 病人表
where 病历号='21129'
go
update 护士表
set 年龄='26'
where 姓名='夏一柳'
go
select 病历号,病人表.姓名,病人表.性别,病人表.年龄,病房号,血型,医生姓名,职称,医生表.科室号
from 病人表 join 医生表
on 病人表.医生姓名=医生表.姓名
where 确诊结果='脑积水'
go
select 科室号,COUNT(护士编号) as '护士人数'
from 护士表
group by 科室号
go
select 科室号,COUNT(病房号) as '病房数'
from 病房表
group by 科室号
having(COUNT(病房号))>1
go
select AVG(年龄) as '全体医生平均年龄'
from 医生表
go
select  姓名,年龄
from 病人表
order by 年龄 desc
go
select 护士编号,姓名,YEAR(GETDATE())-年龄 as '出生日期'
from 护士表
go
select 护士表.护士编号,病房号,姓名
from 护士表 join 分配表
on 护士表.护士编号=分配表.护士编号
go
select  病历号,病人表.姓名,病人表.性别,血型,病房号,医生姓名,职称,医生表.科室号,科室表.科室名,科室表.科室地址
from 病人表 join 医生表
on 病人表.医生姓名=医生表.姓名
join 科室表
on 医生表.科室号=科室表.科室号
go
create view v_pyk
as
select  病历号,病人表.姓名,病人表.性别,血型,病房号,医生姓名,职称,医生表.科室号,科室表.科室名,科室表.科室地址
from 病人表 join 医生表
on 病人表.医生姓名=医生表.姓名
join 科室表
on 医生表.科室号=科室表.科室号
go
select *
from v_pyk
go
update v_pyk
set 病房号='021'
where 病历号='21126'
go
create proc pr_inf @pr varchar(20)
as
select 病人表.姓名,病人表.性别,血型,医生姓名,医生编号,确诊结果
from 病人表 join 医生表
on 病人表.医生姓名=医生表.姓名
where 病人表.姓名 like @pr 
go
exec pr_inf '张%'
use 医院信息管理系统
go
alter proc hs_bf @hno char(6),@bno char(10) output ,@bed varchar(10) output 
as
select @bno=分配表.病房号,@bed=床位数
from 分配表 join 病房表
on 病房表.病房号=分配表.病房号
where 护士编号 =@hno
set @bed=convert(varchar,@bed)
go
declare @bno1 char(10),@bed1 varchar(10)
exec hs_bf '110101',@bno1 output ,@bed1 output
print '病房号'+@bno1
print '床位数'+@bed1
go
use 医院信息管理系统
go
alter trigger tri_br 
on 病人表
after delete,update
as
declare @sno char(10) ,@cno char (10)
select @sno =病历号 from deleted
select @cno=病房号 from deleted
delete from 病人表 where 病历号=@sno
update  病房表
set 床位数=床位数+1
where @cno = 病房表.病房号
go
delete from 病人表 where 病历号='21102'
go

下面是各个表的实体图

 

 

 

 下面是数据流图

 下面是医院管理E-R图

下面是医院功能结构图

1.设计任务 

1.1系统开发背景概述

随着计算机技术的飞速发展,计算机在医院管理中应用的普及,利用计算机实现医院管理势在必行。对于大中型医院来说,利用计算机支持医院高效率完成劳动医院管理的日常事务,就是适应现代医院管理制度要求、推动医院管理走向科学化、规范化的必要条件。

目前市面上流行的信息管理系统不少。但就是,对于中型医院的医院管理系统来说,不需要大型的数据库系统。只需要一个操作方便,功能实用,能满足本中心对数据的管理及需求的系统。我们的目标就就是在于开发一个功能实用、操作方便,简单明了的医院管理系统。能够录入医院的基本资料,在操作上能够完成诸如添加、修改、删除、按各种条件进行查询、新用户的设置及密码修改等方面的工作,基本满足人事日常业务的需要。团队完成了此课题的研究与开发,包括分析、设计、编码、测试、文档编写等内容。

1.2系统开发的目的与意义

随着现代化社会的发展,为了抓住机遇,在竞争中占得先机,作为管理企业的一个必不可少的重要环节—在线管理的信息化、计算机化也就迫在眉捷了。开发在线管理信息系统即有宏观上的意义,那就就是顺应时代信息化、现代化潮流,提高效益,促进国民经济管理的结构优化;也有微观上的意义,那就就是可以提高管理的现代化程序,加强管理的信息化手段,提高工作效率,增加单位效益。

电子计算机与通信技术的快速发展使人类已经逐渐地进入信息化社会。信息与材料、能源一样成为一种社会的基本生产资料,在人类的社会生产活动中发挥着重要的作用。当前,医院还面临着越来越激烈的市场竞争、要想在竞争中取胜,也必须采用先进的管理方法与手段。一个规划周详、设计先进的计算机信息管理网络系统就是取得竞争胜利的必备手段。通过实现先进的计算机网络化管理,能为领导层的管理与决策及时提供可靠的数字依据,使管理更合理、更先进;减少人力、物力资源的浪费,降低成本;提高工作效率,提高管理效率;提高经济效益,从而提高医院的整体竞争力。建立以人事管理为中心的医院信息网络管理系统,以适应医院长期快速发展的需要,更重要的就是能使患者在医院得到更加满意的治疗。

2 需求分析

2.1信息要求

 图2-1医院信息系统的功能模块图

本课程设计模拟一般医院的信息管理情况，方便对医院信息的查询。经过充分的调研，确定本系统应对病人基本信息、科室基本信息、医生基本信息、病房基本信息、护士基本信息、护士的分配情况进行管理，本系统主要要求为：

• 能够全面的管理病人基本信息、科室基本信息、医生基本信息、病房基本信息、护士基本信息以及护士分配信息。
• 本系统可以方便维护各种信息。
• 本系统能够方便查询各个信息表的基本信息。
• 能够方便实现多个信息表的连接查询、嵌套查询。
• 本系统能够实现有用信息的查询统计。
• 本系统能够实现有用信息的输出。

2.2数据流图

 

    图2-2医院信息管理数据流图

2.3数据字典

数据字典是指对数据的数据项、数据结构、数据流、数据存储、处理逻辑、外部实体等五个部分进行定义和描述，其目的是对数据流程图中的各个元素做出详细的说明。本次实验主要对数据字典的数据项进行分析。如表2所示：

表2数据项

序号	数据项	数据类型	长度	备注
1	医生编号	CHAR	6	医生的工作编号
2	姓名	VARCHAR	20	医生的姓名
3	性别	CHAR	2	医生的性别
4	年龄	INT		医生的年龄
5	职称	VARCHAR	20	医生的职称
6	科室号	CHAR	3	医生的科室号
7	病历号	CHAR	5	病人的病历号
8	姓名	VARCHAR	20	病人的姓名
9	性别	CHAR	2	病人的性别
10	年龄	INT		病人的年龄
11	病房号	CHAR	3	病人的病房号
13	血型	VARCHAR	2	病人的血型
14	科室号	CHAR	3	病人的所属科室
15	确诊结果	VARCHAR	20	病人的诊断报告
16	科室号	CHAR	3	科室的编号
17	科室名	VARCHAR	20	科室的名字
18	科室地址	VARCHAR	20	科室的地址
19	科室电话	VARCHAR	10	科室的电话
20	科室主任	VARCHAR	20	科室的主任
21	病房号	CHAR	3	病房的编号
22	床位数	INT		病房的床位数
23	科室号	CHAR	3	病房的所属科室
24	病房地址	VARCHAR	20	病房的地址
25	护士编号	CHAR	6	护士的编号
26	姓名	VARCHAR	20	护士的姓名
27	性别	CHAR	2	护士的性别
28	年龄	INT		护士的年龄
29	科室号	CHAR	3	护士的所属科室

3 概念结构设计

3.1医院信息系统实体属性

医院医生的实体属性，主要包括医生的医生编号，姓名，性别，年龄，科室号，职称。如图3-1医生实体属性图所示：

  图3-1医生实体属性图

医院科室的实体属性，主要包括科室的科室号，科室名，科室地址，科室电话，科室主任；如图3-2科室实体属性图所示：

 图3-3病房实体属性图

医院护士的实体属性，主要包括护士编号，姓名，性别，年龄，科室号。如图3-4护士实体属性图所示： 

医院病人的实体属性图，主要包括病历号，姓名，性别，年龄，确诊结果，医生姓名，病房号，血型，科室号。如图3-5病人实体属性图所示： 

3.2总E-R图

医院信息管理总E-R图，如图3-6总E-R图所示：

4 逻辑结构设计

4.1关系模式：

医生（医生编号、姓名、性别、年龄、职称、所属科室号）

病人（病历号、姓名、性别、年龄、病房号、医生姓名、血型、所属科室号、诊断）

科室（科室号、科室名、科室地址、科室电话、科室主任）

病房（病房号、床位号、所属科室号、病房地址）

护士（护士编号、姓名、性别、年龄、所属病房号）

5 物理结构设计

5.1数据关系模式的优化：

对于1对N关系的挂号，可以在病人模式中加入科室号属性。

对于1对N关系的属于，可以在医生模式中加入科室号属性。

对于1对N关系的拥有，可以在病房模式中加入科室号属性。

这样得到优化的关系模式：

病人（病历号、姓名、性别、年龄、病房号、医生姓名、血型、确诊结果、科室号）

医生（医生编号、姓名、性别、年龄、职称、科室号）

病房（病房号、床位号、病房地址、科室号）

对于N对M的关系模式，可以生成一个新的模式：

分配（病房号、护士编号）

对于优化后的关系模式：

医生（医生编号、姓名、性别、年龄、职称、科室号）

病人（病历号、姓名、性别、年龄、病房号、医生姓名、血型、确诊结果、科室号）

科室（科室号、科室名、科室地址、科室电话、科室主任）

病房（病房号、床位号、病房地址、科室号）

护士（护士编号、姓名、性别、年龄、科室号）

分配（病房号、护士编号）

分析，关系模式的每个关系都是不可再分的原子值，即为第一范式，又因为每个非主属性都不传递依赖于模式的候选键，因此该模式集为第三范式。

通过优化后关系模式得出医生表：

表4.1医生基本信息表

列名	数据类型	字段大小	是否为空	备注
医生编号	CHAR	6	NOT	主键
姓名	VARCHAR	20	NOT
性别	CHAR	2	NOT
年龄	INT		NOT
职称	VARCHAR	20	YES
科室号	CHAR	3	YES	外键

通过优化后关系模式得出病人表：

表4.2病人基本信息表

列名	数据类型	字段大小	是否为空	备注
病历号	CHAR	5	NOT	主键
姓名	VARCHAR	20	NOT
性别	CHAR	2	NOT
年龄	INT		NOT
病房号	CHAR	3	YES
医生姓名	VARCHAR	20	YES
血型	CHAR	2	YES
确诊结果	VARCHAR	20	YES
科室号	CHAR	3	YES	外键

通过优化后关系模式得出科室表：

表4.3科室基本信息表

列名	数据类型	字段大小	是否为空	备注
科室号	CHAR	3	NOT	主键
科室名	VARCHAR	20	NOT
科室地址	VARCHAR	20	YES
科室电话	VARCHAR	10	YES
科室主任	VARCHAR	20	YES

通过优化后关系模式得出病房表：

表4.4病房基本信息表

列名	数据类型	字段大小	是否为空	备注
病房号	CHAR	3	NOT	主键
床位数	INT		YES
病房地址	VARCHAR	20	YES
科室号	CHAR	3	YES	外键

通过优化后关系模式得出护士表：

表4.5护士基本信息表

列名	数据类型	字段大小	是否为空	备注
护士编号	CHAR	6	NOT	主键
姓名	VARCHAR	20	NOT
性别	CHAR	2	NOT
年龄	INT		NOT
科室号	CHAR	3	YES	外键

通过优化后关系模式得出分配表：

表4.6护士分配表

列名	数据类型	字段大小	是否为空	备注
病房号	CHAR	3	NOT	主键、外键
护士编号	CHAR	6	NOT	主键、外键

5.2设计结构的内容

  由于用户最终通过某一特定的DBMS使用数据库，因此数据的物理结构设计必须结合具体的DBMS进行，主要包括选择数据库的存储结构和存取方法两个方面。

5.2.1确定存储结构

 数据库物理结构设计与特定的硬件环境、DBMS及实施环境密切相关，数据库的配置也是确定物理结构的重要内容，包括数据库空间的分配、日志文件大小、数据字典空间的确定以及相关参数的设置等。

5.2.2选择存取方法

数据库的存取方法有索引、聚簇等方法。

1.索引的选择

 一般来说对下列的情况的属性列建立索引：

查询频繁的属性列。

经常出现在链接操作中的属性列。

WHERE、ORDER、GROUP  BYD等句子中的属性列。

不宜建立索引的属性列：

不出现或很少在查询条件出现的属性列。

属性值很少的列。

经常需要更新的列。

经常需要更新或含有记录较少的数据表的属性列。

2.聚簇的选择

聚簇是改进系统性能的另一种技术，聚簇分为以下3种情况：

分段。按属性分组，将文件在垂直方向进行分解。

分区。将文件进行水平分解，按照记录存取频度进行分组。

聚簇。从不同的关系中取出某些属性物理地存储在一起，以改变连接查询的效率。

5.3 评价物理结构

  物理结构满足设计的需求，在时间和空间方面有很大的效率，可以进入数据库实施阶段，数据库的物理结构设计都需要经过反复测试、不断优化。

6 数据库实施

完成分析与结构优化以后，开始数据库的实施阶段，本章主要以数据库的创建，数据表的增、删、改、查，视图的创建以及存储过程的创建为内容。

6.1数据库的创建

create database 医院信息管理系统

on(

 name=医院信息管理系统,

 filename='D:\数据库课程设计\医院信息管理系统\医院信息管理系统.mdf',

 size=5,

 filegrowth=1

 )

 log on

 ( name=医院信息管理系统_log,

  filename='D:\数据库课程设计\医院信息管理系统\医院信息管理系统_log.ldf',

  size=2,

  maxsize=30,

  filegrowth=10%

  )

6.2表的创建

6.2.1科室表的创建

use 医院信息管理系统

create table 科室表

   (科室号 char(3) not null primary key,

    科室名 varchar(20)  not null ,

    科室地址 varchar (20) null,

    科室电话 varchar (10) null,

    科室主任 varchar (10) null)

    go

6.2.2医生表的创建

create table 医生表

  (医生编号 char(6) not null primary key,

   姓名 varchar (20) not null unique,

   性别 char(2)  not null,

   年龄 int not null,

   职称 varchar (20) null,

   科室号 char(3) null)

   go

   alter table 医生表

   add constraint ys_zc default '医师' for 职称

   go

   alter table 医生表

   add constraint fk_ks foreign key(科室号) references 科室表(科室号)

   go

6.2.3病人表的创建

create table 病人表

    (病历号 char(5) not null primary key,

     姓名  varchar (20) not null,

     性别 char(2) not null,

     年龄 int not null,

     病房号 char(3) null,

     医生姓名 varchar (20) null,

     血型 char(2) null,

     确诊结果 varchar (20)null,

     科室号 char(3) null)

    go

    alter table 病人表

    add constraint fk_ksh foreign key(科室号) references 科室表(科室号)

go

  alter table 病人表

    add constraint fk_bfh foreign key(病房号) references 病房表(病房号)

    go

6.2.4病房表的创建

create table 病房表

    (病房号 char(3)  not null primary key,

     床位数 int null, 

     病房地址 varchar (20) null,

     科室号 char(3) null)

    go

    alter table 病房表

    add constraint fk_sk foreign key(科室号) references 科室表(科室号)

go

6.2.5护士表的创建

create table 护士表

    (护士编号 char(6) not null primary key,

     姓名  varchar (20) not null,

     性别 char(2) not null,

     年龄 int not null,

     科室号 char(3) null)

     go

     alter table 护士表

     add constraint fk_skh foreign key(科室号) references 科室表(科室号)

     go

6.2.6分配表的创建

create table 分配表

     ( 病房号 char(3) not null,

       护士编号 char(6) not null,

       primary key (病房号,护士编号),

       foreign key(病房号) references 病房表(病房号),

       foreign key(护士编号) references 护士表(护士编号)) 

      go

6.3表的数据插入

6.3.1科室表插入数据

insert into 科室表(科室号,科室名,科室地址,科室电话,科室主任)

values('101','内科','A1101','A1-10001','高飞'),

('102','外科','A1102',' A1-10002','李想'),

('103','儿科','A1103',' A1-10003','邓杨'),

('104','妇科','A1104',' A1-10004','刘宇'),

('105','神经科','A1105',' A1-10005','白皓'),

('201','脑外科','A1201',' A1-20001','宋思琪'),

('202','泌尿科','A1202',' A1-20002','孙怡'),

('203','骨科','A1203',' A1-20003','杨子龙'),

('204','内分泌科','A1204',' A1-20004','张子怡'),

('205','口腔科','A1205',' A1-20005','郭易曼'),

('301','眼科','A1301',' A1-30001','王陆明'),

('302','耳喉鼻科','A1302',' A1-30002','韩子琦'),

('303','皮肤科','A1303',' A1-30003','徐若鸿'),

('304','心脏外科','A1304',' A1-30004','李丽'),

('305','胸外科','A1305',' A1-30005','陈若玉')

Go

6.3.2医生表插入数据

insert into 医生表(医生编号,姓名,性别,年龄,职称,科室号)

values('101001','高飞','男',30,'主任医师','101'),

('101002','杨蕾','女',24,'住院医师','101'),

('101003','齐风','男',27,'主治医师','101'),

('101004','李想','女',45,'主任医师','102'),

('101005','朱珠','女',30,'主治医师','102'),

('101006','陆风','男',35,'主治医师','102'),

('101007','邓杨','男',30,'主治医师','103'),

('101008','程悦','女',28,'副主治医师','103'),

('101009','刘宇','女',40,'副主任医师','104'),

('101010','刘子怡','女',34,'主治医师','104'),

('101011','白皓','男',58,'主任医师','105'),

('101012','苏楠','女',32,'主治医师','105'),

('101013','宋思琪','女',50,'主任医师','201'),

('101014','陆大明','男',24,'住院医师','201'),

('101015','孙怡','女',33,'主治医师','202'),

('101016','刘楠','男',33,'主治医师','202'),

('101017','杨子龙','男',35,'主治医师','203'),

('101018','焦丹','女',23,'住院医师','203'),

('101019','张子怡','女',38,'副主任医师','204'),

('101020','李娟','女',32,'主治医师','204'),

('101021','郭易曼','女',35,'主治医师','205'),

('101022','刘晓','男',22,'住院医师','205'),

('101023','王陆明','男',36,'主治医师','301'),

('101024','张珍','女',29,'副主治医师','301'),

('101025','韩子琦','男',31,'主治医师','302'),

('101026','乔芳','女',31,'主治医师','302'),

('101027','徐若鸿','男',49,'主任医师','303'),

('101028','陆磊','男',31,'主治医师','303'),

('101029','李丽','女',51,'主任医师','304'),

('101030','高涛','男',31,'主治医师','304'),

('101031','陈若玉','女',39,'副主任医师','305'),

('101032','刘思雨','女',30,'主治医师','305')

Go

6.3.3病房表插入数据

insert into 病房表(病房号,床位数,病房地址,科室号)

values('001',4,'B1001','101'),

('002',5,'B1002','101'),

('003',1,'B1003','102'),

('004',2,'B1004','102'),

('005',0,'B1005','103'),

('006',3,'B1006','103'),

('007',2,'B1007','104'),

('008',3,'B1008','105'),

('009',1,'B1009','105'),

('010',2,'B1010','201'),

('011',1,'B1011','201'),

('012',3,'B1012','202'),

('013',2,'B1013','203'),

('014',2,'B1014','204'),

('015',1,'B1015','204'),

('016',5,'B1016','205'),

('017',2,'B1017','301'),

('018',1,'B1018','302'),

('019',3,'B1019','303'),

('020',1,'B1020','303'),

('021',2,'B1021','304'),

('022',1,'B1022','304'),

('023',3,'B1023','305'),

('024',2,'B1024','305')

go

6.3.4病人表插入数据

insert into 病人表(病历号,姓名,性别,年龄,病房号,医生姓名,血型,确诊结果,科室号)

values('21101','王胜安','男',31,'001','齐风','A','肺炎','101'),

('21102','蔡壮保','男',54,'002','高飞','B','慢性气管炎','101'),

('21103','易江伟','男',28,'003','陆风','O','自发性气胸','102'),

('21104','张顺谷','男',32,'004','朱珠','AB','胆道结石','102'),

('21105','李鑫灏','男',8,'006','邓杨','A','细菌性痢疾','103'),

('21106','梁澄静','女',8,'006','程悦','A','诺如腹泻','103'),

('21107','时党舒','女',26,'007','刘宇','B','宫颈炎','104'),

('21108','何刚名','男',66,'008','白皓','O','脑梗塞','105'),

('21109','严席华','男',58,'008','苏楠','B','脑出血','105'),

('21110','刘鲜发','男',32,'010','宋思琪','A','脑积水','201'),

('21111','何毅','男',35,'010','陆大明','O','脑脓肿','201'),

('21112','唐宸禹','男',38,'011','宋思琪','B','颅脑外伤','201'),

('21113','张顺联','男',50,'012','刘楠','A','泌尿结石','202'),

('21114','汤青','女',29,'012','孙怡','B','急性肾炎','202'),

('21115','柯纤栩','女',34,'013','焦丹','AB','腰间盘突出','203'),

('21116','夏莱','女',30,'014','张子怡','B','电解质紊乱','204'),

('21117','杜鹃','女',28,'015','李娟','A','内分泌紊乱','204'),

('21118','张洪赫','男',25,'016','郭易曼','B','慢性牙周炎','205'),

('21119','池慕颖','女',26,'016','刘晓','AB','口腔白斑','205'),

('21120','齐芬霞','女',69,'017','王陆明','O','白内障','301'),

('21121','卢全旭','男',66,'017','张珍','A','青光眼','301'),

('21122','卓互知','男',45,'018','韩子琦','AB','中耳炎','302'),

('21123','元感奇','男',16,'019','徐若鸿','O','扁平疣','303'),

('21124','秦长乐','男',32,'020','陆磊','B','皮肤癣','303'),

('21125','丘寸心','男',48,'021','李丽','O','血胸','304'),

('21126','路从风','男',18,'022','高涛','B','漏斗胸','304'),

('21127','王浩','男',29,'023','陈若玉','A','胸部异物','305'),

('21128','潘奕','女',30,'024','刘思雨','AB','胸腔积液','305')

Go

6.3.5护士表插入数据

insert into 护士表(护士编号,姓名,性别,年龄,科室号)

values('110101','夏一柳','女',25,'101'),

('110102','席梦荣','女',28,'101'),

('110103','李文倩','女',22,'102'),

('110104','绕詹林','男',24,'102'),

('110105','吴申萌','女',29,'103'),

('110106','田甜','女',23,'103'),

('110107','高悦然','女',25,'104'),

('110108','刘子诺','女',21,'104'),

('110109','张以诺','女',22,'105'),

('110110','赵佳宇','女',22,'105'),

('110111','张怡铭','女',35,'201'),

('110112','蒋欣芹','女',26,'201'),

('110113','马航钰','女',30,'202'),

('110114','苏芷璇','女',25,'202'),

('110115','王晓甜','女',24,'203'),

('110116','陈子媛','女',26,'203'),

('110117','董璇','女',21,'204'),

('110118','孙玉','女',23,'204'),

('110119','李婉婷','女',28,'205'),

('110120','宋雨涵','女',31,'205'),

('110121','邓梦琪','女',24,'301'),

('110122','崔佳玉','女',26,'301'),

('110123','郭星瞳','女',24,'302'),

('110124','郭思羽','女',23,'302'),

('110125','王雨橙','女',28,'303'),

('110126','刘子琳','女',27,'303'),

('110127','李雨霏','女',29,'304'),

('110128','陆嘉晨','女',24,'304'),

('110129','张馨予','女',23,'305'),

('110130','刘欣悦','女',27,'305')

Go

6.3.6分配表插入数据

insert into 分配表(病房号,护士编号)

values('001','110101'),

('002','110102'),

('003','110103'),

('004','110104'),

('005','110105'),

('006','110106'),

('007','110107'),

('007','110108'),

('008','110109'),

('009','110110'),

('010','110111'),

('011','110112'),

('012','110113'),

('012','110114'),

('013','110115'),

('013','110116'),

('014','110117'),

('015','110118'),

('016','110119'),

('016','110120'),

('017','110121'),

('017','110122'),

('018','110123'),

('018','110124'),

('019','110125'),

('020','110126'),

('021','110127'),

('022','110128'),

('023','110129'),

('024','110130')

Go

7 数据库操作

7.1数据表查询

7.1.1科室表数据查询

go

select *

from 科室表

go

7.1.2医生表数据查询

go

select *

from 医生表

go

 

7.1.3医生表科室表内连接查询

select 医生表.姓名 , 科室名

from 医生表 join 科室表

on 医生表.科室号=科室表.科室号

go

 

 7.1.4在病人表中插入一条信息

（’21129’,’张淦’,’男’,’25’,’024’,’刘思雨’,’O’,’胸腔积液’）。

use 医院信息管理系统

insert into 病人表(病历号,姓名,性别,年龄,病房号,医生姓名,血型,确诊结果)

values('21129','张淦','男','25','024','刘思雨','O','胸腔积液')

go

7.1.5删除病人表中最后一条记录

（’21129’,’张淦’,’男’,’25’,’024’,’刘思雨’,’O’,’胸腔积液’）。

go

delete

from 病人表

where 病历号='21129'

go

7.1.6将护士表中夏一柳的年龄修改为26。

update 护士表

set 年龄='26'

where 姓名='夏一柳'

go

7.1.7查询患有脑积水病人

他的病历号，姓名，性别，年龄，病房号，血型以及所对应的医生姓名，职称，所属科室号。

select 病历号,病人表.姓名,病人表.性别,病人表.年龄,病房号,血型,医生姓名,职称,医生表.科室号

from 病人表 join 医生表

on 病人表.医生姓名=医生表.姓名

where 确诊结果='脑积水'

go

7.1.8查询每个科室拥有多少名护士。

select 科室号,COUNT(护士编号) as '护士人数'

from 护士表

group by 科室号

 

7.1.9查询拥有多于一个病房的科室。

select 科室号,COUNT(病房号) as '病房数'

from 病房表

group by 科室号

having(COUNT(病房号))>1

go

7.1.10查询全体医生的平均年龄。

select AVG(年龄) as '全体医生平均年龄'

from 医生表

go 

 

7.1.11查询病人的年龄，并按降序排列。

select  姓名,年龄

from 病人表

order by 年龄 desc

go

7.1.12查询护士的出生年月。

select 护士编号,姓名,YEAR(GETDATE())-年龄 as '出生日期'

from 护士表

go

7.1.13查询每个护士所负责的病房号。

select 护士表.护士编号,病房号,姓名

from 护士表 join 分配表

on 护士表.护士编号=分配表.护士编号

go 

 

7.1.14查询病人所对应的病历号

他的姓名，性别，血型，病房号，医生姓名，职称，所属科室号，科室名，科室地址。

select  病历号,病人表.姓名,病人表.性别,血型,病房号,医生姓名,职称,医生表.科室号,科室表.科室名,科室表.科室地址

from 病人表 join 医生表

on 病人表.医生姓名=医生表.姓名

join 科室表

on 医生表.科室号=科室表.科室号

go

7.1.15创建一个视图v_pyk

查询病人所对应的病历号，姓名，性别，血型，病房号，医生姓名，职称，所属科室号，科室名，科室地址。

create view v_pyk

as

select  病历号,病人表.姓名,病人表.性别,血型,病房号,医生姓名,职称,医生表.科室号,科室表.科室名,科室表.科室地址

from 病人表 join 医生表

on 病人表.医生姓名=医生表.姓名

join 科室表

on 医生表.科室号=科室表.科室号

go

select *

from v_pyk

go

7.1.16将视图v_pyk中数据更改

将病历号为21126的病人的病房号改为021

update v_pyk

set 病房号='021'

where 病历号='21126'

go

7.1.17创建一个存储过程pr_inf

输出指定病人的姓名，性别，血型，主治医生，医生编号，确诊结果。

create proc pr_inf @pr varchar(20)

as

select 病人表.姓名,病人表.性别,血型,医生姓名,医生编号,确诊结果

from 病人表 join 医生表

on 病人表.医生姓名=医生表.姓名

where 病人表.姓名 like @pr 

go

exec pr_inf '张%'

7.1.18创建一个存储过程hs_bf

输入一个护士编号，输出该护士所对应的病房号以及剩余床位数。

create proc hs_bf @hno char(6),@bno char(10) output ,@bed varchar(10) output 

as

select @bno=分配表.病房号,@bed=床位数

from 分配表 join 病房表

on 病房表.病房号=分配表.病房号

where 护士编号 =@hno

set @bed=convert(varchar,@bed)

go

declare @bno1 char(10),@bed1 varchar(10)

exec hs_bf '110101',@bno1 output ,@bed1 output

print '病房号'+@bno1

print '床位数'+@bed1

go

 

7.1.19创建一个触发器

在删除病人表的一条记录时，所对应的病房表的床位数要实现及时更新。

create trigger tri_br 

on 病人表

instead of delete,update

as

declare @sno char(10) ,@cno char (10)

select @sno =病历号 from deleted

select @cno=病房号 from deleted

delete from 病人表 where 病历号=@sno

update  病房表

set 床位数=床位数+1

where @cno = 病房表.病房号

go

delete from 病人表 where 病历号='21102'

go