Orcale 关联查询

–关联查询

select t1.c1,t2.c2, from t1,t2 where t1.c3 = t2.c4;
在进行连接的时候，可以使用等值连接，可以使用非等值连接

–查询雇员的名称和部门的名称
select ename,dname from emp,dept where emp.deptno = dept.deptno;
– 查询雇员名称以及自己的薪水等级

select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal between sg.losal and sg.hisal;
–等值连接，两个表中包含相同的列明
–非等值连接，两个表中没有相同的列名，但是某一个列在另一张表的列的范围之中
–外连接
select * from emp;
select * from dept;
– 需要将雇员表所有数据都进行显示,利用等值连接的话只会把关联到的数据显示出来， 没有关联到的数据不会显示出来，此时需要外连接
– ： 左外连接（把左表的全部数据显示）和右外连接（把右表的全部数据显示）

select * from emp e,dept d where e.deptno = d.deptno; --等值连接
select * from emp e,dept d where e.deptno = d.deptno(+); – 左外连接
select * from emp e,dept d where e.deptno(+) = d.deptno; – 右外连接
– 自连接 将一张表当成不同的表来看待，自己关联自己
–将雇员和他经理的名称查出来
select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;
–笛卡尔积,当关联多张表，但是不指定连接条件的时候，会进行笛卡尔积，
– 关联后的总记录条数为M*N 一般不要使用
select * from emp e,dept d;

–在92 语法中 多张表的连接条件会方法 where字句中，同时 where需要对表进行条件过滤
–因此 相当于将过滤条件和连接退化吧揉到一起，太乱了，因此出现了99语法

– 99语法
/*
cross join
natural join
using 字句
on字句
left outer join
right outer join
full outer join
Inner outer join
*/
– crossjoin 等同于92语法中的笛卡尔积
select * from emp cross join dept;
– natural join 相当于是等值了连接， 不需要写连接条件，会从两张表中找到相同的列做连接
– 当两张表 不具有相同的列名的时候，会进行笛卡尔积
select * from emp e natural join dept d;
select * from emp e natural join salgrade sg;
–on字句 添加连接条件 可以添加任意的等值条件
select * from emp e join dept d on e.deptno = d.deptno;
–相当于92语法中的非等值连接
select * from emp e join salgrade sa on e.sal between sa.losal and sa.hisal;

–left outer join 会把左表中的全部数据正常显示，右表没有对应的数据直接显示空即可
select * from emp e left outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno = d.deptno(+);
–right outer join 会把左表中的全部数据正常显示，左表没有对应的数据直接显示空即可
select * from emp e right outer join dept d on e.deptno = d.deptno;

select * from emp e,dept d where e.deptno(+) = d.deptno;

– full outer join 左外连接和右外连接的合集 不去重
select * from emp e full outer join dept d on e.deptno = d.deptno;

– inner join 做了个等值连接

select * from emp e inner join dept d on e.deptno = d.deptno;

select * from emp e join dept d on e.deptno = d.deptno;

–using 出了可以使用on 表示连接条件之外 也可以使用 using作为连接条件
– 此时连接条件的列再也不归属于任意一张表
select from emp e join dept d using(deptno);
select * from emp e join dept d on e.deptno = d.deptno;

–两种 语法的sql语句没有任何限制 可以任意使用 建议99
– 检索雇员名字、所在单位、薪水等级：
select e.ename, d.dname, s.grade
from emp e
join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;
/*把子查询当做一张表来看待，外层的语句可以把内嵌的子查询返回的结果当成一张表使用

将子查询放在比较运算符的右边（增强可读性）
*/
–查询那些人的薪水是在整个雇员的平均薪水之上
–1 求平均薪水
–2 把所有人的薪水与平均薪水比较
select avg(sal)from emp

select * from emp e where e.sal> (select avg(sal)from emp);
–查询雇员中有哪些人是经理人

–1 查询那些人是经理人
distinct 去重
select distinct e.mgr from emp e;

–2 在雇员表中过滤这些编号
select * from emp e where e.empno in(select distinct e.mgr from emp e);

–找出部门标号为20的所有员工中收入最高的职员

1 找出标号为20的所有员工的收入
select sal from emp where deptno = 20;

select * from emp where sal>=all(select sal from emp where deptno = 20)and deptno = 20;

–求每个部门平均薪水的等级
–1 求每个部门平均薪水
select deptno,avg(sal) from emp group by deptno;
–2 跟薪水等级表做关联，求出 平均薪水的等级

