子查询select，from，exists

子查询select，from，exists

select

#
#案例 查询每个部门的员工个数
SELECT d.department_id,
	(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.`department_id`
	) 个数
	
FROM departments d;

# 案例 查询员工号=102的部门名
SELECT department_name,employee_id
FROM departments d
JOIN employees e
ON e.`department_id`=d.`department_id`
WHERE employee_id=102;

SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON e.`department_id`=d.`department_id`
	WHERE e.employee_id=102
) 部门名;
# select 后的子查询仅仅支持标量子查询，不支持多行子查询

from 后

#from后面子查询
#from后面跟的是 表，所以 from后面的子查询结果当做一个表
# 案例： 查询每个部门的平均工资的工资等级
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;


SELECT j.`grade_level`,ag.*
FROM (SELECT AVG(salary) 工资,department_id
	FROM employees
	GROUP BY department_id) ag 
JOIN job_grades j
ON ag.工资 BETWEEN`lowest_sal`AND`highest_sal`;
#要求：from 子查询结果，当做一个表来使用，必须起别名

exists 后

#放在exists后面（相关子查询） EXISTS (是否存在)
/*
 语法
 exists （完整的查询语句）
 结果：  0或1
 类似java Boolean类型

*/

SELECT EXISTS (SELECT employee_id FROM employees);

#案例  查询有员工的部门名

SELECT department_name
FROM departments d
WHERE EXISTS 
	(SELECT employee_id
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
	);

SELECT department_name
FROM departments d
JOIN employees e
ON e.`department_id`=d.`department_id`
GROUP BY department_name;

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
SELECT bo.*
 FROM boys bo
 WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.id=b.`boyfriend_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 employee_id,last_name,salary,e.department_id
FROM employees e
JOIN (SELECT AVG(salary) g , department_id
	FROM employees
	GROUP BY department_id
	) av
ON e.`department_id`=av.department_id
WHERE salary > av.g;

#4.耷询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT  DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
GROUP BY department_id;

SELECT employee_id,last_name
FROM employees e
JOIN (SELECT  DISTINCT department_id
	FROM employees
	WHERE last_name LIKE '%u%') dep
ON e.`department_id`=dep.department_id;
#-----------
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的部门工作的员工的员工号
SELECT department_id
FROM  departments
WHERE location_id = 1700;
#-----------------
SELECT employee_id,e.department_id
FROM employees e
JOIN (SELECT department_id
	FROM  departments
	WHERE location_id = 1700) dep
ON e.`department_id`=dep.department_id;
#--------------
SELECT employee_id,e.department_id
FROM employees e
WHERE department_id IN (SELECT department_id
	FROM  departments
	WHERE location_id = 1700) ;
#----------
SELECT employee_id,e.department_id
FROM employees e
WHERE department_id =ANY (SELECT department_id
	FROM  departments
	WHERE location_id = 1700) ;	
	
#6.查询管理者是King的员工姓名和工资
 
SELECT last_name,employee_id
FROM employees
WHERE last_name = 'k_ing';

SELECT  last_name,salary
FROM employees
WHERE manager_id IN 
	(SELECT employee_id
	FROM employees
	WHERE last_name = 'k_ing');
	
#7. 查询工资最高的员 工的姓名，要求first_ name和last_ name显示为一列，列名为姓.名
SELECT  CONCAT(first_name,last_name) '姓.名'
FROM employees
WHERE salary=
	(SELECT MAX(salary)
	FROM employees);