SQL学习笔记整理7（子查询）

#子查询
/*
出现在其他语句里的select语句，称为子查询或内查询
外部的查询语句成为外查询或内查询

分类：按查子查询出现的位置
             select后面
                  标量子查询
             from后面
                  表子查询
             where或having 后面
                  标量子查询
                  行子查询
                  列子查询
             exists后面
                  表子查询
       按结果集的行列数不同
             标量子查询（一行一列）
             列子查询（一列多行）
             行子查询（一行队列）
             表子查询
*/

#一、where和having后面的
/*
1.标量子查询：一般搭配着单行操作符使用：
> < = <> >= <=
2.列子查询:一般搭配着多行操作符使用：
IN ANY/SOME ALL
3.行子查询

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

#1.标量子查询
#案例1：谁的工资比Abel的工资高
SELECT last_name,salary FROM employees WHERE salary >(SELECT salary FROM employees WHERE last_name = 'Abel');

#案例2：返回job_id与141号员工相同，salary比143号员工的工资多的员工姓名、job_id，工资
SELECT job_id FROM employees WHERE employee_id = 141;
SELECT salary FROM employees WHERE employee_id = 143;
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;
SELECT department_id,MIN(salary) FROM employees GROUP BY department_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.列子查询（一列多行子查询）
#案例1：返回location_id是1400或1700的部门中的所有员工的姓名
SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700);
#方法1
SELECT last_name FROM employees WHERE department_id IN(SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700));
#方法2
SELECT last_name FROM employees e INNER JOIN departments d ON e.`department_id`=d.`department_id` WHERE d.`location_id` IN(1400,1700);

#案例2：返回其他部门中比job_id为'IT_PROG'部门任意工资低的员工的：工号、姓名、job_id、salary
#方法1
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < ALL(SELECT salary FROM employees WHERE job_id='IT_PROG') AND job_id <> 'IT_PROG';
#方法2
SELECT employee_id,last_name,job_id,salary FROM employees WHERE salary < (SELECT MIN(salary) FROM employees WHERE job_id='IT_PROG') AND job_id <> 'IT_PROG';

#3.行子查询（一行多列）
#案例1：查询员工编号最小并且工资最高的员工信息
SELECT  * 
FROM
  employees 
WHERE (employee_id, salary) = 
  (SELECT 
    MIN(employee_id),
    MAX(salary) 
  FROM
    employees) ;

#二、select后面
#案例：查询每个部门的员工个数(按照部门表）
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.department_id) 个数 
FROM
  departments d ;
  
  
#三、from后面
/*
将子查询的结果看做一个表，要求必须起别名
*/
#案例1：查询每个部门的平均工资的工资等级
SELECT 
  ag_dep.*,
  j.`grade_level` 
FROM
  (SELECT 
    AVG(salary) ag,
    department_id 
  FROM
    employees 
  GROUP BY department_id) ag_dep 
  JOIN job_grades j 
    ON ag_dep.ag BETWEEN j.`lowest_sal` 
    AND j.`highest_sal` ;
    

#四、放在exists后面（相关子查询）
#exists:boolean类型
SELECT EXISTS(SELECT employee_id FROM employees);

#案例1：查询有员工的部门名
#in
SELECT department_name FROM departments d WHERE d.`department_id` IN(SELECT DISTINCT department_id FROM employees);

SELECT 
  department_name 
FROM
  departments d 
WHERE EXISTS 
  (SELECT 
    * 
  FROM
    employees e 
  WHERE d.`department_id` = e.`department_id`) ;
  
#案例2：查询没有女朋友的男神信息
USE girls;
SELECT bo.* FROM boys bo WHERE NOT EXISTS(SELECT * FROM beauty b WHERE b.`boyfriend_id`= bo.id);

#练习1：查询各部门中工资比本部门平均工资高的员工的：员工号、姓名和工资
USE myemployees;
SELECT AVG(salary),department_id FROM employees GROUP BY department_id; 
SELECT employee_id,last_name,salary,e.`department_id` FROM (SELECT AVG(salary)avge,department_id FROM employees GROUP BY department_id)de_avge JOIN employees e ON e.`department_id` = de_avge.department_id WHERE e.salary > de_avge.avge;