select t.deptno, s.grade
  from salgrade s
  join (select deptno, avg(sal) vsal from emp group by deptno) t
    on t.vsal between s.losal and s.hisal;

练习：
–1.求平均薪水最高的部门编号

 1.求部门的平均薪水 
 select e.deptno,avg(e.sal) from emp e group by deptno ;  
 2.求平均薪水的最高部门
 select max(t.vasl) from (select e.deptno,avg(e.sal) vasl from emp e group by deptno) t;
 select e.sal from emp where 
 3.求部门编号
 
 select t.deptno
   from (select e.deptno, avg(e.sal) vasl from emp e group by deptno) t
  where t.vasl = (select max(t.vasl)
                    from (select e.deptno, avg(e.sal) vasl
                            from emp e
                           group by deptno) t)

2.求部门平均薪水的等级

 select t1.deptno,s.grade
   from salgrade s
   join (select deptno, avg(sal) vas from emp group by deptno) t1
     on t1.vas between s.losal and s.hisal;

3.求部门平均的薪水等级

  1 求部门每个人的薪水等级 
  select e.deptno, sg.grade
    from emp e
    join salgrade sg
      on e.sal between sg.losal and sg.hisal;
  2 按照部门求平均等级
  
 select t.deptno, avg(t.grade)
    from (select e.deptno, sg.grade
            from emp e
            join salgrade sg
              on e.sal between sg.losal and sg.hisal) t
   group by deptno;

–限制输出 limit mysql中用来做限制输出 oracle 不支持
–在 oracle 中，如果需要使用限制输出和分页 必须使用rownum,但是不能直接使用 rownum 需要嵌套使用
4.求薪水最高的前5名雇员
select * from (select * from emp order by sal desc) t1 where rownum<=5;

5.求薪水最高的第6到第10名雇员

–使用rownum的时候必须在外层添加嵌套， 才能将rownum作为其中的一个列，然后再进行限制输出

一
select *
  from (select t1.*, rownum rn
          from (select * from emp e order by e.sal desc) t1
         where rownum <= 10) t
 where t.rn > 5
   and t.rn <= 10;
   
二
select *
  from (select t1.*, rownum rn
          from (select * from emp e order by e.sal desc) t1) t
 where t.rn > 5
   and t.rn <= 10;

  练习

----1．列出所有雇员的姓名及其上级的姓名。

92 语法
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
99 语法
select e1.ename,e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;

–2．列出入职日期早于其直接上级的所有雇员。

92语法
select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);
99语法
select e.ename from emp e join emp e2 on e.mgr = e2.empno and e.hiredate<e2.hiredate;

–3.列出所有部门名称及雇员

92语法
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

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

4．列出所有‚CLERK‛（办事员）的姓名及其部门名称。

92 语法
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'CLERK'
99 语法
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno and e.job = 'CLERK';

–5．列出从事‚SALES‛（销售）工作的雇员的姓名，假定不知道销售部的部门编号。

92语法 
select distinct e.deptno,e.ename
  from emp e
 where e.deptno = (select d.deptno from dept d where d.dname = 'SALES');
 
99语法
select  d.deptno,e.ename
  from emp e
  join dept d
    on e.deptno = d.deptno
   and d.dname = 'SALES';

–6．列出在每个部门工作的雇员的数量以及其他信息。

92语法 
select distinct t1.deptno, t1.count,d.dname, d.loc
  from (select e.deptno, count(e.ename) count from emp e group by e.deptno) t1,
       dept d
 where t1.deptno = d.deptno
 order by t1.deptno asc;

99语法
select *
  from (select e.deptno, count(e.ename) from emp e group by e.deptno) t1 natural
  join dept d;

–7．列出所有雇员的雇员名称、部门名称和薪金。

92 语法
select e.ename,d.dname,e.sal from emp e,dept d where e.deptno = d.deptno;

99 语法
select e.ename,d.dname,e.sal from emp e join dept d on  e.deptno = d.deptno;

99 语法
select ename, dname, sal from emp e natural join dept d

–8.求出部门编号为20的雇员名、部门名、薪水等级

92语法 
select e.ename, e.sal, d.deptno, d.dname, s.grade
  from emp e, dept d, salgrade s
 where e.deptno = d.deptno
   and e.deptno = 20
   and e.sal between s.losal and s.hisal;

99语法
select e.ename, e.sal, d.deptno, d.dname, s.grade
  from emp e
  join dept d
    on e.deptno = d.deptno
   and e.deptno = 20
  join salgrade s
    on e.sal between s.losal and s.hisal;