Mysql多表查询

多表查询

多表查询的核心是通过某种条件将多个表的数据关联起来。常见的关联方式包括

  • 连接（JOIN）：通过列值匹配将多个表的行组合在一起。
  • 子查询（Subquery）：将一个查询的结果作为另一个查询的条件或数据源

连接

连接是 MySQL 中最常用的多表查询方式。它通过列值匹配将多个表的行组合在一起。

  1. 交叉连接（CROSS JOIN）

    • 交叉连接（CROSS JOIN） 是一种多表连接方式，它会返回两个表的笛卡尔积。

    • 它会将第一个表的每一行与第二个表的每一行进行组合，生成的结果集的行数是两个表行数的乘积

    • 交叉连接的两种语法：

      SELECT *
FROM table1
CROSS JOIN table2;

      第二种写法是隐式的交叉连接，不推荐使用，因为它容易与其他连接混淆

      SELECT *
FROM table1, table2;

  2. 内连接（INNER JOIN）

    • 用于根据指定的连接条件，从两个或多个表中返回满足条件的行

    • 内连接的核心思想是只保留那些在两个表中都有匹配数据的行

    • 语法

      SELECT table1.columns,table2.columns,
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

      • 内连接需要一个连接条件（通常使用 ON 子句），用于指定两个表之间的关联规则

      • 只有满足连接条件的行才会被包含在结果集中

      • 内连接可以用于连接多个表

        SELECT *
FROM table1
INNER JOIN table2 ON table1.column = table2.column
INNER JOIN table3 ON table2.column = table3.column;

    • 使用别名，为了简化查询，可以使用表别名

      SELECT s.Name, c.CourseName
FROM Students s
INNER JOIN Courses c 
ON s.CourseID = c.CourseID;

  3. 自连接（Self-Join）

    • 自连接的核心思想是将一张表视为两张独立的表，通过别名区分，然后根据某些条件进行连接

    • 自连接的语法与普通连接类似，只是需要对同一张表使用不同的别名

      SELECT t1.column1, t2.column2, ...
FROM table_name t1
JOIN table_name t2
ON t1.common_column = t2.common_column;

  4. 左连接（LEFT JOIN 或 LEFT OUTER JOIN）

    • 左外连接会返回左表（LEFT JOIN 左侧的表）的所有行，以及右表中满足连接条件的行

    • 如果右表中没有匹配的行，则右表的列会以 NULL 填充

    • 要求返回某个表中的全部数据时就可以使用左外连接

    • 基本语法

      SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

  5. 右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）

  6. 全连接（FULL JOIN 或 FULL OUTER JOIN）

    • 用于返回两个表中所有行的组合。它会保留左表和右表中的所有行，即使某些行在另一个表中没有匹配的行
    • 对于没有匹配的行，缺失的列会以 NULL 填充
    • MySQL 中，FULL OUTER JOIN（全连接） 并不是直接支持的语法
    • 可以通过结合 LEFT JOIN、RIGHT JOIN 和 UNION 来实现全连接的功能

    实现方式

    1. 使用 LEFT JOIN 获取左表的所有行以及右表中匹配的行。

    2. 使用 RIGHT JOIN 获取右表的所有行以及左表中匹配的行。

    3. 使用 UNION 将两个结果集合并，并去重

      SELECT *
FROM table1
LEFT JOIN table2 ON table1.column = table2.column
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

    UNION

    • UNION 会自动去重。如果不需要去重，可以使用 UNION ALL
    操作符 描述
    UNION 合并结果集并去除重复行。
    UNION ALL 合并结果集并保留重复行。

    要使用 UNION，两个 SELECT 语句必须满足以下条件：

    • 列数相同：两个 SELECT 语句返回的列数必须一致。

    • 数据类型兼容：对应列的数据类型必须兼容

    • 如果尝试 UNION 两个不同的字段（即列名不同），但列数和数据类型兼容，SQL 会执行合并，结果集的列名通常来自第一个 SELECT 语句

子查询

  • 子查询是将一个查询的结果作为另一个查询的条件或数据源。它可以嵌套在 SELECT、FROM、WHERE、HAVING 等子句中或者作为其他子查询的一部分。

  • 子查询的结果可以是一个值（标量子查询）、一行（行子查询）、一列（列子查询）或一个表（表子查询）

  1. 标量子查询

    • 返回单个值（一行一列）

    • 可以用于 SELECT、WHERE、HAVING、ORDER BY、INSERT、UPDATE 等子句中

    • 标量子查询必须用括号 () 括起来

    • 标量子查询可以嵌套在 SQL 语句的多个部分中

      • 在 SELECT 子句中使用标量子查询

        SELECT Name, Salary, (SELECT AVG(Salary) FROM Employees) AS AvgSalary
