MySQL多表查询详解

多表查询（连接）

在MySQL中，多表查询是指同时从多个表中检索数据并根据表之间的关联关系进行联接（Join）。通过多表查询，可以获取跨多个表的相关数据，实现复杂的查询需求。

以下是几种常见的多表查询方法：

1. 内连接（Inner Join）：内连接用于从多个表中仅返回匹配的行。它基于两个或多个表之间的关联条件，仅返回在所有关联表中都存在匹配的行。语法如下：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
1
2
3

2. 左连接（Left Join）：左连接用于返回左表中的所有行，以及与右表中匹配的行。如果右表中没有匹配的行，则右表的列将显示为NULL。语法如下：

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

root@tennis 19:23 mysql>select pla.playerno,pla.name,pen.amount
    -> from PLAYERS pla
    -> left join PENALTIES pen
    -> using(playerno);
+----------+-----------+--------+
| playerno | name      | amount |
+----------+-----------+--------+
|        6 | Parmenter | 100.00 |
|       44 | Baker     |  75.00 |
|       27 | Collins   | 100.00 |
|      104 | Moorman   |  50.00 |
|       44 | Baker     |  25.00 |
|        8 | Newcastle |  25.00 |
|       44 | Baker     |  30.00 |
|       27 | Collins   |  75.00 |
|        2 | Everett   |   NULL |
|        7 | Wise      |   NULL |
|       28 | Collins   |   NULL |
|       39 | Bishop    |   NULL |
|       57 | Brown     |   NULL |
|       83 | Hope      |   NULL |
|       95 | Miller    |   NULL |
|      100 | Parmenter |   NULL |
|      112 | Bailey    |   NULL |
+----------+-----------+--------+
17 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3. 右连接（Right Join）：右连接用于返回右表中的所有行，以及与左表中匹配的行。如果左表中没有匹配的行，则左表的列将显示为NULL。语法如下：

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
1
2
3

4. 全连接（Full Join）：全连接用于返回左表和右表中的所有行，无论是否存在匹配的行。如果某个表中没有匹配的行，则对应的列将显示为NULL。MySQL不直接支持全连接，但可以使用左连接和右连接的组合来实现全连接效果。

5. 交叉连接（Cross Join）(笛卡尔积)：交叉连接用于返回两个表的笛卡尔积，即将左表的每一行与右表的每一行进行组合。语法如下：

SELECT columns
FROM table1
CROSS JOIN table2;
1
2
3

在多表查询中，通常需要使用关联条件（ON）来指定表之间的连接关系。关联条件定义了用于匹配行的列或表达式。根据具体的业务需求和表之间的关联关系，可以选择合适的连接类型和关联条件。

ANTURAL JOIN

在MySQL中，NATURAL JOIN是一种表连接操作，它根据两个表之间的隐式关系自动匹配列，并返回匹配的结果集。它是一种简化连接语法，适用于具有相同列名的表之间的连接。

NATURAL JOIN不需要显式指定连接条件，而是根据两个表的相同列名自动进行匹配。它将返回两个表中具有相同列名的行，并排除重复的列。这意味着如果两个表中有多个相同列名，则只返回一个列，并且在结果中不重复。

以下是使用NATURAL JOIN的基本语法：

SELECT *
FROM table1
NATURAL JOIN table2;
1
2
3

在上述语法中，table1和table2是要进行自然连接的两个表名。查询将根据两个表中具有相同列名的行进行匹配，并返回结果集。

请注意以下几点：

1. NATURAL JOIN只能匹配具有相同列名的表列。如果两个表没有相同的列名，将无法进行自然连接。
2. NATURAL JOIN可能导致意外的结果，因为它依赖于列名的匹配。如果表结构发生变化或新增了其他相同列名的列，可能会影响连接结果。
3. 在某些情况下，使用NATURAL JOIN可能会导致性能下降，因为它需要在查询过程中隐式匹配列。在大型表或复杂查询中，考虑使用显式的连接条件来获得更精确的控制和更好的性能。

综上所述，NATURAL JOIN提供了一种简化连接的方式，适用于具有相同列名的表之间的连接操作。但是，要小心使用它，并确保理解其可能的结果和潜在的性能影响。

JOIN

在MySQL的JOIN语句中，USING和ON是用于指定连接条件的两种不同方式。

