子查询所要解决的问题：问题不能一步求解

实际上就是sql语句的嵌套，就是在一个select语句中嵌套另外一个select语句

首先我们使用控制台登录连接我们的oracle:

我们先创建员工信息表EMP:

create table EMP(
    EMPNO NUMBER,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER,HIREDATE DATE,SAL BINARY_DOUBLE,COMM BINARY_DOUBLE,DEPTNO NUMBER
);

其中job是职位，mgr是该员工的上司的id，sal是工资，comm是提成，deptno是所属部门。

然后加入一些数据:

insert into EMP (
    EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
)
values(
    1110,'张三','主管',1110,'12-3月 -14',5200,20
);

以后数据的添加如上，这里不再赘述。

添加完成后结果

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1110 张三 主管 1110 12-3月 -14 5200 0 20

1111 李四 销售 1116 03-11月-15 3400 500 30

1112 王五 销售 1116 25-4月 -12 4400 800 30

1113 赵二 后勤 1110 30-5月 -11 3450 0 40

1114 李磊磊 会计 1110 22-12月-15 2500 0 50

1115 张少丽 销售 1110 11-3月 -16 2400 1400 30

1116 林建国 主管 1116 22-1月 -16 5700 0 20

1117 马富邦 后勤 1116 22-7月 -13 2800 0 40

1118 沈倩 会计 1116 06-5月 -10 2100 0 50

然后创建一张部门表

create table dept(
    DEPTNO NUMBER,DNAME VARCHAR2(50)
);

添加数据之后结果

SQL> select * from dept t;

DEPTNO DNAME

——– ——–

20 管理部门

30 销售部门

40 后勤部门

50 金融部门

我们来查询工资比“李磊磊”高的员工信息

(为了防止科学记数法的出现，我们先对工资和提成进行排版:

col sal for 9999

col comm for 9999)

(1)李磊磊的工资

select sal from emp where ename=’李磊磊’;

SAL

———-

2500

(2)查询比3000高的员工

select * from emp where sal >2500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1110 张三 主管 1110 12-3月 -14 5200 0 20

1111 李四 销售 1116 03-11月-15 3400 500 30

1112 王五 销售 1116 25-4月 -12 4400 800 30

1113 赵二 后勤 1110 30-5月 -11 3450 0 40

1116 林建国 主管 1116 22-1月 -16 5700 0 20

1117 马富邦 后勤 1116 22-7月 -13 2800 0 40

使用子查询

select * from emp where

sal>(select sal from emp where ename=’李磊磊’);

结果如上

子查询要注意的问题：

1. 将子查询放入括号中

2. 采用合理的书写风格

3. 可以在主查询的where select from having后面，放置子查询

select deptno,min(sal) from emp

group by deptno

having min(sal) > (select min(sal) from emp where deptno=30);

DEPTNO MIN(SAL)

————— ——–

20 5200

40 2800

拆分一下，其中

select deptno,min(sal) from emp

group by deptno

DEPTNO MIN(SAL)

————— ——–

30 2400

20 5200

40 2800

50 2100

select min(sal) from emp where deptno=30;

MIN(SAL)

——–

2400

所以找到的是min(sal)大于2400的数据

4. 不可以在group by后面放置子查询

5. 强调from后面放置子查询

select * from (select ename,sal from emp);

ENAME SAL

——————– —–

张三 5200

李四 3400

王五 4400

赵二 3450

李磊磊 2500

张少丽 2400

林建国 5700

马富邦 2800

沈倩 2100

6. 主查询和子查询可以不是同一张表，只要子查询返回的结果，主查询可以使用，即可

我们查询部门名称为“销售”的员工信息

select *

from emp

where deptno=(select deptno

from dept

where dname=’销售部门’);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1111 李四 销售 1116 03-11月-15 3400 500 30

1112 王五 销售 1116 25-4月 -12 4400 800 30

1115 张少丽 销售 1110 11-3月 -16 2400 1400 30

效果如同下面的多表查询:

select e.*

from emp e,dept d

where e.deptno=d.deptno and d.dname=’销售部门’;

这里注意：

SQL优化： 如果子查询和多表查询都可以，理论上尽量使用多表查询

7. 一般不在子查询中使用order by;但在Top-N分析问题中，必须使用order by

8. 一般先执行子查询，再执行主查询；但相关子查询除外

9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符

多行操作符

–in :在集合中

–查询部门名称为“销售部门”和“金融部门”的员工信息

select * from emp

where deptno in

(select deptno from dept where dname=’销售部门’ or dname=’金融部门’)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1115 张少丽 销售 1110 11-3月 -16 2400 1400 30

1112 王五 销售 1116 25-4月 -12 4400 800 30

1111 李四 销售 1116 03-11月-15 3400 500 30

1118 沈倩 会计 1116 06-5月 -10 2100 0 50

1114 李磊磊 会计 1110 22-12月-15 2500 0 50

效果如同下面的多表查询:

select e.* from emp e,dept d

where e.deptno=d.deptno and (d.name=’销售部门’ or d.name=’金融部门’);

像下面这种查询就是错误的，非法使用子查询

原因是“多行查询使用单行比较符”

10. 注意子查询中null

查询不是老板的员工信息(老板的上司mgr的id是他自己的id)

select * from emp

where empno not in (select mgr from emp where empno!=mgr);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1111 李四 销售 1116 03-11月-15 3400 500 30

1112 王五 销售 1116 25-4月 -12 4400 800 30

1113 赵二 后勤 1110 30-5月 -11 3450 0 40

1114 李磊磊 会计 1110 22-12月-15 2500 0 50

1115 张少丽 销售 1110 11-3月 -16 2400 1400 30

1117 马富邦 后勤 1116 22-7月 -13 2800 0 40

1118 沈倩 会计 1116 06-5月 -10 2100 0 50

查询是老板的员工信息

select * from emp

where empno in (select mgr from emp where empno=mgr);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1110 张三 主管 1110 12-3月 -14 5200 0 20

1116 林建国 主管 1116 22-1月 -16 5700 0 20

但是如果not in集合中含有空值的话，就会取得所有的数据。

所以我们在not in语句的子集合中，要进行空值得判断，不然就会取所有数据。

最后我们再看一些有用的查询，any、all

(1)any 和集合的任意一个值比较

查询工资比30号部门任意一个员工高的员工信息

select * from emp

where sal>any(select sal from emp where deptno=30);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1116 林建国 主管 1116 22-1月 -16 5700 0 20

1110 张三 主管 1110 12-3月 -14 5200 0 20

1112 王五 销售 1116 25-4月 -12 4400 800 30

1113 赵二 后勤 1110 30-5月 -11 3450 0 40

1111 李四 销售 1116 03-11月-15 3400 500 30

1117 马富邦 后勤 1116 22-7月 -13 2800 0 40

1114 李磊磊 会计 1110 22-12月-15 2500 0 50

相当于

select * from emp

where sal > (select min(sal) from emp where deptno=30)

(2)all 和集合的所有值比较

查询工资比30号部门所有员工高的员工信息

select * from emp

where sal>all(select sal from emp where deptno=30);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

————— ——————– —————— ————— ————– —– —– —————

1110 张三 主管 1110 12-3月 -14 5200 0 20

1116 林建国 主管 1116 22-1月 -16 5700 0 20

相当于

select * from emp

where sal > (select max(sal) from emp where deptno=30)

转载请注明出处：http://www.voidcn.com/article/p-xvnogdko-bbb.html