SQL第3/n篇（持续更新中）子查询很重要

进阶7 子查询

含义：
子查询（内查询）：出现在其他语句中的select语句
主查询（外查询）：外部的查询语句
分类：
一、按子查询出现的位置：
1.select后面：仅仅支持标量子查询
2.from后面：表子查询
3.where或者having后面：❤
（1）标量子查询 √
（2）列子查询 √
（3）行子查询（较少）
4.exsits后面（相关查询）：表子查询

二、按结果集的行列数：
标量子查询（一行一列）单行子查询
列子查询（一列多行）多行子查询
行子查询（一行多列）多行多列
表子查询（多行多列）

一、where 或者 having 后面

1.单行子查询
2.多行子查询
3.多列多行
特点：
1.子查询放在小括号后面
2.子查询一般放在条件的右侧
3.标量子查询搭配着单行操作符使用（>,<,>=,<=,<>,=)
列子查询搭配着多行操作符使用（in，any/some,all)

1.标量子查询
案例1：谁的工资比Abel高
SELECT 
  last_name 
FROM
  employees 
WHERE salary > 
  (SELECT salary FROM employees WHERE last_name = 'Abel') ;
案例2：返回job_id与141号员工相同，salary比143员工多的员工姓名，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);
案例3：返回公司工资最少的员工的姓名，job_id，salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees) ;
案例4：查询最低工资>50号部门最低工资 的部门id和它的最低工资

①50号部门的最低工资

SELECT MIN(salary) FROM employees WHERE department_id = 50

②每个部门的最低工资

SELECT MIN(salary) FROM employees GROUP BY department_id

③ ②的最低>①最低 的部门id 和最低工资

SELECT MIN(salary) FROM employees 
GROUP BY department_id 
HAVING MIN(salary) >(SELECT MIN(salary) FROM employees WHERE department_id = 50);

2.列子查询

in|not in:等于列表中的任何一个 in==any not in== <>all
any|some：跟某一个值比较
all：跟所有值比较

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

法一：外连接

SELECT last_name ,location_id, d.department_id  
FROM employees e
LEFT JOIN departments d ON e.`department_id`=d.`department_id`
WHERE location_id IN (1400,1700);

法二：子查询
①location_id是1400或者1700的部门

SELECT department_id FROM departments WHERE location_id IN (1400,1700)

②部门中所有员工的姓名

SELECT last_name FROM employees
WHERE department_id IN(
    SELECT department_id FROM departments WHERE location_id IN (1400,1700)
    );
案例2：返回其他工种中比 job_id为"IT_PROG"工种 任意工资低 的员工的员工号、姓名、job_id、 salary

job_id为"IT_PROG"部门的工资

SELECT DISTINCT salary  FROM employees WHERE job_id = "IT_PROG"

其他部门的员工的员工号、姓名、job_id、 salary
法一：any

SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM employees 
WHERE  salary < ANY(SELECT DISTINCT salary FROM employees WHERE job_id = "IT_PROG")
AND job_id <> 'IT_PROG'

法二：max

SELECT `employee_id`,`last_name`,`job_id`,`salary`
FROM employees 
WHERE  salary < (SELECT DISTINCT MAX(salary) FROM employees WHERE job_id = "IT_PROG")
AND job_id <> 'IT_PROG'

3.行子查询（一行多列）要求查询的筛选条件一样

案例1：员工编号最小，工资最高的员工信息

法一：普通的and连接条件

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：查询每个部门的员工个数 (有部门但却没有员工departments)
SELECT d.*, (
    SELECT COUNT(*) FROM `employees` e
    WHERE e.`department_id`= d.department_id
    ) 员工个数
FROM departments d GROUP BY `department_id`
案例2：查询员工号=102的部门名
SELECT department_name
FROM `departments` d
WHERE d.department_id = (
    SELECT `department_id` FROM `employees` e
    WHERE `employee_id`=102 
    )

三、from 后面 (子查询的结果表必须起别名)

案例1：查询每个部门的平均工资的工资等级
SELECT avg_d.* ,`grade_level`
FROM (
    SELECT AVG(salary)  ag ,`department_id` FROM employees
    GROUP BY `department_id` 
    ) AS avg_d 
JOIN `job_grades` j
ON avg_d.ag BETWEEN `lowest_sal` AND `highest_sal`

四、exists（相关查询 存在与否返回bull值。）

基本用法
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary =30000);
查询有员工的部门名 （先去查询外查询，再通过exists筛选）
SELECT department_name FROM departments d
WHERE EXISTS(
        SELECT * FROM employees e
        WHERE d.department_id =e.`department_id`
        )

能用exists的也可以用in实现

SELECT department_name FROM departments d
WHERE d.`department_id` IN (
    SELECT `department_id` FROM employees 
)
查询没有女朋友的男神的信息

in

SELECT bo.* FROM boys bo
WHERE bo.id NOT IN (
    SELECT `boyfriend_id` FROM beauty
)

