oracle 连接查询（内、外连接、自连接、子查询）

内连接：
一般使用inner join来实现。其中inner关键字可以省略
同时必须在from子句之后定义一个on子句，用来指定连接条件

外连接
1 左外连接：关键字为left outer join或left join
2 右外连接：关键字为right outer join或right join
3 完全外连接：关键字为full outer join 或　full join

补充说明：
1）外连接除了会把满足连接条件的记录查出来外，还可以把不满足连接条件的查出来。
2）外连接分为左外连接、右外连接和全外连接。
左外连接 LEFT OUTER JOIN：以JOIN左侧表作为驱动表（所有记录都会查出来），那么当该表中某条记录不满足链接条件时，来自右侧表中的字段全部填NULL.
右外连接 RIGHT OUTER JOIN:以JOIN右侧表作为驱动表（所有记录都会查出来），那么当该表中某条记录不满足链接条件时，来自左侧表中的字段全部填NULL.
全外连接 FULL OUTER JOIN:就是左右表不满足的都会查出来。

自连接
自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单张表的多个列
自连接是为了解决同类型数据但又存在上下级关系的树状结构数据时使用。

关联查询
从多张表中查询对应记录的信息。
关联查询的重点在于这些表中的记录的对应关系，这个关系也称为连接条件

当两张表有同名字段时，SELECT子句中必须明确指定该字段来自哪张表，在关联查询中，表名也可以添加别名。

关联查询时要添加查询条件，否在会出现笛卡儿积
笛卡尔积通常是一个毫无意义的结果集，他的记录数是所有参与查询的表的记录数乘积的结果。
要避免出现。数据量大时极易出现内存溢出等现象。
N张表关联查询要有至少N-1个查询条件。
不满足连接条件的记录是不会在关联查询中被查询出来的

子查询
子查询是一条SELECT语句，但它是嵌套在其他SQL中，为的是给该SQL提供数据以支持其执行操作。

此处是说的其他SQL，而不是查询语句中，子查询是可以嵌套在其它各种SQL的，比如DDL，就可以通过子查询创建一张查询出来数据的表。
子查询可分为单行子查询、多行子查询及多列子查询。
单行单列子查询：常用于过滤条件，可以配合>、=、<、>=、<=使用
多行单列子查询：常用于过滤条件，判断=时用in，判断>、>=等时用ANY、ALL
多行多列子查询：常当作一张表看待

–一、内连接、外连接练习10.29

–1、查询所有工种为CLERK的员工的姓名及其部门名称
select job,ename,dname from emp e join dept d on e.deptno=d.deptno where job=‘CLERK’;
–2、显示雇员名，雇员工资及所在部门的名字，并按照部门排序
select ename,sal,dname,e.deptno from emp e join dept d on e.deptno=d.deptno order by deptno;
–3、查询所有部门及其员工信息，包括那些没有员工的部门
select e.,d. from emp e right join dept d on e.deptno=d.deptno;
–4、查询所有员工及其部门信息，包括那些还不属于任何部门的员工
insert into emp(empno,ename,job) values(9001,‘EALA’,‘CLERK’);
select e.,d. from emp e left join dept d on e.deptno=d.deptno;
–5、显示部门号为10的部门名字、员工名和工资
select dname,ename,sal from emp e,dept d where e.deptno=d.deptno;
–6、列出在部门“SALES”（销售部）工作的员工姓名、基本工资、雇用日期、部门名称
select ename,sal,hiredate,dname from emp e,dept d where e.deptno=d.deptno and dname=‘SALES’;
–7、显示各个员工的姓名，工资以及工资的级别(非等值连接)
select ename,sal,grade from emp e join salgrade on sal between losal and hisal;
select ename,sal,grade from emp e,salgrade where sal between losal and hisal;

–二、子查询综合练习10.29
–1. 查询最高工资员工的名字，工作岗位
select ename,job,sal from emp where sal=(select max(sal) from emp);
–2. 算出部门 30 中得到最多奖金的员工姓名
select ename,comm from emp where comm=(select max(comm) from emp where deptno=30);
–3. 找出工资比ford高的员工的信息
select * from emp where sal>(select sal from emp where ename=‘FORD’);
–4. 找出不在部门20，且比部门20的任何一个员工的工资都高的员工的姓名和所在部门名称。
select ename,dname from emp e,dept d where e.deptno=d.deptno and e.deptno<>20 and sal>all(select sal from emp where deptno=20);
–5. 查询与SMITH的部门和岗位完全相同的所有雇员。
select * from emp where (deptno,job) in(select deptno,job from emp where ename=‘SMITH’);
–6. 查询每个部门工资最高的员工
select * from emp where sal in(select max(sal) from emp group by deptno);
–7. 显示员工‘FORD’的上级
select e.ename 员工,m.ename 上级 from emp e join emp m on e.mgr=m.empno;
–8. 查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
–9. 查询工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
–10. 查询emp表中各部门平均工资的最大值和其所在的部门编号
select avg(sal),deptno from emp group by deptno having avg(sal)=(select max(avg(sal)) from emp group by deptno);
–11. 查询emp表中平均工资的最大值和其所在的部门编号及部门名称
select avg(sal),e.deptno,dname from emp e join dept d on e.deptno=d.deptno group by e.deptno,dname having avg(sal)=(select max(avg(sal)) from emp group by deptno);
–12. 查询所有在任职职位上工资最高的员工
select * from emp where (job,sal) in(select job,max(sal) from emp group by job);
–13. 查找所有工资高于自己本部门平均工资的员工(关联查询)
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
–14. 显示每个部门的信息（右外连接）和人员数量
select d.*,renshu from dept d,(select deptno,count(ename) renshu from emp group by deptno)renshu where renshu.deptno(+)=d.deptno;
–15. 查找工资大于同职位的平均工资的员工信息
select * from emp e where sal>(select avg(sal) from emp where job=e.job);
–16. 查询员工表中是领导的员工
select * from emp where empno in(select mgr from emp);
–17. 查询员工表中不是领导的员工
select * from emp where empno not in(select mgr from emp where mgr is not null);

