oracle学习第三天--多表关联

合集 - oracle数据库学习(4)

1.oracle-select第一天2024-07-302.oracle学习第二天--函数2024-07-31

3.oracle学习第三天--多表关联2024-08-05

4.oracle学习第四天-DDL/DML2024-08-05

收起

用到的数据表

emp表

dept表

salgrade表

一、笛卡尔积

1、笛卡尔积

a、多表查询：查询时从多张表中获取所需数据
b、单表查询的SQL语句：select字段列表from表明；
c、原理：在from后面加","号即可，例-form 表名1,表名2...
d、案例：

-- 查询雇员的名字和所在部门名字
-- 分析：雇员名字在emp表，部门名称在dept表
select * from emp, dept;
-- 添加连接条件后
select *from emp, dept
where emp.deptno = dept.deptno;

2、等值连接

a、原理：等值连接指连接条件是等值
b、案例：

-- 【1笛卡尔积】
from emp,dept
-- 【2过滤无效数据】
from emp e,dept d
where e.deptno = d.deptno and e.ename = 'SCOTT'
-- 【3获取需要的字段】
select e.ename, d.deptno, d.dname
from emp e,dept d
where e.deptno = d.deptno and e.ename = 'SCOTT';

3、不等值连接

1、原理：连接条件如果不是等值，就是不等值连接
2、案例

--[1]笛卡尔积
from emp e,salgrade sg
--[2] 过滤无效数据
from emp e,salgrade sg
where e.sal between sg.losal and sg.hisal
 -- [3] 条件
select e.ename,sg.grade
from emp e,salgrade sg
where e.sal between sg.losal and sg.hisal and e.ename = 'SCOTT';

4、自连接（了解即可）

a、原理：自己和自己连接，以不同的“视角”来看待同一张表
b、案例：

select e.ename, m.ename
from emp e, emp m
where e.mgr = m.empno and e.ename = 'SCOTT';

5、问题

多表关联后，我们会发现：表关联的条件和业务过滤的条件都放到where字句中不易维护。

二、JOIN连接

1、内连接

a、原理：内连接查询：查询两表或多表中交集部分数据。

b、语法和案例：

-- []中括号表示可以省略
select 字段列表
from 表1 [inner] join 表2 on 连接条件
[where 字句]

1.1 2张表关联

案例：

-- 查询员工的姓名及所属的部门名称
select emp.ename, dept.dname
from emp join dept on emp.deptno = dept.deptno;

代码是可以精简的，给表起个简单的别名

-- 查询员工的姓名及所属的部门名称
select e.ename, d.dname
from emp e join dept d on e.deptno = d.deptno;

1.2 3张表关联

案例：

-- 查询scott部门名称，薪资等级
-- on
select *
from emp e
	join dept d on e.deptno = d.deptno
	join salgrader sg on e.sal between sg.losal and sg.hisal
where e.ename = 'SCOTT'

1.3 N张表关联

原理：所有的多表查询本质上都是2张表查询。
案例：

-- 查询scott的管理者的名称、所在部门名称、薪资等级
select * 
from emp e
	join dept d on e.deptno = d.deptno
	join salgrader sg on e.sal between sg.losal and sg.hisal
where e.ename = 'SCOTT';

2、外联结

原理：当想让一个表中的所有数据都显示时，可以使用外连接。
分类：左外连接，右外连接

2.1左外连接

语法：

select 字段列表
from 表1 left [outer] join 表2 on 连接条件
[where 子句]

原理：以左表为驱动表，保留左表的全部数据，若右表（被驱动表）不能匹配关联条件（on），则以null值填充右表字段。

案例：

-- 左外连接：以left join关键字左边的表为驱动表，查询驱动表中所有数据，以及和驱动表匹配的右
select * 
from dept d
	left join emp e on d.deptno = e.deptno;
边表中的数据

2.2 右外连接

语法：

select 字段列表
from 表1 right [outer] join 表2 on 连接条件

原理：以右表为驱动表，保留驱动表及右表的全部数据，若被驱动表即左表不能匹配关联条件（on），则以null值填充左表字段。

注意：左外连接和右外连接时可以相互替换的，只需要调整连接查询时sql语句中表的先后顺序就可以了。

三、子查询

原理：子查询指的是在一个查询中嵌套了其他的一个查询。
案例：

-- from子查询
select ...
from (select ... from table)
where filed = value;
-- where子查询
select ...
from table
where field = (select ... from table2)

注意：
a、子查询一般出现在from和where子句中；
b、子查询要使用圆括号括起来。

1、from子查询

原理：from后面的子查询作为一张虚表使用。
案例：

select e1.deptno,e1.ename,e1.sal from (select * from emp where sal > 3000) 
e1;
-- 需求2:每个部门平均薪水的等级
-- 1求部门平均薪资
select e.deptno,avg(e.sal)
 from emp e
 group by e.deptno;
 --2求平均薪水的等级
select *
 from (select e.deptno,avg(e.sal) "AVGSAL" from emp e group by e.deptno) vt
     join salgrade sg on vt.avgsal between sg.losal and sg.hisal

2、where子查询

原理：子查询结果可能是一个结果集，也可以是一个确定的值。
a、如果子查询结果是一个数据集，可以吧数据集看出一张表（虚表）使用。
b、如果子查询的结果是一个确定的值，子查询作为值使用；
c、按照子查询返回的多行1列集合，子查询作为列表使用。

2.1单行子查询

原理：单场子查询返回一个确定的值，外部查询一般结合比较运算符使用。
案例1：查询有哪些人的薪水实在整个雇员的平均薪水之上的雇员

-- 1 整个雇员的平均水平
select avg(e.sal) from emp e
-- 2查询满足条件雇员
select * from emp e where e.sal >(select avg(e.sal) from emp e)

案例2：查询销售部SALES 所有的雇员

select e.ename,e.job,e.sal
from emp e
where e.deptno=(select d.deptno from dept d where d.dname='SALES');

2.2 多行子查询

原理：多行子查询返回多一列，可以看出一个list。作为list时，结合in、exists。结构图

案例：

-- 需求1::查询雇员中所有的管理者信息
-- 1 查询所有管理者(list)
select distinct e.mgr from emp e 
 -- 2 查找在list中的雇员信息
select * from emp e where e.empno in (select distinct e.mgr from emp e )

四、集合运算

1、并集

原理：intersect 返回两个查询共有的记录

 (select deptno from emp)
 intersect
 (select deptno from dept);

2、交集

原理：
 union all 返回各个查询的所有记录，包括重复记录。
 union 返回各个查询的所有记录，不包括重复记录。

(select * from emp)
 union all
 (select * from emp);
 (select * from emp)
 union all
 (select * from emp);

3、差集

原理：minus 返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

 (select deptno from dept)
 minus
 (select distinct deptno from emp)