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

概述:

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

1.部门表 –dept

2.员工表 –emp

3.工资等级表 –salgrade

4.奖金表 –bonus

dept

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

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

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
) engine=innodb charset=utf8;

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
) engine=innodb charset=utf8 ;

总结

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