Oracle之多表查询实例讲解

一、多表查询

一、多表查询 从多张表查询数据 ， 根据需求分析 发现得到的数据来源为多张表

1.内连接

查询特点 作为关联查询的两张表，必须有完全匹配的条件数据 才会提取记录

1）隐式内连接 select * from a,b where a.列=b.列

2）显式内连接 select * from a inner join b on a.列=b.列

2.外连接

1）左外连接 select * from a left join b on a.列=b.列

以左表为基准，左表数据全部提取，右表数据作为

补充显示，没有数据匹配 显示为null

2）右外连接

select * from b right join a on a.列=b.列

以右表为基准，右表数据全部提取，左表数据作为补充显示，

没有数据匹配 显示为null

说明：

1）内连接查询结果为两表的交集

左外查询结果为左表全部和右表中与左表有关联的内容，无数据显示null

右外查询结果为右表全部和左表中与左表有关联的内容，无数据显示null

2）内链接方式较外连接效率低，内链接存在一个消除笛卡尔集的过程，先查询两表所有数据进行一一关联（左表每条数据都会和右边每条数据合并成为一条完整数据），再通过where条件进行消除笛卡尔集

3.oracle特有外连接

使用符号 (+)

将符号（+）放在作为补充显示的表的列后面

4.自连接

自己跟自己做关联查询

规则 ：必须给表起别名用于区分两张一样的表

  select * from a a1,a a2 where a1.列=a2.列
  分析题目得到数据的来源都为同一张表

5.转换列值的显示

条件判断

1）通用条件表达式——–

case 列

when 列值 then 显示值

when 列值2 then 显示值2

when 列值3 then 显示值3

else

默认值

end ;—表达式结束

–2）oracle特有条件表达式

decode(列,列值1,显示值1，列值2,显示值2,’默认值’)

/*
  一、多表查询 从多张表查询数据
     根据需求分析 发现得到的数据来源为多张表
*/
--例：查询员工的信息和员工的部门信息
--步骤：
--1.定义查询数据  员工  部门
--2.定义数据来源  emp   dept
--3.加入条件

select * from emp;
--左外连接实现
select * from  emp left join dept on emp.deptno = dept.deptno;
--右外连接实现
select * from emp right join dept on emp.deptno=dept.deptno
--隐式内链接
select * from emp , dept where emp.deptno = dept.deptno; 
--显示内链接
select * from emp inner join dept on emp.deptno=dept.deptno;

/*
  1.内连接 
      查询特点 作为关联查询的两张表，必须有完全匹配的条件数据 才会提取记录
      1）隐式内连接  select * from a,b where a.列=b.列
      2）显式内连接  select * from a inner join b on a.列=b.列
*/



/*
  2.外连接
      1）左外连接  select * from a left join b on a.列=b.列
                以左表为基准，左表数据全部提取，右表数据作为
                补充显示，没有数据匹配 显示为null
      2）右外连接
               select * from b right join a on a.列=b.列
               以右表为基准，右表数据全部提取，左表数据作为
               补充显示，没有数据匹配 显示为null
*/

/*
  3.oracle特有外连接
    使用符号 (+)
    将符号（+）放在作为补充显示的表的列后面
*/

select * from emp , dept where emp.deptno = dept.deptno (+);

/*
  4.自连接
      自己跟自己做关联查询
      规则必须给表起别名用于区分两张一样的表

      select * from a a1,a a2 where a1.列=a2.列
      分析题目得到数据的来源都为同一张表
*/
--例1.查询员工的信息和员工的领导信息

--例2.查询员工编号 员工姓名 员工的领导编号，领导姓名，员工的部门名称

select e1.empno , e1.ename ,e2.empno,e2.ename, d.dname from emp e1 
         left join emp e2 on e1.mgr = e2.empno 
         left join dept d on e1.deptno = d.deptno -- 左外


--例3.在上面基础上 查询员工的工资等级 salgrade         
select * from salgrade ;

