MySQL版oracle下scott用户建表语句实例

概述:

Oracle scott用户下四张表,比较便于做实验，验证数据，现修改为MySQL版本

1.部门表 --dept

2.员工表 --emp

3.工资等级表 --salgrade

4.奖金表 --bonus

dept

?

1 2 3 4 5 6 7 8 9 10 11

-- Create table create table DEPT (   deptno INT (2) not null ,   dname VARCHAR (14),   loc VARCHAR (13) ) engine=InnoDB charset=utf8; -- Create/Recreate primary, unique and foreign key constraints alter table DEPT   add constraint PK_DEPT primary key (DEPTNO) ;

?

1 2 3 4 5 6 7 8 9 10 11

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- Create table create 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) ) engine=InnoDB charset=utf8; -- Create/Recreate primary, unique and foreign key constraints alter table EMP   add constraint PK_EMP primary key (EMPNO); alter table EMP   add constraint FK_DEPTNO foreign key (DEPTNO)   references DEPT (DEPTNO);

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

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

?

1 2 3 4 5 6

create table SALGRADE (   grade INT ,   losal INT ,   hisal INT ) engine=InnoDB charset=utf8;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

?

1 2 3 4 5 6 7

create table BONUS (   ename VARCHAR (10),   job VARCHAR (9),   sal INT ,   comm INT ) engine=InnoDB charset=utf8 ;

总结

到此这篇关于MySQL版oracle下scott用户建表语句的文章就介绍到这了,更多相关MySQL版oracle scott用户建表内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.jianshu.com/p/532fe68924cb