Oracle数据库之SQL子查询详解_Oracle_领测软件测试网

欢迎您访问我爱IT技术网，今天小编为你分享的是oracle数据库教程：【Oracle数据库之SQL子查询详解_Oracle_领测软件测试网】，通过学习这些教程，你能够更深层次的掌握Oracle数据库！

Oracle数据库之SQL子查询详解_Oracle_领测软件测试网

前言 考虑到这样一种情况:查询员工中有哪些员工的工资比SMITH高。要解决这类问题我们首先要 要的就是将SIMTH的工资查询出来，之后再将所有员工的工资与其进行比较。那么如何利用第一

　　前言

　　考虑到这样一种情况:查询员工中有哪些员工的工资比SMITH高。要解决这类问题我们首先要

　　要的就是将SIMTH的工资查询出来，之后再将所有员工的工资与其进行比较。那么如何利用第一

　　次查询的结果了，就需要用到子查询了。

　　子查询

　　子查询其实就是指嵌入到其他语句中的select语句，也称其为嵌套查询。值得注意的的在DDL

　　语句中应用子查询的时候子查询可以使用order by 子句。但是在DML语句中的where子句，set子句

　　中，子查询是不能使用order by子句的。

　　简单例子：查询工作和SMITH一样的所有员工的信息(不包含SMITH)

　　[sql] view plaincopyprint?

　　select ename ,sal from emp e where job=

　　(select job from emp where ename='SMITH')

　　and e.ename <> 'SMITH';

　　select ename ,sal from emp e where job=

　　(select job from emp where ename='SMITH')

　　and e.ename <> 'SMITH';

　　子查询分类

　　单行子查询

　　只返回一条记录的子查询 ，就可上述例子一样

　　[sql] view plaincopyprint?

　　--返回工资比SMITH多的员工和工资情况

　　select ename ,sal from emp where

　　sal>

　　(

　　select sal from emp where ename='SMITH'

　　) ;

　　--返回工资比SMITH多的员工和工资情况

　　select ename ,sal from emp where

　　sal>

　　(

　　select sal from emp where ename='SMITH'

　　) ;

　　能够用于当行子查询的操作符有>、<、=、like等等

　　多行子查询

　　多行子查询返回的是一个记录组，也就是多条记录，这种情况对于子查询的操作就

　　应该是in、any、all，exits 等。直接一个实例来看

　　查询各部门中员工工资最高的员工信息

　　一种看起来是正确的查询方式：

　　[sql] view plaincopyprint?

　　select max(sal) from emp group by(deptno) --子查询的结果

　　select ename ,sal ,job from emp

　　where sal in

　　(

　　select max(sal) from emp group by deptno

　　)

　　;

　　select max(sal) from emp group by(deptno) --子查询的结果

　　select ename ,sal ,job from emp

　　where sal in

　　(

　　select max(sal) from emp group by deptno

　　)

　　;

　　不过仔细思考就可以知道上述思路是存在问题的，因为虽然将各部门中的最高工资查询出来了

　　但是外部查询中无法无法指定员工的工资与各自部门的最高工资进行比较。查询出来的结果必然

　　是不正确的。

　　下面提供一种正确的方法：

　　[sql] view plaincopyprint?

　　select ename ,deptno,sal,job from emp e

　　where not exists

　　(

　　select ename,deptno,job from emp

　　where deptno=e.deptno

　　and

　　sal > e.sal

　　)

　　order by deptno

　　;

　　select ename ,deptno,sal,job from emp e

　　where not exists

　　(

　　select ename,deptno,job from emp

　　where deptno=e.deptno

　　and

　　sal > e.sal

　　)

　　order by deptno

　　;

　　其实这种子查询的方式是相关子查询的方式，至于什么是相关子查询会在后面进行介绍。

　　多行子查询的一些操作符的使用示例：

　　IN操作符:

　　[sql] view plaincopyprint?

　　--in操作符的使用

　　--查询员工的职业类型在部门10中有的员工的信息

　　select ename,job,sal,deptno from emp where job

　　in

　　(

　　select distinct job from emp where deptno=10

　　)

　　;

　　--in操作符的使用

