oracle的高级查询

1、简单连接

基本连接语法：
SELECT [ALL|DISTINCT]column_name[,expression…]
FROM table1_name[,table2_name,view_name,…]
[WHERE condition]
[GROUP BY column_name1[,column_name2,…]
[HAVING group_condition]]
[ORDER BY column_name2 [ASC|DESC]
[,column_name2,…]];

基本连接
查询emp表中所有员工的编号，姓名，工资，部门号，及部门名称
方法一：
SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
方法二：
SQL> SELECT empno,ename,sal,e.deptno,dname
FROM emp e,dept d
WHERE e.deptno=d.deptno;

使用JOIN连接查询
除了使用“，”号分隔需要参与连接操作的表之外，SQL支持另外一种使用关键字JOIN的连接。
使用Join连接的语法形式如下：
SELECT [ALL|DISTINCT]column_name[,expression…]
FORM join_table JOIN TYPE join_table
ON join_condition

例如：SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp JOIN dept
ON emp.deptno=dept.deptno;

2、交叉连接：

概念
又称为“笛卡儿积连接”，是两个或多个表之间的无条件连接。一个表中所有记录分别与其他表中所有记录进行连接。
如果进行连接的表中分别有n1，n2，n3…条记录，那么交叉连接的结果集中将有n1×n2×n3×…条记录。
以下情况可以出现交叉连接
连接条件省略
连接条件非法
一个表的所有行被连接到另一个表的所有行

SQL> SELECT ename,dname from emp,dept;

 

3、内连接：

执行过程
内连接语法
等值内连接
非等值内连接
自身内连接

内连接是根据指定的连接条件进行连接查询，只有满足连接
条件的数据才会出现在结果集中。

内连接语法：标准SQL语句的连接方式
SELECT table1.column,talbe2.column[,…]
FROM table1 [INNER] JOIN table2 [JOIN …]
ON condition;
内连接语法：Oracle扩展的连接方式
SELECT table1.column,talbe2.column[,…]
FROM table1,table2[,…]
WHERE condition;

等值内连接

使用等号(=)指定连接条件的连接查询。
列的名称可以不同，但是类型必须匹配；如果有相同名称的列，需在列名前加上表名。

例如，查询10号部门员工的员工号、员工名、工资、部门号和部门名称。
SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp JOIN dept
ON emp.deptno=10 AND emp.deptno=dept.deptno;

SQL> SELECT empno,ename,sal,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno=10 AND emp.deptno=dept.deptno;

非等值内连接
连接条件中的运算符不是等号而是其他关系运算符。
例如，查询10号部门员工的工资等级。

SQL> SELECT empno,ename,sal,grade
FROM emp JOIN salgrade
ON sal>losal AND sal<hisal
WHERE deptno=10;

SQL> SELECT empno,ename,sal,grade
FROM emp,salgrade
WHERE sal>losal AND sal<hisal AND deptno=10;

自身内连接
在同一个表或视图中进行连接，相当于同一个表作为两个或多个表使用。
例如，查询所有员工的员工号、员工名和该员工领导的员工名、员工号。

SQL> SELECT worker.empno,worker.ename, manager.empno,
manager.ename
FROM emp worker JOIN emp manager
ON worker.mgr=manager.empno;

SQL> SELECT worker.empno,worker.ename,
manager.empno,manager.ename
FROM emp worker,emp manager
WHERE worker.mgr=manager.empno;
4、外链接：

左外连接
右外连接
全外连接
外连接是在内连接的基础上，将某个连接表中不符合连接
条件的记录加入结果集中。

左外连接
指在内连接的基础上，将连接操作符左侧表中不符合连接条件的记录加入结果集中，与之对应的连接操作符右侧表列用NULL填充。
语法：
标准SQL语句的连接方式
SELECT table1.column, table2.column[,…]
FROM table1 LEFT JOIN table2[,]
ON table1.column <operator> table2.column[,…];
Oracle扩展的连接方式
SELECT table1.column, table2.column[,…]
FROM table1, table2[,…]
WHERE table1.column <operator>
table2.column(+)[…];

例如，查询10号部门的部门名、员工号、员工名和所有其他部门的名称。语句为

SQL> SELECT dname,empno,ename
FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno AND dept.deptno=10;

