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);
查询结果: