MySQL 中用於增強查詢的巢狀選擇語句

Victor A. Oguntuase 2023年1月30日

MySQL MySQL Select MySQL Query

在 MySQL 中使用內外查詢方法實現巢狀的 SELECT 語句
在 MySQL 中實現巢狀的 SELECT 語句作為 JOINS 的替代方案

SELECT 語句是使用 SQL 從資料庫中檢索資料的主要函式。但是，有些用例需要操作或檢索執行的 SELECT 操作的結果。

巢狀的 SELECT 語句方法為此類高階查詢提供了方便的解決方案。

與通用程式語言類似，SQL 提供了多種實現相同結果的方法。巢狀的 SELECT 語句可以作為 JOINS 的替代方案。

但是，在某些情況下，使用巢狀的 SELECT 語句是最佳選擇。本教程通過幾個實際示例探討了這些概念和用例。

在 MySQL 中使用內外查詢方法實現巢狀的 `SELECT` 語句

MySQL 中的內外查詢方法在操作上類似於大多數通用程式語言中的巢狀表示式或函式。內部查詢首先執行，然後將其結果傳遞給包裝外部查詢，通常通過 WHERE 子句。

例如，讓我們建立一個示例表 Employee，其中包含 id、name、title 和 salary 列。

-- Initializing and selecting a database
CREATE DATABASE test_company;
USE test_company;

-- creating a sample employees table
CREATE TABLE employees(
    id INT AUTO_INCREMENT,
    name VARCHAR (255),
    title VARCHAR(255),
    salary INT,

    PRIMARY KEY (id)
);

-- populating the employees' table with information
INSERT INTO employees (name, title, salary) Values
('James Maddison','Computer Engineer',80000),
('Matthew Defoe','Software Architect',150000),
('Daniel Jameson','Software Engineer II', 95000),
('Jules Reddington','Senior Software Engineer',120000),
('Carlos Rodriguez','Data Engineer',100000);

-- previewing the employees' table
SELECT * FROM employees;

輸出：

id	name				title						salary
1	James Maddison		Computer Engineer			80000
2	Matthew Defoe		Software Architect			150000
3	Daniel Jameson		Software Engineer II		95000
4	Jules Reddington	Senior Software Engineer	120000
5	Carlos Rodriguez	Data Engineer				100000
-----------------------------------------------------------------------------------------
1 row(s) affected
0 row(s) affected
0 row(s) affected
5 row(s) affected Records: 5  Duplicates: 0  Warnings: 0
5 row(s) returned

查詢當前收入超過公司平均工資的員工的詳細資訊。

我們首先使用 AVG() 函式計算公司的平均工資，然後根據返回的平均值過濾 Employees 表。

通常，計算量越大的查詢是內部查詢，以提高效率和邏輯。這種方法可確保外部子查詢僅過濾顯著減少的值表。

有關選擇適當的內部和外部子查詢的更多詳細資訊，請參閱此來自 w3resource 的參考。

/* Inner query
SELECT AVG(salary) FROM employees;

Outer query
SELECT * FROM employees
WHERE salary > (Inner query)
*/

-- Full Query
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;   -- starting from the highest-paid

輸出：

id	name				title						salary
2	Matthew Defoe		Software Architect			150000
4	Jules Reddington	Senior Software Engineer	120000
-----------------------------------------------------------------------------------------
0.032 sec / 0.000 sec
2 row(s) returned

該查詢返回當前收入高於平均工資（即 109,000 美元）的兩名員工。

在 MySQL 中實現巢狀的 `SELECT` 語句作為 `JOINS` 的替代方案

在從多個表中檢索資料時，作為實現 JOINS 的替代方案，巢狀 SELECT 可能是一個不錯的選擇。

通過外來鍵新增另一個與 Employees 表相關的名為 Projects 的表。另外，讓我們將額外的必需列和外來鍵約束新增到 Employees 表中。

-- Adding a new projects table
CREATE TABLE projects(
	project_id INT,
    project_name VARCHAR(255) DEFAULT NULL,
    programming_language VARCHAR(255) DEFAULT 'N/A',

    PRIMARY KEY (project_id)
);

-- This modifies the employees' table and adds a foreign key
ALTER TABLE employees
ADD project_id INT,
ADD FOREIGN KEY (project_id) REFERENCES projects(project_id);

-- Populating the projects table and updating the employees' table with project_ids
INSERT INTO projects(project_id, project_name, programming_language) VALUES
(100, 'Movie Recommendation System', 'Python, Javascript, R'),
(105, 'Deep Learning Data Pipeline 10', 'Python, R, SQL'),
(107, 'Web-Based Diagnostic Support AI System', 'Python, Javascript, Html, CSS');

INSERT INTO projects(project_id, project_name) VALUES
(311, 'Computer Hardware Revamp'),
(109, 'Implementing an Advanced Conversational Agent for Effex.inc');

SELECT * FROM projects;

輸出：

project_id	project_name							programming_language
100			Movie Recommendation System				Python, Javascript, R
105			Deep Learning Data Pipeline 10			Python, R, SQL
107			Web-Based Diagnostic Support AI System	Python, Javascript, Html, CSS
109			Implementing an Advanced Conversational N/A
			Agent for Effex.inc
311			Computer Hardware Revamp				N/A
-----------------------------------------------------------------------------------------
0 row(s) affected
5 row(s) affected Records: 5  Duplicates: 0  Warnings: 0
5 row(s) affected Records: 5  Duplicates: 0  Warnings: 0
5 row(s) returned

現在，更新 employees 表中的 project_id 列。

-- NOW relating employees to projects
UPDATE employees
SET project_id = 311
WHERE id = 1;

UPDATE employees
SET project_id = 109
WHERE id = 2;

UPDATE employees
SET project_id = 100
WHERE id = 3;

UPDATE employees
SET project_id = 107
WHERE id = 4;

UPDATE employees
SET project_id = 105
WHERE id = 5;

SELECT * FROM employees;

輸出：

id	name			 title					  salary	project_id
1	James Maddison	 Computer Engineer		  80000		311
2	Matthew Defoe	 Software Architect	  	  150000	109
3	Daniel Jameson	 Software Engineer II	  95000		100
4	Jules Reddington Senior Software Engineer 120000	107
5	Carlos Rodriguez Data Engineer			  100000	105

-----------------------------------------------------------------------------------------
1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0
1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0
1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0
1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0
1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0
5 row(s) returned

然後我們可以使用巢狀的 SELECT 語句從兩個表中選擇值。讓我們找出使用 JavaScript 工作的員工的姓名、職位、薪水、專案名稱和程式語言。

首先，內部查詢從專案表中獲取值。然後，外部查詢從 Result-Set 中獲取相關資料以生成所需的檢視。

-- Selecting details of employees that use Javascript (Inner Query)
SELECT project_id, project_name FROM projects WHERE programming_language LIKE '%Javascript%';

輸出：

project_id	project_name
100			Movie Recommendation System
107			Web-Based Diagnostic Support AI System
-----------------------------------------------------------------------------------------
2 row(s) returned

現在，完整的查詢。

/*
-- Inner query
SELECT project_id, project_name, programming_language FROM projects WHERE programming_language LIKE '%Javascript%';

-- Outer query
SELECT E.name AS 'Employee Name', E.title AS 'Job Title', E.Salary AS 'Salary',
P.project_name AS 'Current Project', P.programming_language AS 'Programming Language'
FROM employees AS E,
(Inner query) AS P
WHERE E.project_id = P.project_id;
*/

-- Full query
SELECT E.name AS 'Employee Name', E.title AS 'Job Title', E.Salary AS 'Salary',
P.project_name AS 'Current Project'
FROM employees AS E,
(SELECT project_id, project_name FROM projects WHERE programming_language LIKE '%Javascript%') AS P
WHERE E.project_id = P.project_id;

輸出：

Employee Name		Job Title					Salary	Current Project
Daniel Jameson	 	Software Engineer II		95000	Movie Recommendation System
Jules Reddington	Senior Software Engineer	120000	Web-Based Diagnostic Support AI 														System
-----------------------------------------------------------------------------------------
2 row(s) returned

這個結果也可以通過編寫良好的 JOIN 語句來實現。但是，需要在效率和便利性之間進行權衡。

作者： Victor A. Oguntuase

Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.

LinkedIn GitHub

MySQL 中用於增強查詢的巢狀選擇語句

在 MySQL 中使用內外查詢方法實現巢狀的 `SELECT` 語句

在 MySQL 中實現巢狀的 `SELECT` 語句作為 `JOINS` 的替代方案

相關文章 - MySQL Select

相關文章 - MySQL Query

在 MySQL 中使用內外查詢方法實現巢狀的 SELECT 語句

在 MySQL 中實現巢狀的 SELECT 語句作為 JOINS 的替代方案

相關文章 - MySQL Select

相關文章 - MySQL Query

在 MySQL 中使用內外查詢方法實現巢狀的 `SELECT` 語句

在 MySQL 中實現巢狀的 `SELECT` 語句作為 `JOINS` 的替代方案