MySQL_05多表查询

🔍 **MySQL 多表查询全解析，破解数据关联的奥秘！** 🚀 当单表查询无法满足需求，多表查询便成为数据库开发的核心技能。本思维导图全面解析 **MySQL 多表查询技巧**，助你轻松驾驭复杂数据关联。

第05章_多表查询

• 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外健，也可能没有建立外健。
• 1. 多表连接笛卡尔积
  • 1.1 笛卡尔积（或交叉连接）的理解
    • 笛卡尔乘积是一个数据运算。假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积数。
    • SQL92中，笛卡尔积也称为交叉连接，英文是CROSS JOIN。在SQL99中也是使用CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积： SELECT * FROM table1, table2; SELECT * FROM table1 CROSS JOIN table2; SELECT * FROM table1 INNER JOIN table2; SELECT * FROM table1 JOIN table2;
  • 1.2 笛卡尔积的产生与避免
    • 笛卡尔积的错误会在下面条件下产生：
      • 省略多个表的连接条件
      • 连接条件（或关联条件）无效
      • 所有表中的所有行互相连接
    • 为了避免笛卡尔积，可以在WHERE加入有效的连接条件。
    • 加入连接条件后，查询语法：
      • SELECT table1.column, table2.column FROM table1, table2 WHERE table.column1 = table2.colum2; #连接条件
      • 在WHERE子句中写入连接条件
• 2. 多表查询分类讲解
  • 2.1 分类1：等值连接 vs 非等值连接
    • 等值连接 SELECT cols FROM A表 a, B表 b WHERE a.col = b.col;
    • 非等值连接 SELECT cols FROM A表 a, B表 b WHERE a.col > b.col;
  • 2.2 分类2：自连接 vs 非自连接
    • --自连接 SELECT cols FROM A表 a1, A表 a2 WHERE a1.col1 = a2.col1; --非自连接 SELECT cols FROM A表 a, B表 b WHERE a.col = b.col;
  • 2.3 分类3：内连接 vs 外连接
    • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个不匹配的行。
    • 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空(NULL)。
    • 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
    • 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
• 3. SQL99语法实现多表查询
  • 3.1 基本语法
    • SELECT 字段列表 FROM A表 JOIN B表 ON 关联条件 WHERE 等其他子句;
  • 3.2 内连接(INNER JOIN)的实现
    • SELECT 字段列表 FROM A表 INNER JOIN B表 ON 关联条件 WHERE 等其他子句;
  • 3.3 外连接(OUTER JOIN)的实现
    • 左外连接(LEFT OUTER JOIN)
      • SELECT 字段列表 FROM A表 LEFT JOIN B表 ON 关联条件 WHERE 等其他子句; SELECT cols FROM tableA LEFT OUTER JOIN tableB ON (tableA.cola = tableB.colb)
    • 右外连接(RIGHT OUTER JOIN)
      • SELECT 字段列表 FROM A表 RIGHT JOIN B表 ON 关联条件 WHERE 等其他子句;
    • 注意：LEFT JOIN 和 RIGHT JOIN 只存在于SQL99及以后的标准中，在SQL92中不存在，只能用(+)表示。
    • 满外连接(FULL OUTER JOIN)
      • 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
      • SQL99是支持满外连接的。使用FULL OUTER JOIN来实现。
      • 需要注意的是，MySQL不支持FULL JOIN，但是可以用LEFT JOIN UNION RIGHT JOIN代替。
• 4. UNION的使用
  • 合并查询结果
    • 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
    • 语法格式：
      • SELECT col,... FROM table1 UNION [ALL] SELECT col,... FROM table2
  • UNION操作符
    • UNION操作符返回两个查询的结果集的并集，去除重复记录。
  • UNION ALL 操作符
    • UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
    • 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
• 7. 章节小结
  • 表连接的约束条件可以有三种方式：WHERE，ON，USING。
    • WHERE：适用于所有关联查询
    • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
    • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。
  • 注意：我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。
• 6. SQL 99语法新特性
  • 6.1 自然连接
    • SQL99在SQL92的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
    • 在SQL92标准中：
      • SELECT a.col, b.col FROM table1 a JOIN table2 b ON a.col1 = b.col1 AND a.col2 = b.col2;
    • 在SQL99中可以写成：
      • SELECT a.col, b.col FROM table1 a NATURAL JOIN table2 b;
  • 6.2 USING连接
    • SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接，但是只能配合JOIN一起使用。
      • SELECT a.col, b.col FROM table1 a JOIN table2 b USING (col)
    • 与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，在 USING 的括号()中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。
• 5. 7种SQL JOINS的实现