Oracle5常用复杂查询.pdf

《Oracle5常用复杂查询.pdf》由会员分享，可在线阅读，更多相关《Oracle5常用复杂查询.pdf（6页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、复杂查询 数据分组 连接查询 子查询 使用集合操作符 1 数据分组 实际应用中，经常需要对数据进行统计。当统计数据时，需要将表的数据划分成几个组，最终统计每个组的数据结果。1)分组函数 分组函数用于统计表的数据，与单行函数不同，分组函数作用于多行，并返回一个结果，所以有时也称为多行函数。一般情况下，分组函数要与 GROUP BY 子句结合使用。在使用分组函数时，如果忽略了 GROUP BY 子句，那么会汇总所有行，并产生一个结果。MAX 和 MIN：用于取得列或表达式的最大值和最小值。SELECT max(sal)最高工资，min(sal)最低工资 FROM emp;AVG 和 SUM：用于取

2、得表达式的平均值或总和 SELECT avg(sal)平均工资，sum(sal)总计工资 FROM emp;COUNT：用于取得总计行数 SELECT count(*)雇员总数 FROM emp；说明count 函数中还可以引用表达。因为分组函数会忽略 NULL 行，所以使用 count(表达式)会显示非 NULL 的总计行数。如：SELECT count(comm)补助非空的雇员总数 FROM emp;VARIANCE 和 STDDEV：用于取得列或表达式的方差和标准偏差。SELECT variance(sal)方差，stddev(sal)标准偏差 FROM emp;当使用分组函数时，有一些

3、注意事项。分组函数只能出现在选择列表、ORDER BY 子句、HAVING 子句中。当使用分组函数时，会忽略 NULL 行。如果在选择列表中既包含了分组函数，也包含了其他列和表达式，那么这些列或表达式必须出现在 GROUP BY 子句中。当使用分组函数时，可以在函数中指定 ALL 和 DISTINCE 选项。其中 ALL 是默认选项，该选项表示统计所有行数据（包括重复值）；如果指定 DISTINCT，则只会统计不同行值。下面是常用的分组函数：2)GROUP BY 和 HAVING 子句 GROUP BY 子句用于对查询结果进行分组统计，HAVING 子句用于限制分组显示结果。如果选择列表同时包

4、含有列、表达式和分组函数，那么这些列和表达式必须出现在 GROUP BY 子句中。语法如下：SELECT column,group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY expression;使用 GROUP BY 进行单列分组 单列分组是指基于单列生成分组统计结果。当进行单列分组时，会基于分组列的每个不同值生成一个统计结果。如：SELECT deptno 部门代码，avg(sal)部门平均工资 FROM emp GROUP BY dept

5、no;使用 GROUP BY 进行多列分组 多列分组是指基于两个或两个以上的列生成分组统计结果。当进行多列分组时，会基于多个列的不同值生成统计结果。如：SELECT deptno,job,avg(sal),max(sal)FROM emp GROUP BY deptno,job;使用 HAVING 子句限制分组显示结果 HAVING 子句用于限制分组统计结果，并且 HAVING 子句必须跟在 GROUP BY 子句后面。如：SELECT deptno,avg(sal),max(sal)FROM emp GROUP BY deptno HAVING avg(sal)1000 GROUP BY d

6、eptno（错误：分组函数不能用在 WHERE 中）2 连接查询 连接查询是指基于两个或两个以上表或视图的查询。在实际应用中，查询单个表可能无法满足应用程序的实际需求（例如显示 SALES 部门位置以及雇员名）。使用连接查询时的注意事项：当使用连接查询时，必须在 FROM 子句后指定两个或两个以上的表。当使用连接查询时，应该在列名前加表名作为前缀。但如果不同表之间列名不同，可以不加表句作前缀。当使用连接查询时，必须在 WHERE 子句中指定有效的连接条件，否则会导致生成笛卡儿集。如：SELECT dept.dname,emp.ename FROM dept,emp WHERE dept.dna

