多表连接查询练习

2022-07-15
作者我爱小小蝶
~2.42K 字

1. 多表连接
2. 内连接
3. 自身连接
4. 外部连接

多表连接

1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

select e.empno,e.ename,e.sal,g.grade 
from emp e 
join salgrade g on e.sal between g.losal and hisal 
join dept d on e.deptno=d.deptno 
order by g.grade;

内连接

select * 
from emp e inner join dept d 
on e.deptno = d.deptno 
where d.loc='NEW YORK';

tip： inner可以省略。

自身连接

1.查询每个员工的姓名和直接上级姓名？

1
2
3

select e.empno,e.ename,m.empno mno,m.ename mgrName
from emp e, emp m 
where e.mgr=m.empno;

注：e为员工表，m为上级表；连接的条件是：员工表的上级字段=上级表的员工字段

自身连接总结：

自连接可能看起来有点晦涩难懂，但是实际上换个角度你就会豁然开朗，你可以把它这个过程想象成两张一样的表进行左连接或右连接，这样就会简单多了，其中一张表通过设别名的方式成为了虚表，但是共享原表中的信息。

应用场景是这样的，就是表的一个字段和另一个字段是相同性质的东西，譬如员工与上司，他们本质也都是员工，在员工表中，员工的直接上司编号会以另一个字段的形式出现，但是他的上司的编号也是会出现在员工编号这个字段里。

连接操作可以是一个表与自身进行的连接，这时候注意需要为表创建别名

2.查询所有工作在MAN_AGER和CLERK的员工姓名，员工编号，以及他们的经理姓名，经理编号。

1
2
3

select e.empno,e.ename,e.job,m.empno mno,m.ename mgrName
from emp e, emp m 
where e.mgr=m.empno and e.job in ('MAN_AGER','CLERK');

注：工作条件也可写成 and (e.job=’MAN_AGER’ or e.job=’CLERK’)

外部连接

在多表连接时，可以使用外部连接来查看哪些行,按照连接条件没有被匹配上。

1. 左外连接

左外连接以FROM子句中的左边表为基表，该表所有行数据按照连接条件无论是否与右边表能匹配上，都会被显示出来。

1.查询所有雇员姓名，部门编号，部门名称，包括没有部门的员工也要显示出来

1
2
3

select * 
from emp e left join dept d 
on e.deptno=d.deptno;  -- 11条数据

tip：以左表为基表，左表的deptno要全部展示，不考虑右表有的，但左表没有的，

2.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select e.ename, m.ename
from emp e
left outer join emp m
on e.mgr = m.empno;

3.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来

2.右外连接

右外连接以FROM子句中的右边表为基表，该表所有行数据按照连接条件无论是否与左边表能匹配上，都会被显示出来。

1
2
3

select * 
from emp e right join dept d 
on e.deptno=d.deptno; -- 14条数据

这里多了三条的原因是：dept部门表有三条数据，分别是deptno=40、50、60，但是在emp员工表中并没有deptno的值等于这三个，因此，在进行右外连接的时候，会以右表（dept表为基表），连接左表（emp表），一共展示11+3=14条数据。

3.练习

1.显示员工SMITH的姓名，部门名称，直接上级名称

1
2
3

select e.ename,d.dname,e.mgr
from emp e,dept d 
where e.ename='SMITH' and e.deptno=d.deptno;

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

1
2
3

select ename,dname,sal,grade  
from emp,dept,salgrade  
where emp.deptno=dept.deptno and sal between losal and hisal and grade>4;

3.显示员工KING和FORD管理的员工姓名及其经理姓名。

select emp2.ename, emp1.ename
from emp emp1, emp emp2
where emp1.empno = emp2.mgr
and emp1.ename in ('KING', 'FORD');

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

select emp1.ename, emp1.hiredate, emp2.ename, emp2.hiredate
from emp emp1, emp emp2
where emp1.mgr = emp2.empno
and emp1.hiredate < emp2.hiredate;

记录