Oracle查询

多表查询

笛卡尔积：是两张表的乘积，实际上没有多大意义。

格式：select * from 表1，表2；

多表的综合查询：

内联接:
       隐式内联接: 
           等值内联接:   where e1.deptno = d1.deptno;
           不等值内联接:  where e1.deptno <> d1.deptno;
           自联接: 自己连接自己
       显式内连接：
       格式：inner join     on（inner可以省略）

--查询员工编号,员工姓名,经理的编号,经理的姓名
select e1.empno,e1.ename,e1.mgr,m1.ename from emp e1,emp m1 where e1.mgr=m1.empno;

--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名
select e1.empno,e1.ename,d1.DNAME,e1.mgr,m1.ename from emp e1,emp m1,dept d1 
where e1.mgr=m1.empno and d1.deptno=e1.deptno;

--查询员工编号,员工姓名,员工的部门名称,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.DNAME,e1.mgr,m1.ename,d2.DNAME 
from emp e1,emp m1,dept d1,dept d2 
where e1.mgr=m1.empno and d1.deptno=e1.deptno and d2.deptno=m1.deptno;
--注意要使用两张dept才可以，因为有可能员工和经理不在一个部门。如果使用一张dept表就是强制员工和经理在同一个部门。

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称
select e1.empno,e1.ename,d1.DNAME,s1.GRADE,e1.mgr,m1.ename,d2.DNAME 
from emp e1,emp m1,dept d1,dept d2,SALGRADE s1
where e1.mgr=m1.empno and d1.deptno=e1.deptno and d2.deptno=m1.deptno and e1.sal between s1.LOSAL and s1.HISAL;

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
select e1.empno,e1.ename,d1.DNAME,s1.GRADE,e1.mgr,m1.ename,d2.DNAME,s2.GRADE 
from emp e1,emp m1,dept d1,dept d2,SALGRADE s1,SALGRADE s2
where e1.mgr=m1.empno and d1.deptno=e1.deptno and d2.deptno=m1.deptno 
and e1.sal between s1.LOSAL and s1.HISAL and m1.sal between s2.losal and s2.hisal;

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
--将工资等级 1,2,3,4 显示成 中文的 一级 二级二级...
--方式1：
select e1.empno,e1.ename,d1.DNAME,
case s1.GRADE
when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
else '四级' end "等级",
e1.mgr,m1.ename,d2.DNAME,
case s2.GRADE
when 1 then '一级'
when 2 then '二级'
when 3 then '三级'
else '四级' end "等级"
from emp e1,emp m1,dept d1,dept d2,SALGRADE s1,SALGRADE s2
where e1.mgr=m1.empno and d1.deptno=e1.deptno and d2.deptno=m1.deptno 
and e1.sal between s1.LOSAL and s1.HISAL and m1.sal between s2.losal and s2.hisal;
--方式2：
select e1.empno,e1.ename,d1.DNAME,
decode(s1.GRADE,1,'一级',2,'二级',3,'三级','四级')"等级",
e1.mgr,m1.ename,d2.DNAME,
decode(s2.GRADE,1,'一级',2,'二级',3,'三级','四级')"等级"
from emp e1,emp m1,dept d1,dept d2,SALGRADE s1,SALGRADE s2
where e1.mgr=m1.empno and d1.deptno=e1.deptno and d2.deptno=m1.deptno 
and e1.sal between s1.LOSAL and s1.HISAL and m1.sal between s2.losal and s2.hisal;

显式內联接：
--查询员工姓名和员工部门所处的位置
select ename,loc from emp inner join dept on emp.deptno=dept.deptno;
select ename,loc from emp join dept on emp.deptno=dept.deptno;

外联接

/*
    外连接: (标准,通用写法)
       左外连接: left outer join 左表中所有的记录,如果右表没有对应记录,就显示空
       右外连接: right outer join 右表中的所有记录,如果左表没有对应记录,就显示空
       outer 关键字可以省略  

    Oracle中的外连接: (+) 实际上是如果没有对应的记录就加上空值
          select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);            
*/
--查询所有员工及其部门的信息
--通用写法
select * from emp left outer join dept on emp.deptno=dept.deptno;
--oracle特有写法
select * from emp,dept where emp.deptno=dept.deptno(+);

--查询所有部门和对应的员工信息
--通用写法
select * from emp right outer join dept on emp.deptno=dept.deptno;
--oracle特有写法
select * from emp,dept where emp.deptno(+)=dept.deptno;

Oracle外联接不可两边写加号，只能一边有

   子查询: 查询语句中嵌套查询语句; 用来解决复杂的查询语句
        查询最高工资的员工信息     
     单行子查询: > >= = < <= <> !=

     多行子查询: in not in  >any >all exists not exists

           查询领导信息

–查询最高工资的员工信息
–1.查询出最高工资 –5000
select max(sal) from emp;
–2. 工资等于最高工资
select * from emp where sal = (select max(sal) from emp);

–查询出比雇员7654的工资高,同时和7788从事相同工作的员工信息
–1.雇员7654的工资 1250
select sal from emp where empno = 7654;
–2.7788从事的工作 ANALYST
select job from emp where empno = 7788;
–3.两个条件合并
select * from emp where sal > 1250 and job = ‘ANALYST’;

select * from emp where sal > (select sal from emp where empno = 7654) and job = (select job from emp where empno = 7788);

–查询每个部门最低工资的员工信息和他所在的部门信息
–1.查询每个部门的最低工资,分组统计
select deptno,min(sal) minsal from emp group by deptno;
–2.员工工资等于他所处部门的最低工资
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal = t1.minsal;
–3.查询部门相关信息
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1,
dept d1
where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;

分页查询

在Oracle里只能使用rownum的子查询。

集合运算

并集：
union：去除重复的，还会进行排序
union all：不去除重复的，不会进行排序。可能结果不是同一个表。
交集：intersect
差集：minus
注意事项：

1.列的类型要一致。
2.列的顺序尽量一致
3.列的数量要一致，如果不足用null值补充，也可以与列同类型数值补充。