Oracle数据库-多表联合查询&子查询

多表联合查询

当需要获取的数据分布在多张中,考虑使用联合查询
1、SQL92方式
2、SQL99方式

SQL92方式

1、笛卡尔积:将多个表的数据进行一一对应,所得到结果为多表的笛卡尔积。
  结果的数量为所有表的数量的乘积。
  select * from emp,dept 
2、等值连接筛选
  概念:先做表的笛卡尔积,然后筛选,筛选条件为等值筛选。
  注意:条件为字段的值相同来进行筛选,字段的名字可以不同
  查询员工姓名,工作,薪资,部门名称
  select * from emp,dept where emp.deptno=dept.deptno
    可以直接在select子句中使用字段直接获取数据,但是效率比较低。建议字段前加上表名
    注意:如果是公共字段,则必须声明表名
    select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno--等值连接筛选
    select emp.ename,emp.job,emp.sal,dept.dname,emp.deptno from emp,dept where emp.deptno=dept.deptno
    select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and sal>2000--给表使用别名
3、不等值连接
  查询员工姓名,工作,工资,工资等级
  select * from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal
4、自连接(自己与自己做笛卡尔积)
  查询员工姓名,工作,薪资,及上级领导姓名
  select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno
5、外连接
  左外连接:加在右边,显示左边对应字段没有值的数据
    查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
    select * from emp e,dept d where e.deptno=d.deptno(+)
  右外连接:加在左边,显示右边对应字段没有值的数据
    查询员工姓名,工作,薪资,部门名称及没有员工信息的部门
    select * from emp e,dept d where e.deptno(+)=d.deptno

SQL99方式

注意1:依然可以给表添加别名
注意2:如果使用on或者usering关键字对结果进行筛选,必须使用inner join作用表与表的连接,其中inner可以省略
注意3:外连接的 outer关键字可以省略不写
注意4:依然可以继续使用分组,having ,排序等
1、笛卡尔积:使用cross join 关键字
  select 内容 from 表名 cross join
  select * from emp cross join dept
2、筛选
  查询员工姓名,工作,薪资,部门名称
  1)自然连接:使用关键字 natural join
    使用:select 内容 from 表名 natural join 表名
    特点1:底层先笛卡尔积,然后按照所有的同名同值字段自动进行等值筛选。
    问题1:如果只想按照部分字段结果筛选怎么办?
    问题2:如果想按照字段名不同,但是值相同进行等值筛选怎么办?
    select * from emp natural join dept
    解决1:使用using关键字
      作用1:指明使用指定的字段对联合查询的结果进行等值筛选
      注意:指明的字段必须是两表的同名同值字段
      使用:select 内容 from 表名 inner join 表名 using(字段名,字段名,....)
      select * from emp inner join dept using(deptno)
    解决2:使用on关键字进行自定义连接条件筛选(等值筛选,不等值筛选)
      注意:普通筛选条件使用where进行筛选,不要使用on进行。好处:SQL语句的阅读性变强。
      使用:select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件
      select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000
  2)外连接:
    左外连接:select 内容 from 表名 left outer join 表名 on 连接条件
      查询员工姓名,工作,薪资,部门名称及没有部门的员工信息
      select * from emp e left outer join dept d on e.deptno=d.deptno
    右外连接:select 内容 from 表名 right outer join 表名 on 连接条件
      查询员工姓名,工作,薪资,部门名称及没有员工的部门信息
      select * from emp e right outer  join dept d on e.deptno=d.deptno
    全外连接:select 内容 from 表名 full outer join 表名 on 连接条件
      select * from emp e full  outer join dept d on e.deptno=d.deptno
  3)自连接:
    查询员工及其上级领导姓名
    select  e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno

三表联合查询

SQL92实现

特点:易于书写,难于阅读
缺点:92的SQL语句结构不清晰
用法:
  select  内容 (别名,连接符,去除重复,oracle函数,逻辑运算)
  from  表名1,表名2,表名3...
  where  条件(连接条件,普通筛选条件,where子句关键字)
  group by 分组字段
  having 多行函数筛选
  order by 排序字段
查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
  select e.*,d.dname,c.cname
  from emp e,dept d,city c
  where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null)
  order by e.sal 

SQL99实现

特点:难于书写,易于阅读
用法:
  select 内容 from 表名1
  inner join 表名2
  on 连接条件
  inner join 表名3
  on 连接条件
  where  普通筛选条件
  group by 分组
  having 多行函数筛选
  order by 排序
查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金
  select * from emp e
  inner join dept d
  on e.deptno = d.deptno
  inner join city c
  on d.loc =c.cid
  where e.sal>2000 or e.comm is not null
  order by e.sal

子查询

使用时机:当查询的筛选条件不明确时,考虑使用子查询。
1、单行子查询
2、多行子查询

单行子查询

使用时机:筛选条件不明确需要执行一次查询,并且查询结果为一个字段并值只有一个
注意:where子句中允许出现查询语句,该查询语句称为子查询
使用:select 内容 from 表名 where 字段名 比较运算符 子查询语句
  查询所有比雇员“CLARK”工资高的员工信息
  select * from emp where sal>(select sal from emp where ename ='CLARK')
  查询工资高于平均工资的员工的名字和工资
  select ename,sal from emp where sal>(select avg(sal) from emp )
  查询和soctt属于同一部门且工资比他低的员工资料
  select * from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT')
  查询职务和scott相同,雇佣时间早的员工信息
  select * from emp where job=(select job from emp where ename='SCOTT') and hiredate <(select hiredate from emp where ename='SCOTT')

多行子查询

使用:子查询的结果只有一个字段但是字段有n个值,考虑使用多行子查询,其实就是使用关键字
关键字1:any 任意
    select 内容 from 表名 where 字段名 比较运算符 any 子查询语句
关键字2:all 所有
    select 内容 from 表名 where 字段名 比较运算符 all 子查询语句
关键字3:in 表示任意存在,相当于 = any 
    select 内容 from 表名 where 字段名 in 子查询语句  
    select 内容 from 表名 where 字段名 not in 子查询语句  
  查询工资高于任意一个CLERK的所有员工信息
  select * from  emp where sal> any (select sal from emp where job='CLERK')
  查询工资高于所有SALESMAN的员工信息
  select * from emp where sal> all (select sal from emp where job='SALESMAN')
  查询部门20中同部门10的雇员工作一样的雇员信息
  select job from emp where deptno=10
  select * from emp where job  in (select job from emp where deptno=10) and deptno=20
  select * from emp where job = any (select job from emp where deptno=10) and deptno=20

上一篇:碎点篇—— oracle 语法和练习


下一篇:[20191125]oracel SQL parsing function qcplgte 2.txt