data.sql

这是一个针对医院管理的一个系统

#use test;

drop table operator;
drop table doctor_message;
drop table patient_message;
drop table see_doctor_message;
drop table price_management;
drop table charge_management;
drop table medicine_type_list;

#Operator操作员
create table operator(
operator_id char(5) primary key,
operator_name char(15) not null,
operator_pwd varchar(8) not null,
operator_sex enum('M','F') not null
);

#Doctor_Message医生档案
create table doctor_message(
doctor_id char(5) primary key,
doctor_name char(15) not null,
doctor_sex enum('M','F') not null,
department char(15) not null,
isdelete enum('Y','N')
);

#Patient_Message病人档案
create table patient_message(
patient_num int auto_increment primary key,
patient_id varchar(10) unique,
patient_name char(15), 
patient_sex enum('M','F'),
patient_birth Date,
patient_address text,
patient_jiguan varchar(10),
patient_state enum('Y','N'), #Y住院 N不住院
pay_money DECIMAL(6,2),
isdelete enum('Y','N')
);

#See_doctor_Message就医档案
create table see_doctor_message(
list_number int auto_increment primary key,
patient_id varchar(10),
department char(15),
doctor_id char(5),
sickness_reason text,
medicine_list text,
isdelete enum('Y','N')
);

#medicine_type_list药品类别
create table medicine_type_list(
type_id varchar(5) primary key,
type_name varchar(15) not null
);

#Price_Management价格管理
create table price_management(
medicine_id char(5) primary key,
medicine_name char(20) not null,
medicine_price DECIMAL(6,2),
type_id varchar(5),
isdelete enum('Y','N')
);

#Charge_Management收费管理 
create table charge_management(
charge_list int auto_increment primary key,
patient_id varchar(10),
medicine_charge DECIMAL(6,2),
registers_charge DECIMAL(6,2),
deal_charge DECIMAL(6,2),
examination_charge DECIMAL(6,2),
isdelete enum('Y','N')
);

insert into operator values('O0001','张三','123','M');
insert into operator values('O0002','李四','123','M');
insert into operator values('O0003','王五','123','F');

insert into doctor_message values ('D0001','华佗','M','内科','N');
insert into doctor_message values ('D0002','Jenny','F','骨科','N');
insert into doctor_message values ('D0003','Tom','M','皮肤科','N');
insert into doctor_message values ('D0004','医1','M','皮肤科','N');
insert into doctor_message values ('D0005','医2','M','皮肤科','N');
insert into doctor_message values ('D0006','医3','M','皮肤科','N');
insert into doctor_message values ('D0007','医4','M','皮肤科','N');

#insert into patient_message  values(008,'蔡弛','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
#insert into patient_message  values(018,'王八','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
#insert into patient_message  values(028,'操','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
#insert into patient_message  values(038,'fuck','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
#insert into patient_message (patient_id,patient_name,patient_sex,patient_birth,patient_address,patient_jiguan,patient_state,pay_money,isdelete) values('A001','蔡弛','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
#insert into patient_message (patient_id,patient_name,patient_sex,patient_birth,patient_address,patient_jiguan,patient_state,pay_money,isdelete) values('A002','黄东','M','1984-11-24','红荔路2号','湖南长沙','Y',180,'N');
#insert into patient_message (patient_id,patient_name,patient_sex,patient_birth,patient_address,patient_jiguan,patient_state,pay_money,isdelete) values('A003','章丽','F','1987-05-13','育才路45号','四川','N',0,'N');

insert into patient_message  values(1,'p001','蔡弛','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(2,'p002','王八','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(3,'p003','操','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(4,'p004','fuck','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(5,'p005','小五','F','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(6,'p006','小一','F','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(7,'p007','小二','F','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(8,'p008','小三','F','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(9,'p009','小国','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(10,'p010','小四','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(11,'p011','地','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');
insert into patient_message  values(12,'p012','在','M','1985-02-13','学院路111号','广东东莞','Y',200,'N');




insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p001','内科','D0001','伤风感冒','M0001、M0002','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p002','骨科','D0002','扭伤,血淤疼痛','M0001、M0004','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p003','皮肤科','D0003','皮肤过敏','M0003、M0002','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p004','内科','D0004','伤风感冒','M0001、M0002','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p005','骨科','D0005','扭伤,血淤疼痛','M0001、M0004','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p006','皮肤科','D0006','皮肤过敏','M0003、M0002','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p007','内科','D0007','伤风感冒','M0001、M0002','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p008','骨科','D0002','扭伤,血淤疼痛','M0001、M0004','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p009','皮肤科','D0003','皮肤过敏','M0003、M0001、M0002、M0004','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p010','内科','D0001','伤风感冒','M0001、M0002','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p011','骨科','D0002','扭伤,血淤疼痛','M0001、M0004','N');
insert into see_doctor_message (patient_id,department,doctor_id,sickness_reason,medicine_list,isdelete) values('p012','皮肤科','D0003','皮肤过敏','M0003、M0002','N');
insert into price_management values('M0001','脱敏粉剂',25,'T0003','N');
insert into price_management values('M0002','排毒王',40,'T0001','N');
insert into price_management values('M0003','接骨七厘片',100,'T0002','N');
insert into price_management values('M0004','白加黑',19,'T0003','N');
insert into price_management values('M0005','感冒止咳颗粒',12,'T0001','N');

insert into charge_management(patient_id,medicine_charge,registers_charge,deal_charge,examination_charge,isdelete)  values(008,50,4,10,10,'N');
insert into charge_management(patient_id,medicine_charge,registers_charge,deal_charge,examination_charge,isdelete)  values(018,100,4,40,10,'N');
insert into charge_management(patient_id,medicine_charge,registers_charge,deal_charge,examination_charge,isdelete)  values(028,84,4,25,12,'N');
insert into charge_management(patient_id,medicine_charge,registers_charge,deal_charge,examination_charge,isdelete)  values(038,8,4,2,1,'N');

insert into medicine_type_list values('T0001','内科药');
insert into medicine_type_list values('T0002','骨科药');
insert into medicine_type_list values('T0003','皮肤科药');


#select * from charge_management;