【oracle学习】1.子查询

子查询所要解决的问题:问题不能一步求解
实际上就是sql语句的嵌套,就是在一个select语句中嵌套另外一个select语句

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

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

[sql] view plain copy

  1. create table EMP(
  2.     EMPNO NUMBER,
  3.     ENAME VARCHAR2(10),
  4.     JOB VARCHAR2(9),
  5.     MGR NUMBER,
  6.     HIREDATE DATE,
  7.     SAL BINARY_DOUBLE,
  8.     COMM BINARY_DOUBLE,
  9.     DEPTNO NUMBER
  10. );


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

然后加入一些数据:

[sql] view plain copy

  1. insert into EMP (
  2.     EMPNO,ENAME ,JOB ,MGR ,HIREDATE ,SAL ,COMM ,DEPTNO
  3. )
  4. values(
  5.     1110,‘张三’,‘主管’,1110,’12-3月 -14′,5200,0,20
  6. );


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

添加完成后结果
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

然后创建一张部门表

[sql] view plain copy

  1. create table dept(
  2.     DEPTNO NUMBER,
  3.     DNAME VARCHAR2(50)
  4. );

添加数据之后结果
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)

未经允许不得转载:JX BLOG » 【oracle学习】1.子查询

赞 (0)

评论 0

评论前必须登录!

登陆 注册