1. USING关键字：

   • USING关键字用于指定连接的列，这些列在连接的两个表中具有相同的名称。
   • USING关键字仅用于指定连接的列，而不需要在列名前面指定表名或表别名。
   • USING关键字将根据指定的列进行连接，并自动排除重复列。

   以下是使用USING关键字的示例：

   SELECT *
   FROM table1
   JOIN table2 USING (common_column);
   1
   2
   3

   在上述示例中，table1和table2是要连接的两个表，common_column是连接的共同列名。

2. ON关键字：

   • ON关键字用于指定连接的条件，可以根据多个列或表达式进行连接。
   • ON关键字要求在连接条件中明确指定连接的列，包括表名或表别名。
   • ON关键字提供了更灵活的连接选项，可以在连接条件中使用各种逻辑运算符、比较运算符和函数。

   以下是使用ON关键字的示例：

   SELECT *
   FROM table1
   JOIN table2 ON table1.column1 = table2.column2;
   1
   2
   3

   在上述示例中，table1和table2是要连接的两个表，column1和column2是连接的列名，并在ON子句中进行了比较运算符的指定。

请注意，USING和ON关键字是JOIN语句中连接条件的两种不同表示方式，具体使用哪种方式取决于具体的需求和表结构。在某些情况下，使用USING关键字可能更简洁和直观，特别是在连接具有相同列名的表时。而使用ON关键字则更灵活，允许进行更复杂的连接条件。

交叉连接

需要两个或多个表之间存在相同的字段。

部门表department

CREATE TABLE dept(
  deptid INTEGER  primary key,
  dname VARCHAR(20)
);
1
2
3
4

插入部门信息

insert into dept(deptid,dname) values(10,"市场部"),values(20,"销售部"),values(30,"devops");
1

员工表employee

CREATE TABLE emp(
  id INTEGER,
  NAME VARCHAR(20),
  deptid INTEGER,
  CONSTRAINT emp_id_pk PRIMARY KEY(id), 
  CONSTRAINT emp_deptid_fk FOREIGN KEY(deptid) REFERENCES dept(deptid)
);
1
2
3
4
5
6
7

插入员工数据

INSERT INTO emp(id,NAME,deptid) VALUES(1,'张三',10),(2,'李四',10); 
1

交叉连接

select * from emp cross join dept;
#或
select * from emp,dept;

1
2
3
4

结果

root@date_8 16:25 mysql>select * from emp cross join dept;
+----+--------+--------+--------+-----------+
| id | NAME   | deptid | deptid | dname     |
+----+--------+--------+--------+-----------+
|  1 | 张三   |     10 |     10 | 市场部    |
|  2 | 李四   |     10 |     10 | 市场部    |
|  1 | 张三   |     10 |     20 | 销售部    |
|  2 | 李四   |     10 |     20 | 销售部    |
|  1 | 张三   |     10 |     30 | devops    |
|  2 | 李四   |     10 |     30 | devops    |
+----+--------+--------+--------+-----------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

自连接

当前表与自身的连接查询，自连接必须使用表别名

在MySQL中，自连接（Self-Join）是一种特殊的表连接操作，它将表与自身进行连接。自连接通常用于解决与同一表中的数据之间的关系和层次结构有关的查询问题。

自连接的基本思想是将一个表视为两个独立的表，然后使用连接条件将它们连接起来。这样可以比较同一表中的不同行，实现对数据之间的比较和关联。

以下是一个自连接的示例，假设我们有一个名为employees的表，包含员工的ID、姓名和经理ID：

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
1
2
3

在上述示例中，我们使用employees表进行自连接。我们通过将e1和e2作为表的别名来表示同一表的两个实例。然后，我们使用连接条件e1.manager_id = e2.employee_id将两个表连接起来，将员工与他们的经理进行匹配。

自连接可以用于解决许多问题，如获取员工及其经理的关系、查找员工的下属、构建组织结构图等。通过自连接，我们可以以递归的方式在同一表中进行关系查询和层次结构导航。

请注意以下几点：

1. 在自连接中，使用别名来区分同一表的不同实例是必要的，以避免列名冲突。
2. 自连接可以通过多个连接条件进行更复杂的查询。您可以根据实际需求指定适当的连接条件。
3. 自连接可能会导致较大的结果集和较高的查询复杂性，因此在使用时要小心性能问题。