欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle之多表查询实例讲解

程序员文章站 2022-03-13 13:35:52
一、多表查询 一、多表查询 从多张表查询数据 , 根据需求分析 发现得到的数据来源为多张表 1.内连接 查询特点 作为关联查询的两张表,必须有完全匹配的条件数据 才会提取记录 1)隐式内连接 sel...

一、多表查询

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

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(员工表)

Oracle之多表查询实例讲解

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

salgrade(工资等级表)

Oracle之多表查询实例讲解

二、子查询

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

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统计每年入职的员工个数

要求显示结果如下:

Oracle之多表查询实例讲解

--例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
喎?>