多表联合查询
通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。
忘记说明表的连接条件是常见的一种错误，这时查询将会产生表连接的笛卡尔积(即一个表中的每条记录与另一个表中的每条记录作连接产生的结果)。一般N个表进行连接，需要至少N-1个连接条件，才能够正确连接。两个表连接是最常见的情况，只需要说明一个连接条件。
两个表的连接有四种连接方式：
*相等连接。
*不等连接。
*外连接。
*自连接。
1．相等连接
通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。
显示雇员的名称和所在的部门的编号和名称。
执行以下查询：
Sql代码
1.SELECTemp.ename,emp.deptno,dept.dnameFROMemp,dept
2.WHEREemp.deptno=dept.deptno;
SELECTemp.ename,emp.deptno,dept.dnameFROMemp,dept
WHEREemp.deptno=dept.deptno;
执行结果如下：
Sql代码
1.ENAMEDEPTNODNAME
2.------------------------------------------------
3.SMITH20RESEARCH
4.ALLEN30SALES
ENAMEDEPTNODNAME
------------------------------------------------
SMITH20RESEARCH
ALLEN30SALES
说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。
以上训练中，不在两个表中同时出现的列，前面的表名前缀可以省略。所以以上例子可以简化为如下的表示：
SELECTename,emp.deptno,dnameFROMemp,dept
WHEREemp.deptno=dept.deptno;
2．外连接
在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。
为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这里用如下的例子予以说明。
使用外连显示不满足相等条件的记录。
步骤1：显示雇员的名称、工资和所在的部门名称及没有任何雇员的部门。
执行以下查询：
Sql代码
1.SELECTename,sal,dnameFROMemp,dept
2.WHEREemp.deptno(+)=dept.deptno;
SELECTename,sal,dnameFROMemp,dept
WHEREemp.deptno(+)=dept.deptno;
执行结果为：
Sql代码
1.ENAMESALDNAME
2.---------------------------------------------------------
3.CLARK2450ACCOUNTING
4.KING5000ACCOUNTING
5.MILLER1300ACCOUNTING
6....
7.TURNER1500SALES
8.WARD1250SALES
9.OPERATIONS
ENAMESALDNAME
---------------------------------------------------------
CLARK2450ACCOUNTING
KING5000ACCOUNTING
MILLER1300ACCOUNTING
...
TURNER1500SALES
WARD1250SALES
OPERATIONS
3．不等连接
还可以进行不等的连接。以下是一个训练实例，其中用到的salgrade表的结构如下：
DESCsalgrade
Sql代码
1.名称是否为空类型
2.-------------------------------------------------------------------------------
3.GRADENUMBER
4.LOSALNUMBER
5.HISALNUMBER
名称是否为空类型
-------------------------------------------------------------------------------
GRADENUMBER
LOSALNUMBER
HISALNUMBER
Grade表示工资等级，losal和hisal分别表示某等级工资的下限和上限。
表的内容为：
Sql代码
1.SELECT*FROMsalgrade;
SELECT*FROMsalgrade;
Sql代码
1.GRADELOSALHISAL
2.---------------------------------------------------
3.17001200
4.212011400
5.314012000
6.420013000
7.530019999
GRADELOSALHISAL
---------------------------------------------------
17001200
212011400
314012000
420013000
530019999
显示雇员名称，工资和所属工资等级。
执行以下查询：
Sql代码
1.SELECTe.ename,e.sal,s.gradeFROMempe,salgrades
2.WHEREe.salBETWEENs.losalANDs.hisal;
SELECTe.ename,e.sal,s.gradeFROMempe,salgrades
WHEREe.salBETWEENs.losalANDs.hisal;
执行结果为：
Sql代码
1.ENAMESALGRADE
2.--------------------------------------------------------
3.JONES29754
4.BLAKE28504
5.CLARK24504
6.SCOTT30004
7.FORD30004
8.KING50005
ENAMESALGRADE
--------------------------------------------------------
JONES29754
BLAKE28504
CLARK24504
SCOTT30004
FORD30004
KING50005
说明：通过将雇员工资与不同的工资上下限范围相比较，取得工资的等级，并在查询结果中显示出雇员的工资等级。
4．自连接
最后是一个自连接的训练实例，自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。
显示雇员名称和雇员的经理名称。
执行以下查询：
Sql代码
1.SELECTworker.ename||'的经理是'||manager.enameAS雇员经理
2.FROMempworker,empmanager
3.WHEREworker.mgr=manager.empno;
SELECTworker.ename||'的经理是'||manager.enameAS雇员经理
FROMempworker,empmanager
WHEREworker.mgr=manager.empno;
执行结果为：
Sql代码
1.雇员经理
2.-------------------------------------------
3.SMITH的经理是FORD
4.ALLEN的经理是BLAKE
5.WARD的经理是BLAKE
雇员经理
-------------------------------------------
SMITH的经理是FORD
ALLEN的经理是BLAKE
WARD的经理是BLAKE
说明：为EMP表分别起了两个别名worker和manager，可以想像，第一个表是雇员表，第二个表是经理表，因为经理也是雇员。然后通过worker表的mgr(经理编号)字段同manager表的empno(雇员编号)字段建立连接，这样就可以显示雇员的经理名称了。
注意：经理编号mgr是雇员编号empno之一，所以经理编号可以同雇员编号建立连接。
统计查询
通常需要对数据进行统计，汇总出数据库的统计信息。比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。
Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDERBY子句中。组函数也可以称为统计函数。
组函数:
AVG:求平均值
COUNT:求计数值，返回非空行数，*表示返回所有行
MAX:求最大值
MIN:求最小值
SUM:求和
STDDEV:求标准偏差，是根据差的平方根得到的
VARIANCE:求统计方差
分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。
使用GROUPBY从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。
如果不使用分组，将对整个表或满足条件的记录应用组函数。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。
求有佣金的雇员人数。
执行以下查询：
Sql代码
1.SELECTCOUNT(comm)FROMemp;
SELECTCOUNT(comm)FROMemp;
返回结果为：
Sql代码
1.COUNT(COMM)
2.---------------------
3.4
COUNT(COMM)
---------------------
4
说明：在本例中，没有返回全部雇员，只返回佣金非空的雇员，只有4个人。
求雇员表中不同职务的个数。
执行以下查询：
Sql代码
1.SELECTCOUNT(DISTINCTjob)FROMemp;
SELECTCOUNT(DISTINCTjob)FROMemp;
返回结果为：
Sql代码
1.COUNT(DISTINCTJOB)
2.-------------------------------
3.5
COUNT(DISTINCTJOB)
-------------------------------
5
说明：该查询返回雇员表中不同职务的个数。如果不加DISTINCT，则返回的是职务非空的雇员个数。
分组统计
通过下面的训练，我们来了解分组的用法。
按职务统计工资总和。
步骤1：执行以下查询：
Sql代码
1.SELECTjob,SUM(sal)FROMempGROUPBYjob;
SELECTjob,SUM(sal)FROMempGROUPBYjob;
执行结果为：
Sql代码
1.JOBSUM(SAL)
2.------------------------------------
3.ANALYST6000
4.CLERK4150
5.MANAGER8275
6.PRESIDENT5000
7.SALESMAN5600
JOBSUM(SAL)
------------------------------------
ANALYST6000
CLERK4150
MANAGER8275
PRESIDENT5000
SALESMAN5600
说明：分组查询允许在查询列表中包含分组列，对以上实例，因为是按职务job分组的，所以在查询列中可以包含job字段，使统计结果很清楚
职务为ANALYST的雇员的总工资为6000，职务为CLERK的雇员的总工资为4150，依此类推。
注意：在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。
错误写法:SELECTename,job,SUM(sal)FROMempGROUPBYjob;
多列分组统计
可以按多列进行分组，以下是按两列进行分组的例子。
按部门和职务分组统计工资总和。
执行以下查询：
Sql代码
1.SELECTdeptno,job,sum(sal)FROMemp
2.GROUPBYdeptno,job;
SELECTdeptno,job,sum(sal)FROMemp
GROUPBYdeptno,job;
执行结果为：
DEPTNOJOBSUM(SAL)
--------------------------------------------------
10CLERK1300
10MANAGER2450
10PRESIDENT5000
20ANALYST6000
20CLERK1900
20MANAGER2975
30CLERK950
30MANAGER2850
30SALESMAN5600
说明：该查询统计每个部门中每种职务的总工资。
分组统计结果限定
对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUPBY从句之后，而WHERE从句要出现在GROUPBY从句之前。
统计各部门的最高工资，排除最高工资小于3000的部门。
执行以下查询：
Sql代码
1.SELECTdeptno,max(sal)FROMemp
2.GROUPBYdeptno
3.HAVINGmax(sal)>=3000;
SELECTdeptno,max(sal)FROMemp
GROUPBYdeptno
HAVINGmax(sal)>=3000;
执行结果为：
Sql代码
1.DEPTNOMAX(SAL)
2.------------------------------------
3.105000
4.203000
DEPTNOMAX(SAL)
------------------------------------
105000
203000
说明：结果中排除了部门30，因部门30的总工资小于3000。
注意：HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。
分组统计结果排序
可以使用ORDERBY从句对统计的结果进行排序，ORDERBY从句要出现在语句的最后。
按职务统计工资总和并排序。
执行以下查询：
Sql代码
1.SELECTjob职务,SUM(sal)工资总和FROMemp
2.GROUPBYjob
3.ORDERBYSUM(sal);
SELECTjob职务,SUM(sal)工资总和FROMemp
GROUPBYjob
ORDERBYSUM(sal);
执行结果为：
Sql代码
1.职务工资总和
2.----------------------------------
3.CLERK4150
4.PRESIDENT5000
5.SALESMAN5600
6.ANALYST6000
7.MANAGER8275
职务工资总和
----------------------------------
CLERK4150
PRESIDENT5000
SALESMAN5600
ANALYST6000
MANAGER8275
组函数的嵌套使用
在如下训练中，使用了组函数的嵌套。
求各部门平均工资的最高值。
执行以下查询：
Sql代码
1.SELECTmax(avg(sal))FROMempGROUPBYdeptno;
SELECTmax(avg(sal))FROMempGROUPBYdeptno;
执行结果为：
Sql代码
1.MAX(AVG(SAL))
2.-----------------------
3.2916.66667
MAX(AVG(SAL))
-----------------------
2916.66667
说明：该查询先统计各部门的平均工资，然后求得其中的最大值。
注意：虽然在查询中有分组列，但在查询字段中不能出现分组列。
子查询
我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，第一步查询雇员SCOTT的工资，第二步查询工资高于SCOTT的雇员。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。
子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。
子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。
单行子查询
查询比SCOTT工资高的雇员名字和工资。
执行以下查询：
Sql代码
1.SELECTename,salFROMemp
2.WHEREsal>(SELECTsalFROMempWHEREempno=7788);
SELECTename,salFROMemp
WHEREsal>(SELECTsalFROMempWHEREempno=7788);
执行结果为：
Sql代码
1.ENAMESAL
2.----------------------------------
3.KING5000
ENAMESAL
----------------------------------
KING5000
说明：在该子查询中查询SCOTT的工资时使用的是他的雇员号，这是因为雇员号在表中是惟一的，而雇员的有可能相重。SCOTT的雇员号为7788。
也可以包含两个或多个子查询。
Sql代码
1.在FROM从句中使用子查询
在FROM从句中使用子查询
在FROM从句中也可以使用子查询，在原理上这与在WHERE条件中使用子查询类似。有的时候我们可能要求从雇员表中按照雇员出现的位置来检索雇员，很容易想到的是使用rownum虚列。比如我们要求显示雇员表中6～9位置上的雇员，可以用以下方法。
查询雇员表中排在第6～9位置上的雇员。
执行以下查询：
Sql代码
1.SELECTename,salFROM(SELECTrownumasnum,ename,salFROMempWHERErownum<=9)
2.WHEREnum>=6;
SELECTename,salFROM(SELECTrownumasnum,ename,salFROMempWHERErownum<=9)
WHEREnum>=6;
执行结果为：
Sql代码
1.ENAMESAL
2.----------------------------------
3.BLAKE2850
4.CLARK2450
5.SCOTT3000
6.KING5000
ENAMESAL
----------------------------------
BLAKE2850
CLARK2450
SCOTT3000
KING5000
说明：子查询出现在FROM从句中，检索出行号小于等于9的雇员，并生成num编号列。在主查询中检索行号大于等于6的雇员。
注意：以下用法不会有查询结果
SELECTename,salFROMemp
WHERErownum>=6ANDrownum<=9;
集合运算
多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。
Oracle共有4个集合操作
UNION:并集，合并两个操作的结果，去掉重复的部分
UNIONALL:并集，合并两个操作的结果，保留重复的部分
MINUS:差集，从前面的操作结果中去掉与后面操作结果相同的部分
INTERSECT:交集，取两个操作结果中相同的部分
使用集合的并运算
查询部门10和部门20的所有职务。
执行以下查询：
Sql代码
1.SELECTjobFROMempWHEREdeptno=10
2.UNION
3.SELECTjobFROMempWHEREdeptno=20;
SELECTjobFROMempWHEREdeptno=10
UNION
SELECTjobFROMempWHEREdeptno=20;
执行结果为：
Sql代码
1.JOB
2.---------
3.ANALYST
4.CLERK
5.MANAGER
6.PRESIDENT
JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的所有职务(相同职务只算一个)共有4个：ANALYST、CLERK、MANAGER和PRESIDENT。
将UNION改为UNIONALL的结果为:把两条语句查出来的值全部都显示,不去掉重复值
使用集合的交运算
查询部门10和20中是否有相同的职务和工资。
执行以下查询：
Sql代码
1.SELECTjob,salFROMempWHEREdeptno=10
2.INTERSECT
3.SELECTjob,salFROMempWHEREdeptno=20;
SELECTjob,salFROMempWHEREdeptno=10
INTERSECT
SELECTjob,salFROMempWHEREdeptno=20;
执行结果为：
未选定行
说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的相同职务为：CLERK和MANAGER。但是职务和工资都相同的雇员没有，所以没有结果。
使用集合的差运算
查询只在部门表中出现，但没有在雇员表中出现的部门编号。
执行以下查询：
Sql代码
1.SELECTdeptnoFROMdept
2.MINUS
3.SELECTdeptnoFROMemp;
SELECTdeptnoFROMdept
MINUS
SELECTdeptnoFROMemp;
执行结果为：
Sql代码
1.DEPTNO
2.------------------
3.40
DEPTNO
------------------
40
说明：部门表中的部门编号有10、20、30和40。雇员表中的部门编号有10、20和30。差集的结果为40。
