您现在的位置：计算机技术学习网 > 技术中心 > 数据库技术 > Oracle >

oracle多表联合查询,统计查询,组函数,order by,having,子查询

来源：未知责任编辑：责任编辑发表时间:2013-08-27 11:16　点击:次

多表联合查询
通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在WHERE子句中用比较运算符指明连接的条件。

忘记说明表的连接条件是常见的一种错误，这时查询将会产生表连接的笛卡尔积(即一个表中的每条记录与另一个表中的每条记录作连接产生的结果)。一般N个表进行连接，需要至少N-1个连接条件，才能够正确连接。两个表连接是最常见的情况，只需要说明一个连接条件。

两个表的连接有四种连接方式：
* 相等连接。
* 不等连接。
* 外连接。
* 自连接。
1．相等连接
通过两个表具有相同意义的列，可以建立相等连接条件。使用相等连接进行两个表的查询时，只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。

显示雇员的名称和所在的部门的编号和名称。
执行以下查询：

Sql代码
1.SELECT emp.ename,emp.deptno,dept.dname FROM emp,dept
2. WHERE emp.deptno=dept.deptno;
SELECT emp.ename,emp.deptno,dept.dname FROM emp,dept
WHERE emp.deptno=dept.deptno;
执行结果如下：

Sql代码
1.ENAME          DEPTNO DNAME
2.        ------------- ------------------------ - ----------
3.        SMITH              20     RESEARCH
4.        ALLEN              30     SALES
ENAME          DEPTNO DNAME
------------- ------------------------ - ----------
SMITH              20   RESEARCH
ALLEN              30   SALES
说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。
以上训练中，不在两个表中同时出现的列，前面的表名前缀可以省略。所以以上例子可以简化为如下的表示：
SELECT ename,emp.deptno,dname FROM emp,dept
WHERE emp.deptno=dept.deptno;

2．外连接
在以上的例子中，相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。
为了解决这个问题可以用外连，即除了显示满足相等连接条件的记录外，还显示那些不满足连接条件的行，不满足连接条件的行将显示在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这里用如下的例子予以说明。

使用外连显示不满足相等条件的记录。
步骤1：显示雇员的名称、工资和所在的部门名称及没有任何雇员的部门。
执行以下查询：

Sql代码
1.SELECT ename,sal,dname FROM emp,dept
2. WHERE emp.deptno(+)=dept.deptno;
SELECT ename,sal,dname FROM emp,dept
WHERE emp.deptno(+)=dept.deptno;
执行结果为：

Sql代码
1.ENAME            SAL DNAME
2.        ------------------- -------------- ------------------------
3.CLARK               2450 ACCOUNTING
4.        KING                5000 ACCOUNTING
5.        MILLER              1300 ACCOUNTING
6.        ...
7.        TURNER              1500 SALES
8.        WARD                1250 SALES
9.                        OPERATIONS
ENAME            SAL DNAME
------------------- -------------- ------------------------
CLARK           2450 ACCOUNTING
KING             5000 ACCOUNTING
MILLER          1300 ACCOUNTING
...
TURNER          1500 SALES
WARD            1250 SALES
                        OPERATIONS

3．不等连接
还可以进行不等的连接。以下是一个训练实例，其中用到的salgrade表的结构如下：
DESC salgrade

Sql代码
1.名称                              是否为空类型
2.        ------------------------------------------- ------------------ ------------------
3.        GRADE                                 NUMBER
4.        LOSAL                                     NUMBER
5.         HISAL                                        NUMBER
名称                              是否为空类型
------------------------------------------- ------------------ ------------------
GRADE                                NUMBER
LOSAL                               NUMBER
HISAL                                NUMBER
Grade 表示工资等级，losal和hisal分别表示某等级工资的下限和上限。
表的内容为：

Sql代码
1.SELECT * FROM salgrade;
SELECT * FROM salgrade;

Sql代码
1.GRADE      LOSAL    HISAL
2.        ------------------- ------------------- -------------
3.                 1        700           1200
4.                 2       1201           1400
5.                 3       1401           2000
6.                4       2001        3000
7.                5       3001        9999
GRADE      LOSAL    HISAL
------------------- ------------------- -------------
        1        700       1200
        2       1201       1400
        3       1401       2000
         4       2001       3000
         5       3001       9999
显示雇员名称，工资和所属工资等级。
执行以下查询：

Sql代码
1.SELECT e.ename, e.sal, s.grade FROM emp e,salgrade s
2. WHERE e.sal BETWEEN s.losal AND s.hisal;
SELECT e.ename, e.sal, s.grade FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
执行结果为：

