mysql学习笔记之构筑部门表，雇佣表等

前提：先使用一个数据库

use database xxx(数据库名）

1.创建部门表

create table `dept`(
`deptno` int(2) PRIMARY KEY,
`dname` varchar(14) NOT NULL,
`loc` varchar(13)
)DEFAULT CHARSET utf8;

insert into dept values(10, 'ACCOUNTING','NEW YORK');
insert into dept values(20, 'RESEARCH','DALLAS');
insert into dept values(30, 'SALES','CHICACO');
insert into dept values(40, 'OPERAITIONS','BOSTONS');

2.创建雇佣表

create table `emp`(
`empno` int(4) PRIMARY KEY,
`ename` varchar(10) NOT NULL,
`job`	varchar(9),
`mgr` int(4),
`hiredate` DATE,
`sal` int,
`comm`	int,
`deptno`	int(2)
)DEFAULT CHARSET=utf8;
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'JONES','NANACER',7839,'1981-04-02',2975,NULL,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'BLAKE','NANACER',7839,'1981-05-01',2850,NULL,30);
insert into emp values(7782,'CLARK','NANACER',7839,'1981-06-09',2450,NULL,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
insert into emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'ADANS','CLERK',7788,'1987-05-23',1100,NULL,20);
insert into emp values(7900,'JANES','CLERK',7788,'1981-12-03',950,NULL,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
insert into emp values(7934,'MELLER','CLERK',7782,'1982-01-23',1200,NULL,10);

3.创建工资等级表

create table `salgrade`(
`grade`  int,
`losal`  int,
`hisal`  int
)DEFAULT CHARSET=utf8;
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

4.创建奖金表

create table `bonus`(
`ename` varchar(10),
`job` varchar(9),
`sal` int,
`comm` int
)DEFAULT CHARSET=utf8;