#進階7：子查詢
/*
含義：
出現(xiàn)在其他語句中的select語句，稱為子查詢或內(nèi)查詢
外部的查詢語句，稱為主查詢或外查詢

分類：
按子查詢出現(xiàn)的位置：
?? ?select后面：
?? ??? ?僅僅支持標量子查詢
?? ?
?? ?from后面：
?? ??? ?支持表子查詢
?? ?where或having后面：★
?? ??? ?標量子查詢（單行） √
?? ??? ?列子查詢 ?（多行） √
?? ??? ?
?? ??? ?行子查詢
?? ??? ?
?? ?exists后面（相關子查詢）
?? ??? ?表子查詢
按結果集的行列數(shù)不同：
?? ?標量子查詢（結果集只有一行一列）
?? ?列子查詢（結果集只有一列多行）
?? ?行子查詢（結果集有一行多列）
?? ?表子查詢（結果集一般為多行多列）

*/

#一、where或having后面
/*
1、標量子查詢（單行子查詢）
2、列子查詢（多行子查詢）

3、行子查詢（多列多行）

特點：
①子查詢放在小括號內(nèi)
②子查詢一般放在條件的右側
③標量子查詢，一般搭配著單行操作符使用
> < >= <= = <>

列子查詢，一般搭配著多行操作符使用
in、any/some、all

④子查詢的執(zhí)行優(yōu)先于主查詢執(zhí)行，主查詢的條件用到了子查詢的結果

*/
#1.標量子查詢

#案例1：誰的工資比 Abel 高?

#①查詢Abel的工資
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查詢員工的信息，滿足 salary>①結果
SELECT *
FROM employees
WHERE salary>(

?? ?SELECT salary
?? ?FROM employees
?? ?WHERE last_name = 'Abel'

);

#案例2：返回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

);

#案例3：返回公司工資最少的員工的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
);

#案例4：查詢最低工資大于50號部門最低工資的部門id和其最低工資

#①查詢50號部門的最低工資
SELECT ?MIN(salary)
FROM employees
WHERE department_id = 50

#②查詢每個部門的最低工資

SELECT MIN(salary),department_id
FROM employees
GROUP BY 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

);

#非法使用標量子查詢-----即子查詢結果必須是一行一列

SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
?? ?SELECT ?salary
?? ?FROM employees
?? ?WHERE department_id = 250

);

#2.列子查詢（多行子查詢）★
#案例1：返回location_id是1400或1700的部門中的所有員工姓名

#①查詢location_id是1400或1700的部門編號
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查詢員工姓名，要求部門號是①列表中的某一個

SELECT last_name
FROM employees
WHERE department_id ?<>ALL(
?? ?SELECT DISTINCT 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，salary<(①)的任意一個
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
?? ?SELECT DISTINCT salary
?? ?FROM employees
?? ?WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
?? ?SELECT MAX(salary)
?? ?FROM employees
?? ?WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#案例3：返回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工 ? 的員工號、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
?? ?SELECT DISTINCT salary
?? ?FROM employees
?? ?WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#或

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
?? ?SELECT MIN( salary)
?? ?FROM employees
?? ?WHERE job_id = 'IT_PROG'

) AND job_id<>'IT_PROG';

#3、行子查詢（結果集一行多列或多行多列）

#案例：查詢員工編號最小并且工資最高的員工信息

#行子查詢
SELECT *?
FROM employees
WHERE (employee_id,salary)=(
?? ?SELECT MIN(employee_id),MAX(salary)
?? ?FROM employees
);

#普通解法
#①查詢最小的員工編號
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后面
/*
僅僅支持標量子查詢-----即子查詢結果只能是一行一列
*/

#案例：查詢每個部門的員工個數(shù)

SELECT d.*,(

?? ?SELECT COUNT(*)
?? ?FROM employees e
?? ?WHERE e.department_id = d.`department_id`
?) 個數(shù)
?FROM departments d;
?
?
?#案例2：查詢員工號=102的部門名
?
SELECT (
?? ?SELECT department_name,e.department_id
?? ?FROM departments d
?? ?INNER JOIN employees e
?? ?ON d.department_id=e.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,department_id
?? ?FROM employees
?? ?GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#四、exists后面（相關子查詢）

/*
語法：
exists(完整的查詢語句)
結果：
1或0

*/

#主要用于子語句的結果是否存在
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

#案例1：查詢有員工的部門名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
?? ?SELECT department_id
?? ?FROM employees

);

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
?? ?SELECT *
?? ?FROM employees e
?? ?WHERE d.`department_id`=e.`department_id`
);

#案例2：查詢沒有女朋友的男神信息