7、me=SALES;当进行连接查询时，使用表别名可以简化连接查询语句。如：SELECT d.dname,e.ename FROM dept d,emp e WHERE d.deptno=e.deptno;2)相等连接 相等连接是指使用相等比较符（=）指定连接条件的连接查询，该类连接查询主要用于检索主从表之间的相关数据。语法：SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column1=table2.column2.;如：SELECT e.ename,e.sal,d.dname FROM emp e,dept

8、d WHERE e.deptno=d.deptno(AND d.deptno=10);3)不等连接 指在连接条件中使用除相等比较符外其他比较操作符的连接查询。主要用于在不同表之间显示特定范围的信息。如：SELECT a.ename,a.sal,b.grade FROM emp a,salgrade b WHERE a.sal BETWEEN b.losal AND b.hisal 4)自连接 自连接是指在同一张表之间的连接查询，它主要用在自参照表上显示上下级关系或者层次关系。自参照表是指在不同列之间具有参照关系或主从关系的表。如下表，EMPNO（雇员号）ENAME MGR（管理者号）7839

9、KING 7566 JONES 7839 7698 BLAKE 7839 7782 CLARK 7839。根据 EMPNO 列和 MGR 列的对应关系，可以确定雇员 JONES、BLAKE 和 CLARK的管理者为 KING。为了显示雇员及其管理者之间的对应关系，可以使用自连接。因为自连接是在同一张表之间的连接，所以必须定义表别名。如：SELECT manager.ename FROM emp manager,emp worker WHERE manager.empno=worker.mgr AND worker.ename=BLAKE;5)内连接和外连接 内连接用于返回满足连接条件的记录；而

10、外连接则是内连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的记录。在 Oracle Database 9i 之前，连接语法都是在 WHERE 子句中指定的；从 9i 开始，有专用语法，格式如下：SELECT table1.column,table2.column FROM table1 INNER|LEFT|RIGHT|FULL JOIN table2 ON table1.column1=table2.column2;内连接 用于返回满足连接条件的所有记录。默认情况下，在执行连接查询时如果没有指定任何连接操作符，那么这些连接查询都属于内连接。如：SELECT a.d

11、name,b.ename FROM dept a INNER JOIN emp b 重要 ON a.deptno=b.deptno AND a.deptno=10;从 Oracle Database 9i 开始，如果主表的主键列和从表的外部键列名称相同，那么还可以使用 NATURAL JOIN 关键字自动执行内连接操作。如：SELECT dname,ename FROM dept NATURAL JOIN emp;select dept.deptno,dname,ename from dept natural join emp;(会出错，提示自然连接中使用的列不能有限定词)左外连接 不仅返回满

12、足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符左边表的其他行。如：SELECT a.dname,b.ename FROM dept a LEFT JOIN emp b ON a.deptno=b.deptno AND a.deptno=10 右外连接 不仅返回满足连接条件的所有记录，而且还会返回不满足连接条件的连接操作符右边表的其他行。完全外连接 不仅返回满足连接条件的所有记录，而且还会返回不满足连接条件的所有其他行。注释：外连接可以使用（+）操作符，但不建议用。3 子查询 子查询是指嵌入在其他 SQL 语句中的 SELECT 语句，也称为嵌套查询。当在 DDL 语句中使用子查询

13、时，可以带有 ORDER BY 子句；但如果在 WHERE 子句、SET 子句中使用子查询，不能带有 ORDER BY 子句。子查询具有以下作用：通过在 INSERT 或 CREATE TABLE 语句中使用子查询，可以将源表数据追加到目标表。通过在 UPDATE 语句中使用子查询可以修改一列或多列的数据 通过在 WHERE、HAVING 子句中使用子查询，可以提供条件值。根据子查询返回结果的不同，子查询又分为单行子查询、多行子查询和多列子查询。1）单行子查询 指只返回一行数据的子查询语句。当在 WHERE 子句中引用单行子查询时，可以使用单行比较符（=，=，=，。如：SELECT ename

