mysql进阶七：子查询

/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询。

分类：按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：表子查询
※where或having后面：
标量子查询（单行）
列子查询（多行）
行子查询
exists后面（相关子查询）：表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（一列多行）
行子查询（一行多列）
表子查询（一般为多行多列）
*/

一、where或having后面

/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、列子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
< > >= <= <>
列子查询：一般搭配着多行操作符使用
in any/some all
*/

1、标量子查询

案例一：谁的工资比abel高？

①查询abel的工资

show databases;
use myemployees;
select salary from employees where last_name ='Abel';

②查询员工的信息，满足salary大于①中的结果

select * from employees where salary > (
    select salary from employees where last_name ='Abel'
);

案例二：job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

①查询141号员工的job_id

select job_id from employees where employee_id = 141;

②查询143号员工的salary

select salary from employees where employee_id = 143;

③查询员工的姓名、job_id和工资（要求job_id与①相同，salary比②多）

select last_name,job_id,salary from employees where job_id = (
    select job_id from employees where employee_id = 141
) and salary>(
    select salary from employees where employee_id = 143
);

案例三：查询公司工资最少的员工的last_name，job_id和salary

①查询公司的最低工资

select min(salary) from employees;

②查询last_name,job_id和salary，要求salary=①

select last_name,job_id,salary from employees 
where salary = (
    select min(salary) from employees
);

案例四：查询最低工资大于 50号部门最低工资 的部门id和其最低工资

①查询50号部门的最低工资

select min(salary) from employees where department_id = 50;

②查询每个部门的最低工资

select min(salary),department_id from employees group by department_id;

③在②基础上筛选salary大于①的department_id和min（salary）

select min(salary),department_id from employees group by department_id 
having min(salary) > (
    select min(salary) from employees where department_id = 50
);

2.列子查询（多行子查询）

案例一：返回location_id是1400或1700的部门中的所有员工姓名

①查询location_id 是1400或1700的部门编号

select department_id from departments where location_id in(1400,1700);

②查询员工姓名，要求部门编号是①列表中的某一个(in关键字可以换成=any（）)

select last_name from employees where department_id in(
    select department_id from departments where location_id in(1400,1700)
);
#或
select last_name from employees where department_id =any(
    select department_id from departments where location_id in(1400,1700)
);
#若换成not in
select last_name from employees where department_id not in(
    select department_id from departments where location_id in(1400,1700)
);
#或
select last_name from employees where department_id <>all(
    select department_id from departments where location_id in(1400,1700)
);

案例二：返回其他工种中比 job_id为‘IT_PROG’工种任一工资 低 的

员工的员工号、姓名、job_id以及salary

#①查询job_id为‘IT_PROG’工种的所有工资
select salary from employees where job_id = 'IT_PROG';
#②查询比①中工资低的员工的员工号、姓名、job_id以及salary
select employee_id,last_name,job_id,salary from employees 
where salary<any(
    select salary from employees where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';
#或
select employee_id,last_name,job_id,salary from employees 
where salary<(
    select max(salary) from employees where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';

案例三：返回其他工种中比 job_id为‘IT_PROG’工种所有工资 低 的

员工的员工号、姓名、job_id以及salary

select employee_id,last_name,job_id,salary from employees 
where salary<all(
    select salary from employees where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';
#或
select employee_id,last_name,job_id,salary from employees 
where salary<(
    select min(salary) from employees where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';

行子查询（结果集一行多列或多行多列）

案例:查询员工编号最小并且工资最高的员工信息

#①查询最小的员工编号
select min(employee_id) from employees;
#②查询工资最大的工资
select max(salary) from employees;
#③查询对应的员工信息
select * from employees where employee_id = (
    select min(employee_id) from employees
) and salary = (
    select max(salary) from employees
);
#或
select * from employees where (employee_id,salary) = (
    select min(employee_id),max(salary) from employees
);

二.select后面

/*
仅仅支持标量子查询
*/

案例1：查询每个员工的个数

select d.*,(
    select count(*) from employees e where e.department_id = d.department_id
) 个数 from departments d;

案例2：查询员工号等于102的部门名

select department_name from departments d inner join employees e 
on e.department_id = d.department_id where e.employee_id = 102;
#或
select (
    select department_name from departments d inner join employees e 
on e.department_id = d.department_id where e.employee_id = 102
) 部门名;

三：from后面

/*
将子查询结果充当一张表，要求必须起别名
*/

查询每个部门的平均工资的工资等级

#①每个部门的平均工资
select avg(salary),department_id from employees group by department_id;
select * from job_grades;
#②连接上面的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
select ag_dep.*,g.grade_level
from (
    select avg(salary) ag_sal,department_id from employees group by department_id
) ag_dep 
inner join job_grades g 
on ag_dep.ag_sal between lowest_sal and highest_sal;

四、exists后面（相关子查询）

/*
语法：exists(完整的查询语句)
结果：0或1
*/

select exists (select employee_id from employees where salary = 30000) 员工编号;

案例1：查询有员工的部门名

select department_name from departments d where exists(
    select * from employees e where e.department_id = d.department_id
);
#in
select department_name from departments d where d.department_id in (
    select department_id from employees 
);

案例2：查询没女朋友的男神信息

#in
use girls;
select bo.* from boys bo where bo.id not in (
    select boyfriend_id from beauty
); 
select * from boys;
select * from beauty;
#exists
select bo.* from boys bo where not exists(
        select boyfriend_id from beauty b 
        where b.boyfriend_id = bo.id
);
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 213,928评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,192评论 3赞 387
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 159,468评论 0赞 349
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,186评论 1赞 286
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,295评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,374评论 1赞 292
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,403评论 3赞 412
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,186评论 0赞 269
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,610评论 1赞 306
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,906评论 2赞 328
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,075评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,755评论 4赞 337
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,393评论 3赞 320
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,079评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,313评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,934评论 2赞 365
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,963评论 2赞 351

推荐阅读更多精彩内容

  • mysql子查询
    进阶7：子查询 /*含义：出现在其他语句中的select语句，称为子查询或内查询外部的查询语句，称为主查询或外查询...
    majorty阅读 3,041评论 0赞 1
  • MySQL实战5 子查询
    MySQL实战 目录 子查询介绍：出现在其他语句中的select语句，被包裹的select语句就是子查询或查询包裹...
    香沙小熊阅读 678评论 0赞 0
  • mysql子查询经典案例
    1. 查询工资最低的员工信息: last_name, salary ①查询最低的工资 SELECT MIN(sal...
    majorty阅读 1,867评论 0赞 1
  • 守夜（上）
    303号病房。 靠里头的病床上，躺着一位满头白发的老奶奶，鼻孔里插着氧气管，身旁摆着一台心电仪...
    今夜独眠阅读 794评论 3赞 5
  • 节日
    节日带来欢乐的氛围 人们心中洋溢着幸福 这一天整体都是暖色调 无论天气怎样 都不会影响 人们内心压抑 所以发明了节...
    让比耶Fonsi阅读 50评论 0赞 2