DQL 8_子查询

8_子查询

/*
含义：

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：

按子查询出现的位置：
select后面：
	仅仅支持标量子查询
from后面：
	支持表子查询
where或having后面：
	标量子查询
	列子查询
	行子查询
exists后面(相关子查询)
	表子查询

按结果集的行列数不同：

标量子查询(结果集只有一行一列)
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

*/

1.where或having后面

/*

1.标量子查询(单行子查询)
2.列子查询(多行子查询)
3.行子查询(多列多行)

特点：

1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询，一般搭配着单行操作符使用  > < >= <= = <>
4.列子查询，一般搭配着多行操作符使用 in、any/some、all
5.子查询的执行优先于主查询的执行，主查询的条件用到了子查询的结果

*/

1.标量子查询

案例1：谁的工资比Abel高？

#1.查询Abel工资
SELECT salary
FROM employees
WHERE LAST_NAME='Abel'
#2.查询员工的信息，满足salary>1的结果
SELECT *
FROM EMPLOYEES
WHERE SALARY >(
	SELECT salary
	FROM employees
	WHERE LAST_NAME='Abel'
);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工，姓名。job_ID 工资

#1.查询141号员工的job_ID
SELECT JOB_ID
FROM employees 
WHERE EMPLOYEE_ID =141
#2.查询143号员工的salary
SELECT SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID=143
#3.查询job_ID与141相同 ，salary比143多的 姓名 jobid 工资
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

#1.查询公司的最低工资
SELECT MIN(SALARY)
FROM EMPLOYEES
#2.查询last——name，job——id和salary，要求salary=1
SELECT LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE SALARY =(
	SELECT MIN(SALARY)
	FROM EMPLOYEES
);

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

#1.查询50号部门的最低工资
SELECT MIN(SALARY)
FROM employees
WHERE DEPARTMENT_ID=50;
#2.查询所有部门的最低工资
SELECT MIN(salary),DEPARTMENT_ID
FROM employees
GROUP BY DEPARTMENT_ID;
#3.在2的基础上筛选，满足min(SALARY)>1
SELECT MIN(salary),DEPARTMENT_ID
FROM employees
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY)>(
	SELECT MIN(SALARY)
	FROM employees
	WHERE DEPARTMENT_ID=50
);

2.列子查询(多行子查询)

/*

in/not in 等于列表中 任意一个
any|SOME 和子查询返回的 某一个值 比较
all 和子查询返回的 所有值 比较

*/

案例5:返回location——id是1400或1700的部门中所有员工姓名

#1.查询location-id是1400或1700的部门编号
SELECT DISTINCT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID IN(1400,1700)
#2.查询所有员工姓名，要求部门号是1的列表中一个
SELECT LAST_NAME 
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN( #如果是 not in 可以 替换为 <> ALL
	SELECT DISTINCT DEPARTMENT_ID
	FROM DEPARTMENTS
	WHERE LOCATION_ID IN(1400,1700)
);
#或
SELECT LAST_NAME 
FROM EMPLOYEES
WHERE DEPARTMENT_ID =ANY(
	SELECT DISTINCT DEPARTMENT_ID
	FROM DEPARTMENTS
	WHERE LOCATION_ID IN(1400,1700)
);

案例6:返回其他工种中比job-id为’it-prog‘工种任一工资低的员工的员工号，姓名，job-id，以及salary

#1.查询job——id为’IT_PROG‘部门任意工资
SELECT DISTINCT SALARY
FROM EMPLOYEES
WHERE JOB_ID='IT_PROG'
#2.查询员工号，姓名，job-id，以及salary，salary<(1)中任意一个
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';

#或

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';

案例7:返回其他工种中比job-id为’it-prog‘工种中 所有 工资低的员工的员工号，姓名，job-id，以及salary

SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE salary < ALL(
	SELECT DISTINCT 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';

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

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

SELECT *
FROM employees
WHERE (EMPLOYEE_ID,SALARY)=(
		SELECT MIN(EMPLOYEE_ID),MAX(salary)
		FROM employees
);
#等价于
#1.查询最小的员工编号
SELECT MIN(EMPLOYEE_ID)
FROM employees
#2.查询最高工资
SELECT MAX(SALARY)
FROM EMPLOYESS;
#3.查询员工信息
SELECT *
FROM employees
WHERE EMPLOYEE_ID =(
	SELECT MIN(EMPLOYEE_ID)
	FROM employees
)AND salary =(
	SELECT MAX(SALARY)
	FROM EMPLOYEES
);

2.SELECT后面

案例9:每个部门的员工个数

SELECT D.*,(
	SELECT COUNT(*)
	FROM EMPLOYEES AS E
	WHERE E.DEPARTMENT_ID=D.`department_id`
) AS 个数
FROM departments AS D;

案例10:查询员工号=102的部门名

SELECT (
	SELECT DEPARTMENT_NAME
	FROM departments AS D
	INNER JOIN employees AS E
	ON D.DEPARTMENT_ID=E.DEPARTMENT_ID
	WHERE E.EMPLOYEE_ID = 102
) AS 部门名;

3.from后面

/*

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

*/

案例11: 查询每个部门的平均工资的工资等级

#1.查询每个部门的平均工资
SELECT AVG(SALARY),DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
#2.连接1的结果集和job——grades表，筛选条件为 平均工资 between lowest and highest
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;

4.exists后面（相关子查询）

/*
语法：

exists（完整的查询语句）
结果：1或0  1代表括号里的查询存在，0代表括号里的查询不存在

*/

SELECT EXISTS(SELECT EMPLOYEE_ID FROM employees) #返回1
SELECT EXISTS(SELECT EMPLOYEE_ID FROM employees WHERE salary = 3000000)#返回0

案例13:查询有员工的部门名

SELECT DEPARTMENT_NAME
FROM departments AS D
WHERE EXISTS(
	SELECT *
	FROM EMPLOYEES AS E
	WHERE D.DEPARTMENT_ID =E.DEPARTMENT_ID
);

案例14:查询有员工的部门名 用in

SELECT DEPARTMENT_NAME
FROM departments AS D
WHERE D.`department_id` IN(
	SELECT DEPARTMENT_ID
	FROM EMPLOYEES

);

案例15：查询没有女朋友的男神信息 NOT IN

SELECT BO.*
FROM BOYS AS BO
WHERE BO.`id` NOT IN(
	SELECT boyfriend_id 
	FROM beauty
);

案例16：查询没有女朋友的男神信息 EXISTS

SELECT BO.*
FROM BOYS AS BO
WHERE  NOT EXISTS (
	SELECT BE.BOYFRIEND_ID
	FROM beauty AS BE
	WHERE BO.`id`=BE.BOYFRIEND_ID
);

5.练习

案例17：查询和 Zlotkey 相同部门的员工姓名和工资

SELECT LAST_NAME,SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
	SELECT DEPARTMENT_ID
	FROM EMPLOYEES
	WHERE LAST_NAME ='Zlotkey'
	
);

案例18：查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM employees
WHERE SALARY >(
	SELECT AVG(SALARY)
	FROM employees
	
);

案例19：查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资

SELECT EMPLOYEE_ID,LAST_NAME,salary,E1.`department_id`
FROM employees AS E1 
WHERE salary >(
	SELECT AVG(salary)
	FROM employees AS E2
	WHERE E1.`department_id`=E2.`department_id`
	GROUP BY department_id
);
#或
#1.查询各部门平均工资
SELECT AVG(salary),department_id
FROM EMPLOYEES
GROUP BY department_id
#2.连接1结果集和employees表，进行筛选
SELECT EMPLOYEE_ID,LAST_NAME,salary,E.`department_id`
FROM EMPLOYEES E
INNER JOIN (
	SELECT AVG(salary) AS AVG1,department_id
	FROM EMPLOYEES
	GROUP BY department_id
	
)AS AG_DEP
ON AG_DEP.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE SALARY>AG_DEP.AVG1;

案例20：查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名

SELECT EMPLOYEE_ID,LAST_NAME
FROM EMPLOYEES AS EM
WHERE EM.DEPARTMENT_ID IN (
	SELECT DISTINCT E.department_ID
	FROM employees AS E
	WHERE E.LAST_NAME LIKE '%u%'
);		

案例21：查询在部门的 location_id 为 1700 的部门工作的员工的员工号

SELECT employee_id
FROM employees AS E
INNER JOIN departments AS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE D.LOCATION_ID=1700
#或
#1.查询location-id为1700的部门
SELECT department_id
FROM DEPARTMENTS
WHERE location_id =1700
#2.查询部门号=1中任意一个员工号
SELECT employee_id
FROM EMPLOYEES
WHERE department_id=ANY(
	SELECT DISTINCT department_id
	FROM DEPARTMENTS
	WHERE location_id =1700
)

案例22：查询管理者是 King 的员工姓名和工资

SELECT E.LAST_NAME,E.salary
FROM EMPLOYEES E
WHERE manager_iD IN (
	SELECT EMPLOYEE_ID
	FROM EMPLOYEES AS D
	WHERE D.LAST_NAME='K_ing' 
);

案例23：查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名

SELECT CONCAT(FIRST_NAME,LAST_NAME)AS "姓.名"
FROM employees 
WHERE salary=(
	SELECT MAX(SALARY)
	FROM employees 
);