MySQL 中用於增強查詢的巢狀選擇語句
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 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