select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号 ,e2.ename 经理姓名,
          d.dname 部门名,s.grade 员工等级   
         from emp e1 
         left join emp e2 on e1.mgr = e2.empno 
         left join dept d on e1.deptno = d.deptno 
         left join salgrade s on e1.sal between s.losal and s.hisal 

--例4.在上面基础之上 再查询领导的工资等级

select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号 ,e2.ename 经理姓名,
          d.dname 部门名,s.grade 员工工资等级 ,s2.grade 领导工资等级
         from emp e1 
         left join emp e2 on e1.mgr = e2.empno 
         left join dept d on e1.deptno = d.deptno 
         left join salgrade s on e1.sal between s.losal and s.hisal 
         left join salgrade s2 on e2.sal between s2.losal and s2.hisal
--------------错误示例--------------------------------
select e.empno,e.ename,d.dname,s1.grade,
    m.empno m_empno,m.ename  m_ename,s1.grade
   from emp e,emp m,dept d ,salgrade s1
   where e.mgr=m.empno and e.deptno=d.deptno
   and   e.sal between s1.losal and s1.hisal
   and   m.sal between s1.losal and s1.hisal
--都从同一张表中查询，这样领导和员工的工资必须在同一个等级 
--与需求不同

--5.转换列值的显示
/*
  if grade ==1 显示 一级 else if grade==2 显示二级 else 默认值
  条件判断  
  通用条件表达式--------
  case 列
    when 列值  then  显示值
    when 列值2  then  显示值2
    when 列值3  then  显示值3
  else
    默认值
  end ;---表达式结束
--oracle特有条件表达式
  decode(列,列值1,显示值1，列值2,显示值2,'默认值')
*/


select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号 
                ,e2.ename 经理姓名,  d.dname 部门名,
          case s.grade  
            when 1  then '一级' 
            when 2  then '二级'
            when 3  then '三级'
            when 4  then '四级'
          else
            '五级'
          end 员工工资等级

          ,case s2.grade 
             when 1  then '一级' 
             when 2  then '二级'
             when 3  then '三级'
             when 4  then '四级'
             when 5  then '五级'  
          else
           null
          end 领导工资等级
         from emp e1 
         left join emp e2 on e1.mgr = e2.empno 
         left join dept d on e1.deptno = d.deptno 
         left join salgrade s on e1.sal between s.losal and s.hisal 
         left join salgrade s2 on e2.sal between s2.losal and s2.hisal