Sql代码
1.ENAME             SAL      GRADE
2.        ----------------- -------------------- -------------------
3.JONES               2975          4
4.        BLAKE               2850          4
5.        CLARK               2450          4
6.        SCOTT               3000          4
7.        FORD                             3000          4
8.        KING                     5000          5
ENAME             SAL      GRADE
----------------- -------------------- -------------------
JONES            2975          4
BLAKE            2850          4
CLARK            2450          4
SCOTT            3000          4
FORD                          3000          4
KING                   5000          5
说明：通过将雇员工资与不同的工资上下限范围相比较，取得工资的等级，并在查询结果中显示出雇员的工资等级。

4．自连接
最后是一个自连接的训练实例，自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。
显示雇员名称和雇员的经理名称。
执行以下查询：

Sql代码
1.SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理
2.        FROM emp worker, emp manager
3.        WHERE worker.mgr = manager.empno;
SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理
FROM emp worker, emp manager
WHERE worker.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子句或ORDER BY子句中。组函数也可以称为统计函数。

组函数:
AVG:求平均值
COUNT:求计数值，返回非空行数，*表示返回所有行
MAX:求最大值
MIN:求最小值
SUM:求和
STDDEV:求标准偏差，是根据差的平方根得到的
VARIANCE:求统计方差

分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。
使用GROUP BY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。
如果不使用分组，将对整个表或满足条件的记录应用组函数。
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT 表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。

求有佣金的雇员人数。
执行以下查询：

Sql代码
1.SELECT COUNT(comm) FROM emp;
SELECT COUNT(comm) FROM emp;
返回结果为：

Sql代码
1.COUNT(COMM)
2.        ---------------------
3.                 4
COUNT(COMM)
---------------------
         4
说明：在本例中，没有返回全部雇员，只返回佣金非空的雇员，只有4个人。

求雇员表中不同职务的个数。
执行以下查询：

Sql代码
1.SELECT COUNT( DISTINCT job) FROM emp;
SELECT COUNT( DISTINCT job) FROM emp;
返回结果为：

Sql代码
1.COUNT(DISTINCT JOB)
2.        -------------------------------
3.                       5
COUNT(DISTINCT JOB)
-------------------------------
                   5
说明：该查询返回雇员表中不同职务的个数。如果不加DISTINCT，则返回的是职务非空的雇员个数。

分组统计
通过下面的训练，我们来了解分组的用法。
按职务统计工资总和。
步骤1：执行以下查询：

Sql代码
1.SELECT job,SUM(sal) FROM emp GROUP BY job;
SELECT job,SUM(sal) FROM emp GROUP BY job;

执行结果为：

Sql代码
1. JOB         SUM(SAL)
2.----------------- -------------------
3.ANALYST         6000
4.CLERK               4150
5.MANAGER         8275
6.PRESIDENT           5000
7.SALESMAN        5600
JOB         SUM(SAL)
----------------- -------------------
ANALYST       6000
CLERK           4150
MANAGER        8275
PRESIDENT       5000
SALESMAN       5600

说明：分组查询允许在查询列表中包含分组列，对以上实例，因为是按职务job分组的，所以在查询列中可以包含job字段，使统计结果很清楚
职务为ANALYST的雇员的总工资为6000，职务为CLERK的雇员的总工资为4150，依此类推。
注意：在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。

错误写法:SELECT ename,job,SUM(sal) FROM emp GROUP BY job;

多列分组统计
可以按多列进行分组，以下是按两列进行分组的例子。
按部门和职务分组统计工资总和。
执行以下查询：

Sql代码
1.SELECT   deptno, job, sum(sal) FROM emp
2.GROUP BY deptno, job;
SELECT   deptno, job, sum(sal) FROM emp
GROUP BY deptno, job;
执行结果为：
    DEPTNO JOB         SUM(SAL)
------------------ --------- -----------------------
10 CLERK         1300
        10 MANAGER      2450
        10 PRESIDENT     5000
        20 ANALYST      6000
        20 CLERK         1900
        20 MANAGER       2975
        30 CLERK          950
        30 MANAGER       2850
        30 SALESMAN      5600
说明：该查询统计每个部门中每种职务的总工资。
分组统计结果限定
对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。
统计各部门的最高工资，排除最高工资小于3000的部门。
执行以下查询：

Sql代码
1.SELECT   deptno, max(sal) FROM emp
2.        GROUP BY deptno
3.        HAVING   max(sal)>=3000;
SELECT   deptno, max(sal) FROM emp
GROUP BY deptno
HAVING   max(sal)>=3000;
执行结果为：

Sql代码
1.DEPTNO   MAX(SAL)
2.        ------------------ ------------------
3.                10       5000
4.             20       3000
DEPTNO   MAX(SAL)
------------------ ------------------
        10       5000
       20       3000
说明：结果中排除了部门30，因部门30的总工资小于3000。
注意：HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。

分组统计结果排序
可以使用ORDER BY从句对统计的结果进行排序，ORDER BY从句要出现在语句的最后。
按职务统计工资总和并排序。
执行以下查询：

