HIS数据库

ER图

创建数据库

-- 删除已有的HIS
drop database HIS;
-- 创建数据库
create database HIS;
-- 调用数据库HIS
use HIS;
-- 创建科室表
create table department (
DeptCategory int primary key,
DepartName varchar(10) not null,
DepartType int not null
);
-- 创建医生表
create table doctor (
DID int primary key,
DName varchar(20) not null,
DeptCategory int not null,
DocTitle int not null,
DPosition varchar(20) not null,
foreign key (DeptCategory) references department(DeptCategory)
);
-- 创建排班表
create table schedule (
DID int not null,
WorkTime date not null,
primary key(DID,WorkTime),
constraint schedule_fk foreign key(DID) references doctor(DID)
);
-- 创建挂号表
create table regist (
RID int primary key auto_increment,
PID char(18) not null,
PName varchar(20) not null,
Gender int not null,
Birthday date not null,
Age char(3),
AgeType int,
Address text,
DiaDate DATE not null,
NoonType int not null,
DeptCategory int not null,
DID int not null,
DocTitle int not null,
FeeType int not null,
IfRecord int not null,
PerID char(3) not null,
RCondition int not null,
RegisTime datetime not null
);
-- 创建疾病表 
create table decease (
DecID int primary key auto_increment,
DecName varchar(20) not null,
DecType int not null
);
-- 创建项目表
create table treatment (
TreatID int primary key auto_increment,
TreatPrice float not null,
TreatName varchar(20) not null
);
-- 创建处方表
create table prescription (
PreID int primary key auto_increment,
PreName varchar(20) not null,
RID int not null,
PreTime datetime not null,
BCondition int not null
);
-- 创建病历表
create table record (
RecordID int primary key auto_increment,
PID char(18) not null,
RID int not null,
Result int not null,
DiaTime datetime not null,
PreID int not null,
constraint record_fk foreign key(PreID) references prescription(PreID)
);
-- 创建缴费表
create table bill (
PayID int primary key auto_increment,
PreID int not null,
OperID char(3) not null,
DocTitle int not null,
Total float not null,
FeeType int not null,
BillType char(2) not null
);
-- 创建药品表
create table medicine (
MedID int primary key auto_increment,
MedName varchar(20) not null,
Drugs_Dosage varchar(20) not null,
Drugs_Type char(2) not null,
MedPrice float not null
);
-- 创建药房表
create table medroom (
MedID int primary key,
NumLeft int not null,
foreign key (MedID) references medicine(MedID)
);
-- 创建检查项目表
create table treatlist (
ListID int primary key auto_increment,
TreatID int not null,
PreID int not null,
TCondition int not null,
foreign key (TreatID) references treatment (TreatID),
constraint treatlist_fk foreign key(PreID) references prescription(PreID)
);
-- 创建划价表
create table medlist (
ListID int primary key auto_increment, 
MedID int not null,
MedNum int not null,
PreID int not null,
MCondition int not null,
foreign key (MedID) references medicine (MedID),
constraint medlist_fk foreign key(PreID) references prescription(PreID)
);
-- 输入药品信息
alter table medicine auto_increment= 1;
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('注射用甲氨喋呤','1g×1支','西药',15.73);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('红芪','10g/袋','中药',30.79);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('50%葡萄糖注射液(塑瓶)','10:20ml×1支','西药',25.16);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('磷酸奥司他韦胶囊(达菲)','75mg×10粒/盒','西药',60.96);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('黄连颗粒','0.5g/3g袋','中药',1.07);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('灵芝','1000mg/g','中药',28.67);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('盐酸氨酮戊酸散（外用）','118mg×1瓶','西药',19.51);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('康妇消炎栓','2.000g*9粒/盒','西药',7.63);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('补中益气丸','0.375g*240粒/瓶','中药',44.58);
insert into medicine (MedName,Drugs_Dosage,Drugs_Type,MedPrice) 
values ('逍遥丸','0.375g*240粒/瓶','中药',36.17);
-- 输入科室信息(DepartType 1代表外科,0代表内科)
insert into department values (1,'心血管内科',0);
insert into department values (2,'神经内科',0);
insert into department values (3,'呼吸内科',0);
insert into department values (4,'肛肠外科',1);
insert into department values (5,'皮肤科',1);
-- 输入医生信息(DocTitle 1代表普通号,2代表专家号,3代表急诊号)
insert into doctor values (1,'华佗',1,3,'主任医师');
insert into doctor values (2,'宋慈',1,1,'主治医师');
insert into doctor values (3,'张仲景',2,3,'主任医师');
insert into doctor values (4,'孙思邈',2,2,'副主任医师');
insert into doctor values (5,'皇甫谧',3,1,'主治医师')