　　--查询员工的职业类型在部门10中有的员工的信息

　　select ename,job,sal,deptno from emp where job

　　in

　　(

　　select distinct job from emp where deptno=10

　　)

　　;

　　ALL操作符：

　　[sql] view plaincopyprint?

　　--ALL操作符的使用

　　--查询员工的工资大于30号部门的所有员工的信息

　　select ename,sal,deptno from emp

　　where sal > all

　　(

　　select sal from emp where deptno=30

　　)

　　;

　　--">all"表示大于最大的，"

　　--ALL操作符的使用

　　--查询员工的工资大于30号部门的所有员工的信息

　　select ename,sal,deptno from emp

　　where sal > all

　　(

　　select sal from emp where deptno=30

　　)

　　;

　　--">all"表示大于最大的，"

　　ANY操作符：

　　[sql] view plaincopyprint?

　　--ANY操作符的使用

　　--只要员工的工资存在这种情况：

　　--即他的工资情况比30部门的其中一个人的工资多就查询出来

select ename,sal,deptno from emp where salany ( select sal from emp where deptno=30 ) ; --ANY操作符的使用 --只要员工的工资存在这种情况： --即他的工资情况比30部门的其中

　　select ename,sal,deptno from emp

　　where sal>any

　　(

　　select sal from emp where deptno=30

　　)

　　;

　　--ANY操作符的使用

　　--只要员工的工资存在这种情况：

　　--即他的工资情况比30部门的其中一个人的工资多就查询出来

　　select ename,sal,deptno from emp

　　where sal>any

　　(

　　select sal from emp where deptno=30

　　)

　　;

　　“>ANY”意味着大于最小值，"

　　多列子查询

　　指的是子查询语句返回的是多列。

　　例如查询：某个名叫SMITH的员工，但是由于员工的名字可能出现重复，因此需要结合其工作

　　和和部门id进行判断。

　　[sql] view plaincopyprint?

　　--多列子查询实例

　　select ename ,job ,deptno from emp

　　where (ename,job,deptno)=

　　(

　　select ename, job,deptno from emp

　　where ename='SMITH'

　　)

　　;--这里主要在于说明多列子查询的使用，并不注重其实际意义

　　--多列子查询实例

　　select ename ,job ,deptno from emp

　　where (ename,job,deptno)=

　　(

　　select ename, job,deptno from emp

　　where ename='SMITH'

　　)

　　;--这里主要在于说明多列子查询的使用，并不注重其实际意义

　　● 成对比较

　　[sql] view plaincopyprint?

　　--成对比较

　　select ename ,sal,comm ,deptno from emp

　　where (sal,nvl(comm,-1)) in

　　(

　　select sal,nvl(comm,-1) from emp

　　where deptno=30

　　)

　　--成对比较

　　select ename ,sal,comm ,deptno from emp

　　where (sal,nvl(comm,-1)) in

　　(

　　select sal,nvl(comm,-1) from emp

　　where deptno=30

　　)

　　;

　　● 非成对比较

　　[sql] view plaincopyprint?

　　--非成对比较

　　elect ename ,sal,deptno,comm 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

　　)

　　;

　　--非成对比较

　　select ename ,sal,deptno,comm 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

　　)

　　;

　　其他子查询

　　1、相关子查询

　　是指需要引用主查询表列的子查询 语句。相关子查询是通过exists 来实现的 。

　　[sql] view plaincopyprint?

　　--查询在NEW YORK工作的员工的信息

　　select ename ,job ,sal ,deptno from emp

　　where exists

　　(

　　select 1 from dept

　　where dept.deptno=emp.deptno

　　and dept.loc='NEW YORK'

　　)

　　;

　　--查询在NEW YORK工作的员工的信息

　　select ename ,job ,sal ,deptno from emp

　　where exists

　　(

　　select 1 from dept

　　where dept.deptno=emp.deptno

　　and dept.loc='NEW YORK'

　　)

　　;

　　思考：相关子查询的执行过程：

　　相关子查询会引用外部查询的一列或多列，在执行的时候外部查询的每一行被一次一行的