Sql代码
1.SELECT job 职务, SUM(sal) 工资总和 FROM emp
2.GROUP BY job
3.ORDER BY SUM(sal);
SELECT job 职务, SUM(sal) 工资总和 FROM emp
GROUP BY job
ORDER BY SUM(sal);
执行结果为：

Sql代码
1.职务           工资总和
2.---------------- ------------------
3.CLERK           4150
4.PRESIDENT           5000
5.SALESMAN        5600
6.ANALYST         6000
7.MANAGER         8275
职务           工资总和
---------------- ------------------
CLERK          4150
PRESIDENT       5000
SALESMAN       5600
ANALYST        6000
MANAGER        8275

组函数的嵌套使用
在如下训练中，使用了组函数的嵌套。
求各部门平均工资的最高值。
执行以下查询：

Sql代码
1.SELECT max(avg(sal)) FROM emp GROUP BY deptno;
SELECT max(avg(sal)) FROM emp GROUP BY deptno;
执行结果为：

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.SELECT ename,sal FROM emp
2.WHERE sal>(SELECT sal FROM emp WHERE empno=7788);
SELECT ename,sal FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7788);
执行结果为：

Sql代码
1.ENAME             SAL
2.-------------- --------------------
3.KING              5000
ENAME             SAL
-------------- --------------------
KING              5000
说明：在该子查询中查询SCOTT的工资时使用的是他的雇员号，这是因为雇员号在表中是惟一的，而雇员的姓名有可能相重。SCOTT的雇员号为7788。
也可以包含两个或多个子查询。

Sql代码
1.在FROM从句中使用子查询
在FROM从句中使用子查询
在FROM从句中也可以使用子查询，在原理上这与在WHERE条件中使用子查询类似。有的时候我们可能要求从雇员表中按照雇员出现的位置来检索雇员，很容易想到的是使用rownum虚列。比如我们要求显示雇员表中6～9位置上的雇员，可以用以下方法。

查询雇员表中排在第6～9位置上的雇员。
执行以下查询：

Sql代码
1.SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM emp WHERE rownum<=9 )
2. WHERE num>=6;
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM emp WHERE rownum<=9 )
WHERE num>=6;
执行结果为：

Sql代码
1.ENAME               SAL
2.        -------------- --------------------
3.        BLAKE               2850
4.        CLARK               2450
5.        SCOTT               3000
6.        KING                5000
ENAME           SAL
-------------- --------------------
BLAKE           2850
CLARK           2450
SCOTT           3000
KING             5000
说明：子查询出现在FROM从句中，检索出行号小于等于9的雇员，并生成num编号列。在主查询中检索行号大于等于6的雇员。
   注意：以下用法不会有查询结果
SELECT ename,sal FROM emp
WHERE rownum>=6 AND rownum<=9;

集合运算
多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。
Oracle共有4个集合操作

UNION:并集，合并两个操作的结果，去掉重复的部分
UNION ALL:并集，合并两个操作的结果，保留重复的部分
MINUS:差集，从前面的操作结果中去掉与后面操作结果相同的部分
INTERSECT:交集，取两个操作结果中相同的部分

使用集合的并运算
查询部门10和部门20的所有职务。
执行以下查询：

Sql代码
1.SELECT job FROM emp WHERE deptno=10
2.        UNION
3.        SELECT job FROM emp WHERE deptno=20;
SELECT job FROM emp WHERE deptno=10
UNION
SELECT job FROM emp WHERE deptno=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改为UNION ALL的结果为:把两条语句查出来的值全部都显示,不去掉重复值

使用集合的交运算
查询部门10和20中是否有相同的职务和工资。
执行以下查询：

Sql代码
1.SELECT job,sal FROM emp WHERE deptno=10
2.        INTERSECT
3.        SELECT job,sal FROM emp WHERE deptno=20;
SELECT job,sal FROM emp WHERE deptno=10
INTERSECT
SELECT job,sal FROM emp WHERE deptno=20;
执行结果为：
未选定行

说明：部门10的职务有PRESIDENT、MANAGER、CLERK；部门20的职务有MANAGER、CLERK、ANALYST。所以两个部门的相同职务为：CLERK和MANAGER。但是职务和工资都相同的雇员没有，所以没有结果。
使用集合的差运算
查询只在部门表中出现，但没有在雇员表中出现的部门编号。
执行以下查询：

Sql代码
1.SELECT deptno FROM dept
2.MINUS
3.SELECT deptno FROM emp ;
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp ;
执行结果为：

Sql代码
1.DEPTNO
2.------------------
3.             40
DEPTNO
------------------
       40
说明：部门表中的部门编号有10、20、30和40。雇员表中的部门编号有10、20和30。差集的结果为40。