MySQL數(shù)據(jù)庫中的嵌套查詢實例詳解
1. 嵌套查詢的定義
嵌套查詢指在一個查詢語句的某個部分嵌入一個子查詢。
嵌套查詢的執(zhí)行過程遵循“先子查詢、后外層查詢”的邏輯。首先,子查詢執(zhí)行并返回一個結(jié)果集,可能是一個值、一行或多行數(shù)據(jù)。接著,外層查詢使用子查詢的結(jié)果繼續(xù)對數(shù)據(jù)進行篩選或處理。通過這種方式,嵌套查詢可以處理更復雜的邏輯,如多層條件過濾、數(shù)據(jù)對比等。
- 子查詢:首先執(zhí)行,返回符合條件的結(jié)果。
- 外層查詢:利用子查詢返回的結(jié)果進行篩選或其他邏輯操作,最終返回結(jié)果。
2. 嵌套查詢的語法
2.1 嵌套查詢的基本結(jié)構(gòu)
SELECT 列名 FROM 表名 WHERE 列名 比較運算符 (子查詢);
先通過子查詢返回結(jié)果,然后再通過比較運算符判斷子查詢返回的結(jié)果是否滿足條件,滿足條件的字段的記錄,就會展示該記錄被SELECT的字段。
示例
SELECT column_name1 FROM table_name1 WHERE column_name2 比較運算符 (SELECT column_name3 FROM table_name2 WHERE condition);
- 比較運算符之后的 “(SELECT column_name3 FROM table_name2 WHERE condition)” 是作為子查詢,執(zhí)行SQL語句時,會先選出表table_name2中符合條件condition的記錄的column_name3字段給外層查詢。
- 當?shù)玫搅俗硬樵兎祷氐腸olumn_name3字段,外層查詢會先將表table_name1的所有記錄的column_name2字段 通過比較運算符與這些返回的column_name3字段進行比較。
- 對于滿足了比較運算符規(guī)則的column_name2字段的記錄,會返回這些記錄的column_name1字段。
2.2 常見的比較運算符
=
:用于檢查外層查詢的某個列的值是否等于子查詢返回的值。>
、<
、>=
、<=
:用于比較外層查詢的列值與子查詢結(jié)果之間的大小關(guān)系。IN
:用于檢查外層查詢的某個列值是否在子查詢返回的一組結(jié)果中。ANY
/SOME
:用于檢查外層查詢的列值是否滿足子查詢返回結(jié)果中的任意一個值的條件。ALL
:用于檢查外層查詢的列值是否滿足子查詢返回結(jié)果中的所有值的條件。
注意:
ANY
/SOME和ALL
一般要結(jié)合>
、<
、>=
、<=來使用
4. ANY
/ SOME
運算符
SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 1);
- 外層查詢篩選出那些薪水大于部門 1 中任意一個員工薪水的員工。
- 只要大于部門1中薪水最低的員工就符合條件。
5. ALL 運算符
SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 1);
- 外層查詢篩選出那些薪水大于部門 1 中所有員工薪水的員工。
- 必須大于部門1中薪水最高的員工才能符合條件。
3. 基于子查詢行為的分類
- 依據(jù):這一分類基于子查詢的返回結(jié)果形式以及子查詢與外層查詢之間的依賴關(guān)系。
- 重點:這一分類更關(guān)注子查詢本身的性質(zhì),即子查詢是返回多少數(shù)據(jù)(單行或多行、多列)、是否依賴于外層查詢,以及子查詢的執(zhí)行方式是一次性還是每行重新執(zhí)行。
3.1 單行子查詢
- 定義:單行子查詢是指子查詢返回一個字段的一個值。這個值可以是一個具體的數(shù)字、日期、文本等。子查詢只返回一個結(jié)果,外層查詢會使用這個結(jié)果進行比較、篩選或計算。
- 特點:子查詢返回的是一個字段的單一值。外層查詢使用這個值來進行條件判斷或篩選。
- 應(yīng)用場景:當需要從子查詢中獲取一個具體的值(如最大值、最小值、平均值等),然后外層查詢使用這個值進行比較。
語法結(jié)構(gòu):
SELECT 列名 FROM 表名 WHERE 列名 比較運算符 (子查詢);
示例:
SELECT 姓名, 工資 FROM 員工 WHERE 工資 = (SELECT MAX(工資) FROM 員工);
解釋:
- 子查詢
(SELECT MAX(工資) FROM 員工)
返回員工表中的最高工資。 - 外層查詢根據(jù)這個最高工資篩選出符合條件的員工(即工資等于最高工資的員工)。
3.2 多行子查詢
- 定義:多行子查詢是指子查詢返回一個或多個字段的一個或多個值。外層查詢通過集合運算符(如
IN
、ANY
、ALL
等)來將外層查詢的字段與子查詢返回的多個結(jié)果進行比較和匹配。 - 特點:子查詢返回的是一組值(可以是一個或多個字段),外層查詢使用這些值進行篩選或比較。
- 應(yīng)用場景:當子查詢返回多個值時,外層查詢通過集合運算符與這些結(jié)果進行匹配。例如,查找某個字段的值是否存在于一組返回值中。
語法結(jié)構(gòu):
SELECT 列名 FROM 表名 WHERE 列名 IN (子查詢);
示例:
SELECT 姓名 FROM 員工 WHERE 部門編號 IN (SELECT 部門編號 FROM 部門 WHERE 城市 = '上海');
解釋:
- 子查詢
(SELECT 部門編號 FROM 部門 WHERE 城市 = '上海')
返回所有位于上海的部門編號,這是一組值。 - 外層查詢使用子查詢返回的這些部門編號來篩選出屬于這些部門的員工。
3.3 相關(guān)子查詢
- 定義:相關(guān)子查詢是指子查詢依賴于外層查詢的每一條記錄,因此每次外層查詢處理新的一條記錄時,子查詢都會重新執(zhí)行一次,并根據(jù)當前這條記錄中的數(shù)據(jù)計算出結(jié)果。這意味著子查詢會根據(jù)外層查詢的變化動態(tài)地生成結(jié)果。
- 特點:每當外層查詢處理一條新記錄時,相關(guān)子查詢就會根據(jù)這條記錄的值重新執(zhí)行,并返回新的結(jié)果。子查詢的結(jié)果因外層查詢的記錄而動態(tài)變化。
- 應(yīng)用場景:當子查詢的結(jié)果依賴于外層查詢當前正在處理的記錄時使用。例如,針對每條記錄計算與其相關(guān)的數(shù)據(jù)或值。
語法結(jié)構(gòu):
SELECT 列名 FROM 表名 AS 外層表 WHERE 列名 比較運算符 (SELECT 列名 FROM 子查詢表 WHERE 子查詢表.列名 = 外層表.列名);
示例:
SELECT 房屋編號, 價格 FROM 房屋 AS 可買房屋 WHERE 價格 > (SELECT AVG(價格) FROM 房屋 AS 出售房屋 WHERE 出售房屋.城市 = 可買房屋.城市);
解釋:
外層查詢:
SELECT 房屋編號, 價格 FROM 房屋 AS 可買房屋
:
外層查詢從房屋
表中檢索每個房屋的編號和價格,并將房屋
表賦予別名可買房屋
。這個別名的作用是幫助區(qū)分外層查詢和子查詢中的相同表名,以便進行比較。這一步的目的是從所有房屋中找到符合特定條件的房屋。
子查詢:
(SELECT AVG(價格) FROM 房屋 AS 出售房屋 WHERE 出售房屋.城市 = 可買房屋.城市)
:
子查詢的任務(wù)是計算當前房屋所在城市的平均房價。子查詢也使用了房屋
表,但被賦予了別名出售房屋
,用于避免與外層查詢中的可買房屋
混淆。子查詢的WHERE
子句指定了只有那些與當前外層查詢的房屋處于相同城市的房屋記錄才會參與平均價格的計算。執(zhí)行過程:每次外層查詢處理一條新的房屋記錄時,子查詢都會根據(jù)這條房屋記錄的城市,動態(tài)地計算該城市中所有房屋的平均價格。也就是說,子查詢依賴于外層查詢中的房屋城市信息。因此,當外層查詢遍歷到某個房屋時,子查詢會執(zhí)行一次,計算出這個房屋所在城市的平均房價。
結(jié)果作用:子查詢返回的是該城市的平均房價。這個值會用于外層查詢的
WHERE
子句中,幫助判斷當前房屋的價格是否高于其所在城市的平均房價。
整體邏輯:
子查詢的動態(tài)執(zhí)行:對于每一條外層查詢的記錄(即每一個房屋),子查詢都會基于該房屋的城市重新計算城市的平均房價。比如,當外層查詢正在處理北京的某個房屋時,子查詢會檢索所有位于北京的房屋,并計算這些房屋的平均價格。
條件比較:外層查詢的
WHERE
子句會將當前房屋的價格與子查詢返回的城市平均房價進行比較。如果當前房屋的價格高于該城市的平均房價,則該房屋的編號和價格會被返回。
3.4 非相關(guān)子查詢
- 定義:非相關(guān)子查詢是指子查詢與外層查詢沒有直接的依賴關(guān)系。子查詢獨立執(zhí)行一次,返回一個結(jié)果,然后外層查詢無論處理多少條記錄,都只會與這個結(jié)果進行比較。
- 特點:子查詢在外層查詢之前獨立執(zhí)行一次,返回一個固定值。這個值用于外層查詢的每一條記錄中進行比較或篩選。
- 應(yīng)用場景:當子查詢的結(jié)果與外層查詢無關(guān)時使用,例如在查詢中用到的某個全局值或固定結(jié)果。
語法結(jié)構(gòu):
SELECT 列名 FROM 表名 WHERE 列名 = (子查詢);
示例:
SELECT 姓名 FROM 員工 WHERE 部門編號 = (SELECT 部門編號 FROM 部門 WHERE 部門名稱 = '市場部');
解釋:
- 子查詢
(SELECT 部門編號 FROM 部門 WHERE 部門名稱 = '市場部')
獨立執(zhí)行一次,返回市場部的部門編號。 - 外層查詢不論處理多少條員工記錄,都始終與市場部的編號進行比較。
3.5 總結(jié)
- 單行子查詢:返回一個字段的一個值,外層查詢與該值進行比較或篩選。
- 多行子查詢:返回一個或多個字段的多個值,外層查詢使用集合運算符與這些值進行匹配。
- 相關(guān)子查詢:每次外層查詢處理新記錄時,子查詢都會根據(jù)該記錄的值重新執(zhí)行,生成新的結(jié)果。
- 非相關(guān)子查詢:子查詢只執(zhí)行一次,返回固定的結(jié)果,外層查詢無論處理多少條記錄,都與該固定結(jié)果進行比較。
4. 基于子查詢位置的分類
- 依據(jù):這一分類基于子查詢在 SQL 語句中所處的位置,即子查詢是出現(xiàn)在
WHERE
、FROM
、SELECT
還是HAVING
子句中。 - 重點:這一分類關(guān)注子查詢在外層查詢中的用途和功能,即子查詢?nèi)绾闻c外層查詢結(jié)合以實現(xiàn)具體的數(shù)據(jù)處理。
4.1 WHERE 子句中的嵌套查詢
語法:
SELECT 列名 FROM 表名 WHERE 列名 比較運算符 (子查詢);
使用場景:
- WHERE 子句中的嵌套查詢常用于條件過濾。通常,嵌套查詢返回一個單一值或一組值,然后在外部查詢的 WHERE 子句中用作過濾條件。
- 例如:篩選出滿足某些特定條件的記錄,如選取工資最高的員工或獲取某個特定狀態(tài)的客戶。
示例:
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = ( SELECT department_id FROM departments WHERE department_name = 'Sales' );
子查詢:
SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id
用于計算每個員工所在部門的平均工資。- 子查詢中的
WHERE department_id = employees.department_id
是關(guān)鍵部分,這里表示子查詢是針對每個員工所在的部門來計算部門的平均工資。 - 子查詢?yōu)槊總€員工執(zhí)行一次,返回該員工所在部門的平均工資。
外層查詢:
- 查詢員工的姓名和工資
SELECT name, salary FROM employees
。 WHERE
子句決定篩選條件,要求員工的salary
大于子查詢返回的值。
- 查詢員工的姓名和工資
執(zhí)行過程:
- 外層查詢對每個員工逐行進行遍歷。
- 對于每個員工,嵌套查詢計算其所在部門的平均工資。
- 然后比較該員工的工資是否高于部門平均工資,只有滿足條件的員工會被返回。
4.2 FROM 子句中的嵌套查詢
語法:
SELECT 列名 FROM (子查詢) AS 臨時表
使用場景:
- FROM 子句中的嵌套查詢被稱為派生表,類似于創(chuàng)建了一個臨時表。它可以簡化復雜的聚合操作,特別是當需要多次使用相同的中間結(jié)果時。
- 這種方法常用于對中間結(jié)果進行進一步的查詢,例如對一個已經(jīng)聚合的數(shù)據(jù)集再次進行過濾或計算。
示例:
SELECT department_name, avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) AS avg_department_salaries JOIN departments ON avg_department_salaries.department_id = departments.department_id;
子查詢:
- 子查詢
SELECT department_id, salary FROM employees
提取所有員工的部門ID和工資,形成了一個虛擬表dept_employees
。 - 這個虛擬表作為數(shù)據(jù)源傳遞給外層查詢,就像一個普通的表一樣。
- 子查詢
外層查詢:
- 外層查詢的
SELECT department_id, AVG(salary) AS average_salary, COUNT(*) AS num_employees
負責從dept_employees
中對數(shù)據(jù)進行處理。 AVG(salary)
計算每個部門的平均工資。COUNT(*)
計算每個部門的員工數(shù)。
- 外層查詢的
執(zhí)行過程:
- 子查詢生成一個僅包含
department_id
和salary
列的臨時表dept_employees
。 - 外層查詢對這個臨時表的數(shù)據(jù)進行分組,并計算每個部門的員工數(shù)量和平均工資。
- 子查詢生成一個僅包含
4.3 SELECT 子句中的嵌套查詢
語法:
SELECT (子查詢) AS 列名 FROM 表名;
使用場景:
- SELECT 子句中的嵌套查詢常用于動態(tài)生成新的列。這些子查詢通常為每一行返回一個計算結(jié)果,用于豐富原始數(shù)據(jù)集。
- 這種方式通常用于統(tǒng)計計算、數(shù)據(jù)轉(zhuǎn)換,或者從其他表中提取額外的信息。
示例:
SELECT employee_id, first_name, last_name, (SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) AS department_name FROM employees;
子查詢:
- 子查詢
SELECT MAX(degree) FROM education WHERE education.employee_id = employees.id
用于查找與當前員工對應(yīng)的最高學歷。 WHERE education.employee_id = employees.id
確保子查詢與外層查詢中的每個員工相匹配。- 子查詢返回當前員工的最高學歷。
- 子查詢
外層查詢:
- 外層查詢
SELECT name, ... FROM employees
檢索所有員工的姓名。 highest_degree
列使用子查詢的結(jié)果作為額外的信息。
- 外層查詢
執(zhí)行過程:
- 對于外層查詢中的每個員工,子查詢會查找其最高學歷,并將其作為外層查詢的結(jié)果列
highest_degree
。 - 每行執(zhí)行一次子查詢,因此每個員工的最高學歷都會與員工姓名一起返回。
- 對于外層查詢中的每個員工,子查詢會查找其最高學歷,并將其作為外層查詢的結(jié)果列
4.4 HAVING 子句中的嵌套查詢
語法:
SELECT 列名 FROM 表名 GROUP BY 列名 HAVING 聚合函數(shù) 比較運算符 (子查詢);
使用場景:
- HAVING 子句中的嵌套查詢通常用于在分組后的數(shù)據(jù)基礎(chǔ)上進行復雜的過濾。HAVING 是對聚合結(jié)果(如 SUM、COUNT、AVG 等)的過濾,嵌套查詢可以用于比較每個分組的結(jié)果與其他表的數(shù)據(jù)或特定計算值。
- 常見場景是當我們需要對聚合后的結(jié)果集進行精細化的過濾時,例如篩選出滿足某一條件的分組。
示例:
SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > (SELECT AVG(employee_count) FROM (SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id) AS dept_counts);
子查詢:
- 子查詢
SELECT AVG(employee_count) FROM (SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id) AS dept_counts
首先計算每個部門的員工數(shù)量,然后計算這些數(shù)量的平均值。 - 這其實是一個雙重嵌套查詢,第一層子查詢計算每個部門的員工數(shù)量,第二層子查詢計算這些員工數(shù)量的平均值。
- 子查詢
外層查詢:
SELECT department_id FROM employees GROUP BY department_id
對員工按部門進行分組。HAVING COUNT(*) > ...
是一個過濾條件,用于過濾掉員工人數(shù)不滿足條件的部門。
執(zhí)行過程:
- 外層查詢首先按部門分組,計算每個部門的員工數(shù)量。
- 子查詢計算所有部門的平均員工數(shù)量。
HAVING
子句確保只返回那些員工數(shù)量大于平均值的部門。
4.5 總結(jié)
WHERE
子句中的嵌套查詢:用于動態(tài)過濾外層查詢的行,基于子查詢的結(jié)果進行比較。FROM
子句中的嵌套查詢:創(chuàng)建臨時表,允許在外層查詢中使用簡化的數(shù)據(jù)集進行進一步的操作。SELECT
子句中的嵌套查詢:生成動態(tài)列值,將子查詢的結(jié)果直接應(yīng)用到外層查詢的每一行。HAVING
子句中的嵌套查詢:用于基于聚合結(jié)果進行分組后的過濾,是處理復雜分組統(tǒng)計場景的有效手段。
5. 嵌套查詢的性能考慮
5.1 嵌套查詢對性能的影響因素
嵌套查詢,尤其是復雜的嵌套查詢,可能對數(shù)據(jù)庫性能產(chǎn)生顯著的影響。以下是影響性能的主要因素:
查詢的重復執(zhí)行:
- 對于相關(guān)子查詢,每處理一條外層查詢的記錄,子查詢都會執(zhí)行一次。這樣,子查詢的執(zhí)行次數(shù)與外層查詢的記錄數(shù)成正比,導致性能下降,特別是在處理大量數(shù)據(jù)時。
- 非相關(guān)子查詢相對更快,因為子查詢只執(zhí)行一次,結(jié)果存儲后供外層查詢使用,但如果子查詢本身很復雜或數(shù)據(jù)量很大,性能也會受到影響。
索引的利用情況:
- 嵌套查詢中,如果子查詢和外層查詢沒有正確地使用索引,數(shù)據(jù)庫可能需要進行大量的全表掃描(Full Table Scan)。這會導致較大的 I/O 負擔,進而影響性能。
查詢的復雜性:
- 嵌套查詢往往涉及多個表的聯(lián)結(jié)、分組、排序等操作,復雜的邏輯可能導致數(shù)據(jù)庫查詢計劃(Query Plan)變得更復雜,增加了查詢的處理時間。
5.2 優(yōu)化嵌套查詢的方法
使用 JOIN 替代子查詢:
- 如果可以,將嵌套查詢轉(zhuǎn)換為
JOIN
查詢。JOIN
查詢通常更容易優(yōu)化,數(shù)據(jù)庫可以更高效地處理連接操作。例如,子查詢可以被重寫為INNER JOIN
或LEFT JOIN
,這通常會顯著提高性能。
- 如果可以,將嵌套查詢轉(zhuǎn)換為
索引優(yōu)化:
- 在參與嵌套查詢的字段上創(chuàng)建索引,尤其是用于過濾條件的字段。例如,在
WHERE
子句中出現(xiàn)的字段,如果有適當?shù)乃饕?,可以顯著提高查詢速度。
- 在參與嵌套查詢的字段上創(chuàng)建索引,尤其是用于過濾條件的字段。例如,在
避免相關(guān)子查詢:
- 盡可能避免使用相關(guān)子查詢,因為相關(guān)子查詢會為外層查詢的每條記錄執(zhí)行一次子查詢,效率較低??梢試L試將相關(guān)子查詢重寫為非相關(guān)子查詢或連接查詢。
分解復雜查詢:
- 將復雜的嵌套查詢分解為多個簡單的查詢,使用臨時表或視圖保存中間結(jié)果。這種做法有時可以簡化數(shù)據(jù)庫的處理邏輯,提升性能。
6. 嵌套查詢的優(yōu)勢與劣勢
優(yōu)勢
- 功能強大:嵌套查詢能夠處理復雜的業(yè)務(wù)邏輯,尤其是當需要在同一個查詢中進行多個獨立計算時(如計算聚合值、條件篩選等)。
- 代碼結(jié)構(gòu)簡潔:某些情況下,嵌套查詢可以避免使用中間結(jié)果或多次查詢,代碼邏輯更加緊湊。
- 動態(tài)篩選:嵌套查詢可以動態(tài)計算出結(jié)果,適合處理依賴于其他結(jié)果的數(shù)據(jù)操作。
劣勢
- 性能問題:嵌套查詢,特別是相關(guān)子查詢,可能帶來性能問題,尤其是在大數(shù)據(jù)集上運行時效率較低。
- 可讀性差:多層嵌套查詢會導致查詢邏輯復雜、代碼難以理解,維護和調(diào)試變得困難。
- 數(shù)據(jù)庫支持差異:不同數(shù)據(jù)庫對嵌套查詢的優(yōu)化程度不一樣,在某些數(shù)據(jù)庫中嵌套查詢的表現(xiàn)不佳,可能需要轉(zhuǎn)換為其他查詢方式。
總結(jié)
到此這篇關(guān)于MySQL數(shù)據(jù)庫中嵌套查詢的文章就介紹到這了,更多相關(guān)MySQL中嵌套查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL NDB Cluster關(guān)于Nginx stream的負載均衡配置方式
這篇文章主要介紹了MySQL NDB Cluster關(guān)于Nginx stream的負載均衡配置方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05mysql中DATE_FORMAT()函數(shù)的具體使用
在MySQL中,DATE_FORMAT()函數(shù)用于將日期/時間類型的值按照指定的格式進行格式化輸出,具有一定的參考價值,感興趣的可以了解一下2024-05-05