--oracle特有 ，decode(列,列值1,显示值1，列值2,显示值2,'默认值')        
select e1.empno 员工编号 , e1.ename 员工姓名 ,e2.empno 经理编号 
     ,e2.ename 经理姓名,  d.dname 部门名, 
      decode(s.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') 员工工资等级
     ,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级',null)  领导工资等级
      from emp e1 
      left join emp e2 on e1.mgr = e2.empno 
      left join dept d on e1.deptno = d.deptno 
      left join salgrade s on e1.sal between s.losal and s.hisal 
      left join salgrade s2 on e2.sal between s2.losal and s2.hisal

：

emp（员工表）

dept（部门表）喎? f/ware/vc/"="" target="_blank" class="keylink">vcd4ncjxwpjxpbwcgywx0pq=="这里写图片描述" src="/uploadfile/collfiles/20180426/20180426090446584.png" title="\" />

salgrade（工资等级表）

二、子查询

在查询语句中嵌套查询语句 不限制一条

1单行子查询 ：子查询语句返回单列单值

select * from a where a.列=(select语句必须返回单列单值)

2多行子查询 子查询语句①返回单列多值（使用in进行条件判断），或者②多列多值（作为虚拟表使用）

① select * from a where a.列 in (select语句必须返回单列多值)

② select * from a,(select语句返回多列多值) t where a.列 =t.列

3.子查询的特殊使用
普通子查询的执行顺序—-> 先执行括号内的子查询语句 得到结果用于主查询使用

exists 存在

1）作用：

是判断 sql语句结果集是否存在

exists(sql查询语句) 没有结果集 false

有结果集 true

2）执行数序

exists作为子查询如果子查询语句关联主查询的列值 执行顺序发生更改

先走主查询得到列值 用于子查询的判断 满足条件 返回当前主查询记录

3）in和exists的使用场景区别

根据表中的记录分析 如果主查询的表记录少 推荐使用exists

如果子查询的表记录少 推荐使用in

先走的sql语句记录结果少 效率高

/*
  二、 子查询的使用
       在查询语句中嵌套查询语句 不限制一条
       1）单行子查询   
              select * from a where a.列=(select语句必须返回单列单值)
       2）多行子查询   
            ① select * from a where a.列 in (select语句必须返回单列多值)
           ② select * from a,(select语句返回多列多值) t where a.列 =t.列
*/         

--例1：查询比员工7654工资高，同时从事和7788相同工作的员工信息？
--1.员工信息 
--2.emp
--3.条件 sal>7654的工资  job=7788的工作 

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

--例2；查询每个部门的最低工资，和最低工资的员工信息及员工的部门名称
--员工信息 员工的部门 部门最低工资


--内链接
select e.*,d.* from 
    emp e,dept d, (select min(sal) m_sal from emp group by deptno) ms 
where e.deptno = d.deptno and e.sal = ms.m_sal     

--外连接 
  select e.*,d.* from 
    emp e
  right join dept d on e.deptno = d.deptno
  right join (select min(sal) m_sal from emp group by deptno) ms 
  on ms.m_sal = e.sal   

  insert into emp values (1112,'zs','job',7369,'1990/11/11',800.00,800.00,20)       

select * from emp
  --例3：查询不是领导的员工信息
--1.员工信息
--2.emp
--3.不是领导
---如果其余 > < =  in  判断null值 unkonw
   select distinct mgr from emp where mgr is not null --查询出领导id

   select * from emp where empno 
      not in (select distinct mgr from emp where mgr is not null); 

--例4.查询每个部门的最低工资，和最低工资的员工信息及员工的部门名称   
--emp员工表 ，dept部门表 ， 部门最低工资表   
select deptno,min(sal) from emp group by deptno; --部门最低工资表

select e.*,d.* from
       emp e,dept d, (select deptno,min(sal) m_sal from emp group by deptno) ms
     where e.deptno = d.deptno and e.sal = ms.m_sal

--错误示例： 
--查询结果是各部门工资，是否在  800  950 1300 中 
select * from emp where sal in (select min(sal) from emp group by deptno) 
/*
  3.子查询的特殊使用
    普通子查询的执行顺序----> 先执行括号内的子查询语句 得到结果用于主查询使用
  exists 存在
    1）作用是判断 sql语句结果集是否存在
    exists(sql查询语句) 没有结果集 false
                        有结果集   true
    2）执行数序                     
   exists作为子查询如果子查询语句关联主查询的列值 执行顺序发生更改
          先走主查询得到列值 用于子查询的判断 满足条件 返回当前主查询记录
   3）in和exists的使用
      根据表中的记录分析  如果主查询的表记录少 推荐使用exists
                          如果子查询的表记录少 推荐使用in
      先走的sql语句记录结果少 效率高

*/
--例.查询有员工的部门信息
--1.查询数据：部门信息
--2.数据来源：dept
--3.条件：部门有员工
select distinct deptno from emp; --有员工的部门
-- in 子查询
select * from dept where deptno in (select distinct deptno from emp);
--exists

select *  from dept where exists (select * from emp where emp.deptno = dept.deptno );

三、分页查询

分页提取特定的条数 mysql limit 6 ,5 扫描11行

limit 900000 ,10 扫描900010行

1.rownum：

1）概念

oracle使用rownum 是一个伪列 数据库提取记录才会生成的数值 1,2,3,4

作用是用于实现oracle的分页 必须使用子查询实现

2）执行流程（带条件）

a.查询emp表，生成emp伪表

b.成一个rownum

c.根据分页条件判断该rownum是否与该条件匹配

d.条件匹配 取出该条记录

d.生成第二个rownum重复c操作

3)别名问题

由于rownum的where判断执行在select关键字之前，

当前查询中的rownum别名不能用于条件中做判断，别名只可以用于外部条件判断

见例6的最后一个查询

