小白学习MySQL Day11-12 20240826-0827

一、子查询 Subqueries

1、概念

是MySQL中的一个特性，是通过编写特定SQL语句来实现的：

一个SELECT语句嵌套在另一个SELECT语句中。

子查询的结果集被用作外层查询的条件或数据源。

2、基本用法

（1）在WHERE子句中使用

它返回一个值或一组值，这些值用于外层查询的条件判断。

例1）

假设有两个表：employees和departments。

employees表有一个department_id列，指向departments表的id列。

想找出所有在“销售”部门工作的员工的姓名：

SELECT name  
FROM employees  
WHERE department_id = (  
      SELECT id  
      FROM departments  
      WHERE name = '销售'  
);

• 思路：

1. 明确查询目标

最终所求是“员工的姓名”即employees表中的name列

故 SELECT name FROM employees 

2. 确定筛选条件

员工必须属于“销售”部门。

但是，employees表中并没有直接存储“销售”这个字符串，

只有（employees表中的department_id）与（departments表中的id）相对应。

3. 使用子查询获取部门ID

为了获取“销售”部门的ID，我们需要查询departments表，使用子查询。

子查询的作用是在departments表中查找name字段值为“销售”的记录，并返回其id字段的值。

4. 将子查询结果作为筛选条件

现在，我们有了“销售”部门的ID，可以将其用作筛选employees表中记录的条件。

我们将子查询的结果（即“销售”部门的ID）与employees表中的department_id字段进行比较。

只有当department_id与“销售”部门的ID相匹配时，该员工的记录才会被选中。

5. 执行查询并返回结果

执行这个查询，并返回所有在“销售”部门工作的员工的姓名。

另解（连接两个表，在中间结果集中使用WHERE predicate筛选名为销售的）：

SELECT e.name

FROM employees AS e

JOIN departments AS d

ON e.department_id = d.id

WHERE d.name = '销售';

例2）

从employee表中选出所有与名为'Andy'的员工在同一个部门的员工的名字。department_id

SELECT name

FROM employee

WHERE dept = (

        SELECT dept

        FROM employee

        WHERE name = 'Andy'

);

（2）在FROM子句中使用

子查询还可以作为另一个SELECT语句的临时表，

通常需要通过AS关键字为子查询结果集指定一个别名。

E.g. 从teachers表中选出所有 对应电子邮箱非空 的name和email。

SELECT * FROM (SELECT name, email FROM teachers) AS t

WHERE t.email IS NOT NULL;

上述代码可简化为：

SELECT name, email

FROM teachers

WHERE email IS NOT NULL;

3、操作符

Often a subquery will return a set of values rather than a single value.

We cannot directly compare a single value to a set. Doing so will result in an error.

• Options for handling sets:

        • IN : checks to see if a value is in a set.

        • EXISTS : checks to see if a set is empty.

        • ALL/ANY : checks to see if a relationship holds for every/one member of a set.

        • NOT : can be used with any of the above.

（1） IN

用于测试 某个值 是否 存在于子查询返回的集合中。

SELECT columns FROM tables

WHERE col IN set;

SELECT columns FROM tables

WHERE col NOT IN set;

The set can be given explicitly or can be produced in a subquery.

E.G.

1）明确给出集合

SELECT * FROM Employee

WHERE Department = 'Marketing'

OR Department = 'Sales';

使用IN代替了OR，简化了查询。

2）子查询生成集合

子查询返回的 结果集 必须是 单一列的。

E.G. 从employees表中检索出所有（（位置ID为1000）的部门）的员工的所有信息：

SELECT * FROM employees

WHERE department_id IN (

        SELECT department_id FROM departments

        WHERE location_id = 1000

); 

基于一个表（departments）中的条件来过滤另一个表（employees）中的记录：
根据一个可能包含多个值的列表来过滤记录，而这个列表是通过子查询动态生成的。
首先，子查询会从departments表中筛选出所有location_id为1000的部门的department_id（department_id可能多个值）。
然后，主查询会利用这些department_id来过滤出employees表中相应的员工记录。
IN运算符 : 检查一个值是否存在于一个集合中时。
即使子查询只返回一个单一的department_id值，IN运算符仍然是有效的，并且会正确地过滤出具有该department_id的员工记录。

