定义:在一个查询语句中 嵌套一个或者多个查询语句,该嵌套的查询语句成了外层查询的一部分
里层查询 --子查询
外层查询 --主查询
把子查询(里层查询)的结果,当做条件来进行主查询(外层查询)。
分类:
按照子查询 是否 与 主查询 相关联来分类:
1.不相关子查询:
子查询可以单独运行,子查询不会牵扯到主查询的部分内容,
主查询只在乎子查询的查询结果,把子查询(里层查询)的结果,当做条件来进行主查询(外层查询)
2.相关子查询:
子查询不能单独运行,子查询会牵扯到主查询的部分内容
主查询只在乎和子查询的关联关系
不相关子查询:
子查询可以单独运行,子查询不会牵扯到主查询的部分内容,
主查询只在乎子查询的查询结果,把子查询(里层查询)的结果,当做条件来进行主查询(外层查询)
分类:按照子查询的查询结果不同分类
1.1单列子查询
语法:select *|列 from 表名1 where 列 =|<>|>|<|in (select 列 from 表名2) ;
案例:
1.查询地点在纽约的部门下的所有员工信息
select * from dept;
--分步:
--1.地点在纽约的部门编号
select deptno from dept where loc = 'NEW YORK';
--2.查询步骤1结果中的员工信息
select * from emp where deptno=10;
--3.合并前2步:
select * from emp where
deptno=(select deptno from dept where loc = 'NEW YORK'); ---子查询需要括号括起来
2.查询和SMITH在同一个部门下的员工名字,工资,部门编号
--分步:
--1.SMITH的部门编号
select deptno from emp where ename='SMITH';
--2.查询步骤1结果中的员工信息
select * from emp where deptno=20;
--3.合并前2步:
select * from emp
where deptno=(select deptno from emp where ename='SMITH'); ---子查询需要括号括起来
3.查询emp表中一共有多少个部门
--方法一:
--把子查询的结果当作一个临时的结果表
--(子查询实现)
--分步:
--1.先取到员工表中不同的部门编号:
select deptno from emp group by deptno;
select distinct deptno from emp ;
--2.把步骤1的结果当作一个临时表,取该表中的记录数:
select count(1) from (select deptno from emp group by deptno);
select count(1) from (select distinct deptno from emp);
--方法二:
--count(列) ---取该列不为空的值的个数
select count(distinct deptno) from emp;
1.2多列子查询
语法:select * from 表名1 where (列1,列2) =|in (select 列1,列2 from 表2 where 条件);
--1.查询与SMITH相同部门且相同岗位的员工信息
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';
--2.查询和10号部门的职位和领导都相同的员工信息
select * from emp where (job,mgr)in (select job,mgr from emp where deptno=10) and deptno<>10;
注意:多列子查询查询出来的列的个数,顺序,数据类型要和主查询条件里保持一致。
相关子查询:
子查询不能单独运行,子查询会牵扯到主查询的部分内容
主查询只在乎和子查询的关联关系
案例:
1.查询员工姓名,岗位,薪资,部门编号,以及对应的部门名称
1.1 主查询
select ename,job,sal ,t.deptno ,(对应的部门名称)
from emp t;
1.2 子查询:
select dname from dept where deptno =t.deptno;
1.3合并前两步:
select ename,job,sal ,t.deptno ,(select dname from dept where deptno =t.deptno) as 部门名称
from emp t;
select ename,job,sal ,deptno ,(select dname from dept where deptno =deptno) as 部门名称
from emp ; ---报错:单行子查询返回多行
2.要求每个员工后边显示他自己的领导姓名
2.1 每个员工的信息
select empno,ename,t.mgr,(显示他自己的领导姓名) from emp t;
2.2 员工的领导姓名
select ename from emp where empno=t.mgr; ---员工的领导编号 ==领导的员工编号
2.3合并前两步
select empno,ename,t.mgr,(select ename from emp where empno=t.mgr) from emp t;
--在相关子查询中,子查询和主查询用到的表,一定要起别名区分。
select empno,ename,t.mgr,(select ename from emp where empno=t.mgr)
from emp t;
3.查询工资高于自己部门平均工资的员工信息
3.1 员工信息
select * from emp where sal>(自己部门平均工资)
3.2 自己部门平均工资
--10部门的平均工资
select avg(sal) from emp where deptno=10;
--20部门的平均工资
select avg(sal) from emp where deptno=20;
select avg(sal) from emp where deptno=t.deptno;
3.3合并前两步
select t.*, (select avg(sal) from emp where deptno = t.deptno)
from emp t
where sal > (select avg(sal) from emp where deptno = t.deptno);
总结:
子查询:
不相关子查询 ---- 子查询可以单独运行
多列子查询(子查询的返回结果与主查询的条件 在 个数、 顺序、数据类型要保持一致)
相关子查询 -----子查询不可以单独运行
子查询与主查询之间存在关联关系
1.子查询可以放在 where 和 having 后边
2.子查询可以放在select 后边
注意:--只能放单行单列的子查询 select empno,(单行单列子查询) from emp;
3.子查询可以放在 from 后边
4.子查询不能放在 group by 后边 ***************
5.子查询必须要用括号括起来
6.子查询的返回结果与主查询的条件在 个数、 顺序、数据类型要保持一致
7.不相关子查询返回结果为单行时可以用=|in,返回结果为多行时 必须要用 in