MySQL的子查询(二十)

一 . MySQL的子查询

一.一 子查询的产生

子查询指的是，在一个完整的查询语句里面，嵌套几个不同功能的小查询语句，从而一起完成一个复杂的查询 。 简单来说，就是 功能小查询语句的输出，是完整查询语句的输入， 类似于 Linux 系统中的 管道符 |

如找出与 岳泽霖是同一个部门的员工。

• 那么需要分两步:

第一步: 从 user 表里面找出 姓名 name 为岳泽霖的员工，查询出其员工编号。

select t.deptId from user t where t.name='岳泽霖';

查询出结果 部门编号为1。

第二步: 从 user 表里面找出 部门编号为 1的员工，并且去除掉 ‘岳泽霖’

select * from user t where t.deptId=1 and t.name <> '岳泽霖' ；

这是两条语句，需要依次进行查询。 那么，能不能变成一条语句进行查询呢?

需要将第一步的结果放置到第二步里面。

这就是子查询。 是sql查询语句的批处理。

一.二 子查询的输出结果分类

子查询，即功能小查询语句的输出结果，按照查询出的数据结果划分的话，可以分成四类：

1. 单行单列       (标量子查询)
2. 单行多列        (列子查询)
3. 多行单列        (行子查询)
4. 多行多列        (表子查询)

单行单列 返回一个具体列的内容，是一个确定的值， 可以是字符串，也可以是普通数字，还可以是日期。

单行多列 就是从表中查询出 符合条件的某一行的多个列值。

多行单列 就是从表中 查询出符合条件的某一列的多个行值。

多行多列 就是从表中查询出符合条件的 多个行，多个列的值，是一个新的二维数据表，常用作临时表。

一.三 子查询出现的位置

子查询一般出现在 where 和having 语句后面， 也可以出现在 select 和 from 语句的后面，

但一般以 where 和having 居多。

其中，

在where 语句后面出现的子查询 可以为 单行单列，单行多列，多行单列

在 having 语句后面出现的子查询 为单行单列，用于统计函数，如count() avg() 等。

在 from 语句后面出现的子查询为 多行多列， 作为临时表。

老蝴蝶 根据子查询出现的不同的位置，做不同的讲解。

仍然使用的是 yuejl 数据库里面的 dept 表和 user 表。

二 . where 语句 的子查询

在where 语句后面，常常出现 单行单列，单行多列，多行单列的子查询。

下面，老蝴蝶 分别进行讲解。

二.一 单行单列 (=，<>,> 等 操作符)

单行单列，返回的是一个固定的值， 常常用 = , != , >, < 这样的操作符， 具体使用哪一个，要根据要求选择。

问题1 : 查询出 与 岳泽霖 同一个部门的员工。

需要分三步:

1.查询出 员工 岳泽霖 的所在部门

select t.deptId from user t where t.name='岳泽霖';

2.根据第一步的部门编号 查询出所有的员工

select * from user a where a.deptId=(select t.deptId from user t where t.name='岳泽霖');

3.从第三步的员工里面 去除掉 姓名为 岳泽霖的员工

select * from user a where a.deptId=(select t.deptId from user t where t.name='岳泽霖')
  and a.name <> '岳泽霖';

与岳泽霖 同一个部门的员工 有 两个蝴蝶飞和精灵妹。

问题2：查询出比 两个蝴蝶飞 年龄大的员工

需要分两步:

1.查询出 两个蝴蝶飞的年龄

select t.age from user t where t.name='两个蝴蝶飞';

2.从user表里面查询出所有 年龄大于 第一步两个蝴蝶飞年龄的员工

select * from user a where a.age > (
  select t.age from user t where t.name='两个蝴蝶飞'
  );

问题3 ： 查询出与岳泽霖 同一个部门，并且年龄小于岳泽霖的 员工。

主要是为了说明， where 后面可以跟多个子查询语句

需要分四步:

查询出岳泽霖所在的部门编号

查询出岳泽霖的年龄

从user表里面根据部门编号去查询所有的员工

从第三步结果里面进行筛选，筛选条件为 年龄 小于第二步的年龄

直接写sql语句 为

select * from user t where t.deptId=(select deptId from user where name='岳泽霖')
  and t.age < (select age from user where name='岳泽霖');

这两个人都 符合条件 。

二.二 单行多列 (=,<> 操作符)

