`

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

OracleSQL数据结构 
阅读更多

多表联合查询
通过连接可以建立多表查询，多表查询的数据可以来自多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。一般是在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。

分享到：
| 十年学会程序设计
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    精通SQL 结构化查询语言详解

    10.2.2　IN子查询实现集合交和集合差运算 10.2.3　EXISTS子查询　 10.2.4　EXISTS子查询实现两表交集　 10.2.5　SOME/ALL子查询　 10.2.6　UNIQUE子查询　 10.3　相关子查询　 10.3.1　使用IN引入相关子查询　 ...

    Oracle数据库、SQL

    14.4子查询、连接、集合总结 29 十五、 排名分页问题 30 15.1什么是rownum 30 15.2 where rownum的执行过程 30 15.3 where rownum=5的执行过程 30 十六、 约束constraint 31 16.1约束的类型 31 16.2 primary key：...

    Oracle SQL高级编程(资深Oracle专家力作，OakTable团队推荐)--随书源代码

    作者通过总结各自多年的软件开发和教学培训经验，与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在，内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧：...

    精通SQL--结构化查询语言详解

    10.2.2 in子查询实现集合交和集合差运算 191 10.2.3 exists子查询 192 10.2.4 exists子查询实现两表交集 194 10.2.5 some/all子查询 195 10.2.6 unique子查询 197 10.3 相关子查询 198 10.3.1 使用in引入相关...

    Oracle8i_9i数据库基础

    §2.3.1 多记录组函数 73 §2.3.2 带 GROUP BY 的计算 75 §2.3.3 用 HAVING 来限制分组的计算 75 §2.4 控制和格式化输出 76 §2.4.1 用 ORDER BY 子句来对结果进行排序 76 §2.4.2 用 BREAK 命令 来对结果进行排列...

    mysql数据库的基本操作语法

    注意：alter modify不支持一次修改多个列，但是Oracle支持多列修改 但是MySQL可以通过多个modify的方式完成： alter table user modify tel varchar(15) default '02087654321' first, modify name varchar(20) ...

    2009达内SQL学习笔记

    多表查询时，可给表起别名。(给列起别名，列&lt;空格&gt;列别名；给表起别名，表&lt;空格&gt;表别名；)。 如：Select first_name EMPLOYEES, 12*(salary+100) AS MONEY, manager_id "ID1" From s_emp E; 4、字段的拼接,可用双...

    SQL21日自学通

    在子查询中使用汇总函数140 子查询的嵌套141 相关子查询144 EXISTS ANY ALL 的使用147 总结151 问与答151 校练场152 练习153 第一周回顾154 预览154 第二周概貌155 这一周都讲些什么155 第八天操作数据156 目标...

    数据库基础

    §2.3.1 多记录组函数 73 §2.3.2 带 GROUP BY 的计算 75 §2.3.3 用 HAVING 来限制分组的计算 75 §2.4 控制和格式化输出 76 §2.4.1 用 ORDER BY 子句来对结果进行排序 76 §2.4.2 用 BREAK 命令 来对结果进行排列...

    SQL语句教程.pdf

    子查询 EXISTS CASE 算排名 算中位数 算总合百分比 算累积总合百分比 SQL 语法 无论您是一位 SQL 的新手，或是一位只是需要对 SQL 复习一下的资料仓储业界老将，您 就来对地方了。这个 SQL 教材网站列出常用的 SQL ...

    21天学习SQL V1.0

    日期/时间函数......................................................................................................... 66 ADD_MONTHS.......................................................................

Magicbox
Global site tag (gtag.js) - Google Analytics