mysql-子查询的学习

子查询
由一个具体的需求，引入子查询 
谁的工资比Abel 的高
            SELECT * 
            from employees
            WHERE salary > (
            SELECT salary FROM employees WHERE last_name = 'Abel'
            )
            -- 自连接
            SELECT e2.*
            FROM employees  e1, employees e2 
            WHERE e2.salary > e1.salary
            AND e1.last_name = "abel"
        /*
- 子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
- 注意事项
  - 子查询要包含在括号内
  - 将子查询放在比较条件的右侧
  - 单行操作符对应单行子查询，多行操作符对应多行子查询
*/

1 单行操作符： =  !=  >   >=  <  <= 
            #题目：查询工资大于149号员工工资的员工的信息
            SELECT * FROM employees
            WHERE salary > (
            SELECT salary
            FROM employees WHERE employee_id=149
            )

            #题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
            SELECT 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)

            #题目：查询与141号员工的manager_id和department_id相同的其他员工 
            SELECT * FROM employees
            WHERE manager_id = (SELECT manager_id FROM employees WHERE employee_id=141)
            and department_id = (SELECT department_id FROM employees where employee_id=141)
            AND employee_id <> 141;

            #题目：查询最低工资大于110号部门最低工资的部门id和其最低工资
            SELECT department_id , min(salary)
            FROM employees GROUP BY department_id
            HAVING min(salary)>(
            SELECT min(salary)
            FROM employees GROUP BY department_id
            HAVING department_id = 110)

            #题目：显式员工的employee_id,last_name和location。
            #其中，若员工department_id与location_id为1800的department_id相同，
            #则location为’Canada’，其余则为’USA’。

            SELECT employee_id,last_name,
            case department_id WHEN (SELECT department_id FROM departments WHERE location_id=1800) THEN 'canada'
            else 'USA' end 'location'
            FROM employees
2. 多行子查询
        多行子查询的操作符；IN ANY ALL SOME(同any)
        也称为集合比较子查询
        内查询返回多行
        使用多行比较操作符
        in ： 等于列表中的任意一个
        ANY : 需要和单行比较操作符一起使用，和子查询返回的某一个值比较
        ALL ： 需要和单行比较操作符一起使用，和子查询返回的所有值比较
        SOME： 实际是any的别名，作用相同，一般常使用any
        
        #题目：查询平均工资最低的部门id
SELECT department_id
FROM employees GROUP BY department_id
HAVING avg(salary) =     
        (SELECT min(avg_sal)
        FROM 
        (SELECT avg(salary) avg_sal 
        FROM employees GROUP BY department_id) de_min_sal
        )
        
        SELECT department_id FROM employees GROUP BY department_id
        HAVING avg(salary)<= ALL(
        SELECT avg(salary) FROM employees GROUP BY department_id)
    
    #6. 相关子查询
            #回顾：查询员工中工资大于公司平均工资的员工的last_name,salary和其department_id
            SELECT last_name, salary, department_id FROM employees 
            WHERE salary > (SELECT avg(salary) FROM employees    )
            #题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
            SELECT e.last_name, e.salary,e.department_id from employees e,
            (SELECT department_id,avg(salary) dep_avg_sal FROM employees GROUP BY department_id) d
            WHERE e.department_id=d.department_id and salary > d.dep_avg_sal
            ##查询员工的ID，salary ，按照department_name 排序
            SELECT employee_id,salary FROM employees
            ORDER BY (
            SELECT department_name FROM departments
            WHERE employees.department_id=departments.department_id
            )
EXISTS 和not EXISTS 关键字

SELECT distinct e2.employee_id,e2.last_name,e2.job_id ,e2.department_id
FROM employees e1,employees e2 
WHERE e1.manager_id=e2.employee_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.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name, job_id,salary FROM employees 
WHERE salary > ALL (SELECT salary from employees where job_id = 'SA_MAN' );
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id FROM employees WHERE department_id IN (
SELECT department_id FROM departments WHERE location_id=1700)
#6.查询管理者是King的员工姓名和工资
SELECT e2.last_name, e2.salary
FROM employees e1,employees e2 
where e1.employee_id=e2.manager_id
AND e1.last_name="King"

#7.查询工资最低的员工信息: last_name, salary
SELECT last_name, salary FROM employees WHERE salary = (SELECT min(salary) FROM employees)
#8.查询平均工资最低的部门信息
SELECT  * FROM  departments
WHERE department_id = (
SELECT department_id from 
(SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id order by avg_sal ASC limit 1) dep_min_sal)
#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
SELECT d.*,avg_sal from  departments d, 
(SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id ORDER BY avg_sal ASC limit 1) dep_avg_sal 
WHERE d.`department_id` = dep_avg_sal.`department_id` 
#10.查询平均工资最高的 job 信息 
#11.查询平均工资高于公司平均工资的部门有哪些?
SELECT avg(salary) avg_sal,department_id FROM employees GROUP BY department_id HAVING avg_sal > (
SELECT avg(salary) from employees) AND department_id is not NULL
#12.查询出公司中所有 manager 的详细信息 
SELECT DISTINCT e1.* FROM employees e1,employees e2 WHERE e1.employee_id=e2.manager_id 
#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MAX(salary),department_id FROM employees GROUP BY department_id. ????
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
SELECT DISTINCT e1.last_name,e1.department_id,e1.email,e1.salary FROM employees e1, employees e2 WHERE e1.employee_id=e2.manager_id and e2.department_id = (
SELECT department_id from 
(SELECT avg(salary) avg_sal, department_id FROM employees GROUP BY department_id ORDER BY avg_sal DESC limit 1) dep_max_sal)
#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
#16. 选择所有没有管理者的员工的last_name
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'
SELECT last_name, employee_id,hire_date,salary FROM employees WHERE manager_id = (
SELECT employee_id FROM employees WHERE last_name = 'De Haan')
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

( SELECT avg(salary),department_id FROM employees GROUP BY department_id )
#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT department_name FROM departments WHERE department_id = ANY(
SELECT department_id FROM (
SELECT department_id,count(*) num FROM employees GROUP BY department_id HAVING num > 5) dep_5 )
#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）