#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
?? ?SELECT boyfriend_id
?? ?FROM beauty
)

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

);

#---------子查詢相關案例-----------#

#1.查詢和Zlotkey相同部門的員工姓名和工資

#(1)查詢和Zlotkey相同部門id
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey';

#(2)查詢員工姓名和工資
SELECT `last_name`,`salary`
FROM employees
WHERE department_id=(
?? ?SELECT department_id
?? ?FROM employees
?? ?WHERE last_name = 'Zlotkey'
);

#2.查詢工資比公司平均工資高的員工的員工號，姓名和工資。

#(1)查詢公司平均工資
SELECT AVG(salary)
FROM employees;

#(2)查詢工資比公司平均工資高的員工的員工號，姓名和工資
SELECT `employee_id`,`last_name`,`salary`
FROM employees
WHERE `salary`>(
?? ?SELECT AVG(salary)
?? ?FROM employees
);

#3.查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資

#(1)查詢各部門的平均工資
SELECT AVG(salary),`department_id`
FROM `employees`
GROUP BY `department_id`;

#(2)查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資
SELECT e.`employee_id`,e.`last_name`,e.`salary`
FROM `employees` e
JOIN (
?? ?SELECT AVG(salary) ag,`department_id`
?? ?FROM `employees`
?? ?GROUP BY `department_id`
) n
ON e.`department_id`=n.`department_id`
WHERE e.`salary`>n.ag;

#4.查詢和姓名中包含字母u的員工在相同部門的員工的員工號和姓名

#(1)查詢姓名中包含字母u的員工的部門id
SELECT DISTINCT `department_id`
FROM `employees`
WHERE last_name LIKE '%u%';

#(2)查詢和(1)相同部門的員工的員工號和姓名
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的部門工作的員工的員工號

#(1)查詢location_id為1700的部門id
SELECT `department_id`
FROM `departments`
WHERE `location_id`=1700;

#(2)查詢在部門的location_id為1700的部門工作的員工的員工號
SELECT `employee_id`
FROM `employees`
WHERE `department_id` IN (
?? ?SELECT `department_id`
?? ?FROM `departments`
?? ?WHERE `location_id`=1700
);

#6.查詢管理者是K_ing的員工姓名和工資

#(1)查詢K_ing的id
SELECT `employee_id`
FROM `employees`
WHERE `last_name`='K_ing';

#(2)查詢管理者是K_ing的員工姓名和工資
SELECT m.`last_name`,m.`salary`
FROM `employees` m
WHERE m.`manager_id` IN (
?? ?SELECT `employee_id`
?? ?FROM `employees`
?? ?WHERE `last_name`='K_ing'
);

#7.查詢工資最高的員工的姓名，要求first_name和last_name顯示為一列，列名為 姓.名

#①查詢最高工資
SELECT MAX(salary)
FROM employees

#②查詢工資=①的姓.名

SELECT CONCAT(first_name,last_name) "姓.名"
FROM employees
WHERE salary=(
?? ?SELECT MAX(salary)
?? ?FROM employees

);

#------子查詢總結------#
一、含義
嵌套在其他語句內(nèi)部的SELECT語句稱為子查詢或內(nèi)查詢，
外面的語句可以是INSERT、UPDATE、DELETE、SELECT等，一般SELECT作為外面語句較多
外面如果為SELECT語句，則此語句稱為外查詢或主查詢

二、分類
1、按出現(xiàn)位置
SELECT后面：
?? ??? ?僅僅支持標量子查詢
FROM后面：
?? ??? ?表子查詢
WHERE或HAVING后面：
?? ??? ?標量子查詢
?? ??? ?列子查詢
?? ??? ?行子查詢
EXISTS后面：
?? ??? ?標量子查詢
?? ??? ?列子查詢
?? ??? ?行子查詢
?? ??? ?表子查詢

2、按結果集的行列
標量子查詢（單行子查詢）：結果集為一行一列
列子查詢（多行子查詢）：結果集為多行一列
行子查詢：結果集為多行多列
表子查詢：結果集為多行多列

三、示例
WHERE或HAVING后面
1、標量子查詢
案例：查詢最低工資的員工姓名和工資
①最低工資
SELECT MIN(salary) FROM employees

②查詢員工的姓名和工資，要求工資=①
SELECT last_name,salary
FROM employees
WHERE salary=(
?? ?SELECT MIN(salary) FROM employees
);

2、列子查詢
案例：查詢所有是領導的員工姓名
①查詢所有員工的 manager_id
SELECT manager_id
FROM employees

②查詢姓名，employee_id屬于①列表的一個
SELECT last_name
FROM employees
WHERE employee_id IN(
?? ?SELECT manager_id
?? ?FROM employees
);