-- 部门表：
create table dept(
deptno int(2) not null,
dname varchar(14),
loc varchar(13)
);
-- 设置主键
alter table dept add constraint pk_dept primary key (deptno);
-- 创建员工表：
create table emp(
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4), -- 上级领导 自身表 自关联
hiredate date,
sal double(7,2),
comm double(7,2), -- 补助
deptno int(2)
);
-- 设置外键：
alter table emp add constraint fk_deptno foreign key (deptno) references dept (deptno);
-- 创建薪资等级表：
create table salgrade(
grade int primary key,
losal double(7,2), -- 最低
hisal double(7,2) -- 最高
);
-- 创建奖金表：
create table bonus(
ename varchar(18),
job varchar(9),
sal double(7,2),
comm double(7,2)
);
-- 插入数据：
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(40,'sales','chicago');
insert into dept (deptno,dname,loc) values(30,'oprations','boston');
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7369, 'smith', 'clerk', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7499, 'allen', 'salesman', 7698, '1982-11-12', 1600.00, 300.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7521, 'ward', 'salesman', 7698, '1981-03-13', 1250.00, 500.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7566, 'jones', 'manager', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7654, 'martin', 'salesman', 7698, '1980-01-10', 1250.00, 1400.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7698, 'blake', 'manager', 7839, '1984-05-14', 2850.00, NULL, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7782, 'clark', 'manager', 7839, '1983-07-02', 2450.00, NULL, 10);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7788, 'scott', 'analyst', 7566, '1986-02-04', 3000.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7839, 'king', 'president', NULL, '1987-11-02', 5000.00, NULL, 10);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7844, 'turner', 'salesman', 7698, '1983-05-03', 1500.00, 0.00, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7875, 'adams', 'clerk', 7788, '1984-11-12', 1100.00, NULL, 20);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7900, 'jamesford', 'clerk', 7698, '1987-12-11', 950.00, NULL, 30);
INSERT INTO `mytestdb`.`emp` (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7902, 'analyst', 'analyst', 7566, '1980-12-17', 3000.00, NULL, 20);
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);
-- 查看表：
select * from dept;
select * from emp;
select * from salgrade;
select * from bonus;
show create table emp;
desc emp;