Mysql 版 oracle下scott用户建表语句

概述:
Oracle scott用户下四张表,比较便于做实验，验证数据，现修改为Mysql版本
1.部门表 –dept
2.员工表 –emp
3.工资等级表 –salgrade
4.奖金表 –bonus

dept

-- Create tablecreate table DEPT(deptno INT(2) not null,dname  VARCHAR(14),loc    VARCHAR(13));-- Create/Recreate primary, unique and foreign key constraintsalter table DEPTadd constraint PK_DEPT primary key (DEPTNO);

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\');

emp

-- Create tablecreate table EMP(empno    INT(4) not null,ename    VARCHAR(10),job      VARCHAR(9),mgr      INT(4),hiredate DATE,sal      decimal(7,2),comm     decimal(7,2),deptno   INT(2));-- Create/Recreate primary, unique and foreign key constraintsalter table EMPadd constraint PK_EMP primary key (EMPNO);alter table EMPadd constraint FK_DEPTNO foreign key (DEPTNO)references DEPT (DEPTNO);

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-06-13\', \'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-06-13\', \'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\');

salgrade

create table SALGRADE(grade INT,losal INT,hisal INT);

insert into salgrade (GRADE, LOSAL, HISAL)values (\'1\', \'700\', \'1200\');insert into salgrade (GRADE, LOSAL, HISAL)values (\'2\', \'1201\', \'1400\');insert into salgrade (GRADE, LOSAL, HISAL)values (\'3\', \'1401\', \'2000\');insert into salgrade (GRADE, LOSAL, HISAL)values (\'4\', \'2001\', \'3000\');insert into salgrade (GRADE, LOSAL, HISAL)values (\'5\', \'3001\', \'9999\');

bonus

create table BONUS(ename VARCHAR(10),job   VARCHAR(9),sal   INT,comm  INT);