Oracle学习笔记(三)

12. SQL1999_table_connections

select ename, dname,grade from emp e,deptd, salgrade s
      where e.deptno = d.deptno and e.sal betweens.losal and s.hisal and 
      job <> 'CLERK'

​ 有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是

旧的语法,所以得看懂这种语句.

        select ename,dname from emp,dept;(旧标准).
  ​
        select ename,dname from emp** cross join** dept;(1999标准)
  ​
        select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
  ​
        select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
  ​
        select ename,dname from emp join deptusing(deptno);等同上句,但不推荐使用.
  ​
        select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);

​ join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。​ 三张表连接：

        slect ename, dname, grade from
  ​
        emp e join dept d on(e.deptno=d.deptno)
  ​
        join salgrade s on(e.sal between s.losal and s.hisal)
  ​
        where ename not like '_A%';

​ 把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。

        select e1.ename,e2.ename from emp e1 join emp e2
  ​
        on(e1.mgr = e2.emptno);

​ 左外连接：会把左边这张表多余数据显示出来。

        select e1.ename,e2,ename from emp e1 left join
  ​
        emp e2 on(e1.mgr =e2.empno);left 后可加outer

​ 右外连接：把右边这张表多余数据显示出来。

      select ename,dname from emp e right outer join dept d
  ​
      on(e.deptno =d.deptno); outer可以取掉。

​ 全外连接： 即把左边多余数据，也把右边多余数据拿出来，

        select ename,dname from emp e full join dept d
  ​
        on(e.deptno =d.deptno);

PS：所谓的“外”连接，即把多余的数据显示出来。Outer关键字可以省略。

13. 求部门中哪些人的薪水最高

  select ename, sal from emp
  join (select max(sal) max_sal, deptno from emp group by deptno) t
  on (emp.sal = t.max_sal and emp.deptno = t.deptno)

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

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

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

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

16. 哪些人是经理

     select ename from emp where empno in(select mgr from emp);
     select ename from emp where empno in(select distinct mgr from emp);

17.不准用组函数，求薪水的最高值（面试题）

     select distinct sal from emp where sal not in(
     select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));

18.平均薪水最高的部门编号

     select deptno,avg_sal from
     (select avg(sal)avg_sal,deptno from emp group by deptno)
     where avg_sal=
     (select max(avg_sal)from 
     (select avg(sal) avg_sal,deptno from emp group by deptno)
     )

19.平均薪水最高的部门名称

     select dname from dept where deptno=
    ( 
      select deptno from
      (select avg(sal)avg_sal,deptno from emp group by deptno)
      where avg_sal=
      (select max(avg_sal)from 
      (select avg(sal) avg_sal,deptno from emp group by deptno)
      )
     )

20.求平均薪水的等级最低的部门的部门名称

      --组函数嵌套
      --如：平均薪水最高的部门编号，可以E.更简单的方法如下：
      select deptno,avg_sal from 
      (select avg(sal) avg_sal,deptno from emp group by deptno)
      where avg_sal =
      (select max(avg(sal)) from emp group by deptno)
          组函数最多嵌套两层
          
         -- 分析：
         -- 首先求
         --1.平均薪水： 
              select avg(sal) from group by deptno;
         -- 2.平均薪水等级：  把平均薪水当做一张表，需要和另外一张表连接salgrade
          select  deptno,grade avg_sal from 
            ( select deptno,avg(sal) avg_sal from emp group by deptno) t
          join salgrade s on(t.avg_sal between s.losal and s.hisal)
          
     --  上面结果又可当成一张表。
          
     --   DEPTNO    GRADE    AVG_SAL
        --------  -------  ----------
     --   30           3   1566.66667
     --   20           4   2175
     --   10           4   2916.66667
  ​
     --   3.求上表平均等级最低值
          
          select min(grade) from
          (
            select deptno,grade,avg_sal from
             (select deptno,avg(sal) avg_sal from emp group by deptno)t
            join salgrade s on(t.avg_sal between s.losal and s.hisa)
           )
  ​
     --   4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
            
            select dname ,deptno,grade,avg_sal from
              (
            select deptno,grade,avg_sal from
                (select deptno,avg(sal) avg_sal from emp group by deptno)t
               join salgrade s on(t.avg_sal between s.losal and s.hisal)
               ) t1
              join dept on (t1.deptno = dept.deptno)
              where t1.grade =
              ( 
                select deptno,grade,avg_sal from
                 (select deptno,avg(sal) avg_sal from emp group by deptno) t
                  join salgrade s on(t.avg_sal between s.losal and s.hisal)
                 )
              )
    --       结果如下：
           
    --      DNAME    DEPTNO     GRADE    AVG_SAL
    --    --------  -------  --------   --------
    --      SALES        30        3    1566.6667 

21. 视图（视图就是一张表，一个字查询）

【

​ 默认scott账户没有创建视图的权限，可通过如下语句授权：​ 首先登陆超级管理员：conn sys/用户密码 as sysdba;​ 授权：grant create table,create view to scott;​ 再以scott账户登陆：conn scott/tiger

】

  -- 20中语句有重复，可以用视图来简化。
     conn sys/bjsxt as sysdba;
     grant create table,create view to scott;
     conn scott/tiger
  -- 创建视图：
     create view v$_dept_avg-sal_info as
     select deptno,grade,avg_sal from
      ( select deptno,avg(sal) avg_sal from emp group by deptno)t
     join salgrade s on 9t.avg_sal between s.losal and s.hisal)
    
  -- 然后 
     select * from v$_dept_avg-sal_info
     
  -- 结果如下：
  --   DEPTNO      GRADE    AVG_SAL
  --  --------  -------  ----------
  --    30           3   1566.66667
  --    20           4   2175
  --    10           4   2916.66667
  ​
  -- 然后G中查询可以简化成：
     select  dname,t1.deptno,grade,avg_sal from
     v$_dept_avg-sal_info t1
     join dept on9t1.deptno =dept.deptno)
     where t1.grade=
     (
      select min(grade) from v$_dept_avg-sal_info t1
     ) 

22. 求比普通员工最高薪水还要高的经理人的名称

  select ename, sal from emp where empno in
         (select distinct mgr from emp where mgr is not null)
      and sal >
      (
         select max(sal) from emp where empno not in
           (select distinct mgr from emp where mgr is not null)
      )

23. 面试题：比较效率

          理论上前一句效率高，但实际上可能Oracle可能会自动对代码优化，所以不见得后一句就会慢。

      select * from emp where deptno = 10 and ename like '%A%';    --效率高，因为将过滤力度大的放在前面
      select * from emp where ename like '%A% and deptno = 10;