MySQL版oracle下scott用户建表语句实例
概述:
Oraclescott用户下四张表,比较便于做实验,验证数据,现修改为MySQL版本
1.部门表--dept
2.员工表--emp
3.工资等级表--salgrade
4.奖金表--bonus
dept
--Createtable createtableDEPT ( deptnoINT(2)notnull, dnameVARCHAR(14), locVARCHAR(13) )engine=InnoDBcharset=utf8; --Create/Recreateprimary,uniqueandforeignkeyconstraints altertableDEPT addconstraintPK_DEPTprimarykey(DEPTNO) ;
insertintoDEPT(DEPTNO,DNAME,LOC) values('10','ACCOUNTING','NEWYORK'); insertintoDEPT(DEPTNO,DNAME,LOC) values('20','RESEARCH','DALLAS'); insertintoDEPT(DEPTNO,DNAME,LOC) values('30','SALES','CHICAGO'); insertintoDEPT(DEPTNO,DNAME,LOC) values('40','OPERATIONS','BOSTON');
emp
--Createtable createtableEMP ( empnoINT(4)notnull, enameVARCHAR(10), jobVARCHAR(9), mgrINT(4), hiredateDATE, saldecimal(7,2), commdecimal(7,2), deptnoINT(2) )engine=InnoDBcharset=utf8; --Create/Recreateprimary,uniqueandforeignkeyconstraints altertableEMP addconstraintPK_EMPprimarykey(EMPNO); altertableEMP addconstraintFK_DEPTNOforeignkey(DEPTNO) referencesDEPT(DEPTNO);
insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7369','SMITH','CLERK','7902','1980-12-17','800',null,'20'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7499','ALLEN','SALESMAN','7698','1981-02-20','1600','300','30'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7521','WARD','SALESMAN','7698','1981-02-22','1250','500','30'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7566','JONES','MANAGER','7839','1981-04-02','2975',null,'20'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7654','MARTIN','SALESMAN','7698','1981-09-28','1250','1400','30'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7698','BLAKE','MANAGER','7839','1981-05-01','2850',null,'30'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7782','CLARK','MANAGER','7839','1981-06-09','2450',null,'10'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7788','SCOTT','ANALYST','7566','1987-06-13','3000',null,'20'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7839','KING','PRESIDENT',null,'1981-11-17','5000',null,'10'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7844','TURNER','SALESMAN','7698','1981-09-08','1500','0','30'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7876','ADAMS','CLERK','7788','1987-06-13','1100',null,'20'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7900','JAMES','CLERK','7698','1981-12-03','950',null,'30'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7902','FORD','ANALYST','7566','1981-12-03','3000',null,'20'); insertintoEMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values('7934','MILLER','CLERK','7782','1982-01-23','1300',null,'10');
salgrade
createtableSALGRADE ( gradeINT, losalINT, hisalINT )engine=InnoDBcharset=utf8;
insertintoSALGRADE(GRADE,LOSAL,HISAL) values('1','700','1200'); insertintoSALGRADE(GRADE,LOSAL,HISAL) values('2','1201','1400'); insertintoSALGRADE(GRADE,LOSAL,HISAL) values('3','1401','2000'); insertintoSALGRADE(GRADE,LOSAL,HISAL) values('4','2001','3000'); insertintoSALGRADE(GRADE,LOSAL,HISAL) values('5','3001','9999');
bonus
createtableBONUS ( enameVARCHAR(10), jobVARCHAR(9), salINT, commINT )engine=InnoDBcharset=utf8;
总结
到此这篇关于MySQL版oracle下scott用户建表语句的文章就介绍到这了,更多相关MySQL版oraclescott用户建表内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票!