查询出多个列值， 那么需要将多个列值封装成一个元组，即(列值1，列值2…)， 利用元组进行判断， 而元组判断只能是 = （相同）, <> (不相同) 其中 元组的 = 表示 元组里面的所有的列值都相同。 <> 表示元组里面的所有的列值有一个不同即可。

= 与 <> 是互补的。

问题1: 查询出与 两个蝴蝶飞 性别相同，年龄相同的 员工。

需要分三步:

1.查询出两个蝴蝶飞的性别和年龄

select * from user t where t.name='两个蝴蝶飞';

2 . 从user 表里面查询出 性别和年龄 为 第一步结果的值

select * from user a where (a.sex,a.age)=(
  select t.sex,t.age from user t where t.name='两个蝴蝶飞'
  );

3 . 从第三步里面筛选出 名称不为两个蝴蝶飞的员工

select * from user a where (a.sex,a.age)=(
  select t.sex,t.age from user t where t.name='两个蝴蝶飞'
  ) and a.name <> '两个蝴蝶飞';

与两个蝴蝶飞 性别相同，年龄也相同的 只有 老蝴蝶 一个人。

问题 2 : 查询出与 两个蝴蝶飞 性别不相同，或者年龄 也不相同的员工。

是第一个问题的补集。 用 <> 代替问题1中的 =号即可。

select * from user a where (a.sex,a.age) <> (
  select t.sex,t.age from user t where t.name='两个蝴蝶飞'
  ) and a.name <> '两个蝴蝶飞';

问题 3: 查询出 与两个蝴蝶飞 性别，年龄，和出生日期 均相同的员工。

主要是说明，可以放置多个列， 但一般都是两个列。

直接写 查询语句为 :

select * from user a where (a.sex,a.age,a.birthday)=(
  select t.sex,t.age,t.birthday from user t where t.name='两个蝴蝶飞'
  ) and a.name <> '两个蝴蝶飞';

二.三 多行单列 (in ,any,all 操作符)

为了更准确的展示数据，先把 user 表进行一下改变， 添加一些新员工数据，修改一下老员工的年龄。

修改完成之后的数据为:

select * from user;

查询出多个行同一列的值，数据类型是相同的，即 查询出一个数组。 那么有关数组的操作， 如 最大的值，最小的值，是否存在于数组 等就可以在数据库中进行体现了。 常见的有 in,any, all 三个操作符

二.三.一 in 操作符

in 表示在里面， not in 表示不在里面。

问题1: 查询出每一个部门，与最大年龄相同的员工。

需要三个步骤:

1 . 查询出每一个部门的最大年龄。 用分组和聚合函数 max

select max(age) from user group by deptId;

2.查询出员工的年龄 在第一步骤里面的员工

select * from user where age in (select max(age) from user group by deptId);

3.查询出年龄 在 25，23，21 中的所有的员工。 与以前的操作符 in 是相同的。

问题2 : 查询出每一个部门，不与最大年龄相同的员工。

即 not in 操作符， 与以前的 not in 操作符是相同的。

select * from user where age not in (select max(age) from user group by deptId);

二.三.二 any(some) 操作符

any 表示 任何一个。 有三种符号

= any , 表示与其中任意一个相同即可。

> any, 比其中任意一个大即可， 即 大于 范围内的最小值即可

< any , 比其中任意一个小即可，即 小于范围内的最大值即可。

> ,< 均不 包括 = ,如果想表示等于， 可以用 >= any, <=any

还有一个 <> any, 这个并不等于 not in .

any 与 some 是相同的意思， 但一般都 用 any 来统一表示。

其中， 每一个部门的最大员工的年龄为:

select max(age) from user group by deptId;

为 25，23，21 。

1 .为 =any 时， 与任意一个相同。 此时 与in 是一致的。

select * from user where age =any (select max(age) from user group by deptId);

2 . 为 > any 时，大于范围内的最小值， 即 年龄大于21即可。

select * from user where age > any (select max(age) from user group by deptId);

会将部门 1中的年龄为24的 员工 ‘精灵妹’ 查询出来。

3 . 为 >= any 时，大于等于范围内的最小值， 即年龄 大于等于21即可。

select * from user where age >= any (select max(age) from user group by deptId);

包含了21, 会将年龄为 21的员工也查询出来。

4 . 为< any时， 小于范围内的最大值， 即 年龄小于 25 即可。

select * from user where age < any (select max(age) from user group by deptId);

会查询出那些为 18和 20 的员工