或
SQL> SELECT dname,empno,ename
FROM dept,emp
WHERE dept.deptno=emp.deptno(+) AND
emp.deptno(+)=10;

说明：外部连接就好象是为符号(+)所在侧的表增加一个“万能”的行，这个
行全部由空值组成。它可以和另一侧的表中所有不满足连接条件的记
录进行连接，将不符合条件的列全部检索出来。

例：查询公司内所有的员工名、部门号和部门名。(说明：dept表中有4行记录，其中40号部门没有员工，但是该部门的信息也必须检索出来。)

SQL> SELECT e.ename, d.deptno, d.dname
FROM emp e, dept d
WHERE d.deptno=e.deptno(+);

右外连接
指在内连接的基础上，将连接操作符右侧表中不符合连接条件的记录加入结果集中，与之对应的连接操作符左侧表列用NULL填充。
语法：
标准SQL语句的连接方式
SELECT table1.column, table2.column[,…]
FROM table1 RIGHT JOIN table2[,…]
ON table1.column <operator> table2.column[…];
Oracle扩展的连接方式
SELECT table1.column, table2.column[,…]
FROM table1, table2[,…]
WHERE table1.column (+)<operator>
table2.column[…];

例如，查询20号部门的员工号、员工名及其部门名称，和所有其他部门的员工号、员工名。

SQL> SELECT empno,ename,dname
FROM dept RIGHT JOIN emp
ON dept.deptno=emp.deptno AND
dept.deptno=20;

SQL> SELECT empno,ename,dname
FROM dept,emp
WHERE dept.deptno(+)=emp.deptno AND
dept.deptno(+)=20;

 

注意：(+)操作符仅适用于左/右外连接，而且如果WHERE子句中包含多个
条件，则必须在所有条件中都包含(+)操作符。

全外连接：指在内连接的基础上，将连接操作符两侧表中不符合连接条件的记录加入结果集中。
在Oracle数据库中，全外连接的表示方式为
SELECT table1.column, table2.column[,…]
FROM table1 FULL JOIN table2[,…]
ON table1.column1 = table2.column2[…];
查询所有的部门名和员工名，语句为
SQL> SELECT dname,ename
FROM emp FULL JOIN dept
ON emp.deptno=dept.deptno;

5、子查询

子查询概述
单行单列子查询
多行单列子查询
单行多列子查询
多行多列子查询
相关子查询
在FROM子句中使用子查询
在DDL语句中使用子查询
使用WITH子句的子查询

概念
子查询：指嵌套在其他SQL语句中的SELECT语句，也称为嵌套查询 。
在执行时，由里向外，先处理子查询，再将子查询的返回结果用于其父语句(外部语句)的执行。
作用
在INSERT或CREATE TABLE语句中使用子查询，可以将子查询的结果写入到目标表中；
在UPDATE语句中使用子查询可以修改一个或多个记录的数据；
在DELETE语句中使用子查询可以删除一个或多个记录；
在WHERE和HAVING子句中使用子查询可以返回的一个或多个值。

注意：在DDL语句中的子查询可以带有ORDER BY子句，而在DML语句和DQL语
句中使用子查询时不能带有ORDER BY子句。

单行单列子查询：指子查询只返回一行数据，而且只返回一列的数据。
运算符
=，>，<，>=，<=，!=
例如，查询比7934号员工工资高的员工的员工号、员工名、员工工资信息，语句为

SQL> SELECT empno,ename,sal
FROM emp
WHERE sal>
(SELECT sal FROM emp WHERE empno=7934);

多行单列子查询：指返回多行数据，且只返回一列的数据。
运算
符号

例：查询与10号部门某个员工工资相等的员工信息。
SQL> SELECT empno,ename,sal
FROM emp
WHERE sal IN
(SELECT sal FROM emp WHERE deptno=10);

 

例：查询比10号部门某个员工工资高的员工信息。
SQL> SELECT empno,ename,sal
FROM emp
WHERE sal >ANY
(SELECT sal FROM emp WHERE deptno=10);

例：查询比10号部门所有员工工资高的员工信息。

SQL> SELECT empno,ename,sal FROM emp
WHERE sal >ALL
(SELECT sal FROM emp WHERE deptno=10);

