扒扒数据库长长知识(下载资源组合看day2)之 07 （子查询重点+难点）（一般子查询）

最新推荐文章于 2024-11-01 22:34:20 发布

翻译最新推荐文章于 2024-11-01 22:34:20 发布 · 292 阅读

数据库专栏收录该内容

12 篇文章

订阅专栏

本文详细介绍了SQL中子查询的基本概念、分类、执行顺序、常见问题及优化技巧，包括如何合理书写、避免NULL值影响、理解集合操作符与NULL的交互、以及通过子查询实现复杂查询的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

day2 的资料

1 为什么要有子查询
--查询工资比scott高的员工信息

select sal from emp
where ename = 'SCOTT'

select * from emp
where sal > 3000

====把2步合成一步
select *
from emp
where sal > (select sal from emp
where ename = 'SCOTT')
===主查询和子查询
====子查询的本质多个select语言的嵌套

2 子查询知识体系搭建

1 合理的书写风格
2 子查询外面的()不要忘记
3 子查询和主查询可以查询的是同一张表,也可以不是同一张表,
只要子查询返回的结果,主查询可以用即可...
4 在什么地方放置子查询

select a , b, c ---OK, 只能存放单行子查询,不能是多行子查询
from tab1 ---OK 重点..

where col in (em1, em2) ----OK
col between a1 and a2
col > 222
col > ()
group by ... ---不可以
having ..... ---可以
order by .. ---不可以
5 子查询的分类
单行操作符对应单行子查询，多行操作符对应多行子查询。
按照子查询返回的条目数,分为: 单行子查询和多行子查询
单行子查询只能使用单行比较操作符( = > >= < <= <>), --ppt上的例子
多行子查询只能使用多行比较操作符(in any all)
--eg 单行例子ppt例子
--eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法
--eg 查询薪水比30号部门任意一个员工薪高的员工信息
-eg 查询薪水比30号部门所有员工高的员工信息

子查询按照执行的顺序
一般性子查询相关子查询

6 子查询遇见NULL

7 一般情况下,子查询返回的是一个集合..子查询不排序....Top-N问题除外

=======>oracle分页

解释3: --eg 查询部门名称是SALES的员工信息 2种方法

A)
select *
from emp
where DEPTNO = (select deptno from dept where dname='SALES') --2次检索

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----------- --------- ---------- -------------- ------ ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
B)
select e.*
from emp e, dept d
where e.deptno = d.deptno and d.dname='SALES'

=========>sql优化
1)select * /abc 2) where and and and ....<---- 3)having where ...
4) 是字查询的效率高还是多表查询的效率高多表查询效率高...一次检索一次连接（拼接两个表占用内存多）
子查询低多次连接（连接最费时）

解释4-1:---OK, 只能存放单行子查询,不能是多行子查询

select ename, empno, (select deptno from emp) AA
from emp
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行

select ename, empno, (select deptno from emp where EMPNO = 7369) AA
from emp

已写入 file afiedt.buf

1 select ename, empno, (select deptno from emp where EMPNO = 7369) AA ,sysdate
2* from emp
SQL> /

ENAME EMPNO AA SYSDATE
----------- ---------- ---------- --------------
SMITH 7369 20 08-10月-14
ALLEN 7499 20 08-10月-14
WARD 7521 20 08-10月-14
JONES 7566 20 08-10月-14
MARTIN 7654 20 08-10月-14
BLAKE 7698 20 08-10月-14
CLARK 7782 20 08-10月-14
SCOTT 7788 20 08-10月-14
KING 7839 20 08-10月-14
TURNER 7844 20 08-10月-14
ADAMS 7876 20 08-10月-14
JAMES 7900 20 08-10月-14
FORD 7902 20 08-10月-14
MILLER 7934 20 08-10月-14

已选择14行。
解释4-2 from tab1 ---OK 重点..

查询员工的姓名和薪水 (考试题)

select *
from emp - ---集合.....
----------

select *
from ( select ename, sal from emp)

ENAME SAL
----------- ------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300

解释5-1 单行子查询
查询和141号工种一样的,并且比143号员工薪水高的员工信息
查询薪水最低的员工信息
查询每部门编号和部门最小工资,并且这个部门最低工资大于50号部门的最低工资
解释5-2 多行子查询
多行子查询只能使用多行比较操作符(in any all)
--eg 单行例子ppt例子
--eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法
--eg 查询薪水比30号部门任意一个员工薪高的员工信息
-eg 查询薪水比30号部门所有员工高的员工信息
-eg 查询部门名称是*(不是)SALES 和 ACCOUNTING 的员工信息 2种方法

select * from emp

where deptno in
(select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING')

1 select * from emp
2 where deptno in
3* (select deptno from dept where dname = 'SALES' or dname = 'ACCOUNTING')
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10

ANY 和集合中的任意一个值比较
ALL 和集合中的所有值比较

--eg 查询薪水比30号部门任意一个员工薪高的员工信息大于集合中的最小值 any
--eg 查询薪水比30号部门所有员工高的员工信息大于集合中的最大值 all

单行操作符对应单行子查询，多行操作符对应多行子查询。
select * from emp
where sal > all(select sal from emp where deptno = 30)

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

1 select * from emp
2* where sal > (select sal from emp where deptno = 30)
SQL> /
where sal > (select sal from emp where deptno = 30)
*
第 2 行出现错误:
ORA-01427: 单行子查询返回多个行

1 select * from emp
2* where sal > (select max(sal) from emp where deptno = 30)
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20

解释6 - 查询不是经理的员工信息
---- 查询是经理的员工信息

select * from emp
where empno in (经理的集合)

select * from emp
where empno in (select mgr from emp)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7902 FORD ANALYST 7566 03-12月-81 3000 20

已选择6行。

select * from emp
where empno not in (select mgr from emp)

select * from emp
where empno not in (select mgr from emp where mgr is not null)

1 select * from emp
2* where empno not in (select mgr from emp where mgr is not null)
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7934 MILLER CLERK 7782 23-1月 -82 1300 10

已选择8行。