测试数据以Oracle数据库自带scott用户emp和dept表为准:
一、MySQL数据库创建的emp和dept表语法及数据:
drop table if exists dept;
create table dept( deptno int not null comment '部门编号', dname varchar(14) comment '部门名称', loc varchar(13) comment '部门地址', PRIMARY KEY (deptno))default character set 'utf8' comment '部门表';insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCH','DALLAS');insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');----------------------------------------------------------------------------------------------------------
drop table if exists emp;create table emp( empno int not null comment '员工编号', ename varchar(10) comment '员工姓名', job varchar(9) comment '职位', mgr int comment '直接领导', hiredate date comment '出生日期', sal float comment '薪水', comm float comment '奖金', deptno int comment '所属部门', primary key (empno), unique key (ename), key (empno) using btree, key (deptno) using btree)default character set 'utf8' comment '员工表';insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);二、Hive数据仓库创建emp表和dept表语法和数据:
create table chavin.DEPT
(DEPTNO int,DNAME string,LOC string) row format delimited fields terminated by '\t';create table chavin.EMP
(EMPNO int,ENAME string,JOB string,MGR int,HIREDATE string,SAL float,COMM float,DEPTNO int) row format delimited fields terminated by '\t'; ----------------------------10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON -----------------------7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 307521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 307566 JONES MANAGER 7839 1981-04-02 2975.00 207654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 307698 BLAKE MANAGER 7839 1981-05-01 2850.00 307782 CLARK MANAGER 7839 1981-06-09 2450.00 107788 SCOTT ANALYST 7566 1987-04-19 3000.00 207839 KING PRESIDENT 1981-11-17 5000.00 107844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 307876 ADAMS CLERK 7788 1987-05-23 1100.00 207900 JAMES CLERK 7698 1981-12-03 950.00 307902 FORD ANALYST 7566 1981-12-03 3000.00 207934 MILLER CLERK 7782 1982-01-23 1300.00 10----------------------------------------------------------------------------------------------------