（2）EXISTS

只需要检查存在性，而不需要实际获取匹配项的数据时，EXISTS会比INNER JOIN更快。

EXISTS 用于测试一个子查询 是否返回了至少一行数据。

即，检查的是：子查询的结果集是否为空。

The set is always given by a subquery
EXISTS is true if  there is at least one element in a given set.
NOT EXISTS is true if the set is empty.

SELECT column_name(s)

FROM table_name

WHERE EXISTS (subquery);

EXISTS子句中的子查询不需要返回任何列或数据，它只返回布尔值TRUE或FALSE。

如果子查询返回至少一行数据，EXISTS将返回TRUE。

如果子查询没有返回任何行，EXISTS将返回FALSE。

用于测试子查询是否返回至少一行数据。只关心子查询是否返回数据，而不关心返回的具体内容。

使用场景：选择与另一个表有关联的记录

E.G.

1）有两个表Orders和Customers，需要选择 所有 有订单的客户：

-- 法1：INNER JOIN
/* 返回两个表中满足指定条件(ON...)的行对。
   如果某个表中有行在另一个表中没有匹配的行，
   那么这些行就不会出现在查询结果中。
*/
SELECT c.name, c.contact
FROM Customers c
INNER JOIN Orders o ON c.id = o.customer_id;


-- 法2：EXISTS
SELECT Customers.CustomerName, Customers.ContactName
FROM Customers
WHERE EXISTS (
    SELECT 1 FROM Orders
    WHERE Orders.CustomerID = Customers.CustomerID
);

这个查询会返回所有在Orders表中有对应订单的客户的名称和联系人。

内部子查询：

WHERE EXISTS (

        SELECT 1 FROM Orders

        WHERE Orders.CustomerID = Customers.CustomerID

)

用于检查 对于Customers表中的每一行，是否存在至少一个Orders表中的记录，

使得Orders.CustomerID等于Customers.CustomerID。

EXISTS子句的作用：决定哪些记录应该被包括在结果集中。

对于Customers表中的每一条记录，

如果EXISTS子句返回TRUE（即在Orders表中至少存在一个匹配的CustomerID），那么这条记录就会包含在最终的结果集中。

如果EXISTS子句返回FALSE（即在Orders表中没有找到匹配的CustomerID），那么这条记录就不会被包括在结果集中。

2）

执行逻辑：

1.外部查询（主查询）：

查询从Employee表中选择所有列，这里使用别名E1来引用。

2.内部子查询（EXISTS子查询）：

对于外部查询中的每一条记录，都会执行一次内部子查询。

这个子查询也从Employee表中选择所有列，使用别名E2来引用。

3.EXISTS条件：

在内部子查询中，使用WHERE子句来检查：

是否存在至少一条记录，使得E1.Name（外部查询中的员工姓名）等于E2.Manager（内部查询中的员工的经理字段）。

4.EXISTS操作符的作用：

如果对于外部查询中的某条记录，内部子查询找到了至少一条满足条件的记录，那么EXISTS操作符将返回TRUE。这意味着外部查询中的这条记录将被包含在最终结果集中。

5.结果集：

最终，外部查询将返回所有满足条件的记录，即所有被至少一个其他员工记录中的Manager字段引用的员工记录。

3）

SELECT *  
FROM departments d  
WHERE EXISTS (  
    SELECT 1    
    FROM employees e    
    WHERE e.department_id = d.id  
);

SELECT * FROM departments d 是外部查询，

它从 departments 表中选择所有列，并且为这个表指定了一个别名 d。

WHERE EXISTS (...) 是外部查询的条件，它使用了 EXISTS 子句来测试子查询是否返回至少一行数据。

• SELECT 1 FROM employees e WHERE e.department_id = d.id 是子查询，

从 employees 表中选择一个常量值（在这个例子中是 1，但实际上可以是任何值，因为 EXISTS 只关心是否返回行），并且为 employees 表指定了一个别名 e。

这个子查询的条件是 e.department_id = d.id，它比较了 employees 表中的 department_id 列和外部查询中 departments 表的 id 列（通过别名 d 引用）。

