mysql加强（6）~子查询简单介绍、子查询分类

一、子查询简单介绍

1、什么是子查询？

一个查询之中嵌套了其他的若干查询。

• 在使用select 语句查询时，有时候where的查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。
• 子查询一般出现在from 和 where 子句中。

2、子查询语法：

select <select_list>

from 表名

where 条件(s) 操作符

​ (select <select_list> from 表名);

• 子查询在主查询前执行，然后主查询再使用子查询的结果。

3、子查询例子：

# 查询大于公司平均工资的员工姓名
select ename, sal from emp where sal > (select avg(sal) from emp);

4、子查询经典案例---Oracle的分页查询

5、子查询的使用注意事项：

• 子查询需要用括号括起来
• 将子查询放在比较运算符的右边【增强可读性】
• 对单行子查询使用单行运算符
• 对多行子查询使用多行运算符

 

二、子查询的分类

1、子查询的分类【根据子查询的结果集来划分】

（1）单行单列子查询：子查询返回一行一列记录，好比就是一个值

• 返回一条记录---比较运算符(针对一个值的运算符)： = > < >= < !=

  # 查询大于公司平均工资的员工姓名
  select ename, sal from emp where sal > (select avg(sal) from emp);
  

(2) 多行单列子查询：子查询返回单列多行记录，好比是多个值

• 返回多条记录---比较运算符(多个值的运算符)： in any all

• in 相当于 = any

• any:与子查询返回的任意一个值做比较； all：与子查询返回的每一个值做比较

  # 查询工资等于部门经理的员工信息
  select * from emp where sal in (select sal from emp where job = 'manager');
  select * from emp where sal = any (select sal from emp where job = 'manager');
  # 查询工资大于任意部门经理的员工信息
  select * from emp where sal > any (select sal from emp where job = 'manager');
  # 查询工资小于任意部门经理的员工信息
  select * from emp where sal < any (select sal from emp where job = 'manager');
  

（3）多行多列子查询：子查询返回多列一行/多行记录，好比是一张表 【其实就是多表连接查询】

• 一般把查询结果含多个列的当做临时表【必须给临时表起个别名】，接着在临时表上继续查询或者连接查询；

  # 查询出每个部门的编号、名称、部门人数、平均工资
  select t.deptno, d.dname, t.count_empno, t.avg_sal
  from dept d  left join
  (select e.deptno, count(e.empno) count_empno, avg(e.sal) avg_sal from emp e group by deptno) t
   on d.deptno = t.deptno;
  

 

2、union/union all

• join 是横向连接，union/union all 是纵向连接（一般用于作为临时表）
• 注意：
  • union 内部的select 语句必须是拥有相同数量的列
  • 列也必须拥有兼容的数据类型
  • 每条select语句中的列的顺序必须相同
  • union 结果集的列名总是等于union中第一个select语句的列名
  • union操作符选去不同的值，若允许值重复-使用union all 性能更高

• 语法：

  select <select_list> from 表名1

  union | union all

  select <select_list> from 表名2

• 全连接--mysql不止全连接，不过可以使用左右连接+union实现

  #全连接查询员工的编号、名称、部门名称
  select e.deptno,e.ename,d.dname from emp e left join dept d on e.deptno = d.deptno
  union
  select e.deptno,e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;