From c56a90ea416385c32168bd2b83c794e36092d5cd Mon Sep 17 00:00:00 2001 From: houxiao <houxiao@454eff88-639b-444f-9e54-f578c98de674> Date: 星期四, 16 三月 2017 11:01:09 +0800 Subject: [PATCH] update sql --- /dev/null | 312 ------------------------------- VisitFace/script/face_discern.mysql.txt | 288 ++++++++++++++++++++++++++++ 2 files changed, 288 insertions(+), 312 deletions(-) diff --git a/VisitFace/script/face_discern.mysql.sql b/VisitFace/script/face_discern.mysql.sql deleted file mode 100644 index e69de29..0000000 --- a/VisitFace/script/face_discern.mysql.sql +++ /dev/null diff --git a/VisitFace/script/face_discern.mysql.txt b/VisitFace/script/face_discern.mysql.txt new file mode 100644 index 0000000..8ff90aa --- /dev/null +++ b/VisitFace/script/face_discern.mysql.txt @@ -0,0 +1,288 @@ +/* +Navicat MySQL Data Transfer + +Source Server : 192.168.1.51 +Source Server Version : 50714 +Source Host : 192.168.1.51:3306 +Source Database : face_discern + +Target Server Type : MYSQL +Target Server Version : 50714 +File Encoding : 65001 + +Date: 2017-03-16 10:54:29 +*/ + +SET FOREIGN_KEY_CHECKS=0; + +-- ---------------------------- +-- Table structure for calling +-- ---------------------------- +DROP TABLE IF EXISTS `calling`; +CREATE TABLE `calling` ( + `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '绛惧埌浜嬩欢涓婚敭', + `call_dt` datetime DEFAULT NULL COMMENT '鍛煎彨鏃堕棿', + `caller_person_id` int(10) DEFAULT NULL COMMENT '鍛煎彨浜哄憳涓婚敭', + `callee_person_id` int(10) DEFAULT NULL COMMENT '琚懠鍙汉鍛樹富閿�', + `place_id` int(10) DEFAULT NULL COMMENT '鍛煎彨鍙戠敓鎵�鍦ㄨ澶�(鍦扮偣)', + PRIMARY KEY (`id`), + KEY `caller_person_id` (`caller_person_id`), + KEY `callee_person_id` (`callee_person_id`), + KEY `calling_ibfk_3` (`place_id`), + CONSTRAINT `calling_ibfk_1` FOREIGN KEY (`caller_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `calling_ibfk_2` FOREIGN KEY (`callee_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `calling_ibfk_3` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for checking +-- ---------------------------- +DROP TABLE IF EXISTS `checking`; +CREATE TABLE `checking` ( + `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '绛惧埌浜嬩欢涓婚敭', + `status_id` tinyint(2) DEFAULT NULL COMMENT '绛惧埌鐘舵��', + `status_dt` datetime DEFAULT NULL COMMENT '绛惧埌鏃堕棿', + `person_id` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + `place_id` int(32) DEFAULT NULL COMMENT '绛惧埌浣嶇疆', + PRIMARY KEY (`id`), + KEY `person_id` (`person_id`), + KEY `place_id` (`place_id`), + CONSTRAINT `checking_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `checking_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for checking_company +-- ---------------------------- +DROP TABLE IF EXISTS `checking_company`; +CREATE TABLE `checking_company` ( + `person_id` int(32) NOT NULL COMMENT '浜哄憳涓婚敭', + `company_id` int(32) NOT NULL COMMENT '鍏徃涓婚敭', + PRIMARY KEY (`person_id`,`company_id`), + KEY `company_id` (`company_id`), + CONSTRAINT `checking_company_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `checking_company_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for company +-- ---------------------------- +DROP TABLE IF EXISTS `company`; +CREATE TABLE `company` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '鍏徃涓婚敭', + `name` varchar(255) DEFAULT NULL COMMENT '鍏徃鍚嶇О', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for department +-- ---------------------------- +DROP TABLE IF EXISTS `department`; +CREATE TABLE `department` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '閮ㄩ棬涓婚敭', + `label` varchar(40) DEFAULT NULL COMMENT '閮ㄩ棬鍚嶇О', + `label_spell` char(20) DEFAULT NULL COMMENT '閮ㄩ棬棣栧瓧姣�', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for face_image +-- ---------------------------- +DROP TABLE IF EXISTS `face_image`; +CREATE TABLE `face_image` ( + `face_id` int(10) NOT NULL COMMENT '鍛樺伐鍥剧墖涓婚敭', + `register_company_id` int(10) NOT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', + `person_id` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + `image_path` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', + `image_type_id` int(10) DEFAULT NULL COMMENT '鍥剧墖绫诲瀷', + PRIMARY KEY (`face_id`,`register_company_id`), + KEY `register_company_id` (`register_company_id`), + KEY `person_id` (`person_id`), + CONSTRAINT `face_image_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`), + CONSTRAINT `face_image_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for image +-- ---------------------------- +DROP TABLE IF EXISTS `image`; +CREATE TABLE `image` ( + `person_id` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + `image_type` varchar(32) DEFAULT NULL COMMENT '鍥剧墖绫诲瀷', + `image_path` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', + KEY `person_id` (`person_id`), + CONSTRAINT `image_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for person +-- ---------------------------- +DROP TABLE IF EXISTS `person`; +CREATE TABLE `person` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '浜哄憳涓婚敭', + `name` varchar(40) DEFAULT NULL COMMENT '浜哄憳鍚嶅瓧', + `name_spell` char(2) DEFAULT NULL COMMENT '浜哄憳鍚嶉瀛楁瘝', + `gender` tinyint(1) DEFAULT NULL COMMENT '鎬у埆', + `country` char(3) DEFAULT NULL COMMENT '鍥界睄', + `province` char(20) DEFAULT NULL COMMENT '鐪�', + `city` char(20) DEFAULT NULL COMMENT '甯�', + `birthday` date DEFAULT NULL COMMENT '鍑虹敓鏃ユ湡', + `phone` char(12) DEFAULT NULL COMMENT '搴ф満鐢佃瘽', + `mobile_phone` char(15) DEFAULT NULL COMMENT '绉诲姩鐢佃瘽', + `identity_type` char(40) DEFAULT NULL COMMENT '璇佷欢绫诲瀷', + `identify` varchar(255) DEFAULT NULL COMMENT '璇佷欢缂栧彿', + `company_id` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', + `department_id` int(10) DEFAULT NULL COMMENT '閮ㄩ棬涓婚敭', + `post_id` int(10) DEFAULT NULL COMMENT '鑱屽姟涓婚敭', + `remark` varchar(255) DEFAULT NULL COMMENT '浜哄憳澶囨敞娑堟伅', + `warning` tinyint(1) DEFAULT NULL COMMENT '鍗遍櫓浜哄憳璀﹀憡娑堟伅', + PRIMARY KEY (`id`), + KEY `company_id` (`company_id`), + KEY `post_id` (`post_id`), + KEY `department_id` (`department_id`), + CONSTRAINT `person_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`), + CONSTRAINT `person_ibfk_3` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`), + CONSTRAINT `person_ibfk_4` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for person_ext +-- ---------------------------- +DROP TABLE IF EXISTS `person_ext`; +CREATE TABLE `person_ext` ( + `person_id` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + KEY `person_id` (`person_id`), + CONSTRAINT `person_ext_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for place +-- ---------------------------- +DROP TABLE IF EXISTS `place`; +CREATE TABLE `place` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁惧涓婚敭', + `company_id` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', + `label` varchar(40) DEFAULT NULL COMMENT '璁惧鍦扮偣', + `authorization_id` varchar(100) NOT NULL COMMENT '璁惧鎺堟潈鐮�', + PRIMARY KEY (`id`), + KEY `company_id` (`company_id`), + CONSTRAINT `place_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for post +-- ---------------------------- +DROP TABLE IF EXISTS `post`; +CREATE TABLE `post` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '鑱屼綅涓婚敭', + `label` varchar(40) DEFAULT NULL COMMENT '鑱屼綅鍚嶇О', + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit +-- ---------------------------- +DROP TABLE IF EXISTS `visit`; +CREATE TABLE `visit` ( + `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '璁块棶浜嬩欢涓婚敭', + `visitor_person_id` int(32) DEFAULT NULL COMMENT '璁块棶浜哄憳涓婚敭', + `visitee_person_id` int(32) DEFAULT NULL COMMENT '琚闂汉鍛樹富閿�', + `matter_id` int(32) DEFAULT NULL COMMENT '璁块棶浜嬬敱涓婚敭', + `remark` varchar(255) DEFAULT NULL COMMENT '璁块棶澶囨敞', + `last_status` int(40) DEFAULT NULL COMMENT '鏈�鏂扮殑璁块棶鐘舵��', + `start_time` datetime DEFAULT NULL COMMENT '鏉ヨ鏃堕棿', + `last_time` datetime DEFAULT NULL COMMENT '绛剧鏃堕棿', + `visitor_company_id` int(10) DEFAULT NULL, + `visitee_company_id` int(10) DEFAULT NULL, + `visitor_type_id` int(10) DEFAULT NULL COMMENT '璁垮绫诲瀷', + `warning` tinyint(1) DEFAULT NULL COMMENT '褰撳墠璁块棶鐨勮鍛婄姸鎬�', + PRIMARY KEY (`id`), + KEY `visitor_person_id` (`visitor_person_id`), + KEY `visitee_person_id` (`visitee_person_id`), + KEY `matter_id` (`matter_id`), + KEY `visit_ibfk_4` (`visitor_type_id`), + KEY `visitor_company_id` (`visitor_company_id`), + KEY `visitee_company_id` (`visitee_company_id`), + CONSTRAINT `visit_ibfk_1` FOREIGN KEY (`visitor_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_ibfk_2` FOREIGN KEY (`visitee_person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_ibfk_3` FOREIGN KEY (`matter_id`) REFERENCES `visit_matter` (`id`), + CONSTRAINT `visit_ibfk_4` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`), + CONSTRAINT `visit_ibfk_5` FOREIGN KEY (`visitor_company_id`) REFERENCES `company` (`id`), + CONSTRAINT `visit_ibfk_6` FOREIGN KEY (`visitee_company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visiting +-- ---------------------------- +DROP TABLE IF EXISTS `visiting`; +CREATE TABLE `visiting` ( + `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '璁块棶浜嬩欢娴佹按id', + `visit_id` int(10) DEFAULT NULL COMMENT '璁块棶浜嬩欢id', + `status_id` tinyint(2) DEFAULT NULL COMMENT '褰撳墠璁块棶鐘舵��', + `status_dt` datetime DEFAULT NULL COMMENT '鏇存柊鐘舵�佹椂闂�', + `place_id` int(10) DEFAULT NULL COMMENT '璁惧浣嶇疆涓婚敭', + PRIMARY KEY (`id`), + KEY `visit_id` (`visit_id`), + KEY `place_id` (`place_id`), + CONSTRAINT `visiting_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visit` (`id`), + CONSTRAINT `visiting_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `place` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visitor_type +-- ---------------------------- +DROP TABLE IF EXISTS `visitor_type`; +CREATE TABLE `visitor_type` ( + `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '璁垮绫诲瀷涓婚敭', + `label` varchar(32) DEFAULT NULL COMMENT '璁垮绫诲瀷', + `register_company_id` int(32) DEFAULT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', + `available` tinyint(1) DEFAULT NULL COMMENT '浜哄憳涓婚敭', + PRIMARY KEY (`id`), + KEY `register_company_id` (`register_company_id`), + CONSTRAINT `visitor_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit_matter +-- ---------------------------- +DROP TABLE IF EXISTS `visit_matter`; +CREATE TABLE `visit_matter` ( + `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '璁块棶浜嬬敱涓婚敭', + `company_id` int(32) DEFAULT NULL COMMENT '鍏徃涓婚敭', + `label` varchar(40) NOT NULL COMMENT '璁块棶浜嬬敱', + `available` tinyint(1) DEFAULT NULL COMMENT '鏄惁鏄�氱敤浜嬬敱', + PRIMARY KEY (`id`), + KEY `company_id` (`company_id`), + CONSTRAINT `visit_matter_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit_person_type +-- ---------------------------- +DROP TABLE IF EXISTS `visit_person_type`; +CREATE TABLE `visit_person_type` ( + `register_company_id` int(11) NOT NULL, + `person_id` int(11) NOT NULL, + `visitor_type_id` int(11) NOT NULL, + PRIMARY KEY (`register_company_id`,`person_id`,`visitor_type_id`), + KEY `person_id` (`person_id`) USING BTREE, + KEY `visitor_type_id` (`visitor_type_id`) USING BTREE, + CONSTRAINT `visit_person_type_ibfk_1` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`), + CONSTRAINT `visit_person_type_ibfk_2` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_person_type_ibfk_3` FOREIGN KEY (`visitor_type_id`) REFERENCES `visitor_type` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ---------------------------- +-- Table structure for visit_stat +-- ---------------------------- +DROP TABLE IF EXISTS `visit_stat`; +CREATE TABLE `visit_stat` ( + `person_id` int(10) NOT NULL COMMENT '浜哄憳涓婚敭', + `register_company_id` int(10) NOT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', + `total_count` int(20) DEFAULT NULL COMMENT '鏉ヨ鍏徃娆℃暟', + PRIMARY KEY (`person_id`,`register_company_id`), + KEY `person_id` (`person_id`), + KEY `register_company_id` (`register_company_id`), + CONSTRAINT `visit_stat_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`), + CONSTRAINT `visit_stat_ibfk_2` FOREIGN KEY (`register_company_id`) REFERENCES `company` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; diff --git a/VisitFace/script/face_discern.sql b/VisitFace/script/face_discern.sql deleted file mode 100644 index 73774c2..0000000 --- a/VisitFace/script/face_discern.sql +++ /dev/null @@ -1,183 +0,0 @@ - -#-----------------------------鎴戞槸鍒嗗壊绾�------------------------------------------- - -CREATE TABLE t_company( -ID INT(10) PRIMARY KEY COMMENT '鍏徃涓婚敭', -NAME VARCHAR(255) COMMENT '鍏徃鍚嶇О' -) - - -CREATE TABLE t_place( -ID INT(10) PRIMARY KEY COMMENT '璁惧涓婚敭', -companyID INT(10) COMMENT '鍏徃涓婚敭', -label VARCHAR(40) COMMENT '璁惧鍦扮偣', -authorizationID VARCHAR(40) COMMENT '璁惧鎺堟潈鐮�', -CONSTRAINT con_place_companyID FOREIGN KEY(companyID) REFERENCES t_company(ID) -) - -CREATE TABLE t_department( -ID INT(10) PRIMARY KEY COMMENT '閮ㄩ棬涓婚敭', -label VARCHAR(40) COMMENT '閮ㄩ棬鍚嶇О', -labelSpell CHAR(2) COMMENT '閮ㄩ棬棣栧瓧姣�' -) - - -CREATE TABLE t_post( -ID INT(10) PRIMARY KEY COMMENT '鑱屼綅涓婚敭', -label VARCHAR(40) COMMENT '鑱屼綅鍚嶇О' -) - -CREATE TABLE t_person( -ID INT(10) PRIMARY KEY COMMENT '浜哄憳涓婚敭', -NAME VARCHAR(40) COMMENT '浜哄憳鍚嶅瓧', -nameSpell CHAR(2) COMMENT '浜哄憳鍚嶉瀛楁瘝', -gender TINYINT(1) COMMENT '鎬у埆', -country CHAR(20) COMMENT '鍥界睄', -province CHAR(20) COMMENT '鐪�', -city CHAR(20) COMMENT '甯�', -birthday DATE COMMENT '鍑虹敓鏃ユ湡', -phone CHAR(12) COMMENT '搴ф満鐢佃瘽', -mobilePhone CHAR(15) COMMENT '绉诲姩鐢佃瘽', -identityType CHAR(40) COMMENT '璇佷欢绫诲瀷', -identify VARCHAR(255) COMMENT '璇佷欢缂栧彿', -companyID INT(10) COMMENT '鍏徃涓婚敭', -departmentID INT(10) COMMENT '閮ㄩ棬涓婚敭', -postID INT(10) COMMENT '鑱屽姟涓婚敭', -remark VARCHAR(255) COMMENT '浜哄憳澶囨敞娑堟伅', -warning VARCHAR(255) COMMENT '鍗遍櫓浜哄憳璀﹀憡娑堟伅', -CONSTRAINT con_person_companyID FOREIGN KEY(companyID) REFERENCES t_company(ID), -CONSTRAINT con_person_departmentID FOREIGN KEY(departmentID) REFERENCES t_department(ID), -CONSTRAINT con_person_postID FOREIGN KEY(postID) REFERENCES t_post(ID) -) - - -CREATE TABLE t_image( -personID INT(10) COMMENT '浜哄憳涓婚敭', -imageTye VARCHAR(32) COMMENT '鍥剧墖绫诲瀷', -imagePath VARCHAR(255) COMMENT '鍥剧墖鍦板潃', -CONSTRAINT con_image_personID FOREIGN KEY(personID) REFERENCES t_person(ID) -) - -CREATE TABLE t_visitFace( -ID INT(10) PRIMARY KEY COMMENT '璁垮鍥剧墖涓婚敭', -personID INT(32) COMMENT '浜哄憳涓婚敭', -imagePath VARCHAR(255) COMMENT '鍥剧墖鍦板潃', -CONSTRAINT con_visitFace_personID FOREIGN KEY(personID) REFERENCES t_person(ID) -) - - - -CREATE TABLE t_employeeFace( -ID INT(32) PRIMARY KEY COMMENT '鍛樺伐鍥剧墖涓婚敭', -registerCompanyID INT(32) COMMENT '娉ㄥ唽鍏徃涓婚敭', -personID INT(32) COMMENT '浜哄憳涓婚敭', -imagePath VARCHAR(255) COMMENT '鍥剧墖鍦板潃', -CONSTRAINT con_employeeFace_personID FOREIGN KEY(personID) REFERENCES t_person(ID), -CONSTRAINT con_employeeFace_registerCompanyID FOREIGN KEY(registerCompanyID) REFERENCES t_company(ID) -) - - -CREATE TABLE t_visitorType( -ID INT(32) PRIMARY KEY COMMENT '璁垮绫诲瀷涓婚敭', -label VARCHAR(32) COMMENT '璁垮绫诲瀷', -registerCompanyID INT(32) COMMENT '娉ㄥ唽鍏徃涓婚敭', -personID INT(32) COMMENT '浜哄憳涓婚敭', -CONSTRAINT con_visitorType_personID FOREIGN KEY(personID) REFERENCES t_person(ID), -CONSTRAINT con_visitorType_registerCompanyID FOREIGN KEY(registerCompanyID) REFERENCES t_company(ID) -) - -CREATE TABLE t_visitStat( -personID INT(32) COMMENT '浜哄憳涓婚敭', -registerCompanyID INT(32) COMMENT '娉ㄥ唽鍏徃涓婚敭', -totalCount INTEGER(20) COMMENT '鏉ヨ鍏徃娆℃暟', -CONSTRAINT con_visitStat_personID FOREIGN KEY(personID) REFERENCES t_person(ID), -CONSTRAINT con_visitStat_registerCompanyID FOREIGN KEY(registerCompanyID) REFERENCES t_company(ID) -) - -CREATE TABLE t_checkingCompany( -personID INT(32) COMMENT '浜哄憳涓婚敭', -companyID INT(32) COMMENT '鍏徃涓婚敭', -CONSTRAINT con_checkingCompany_personID FOREIGN KEY(personID) REFERENCES t_person(ID), -CONSTRAINT con_checkingCompany_registerCompanyID FOREIGN KEY(companyID) REFERENCES t_company(ID) -) - - -CREATE TABLE t_personExt( -personID INT(32) COMMENT '浜哄憳涓婚敭', -CONSTRAINT con_personExt_personID FOREIGN KEY(personID) REFERENCES t_person(ID) -) - - - -CREATE TABLE t_checking( -ID INT(32) PRIMARY KEY COMMENT '绛惧埌浜嬩欢涓婚敭', -statusID TINYINT(2) COMMENT '绛惧埌鐘舵��', -statusDT DATETIME COMMENT '绛惧埌鏃堕棿', -personID INT(32) COMMENT '浜哄憳涓婚敭', -placeID INT(32) COMMENT '绛惧埌浣嶇疆', -CONSTRAINT con_checking_personID FOREIGN KEY(personID) REFERENCES t_person(ID), -CONSTRAINT con_checking_placeID FOREIGN KEY(placeID) REFERENCES t_place(ID) -) - - -CREATE TABLE t_calling( -ID INT(32) PRIMARY KEY COMMENT '绛惧埌浜嬩欢涓婚敭', -callDT DATETIME COMMENT '鍛煎彨鏃堕棿', -callerPersonID INT(32) COMMENT '鍛煎彨浜哄憳涓婚敭', -calleePersonID INT(32) COMMENT '琚懠鍙汉鍛樹富閿�', -CONSTRAINT con_calling_callerPersonID FOREIGN KEY(callerPersonID) REFERENCES t_person(ID), -CONSTRAINT con_calling_calleePersonID FOREIGN KEY(calleePersonID) REFERENCES t_person(ID) -) - - - -#----------------------------鍚庨潰涓変釜------------------------------------------------- - - -CREATE TABLE t_visitMatter( -ID INT(32) PRIMARY KEY COMMENT '璁块棶浜嬬敱涓婚敭', -companyID INT(32) COMMENT '鍏徃涓婚敭', -label VARCHAR(40) COMMENT '璁块棶浜嬬敱', -available TINYINT(1) COMMENT '鏄惁鏄�氱敤浜嬬敱', -CONSTRAINT con_visitMatter_companyID FOREIGN KEY(companyID) REFERENCES t_company(ID) -) - - - -CREATE TABLE t_visit( -ID INT(32) PRIMARY KEY COMMENT '璁块棶浜嬩欢涓婚敭', -visitorPersonID INT(32) COMMENT '璁块棶浜哄憳涓婚敭', -visiteePersonID INT(32) COMMENT '琚闂汉鍛樹富閿�', -matterID INT(32) COMMENT '璁块棶浜嬬敱涓婚敭', -remark VARCHAR(255) COMMENT '璁块棶澶囨敞', -lastStatus INTEGER(40) COMMENT '鏈�鏂扮殑璁块棶鐘舵��', -startTime DATETIME COMMENT '鏉ヨ鏃堕棿', -lastTime DATETIME COMMENT '绛剧鏃堕棿', -visitorTypeLabel VARCHAR(32) COMMENT '璁垮绫诲瀷', -warning VARCHAR(255) COMMENT '褰撳墠璁块棶鐨勮鍛婄姸鎬�', -CONSTRAINT con_visit_visitorPersonID FOREIGN KEY(visitorPersonID) REFERENCES t_person(ID), -CONSTRAINT con_visit_visiteePersonID FOREIGN KEY(visiteePersonID) REFERENCES t_person(ID), -CONSTRAINT con_visit_matterID FOREIGN KEY(matterID) REFERENCES t_visitMatter(ID) -) - - - -CREATE TABLE t_visiting( -ID INT(32) PRIMARY KEY COMMENT '璁块棶浜嬩欢娴佹按ID', -visitID INT(32) COMMENT '璁块棶浜嬩欢ID', -statusID DATETIME COMMENT '褰撳墠璁块棶鐘舵��', -statusDT DATETIME COMMENT '鏇存柊鐘舵�佹椂闂�', -placeID INT(32) COMMENT '璁惧浣嶇疆涓婚敭', -CONSTRAINT con_visiting_placeID FOREIGN KEY(placeID) REFERENCES t_place(ID) -) - - - - - - - - - - - diff --git "a/VisitFace/script/\345\257\274\345\207\272\347\232\204face_discern.sql" "b/VisitFace/script/\345\257\274\345\207\272\347\232\204face_discern.sql" deleted file mode 100644 index 4e61de1..0000000 --- "a/VisitFace/script/\345\257\274\345\207\272\347\232\204face_discern.sql" +++ /dev/null @@ -1,312 +0,0 @@ -/* -SQLyog Ultimate v12.09 (64 bit) -MySQL - 5.7.17 : Database - face_discern -********************************************************************* -*/ - -/*!40101 SET NAMES utf8 */; - -/*!40101 SET SQL_MODE=''*/; - -/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; -/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; -/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; -/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -CREATE DATABASE /*!32312 IF NOT EXISTS*/`face_discern` /*!40100 DEFAULT CHARACTER SET utf8 */; - -USE `face_discern`; - -/*Table structure for table `t_calling` */ - -DROP TABLE IF EXISTS `t_calling`; - -CREATE TABLE `t_calling` ( - `ID` int(32) NOT NULL COMMENT '绛惧埌浜嬩欢涓婚敭', - `callDT` datetime DEFAULT NULL COMMENT '鍛煎彨鏃堕棿', - `callerPersonID` int(32) DEFAULT NULL COMMENT '鍛煎彨浜哄憳涓婚敭', - `calleePersonID` int(32) DEFAULT NULL COMMENT '琚懠鍙汉鍛樹富閿�', - PRIMARY KEY (`ID`), - KEY `con_calling_callerPersonID` (`callerPersonID`), - KEY `con_calling_calleePersonID` (`calleePersonID`), - CONSTRAINT `con_calling_calleePersonID` FOREIGN KEY (`calleePersonID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_calling_callerPersonID` FOREIGN KEY (`callerPersonID`) REFERENCES `t_person` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_calling` */ - -/*Table structure for table `t_checking` */ - -DROP TABLE IF EXISTS `t_checking`; - -CREATE TABLE `t_checking` ( - `ID` int(32) NOT NULL COMMENT '绛惧埌浜嬩欢涓婚敭', - `statusID` tinyint(2) DEFAULT NULL COMMENT '绛惧埌鐘舵��', - `statusDT` datetime DEFAULT NULL COMMENT '绛惧埌鏃堕棿', - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - `placeID` int(32) DEFAULT NULL COMMENT '绛惧埌浣嶇疆', - PRIMARY KEY (`ID`), - KEY `con_checking_personID` (`personID`), - KEY `con_checking_placeID` (`placeID`), - CONSTRAINT `con_checking_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_checking_placeID` FOREIGN KEY (`placeID`) REFERENCES `t_place` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_checking` */ - -/*Table structure for table `t_checkingcompany` */ - -DROP TABLE IF EXISTS `t_checkingcompany`; - -CREATE TABLE `t_checkingcompany` ( - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - `companyID` int(32) DEFAULT NULL COMMENT '鍏徃涓婚敭', - KEY `con_checkingCompany_personID` (`personID`), - KEY `con_checkingCompany_registerCompanyID` (`companyID`), - CONSTRAINT `con_checkingCompany_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_checkingCompany_registerCompanyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_checkingcompany` */ - -/*Table structure for table `t_company` */ - -DROP TABLE IF EXISTS `t_company`; - -CREATE TABLE `t_company` ( - `ID` int(10) NOT NULL COMMENT '鍏徃涓婚敭', - `NAME` varchar(255) DEFAULT NULL COMMENT '鍏徃鍚嶇О', - PRIMARY KEY (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_company` */ - -/*Table structure for table `t_department` */ - -DROP TABLE IF EXISTS `t_department`; - -CREATE TABLE `t_department` ( - `ID` int(10) NOT NULL COMMENT '閮ㄩ棬涓婚敭', - `label` varchar(40) DEFAULT NULL COMMENT '閮ㄩ棬鍚嶇О', - `labelSpell` char(2) DEFAULT NULL COMMENT '閮ㄩ棬棣栧瓧姣�', - PRIMARY KEY (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_department` */ - -/*Table structure for table `t_employeeface` */ - -DROP TABLE IF EXISTS `t_employeeface`; - -CREATE TABLE `t_employeeface` ( - `ID` int(32) NOT NULL COMMENT '鍛樺伐鍥剧墖涓婚敭', - `registerCompanyID` int(32) DEFAULT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - `imagePath` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', - PRIMARY KEY (`ID`), - KEY `con_employeeFace_personID` (`personID`), - KEY `con_employeeFace_registerCompanyID` (`registerCompanyID`), - CONSTRAINT `con_employeeFace_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_employeeFace_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_employeeface` */ - -/*Table structure for table `t_image` */ - -DROP TABLE IF EXISTS `t_image`; - -CREATE TABLE `t_image` ( - `personID` int(10) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - `imageTye` varchar(32) DEFAULT NULL COMMENT '鍥剧墖绫诲瀷', - `imagePath` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', - KEY `con_image_personID` (`personID`), - CONSTRAINT `con_image_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_image` */ - -/*Table structure for table `t_person` */ - -DROP TABLE IF EXISTS `t_person`; - -CREATE TABLE `t_person` ( - `ID` int(10) NOT NULL COMMENT '浜哄憳涓婚敭', - `NAME` varchar(40) DEFAULT NULL COMMENT '浜哄憳鍚嶅瓧', - `nameSpell` char(2) DEFAULT NULL COMMENT '浜哄憳鍚嶉瀛楁瘝', - `gender` tinyint(1) DEFAULT NULL COMMENT '鎬у埆', - `country` char(20) DEFAULT NULL COMMENT '鍥界睄', - `province` char(20) DEFAULT NULL COMMENT '鐪�', - `city` char(20) DEFAULT NULL COMMENT '甯�', - `birthday` date DEFAULT NULL COMMENT '鍑虹敓鏃ユ湡', - `phone` char(12) DEFAULT NULL COMMENT '搴ф満鐢佃瘽', - `mobilePhone` char(15) DEFAULT NULL COMMENT '绉诲姩鐢佃瘽', - `identityType` char(40) DEFAULT NULL COMMENT '璇佷欢绫诲瀷', - `identify` varchar(255) DEFAULT NULL COMMENT '璇佷欢缂栧彿', - `companyID` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', - `departmentID` int(10) DEFAULT NULL COMMENT '閮ㄩ棬涓婚敭', - `postID` int(10) DEFAULT NULL COMMENT '鑱屽姟涓婚敭', - `remark` varchar(255) DEFAULT NULL COMMENT '浜哄憳澶囨敞娑堟伅', - `warning` varchar(255) DEFAULT NULL COMMENT '鍗遍櫓浜哄憳璀﹀憡娑堟伅', - PRIMARY KEY (`ID`), - KEY `con_person_companyID` (`companyID`), - KEY `con_person_departmentID` (`departmentID`), - KEY `con_person_postID` (`postID`), - CONSTRAINT `con_person_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`), - CONSTRAINT `con_person_departmentID` FOREIGN KEY (`departmentID`) REFERENCES `t_department` (`ID`), - CONSTRAINT `con_person_postID` FOREIGN KEY (`postID`) REFERENCES `t_post` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_person` */ - -/*Table structure for table `t_personext` */ - -DROP TABLE IF EXISTS `t_personext`; - -CREATE TABLE `t_personext` ( - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - KEY `con_personExt_personID` (`personID`), - CONSTRAINT `con_personExt_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_personext` */ - -/*Table structure for table `t_place` */ - -DROP TABLE IF EXISTS `t_place`; - -CREATE TABLE `t_place` ( - `ID` int(10) NOT NULL COMMENT '璁惧涓婚敭', - `companyID` int(10) DEFAULT NULL COMMENT '鍏徃涓婚敭', - `label` varchar(40) DEFAULT NULL COMMENT '璁惧鍦扮偣', - `authorizationID` varchar(40) DEFAULT NULL COMMENT '璁惧鎺堟潈鐮�', - PRIMARY KEY (`ID`), - KEY `con_place_companyID` (`companyID`), - CONSTRAINT `con_place_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_place` */ - -/*Table structure for table `t_post` */ - -DROP TABLE IF EXISTS `t_post`; - -CREATE TABLE `t_post` ( - `ID` int(10) NOT NULL COMMENT '鑱屼綅涓婚敭', - `label` varchar(40) DEFAULT NULL COMMENT '鑱屼綅鍚嶇О', - PRIMARY KEY (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_post` */ - -/*Table structure for table `t_visit` */ - -DROP TABLE IF EXISTS `t_visit`; - -CREATE TABLE `t_visit` ( - `ID` int(32) NOT NULL COMMENT '璁块棶浜嬩欢涓婚敭', - `visitorPersonID` int(32) DEFAULT NULL COMMENT '璁块棶浜哄憳涓婚敭', - `visiteePersonID` int(32) DEFAULT NULL COMMENT '琚闂汉鍛樹富閿�', - `matterID` int(32) DEFAULT NULL COMMENT '璁块棶浜嬬敱涓婚敭', - `remark` varchar(255) DEFAULT NULL COMMENT '璁块棶澶囨敞', - `lastStatus` int(40) DEFAULT NULL COMMENT '鏈�鏂扮殑璁块棶鐘舵��', - `startTime` datetime DEFAULT NULL COMMENT '鏉ヨ鏃堕棿', - `lastTime` datetime DEFAULT NULL COMMENT '绛剧鏃堕棿', - `visitorTypeLabel` varchar(32) DEFAULT NULL COMMENT '璁垮绫诲瀷', - `warning` varchar(255) DEFAULT NULL COMMENT '褰撳墠璁块棶鐨勮鍛婄姸鎬�', - PRIMARY KEY (`ID`), - KEY `con_visit_visitorPersonID` (`visitorPersonID`), - KEY `con_visit_visiteePersonID` (`visiteePersonID`), - KEY `con_visit_matterID` (`matterID`), - CONSTRAINT `con_visit_matterID` FOREIGN KEY (`matterID`) REFERENCES `t_visitmatter` (`ID`), - CONSTRAINT `con_visit_visiteePersonID` FOREIGN KEY (`visiteePersonID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_visit_visitorPersonID` FOREIGN KEY (`visitorPersonID`) REFERENCES `t_person` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_visit` */ - -/*Table structure for table `t_visitface` */ - -DROP TABLE IF EXISTS `t_visitface`; - -CREATE TABLE `t_visitface` ( - `ID` int(10) NOT NULL COMMENT '璁垮鍥剧墖涓婚敭', - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - `imagePath` varchar(255) DEFAULT NULL COMMENT '鍥剧墖鍦板潃', - PRIMARY KEY (`ID`), - KEY `con_visitFace_personID` (`personID`), - CONSTRAINT `con_visitFace_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_visitface` */ - -/*Table structure for table `t_visiting` */ - -DROP TABLE IF EXISTS `t_visiting`; - -CREATE TABLE `t_visiting` ( - `ID` int(32) NOT NULL COMMENT '璁块棶浜嬩欢娴佹按ID', - `visitID` int(32) DEFAULT NULL COMMENT '璁块棶浜嬩欢ID', - `statusID` datetime DEFAULT NULL COMMENT '褰撳墠璁块棶鐘舵��', - `statusDT` datetime DEFAULT NULL COMMENT '鏇存柊鐘舵�佹椂闂�', - `placeID` int(32) DEFAULT NULL COMMENT '璁惧浣嶇疆涓婚敭', - PRIMARY KEY (`ID`), - KEY `con_visiting_placeID` (`placeID`), - CONSTRAINT `con_visiting_placeID` FOREIGN KEY (`placeID`) REFERENCES `t_place` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_visiting` */ - -/*Table structure for table `t_visitmatter` */ - -DROP TABLE IF EXISTS `t_visitmatter`; - -CREATE TABLE `t_visitmatter` ( - `ID` int(32) NOT NULL COMMENT '璁块棶浜嬬敱涓婚敭', - `companyID` int(32) DEFAULT NULL COMMENT '鍏徃涓婚敭', - `label` varchar(40) DEFAULT NULL COMMENT '璁块棶浜嬬敱', - `available` tinyint(1) DEFAULT NULL COMMENT '鏄惁鏄�氱敤浜嬬敱', - PRIMARY KEY (`ID`), - KEY `con_visitMatter_companyID` (`companyID`), - CONSTRAINT `con_visitMatter_companyID` FOREIGN KEY (`companyID`) REFERENCES `t_company` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_visitmatter` */ - -/*Table structure for table `t_visitortype` */ - -DROP TABLE IF EXISTS `t_visitortype`; - -CREATE TABLE `t_visitortype` ( - `ID` int(32) NOT NULL COMMENT '璁垮绫诲瀷涓婚敭', - `label` varchar(32) DEFAULT NULL COMMENT '璁垮绫诲瀷', - `registerCompanyID` int(32) DEFAULT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - PRIMARY KEY (`ID`), - KEY `con_visitorType_personID` (`personID`), - KEY `con_visitorType_registerCompanyID` (`registerCompanyID`), - CONSTRAINT `con_visitorType_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_visitorType_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_visitortype` */ - -/*Table structure for table `t_visitstat` */ - -DROP TABLE IF EXISTS `t_visitstat`; - -CREATE TABLE `t_visitstat` ( - `personID` int(32) DEFAULT NULL COMMENT '浜哄憳涓婚敭', - `registerCompanyID` int(32) DEFAULT NULL COMMENT '娉ㄥ唽鍏徃涓婚敭', - `totalCount` int(20) DEFAULT NULL COMMENT '鏉ヨ鍏徃娆℃暟', - KEY `con_visitStat_personID` (`personID`), - KEY `con_visitStat_registerCompanyID` (`registerCompanyID`), - CONSTRAINT `con_visitStat_personID` FOREIGN KEY (`personID`) REFERENCES `t_person` (`ID`), - CONSTRAINT `con_visitStat_registerCompanyID` FOREIGN KEY (`registerCompanyID`) REFERENCES `t_company` (`ID`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -/*Data for the table `t_visitstat` */ - -/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; -/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; -/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; -/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Gitblit v1.8.0