（MySQL学习笔记）子查询

补发一下之前的笔记

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


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

#一、where或having后面

#1、标量子查询（单行子查询）
#2、列子查询（多行子查询）
#3、行子查询（多行多列）
/*
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询一般搭配着单行操作符使用
> < >= <= = <>
列子查询一般搭配着多行操作符使用
in,any/some,all

④子查询的执行优先于主查询，主查询的条件用到了子查询的结果
*/

#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');
		
		
#例：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_if和工资
SELECT last_name,job_id,salary
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143)
AND job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141);
	
	
#例：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees);
	
#例：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50
	GROUP BY department_id);



#非法使用标量子查询的情况




#2、列子查询（多行子查询，一列多行）
#使用多行操作符
#in/not in,any|some,all

#例：返回location_id是1400或1700的部门中的所有员工信息
SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id IN (1400,1700));
	
#例：返回其他部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id<>'IT_PROG'
AND salary<ANY(
	SELECT salary
	FROM employees
	WHERE job_id='IT_PROG');
	
#3、行子查询（结果集一行多列或多行多列）
#例：查询员工编号最小并且工资最高的员工信息
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后面
/*
支持标量子查询
*/
#例：查询每个部门的部门信息和员工个数
SELECT d.*,COUNT(*)
FROM employees e
LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`;

SELECT d.*,(	SELECT COUNT(*)
		FROM employees e
		WHERE e.department_id = d.`department_id`)
FROM departments d;

#例：查询员工编号为102的部门名
SELECT department_name
FROM departments d
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;

*/


#四：exists后面（相关子查询）
/*
语法：
exists(完整的查询语句)

结果： 
1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees);

#例：查询员工名和部门名
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`;

#例：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE e.`department_id` = d.`department_id`);
	
#例：查询没有女朋友的男生姓名
SELECT bo.`boyName`
FROM boys bo
WHERE NOT EXISTS(
	SELECT *
	FROM beauty be
	WHERE be.`boyfriend_id` = bo.`id`);
	
	
#例：查询和zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees e
WHERE e.`department_id` = (
	SELECT department_id
	FROM employees e
	WHERE e.`last_name`='zlotkey');

#例：
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
	SELECT AVG(salary)
	FROM employees
);

#例：
SELECT employee_id,last_name,salary
FROM employees e
LEFT JOIN (
	SELECT AVG(salary) a,department_id
	FROM employees
	GROUP BY department_id
	) ag
ON e.department_id = ag.department_id
WHERE e.salary > ag.a;

#例：
SELECT employee_id,last_name
FROM employees
JOIN (	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
	) id
ON employees.`department_id` = id.department_id
ORDER BY employee_id;

SELECT employee_id,last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%'
	)
ORDER BY employee_id;

#例：
SELECT employee_id
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id=1700
	);
	
#例：
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees
	WHERE last_name='K_ing');
	
#例：
SELECT CONCAT(first_name,'.',last_name)
FROM employees
WHERE salary = (
	SELECT MAX(salary)
	FROM employees);