因此，整个查询的意思是：

从 departments 表中选择所有行，但仅当这些部门在 employees 表中至少有一个对应的员工（即 employees 表中存在至少一行，其 department_id 等于 departments 表的 id）时。

（3）ANY 

SELECT column_name(s)

FROM table_name

WHERE column_name comparison_operator ANY (subquery);

其中，comparison_operator 可以是 =, >, <, >=, <=, <> 等比较运算符之一。

ANY操作符后面跟着一个子查询，该子查询返回一个单一列的结果集。

用于比较单个值与子查询返回的结果集中的任意一行。

使用场景：

比较单个值：当需要将一个特定的值与另一个表中的多个值进行比较时。

简化查询：在某些情况下，使用ANY可以简化查询，避免使用多个OR条件。

E.g.

1）

2）想查询薪水高于IT部门任何员工薪水的所有员工的名称和薪水，可以使用ANY操作符：

-- Employees 表
+-----------+----------+------------+
| employee_id | name     | salary     |
+-----------+----------+------------+
| 1         | Alice    | 50000      |
| 2         | Bob      | 60000      |
| 3         | Charlie  | 55000      |
+-----------+----------+------------+

-- Departments 表
+-------------+----------------+
| department_id | department_name |
+-------------+----------------+
| 1           | IT             |
| 2           | HR             |
| 3           | Marketing      |
+-------------+----------------+

SELECT name, salary
FROM Employees
WHERE salary > ANY (SELECT salary FROM Employees WHERE department_id = 1);

（4）ALL

SELECT column_name(s)

FROM table_name

WHERE column_name comparison_operator ALL (subquery);

ALL操作符后面跟着一个子查询，该子查询返回一个单一列的结果集。

用于比较单个值与子查询返回的所有行。

E.G.

假设Orders表，记录了不同客户的订单金额。

Customers表，记录了客户的信息。

想要找出那些订单金额都低于某个特定值的所有客户的名称。

-- Orders 表
+-------------+-----------+
| order_id    | customer_id | amount |
+-------------+-----------+-------+
| 1           | 101       | 100   |
| 2           | 102       | 200   |
| 3           | 103       | 50    |
| 4           | 101       | 150   |
| 5           | 104       | 300   |
+-------------+-----------+-------+
-- Customers 表
+-----------+----------+
| customer_id | name     |
+-----------+----------+
| 101       | Alice    |
| 102       | Bob      |
| 103       | Charlie  |
| 104       | David    |
+-----------+----------+

如果想要找出所有订单金额都低于200的所有客户的名称，可以使用以下SQL查询：

SELECT name
FROM Customers
WHERE 200 > ALL (
    SELECT amount 
    FROM Orders 
    WHERE Orders.customer_id = Customers.customer_id
);

执行逻辑：

- 对于Customers表中的每个客户，ALL子句会在Orders表中查找所有与该客户相关的订单金额。
- 200 > ALL部分检查200是否大于该客户的所有订单金额。
- 如果对于某个客户的所有订单金额都满足这个条件（即都小于200），那么这个客户的名称将被包括在查询结果中。

在这个例子中，只有Charlie的所有订单金额都低于200，所以他的名称会被返回：

+----------+
| name     |
+----------+
| Charlie  |
+----------+

二、总结

1、概念与使用

子查询是嵌套在另一个查询中的SQL语句，它可以返回单个值、多个值或一个结果集。

在FROM子句中使用子查询，作为另一个SELECT语句的临时表。

在WHERE子句中使用子查询，返回一个值或一组值，这些值用于外层查询的条件判断。

2、四个子查询操作符

ANY：比较——至少一个子查询值满足条件（逻辑 OR）。

ALL：比较——所有子查询值都满足条件（逻辑 AND）。

IN：检查相等性（是否相等）。

—— 外部值等于子查询集合中的任意一个值，或列表中的任意一个值。

        （逻辑 OR，用于每个可能的匹配项）。

EXISTS: 检查存在性（是否有行返回）。

—— 如果子查询返回至少一行数据，则 EXISTS 操作为真。