Mysql-----子查询

1、子查询概述

子查询简介

子查询指一个查询语句嵌套在另一个查询语句内部的查询，内部的查询是外部查询的条件，这个特性从MySQL4.1开始引入。

SQL中子查询的使用大大增强了SELECT查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

子查询语法

SELECT -- 主查询
    select_list
FROM
    table
WHERE
    expr operator > (SELECT -- 子查询
            select_list
        FROM
            table);

子查询(内查询)在主查询之前执行完成。

子查询的结果被主查询(外查询)使用。

2、子查询的分类

根据子查询的结果集可分为：

标量子查询(单行单列)

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。

一般多为条件的一部分 配合比较运算符： = <> > >= < <=

具体实例：

#查询 工资高于ALLEN的员工
#第一步：查询ALLEN的工资
SELECT sal FROM emp WHERE ename='ALLEN'

第二步：查询高于ALLEN工资的员工
SELECT * FROM emp WHERE sal > (${第一步})

合并：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='ALLEN')

列子查询(多行单列)

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。

一般配合的运算符： in , not in , any , all

可以使用 IN、ANY、SOME 和 ALL 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

具体实例

#查询 工资高于30部门所有人的员工信息

#1.查询30部门最高工资
select max(sal) from emp where deptno = 30;

#2. 查询工资大于1 的信息
select * from emp where sal >  (1);

#3.合并
select * from emp where sal >  (
  select max(sal)  from emp where deptno = 30
);

行子查询(单行多列)

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

常用的 运算符 ： = , <> , in , not in

#查询工作和工资与MARTIN完全相同的员工信息
#1. 查询MARTIN的工作和工资
select job,sal from emp where ename = 'MARTIN';

#2. 查询工作与薪水与1一样的员工
select * from emp where job = (
  select job from emp where ename = 'MARTIN'
)
and sal = (
   select sal from emp where ename = 'MARTIN'
)
and ename <> 'MARTIN';


简化： =替换为in   多列使用小括号括起来
select * from emp where (job,sal) in (
  select job,sal from emp where ename = 'MARTIN' 
) and ename <> 'MARTIN';

表子查询(多行多列)

指子查询返回的结果集是 N 行 N 列的一个表数据。

一般作为 表使用 即 在from中使用 关键字有：exists

具体案例：

#查询每个部门的人数 (emp), 以及部门名称(dept)

#1. 查询每个部门的人数
select deptno, count(1) from emp group by deptno;

# 把1与dept表进行表连接

SELECT
	d.dname,
	t.num
FROM
	dept d
	left JOIN ( SELECT deptno, count(1) num FROM emp GROUP BY deptno ) t ON d.deptno = t.deptno;
	
# 使用另外一种写法, 子查询作为select一部分
# 关联子查询:子查询与主查询有关联的,不能独立运行 
select d.dname, (select count(1) from emp e group by deptno having e.deptno = d.deptno ) num from dept d;

3、关联子查询与非关联子查询区别

非关联子查询执行原理:

1、先执行子查询

2、再执行主查询

3、进行循环判断

关联子查询运行原理

1、先执行主查询

2、循环获取主查询的一条记录

3、执行子查询

总结：很多查询中需要使用子查询。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死。子查询可以使查询语句很灵活，但子查询的执行效率不高。