/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50521
Source Host           : localhost:3306
Source Database       : hrz

Target Server Type    : MYSQL
Target Server Version : 50521
File Encoding         : 65001

Date: 2020-05-07 10:07:45
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`area_name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`area_population`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`area_name`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of area
-- ----------------------------
BEGIN;
INSERT INTO `area` VALUES ('武昌区', '1263700'), ('江汉区', '680000'), ('硚口区', '864900');
COMMIT;

-- ----------------------------
-- Table structure for areadata
-- ----------------------------
DROP TABLE IF EXISTS `areadata`;
CREATE TABLE `areadata` (
`time`  date NOT NULL DEFAULT '0000-00-00' ,
`confirmed`  int(11) NULL DEFAULT NULL ,
`suspected`  int(11) NULL DEFAULT NULL ,
`death`  int(11) NULL DEFAULT NULL ,
`input`  int(11) NULL DEFAULT NULL ,
`output`  int(11) NULL DEFAULT NULL ,
`areaname`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
PRIMARY KEY (`time`, `areaname`),
FOREIGN KEY (`areaname`) REFERENCES `area` (`area_name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `data_area` (`areaname`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of areadata
-- ----------------------------
BEGIN;
INSERT INTO `areadata` VALUES ('2020-02-02', '23', '68', '3', '567', '647', '武昌区'), ('2020-02-02', '13', '43', '0', '342', '245', '江汉区'), ('2020-02-02', '43', '54', '1', '246', '316', '硚口区'), ('2020-02-03', '32', '89', '4', '100', '132', '武昌区'), ('2020-02-03', '32', '65', '4', '214', '146', '江汉区'), ('2020-02-03', '53', '65', '4', '143', '214', '硚口区'), ('2020-02-04', '56', '156', '25', '46', '42', '武昌区'), ('2020-02-04', '64', '134', '3', '128', '213', '江汉区'), ('2020-02-04', '79', '158', '3', '86', '36', '硚口区'), ('2020-02-05', '74', '200', '32', '31', '14', '武昌区'), ('2020-02-05', '74', '214', '17', '94', '87', '江汉区'), ('2020-02-05', '87', '213', '13', '74', '24', '硚口区'), ('2020-02-06', '89', '258', '54', '10', '2', '武昌区'), ('2020-02-06', '69', '167', '24', '36', '43', '江汉区'), ('2020-02-06', '45', '124', '12', '31', '23', '硚口区'), ('2020-02-07', '45', '187', '46', '3', '0', '武昌区'), ('2020-02-07', '56', '115', '17', '14', '8', '江汉区'), ('2020-02-07', '32', '74', '7', '14', '9', '硚口区'), ('2020-02-08', '24', '107', '35', '0', '0', '武昌区'), ('2020-02-08', '35', '89', '12', '4', '0', '江汉区'), ('2020-02-08', '23', '41', '4', '7', '3', '硚口区');
COMMIT;

-- ----------------------------
-- Table structure for cooperation
-- ----------------------------
DROP TABLE IF EXISTS `cooperation`;
CREATE TABLE `cooperation` (
`doc_id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`nur_id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
PRIMARY KEY (`doc_id`, `nur_id`),
FOREIGN KEY (`doc_id`) REFERENCES `doctor` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`nur_id`) REFERENCES `nurse` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `nur_nur` (`nur_id`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of cooperation
-- ----------------------------
BEGIN;
INSERT INTO `cooperation` VALUES ('420101195512234832', '420101197411250381'), ('420101195709132831', '420101197411250381'), ('420101195709132831', '420101198212218931'), ('420101195709132831', '420101198303283712'), ('420101195804034811', '420101198303283712'), ('420101196211234832', '420101198504273823'), ('420101195512234832', '420101198901243821'), ('420101196303091232', '420101198901243821'), ('420101195512234832', '420101199004023920'), ('420101195709132831', '420101199004023920');
COMMIT;

-- ----------------------------
-- Table structure for doctor
-- ----------------------------
DROP TABLE IF EXISTS `doctor`;
CREATE TABLE `doctor` (
`id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sex`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`year_birth`  int(11) NULL DEFAULT NULL ,
`year_of_work`  int(11) NULL DEFAULT NULL ,
`deptname`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`infection`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`contact`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`workunit`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`workunit`) REFERENCES `hospital` (`name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `doc_hos` (`workunit`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of doctor
-- ----------------------------
BEGIN;
INSERT INTO `doctor` VALUES ('420101195512234832', '李庆年', '男', '1955', '40', '呼吸内科', '是', '13872649037', '武汉协和医院'), ('420101195709132831', '杨丽华', '女', '1957', '42', '感染性疾病科', '否', '13463820837', '武汉市中西医结合医院'), ('420101195804034811', '邓斌', '男', '1958', '40', '感染性疾病科', '否', '13474890376', '武汉大学人民医院'), ('420101196211234832', '龚作同', '男', '1962', '35', '感染性疾病科', '否', '15673802359', '武汉市中西医结合医院'), ('420101196303091232', '向龙奎', '男', '1963', '33', '心肺内科', '否', '15692034857', '武汉大学人民医院'), ('420101196708248392', '何力优', '男', '1967', '30', '呼吸内科', '否', '15693210273', '武汉大学中南医院'), ('420101197309282913', '胡积军', '男', '1973', '23', '心肺内科', '否', '15648395783', '武汉市中西医结合医院'), ('420101198507193847', '王自立', '男', '1985', '13', '感染性疾病科', '否', '13490483726', '湖北省中医院花园山院区');
COMMIT;

-- ----------------------------
-- Table structure for hosdata
-- ----------------------------
DROP TABLE IF EXISTS `hosdata`;
CREATE TABLE `hosdata` (
`time`  date NOT NULL DEFAULT '0000-00-00' ,
`treatment`  int(11) NULL DEFAULT NULL ,
`death`  int(11) NULL DEFAULT NULL ,
`confirmed`  int(11) NULL DEFAULT NULL ,
`suspected`  int(11) NULL DEFAULT NULL ,
`hosname`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
PRIMARY KEY (`time`, `hosname`),
FOREIGN KEY (`hosname`) REFERENCES `hospital` (`name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `data_hos` (`hosname`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of hosdata
-- ----------------------------
BEGIN;
INSERT INTO `hosdata` VALUES ('2020-02-02', '12', '1', '14', '12', '武汉协和医院'), ('2020-02-02', '13', '31', '14', '41', '武汉大学中南医院'), ('2020-02-02', '13', '21', '31', '21', '武汉大学人民医院'), ('2020-02-02', '31', '21', '42', '21', '武汉市中西医结合医院'), ('2020-02-02', '11', '22', '15', '65', '湖北省中医院花园山院区'), ('2020-02-03', '14', '21', '36', '25', '武汉协和医院'), ('2020-02-03', '22', '12', '21', '21', '武汉大学中南医院'), ('2020-02-03', '12', '12', '21', '34', '武汉大学人民医院'), ('2020-02-03', '24', '12', '32', '42', '武汉市中西医结合医院'), ('2020-02-03', '25', '12', '21', '54', '湖北省中医院花园山院区'), ('2020-02-04', '23', '21', '42', '21', '武汉协和医院'), ('2020-02-04', '21', '12', '32', '12', '武汉大学中南医院'), ('2020-02-04', '21', '53', '21', '23', '武汉大学人民医院'), ('2020-02-04', '16', '21', '21', '52', '武汉市中西医结合医院'), ('2020-02-04', '21', '32', '12', '14', '湖北省中医院花园山院区'), ('2020-02-05', '23', '12', '23', '32', '武汉协和医院'), ('2020-02-05', '21', '11', '22', '11', '武汉大学中南医院'), ('2020-02-05', '24', '31', '41', '21', '武汉大学人民医院'), ('2020-02-05', '22', '11', '31', '42', '武汉市中西医结合医院'), ('2020-02-05', '21', '22', '12', '22', '湖北省中医院花园山院区'), ('2020-02-06', '23', '15', '32', '21', '武汉协和医院'), ('2020-02-06', '16', '23', '31', '12', '武汉大学中南医院'), ('2020-02-06', '25', '21', '42', '23', '武汉大学人民医院'), ('2020-02-06', '21', '15', '21', '53', '武汉市中西医结合医院'), ('2020-02-06', '22', '34', '14', '53', '湖北省中医院花园山院区'), ('2020-02-07', '15', '22', '33', '25', '武汉协和医院'), ('2020-02-07', '11', '31', '21', '23', '武汉大学中南医院'), ('2020-02-07', '21', '42', '21', '52', '武汉大学人民医院'), ('2020-02-07', '22', '15', '41', '73', '武汉市中西医结合医院'), ('2020-02-07', '11', '42', '12', '54', '湖北省中医院花园山院区'), ('2020-02-08', '21', '34', '21', '42', '武汉协和医院'), ('2020-02-08', '12', '21', '14', '21', '武汉大学中南医院'), ('2020-02-08', '21', '21', '25', '42', '武汉大学人民医院'), ('2020-02-08', '14', '32', '12', '52', '武汉市中西医结合医院'), ('2020-02-08', '21', '21', '25', '16', '湖北省中医院花园山院区');
COMMIT;

-- ----------------------------
-- Table structure for hospital
-- ----------------------------
DROP TABLE IF EXISTS `hospital`;
CREATE TABLE `hospital` (
`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`location`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`rank`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`max`  int(11) NULL DEFAULT NULL ,
`respirator`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`name`),
FOREIGN KEY (`location`) REFERENCES `area` (`area_name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `hos_area` (`location`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of hospital
-- ----------------------------
BEGIN;
INSERT INTO `hospital` VALUES ('武汉协和医院', '江汉区', '三甲', '164', '15'), ('武汉大学中南医院', '武昌区', '三甲', '120', '9'), ('武汉大学人民医院', '武昌区', '三甲', '140', '7'), ('武汉市中西医结合医院', '硚口区', '三甲', '173', '12'), ('湖北省中医院花园山院区', '武昌区', '三甲', '117', '6');
COMMIT;

-- ----------------------------
-- Table structure for nurse
-- ----------------------------
DROP TABLE IF EXISTS `nurse`;
CREATE TABLE `nurse` (
`id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`year_of_work`  int(11) NULL DEFAULT NULL ,
`year_birth`  int(11) NULL DEFAULT NULL ,
`sex`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`workunit`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`workunit`) REFERENCES `hospital` (`name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `nur_hos` (`workunit`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of nurse
-- ----------------------------
BEGIN;
INSERT INTO `nurse` VALUES ('420101197411250381', '李丽', '20', '1974', '女', '湖北省中医院花园山院区'), ('420101198212218931', '韩梅', '15', '1982', '女', '武汉大学人民医院'), ('420101198303283712', '周华', '16', '1983', '女', '湖北省中医院花园山院区'), ('420101198504273823', '李艳', '13', '1985', '女', '武汉协和医院'), ('420101198901243821', '陈菊', '10', '1989', '女', '武汉协和医院'), ('420101199003193872', '龙勇', '7', '1990', '男', '武汉市中西医结合医院'), ('420101199004023920', '李凤梅', '5', '1990', '女', '武汉大学人民医院'), ('420101199310248321', '王涛', '6', '1993', '男', '武汉大学中南医院');
COMMIT;

-- ----------------------------
-- Table structure for patient
-- ----------------------------
DROP TABLE IF EXISTS `patient`;
CREATE TABLE `patient` (
`id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sex`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`year_birth`  int(11) NULL DEFAULT NULL ,
`treat_doc`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`treat_nur`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`treat_hos`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`location`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`timeofcon`  datetime NOT NULL ,
`timeofrec`  datetime NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`location`) REFERENCES `area` (`area_name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`treat_doc`) REFERENCES `doctor` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`treat_hos`) REFERENCES `hospital` (`name`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`treat_nur`) REFERENCES `nurse` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
INDEX `pat_doc` (`treat_doc`) USING BTREE ,
INDEX `pat_nur` (`treat_nur`) USING BTREE ,
INDEX `pat_hos` (`treat_hos`) USING BTREE ,
INDEX `pat_area` (`location`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

;

-- ----------------------------
-- Records of patient
-- ----------------------------
BEGIN;
INSERT INTO `patient` VALUES ('420101194703281325', '丁梅', '女', '1947', '420101195804034811', '420101199310248321', '武汉大学中南医院', '武昌区', '2020-02-02 09:31:23', '2020-02-08 09:35:37'), ('420101194912213912', '贺建国', '男', '1949', '420101196708248392', '420101199004023920', '湖北省中医院花园山院区', '武昌区', '2020-02-06 09:31:56', null), ('420101195006182931', '王国强', '男', '1950', '420101195804034811', '420101199004023920', '武汉大学人民医院', '武昌区', '2020-02-07 09:32:02', null), ('420101195603301843', '李兰', '女', '1956', '420101195709132831', '420101198212218931', '武汉大学中南医院', '江汉区', '2020-02-03 09:32:11', '2020-02-07 09:35:45'), ('420101196405281731', '段誉', '男', '1964', '420101197309282913', '420101199310248321', '湖北省中医院花园山院区', '江汉区', '2020-02-06 09:32:18', null), ('420101196803281741', '王才', '男', '1969', null, null, null, null, '2020-02-04 09:32:25', null), ('420101196909231481', '陈龙', '男', '1969', null, null, null, null, '2020-02-04 09:33:08', null), ('420101197104271932', '刘胜男', '女', '1971', '420101196708248392', '420101198303283712', '武汉大学中南医院', '江汉区', '2020-02-05 09:33:14', null), ('420101197302183721', '韩梅', '女', '1973', '420101195709132831', '420101198212218931', '武汉市中西医结合医院', '江汉区', '2020-02-06 09:33:19', null), ('420101197303233823', '赵基', '男', '1973', '420101196211234832', '420101197411250381', '武汉市中西医结合医院', '硚口区', '2020-02-08 09:33:25', null), ('420101197407174738', '李龙', '男', '1970', '420101195709132831', '420101198303283712', '武汉大学中南医院', '江汉区', '2020-02-04 09:33:33', null), ('420101197509142834', '贺信', '男', '1975', '420101198507193847', '420101199310248321', '武汉大学人民医院', '江汉区', '2020-02-06 09:33:39', null), ('420101198203121831', '唐生', '男', '1982', '420101196303091232', '420101198901243821', '湖北省中医院花园山院区', '江汉区', '2020-02-02 09:33:45', '2020-02-08 09:35:53'), ('420101198211211312', '陈工', '男', '1982', '420101196708248392', '420101199004023920', '湖北省中医院花园山院区', '硚口区', '2020-02-05 09:33:51', null), ('420101198212123912', '钱莉莉', '女', '1982', '420101198507193847', '420101199004023920', '武汉大学中南医院', '硚口区', '2020-05-05 09:33:56', null), ('420101198301193921', '唐国强', '男', '1983', '420101195709132831', '420101197411250381', '武汉协和医院', '江汉区', '2020-05-04 09:34:00', null), ('420101198506294831', '常胜', '男', '1985', '420101196708248392', '420101199004023920', null, null, '2020-02-02 09:34:03', '2020-02-08 09:36:02'), ('420101198612244821', '李博文', '男', '1986', '420101198507193847', '420101199003193872', '武汉市中西医结合医院', '江汉区', '2020-02-03 09:34:11', null), ('420101198706131342', '于德水', '男', '1987', '420101196303091232', '420101199004023920', '武汉市中西医结合医院', '硚口区', '2020-02-04 09:34:17', null), ('420101198711271384', '孙箜', '女', '1987', '420101196303091232', '420101198303283712', '湖北省中医院花园山院区', '硚口区', '2020-02-03 09:34:23', '2020-05-08 09:36:09'), ('420101198801278347', '韩信', '男', '1988', '420101198507193847', '420101199004023920', '湖北省中医院花园山院区', '硚口区', '2020-02-04 09:34:29', null), ('420101198811211921', '朱能', '男', '1988', '420101196211234832', '420101198504273823', '武汉市中西医结合医院', '江汉区', '2020-02-07 09:34:36', null), ('420101198902143818', '赵信', '男', '1989', '420101195804034811', '420101198504273823', '湖北省中医院花园山院区', '硚口区', '2020-02-04 09:34:43', null), ('420101198907211382', '陈琳琳', '女', '1989', null, null, null, null, '2020-02-08 09:34:48', null), ('420101198909121321', '陈磊', '男', '1989', '420101196211234832', '420101198901243821', '武汉市中西医结合医院', '硚口区', '2020-02-07 09:34:57', null), ('420101199204123941', '龙显', '男', '1992', null, null, null, null, '2020-02-04 09:35:05', null), ('420101199210231391', '陈惠', '男', '1992', '420101196303091232', '420101198504273823', '湖北省中医院花园山院区', '硚口区', '2020-02-03 09:35:10', null), ('420101199402271432', '王显', '男', '1994', '420101197309282913', '420101198901243821', '武汉市中西医结合医院', '硚口区', '2020-02-04 09:35:17', null), ('420101199501201321', '胡文', '男', '1995', '420101197309282913', '420101199004023920', '武汉市中西医结合医院', '硚口区', '2020-02-03 09:35:22', null), ('420101199901102931', '王强', '男', '1999', '420101198507193847', '420101199310248321', '武汉市中西医结合医院', '硚口区', '2020-02-04 09:35:28', null);
COMMIT;