exists (总有一个连接条件，而且直接写exists 它之前不用写变量啥的）

SELECT bo.* FROM boys bo
WHERE NOT EXISTS (
    SELECT * FROM beauty b
    WHERE b.`boyfriend_id` = bo.id
)

----做题练习----

1、查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees 
WHERE department_id = (
        SELECT department_id FROM employees 
        WHERE last_name = 'Zlotkey'
    )
2、查询工资比公司的平均工资高的员工的员工号、姓名和工资
SELECT employee_id ,last_name,salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
)
3、查询各部门中，工资比本部门平均工资高的员工的员工号，姓名和工资

①本部门平均工资

SELECT AVG(salary) FROM employees GROUP BY department_id

②连接虚拟表和employees表， 条件工资>①

SELECT employee_id ,last_name,salary
FROM  employees e
JOIN (SELECT AVG(salary) ag ,department_id  FROM employees  GROUP BY department_id) avg_s 
ON e.department_id = avg_s.department_id 
WHERE e.salary > avg_s.ag
4、查询 与姓名中包含字母u的员工 在相同部门 的员工的工号和姓名

姓名中包含字母u的员工 的部门

SELECT department_id FROM employees WHERE last_name LIKE '%u%'

SELECT employee_id ,last_name
FROM  employees 
WHERE department_id IN (
    SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%'
)
5、查询在部门的location_id 为1700的部门工作的员工工号

location_id 为1700的部门

SELECT DISTINCT department_id FROM `departments` WHERE `location_id`=1700

SELECT `employee_id` FROM `employees` 
WHERE `department_id` IN (
    SELECT DISTINCT department_id FROM `departments` WHERE `location_id`=1700
)
6、查询管理者是K_ing的员工姓名和工资
SELECT last_name ,salary
FROM employees
WHERE `manager_id` IN (
#K_ing 的员工号
    SELECT `employee_id` FROM employees
    WHERE last_name = 'K_ing'
)

King 的员工号

SELECT `employee_id` FROM employees
WHERE last_name = 'K_ing'

进阶八 分页查询 limit

场景：一页显示不全，分页提交sql语句
语法：
select 查询列表
from 表
【join type】 表
where 筛选条件
group by 分组条件
having 分组后的筛选条件
order by 排序
limit offset,size

offset:要查询的起始索引，从0开始
如果查询共第一条开始，offset可以省略
size：要显示的条目

执行顺序：
from 表 join 表 on 字段
where 筛选 group by having 筛选
select 字段 order by 排序 limit 分页

公式 page第几页，size 一页显示多少条 limit (page-1)*size,size

size = 10

page   起始索引
1   0
2   10
3   20
案例1、第11条到第25条的员工信息
SELECT * FROM employees LIMIT 10,15;
案例2、有奖金的员工信息，返回工资较高的前10名
SELECT * FROM employees
WHERE `commission_pct` IS NOT NULL
ORDER BY `salary` DESC
LIMIT 10
案例3、平均工资最低的部门信息

平均工资最低的部门ID

SELECT department_id 
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1

查询部门信息

SELECT d.*
FROM departments d
WHERE department_id =(
    SELECT department_id 
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary) 
    LIMIT 1
)
案例4：查询平均工资最低的部门信息以及该部门的平均工资

①平均工资最低的部门id

select avg(salary) ag,department_id
from employees 
group by department_id
order by avg(salary)
limit 1

②部门信息

select d.*,ag
from departments d
join (
    SELECT AVG(salary) ag,department_id
    FROM employees 
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1
    ) ag_dep  on ag_dep.department_id = d.department_id
案例5：查询平均工资最高的job信息
select j.*
from jobs j
where job_id = (
    select  job_id from employees 
    group by job_id
    order by AVG(salary) desc limit 1
)
案例6：查询平均工资高于公司平均工资的部门
select d.*
from departments d
where department_id in  (
    select department_id from employees 
    group by department_id
    having avg(salary) >(select avg(salary) from employees )
)
案例7：查询公司中manager的详细信息
select *
from employees e
where `employee_id` in (
    select distinct manager_id from employees
)
案例8：各部门中，最高工资中最低的那个部门的 最低工资

各部门中，最高工资中最低的那个部门

select department_id 
from employees 
group by department_id
order by max(salary)
limit 1

该部门的最低工资

select min(salary),department_id
from employees
where department_id = (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id
    ORDER BY MAX(salary)
    LIMIT 1 

)
案例9：查询平均工资最高的部门manager的详细信息

平均工资最高的部门

SELECT department_id FROM employees
GROUP BY department_id 
ORDER BY AVG(salary) DESC LIMIT 1

部门的领导编号

select `manager_id` from`departments`
where `department_id` =(
    SELECT department_id FROM employees
    GROUP BY department_id 
    ORDER BY AVG(salary) DESC LIMIT 1

)

领导的详细信息

select *
from employees
where employee_id = (

    SELECT `manager_id` FROM`departments`
    WHERE `department_id` =(
        SELECT department_id FROM employees   
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC LIMIT 1

        )   
)

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