FROM Employees;
查询每个员工的工资以及公司平均工资

      • 在 WHERE 子句中使用标量子查询

        SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
查询工资高于公司平均工资的员工

      • 在 HAVING 子句中使用标量子查询

        查询订单总金额高于平均订单总金额的客户
SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > (SELECT AVG(TotalAmount)
                      FROM (SELECT SUM(Amount) AS TotalAmount
                            FROM Orders
                            GROUP BY CustomerID) AS CustomerTotals);

      • 在 ORDER BY 子句中使用标量子查询

        查询所有员工，并按部门平均工资排序
SELECT Name, Salary, DepartmentID
FROM Employees
ORDER BY (SELECT AVG(Salary) FROM Employees e2 WHERE e1.DepartmentID = e2.DepartmentID);

  2. 列子查询

    • 返回一列数据（多行一列）

    • 通常用于 IN、ANY、ALL 等操作符中

    • 或者在 SELECT 子句中使用。列子查询的结果是一个集合，可以与外部查询的列进行比较或操作

      • 使用 IN 操作符

        查询所有在 IT 部门的员工
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'IT');

      • 使用 ANY 操作符

        查询工资高于 IT 部门任一员工工资的员工
SELECT Name, Salary
FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE DepartmentID = 2);

      • 使用 ALL 操作符

        查询工资高于 IT 部门所有员工工资的员工
SELECT Name, Salary
FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE DepartmentID = 2);

    • 在 SELECT 子句中使用列子查询

      • 列子查询可以作为查询结果的一列

        查询每个员工的工资以及 IT 部门的平均工资
SELECT Name, Salary, (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = 2) AS ITAvgSalary
FROM Employees;

  3. 行子查询

    • 返回一行数据（一行多列）

    • 通常用于 WHERE 子句中的行比较

    • 也可以用于 INSERT、UPDATE 语句中

      • 在 WHERE 子句中使用行子查询

      • 行子查询可以用于行比较，通常与比较运算符一起使用

        • 使用 = 操作符

          查询与 Alice 工资和部门相同的员工
SELECT Name, Salary, DepartmentID
FROM Employees
WHERE (Salary, DepartmentID) = (SELECT Salary, DepartmentID FROM Employees WHERE Name = 'Alice');

        • 使用 IN 操作符

          查询工资和部门与 Alice 或 Bob 相同的员工
SELECT Name, Salary, DepartmentID
FROM Employees
WHERE (Salary, DepartmentID) IN (SELECT Salary, DepartmentID FROM Employees WHERE Name IN ('Alice', 'Bob'));

        • 在 INSERT和UPDATE 语句中使用行子查询

          将 NewEmployees 中的数据插入到 Employees 表中
INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)
SELECT EmployeeID, Name, Salary, DepartmentID
FROM NewEmployees;

将 Alice 的工资和部门更新为与 Bob 相同
UPDATE Employees
SET (Salary, DepartmentID) = (SELECT Salary, DepartmentID FROM Employees WHERE Name = 'Bob')
WHERE Name = 'Alice';

  4. 表子查询

    • 返回一个表（多行多列）

    • 通常用于 FROM 子句或 JOIN 中，作为临时表

    • 基本语法

      • 表子查询必须用括号 () 括起来
      • 表子查询必须有一个别名（AS alias），以便外部查询可以引用它
      SELECT column1, column2, ...
FROM (SELECT column1, column2, ... FROM table_name WHERE condition) AS alias
WHERE condition;

    • 用在from子句中

      SELECT CustomerID, TotalAmount
FROM (SELECT CustomerID, SUM(Amount) AS TotalAmount
      FROM Orders
      GROUP BY CustomerID) AS CustomerTotals
where ...

    • 用在join子句中

      SELECT c.Name, ct.TotalAmount
FROM Customers c
JOIN (SELECT CustomerID, SUM(Amount) AS TotalAmount
      FROM Orders
      GROUP BY CustomerID) AS ct
ON c.CustomerID = ct.CustomerID;
posted @ 2025-03-17 21:32  QAQ001  阅读(56)  评论(0)    收藏  举报