三、10.31查询综合练习（子查询、关联查询、外连接查询）
–查询练习10.31
–1、查询员工的基本信息，附加其上级的姓名（自关联）
select e.,m.ename from emp e,emp m where e.mgr=m.empno(+);
–2、查询所有工资高于平均工资（包括所有员工）的销售人员
select ename,sal from emp where job=‘ANALYST’ and sal>all(select avg(sal) from emp group by deptno);
–3、查询各个部门的名称和员工人数
select d.dname,tet.renshu from dept d,(select deptno dno,count(ename) renshu from emp group by deptno)tet where d.deptno=tet.dno;
分析：此处可以用别名来单独区分查询操作，既可以给表起别名，也可以给表中的某个字段起别名，思路更加清晰。
–4、查询工资相同的员工的工资和姓名（子查询）
select e.ename,e.sal from emp e where(select count() from emp where sal=e.sal)>1;
–5、查询每个部门，每个职位的最高工资
select deptno,job,max(sal) from emp group by deptno,job;
分析：如果选择列表包含有列、表达式和分组函数，那么这些列和表达式必须出现在 group by 子句中，否则会显示错误消息。
–6、查询各个部门工资最高的员工信息
select * from emp where sal in(select max(sal) from emp group by deptno);
–7、查询出有3个以上下属的员工信息（自关联）
select * from emp e where(select count() from emp where e.empno=mgr)>=3;
–8、查询平均工资最高的部门信息
select d.,round(avgsal,2) from dept d,(select deptno,avg(sal) avgsal from emp group by deptno)s
where avgsal=(select max(avg(sal)) from emp group by deptno) and d.deptno=s.deptno;
分析：该查询较为复杂，用别名命名处理查询较为容易
–9、查询没有员工的部门信息
select d.,n.count from dept d,(select e.deptno dno,count(empno) count from emp e group by deptno)n where n.dno(+)=d.deptno and n.count is null;
分析：此处的查询是右外连接查询，即把所有在dept表中deptno列出来，而在emp表中的deptno字段可能为空。
–10、列出至少有一个雇员的所有部门信息（嵌套子查询）
SELECT d.,e.* FROM dept d,
(SELECT e.deptno dno,e.* FROM emp e)e
WHERE d.deptno=e.dno;

四、关于使用分组函数以及group by的注意点
使用 group by 子句、where 子句和分组函数有以下一些注意事项：

1、分组函数只能出现在选择列表、having 子句和 order by 子句中
2、如果在select 语句中同时包含 group by 、having 以及 order by 子句，则必须将 order by 子句放在最后。默认情况下，当使用 group by 子句统计数组时，会自动按照分组列的升序显示统计结果。通过使用 order by 子句，可以改变数据分组的排序方式。
3、如果选择列表包含有列、表达式和分组函数，那么这些列和表达式必须出现在 group by 子句中，否则会显示错误消息。
4、当限制分组显示结果时，必须要使用 having 子句，而不能在 where 子句中使用分组函数限制显示结果，否则会显示错误信息。
5、当使用分组函数时，除了函数 count(*) 之外，其他分组函数都会忽略 null 行，当然 count(列名) 也会忽略 null 行
6、当使用分组函数时，在分组函数中可以指定 all 和 distinct 选项。其中 all 是默认选项，该选项表示统计所有行数据（包括重复行）；如果指定 distinct，则只会统计不同行值。

五、oracle_常用分组函数
1、分组
分组数据: GROUP BY 子句
语法：可以使用GROUP BY子句将表中的数据分成若干组，
包含在 GROUP BY 子句中的列不必包含在SELECT 列表中，
所有包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。

2、过滤分组： HAVING 子句
使用 HAVING 过滤分组:
1）行已经被分组。
2）使用了组函数。
3）满足HAVING 子句中条件的分组将被显示。

3、组函数嵌套
max(avg(salary))

4、常用分组函数
分组函数

1）max(column):求最大值,对数据类型没有要求，任意数据类型都可以

2）min(column):求最小值,对数据类型没有要求，任意数据类型都可以

3）avg(column):返回column不为null的平均值,对数据类型有要求，只能针对number类型(数字类型)

4）sum(column):返回column不为null的总和,对数据类型有要求，只能针对number类型(数字类型)

5.count(column):返回column不为null的记录数,对数据类型没有要求，任意数据类型都可以