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` 的替代方案