目录
项目条件
dept表(部门)
dept部门表结构
字段
类型
描述
deptno
mediumint(8)
部门编号 PK
dname
varchar(20)
部门名称 unique
loc
varchar(13)
部门位置
emp表(员工)
emp表结构
字段
类型
描述
empno
mediumint(9)
员工编号 PK
ename
varchar(20)
员工姓名 not null
job
varchar(9)
员工职位
mgr
mediumint(9)
领导编号 FK
hiredate
date
雇佣日期
sal
decimal(7,2)
员工工资
comm
decimal(7,2)
员工奖金
deptno
mediumint(9)
员工所在部门 FK
salgrade(工资等级表)
salgrade工资等级表结构
字段
类型
描述
grade
mediumint(8)
工资等级
losal
decimal(17,2)
最低工资
hisal
decimal(17,2)
最高工资
bonus奖金表
bonus表(奖金表) 表结构
字段
类型
描述
ENAME
VARCHAR(10)
员工的名称
JOB
VARCHAR(9)
员工的工作
SAL
decimal(17,2)
员工的薪水
COMM
decimal(17,2)
员工的奖金
设计表
dept部门表
create table dept( deptno mediumint(8) primary key, dname varchar(20) unique, loc varchar(13) );
emp员工表
reate table emp( empno mediumint(9) primary key, ename varchar(20) not null, job varchar(9), mgr mediumint(9), hiredate date, sal decimal(7,2), comm decimal(7,2), deptno mediumint(9), constraint fk_deptno foreign key(deptno) references dept(deptno) on delete cascade on update cascade );
注:先插入数据后,再建立此自连外键
alter table emp add constraint fk_mgr foreign key(mgr) references emp(empno) on delete cascade on update cascade;
salgrade工资等级表
create table salgrade( grade mediumint(8) , losal decimal(17,2), hisal decimal(17,2) );
bonus奖金表
create table bouns( ename varchar(10), job varchar(9), sal decimal(17,2), comm decimal(17,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 (30, 'SALES', 'CHICAGO'); insert into DEPT (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON');
员工表数据
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-04-19', 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-05-23', 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);
工资级别表数据
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);
查询操作
- 查询所有的职位
select distinct job from emp; //distinct去重
- 查询emp表中每个员工的姓名和年薪
select ename,(sal+ifnull(comm,0))*12 as salary from emp;
- 查询工资高于1500的所有员工的信息
select * from emp where sal>1500;
- 查询工资高于(等于)1500并且低于(等于)3000的所有员工信息
//方式一 select * from emp where sal between 1500 and 3000; //方式二 select * from emp where sal>=1500 and sal<=3000;
- 查询每个月可以获得奖金的员工信息
select * from emp where comm is not null and comm!=0;
- 查询每个月没有奖金的员工信息
select * from emp where comm is null or comm=0;
- 查询工资高于1500,同时可以获得奖金的员工信息
select * from emp where sal>1500 and comm is not null and comm!=0;
- 查询工资不高于1500,同时不可以获得奖金的员工信息
select * from emp where sal<=1500 and comm is null;
- 查询在1981年入职的员工信息
//方式一: select * from emp where hiredate like '1981%'; //方式二: select * from emp where hiredate between '1981-01-01' and '1981-12-1';
- 查询员工为smith的信息
select * from emp where ename='smith';
- 查询员工名字中带S的员工信息
select * from emp where ename like '%s%';
- 查询员工姓名以l结尾的长度为2个字符的员工信息
select * from emp where ename like '_l';
- 查询员工编号不等于7369的信息
//方式一 select * from emp where empno!=7369; //方式二 select * from emp where empno<>7369;
- 查询员工编号在7369,7788,7900的员工信息
方式一: select * from emp where empno=7369 or empno=7788 or empno=7900; 方式二: select * from emp where empno in(7369,7788,7900);
- 查询出20部门的员工信息,查询出的信息按照工资由高到低排序,如果工资相同,按照入职日期由早到晚排序
select * from emp where deptno=20 order by sal desc,hiredate asc;