MySQL多变的子查询

子查询的分类

按查询的结果数来分类，可分为单行子查询和多行子查询：

• 单行子查询：返回一条结果或0条结果
• 多行子查询：返回的记录数 >= 2

按子查询是否被执行多次，可分为相关子查询和不相关子查询

• 相关子查询：子查询执行多次
• 不相关子查询：子查询执行一次

单行子查询

单行比较操作符

= > >= < <= <>

基本用法

-- 查询菜单ID大于菜单名为"用户管理"的所有菜单信息
select *
from sys_menu
where menu_id > (select menu_id from sys_menu where menu_name = '菜单管理');

查询结果：

HAVING中的子查询

-- 查询最小的用户名长度大于部门ID为103的部门的最小的用户名长度的用户ID和最小用户名长度（有点绕。。。）
select user_id, min(length(nick_name))
from sys_user
group by user_id
having min(length(nick_name)) >= (select min(length(nick_name))
                       from sys_user
                       where dept_id = 103);

查询结果：

CASE中的子查询

-- 根据部分名称是否为”深圳总公司“来区分用户是在”总部“还是在“分部”
select nick_name,
       (case dept_id when (select dept_id from sys_dept where dept_name = '深圳总公司') then '总部' else '分部' end) location
from sys_user;

-- 当然，以上查询更好的方式是使用IF函数，如下：
select nick_name,
       (IF(dept_id = (select dept_id from sys_dept where dept_name = '深圳总公司'), '总部', '分部')) location
from sys_user;

查询结果：

子查询中的空值

-- 子查询返回空值时，结果为空
select *
from sys_menu
where menu_id = (select menu_id from sys_menu where menu_name = '222');

查询结果：

多行子查询

多行比较操作符

• IN：等于列表中的任意一个
• ANY：需要和单行比较操作符一起使用，和子查询返回的某一个值比较
• ALL：需要和单行比较操作符一起使用，和子查询返回的所有值比较
• SOME：与ANY作用相同

-- 查询用户名长度小于部门ID为103的任一用户名长度的用户信息
select *
from sys_user
where length(nick_name) <= any
      (select length(nick_name)
       from sys_user
       where dept_id = 103);

查询结果：

相关子查询

-- 查询本部门中用户名长度小于部门平均用户名长度的用户信息
select *
from sys_user a
where length(a.nick_name) >
      (select avg(length(b.nick_name)) from sys_user b where a.dept_id = b.dept_id);

查询结果：

• EXISTS：若子查询中存在数据则返回TRUE，否则返回FALSE

• NOT EXISTS：与EXISTS相反

-- 查询用户表中部门ID在部门表中不存在的用户数据
select *
from sys_user a
where not exists(select 'X' from sys_dept b where a.dept_id = b.dept_id);

查询结果：

测试表结构与脚本

脚本文件：ry_20210908.sql

欢迎关注我的公众号：『深海云帆』