14、,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename=SCOTT);其产生的结果只有一行 2)多行子查询 指返回多行数据的子查询语句。当在 WHERE 子句中使用多行子查询时，必须使用多行比较符（IN，ALL，ANY）。IN：匹配于子查询结果的任一个值即可 SELECT ename,job,sal,deptno FROM emp WHERE job IN (SELECT distinct job FROM emp WHERE deptno=10);其产生的结果有多行 ALL：必须要符合子查询结果的所有值 SE

15、LECT ename,sal,deptno FROM emp WHERE salall (SELECT sal FROM emp WHERE deptno=30);ANY：只要符合子查询结果的任一个值即可 SELECT ename,sal,deptno FROM emp WHERE salANY (SELECT sal FROM emp WHERE deptno=30)3)多列子查询 单行子查询是指子查询只返回单列单行数据，多行子查询是指子查询返回单列多行数据，二者都是针对单列而言的。而多列子查询则是指返回多个列数据的子查询语句。当多列子查询返回单行数据时，在 WHERE 子句中可以使用单行比

16、较符；当多列子查询返回多行数据时，有WHERE 子句中必须使用多行比较符（IN、ANY、ALL）。如：SELECT ename,job,sal,deptno FROM emp WHERE(deptno,job)=(SELECT deptno,job FROM emp WHERE ename=SMITH);当使用子查询比较多个列的数据时，既可以使用成对比较，也可以使用非成对比较 成对比较示例 SELECT ename,sal,comm,deptno FROM emp WHERE(sal,nvl(comm,-1)IN(SELECT sal,nvl(comm,-1)FROM emp WHERE de

17、ptno=30);非成对比较示例 SELECT ename,sal,comm,deptno FROM emp WHERE sal IN(SELECT sal FROM emp WHERE deptno=30)AND nvl(comm,-1)IN(SELECT nvl(comm,-1)FROM emp WHERE deptno=30);4)其他子查询 相关子查询（通过EXISTS谓词实现）SELECT ename,job,sal FROM emp WHERE EXISTS(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc=N

18、EW YORK);显示工作在 NEW YORK 的所有雇员的姓名，工作，工资及部门号）在 FROM 子句中使用子查询（子查询作为视图对待，又称内嵌视图）SELECT ename,job,sal FROM emp,(SELECT deptno,avg(sal)avgsal FROM emp GROUP BY deptno)d WHERE emp.deptno=d.deptno and sald.avgsal;注：当在 FROM 子句中使用子查询时，必须给予查询指定别名。在 DML 语句中使用子查询 在 INSERT 语句中使用子查询 可以将一张表的数据装载到另一张表。如：INSERT INTO

19、employee(id,name,title,salary)SELECT empno,ename,job,sal FROM emp;UPDATE 中 DELETE 中 在 DDL 语句中使用子查询 在 CREATE TABLE 语句中使用子查询 可以在建立新表的同时复制表数据 如：CREATE TABLE new_emp(id,name,sal,job,deptno)AS SELECT empno,ename,sal,job,deptno FROM emp;在 CREATE VIEW 语句中使用子查询 CREATE OR REPLACE VIEW dept_10 AS SELECT empno

20、,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno 4 使用集体操作符 为了合并多个 SELECT 语句的结果，可以使用集合操作符 UNION、UNION ALL，语法如下：SELECT 语句 1 UNION|UNION ALL SELECT 语句 2 UNION 用于取得两个结果集的并集，自动去掉结果集中的重复行。如：SELECT ename,sal,job FROM emp WHERE sal2500 UNION SELECT ename,sal,job FROM emp WHERE job=MANAGER UNION ALL 用于取得两个结果集的并集，与 UNION 操作符不同，该操作符不会取消重复值