mysql子查詢如何應用

這篇“mysql子查詢如何應用”文章的知識點大部分人都不太理解，所以小編給大家總結了以下內容，內容詳細，步驟清晰，具有一定的借鑒價值，希望大家閱讀完這篇文章能有所收獲，下面我們一起來看看這篇“mysql子查詢如何應用”文章吧。

• 簡介

子查詢要包含在括號內
將子查詢放在比較條件的右側
單行操作符對應單行子查詢，多行操作符對應多行子查詢

• 分類

單行子查詢：查詢的結果只有1行
多行子查詢：查詢的結果有多行
相關(或關聯)子查詢：子查詢中的參數需要用到主查詢中的值
不相關(或非關聯)子查詢

• 案例1

# 1.查詢工資比Abel高用戶
# 方式1：需要2步
SELECT salary FROM employees WHERE last_name = 'Abel';

SELECT last_name, salary FROM employees WHERE salary > 11000;

# 方式2：自連接
SELECT e2.last_name, e2.salary
FROM employees e1, employees e2
WHERE e2.`salary` > e1.`salary` #多表的連接條件
AND e1.last_name = 'Abel';

# 方式3：子查詢
SELECT last_name,salary
FROM employees
WHERE salary > 
  (SELECT salary
        FROM employees
        WHERE last_name = 'Abel'
        );

• 稱謂的規范

外查詢（或主查詢）
內查詢（或子查詢）

• 單行比較操作符



• 代碼案例

# 查詢工資大于149號員工工資的員工的信息
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE employee_id = 149
  );

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


# 返回公司工資最少的員工的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相同的其他員工的employee_id，manager_id，department_id
# 方式1：
SELECT employee_id, manager_id, department_id
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;

# 方式2：
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id,department_id) = (
  SELECT manager_id,department_id
  FROM employees
  WHERE employee_id = 141
  )
AND employee_id <> 141;

# 查詢最低工資大于110號部門最低工資的部門id和其最低工資
SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
  SELECT MIN(salary)
  FROM employees
  WHERE 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;

# 子查詢結果為null，則最后結果為null
SELECT last_name, job_id
FROM   employees
WHERE  job_id =
  (SELECT job_id
  FROM   employees
  WHERE  last_name = 'Haas');

• 多行子查詢

也稱為集合比較子查詢
內查詢返回多行
使用多行比較操作符

• 多行比較操作符



• 代碼案例

# IN:
SELECT employee_id, last_name
FROM employees
WHERE salary IN
  (SELECT MIN(salary)
  FROM employees
  GROUP BY department_id); 
                 
# ANY 或 ALL:
# 返回其它job_id中比job_id為‘IT_PROG’部門任一工資低的員工的員工號、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );

# 返回其它job_id中比job_id為‘IT_PROG’部門所有工資低的員工的員工號、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (
  SELECT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
  );
    
# 查詢平均工資最低的部門id
# MySQL中聚合函數是不能嵌套使用的。
# 方式1：
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
    ) t_dept_avg_sal
  );

# 方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL( 
  SELECT AVG(salary) avg_sal
  FROM employees
  GROUP BY department_id
  ) 

# 子查詢結果為null，則最后結果為null
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
  SELECT manager_id
  FROM employees
  );

• 相關子查詢

子查詢中使用主查詢中的列

• 代碼案例

# 查詢員工中工資大于公司平均工資的員工的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
# 方式1：使用相關子查詢
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE department_id = e1.`department_id`
  );

#方式2：在FROM中聲明子查詢
SELECT e.last_name, e.salary, e.department_id
FROM employees e,(
  SELECT department_id,AVG(salary) avg_sal
  FROM employees
  GROUP BY department_id) t_dept_avg_sal    # 取別名
WHERE e.department_id = t_dept_avg_sal.department_id
AND e.salary > t_dept_avg_sal.avg_sal

# 查詢員工的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`
  ) ASC;

• 在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以聲明子查詢

SELECT ....,....,....(存在聚合函數)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的連接條件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函數的過濾條件
GROUP BY ...,....
HAVING 包含聚合函數的過濾條件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

cDNAF" class=" list-paddingleft-2">

• EXISTS 與 NOT EXISTS關鍵字

1、關聯子查詢通常也會和 EXISTS操作符一起來使用，用來檢查在子查詢中是否存在滿足條件的行
2、如果在子查詢中不存在滿足條件的行：
    條件返回 FALSE
    繼續在子查詢中查找
3、如果在子查詢中存在滿足條件的行：
    不在子查詢中繼續查找
    條件返回 TRUE
4、NOT EXISTS關鍵字表示如果不存在某種條件，則返回TRUE，否則返回FALSE

• 代碼案例

# 若employees表中employee_id與job_history表中employee_id相同的數目不小于2，輸出這些相同id的員工的employee_id,last_name和其job_id
SELECT employee_id, last_name, job_id
FROM employees e
WHERE 2 <= (
  SELECT COUNT(*)
  FROM job_history j
  WHERE e.`employee_id` = j.`employee_id`
  )

# EXISTS 與 NOT EXISTS關鍵字
# 查詢公司管理者的employee_id，last_name，job_id，department_id信息
# 方式1：自連接
SELECT DISTINCT mgr.employee_id, mgr.last_name, mgr.job_id, mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

# 方式2：子查詢
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (
  SELECT DISTINCT manager_id
  FROM employees
  );

#方式3：使用EXISTS
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (
  SELECT *
  FROM employees e2
  WHERE e1.`employee_id` = e2.`manager_id`
  );

# 查詢departments表中，不存在于employees表中的部門的department_id和department_name
#方式1：
SELECT d.department_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 方式2：
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
  SELECT *
  FROM employees e
  WHERE d.`department_id` = e.`department_id`
  );

以上就是關于“mysql子查詢如何應用”這篇文章的內容，相信大家都有了一定的了解，希望小編分享的內容對大家有幫助，若想了解更多相關的知識內容，請關注億速云行業資訊頻道。