ORACLE_高级查询（3）_多表链接查询_集合运算_多表查询练习题_Unit_5;

 多表连接查询

--多表连接查询
--笛卡儿积 （交叉连接）： 列相加  行相乘；
--有些数据是没有意义的
--笛卡儿积中有意义的数据就是内连接
--内连接 ： 根据内部某i一个字段相同进行匹配；
select e.empno, e.ename, d.deptno, d.dname
  from emp e, dept d
 where e.deptno = d.deptno; --这个也属于等值连接
--多表查询的时候我们一般要使用表的别名；
--他等价于
select e.empno,e.ename,d.deptno,d.dname from emp e
inner join dept d
on e.deptno = d.deptno;
--还等价于  自然连接  注意这个自然连接的时候呢我们不能对使用连接的那一列使用限定词  在这里是不能对deptno使用限定词
--就是不能对等值连接的条件那个使用限定词
select e.empno,e.ename,deptno,d.dname from emp e --注意deptno没有加限定词
natural join dept d;
--using子句
select e.empno,e.ename,deptno,d.dname from emp e --注意deptno没有加限定词 using列的部分也是不能加限定词的；
join dept d
using (deptno);  --对于using后面的连接条件是要加括号的
--on子句
select e.empno,e.ename,d.deptno,d.dname from emp e --注意deptno没有加限定词 on后面的部分也是能加限定词的；
join dept d
on (e.deptno = d.deptno);  --对于using后面的连接条件是要加括号的
--这个时候我们会注意到  当我们明确的写出连接条件的时候我们就可以对连接的列加限定词；

--不等值内连接
select e.empno,e.ename,d.deptno,d.dname from emp e
inner join dept d
on e.deptno != d.deptno;
--(>,< ,<>)都可以用于不等值内连接

--外连接
--左外连接（左表全出现   右表数据可以用加号的形式）   右外连接  全外链接（左外加右外加去重）

--左外链接
select e.ename,d.dname from emp e
left outer join dept d  --这个outer可以省略的  改成inner join就变为内连接了
on e.deptno = d.deptno;

--右外连接
select e.ename,d.dname from emp e
right outer join dept d  --这个outer可以省略的  改成inner join就变为内连接了
on e.deptno = d.deptno;

--全外连接
select e.ename,d.dname from emp e
full outer join dept d  --这个outer可以省略的  改成inner join就变为内连接了
on e.deptno = d.deptno;

--我们看这个内连接
select e.ename,d.dname from emp e,dept d 
where e.deptno = d.deptno;
--然后看左外链接
select e.ename,d.dname from emp e,dept d 
where e.deptno = d.deptno(+);
--右外连接
select e.ename,d.dname from emp e,dept d 
where e.deptno(+) = d.deptno;

--是左表还是是右表以查询的先后顺序来分的


--自连接 （一张表实现的多表连接的查询）
--将一张表通过不同的别名是为不同的表
--练习显示员工的姓名和他老板的名字
select e.ename,e2.ename from emp e, emp e2
where e.MGR = e2.empno;
--将e的MGR与e2的empno进行连接   就是通过MGR找到那个领导的名字
-- 自连接也很精妙的  仔细体会一下；
--既然是内连接 那我们就想到了另一个写法
select e.ename,e2.ename from emp e
inner join emp e2
on e.mgr = e2.empno;
-- 自连接的弊端
-- 本质是查询笛卡尔积（交叉链接） 查询量相当大  列相加行相乘  这样就会影响性能  不适合查询数据量很大的表
-- 对于自连接的弊端  我们想到了层次查询  提过了level伪列  最底层的level最大（rownum（数据的逻辑地址）和rowid（数据的物理地址）一样）
 
 select level,empno,ename,mgr from emp
 connect by prior empno = mgr  --上一层的员工编号等于下一层的领导编号
 start with mgr is null --就是从mgr is null的那个最上层开始查询  隐含的条件就是查询整个的表结构； 也可以从mgr = 7839
 order by 1;  --就是按照第一列排序被
--层次连接的缺点就是我们很难发现一张表的层次关系；

--集合运算
-- 并集交集和差积
-- 差积  minus    用a减去ab所共有的
-- 交集  intersect   ab所共有的
-- 并集  union（去重）  union all（不去重）
-- 并集例题：
-- 查询10号和20号部门的员工
select * from emp
where deptno = 10
union
select *from emp
where deptno = 20;

select * from emp
where deptno in (10,20);

select * from emp
where deptno = 10 or deptno = 20;

-- 注意集合运算的前提是列的个数和列的类型必须一致；
-- 任何类型的空都可以用null  所以null可以是任何类型；

--多表查询习题：
--基本的思路
--1：查询所有员工的年工资，所在的部门名称，安年薪从低往高排序
select e.ename, e.sal*12 + nvl(e.comm,0) as "年薪",d.dname from emp e
inner join  dept d
on e.deptno = d.deptno
order by 年薪; --我们order by后面也可以使用别名

--2： 查询所有员工的编号姓名及其上级领导的编号姓名  显示结果按领导的年工资降序排列
--  我们可以想到 员工表中是有普通员工和领导的    所以这个题只需要用到这一张表  
-- 但是我门要把他分为员工表(emp a)  和  领导表(emp b)   这个的连接条件我们写过就是 e.mgr = b.empno;
-- 自连接   通过表别名
select a.empno,a.ename,b.empno,b.ename 
from emp a
inner join emp b
on a.mgr = b.empno
order by b.sal*12 + nvl(b.comm,0) desc;    
-- 我们这里使用 内连接就没有办法显示king的信息
-- 这里我们可以把左表的信息显示完
select a.empno,a.ename,b.empno,b.ename 
from emp a
left outer join emp b
on a.mgr = b.empno
order by b.sal*12 + nvl(b.comm,0) desc;  
--也可以这样
select a.empno,a.ename,b.empno,b.ename 
from emp a,emp b
where a.mgr = b.empno(+)
order by b.sal*12 + nvl(b.comm,0) desc;  

--3:查询费非销售人员的工作名称  以及从事同一工作人员的月工资之和  筛选出月工资之和大于5000   输出结果按月工资之和降序排列
--字段job  sum（sal） --表  emp

select job,sum(sal) from emp 
where job != 'SALESMAN'
group by  job 
having sum(sal) > 5000
order by sum(sal) desc;

-- 4 ： 查询所有领取奖金和不领取奖金的员工总数，平均工资；
-- 和 字   我们可以用并集来做
select count(empno) as 有无奖金人数,avg(sal) as 有无奖金平均工资 from emp
where comm is not null or comm > 0
union
select count(empno),avg(sal) from emp
where comm is null or comm= 0;