查询操作--子查询

1、简介

一个查询嵌套着另一个查询，称为子查询

子查询必须放在小括号中

子查询可以出现在任意位置，如select、where、having等

2、基本用法

2.1 语法

select (子查询)

from (子查询) 别名

where (子查询)

group by

having (子查询)

2.2 示例

例：查询工资比7566雇员工资高的员工信息？

-- 使用连接查询

select e2.*

from emp e2,emp e1

where e1.empno=7566 and e2.sal>e1.sal

-- 使用子查询

select *

from emp

where sal>(

select sal from emp where empno=7566

);注意：将子查询与比较运算符一起使用时，必须保证子查询返回的结果不

能多于1个

例：查询雇员的编号、姓名、部门名称。

-- 使用多表连接

select e.empno,e.ename,d.dname

from emp e,dept d

where d.deptno=e.deptno;

-- 使用子查询

select empno,ename,(select dname from dept where deptno=e.dept

from emp e;

总结：

一般来说，多表连接查询可以使用子查询替换，但有的子查询不能使用

多表连接查询来替换

子查询的特点：灵活、方便，一般常作为增、删、改、查询操作的条

件，适合于操作一个表的数据

多表连接查询更适合于查看多表中的数据

3、子查询的分类

可以分为三列：

单列子查询

返回单行单列，使用频率最高

多行子查询

返回多行单列

多列子查询

返回单行多列或多行多列

3.1单列子查询

例：查询工资比7654雇员工资高的，同时又与雇员7900从事相同工作的雇

员信息？

select *

from emp

where sal > (

select sal from emp where empno=7654

) and job = (

select job from emp where empno=7900

);

例：查询工资最低的雇员的姓名、职位和工资？

select ename,job,sal

from emp

where sal = (

select min(sal) from emp

);

例：查询部门的名称、部门的员工数、部门的平均工资、部门的最低收入

雇员的姓名。

-- 方式1：使用子查询

select

(select dname from dept where deptno=e.deptno) dname,

count(empno),

avg(sal),

min(sal),

(select ename from emp where sal=min(e.sal)) ename

from emp e

group by deptno;

-- 方式2：使用多表连接查询

select d.dname,temp.cnt,temp.avg,temp.min,e.ename

from (

select deptno,count(empno) cnt,avg(sal) avg,min(sal) min f

) temp,dept d,emp e

where d.deptno=temp.deptno and e.sal=temp.min

3.2多行子查询

对于多行子查询，可以使用如下三种操作符

in

例：查询所在部门编号大于等于20的雇员信息？

select * from emp where deptno>=20;

select *

from emp

where deptno in(

select deptno from dept where deptno>=20

);

any|some

三种用法：

=any|some 与任意一个相同，此时与in操作符功能一样

>any|some 只要比结果中最小的大即可

select *

from emp

where sal =any(

select sal from emp where deptno=20

);

all

两种用法

>all 比结果中最大的值要大

all 比结果中最小的值要小

select *

from emp

where sal >all(

select sal from emp where deptno=20

);

3.3 多列子查询

多列子查询一般出现在from子句中，作为查询结果集

例：在所从事销售工作的雇员中找出工资大于1500的雇员？

select temp.*

from (select * from emp where job="salesman") temp

where temp.sal>1500;