oracle 三表关联更新_Oracel学习--多表关联（99语法）、TOP-N、分页

（以下连接，都基于笛卡尔积模型）

1.自然连接

关键字 nuatural join

概念：基于两个表中列名完全相同的列产生连接

注意：

[1]两个表有相同名字的列

[2]数据类型相同

[3]从两个表中选出连接列的值相等的所有行

语法：

select *
from dept d natural join emp e

使用自然连接后，新表只会保留一个重复列。

自然连接的使用特点：

主外键关系且主外键字段只有一个。这样才会比较符合我们的实际运用。

2.using

using 主要用于指定连接字段。

[1] 按照指定的字段连接两个表。

[2] 选指定字段值相同的数据行。

由于自然连接的局限性，Oracle推出了Using关键字，Using可以灵活地制定关键字段来进行连接。

select *
from emp e join dept d using (deptno)

3. on

为了设置任意的连接条件或者指定连接的列，需要使用ON子句

连个表的关联用关键字 join ，默认内连接(inner) 语法

语法：

select filed1,fild2,…
from table1 
join table2 on condition1
     [join table3 on condition2]*

-- 查询出员工的部门名称
select *
from dept d join emp e
on d.deptno = e.deptno

-- 查询scott的上级领导
select e.ename,m.ename
from emp e join emp m
on e.mgr = m.empno
where e.ename = 'SCO%T'

3.1三表连接

-- 查询SCOTT部门名称和薪资等级
select e.ename,d.dname,sg.grade
from dept d join emp e on d.deptno = e.deptno 
join salgrade sg on e.sal between sg.losal and sg.hisal
where e.ename = 'SCO%T'

-- 查询SCOTT的管理者名称和其管理者的薪资等级
select e.ename,m.ename,sg.grade
from emp e join emp m on e.mgr = m.empno
join salgrade sg on m.sal between sg.losal and sg.hisal
where e.ename = 'SCOTT'

4.外连接

按照主表的位置，可以分为left outer/right outer

语法：

select filed1,field2
from table1 left/right outer join table2 on condition1
      [left/right outer join table3 on condition2]*

左外连接

-- 查询所有部门的所有员工
select *
from dept d left outer join emp e
on d.deptno = e.deptno

右外连接

-- 查询所有部门的所有员工
select *
from emp e right outer join dept d
on e.deptno = d.deptno;

5.子查询

查询得到的一个表，我们可以称之为虚表，这个虚表我们可以作为另一个查询的条件，或者表。即在另一个查询中，嵌套一个sql语句。

SELECT	select_list
FROM	table
WHERE	expr operator
		(SELECT	select_list
			 FROM	table);

理解子查询的关键在于把子查询当作一张表来看待。外层的语句可以把内嵌的子查询返回的结果当成一张表使用。子查询可以作为一个虚表被使用。

子表查询需要用括号把子表括起来。

一般语法中，我们会将子查询放在比较运算符的右边（便于增加代码可读性）

5.1单行子查询

单行子查询，只该查询的查询结果只有一个值时，我们称之为单行子查询，值得注意的是，这样的值，在子查询中，我们常常将其用于条件比较。

-- 查询雇员其薪资在雇员平均薪资以上
-- [1] 查询员工的平均薪资
select avg(e.sal) "AVGSAL"
from emp e

--[2] 查询满足条件的雇员
select *
from emp e
where e.sal > (select avg(e.sal) "AVGSAL" from emp e)

5.2多行子查询

多行子查询，我们只需把这条sql语句当成一个表即可，而且经常会与 in 、 some 、any 、all 、exists 联用。

-- 查在雇员中有哪些人是管理者
--【1】查询管理者
select distinct e.mgr
from emp e
where e.mgr is not null

--【2】查询指定列表的信息 in
select e.*
from emp e 
where e.empno in (select distinct e.mgr
                         from emp e
                         where e.mgr is not null)

5.3 from后的子查询

同样，将这条sql语句当成一个表来使用，就跟多表连接（关联）类似。

-- 每个部门平均薪水的等级
--【1】部门的平均薪资
select e.deptno,avg(e.sal) "AVGSAL"
from emp e
group by e.deptno

--【2】求等级
select vt0.deptno,vt0.avgsal,sg.grade
from (select e.deptno,avg(e.sal) "AVGSAL"
        from emp e
        group by e.deptno) VT0,salgrade sg
where vt0.avgsal between sg.losal and sg.hisal
-- 99 join on
select vt0.deptno,vt0.avgsal,sg.grade
from (select e.deptno,avg(e.sal) "AVGSAL"
        from emp e
        group by e.deptno) VT0 join salgrade sg on vt0.avgsal between sg.losal and sg.hisal

6. TOP-N

把select得到的数据集提取前n条数。

rownum:表示对查询的数据集记录的编号，从1开始。

-- 查询前10名雇员
select e.*,rownum
from emp e
where rownum <= 10

-- 查询按照薪资降序，前10名雇员
select e.*,rownum
from emp e
where rownum <= 10
order by e.sal desc

注意：

[1] order by 一定在整个结果集出现后才执行。

[2] rownum 在结果集出现后才有编号。

执行顺序：

...-> select -> rownum -> order by

[3] order by 正常情况下，永远最后执行，所以，这里的rownum就有可能会生变化。

上述例子正确语法：

-- 查询按照薪资降序，前10名雇员
select vt0.*,rownum
from (select e.*
     from emp e 
     order by e.sal desc) VT0
where rownum <= 10

把ruwnum冻结之后，我们再使用rownum进行条件过滤。

7.分页

-- 求查询6-10号的雇员
select vt0.*
from (select e.*,rownum "RN"
        from emp e 
        where rownum <= 10) VT0
where vt0.rn >= 6

（一般用于网页的分页）格式或规律：

求page=n,pagesize=size的数据

=>[(n-1)*size+1,n*size]

select vt0.*
from (select t.*, rownum “RN”
from table t 
where rownum <= n*size) VT0
where vt0.rn >= (n-1)*size+1

8.行转列

将上面的结果改成下面的结果：

姓名 语文 数学 英语

张三 78 88 98

王五 89 56 89

select ts.name,
sum(decode(ts.subject,'语文',ts.score)) "语文",
sum(decode(ts.subject,'数学',ts.score)) "数学",
sum(decode(ts.subject,'英语',ts.score)) "英语"
from test_score ts
group by ts.name