oracle(三)

初级知识的高级应用
大概的内容如下：从多个表中获取数据，分组函数，子查询。
而这里以子查询为最重要。

1.内联接——等联接
内连接实际上就是利用where子句对两张表形成的笛卡尔积进行筛选等连接（用=条件进行筛选）比如：
 Select * from tab1 t,tab2 r where t.aa=r.bb;
 select * from emp e,dept d where e.deptno=d.deptno

2.内联接——非等联接
内连接实际上就是利用where子句对两张表形成的笛卡尔积进行筛选非等连接（用=以外的条件进行筛选）一个基本的案例：
 Select * from tab1 t,tab2 r where t.aa>r.bb;

来看一个实际的例子：察看员工的工资级别
 select empno,sal,s.* from emp e,salgrade s where sal between s.losal and s.hisal;

3.外联接
我们会注意到两个事情，tab1和tab2两个标做内联接的时候有些记录没有办法显示出来，原因是另一个表没有匹配的条件：我们发出
select * from emp e,dept d where e.deptno=d.deptno；的时候40部门不会被显示，原因是40部门没有员工，所以在员工表中没有记录，因此不满足联接的筛选条件所以显示不出来。

4.Oracle的外联接语法
外连接：
    a和ｂ进行连接，如果要完全显示a，就在ｂ条件上加一个（＋），也就是说不带＋的表完全显示
 select * from emp e,dept d where e.deptno(+)=d.deptno;
因为我们给e.deptno带上一个(+),所以d表将会被完全显示也就是dept表会被完全显示，如果左侧的表完全显示我们就说是左外联接。比如：
 Select * from tab1 t,tab2 r where t.aa=r.bb(+);
如果右侧的表完全显示我们就说是右外联接。比如：
 Select * from tab1 t,tab2 r where t.aa(+)=r.bb;

左外联接：
左连接（左侧的表完全显示）
SQL> Select * from tab1 t left join tab2 r on  t.aa=r.bb;
        AA         BB CC
---------- ---------- --------------------
         2          2 a
         3          3 b
         1
必须显示所有的员工
 select * from emp e left join dept d on e.deptno=d.deptno;

右外联接：
右连接（右侧的表完全显示）
SQL> Select * from tab1 t right join tab2 r on  t.aa=r.bb;
        AA         BB CC
---------- ---------- --------------------
         2          2 a
         3          3 b
                     4 c
要求必须显示所有的部门
 select * from emp e right join dept d on e.deptno=d.deptno;
完全外联接：
完全显示两个表，没有匹配的记录置为空：

SQL> Select * from tab1 t full outer join tab2 r on  t.aa=r.bb;
        AA         BB CC
---------- ---------- --------------------
         2          2 a
         3          3 b
         1           
                    4 c

 Char varchar2   nchar  nvarchar2  date    blob clob number(3,1)
内联接：
自连接(一个特殊的内连接，自己连接自己的一个副本)比如：
 select * from tab1 a,tab1 b;
就是自己联接自己的笛卡尔积

    我们分析emp表，该表中有一个字段叫作mgr，它的具体含义是该员工领导的empno，如果我们要查找一个员工领导的姓名怎么办呢？
我们想到把emp表形成2个副本，连接后我们使用如下条件，a.mgr=b.empno就可以了，查询如下：
 select e.empno,e.ename,e.mgr,b.empno,b.ename from emp e,emp b where e.mgr=b.empno;

再一次注意了，一般大企业考数据库专考下面的内容，为什么，看了下面的内容就知道了。

相关联子查询
一个查询如下：查找所有工资高于公司平均工资的人：
 select * from emp where sal>(select avg(sal) from emp);
查询如何进行呢？系统找到emp的所有的纪录，从中分别拿出每一行来求解where子句，也就是说我们的emp表有14行，自查询也会执行14次。

我们来看一个新的查询要求：
1.查找所有工资高于自己本部门平均工资的人。
    看起来和刚才的很像，但是这样一行记录是否能保存下来就依赖于他的工资是否高于他所在部门的平均工资既然查询是逐行来求解where子句，我们是否可以让
where子句求解当前行员工所在部门的平均工资呢，也就是说我们要在自查询中获得主查询当前行的值：具体如法如下：用主表的别名.列名，代表主查询当前行得值
我们发出查询如下：select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
注意：e.deptno代表的就是主查询当前行的deptno的值

2.查找每个部门工资最高的人的详细资料
思路：得到所有的员工，进行筛选，每拿到一个员工，判断该员工的工资是否是他们部门的最高工资
 select * from emp e where sal=(select max(sal) from emp where deptno=e.deptno);

 select max(sal) from emp where deptno=e.deptno就是求解当前员工所在部门的最高工资

3.显示每个部门的信息和人员数量
思路：查询获得所有的部门信息，没获得一个部门，我们就查询该部门的人数，保存为一个新的列

 select d.*,(select count(*) from emp where deptno=d.deptno) tot from dept d;

4.创建表myemp和emp拥有相同的记录和结构
给myemp 增加一个列 no_of_sal(该员工的工资在公司的排名)和no_of_per（该员工工资在他所在部门的排名）

思路：每拿到一个员工判断它的工资在公司和部门排名第几
可是如何确定排名呢？
思路：查找工资比当前员工工资高的人数，工资比当前高的有10个的话，该员工排第11

现在更新no_of_sal为该员工工资在公司的排名
 update myemp m set no_of_sal=(select count(*)+1 from myemp where sal>m.sal);

现在更新no_of_per为该员工工资在本部门的排名
 update myemp m set no_of_sal=(select count(*)+1 from myemp where sal>m.sal and deptno=m.deptno);

相关联子查询 总结
什么是关联自查询？
在子查询中需要利用“主查询表别名.列名”来引用主查询当前行的值

他的用途非常广泛，比如：
查找入职日期早于其直接上级的员工：
  select * from emp e where hiredate<(select hiredate from emp where empno=e.mgr);

这道也是一家企业的SQL面试题目:
给出这四个名称：id  country  region  population，为下面的内容写段SQL语句：
查询出哪个 国家的人口比他周边国家的人口的平均值大3倍的国家。
Select * from table t1 population>(
 Select avg(popylation) from table t2
  Where t2.region=t1.region and country nto in(t1.country))*3;
通过这题可以看出相关子查询的重要性。