单行多列子查询：指子查询返回一行数据，但是包含多列数据。
多列数据进行比较时，可以成对比较，也可以非成对比较。
成对比较要求多个列的数据必须同时匹配；
非成对比较则不要求多个列的数据同时匹配。

例：查询与7844号员工的工资、工种都相同的员工的信息。
SQL> SELECT empno,ename,sal,job FROM emp
WHERE (sal,job)=(SELECT sal,job FROM emp
WHERE empno=7844);

例：查询与10号部门某个员工工资相同，工种也与10号部门的某个员工相同的员工的信息。
SQL> SELECT empno,ename,sal,job FROM emp
WHERE sal IN (SELECT sal FROM emp
WHERE deptno=10)AND
job IN (SELECT job FROM emp
WHERE deptno=10);

多行多列子查询：指子查询返回多行数据，并且是多列数据。
例如，查询与10号部门某个员工的工资和工种都相同的员工的信息，语句为
SQL> SELECT empno,ename,sal,job FROM emp
WHERE (sal,job) IN (
SELECT sal,job FROM emp WHERE deptno=10);

子查询在执行时并不需要外部父查询的信息，这种子查询称为无关子查询。
如果子查询在执行时需要引用外部父查询的信息，那么这种子查询就称为相关子查询。
无关子查询与相关子查询的区别：
无关子查询中，内部的SELECT查询首先执行并且只执行一次，返回值被主查询使用。
相关子查询中，对由外查询考虑的每个候选行都执行一次，换句话说，内查询由外查询驱动。

相关子查询的执行：
取得候选行(由外查询取回)；
用候选行的值执行内查询；
用来自内查询的值确认或取消候选行；
重复前三步直到无剩余的候选行。
当一个子查询必须对每个由主查询考虑的候选行返回一个不同的结果或结果集时，可以使用相关子查询。
在相关子查询中经常使用EXISTS或NOT EXISTS谓词来实现。如果子查询返回结果，则条件为TRUE，如果子查询没有返回结果，则条件为FALSE。也可以使用ANY和ALL进行运算。

 

例：查询至少变换过两次工作的员工信息。
SQL> SELECT e.employee_id, last_name, e.job_id
FROM employees e
WHERE 2<=(SELECT count(*) FROM job_history
WHERE employee_id=e.employee_id);

例：查询没有任何员工的部门号、部门名。

SQL> SELECT deptno,dname,loc FROM dept
WHERE NOT EXISTS(SELECT * FROM emp
WHERE emp.deptno=dept.deptno);

说明：EXISTS和NOT EXISTS操作经常用于相关子查询来测试是否一个由外查
询取回的值存在或不存在于由内查询取回的值的结果集中。

例如，查询至少有一个雇员的经理信息。
SQL> SELECT empno,ename,job,deptno FROM emp e
WHERE EXISTS(SELECT 'X' FROM emp
WHERE mgr=e.empno);

SQL> SELECT empno,ename,job,deptno FROM emp e
WHERE empno IN (SELECT mgr FROM emp
WHERE mgr IS NOT NULL);
例：查询比本部门平均工资高的员工信息。
SQL> SELECT empno,ename,sal
FROM emp e
WHERE sal>(SELECT avg(sal) FROM emp
WHERE deptno=e.deptno); 、

当在FROM子句中使用子查询时，该子查询被作为视图对待，必须为该子查询指定别名。
例：查询各个员工的员工号、员工名及其所在部门平均工资。
SQL> SELECT empno,ename,d.avgsal
FROM emp,(SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno) d
WHERE emp.deptno=d.deptno;

 

例：查询各个部门号、部门名、部门人数及部门平均工资。
SQL> SELECT dept.deptno,dname, d.amount,d.avgsal
FROM dept,(SELECT deptno,count(*)amount,
avg(sal) avgsal
FROM emp
GROUP BY deptno)d
WHERE dept.deptno=d.deptno;

可以在CREATE TABLE和CREATE VIEW语句中使用子查询来创建表和视图。

CREATE TABLE emp_subquery
AS
SELECT empno,ename,sal FROM emp;

CREATE VIEW emp_view_subquery
AS
SELECT * FROM emp WHERE sal>2000;

如果在一个SQL语句中多次使用同一个子查询，可以通过WITH子句给子查询指定一个名字，从而可以实现通过名字引用该子查询，而不必每次都完整写出该子查询。
查询人数最多的部门的信息。

