Oracle多表查询，四种连接方式 [数据库教程]

Z时代
2024-01-10
分类：综合

database

一、聚合函数:（都会忽略null数据）
1、常用的有5种：将字段中所有的数据聚合在一条中
1、sum(字段名) ：求总和
2、avg(字段名) ：求平均值
3、max(字段名) ：求最大值
4、min(字段名) ：求最小值
5、count(字段名、*) ：统计行数
2、按部门编号，查询平均薪水，并且平均薪水<1300的不显示，结果按降序排序
select empno,avg(sal) as avgsal
from scott.emp
group by empno
having avg(sal)>=1300
order by avgsal desc;

说明：
SQL语句的执行顺序：一定是从上到下的！
group by 执行之后才会执行having、select中没有使用聚合函数的字段名必须写在这里
having 后不能使用字段的别名、可以聚合函数、一般字段名
order by 后面可以有：字段名、聚合函数、字段别名

注意：
Oracle 10g中才会出现的容错性：
having 语句可以写在group by 之前，不会报错，但是实际上执行的顺序还是先执行group by 后执行having子句

说明：
1、当一个查询中，出现聚合函数和没有使用聚合函数的字段，则该字段必须出现在group by子句中！
2、group by 字段1，字段2；会先按照字段1分组得到一个结果集，再按照字段2进行分组!
3、where 发生在group by 前！
4、where 后面不能有聚合函数！

二、多表查询：
表连接分类：内连接、外连接、交叉连接、自连接
1、内连接： [inner] join on
SQL语法格式：
语法1：
select *
from 表1 [inner] join 表2 on 表1.字段1=表2.字段1;
语法2：
select *
from 表1,表2
where 表1.字段1=表2.字段1;

说明：
内连接中的inner join 和 join 是等价的！但是建议为了程序的可读性
尽量不要省略inner!

  2、外连接：
分类：左外连接、右外连接、全连接！
1）、左外连接：left outer join
连接效果：
左侧的表中的全部数据都会被显示出来，但是右侧表的数据，
只有和左侧匹配上的字段才会被查询出来！否则都会显示null！
SQL语法格式：
语法1：
select *
from 表1 left outer join 表2
on 表1.字段1=表2.字段1;
语法2：
select *
from 表1 left outer join 表2
where 表1.字段1=表2.字段1(+);

2）、右外连接：right outer join
连接效果：
右侧的表中的全部数据都会被显示出来，但是左侧表的数据，
只有和右侧匹配上的字段才会被查询出来！否则都会显示null！
SQL语法格式：
语法1：
select *
from 表1 right outer join 表2
on 表1.字段1=表2.字段1;
语法2：
select *
from 表1 left outer join 表2
where 表1.字段1(+)=表2.字段1;

3）、全外连接：full/all outer join
SQL语法格式：
select *
from 表1 full outer join 表2
on 表1.字段1=表2.字段1;

3、自连接(self join)
自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。
示例：
在oracle的scott的schema中有一个表是emp。在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。
但现在我们只有一张emp表。所以我们可以采用自连接。自连接的本意就是将一张表看成多张表来做连接。我们可以这样来写SQL语句:
SQL> select work.ename worker,mgr.ename manager from scott.emp work, scott.emp mgr
2 where work.mgr = mgr.empno
3 order by work.ename;
WORKER MANAGER
---------- ----------
ADAMS SCOTT
ALLEN BLAKE
BLAKE KING
CLARK KING
FORD JONES
JAMES BLAKE
JONES KING
MARTIN BLAKE
MILLER CLARK
SCOTT JONES
SMITH FORD