Oracle 多表连接查询（内连接、左/右外连接、全连接）以及分组查询的运用详解

多表查询的连接

连接分为两种：内连接与外连接

1.内连接

1.1等值连接

SELECT 
e.empno, e.ename, e.job, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

1.2自身关联

--查询出每一位雇员的编号、姓名、职位、领导的姓名

--第一步：查询出每个雇员的编号、姓名、职位
   SELECT e.empno,e.ename, e.job
   FROM emp e;
--第二步：查询领导信息，加入自身关联
   SELECT e.empno,e.ename, e.job, me.ename 领导
   FROM emp e, emp me
   WHERE e.mgr=me.empno;

2.外连接

外连接：分为左外连接，右外连接和全外连接

2.1连接语法

只要是有 JOIN 的语法，都是 SQL：1999 的写法

2.1.1JOIN…ON() 子句

表A JOIN 表B ON(指定一个可以消除笛卡尔积的关联条件)

 SELECT e.empno,e.ename,e.job,e.sal,d.dname
 FROM emp e JOIN dept d
 ON(e.deptno=d.deptno);

2.1.2JOIN…USING() 子句

表A JOIN 表B USING(指定一个可以消除笛卡尔积的关联字段)

 SELECT e.empno,e.ename,e.job,e.sal,d.dname
 FROM emp e JOIN dept d
 USING(deptno);

• 注意：
  • USING()指定的关联字段后，SELECT不能出现被关联表的关联字段。如,错误例子

--错误例子
SELECT d.deptno,e.ename
FROM emp e JOIN dept d
USING(deptno);

2.1.3自然连接

寻找关联字段作为内连接的等值连接

两个表必须有关联字段才可以

 SELECT e.empno,e.ename,e.job,e.sal,d.dname
 FROM emp e NATURAL JOIN dept d;

2.2左外连接

左外连接就是在等值连接的基础上加上主表中的未匹配数据

--查询出每一位雇员的编号、姓名、职位、部门编号、部门名称

   SELECT e.empno,e.ename,e.job,d.deptno,d.dname
   FROM emp e LEFT OUTER JOIN dept d
   ON e.deptno=d.deptno;

• Oracle 支持另一种写法 (+)

SELECT e.empno,e.ename,e.job,d.deptno,d.dname
FROM emp e , dept d
WHERE e.deptno=d.deptno(+);

2.3右外连接

右外连接是在等值连接的基础上加上被连接表的不匹配数据

--查询出每一位雇员的编号、姓名、职位、部门编号、部门名称

SELECT e.empno,e.ename,e.job,d.deptno,d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

2.4全外连接

全外连接是在等值连接的基础上将左表和右表的未匹配数据都加上

SELECT e.empno,e.ename,e.job,d.deptno,d.dname
FROM emp e FULL OUTER JOIN dept d
ON e.deptno=d.deptno;

2.5外连接Demo

--表A	 id		name
		  1 	 Tom
		  2		 John
		  3		 Amy
		 
--表B     id 	A_id	job
		  1		 2		 Student
		  2		 4		 Teacher
		  
--左外连接
		Tom		null
		John	Student
		Amy		null
		
--右外连接
		John	Student
		null	Teacher
		
--全外连接
		Tom		null
		John	Student
		Amy		null
		null	Teacher

3.分组统计查询

3.1聚合函数（分组函数）

聚合函数就是基于多行数据返回一行结果

• 分组函数可以在没有分组的时候单独用使用，但不可以和非分组函数同时使用

	说明
SUM([DISTINCT] 数值列);	对所有数值求和
COUNT([DISTINCT] 列);或者COUNT(*);	求非空的记录、数据个数
MAX([DISTINCT] 数值日期列);	求最大值
MIN([DISTINCT] 数值日期列);	求最小值
AVG([DISTINCT] 数值列);

3.2分组查询

当数据重复的时候分组才有意义

SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

• 分组查询SELECT后，可以出现分组字段和统计函数

3.2.1Demo

--按照部门编号分组，求出每个部门的人数，平均工资

SELECT deptno, COUNT(empno), AVG(sal)
FROM emp
GROUP BY deptno;

--按照职位分组，求出每个职位的最高和最低工资

SELECT job, MAX(sal), MIN(sal)
FROM emp
GROUP BY job;

3.2.2分组函数嵌套

分组函数嵌套后，查询中不能有其他字段

--按照职位分组，统计平均工资最高的工资
    1、先统计出各个职位的平均工资
    SELECT job,AVG(sal)
    FROM emp
    GROUP BY job;
   
    2、再统计平均工资最高的工资(不能出现任何字段)
    SELECT MAX(AVG(sal))
    FROM emp
	GROUP BY job;

3.2.3HAVING的使用

3.2.3.1HAVING & WHERE 的区别

• WHERE：是在执行GROUP BY操作之前进行的过滤，表示从全部数据之中筛选出部分的数据，在WHERE之中不能使用统计函数；
• HAVING：是在GROUP BY分组之后的再次过滤，可以在HAVING子句中使用统计函数；

SELECT [DISTINCT] *|分组字段1 [别名] [,分组字段2 [别名] ,…] | 统计函数
FROM 表名称 [别名], [表名称 [别名] ,…]
[WHERE 条件(s)]
[GROUP BY 分组字段1 [,分组字段2 ,…]]
[HAVING 分组后的过滤条件（可以使用统计函数）]
[ORDER BY 排序字段 ASC | DESC [,排序字段 ASC | DESC]];

3.3分组查询总和运用

--显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列

1. 第一步：查询出所有的非销售人员的信息
   SELECT * FROM emp WHERE job<>'SALESMAN';

2. 第二步：按照职位进行分组，并且使用SUM函数统计
   SELECT job,SUM(sal)
   FROM emp
   WHERE job<>'SALESMAN'
   GROUP BY job;
  
3. 第三步：月工资的合计是通过统计函数查询的，所以现在这个对分组后的过滤要使用HAVING子句完成
   SELECT job,SUM(sal)
   FROM emp
   WHERE job<>'SALESMAN'
   GROUP BY job
   HAVING SUM(sal)>5000;

4. 第四步：按照升序排列
   SELECT job,SUM(sal) sum
   FROM emp
   WHERE job<>'SALESMAN'
   GROUP BY job
   HAVING SUM(sal)>5000
   ORDER BY sum ASC;

--显示部门编号不是30的，的部门详细信息（部门编号、部门名称、部门人数、部门月薪资总和），并要求部门月工资总和大于$8000，输出结果按部门月薪资的总和降序排列

-- 显示部门编号不是30的，的部门详细信息
    -- 查表  emp , dept 
    -- 字段  deptno dname empno sal 
   
    -- 查出信息 
    select * from  emp e right join dept d on e.deptno = d.deptno; 
    -- 部门不是 30 
    select d.deptno,d.dname,e.empno,e.sal from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30;
     
    --分组
    select d.deptno,d.dname,count(e.empno),sum(e.sal) 
    from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30
    group by d.deptno,d.dname;
    
    -- 综合大于 80000
    select d.deptno,d.dname,count(e.empno),sum(e.sal) 
    from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30
    group by d.deptno,d.dname 
    having sum(e.sal) > 8000;
    
    -- 降序 
    select d.deptno,d.dname,count(e.empno),sum(e.sal) 
    from  emp e right join dept d 
    on e.deptno = d.deptno
    where e.deptno != 30
    group by d.deptno,d.dname 
    having sum(e.sal) > 8000
	order by sum(e.sal) desc;