SQL> SELECT * FROM dept
WHERE deptno IN (
SELECT deptno FROM emp GROUP BY deptno
HAVING count(*)>=ALL(
SELECT count(*) FROM emp GROUP BY deptno)
);

相同的子查询连续出现了两次，因此可以按下列方式编写查询语句。

SQL> WITH deptinfo AS(SELECT deptno,count(*) num
FROM emp GROUP BY deptno)
SELECT * FROM dept
WHERE deptno IN(SELECT deptno
FROM deptinfo
WHERE num=(SELECT max(num)
FROM deptinfo)
);

层次查询(hierarchical_query)，又称树形查询，能够将一个表中的数据按照记录之间的联系以树状结构的形式显示出来。
层次查询的语法为
SELECT [LEVEL], column[,expression...]
FROM table
[WHERE condition]
[START WITH column = value]
[CONNECT BY condition];

参数说明
LEVEL：伪列，表示记录的层次，根行的LEVEL返回1，其孩子LEVEL返回2，依此类推；
WHERE：记录(节点)选择条件；
START WITH：层次查询的起始记录(起始节点)
CONNECT BY：指定父记录与子记录之间的关系及分支选择条件。必须使用PRIOR引用父记录，形式为
PRIOR column1=column2
或
column1=PRIOR column2。
层次查询应遵循以下限制：
SELECT 语句不能包含连接 (JOIN) 或者，查询来自一个包含连接的视图。
如果在SELECT层次查询语句中有ORDER BY从句，Oracle将以ORDER BY的次序重新给选出的层次数据排序。
CONNECT BY子句中不能包含子查询。

层次查询的执行步骤：(Oracle服务器)
⑴将表中满足START WITH从句中条件的行选取出来，将其作为层次数据的根数据；
⑵依据CONNECT BY子句中的条件，选择每一个根行的子行；
⑶选择子行的后续子行。Oracle先选择⑵中提到的子行，将其作为根行，选择出它们的子行；然后再将这些子行作为根行，再选择这些行的子行，以此递推。
⑷如果层次查询语句中包含WHERE语句，Oracle将从层次数据(结果数据)中移去所有的不满足WHERE条件子句的行。
⑸以特定的次序返回数据行，用以保证子行显示在父行之下的次序不乱。

集合：
语法：
SELECT query_statement1
[UNION|UNION ALL|INTERSECT|MINUS]
SELECT query_statement2;
注意：
当要合并几个查询的结果集时，这几个查询的结果集必须具有相同的列数与数据类型。
如果要对最终的结果集排序，只能在最后一个查询之后用ORDER BY子句指明排序列。

UNION运算符用于获取几个查询结果集的并集，将重复的记录只保留一个，并且默认按第一列进行排序。
例如，查询10号部门的员工号、员工名、工资和部门号以及工资大于2000的所有员工的员工号、员工名、工资和部门号。语句为

SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=10
UNION
SELECT empno,ename,sal,deptno
FROM emp WHERE sal>2000
ORDER BY deptno;

SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=10 OR sal>2000;

如果要保留所有的重复记录，则需要使用UNION ALL运算符。例如：
SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=10
UNION ALL
SELECT empno,ename,sal,deptno
FROM emp WHERE sal>2000
ORDER BY deptno;
INTERSECT用于获取几个查询结果集的交集，只返回同时存在于几个查询结果集中的记录。同时，返回的最终结果集默认按第一列进行排序。
例如，查询30号部门中工资大于2000的员工号、员工名、工资和部门号，语句为：
SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=30
INTERSECT
SELECT empno,ename,sal,deptno
FROM EMP WHERE sal>2000;

SQL> SELECT empno,ename,sal,deptno
FROM emp WHERE deptno=30 AND sal>2000;

MINUS用于获取几个查询结果集的差集，即返回在第一个结果集中存在，而在第二个结果集中不存在的记录。同时，返回的最终结果集默认按第一列进行排序。
例如，查询30号部门中工种不是“SALESMAN”的员工号、员工名和工种名称。语句为：
SQL> SELECT empno,ename,job
FROM emp WHERE deptno=30
MINUS
SELECT empno,ename,job
FROM EMP WHERE job='SALESMAN';