Oracle数据库笔记之各种连接及合并查询

1.等值连接
查询部门10中经理的员工编号、姓名、部门编号、部门名称及年薪
select empno,ename,e.deptno,dname,12*sal+nvl(comm,0)*12 from emp e,dept d where e.deptno=d.deptno and 


d.deptno=10 and job='MANAGER';


查询每个部门经理的员工编号、姓名、工资、部门编号和部门名称
select empno,ename,sal,d.deptno,dname
from emp e,dept d
where e.deptno = d.deptno
and job = 'MANAGER';
结果为：
EMPNO ENAME      SAL DEPTNO DNAME
----- ------ ------- ------ ----------
 7782 CLARK  2450.00     10 ACCOUNTING
 7566 JONES  2975.00     20 RESEARCH
 7698 BLAKE  2850.00     30 SALES


2.不等值连接
查询每个员工的姓名、工资及工资等级
select ename, sal, grade
  from emp e, salgrade s
 where sal between losal and hisal;


3.内连接--匹配的记录查询出来
语法：inner join 表名 on 条件
查询部门20中员工编号、姓名、部门编号和部门名称
select empno, ename, e.deptno, dname
  from emp e, dept d
 where e.deptno = d.deptno
   and e.deptno = 20;
<==>
select empno, ename, e.deptno, dname
  from emp e inner join dept d
    on e.deptno = d.deptno
 where e.deptno = 20;


4.外连接：
通过外链接可以把对于连接条件不成立的记录仍然包含最后的结果中
左外连接：当连接条件不成立的时候，等号左边仍然被包含
右外连接：当连接条件不成立的时候，等号右边仍然被包含


--按部门统计员工人数，要求显示部门号，部门名称，部门人数
select d.deptno,dname,count(empno) as deptno_number
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,dname
order by d.deptno;
查询员工编号、姓名、部门编号、部门名称并释放部门表中的数据
①select empno,
ename,
e.deptno,
d.deptno,
dname
    from emp e, dept d
   where e.deptno(+) = d.deptno;
②select empno,
ename,
e.deptno,
d.deptno,
dname
    from dept d left join emp e
 on d.deptno = e.deptno;
③select empno,
ename,
e.deptno,
d.deptno,
dname
    from emp e right outer join dept d
 on e.deptno = d.deptno;
④select empno,
ename,
e.deptno,
d.deptno,
dname
    from emp e, dept d
   where d.deptno = e.deptno(+);


5.自连接(表与自身连接)
查询自己和所属经理的姓名
select yuangong.ename, jingli.ename
  from emp yuangong, emp jingli
 where yuangong.mgr = jingli.empno;


查询员工SMITH的经理名字
select m.ename
  from emp e, emp m
 where e.mgr = m.empno
   and e.ename = 'SMITH';


6.层次查询
自连接虽然显示很直观，但自连接本质属于多表查询，仍然会产生笛卡尔集

不是适合操作大表，解决方式--》层次查询（层次查询是单表查询在某些情况下可以取代自连接）

层次查询提供了一个level的伪列
select level,ename,mgr 
from emp 
connect by 上一层的员工号=当前这一层的老板号
strat with 从树的哪个结点开始


select level,empno,ename ,mgr
from emp 
connect by prior empno=mgr
start with empno=7839 --king的员工号(只有根节点可以使用start with mgr is null)

order by level;

7.全连接
释放左右两个表中不匹配的数据
语法：full [outer] join 表名 on 条件
查询学生表和年级表中的信息(不匹配的信息也查询)
select sname,s.gid,g.gid,gname
  from student s full join grade g
on s.gid = g.gid;


8.合并查询
①union   合并查询并去重复
②union all  合并查询不去重复
③intersect  交集
④minus   差集


注意：被连接的两条SQL语句，查询的列数必须一致


1.select ename,sal,dept.deptno,mgr,dname
  from emp, dept
 where emp.deptno = dept.deptno
union all
select ename,sal,deptno,comm,job
  from emp
 where deptno = 30;


2.select ename, job, sal
  from emp
 where sal > 2000
minus
select ename, job, sal
  from emp
 where job = 'MANAGER';
结果为：
ENAME      JOB             SAL
---------- --------- ---------
FORD       ANALYST     3000.00
KING       PRESIDENT   5000.00
SCOTT      ANALYST     3000.00


3.select ename, job, sal
  from emp
 where sal > 2000
union
select ename, job, sal
  from emp
 where job = 'MANAGER';
结果为：
ENAME      JOB             SAL
---------- --------- ---------
BLAKE      MANAGER     2850.00
CLARK      MANAGER     2450.00
FORD       ANALYST     3000.00
JONES      MANAGER     2975.00
KING       PRESIDENT   5000.00
SCOTT      ANALYST     3000.00


4.select ename, job, sal
  from emp
 where sal > 2000
intersect
select ename, job, sal
  from emp
 where job = 'MANAGER';
结果为：
ENAME      JOB             SAL
---------- --------- ---------
BLAKE      MANAGER     2850.00
CLARK      MANAGER     2450.00
JONES      MANAGER     2975.00


查询入职日期是1981年以后的员工的工资级别
select ename, hiredate, sal, grade
  from emp, salgrade
 where sal between losal and hisal
   and to_char(hiredate,'yyyy') > 1981;