2.rowid 是数据库保存记录时候生成的真实物理地址 唯一不变

作用： 数据库操作记录使用

索引值→rowid->将rowid换算成一行数据的物理地址->得到一行数据

      第一部分6位表示：该行数据所在的数据对象的 data_object_id；
      第二部分3位表示：该行数据所在的相对数据文件的id;
      第三部分6位表示：该数据行所在的数据块的编号；
      第四部分3位表示：该行数据的行的编号；

/*
  三、分页提取特定的条数   mysql limit 6 ,5        扫描11行
                                 limit 900000 ,10  扫描900010行
     1.rownum：  1）概念
               oracle使用rownum 是一个伪列 数据库提取记录才会生成的数值 1,2,3,4
              作用是用于实现oracle的分页  必须使用子查询实现
               2）执行流程（带条件）
               a.查询emp表，生成emp伪表
               b.成一个rownum
               c.根据分页条件判断该rownum是否与该条件匹配
               d.条件匹配 取出该条记录
               d.生成第二个rownum重复c操作
              3)别名问题
              由于rownum的where判断执行在select关键字之前，
              当前查询中的rownum别名不能用于条件中做判断，别名只可以用于外部条件判断
              见例6的最后一个查询

*/

--例1：提取员工表前三行

select rownum, emp.* from emp where rownum <4;

--例2：提取4行之后的
--错误示例
select rownum,emp.* from emp where rownum >3; --错误
--生成第一个rownum，进行条件判断时不符合，无法提取结果，结束查询，结果为空--

--解决方案，先查询带rownum的伪表
select rownum,emp.* from emp

--查询伪表，选出4条以后的数据
select * from (select rownum r,emp.* from emp) re where re.r >3;


--例4：提取工资排行前三的员工
--错误示例：虽然查询了结果，但是似乎并不是前工资排名前三的员工
select rownum ,emp.* from emp where rownum <4 order by sal desc;
--原因分析：where条件的执行 在 order by 之前 ，也就是先生成了rownum之后，才进行了排序
         -- 显然这时候序号已经生成好了 
--正确示例 
select * from emp order by sal desc --先排序

select rownum ,e.* from (select * from emp order by sal desc) e   where rownum <4;

--例5.提取6----10的记录
--扫描全表生成伪表，再进行提取分页(表数据多时效率极低)
select * from (select rownum r, e.* from emp e ) er where er.r > 5 and er.r <11;
--提高效率后的写法，只扫描10行提取分页生成伪表
select * from (select rownum r, e.* from emp e where rownum < 11) er where er.r > 5;

--例6.排序加分页
--a排序
select * from emp order by sal desc 
--b生成前10条伪表
select rownum , t1.* 
        from (select * from emp order by sal desc  ) t1
        where rownum <11
--提取6到10
select * from
    (select rownum r , t1.* from (select * from emp order by sal desc  ) t1
        where rownum <11) t2 where t2.r >4



/*
    2.rowid  是数据库保存记录时候生成的真实物理地址 唯一不变
         数据库操作记录使用
         索引值→rowid->将rowid换算成一行数据的物理地址->得到一行数据
          第一部分6位表示：该行数据所在的数据对象的 data_object_id；
          第二部分3位表示：该行数据所在的相对数据文件的id;
          第三部分6位表示：该数据行所在的数据块的编号；
          第四部分3位表示：该行数据的行的编号；


     rownum 是查询记录生成 根据条件 排序 rownum的值不一样
           用于分页

*/

select rowid,emp.* from emp;

练习

--例7.找到员工表中薪水大于本部门平均薪水的员工
--员工信息 部门平均薪水
--员工表 部门平均薪水表
--条件 员工薪水大于本部门平均薪水

--1）查询部门薪水伪表
select deptno, avg(sal) from emp group by deptno 
--2）表联查 关联条件  e.deptno = a_sal.deptno
--        需求条件  e.sal > a_sal.a
select *  from 
    emp e, (select deptno, avg(sal) a from emp group by deptno) a_sal
where e.deptno = a_sal.deptno and  e.sal > a_sal.a

例8统计每年入职的员工个数

