MySQL学习记录（七）--子查询

#进阶七：子查询
/*
含义：出现在其他语句中的SELECT语句，称为子查询或者内查询
	外部的查询语句，称为主查询或者外查询
分类：
	按子查询出现的位置：
		select 后面
			--仅支持标量子查询
		from 后面
			--仅支持表子查询
		where后面或having后面（※）
			--标量子查询（※）
			--列子查询（※）
			--行子查询
		exists后面（相关子查询）
			--表子查询
	按结果集的行列数：
		标量子查询（结果只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集有一行多列）
		表子查询（结果为多行多列）

*/

#一、where后面或having后面
/*
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多行多列）

特点:
	1.子查询放在小括号内
	2.子查询一般放在条件右侧
	3.标量子查询一般搭配单行操作符--<> = >= <=
	4.列子查询 一般搭配多行操作符使用--in any all
	5.子查询执行优先于主查询
*/

#1.标量子查询

#案例1：谁的工资比Able高？
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 `department_id`,MIN(`salary`)
 FROM `employees`
 GROUP BY `department_id`
 HAVING MIN(`salary`)> (
		SELECT MIN(`salary`)
		FROM `employees`
		WHERE `department_id` = 50
 )
 
 #非法使用标量子查询
 
 
 #2.列子查询
 # in any all
 
 #案例1：返回`location_id` 是1400或1700的部门中所有员工的姓名
 SELECT `last_name`,`location_id`
 FROM `employees`
 JOIN `departments`
 ON `employees`.`department_id`=`departments`.`department_id`
 WHERE `location_id`IN (1400,1700)
 
 #案例2：返回其他部门中比job_id 为'IT_PROG'部门任一工资低的`employee_id`、`last_name`，`salary`
 SELECT `salary`,`job_id`,`last_name`,`employee_id`
 FROM `employees`
 WHERE `salary` < ANY (
		 SELECT `salary`
		 FROM `employees`
		 WHERE `job_id` = 'IT_PROG'
)
 
 #3.行子查询（结果集一行多列）
 
 #案例：查询员工编号最小并且工资最高的员工信息
 SELECT *
 FROM `employees`
 WHERE (`employee_id`,`salary`)  = (
		SELECT MIN (`employee_id`),MAX(`salary`)
		FROM `employees`
 
 )
 
 #二、select后面
 
 #案例：查询每个部门的员工个数
 SELECT COUNT(*)
 FROM `employees`
 GROUP BY `department_id`
 
 SELECT d.*,(
	SELECT COUNT(*)
	FROM `employees` e 
	WHERE e.`department_id` = d.`department_id`
 )
 FROM `departments` d ;
 
 #案例2：查询员工号=102的部门名
  SELECT `department_name`,`employee_id`
  FROM `employees` e 
  JOIN `departments` d
  ON e.`department_id` = d.`department_id`
  WHERE `employee_id` = '102'
  
  #三、from后面(子查询结果充当表，必须起别名)
  
  #案例：查询每个部门的平均工资等级
  SELECT `grade_level`,department_id
  FROM (
  SELECT AVG(`salary`) a,department_id
  FROM `employees`
  GROUP BY `department_id`) ag
  JOIN `job_grades` g
  ON ag.a BETWEEN `lowest_sal` AND `highest_sal`
  
  #四、exists 后面（相关子查询）
  
  /*
  语法：
  exists（完整的查询语句）
  结果：0或1；布尔值
  
  */
  SELECT EXISTS --查询结果是否有值
  SELECT EXISTS(SELECT `employee_id` FROM `employees`)
  
  #案例1：查询有员工的部门名
 
 SELECT `department_name`
 FROM `departments` d
 WHERE EXISTS(
		SELECT * 
		FROM `employees` e 
		WHERE d.`department_id` = e.`department_id`
 )
 
 #练习
 SELECT `last_name`,`salary` FROM `employees`
 WHERE `department_id` = (
	SELECT `department_id`
	FROM `employees`
	WHERE `last_name` = 'Zlotkey'
 )
 #1
 SELECT `employee_id`,`last_name`,`salary`
 FROM `employees`
 WHERE `salary`>(SELECT AVG(`salary`)FROM `employees`)
 #2
 
 SELECT`last_name`,`employee_id`,`salary`
 FROM `employees`
 JOIN(
 SELECT AVG(`salary`) a,`department_id`
 FROM `employees`
 GROUP BY `department_id`) ag
 ON `employees`.`department_id` =ag.`department_id`
 WHERE `employees`.`salary`>ag.a
 #3
 SELECT`employee_id`FROM `employees`
 WHERE `department_id` IN (
	SELECT DISTINCT `department_id` FROM `employees`
	WHERE `last_name` LIKE '%u%')
 #where `last_name` != 
 #(SELECT `last_name` FROM `employees`
# WHERE `last_name` LIKE '%u%')

SELECT `employee_id` 
FROM `employees` e
JOIN `departments` d
ON e.`department_id` = d.`department_id`
WHERE `location_id` = '1700'
#4
SELECT `last_name`,`salary`
FROM `employees` 
WHERE `manager_id` IN(
SELECT `employee_id` 
FROM `employees`
WHERE `last_name` = 'K_ing')
#5
SELECT CONCAT(`first_name`,`last_name`)  姓，名
FROM `employees`
WHERE `salary` = MAX(`salary`)