数据查询语言（Data Query Language，DQL）-多表查询

数据查询语言(Data Query Language，DQL)-多表查询

• 多表查询主要的查询方式有
  • 交叉连结 cross join
  • 自然连接 natural join
  • 内连接 using子句
  • 内连接 on子句
  • 外连接
• 以上查询方法中,内连接 on子句与外连接查询当前应用较多,其他三种查询方式使用不多

内连接 on子句

select e.empno,e.ename,e.sal,d.dname,d.loc, d.deptno 
from emp e -- 给表起一个别名,方便指定需要哪些表里面的哪些字段,提高查询效率
inner join dept d -- inner可以省略不写
on e.deptno = d.deptno;

外连接

-- 外连接 除了显示匹配的数据外,还可以显示不匹配的数据
-- 左外连接 left outer join 左边的表,即使不匹配也可以显示出所有的信息
select * 
from emp e
left outer join dept d
on (e.deptno = d.deptno);

-- 右外连接  right outer join  右边的表,即使不匹配也可以显示出所有的信息
select * 
from emp e
right outer join dept d
on (e.deptno = d.deptno);

-- 全外连接 full outer join -- 但是mysql中不支持  oracle中支持
select * 
from emp e
full outer join dept d
on (e.deptno = d.deptno);

-- mysql中的全外连接解决办法 outer可以省略不写
-- 左右外连接取并集
select * 
from emp e
left outer join dept d
on (e.deptno = d.deptno)
union -- union的作用就是取并集,并去重
select * 
from emp e
right outer join dept d
on (e.deptno = d.deptno);


select * 
from emp e
left outer join dept d
on (e.deptno = d.deptno)
union all -- union all的作用就是取并集但是不去重
select * 
from emp e
right outer join dept d
on (e.deptno = d.deptno);

-- mysql中对集合操作比较弱,只支持并集, 交集与差集都不支持(oracle中支持)

交叉连结

-- 查询员工的编号，姓名，部门编号,部门的名称 -- 多表查询
select * 
from emp  
cross join dept;
-- cross join 交叉连接 cross可以省略不写,   这个结果是笛卡尔乘积,没有实际意义,将所有的匹配可能都查询出来

自然连接

-- 自然连接 natural join
-- 优点,自动匹配所有的同名字段,同名字段只展示一次,简单
select * from emp natural join dept;

select empno,ename,sal,dname,loc from emp natural join dept;
-- 缺点:查询字段的时候,没有指定字段所属的数据库表,两个表都需要查询这两个表,效率低
-- 改进 指定查询的表
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc, dept.deptno 
from emp 
natural join dept;
-- 缺点,表名太长
-- 改进,给表起别名
select e.empno,e.ename,e.sal,d.dname,d.loc, d.deptno 
from emp e 
natural join dept d;

内连接 using子句

-- 自然连接 natural join的缺点:会自动匹配所有的同名列,但是有时候只需要匹配部分同名列
-- 解决: 内连接 -- using子句:
select e.empno,e.ename,e.sal,d.dname,d.loc, d.deptno  
from emp e 
inner join dept d -- inner可以省略不写 
using(deptno); -- 这里不能写natural join
-- using 缺点: 关联的字段必须是同名字段

三表及以上查询(使用三表查询进行示例)

-- 三表查询
-- 查询员工编号,姓名,薪水,部门编号,部门名称,薪水等级
-- 相当于将前面两张表的查询结果当做一张表,然后再与第三张表进行关联查询
-- 更多表以此类推
select e.ename,e.sal,e.empno,d.dname,s.*
from emp e
inner join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal;

自连接查询

-- 自连接查询
-- 自关联的表进行自连接查询,可以将同一张表同时看做两张不同的表
-- 里面的两个字段相互关联
select e1.empno,e1.ename 员工姓名, e1.mgr, e2.ename 领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;