SQL---子查询

#子查询

说明：当一个查询语句中又嵌套了另一个完整的select语句，
则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。

分类：
按子查询出现的位置进行分类：

1、select后面
	要求：子查询的结果为单行单列（标量子查询）
2、from后面
	要求：子查询的结果可以为多行多列
3、where或having后面
	要求：子查询的结果必须为单列
		单行子查询
		多行子查询
4、exists后面
	要求：子查询结果必须为单列（相关子查询）
特点：	
	1、子查询放在条件中，要求必须放在条件的右侧
	2、子查询一般放在小括号中
	3、子查询的执行优先于主查询
	4、单行子查询对应了 单行操作符：> < >= <= = <>
	   多行子查询对应了 多行操作符：any/some all in

一、单行子查询

#查询和Zlotkey相同部门的员工姓名和工资

#①查询Zlotkey的部门编号
SELECT `department_id`
FROM `employees`
WHERE `last_name`='Zlotkey';

#②查询department_id=①的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
	SELECT `department_id`
	FROM `employees`
	WHERE `last_name`='Zlotkey'
);

#查询工资比公司平均工资高的员工的员工号，姓名和工资

#①查询平均工资
SELECT AVG(salary)
FROM employees

#查询salary>①的信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
);

#二、多行子查询

in:判断某字段是否在指定列表内
x in(10,30,50)

any/some:判断某字段的值是否满足其中任意一个

x>any(10,30,50)
x>min()

x=any(10,30,50)
x in(10,30,50)

all:判断某字段的值是否满足里面所有的

x>all(10,30,50)
x>max()


#返回location_id是1400或1700的部门中的所有员工姓名

①查询location_id是1400或1700的部门
SELECT `department_id`
FROM `departments`
WHERE `location_id` IN(1400,1700)

②查询deparment_id=①的姓名
SELECT last_name
FROM `employees`
WHERE department_id IN(
	SELECT DISTINCT `department_id`
	FROM `departments`
	WHERE `location_id` IN(1400,1700)
);

#返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id以及salary

#①查询job_id为'IT_PROG'部门的工资
SELECT DISTINCT salary
FROM `employees`
WHERE job_id='IT_PROG';

#②查询其他部门的工资<任意一个①的结果
SELECT `employee_id`,`last_name`,`job_id`,salary
FROM `employees`
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM `employees`
	WHERE job_id='IT_PROG'
);

等价于

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

放在其他位置的子查询

#一、放在select后面

#查询部门编号是50的员工个数

SELECT (
	SELECT COUNT(*)
	FROM `employees`
	WHERE `department_id`=50
) 个数;

#三、放在exists后面

#查询有无名字叫"张三丰"的员工信息
SELECT EXISTS(
	SELECT *
	FROM `employees`
	WHERE last_name='Abel'
)有无Abel;

#查询没有女朋友的男神信息

USE girls

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

);


#二、放在from后面

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

#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM `employees`
GROUP BY `department_id`


#②将①和sal_grade两表连接查询

SELECT dep_ag.department_id,dep_ag.ag
FROM sal_grade g 
JOIN(
	
	SELECT AVG(salary) ag,`department_id`
	FROM `employees`
	GROUP BY `department_id
)dep_ag on dep_ag.ag BETWEEN g.min_salary AND g.max_salary;