mysql 子查询

/*
进阶7：子查询

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


分类：
按子查询出现的位置：
	select后面；
		仅仅支持标量子查询
	from后面：
		支持表子查询
	where或having后面：★
		标量子查询（单行）★
		列子查询★
		
		行子查询
	
	exists后面 （相关子查询）
		表子查询

按结果集的行列数不同：
	标量子查询（结果集只有一行一列）
	列子查询（结果集只有一列多行）
	行子查询（结果集只有一行多列
	表子查询（结果集一般为多行多列）
	
*/
#一、where或having后面
/*
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多行多列）

特点：
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') ;

#案例2:题目:返回job_ id与141号 员工相同，salary比143号员工多的员工姓名，job_ _id和工资
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 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;
    
 #案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
 SELECT MIN(salary)
 FROM employees
 WHERE department_id = 50;
 #-----------------------------------------------------------
 #有分组查询之后条件放在having后面
 SELECT department_id,MIN(salary)
 FROM employees  
 GROUP BY department_id
 HAVING MIN(salary) > (
 
	SELECT MIN(salary)
	 FROM employees
	 WHERE department_id = 50
 
 );

二、多行子查询

#二）列子查询（多行列子查询）
 #案例1:返location id是1400或1700的部门中的所有员工姓名
 SELECT department_id 
 FROM departments
 WHERE location_id
 IN (1400,1700);
 #---
 SELECT last_name 
 FROM employees
 WHERE department_id
 IN (
 
 SELECT department_id 
 FROM departments
 WHERE location_id
 IN (1400,1700)
 
 );
 
#案例2:返回其它部门中比job_ id为IT_ _PROG' 部门任一工资低的员工的:工号、姓名、job_ id以及salary
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ANY 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';
  
#案例3:返回其它部门中比job_ _id为'IT_ PROG' 部门所有
#工资都低的员工的员工号、 姓名、job_ id以及salary  
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ALL 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';  
  
  #查询员工最低编号，最高工资的员工
SELECT * 
FROM employees
WHERE employee_id = (

	SELECT MIN(employee_id) 
	FROM employees 

)  AND salary = (

	SELECT MAX(salary) 
	FROM employees

);

#二、select后面(只有一列，仅仅只支持标量子查询)
#案例:查询每个部门的员工个数
/*
SELECT COUNT(*),department_id 
FROM employees
GROUP BY department_id;
*/

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

#案例2:查询员工号=102的部门名
/*
SELECT d.`department_name`,(

	SELECT job_id 
	FROM employees e
	WHERE d.`department_id` = e.`department_id` AND job_id = 102

)
FROM departments d;
*/

SELECT (

	SELECT d.`department_name`
	FROM departments d
	INNER JOIN `employees` e
	ON d.`department_id` = e.`department_id`
	WHERE e.`employee_id` = 102

);

#三、from后面
#将子查询结果充当一张表，要求必须起别名
#案例:查询每个部门的平均工资的工资等级
SELECT department_id,AVG(salary) AS ag
FROM employees
GROUP BY department_id;

SELECT ag_grades.*,j.`grade_level`
FROM  (

	SELECT department_id,AVG(salary) AS ag
	FROM employees
	GROUP BY department_id

) ag_grades
INNER JOIN `job_grades` j
ON ag_grades.ag BETWEEN j.`lowest_sal` AND j.`highest_sal`;
  
  
#四、exists后面（相关子查询）

/*
语法：
exists（完整的查询语句）
结果是：
1或0
*/ 
 
SELECT EXISTS(SELECT * FROM employees WHERE salary = 300000);
 
#案例一、查询有员工的部门名
SELECT `department_name`
FROM `departments` d
WHERE EXISTS (
	SELECT * 
	FROM employees e
	WHERE d.`department_id` = e.`department_id`
);
 
 
#用in
SELECT 
  `department_name` 
FROM
  `departments` d 
WHERE d.`department_id` IN 
  (SELECT 
    `department_id` 
  FROM
    employees);
    
    
#1.查询和Zlotkey部门相同的员工的姓名和工资
SELECT `department_id` 
FROM `employees`
WHERE `last_name` = 'Zlotkey';

SELECT `last_name`,`salary`,department_id
FROM employees
WHERE department_id IN (

	SELECT `department_id` 
	FROM `employees`
	WHERE `last_name` = 'Zlotkey'

);


#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT AVG(salary)
FROM employees;

SELECT `employee_id`,`last_name`,`salary`
FROM `employees`
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

#3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资;
SELECT AVG(salary) ,`department_id`
FROM `employees`
GROUP BY `department_id`;

SELECT `employee_id`,`last_name`,`salary`
FROM `employees` e
INNER JOIN (
	SELECT AVG(salary) 平均 ,`department_id`
	FROM `employees`
	GROUP BY `department_id`
) avg_sa
ON e.`department_id` = avg_sa.`department_id`
WHERE e.salary > avg_sa.平均
;


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


SELECT `last_name`,`employee_id`
FROM employees e
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 
FROM `employees`
INNER JOIN (
	SELECT `department_id` 
	FROM departments 
	WHERE location_id = 1700
) de
ON employees.`department_id` = de.`department_id`;
 
6.查询管理者是King的员工姓名和工资
SELECT `department_id` 
FROM employees 
WHERE last_name = 'K_ing';

SELECT last_name,salary
FROM employees e
INNER JOIN (
SELECT `employee_id` 
FROM employees 
WHERE last_name = 'K_ing'
) es
ON e.`manager_id` = es.`employee_id`
;
#7.查询工资最高的员工的姓名，要求first_ _name和1ast_ name显示为一列，列名为姓.名
SELECT MAX(salary)
FROM employees;

SELECT CONCAT(first_name,'.',last_name) `姓.名`
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);