《Oracle5常用复杂查询.pdf》由会员分享,可在线阅读,更多相关《Oracle5常用复杂查询.pdf(6页珍藏版)》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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 操作符不同,该操作符不会取消重复值