Oracle数据库4--多表关联

--笛卡尔积
select *
from emp,dept

--等值连接--需求:查询雇员的部门名称
selecte.ename,e.deptno,d.dname
fromemp e,dept d
where e.deptno = d.deptno

--不等值连接--查询每个雇员的薪资等级
selecte.ename,e.sal,sg.grade
fromemp e,salgrade sg
where e.sal >= sg.losal and e.sal <=sg.hisal
--where e.sal between sg.losal and sg.hisal

--左外连接（B）--需求:查询所有部门的雇员
select *
fromdept d,emp e
where d.deptno = e.deptno(+)

--右外连接（B）
select *
fromemp e,dept d
where e.deptno(+) = d.deptno;

--查询每个雇员的上级领导
selecte.ename "雇员",m.ename "领导"
fromemp e,emp m
where e.mgr =m.empno

--优化king
select e.ename "雇员",nvl(m.ename,'boss') "领导"
fromemp e,emp m
where e.mgr = m.empno(+)

--查询SCO%T管理者的薪资等级
selecte.ename,m.ename,sg.grade
fromemp 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.*
fromemp e,dept d,salgrade sg
where e.deptno = d.deptno and e.sal between sg.losal andsg.hisal
and e.ename = 'SCO%T'

--99 笛卡尔积
select *
from dept d cross join emp e

--[2]自然连接
select *
from dept d natural join emp e

selectfiled1,fild2,…
fromtable1 
join table2 oncondition1
     [join table3 on condition2]*

--查询出员工的部门名称
select *
from dept d joinemp e
on d.deptno =e.deptno
--查询scott的上级领导
selecte.ename,m.ename
from emp e joinemp m
on e.mgr =m.empno
where e.ename = 'SCO%T'

--查询SCO%T部门名称和薪资等级
selecte.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 andsg.hisal
where e.ename = 'SCO%T'
--查询SCO%T的管理者名称和其管理者的薪资等级
selecte.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 andsg.hisal
where e.ename = 'SCO%T'

selectfiled1,field2
from table1 left/right outer join table2 oncondition1
      [left/right outer join table3 on condition2]*

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

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

SELECTselect_list
FROM    table
WHEREexpr operator
        (SELECTselect_list
             FROM    table);

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

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

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

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

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

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

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

-- 查询按照薪资降序，前10名雇员

select vt0.*,rownum

from (select e.*

from emp e

order by e.sal desc) VT0

where rownum <= 10

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

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

selectts.name,
sum(decode(ts.subject,'语文',ts.score)) "语文",
sum(decode(ts.subject,'数学',ts.score)) "数学",
sum(decode(ts.subject,'英语',ts.score)) "英语"
fromtest_score ts
group by ts.name