1 数据表准备

创建emp表

CREATE TABLE `emp` (
  `EMPNO` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ENAME` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `JOB` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `MGR` tinyint(4) DEFAULT NULL,
  `HIREDATE` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SAL` tinyint(4) DEFAULT NULL,
  `COMM` tinyint(4) DEFAULT NULL,
  `DEPTNO` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

插入数据

INSERT INTO `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`)
VALUE (7369,"SMITH","CLERK","7902","17-DEC-2005","800","","20"),
(7499,"ALLEN","SALESMAN","7698","20-FEB-2006","1600","300","30"),
(7521,"WARD","SALESMAN","7698","22-FEB-2006","1250","500","30"),
(7566,"JONES","MANAGER","7839","2-APR-2006","2975","","20"),
(7654,"MARTIN","SALESMAN","7698","28-SEP-2006","1250","1400","30"),
(7698,"BLAKE","MANAGER","7839","1-MAY-2006","2850","","30"),
(7782,"CLARK","MANAGER","7839","9-JUN-2006","2450","","10"),
(7788,"SCOTT","ANALYST","7566","9-DEC-2007","3000","","20"),
(7839,"KING","PRESIDENT","","17-NOV-2006","5000","","10"),
(7844,"TURNER","SALESMAN","7698","8-SEP-2006","1500","0","30"),
(7876,"ADAMS","CLERK","7788","12-JAN-2008","1100","","20"),
(7900,"JAMES","CLERK","7698","3-DEC-2006","950","","30"),
(7902,"FORD","ANALYST","7566","3-DEC-2006","3000","","20"),
(7934,"MILLER","CLERK","7782","23-JAN-2007","1300","","10")

创建dept表

CREATE TABLE `dept` (
  `DEPTNO` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `DNAME` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `LOC` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

插入数据

INSERT INTO `dept`(`DEPTNO`,`DNAME`,`LOC`)
VALUE (10,"ACCOUNTING","NEW YORK"),
(20,"RESEARCH","DALLAS"),
(30,"SALES","CHICAGO"),
(40,"OPERATIONS","BOSTON")