/*
SQLyog Ultimate v8.32 
MySQL - 8.0.11 : Database - infodb
*********************************************************************
*/

/*!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*/`infodb` /*!40100 DEFAULT CHARACTER SET gbk */;

USE `infodb`;

/*Table structure for table `department` */

DROP TABLE IF EXISTS `department`;

CREATE TABLE `department` (
  `ID` varchar(20) NOT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  `MANAGER` varchar(20) DEFAULT NULL,
  `INTRO` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `department` */

insert  into `department`(`ID`,`NAME`,`MANAGER`,`INTRO`) values ('1','财务部','洛洛','成立于2015年');

/*Table structure for table `edu_level` */

DROP TABLE IF EXISTS `edu_level`;

CREATE TABLE `edu_level` (
  `CODE` varchar(20) NOT NULL,
  `DESCRIPTION` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `edu_level` */

insert  into `edu_level`(`CODE`,`DESCRIPTION`) values ('0','本科'),('1','硕士'),('2','博士及博士后');

/*Table structure for table `job` */

DROP TABLE IF EXISTS `job`;

CREATE TABLE `job` (
  `CODE` varchar(20) NOT NULL,
  `DESCRIPTION` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `job` */

insert  into `job`(`CODE`,`DESCRIPTION`) values ('0','会计'),('1','总监');

/*Table structure for table `person` */

DROP TABLE IF EXISTS `person`;

CREATE TABLE `person` (
  `ID` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL,
  `PASSWD` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,
  `AUTHORITY` varchar(20) DEFAULT NULL,
  `NAME` varchar(20) DEFAULT NULL,
  `SEX` varchar(20) DEFAULT NULL,
  `BIRTHDAY` varchar(20) DEFAULT NULL,
  `DEPARTMENT` varchar(20) DEFAULT NULL,
  `JOB` varchar(20) DEFAULT NULL,
  `EDU_LEVEL` varchar(20) DEFAULT NULL,
  `SPCIALTY` varchar(20) DEFAULT NULL,
  `ADDRESS` varchar(20) DEFAULT NULL,
  `TEL` varchar(20) DEFAULT NULL,
  `EMAIL` varchar(20) DEFAULT NULL,
  `STATE` varchar(20) DEFAULT NULL,
  `REMARK` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `person` */

insert  into `person`(`ID`,`PASSWD`,`AUTHORITY`,`NAME`,`SEX`,`BIRTHDAY`,`DEPARTMENT`,`JOB`,`EDU_LEVEL`,`SPCIALTY`,`ADDRESS`,`TEL`,`EMAIL`,`STATE`,`REMARK`) values ('1','1','管理员','贺步贵','男','2000-4-15','1','1','0','一级财务师','衡阳','18075880415','2424681270@qq.com','T','开发者'),('2','2','员工','张三','男','1999-1-03','1','0','0','二级财务师','湘潭','11111111111','1111111111@qq.com','T','助手');

/*Table structure for table `personnel` */

DROP TABLE IF EXISTS `personnel`;

CREATE TABLE `personnel` (
  `ID` varchar(20) NOT NULL,
  `PERSON` varchar(20) DEFAULT NULL,
  `CHANGE` varchar(20) DEFAULT NULL,
  `DESCRIPTION` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `personnel` */

insert  into `personnel`(`ID`,`PERSON`,`CHANGE`,`DESCRIPTION`) values ('1','2','入职','2020年五月');

/*Table structure for table `personnel_change` */

DROP TABLE IF EXISTS `personnel_change`;

CREATE TABLE `personnel_change` (
  `CODE` varchar(20) NOT NULL,
  `DESCRIPTION` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*Data for the table `personnel_change` */

insert  into `personnel_change`(`CODE`,`DESCRIPTION`) values ('-1','降职'),('0','入职'),('66','升职'),('88','离职');

/*Table structure for table `view1` */

DROP TABLE IF EXISTS `view1`;

/*!50001 DROP VIEW IF EXISTS `view1` */;
/*!50001 DROP TABLE IF EXISTS `view1` */;

/*!50001 CREATE TABLE  `view1`(
 `ID` varchar(20) ,
 `PASSWD` varchar(20) ,
 `AUTHORITY` varchar(20) ,
 `NAME` varchar(20) ,
 `SEX` varchar(20) ,
 `BIRTHDAY` varchar(20) ,
 `SPCIALTY` varchar(20) ,
 `ADDRESS` varchar(20) ,
 `TEL` varchar(20) ,
 `EMAIL` varchar(20) ,
 `STATE` varchar(20) ,
 `REMARK` varchar(20) ,
 `department` varchar(20) ,
 `job` varchar(20) ,
 `edu_level` varchar(20) 
)*/;

/*View structure for view view1 */

/*!50001 DROP TABLE IF EXISTS `view1` */;
/*!50001 DROP VIEW IF EXISTS `view1` */;

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view1` AS select `person`.`ID` AS `ID`,`person`.`PASSWD` AS `PASSWD`,`person`.`AUTHORITY` AS `AUTHORITY`,`person`.`NAME` AS `NAME`,`person`.`SEX` AS `SEX`,`person`.`BIRTHDAY` AS `BIRTHDAY`,`person`.`SPCIALTY` AS `SPCIALTY`,`person`.`ADDRESS` AS `ADDRESS`,`person`.`TEL` AS `TEL`,`person`.`EMAIL` AS `EMAIL`,`person`.`STATE` AS `STATE`,`person`.`REMARK` AS `REMARK`,`department`.`NAME` AS `department`,`job`.`DESCRIPTION` AS `job`,`edu_level`.`DESCRIPTION` AS `edu_level` from (((`person` join `department` on((`person`.`DEPARTMENT` = `department`.`ID`))) join `job` on((`person`.`JOB` = `job`.`CODE`))) join `edu_level` on((`person`.`EDU_LEVEL` = `edu_level`.`CODE`))) group by `person`.`DEPARTMENT` */;

/*!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 */;