Oracle数据库4--多表关联
1. 92语法多表关联
1.1笛卡尔积
-- 笛卡尔积 select * from emp,dept
1.2等值连接
-- 等值连接 -- 需求:查询雇员的部门名称 select e.ename,e.deptno,d.dname from emp e,dept d where e.deptno = d.deptno
1.3不等值连接
-- 不等值连接 -- 查询每个雇员的薪资等级 select e.ename,e.sal,sg.grade from emp e,salgrade sg where e.sal >= sg.losal and e.sal <= sg.hisal -- where e.sal between sg.losal and sg.hisal
1.4外连接
左外连接：左边的表作为主表，右边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示。
-- 左外连接（B） -- 需求:查询所有部门的雇员 select * from dept d,emp e where d.deptno = e.deptno(+)
右外连接: 右边的表作为主表，左边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示。
-- 右外连接（B） select * from emp e,dept d where e.deptno(+) = d.deptno;
1.5自连接
-- 查询每个雇员的上级领导 select e.ename "雇员",m.ename "领导" from emp e,emp m where e.mgr = m.empno -- 优化king select e.ename "雇员",nvl(m.ename,'boss') "领导" from emp e,emp m where e.mgr = m.empno(+)
1.6多余两张表的连接
如果有多个表参与查询，先把t1xt2笛卡尔积得到一个大表T1，再把T1xt3笛卡尔积得到一个另外的大表T2，依次类推。
所有的多表查询最终都是两种表的查询。
-- 查询SCO%T管理者的薪资等级 select e.ename,m.ename,sg.grade from emp e,emp m,salgrade sg where e.mgr = m.empno and (m.sal between sg.losal and sg.hisal) and e.ename = 'SCO%T' -- 查询雇员Scott所在部门名称和薪资等级 select e.*,d.*,sg.* from emp e,dept d,salgrade sg where e.deptno = d.deptno and e.sal between sg.losal and sg.hisal and e.ename = 'SCO%T'
2.99语法多表关联
92语法的主要问题:
[1]表的过滤条件和表的连接条件混合在一起，维护麻烦
[2]数据库的数据适合变化，根据where子句的执行规则，sql语言也会相应发生变化，给维护造成一定成本。
2.1笛卡尔积
-- 99 笛卡尔积 select * from dept d cross join emp e
2.2自然连接
NATURAL JOIN子句基于两个表中列名完全相同的列产生连接
[1]两个表有相同名字的列
[2]数据类型相同
[3]从两个表中选出连接列的值相等的所有行
-- [2]自然连接 select * from dept d natural join emp e
注意:自然连接最优的使用场景是：主外键关系且主外键字段只有一个。
2.3using关键字
using 主要用于指定连接字段。
[1] 按照指定的字段连接两个表。
[2] 选指定字段值相同的数据行。
2.4on关键字指定连接条件
自然连接的条件是基于表中所有同名列的等值连接，为了设置任意的连接条件或者指定连接的列，需要使用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'
2.5使用jion on 连接3张表
-- 查询SCO%T部门名称和薪资等级 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' -- 查询SCO%T的管理者名称和其管理者的薪资等级 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 = 'SCO%T'
2.6外连接
外连接在99语法中通过outer 关键字，按照主从表的位置可以分为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;
3.子查询
sql中查询是可以嵌套的。一个查询可以作为另外一个查询的条件、表。
SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);
3.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)
3.2多行子查询
-- 查在雇员中有哪些人是管理者 --【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)
多行子查询返回的结果可以作为 表 使用，通常结合in、some/any、all、exists。
3.3from后的子查询
-- 每个部门平均薪水的等级 --【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
3.4TOP-N
把select得到的数据集提取前n条数。
rownum:表示对查询的数据集记录的编号，从1开始。
-- 查询前10名雇员 select e.*,rownum from emp e where rownum <= 10
rownum和order-by：
-- 查询按照薪资降序，前10名雇员
select vt0.*,rownum
from (select e.*
from emp e
order by e.sal desc) VT0
where rownum <= 10
总结
[1] order by 一定在整个结果集出现后才执行。
[2] rownum 在结果集出现后才有编号。
3.5分页
-- 求查询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
3.6行转列
要想从上表中得到类似下面的结果：
姓名 语文 数学 英语
张三 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