　　传递给子查询，子查询依次读取外部查询传递过来的每一个值，并将其用到子查询上，直到外部

　　查询的所有行都用完为止，然后返回查询结果。

　　2、FROM子句中的子查询

　　在from子句中使用子查询的时候子查询会被当做一个view对待，需要给子查询指定别名。

　　[sql] view plaincopyprint?

　　--查看比本部门平均工资高的员工信息

　　select ename,job,sal from emp ,

　　(

　　select deptno,avg(sal) as avgsal from emp

　　group by deptno

　　) tmp_dept

　　where emp.deptno=tmp_dept.deptno and sal >tmp_dept.avgsal;

　　--查看比本部门平均工资高的员工信息

　　select ename,job,sal from emp ,

　　(

　　select deptno,avg(sal) as avgsal from emp

　　group by deptno

　　) tmp_dept

　　where emp.deptno=tmp_dept.deptno and sal >tmp_dept.avgsal;

　　3、DML语句中使用子查询

　　[sql] view plaincopyprint?

　　--DML语句中使用子查询

　　--insert中

　　insert into employee (id,name,title,salary)

　　select emptno，ename ,job,sal from emp;

　　--update中

　　update emp set (sal,comm)=

　　(

　　select sal,comm from emp

where ename=SMITH ) where job=( select job from emp where ename=SMITH ) ; --去除销售部门 delete from emp where deptno=( select deptno from dept where dname=SALES ) ; --DML语句中使用子

　　where ename='SMITH'

　　)

　　where job=

　　(

　　select job from emp where ename='SMITH'

　　)

　　;

　　--去除销售部门

　　delete from emp where deptno=

　　(

　　select deptno from dept

　　where dname='SALES'

　　)

　　;

　　--DML语句中使用子查询

　　--insert中

　　insert into employee (id,name,title,salary)

　　select emptno，ename ,job,sal from emp;

　　--update中

　　update emp set (sal,comm)=

　　(

　　select sal,comm from emp

　　where ename='SMITH'

　　)

　　where job=

　　(

　　select job from emp where ename='SMITH'

　　)

　　;

　　--去除销售部门

　　delete from emp where deptno=

　　(

　　select deptno from dept

　　where dname='SALES'

　　)

　　;

　　4、在DDL语句中使用子查询

　　通过select子查询来实现创建视图的方式来说明其使用方法

　　需要注意的是在执行下面的sql语句之前，要对scott用户赋予创建视图的权利

　　[sql] view plaincopyprint?

　　SQL>conn / as sysdba

　　SQL>grant create view to scott;

　　SQL>conn / as sysdba

　　SQL>grant create view to scott;

　　[sql] view plaincopyprint?

　　--创建视图并查询视图

　　create or replace view dept10 as

　　select empno,ename ,job,sal,deptno from emp

　　where deptno=10 order by empno;

　　select * from dept10;

　　--创建视图并查询视图

　　create or replace view dept10 as

　　select empno,ename ,job,sal,deptno from emp

　　where deptno=10 order by empno;

　　select * from dept10;

　　关于子查询的相关信息就到这里了。

关于Oracle数据库之SQL子查询详解_Oracle_领测软件测试网的用户使用互动如下：

　　相关问题：oracle数据库老是死

　　答：估计你的问题是这个，优化sql！仔细看看sql! 查看用户最大游标数量 这个cursor主要是由于应用程序端（JAVA）造成的，测试结果是 1、对于jdbc来说，每一个从Connection中产生的Statement相当于一个Session,此时会在v$session中产生或者重用一条se... >>详细

　　相关问题：请问SqlServer和Oracle数据库的区别！我学习了SqlS...

　　答：1.SqlServer比较容易学，Oracle也不算难 2.sqlServer由于是微软的东西，所以与windows的系统结合的比较多，换句话说，后门比较多 3.Oracle 大多数用java写，可以垮平台，sqlServer不可以。 4.sqlServer与Oracle的sql 语句并不多，不过Oracle多了... >>详细

　　相关问题：Oracle与sql server区别是什么？

　　答：sql就是oracle 出的 oracle对个个平台的支持都不错 sql server是微软出的...对windows平台支持度最高... >>详细