嵌套套娃，MySQL子查询，单行与多行子查询，相关和不相关（关联）子查询，完整详细可收藏

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`;
#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
    );

#返回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);

#查询最低工资大于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);

#显式员工的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;

#查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
         SELECT AVG(salary) 
         FROM employees
         GROUP BY department_id
         );

SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

#查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name
    FROM departments d
    WHERE e.`department_id` = d.`department_id`
    );

#方式一
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
      FROM employees e2
      WHERE e2.manager_id =e1.employee_id);
#方式二：自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
#方式三
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
       SELECT DISTINCT manager_id
       FROM employees
       );

UPDATE table1 alias1
SET column = (SELECT expression
     FROM table2 alias2
     WHERE alias1.column = alias2.column);

DELETE FROM table1 alias1
WHERE column operator (SELECT expression
        FROM table2 alias2
        WHERE alias1.column = alias2.column);

#方式1：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
     SELECT salary
     FROM employees
     WHERE last_name = 'Abel'
    );