MySQL多表操作案例练习

目录

准备

需求

代码

---

 

准备

-- 创建test1数据库
create database test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(
	deptno int primary key,-- 部门编号
	dname varchar(14) ,-- 部门名称
	loc varchar(13) -- 部门地址
) ;

insert into dept values(10,'accounting','new york'),
						(20,'research','dallas'),
						(30,'sales','chicago'),
						(40,'operations','boston');
-- 创建员工表					
create table emp(
	empno int primary key, -- '员工编号',
    ename varchar(10), -- '员工名',
    job varchar(9), -- '员工工作',
    mgr int, -- '员工直属领导编号',
    hiredate date, -- '入职时间',
    sal double, -- '工资',
    comm double, -- '奖金',
    deptno int -- '对应dept表的外键'
);
-- 添加部门和员工之间的外键关系
alter table emp add constraint foreign key emp(deptno) references dept (deptno);


insert into emp values (7369,'smith','clerk',7902,'19801217',800,null,20),
(7499,'allen','salesman',7698,'19810220',1600,300,30),
(7521,'ward','salesman',7698,'19810222',1250,500,30),
(7566,'JONES','MANAGER',7839,'19810402',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'19810928',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'19810501',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'19810609',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'19870419',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'19811117',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'19810908',1500,0,30),
(7876,'ADAMS','CLERK',7788,'19870523',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'19811203',950,NULL,30),
(7902,'FORD','ANALYST',7566,'19811203',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'19820123',1300,NULL,10);

create table salgrade(
	grand int ,-- '等级',
    losal double, -- '最低工资',
    hisal double -- '最高工资'
);
insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

需求

1、返回拥有员工的部门名、部门号。
2、工资水平多于smith的员工信息。
3、返回员工和所属经理的姓名。
4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
5、返回员工姓名及其所在的部门名称。

6、返回从事clerk工作的员工姓名和所在部门名称。
7、返回部门号及其本部门的最低工资。
8、返回销售部(sales)所有员工的姓名。

9、返回工资水平多于平均工资的员工。
10、返回与scott从事相同工作的员工。

11、返回工资高于30部门所有员工工资水平的员工信息。
12、返回员工工作及其从事此工作的最低工资。
13、计算出员工的年薪,并且以年薪排序。
14、返回工资处于第四级别的员工的姓名。

15、返回工资为二等级的职员名字、部门所在地

代码

-- 1、返回拥有员工的部门名、部门号。
select  distinct  d.dname ,d.deptno  from dept d ,emp e where d.deptno =e.deptno ;
select  distinct  d.dname ,d.deptno  from dept d  right outer join emp e on d.deptno =e.deptno ;

-- 2、工资水平多于smith的员工信息。
select * from emp where sal >(select sal from emp where ename='smith');
-- 3、返回员工和所属经理的姓名。
select  e.ename ,e2.ename  from emp e join emp e2 on e.mgr =e2.empno ;

select  e.ename ,e2.ename  from emp e, emp e2 where e.mgr =e2.empno ;

-- 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。

select  e.ename,e.hiredate  ,e2.ename,e2.hiredate  from emp e join emp e2 on e.mgr =e2.empno and e.hiredate <e2.hiredate ;

-- 5、返回员工姓名及其所在的部门名称。

select e.ename ,d.dname  from emp e join dept d on e.deptno =d.deptno ;

-- 6、返回从事clerk工作的员工姓名和所在部门名称。
select e.ename ,d.dname ,e.job  from emp e  join dept d on e.deptno =d.deptno and e.job ='clerk';

-- 7、返回部门号及其本部门的最低工资。
select deptno ,min(sal) from emp group by deptno ;

-- 8、返回销售部(sales)所有员工的姓名。
select e.ename,e.deptno  from emp e where e.deptno =(select d.deptno  from dept d where d.dname='sales');

select  e.ename,e.deptno from emp e join dept d on e.deptno =d.deptno and d.dname ='sales';

-- 9、返回工资水平多于平均工资的员工。

select  * from emp e where sal>(select avg(sal)  from emp e2 );
-- 10、返回与scott从事相同工作的员工。
select * from emp e where e.job =(select e2.job  from emp e2 where e2.ename='scott' ) and e.ename !='scott';

-- 11、返回工资高于30部门所有员工工资水平的员工信息。
select * from emp e where sal>all(select sal from emp e2 where e2.deptno=30);

-- 12、返回员工工作及其从事此工作的最低工资。
select e.job ,min(sal) from emp e group by e.job ;


-- 13、计算出员工的年薪,并且以年薪排序。
select sal*12+ifnull(comm,0) as nian_sal  from emp e order by sal*12+ifnull(comm,0)  ;

-- 14、返回工资处于第四级别的员工的姓名。
select e.ename,e.sal  from emp e where e.sal between (select s.losal from salgrade s where s.grand=4) and (select s.hisal  from salgrade s where s.grand=4) ;

-- 15、返回工资为二等级的职员名字、部门所在地

select e.ename ,d.dname ,e.sal  from emp e ,dept d ,salgrade s where 
s.grand =2 
and (e.sal>s.losal and e.sal <s.hisal  )
and (e.deptno=d.deptno);


select e.ename ,d.dname  ,e.sal  from emp e 
join salgrade s on s.grand =2  and (e.sal>s.losal and e.sal <s.hisal  )
join dept d  on e.deptno =d.deptno ;