要求显示结果如下:

--例8统计每年入职的员工个数

select to_char(hiredate,'yyyy') hire_year  ,count(1) count
    from emp group by to_char(hiredate,'yyyy')


---处理格式 使用decode尝试竖起来一列
select decode(t.hire_year,'1987',t.hire_count) "1987" 
 from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
       from emp group by to_char(hiredate,'yyyy')) t

--聚合函数忽略null值的记录
select avg(decode(t.hire_year,'1987',t.hire_count)) "1987" 
 from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
       from emp group by to_char(hiredate,'yyyy')) t
-- 使用聚合函数补全其余的列，
select avg(decode(t.hire_year,'1980',t.hire_count)) "1980",
       avg(decode(t.hire_year,'1981',t.hire_count)) "1981" ,
       avg(decode(t.hire_year,'1982',t.hire_count)) "1982" ,
       avg(decode(t.hire_year,'1987',t.hire_count)) "1987"        
 from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
       from emp group by to_char(hiredate,'yyyy')) t   
--加入sum求和运算补全total     
 select sum(t.hire_count) total,
       avg(decode(t.hire_year,'1980',t.hire_count)) "1980",
       avg(decode(t.hire_year,'1981',t.hire_count)) "1981" ,
       avg(decode(t.hire_year,'1982',t.hire_count)) "1982" ,
       avg(decode(t.hire_year,'1987',t.hire_count)) "1987"        
 from
(select to_char(hiredate,'yyyy') hire_year,count(1) hire_count
       from emp group by to_char(hiredate,'yyyy')) t 

四、集合使用

1.交集 取两个集合共同的部分 intersect a(1,2,3) b(2,3,4) a 交b (2,3)

2**.并集**

1)取两个集合所有的部分 union a(1,2,3) b(2,3,4) a 并b (1,2,3,4)

2)合并所有的数据包含重复 union all a(1,2,3) b(2,3,4) a 并b (1,2,3,2,3,4)

3.差集 从一个集合去掉另外一个集合剩余的部分 minus a(1,2,3) b(2,3,4) a 差b (1)

a 集合的使用场景：用于跨表的数据合并

b 规则 合并的①数据列数一致 ②类型一致

/*
  四、集合运算
  1.交集 取两个集合共同的部分 intersect   a(1,2,3) b(2,3,4)  a 交b (2,3)
  2.并集 1)取两个集合所有的部分 union       a(1,2,3) b(2,3,4)  a 并b (1,2,3,4)
         2)合并所有的数据包含重复 union all   a(1,2,3) b(2,3,4)  a 并b (1,2,3,2,3,4)
  3.差集 从一个集合去掉另外一个集合剩余的部分 minus  a(1,2,3) b(2,3,4)  a 差b (1)
  集合的使用场景：
        用于跨表的数据合并
  规则 合并的数据列数一致 类型一致
*/


--例1：工资大于1500,或者是20部门下的员工
--数据源
select * from emp where sal > 1500
select * from emp where deptno =20
--or 方式
select * from emp where sal > 1500 or deptno =20

--集合方式 并集
select * from emp where sal > 1500
union
select * from emp where deptno =20

--例2：工资大于1500,并且是20部门下的员工
--and方式
select * from emp where sal > 1500
                 and deptno =20
select * from emp where sal > 1500
intersect
select * from emp where deptno =20

--例3：1981年入职的普通员工（不包括经理，总裁）
select * from emp where  job  in ('manager','president')
select * from emp where to_char (hiredate,'yyyy') =1981
--and 方式

select * from emp where to_char (hiredate,'yyyy') =1981
                  and job  not in ('manager','president')
--集合方式 差集
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where  job  in ('manager','president')

--例4
--创建manager表作为公司的所有领导
create table manager (
   mid number(9),
   mname varchar(10) 
)
select * from manager 
insert into manager values(1,'zs');
insert into manager values(2,'lis');
commit;

--------查询公司的所有员工的姓名 编号 职位
--表 员工表  领导表
select mid 员工编号 ,mname 员工名称 from manager
union
select empno ,ename  from emp

喎?>