MySQL项目练习2——员工信息表项目

目录

项目条件

设计表

数据插入

查询操作

项目条件

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;