5 . 为<= any 时，小于等于范围内的最大值， 即年龄大于等于 25即可。

select * from user where age <= any (select max(age) from user group by deptId);

会将年龄为25的员工也查询出来。

6 为 <> any时， 看看是什么情况

select * from user where age <> any (select max(age) from user group by deptId);

发现 会将所有的内容 都 查询出来 了。

<> any 并不常用。 想表达 not in 的用法时，常常使用的是 <> all .

二.三.三 all 操作符

all 是全部， 有以下三种常见的情况。

<> all , 不等于其中的任意一个， 即 not in 的意思

> all, 大于所有的， 即 比其中最大的值还要大。

< all, 小于所有的， 即比其中最小的值还要小。

1 . <> all 时， 不等于其中的任意一个

select * from user where age <> all (select max(age) from user group by deptId);

与 not in 的结果是一样的。

2. > all, 大于所有的，比其中最大的25 还要大

select * from user where age > all (select max(age) from user group by deptId);

没有比25还大的。

3 >= all, 大于等于所有的， 比其中最大的25还要大，或者是相同。

select * from user where age >= all (select max(age) from user group by deptId);

会把 25的查询出来

4 < all, 小于所有的， 比其中最小的21 还要小

select * from user where age < all (select max(age) from user group by deptId);

5 <= all, 小于等于所有的，比其中最小的21还要小，或者是相同的。

select * from user where age <= all (select max(age) from user group by deptId);

二.四 exists 和 not exists

exists 会查询的结果进行判断，如果exists 后面跟的子查询 至少返回 一行， 那么就返回 true,会执行最外侧的查询语句，

如果没有返回一行，那么就返回false，不执行最外侧的那个查询语句

exists 后面的子查询 要用() 进行扩起来。

not exists 与 exists 正好相反

问题1 . 查询一下是否存在编号为1的部门编号，如果存在，则将该部门下的员工查询出来，如果不存在，则不查询任何记录。

这样 判断形式的，可以用 exists 来进行处理

select * from user where exists (select * from dept where id=1) and deptId=1;

看一下，是否存在部门编号为207 的。 (部门表里面没有编号为207的数据)

select * from user where exists (select * from dept where id=207) and deptId=207;

没有查询出任何记录

问题 2. not exists 是对 exists 进行的取反操作

select * from user where not exists (select * from dept where id=1) and deptId=1;

三. having 语句的子查询

having 后面的子查询，主要用于分组后的筛选， 与分组统计有关的。

后面只有一种用法，跟的是 单行单列的值。

问题1 : 查询每个部门的平均年龄大于公司的平均年龄的所有部门及其该部门的平均年龄。

需要分三步:

第一步: 查询出公司的平均年龄 ，即 user表的平均年龄

select avg(age) from user;

第二步: 查询每个部门的平均年龄

select deptId,avg(age) from user group by deptId;

可以简单看出，只有 部门编号为1的才符合。

第三步: 在group by 分组后， 利用 having 进行筛选

select deptId,avg(age) as dage from user group by deptId
  having dage >(select avg(age) from user) ;

四.from 语句的子查询

from 后面的子查询，是多行多列的，是一个临时表， 表示从临时表里面查询数据。 (一般会将这样的临时表，处理成视图)

问题 1: 查询 每个部门的编号，名称 和 员工的最大年龄。

主要分成两步:

连接部门表和员工表，查询出 部门的编号，部门的名称 和员工的姓名，员工的年龄。

select a.id as deptId,a.name as deptName,b.name as userName,b.age as userAge from dept a left join user b 
  on a.id=b.deptId;

2 . 从第一步的结果里面 进行分组， 按照部门编号，部门名称进行分组，查询出最大的年龄

select c.deptId,c.deptName,max(c.userAge) from (
  select a.id as deptId,a.name as deptName,b.name as userName,b.age as userAge from dept a left join user b 
  on a.id=b.deptId
  ) c
    group by c.deptId,c.deptName;

其中, from 后面的子查询 一般比较复杂，需要小心对待。

五. select 语句的子查询

select 语句的子查询，一般不常使用。

如 查询出每个部门的编号，部门名称，和该部门的平均年龄

需要将 平均年龄处理成列的形式。

select d.id,d.name,
  (select avg(u.age) from user u where u.deptId=d.id) as davg_age 
  from dept d;

这样查询，效率很低，所以一般不在select 时进行子查询。

子查询一定要记住 where 和having 的常见用法。