Mysql11——多表查询01

多表查询01

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

多表查询是数据库中一种常见的查询操作，它涉及对多个表进行联合查询，以获取更丰富的数据信息。通过多表查询，可以将不同表中的数据进行关联，从而通过一个查询语句获取到多个表的数据，并根据指定的条件进行筛选和排序。

多表查询常用于解决以下情况：

1. 需要查询相关联的数据：当数据分布在多个表中，并且这些表之间存在关联关系时，多表查询可以通过连接这些表来获取相关联的数据。
2. 需要跨表进行筛选和排序：通过多表查询，可以根据不同表中的字段进行复杂的筛选和排序操作，以获取满足特定条件的数据。
3. 需要进行数据统计和汇总：多表查询可以用于对多个表中的数据进行聚合、计算和汇总，以生成更全面的统计结果。

需要注意的是，在进行多表查询时，需要根据表之间的关系选择合适的连接方式（如JOIN、INNER JOIN、OUTER JOIN等），并指定连接条件，以保证查询结果的准确性和完整性。合理设计表结构和索引也有助于提高多表查询的性能。

1. 一个案例引发的多表连接

1.1 案例说明

数据来源表：

查询字段：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;
1
2
3

查询结果：

+-----------+----------------------+
| last_name | department_name      |
+-----------+----------------------+
| King      | Administration       |
| King      | Marketing            |
| King      | Purchasing           |
| King      | Human Resources      |
| King      | Shipping             |
| King      | IT                   |
| King      | Public Relations     |
| King      | Sales                |
| King      | Executive            |
| King      | Finance              |
| King      | Accounting           |
| King      | Treasury             |
...
| Gietz     | IT Support           |
| Gietz     | NOC                  |
| Gietz     | IT Helpdesk          |
| Gietz     | Government Sales     |
| Gietz     | Retail Sales         |
| Gietz     | Recruiting           |
| Gietz     | Payroll              |
+-----------+----------------------+
2889 rows in set (0.01 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

从查询结果中可以看到，一共查询到了2889条数据，远远多于实际数据。

分析错误情况：

SELECT COUNT(employee_id) FROM employees;
#输出107行

SELECT COUNT(department_id)FROM departments;
#输出27行

SELECT 107*27 FROM dual;
1
2
3
4
5
6
7

我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

1.2 笛卡尔积（或交叉连接）的理解

笛卡尔积是一个数学概念，用于描述多个集合之间的组合情况。它是由法国数学家笛卡尔（René Descartes）引入的。

在集合论中，给定两个集合 A 和 B，它们的笛卡尔积（Cartesian product）是一个集合，包含了所有可能的有序对，其中第一个元素来自集合 A，第二个元素来自集合 B。简而言之，笛卡尔积表示了两个集合之间的全排列组合。

记作：A × B = {(a, b) | a ∈ A, b ∈ B}

例如，如果集合 A = {1, 2}，集合 B = {3, 4}，那么它们的笛卡尔积就是 {(1, 3), (1, 4), (2, 3), (2, 4)}。

在数据库中，当涉及多个表之间的查询时，我们也可以使用笛卡尔积的概念。通过对表进行笛卡尔积操作，我们可以获取所有可能的组合结果。

需要注意的是，当表的记录数量较大时，进行笛卡尔积操作可能会导致结果集非常庞大，对系统性能和资源消耗造成负担。因此，在实际应用中，我们通常会根据具体需求使用更加有意义和有条件的连接方式，如内连接、外连接等，以获得更精确和符合需求的结果

数据库图解：

SQL92中，笛卡尔积也称为交叉连接，英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;
1
2
3
4
5

1.3 案例分析与问题解决

SQL中出现笛卡尔积是由于查询语句中没有指定合适的连接条件或使用了CROSS JOIN关键字，导致生成了表之间的笛卡尔积。其原因可以归结为以下几点：

1. 缺乏明确的连接条件：如果在多表查询语句中没有指定连接条件（如使用JOIN关键字并指定连接条件），数据库无法确定表之间的关联关系，就会默认生成笛卡尔积。这种情况通常是由于疏忽、错误或者忘记添加连接条件导致的。
2. 错误使用CROSS JOIN：CROSS JOIN是一种连接方式，它会对两个或多个表中的所有行进行组合，生成笛卡尔积。如果在查询语句中错误地使用了CROSS JOIN关键字，就会导致生成笛卡尔积而产生不必要的结果。
3. 数据库表之间的关系模型：在某些情况下，两个或多个表之间确实不存在明确的关联关系，需要获取表之间的所有可能组合。在这种情况下，笛卡尔积可能是有意生成的。

需要注意的是，生成笛卡尔积可能会导致结果集巨大，占用大量的计算资源和存储空间，造成性能问题。为了减少笛卡尔积的出现，应该在多表查询中明确指定连接条件，并合理设计表结构和索引，以避免不必要的笛卡尔积产生。

总结

• 笛卡尔积的错误会在下面条件下产生：

  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接

• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

• 加入连接条件后，查询语法：

  SELECT	table1.column, table2.column
  FROM	table1, table2
  WHERE	table1.column1 = table2.column2;  #连接条件
  1
  2
  3

  • 在 WHERE子句中写入连接条件。

• 正确写法：

  #案例：查询员工的姓名及其部门名称
  SELECT last_name, department_name
  FROM employees, departments
  WHERE employees.department_id = departments.department_id;
  1
  2
  3
  4

• 在表中有相同列时，在列名之前加上表名前缀。

2. 多表查询分类讲解

分类1：等值连接 vs 非等值连接

等值连接

两表联查：

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;
1
2
3
4
5

注意1：多个连接条件使用 AND 操作符

注意2：区分重复的列名

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用表名加以区分。

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
1
2
3

注意3：表的别名

当表的名字很长或者执行一些特殊的查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名替代表原来的名称。

• 使用别名可以简化查询。

• 列名前使用表名前缀可以提高查询效率。

  语法格式：

  表名 AS 表别名

  • "表名"为数据库中存储的数据表的名称
  • "表别名"为查询时指定的表的新名称，AS关键字为可选参数

SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;
1
2
3
4

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

阿里开发规范：

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或 表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在 某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column ‘name’ in field list is ambiguous。

注意4：连接多个表

**总结：连接 n个表,至少需要n-1个连接条件。**比如，连接三个表，至少需要两个连接条件。

实例操作：查询出公司员工的 last_name,department_name, city

SELECT e.last_name,d.department_name,l.city
FROM   
	employees as e , 
	departments as d,
	locations as l
WHERE  
	e.department_id = d.department_id 
AND 
	d.location_id = l.location_id;
1
2
3
4
5
6
7
8
9

非等值连接

在 MySQL 中，非等值连接（non-equi join）是指在多表查询中，使用不等于（<>, !=, <, >, <=, >=）等操作符来建立连接条件的一种连接方式。与等值连接（equi join）不同，非等值连接不仅仅依赖于相等关系，而是基于其他比较关系进行连接。

非等值连接在某些情况下可以解决一些特定的查询需求，例如：

1. 范围比较：当需要根据一个范围进行连接时，可以使用非等值连接。例如，查询一个表中某个列的值在另一个表的两个列的范围内的数据。
2. 基于条件连接：当连接条件需要满足一定的条件表达式时，可以使用非等值连接。例如，查询满足某个条件表达式的数据。
3. 多重条件连接：当连接条件涉及多个字段或多个条件时，可以使用非等值连接。例如，查询某表中的数据，在另一个表中匹配多个字段的组合。

非等值连接可以通过在多表查询中使用WHERE子句来实现，常用的非等值连接操作符包括：<>, !=, <, >, <=, >=。例如：

SELECT *
FROM table1
JOIN table2 ON table1.column1 <> table2.column2
1
2
3

需要注意的是，非等值连接可能会导致查询结果不准确或产生重复数据，因此在使用非等值连接时应谨慎，并确保连接条件和查询逻辑的正确性。同时，非等值连接可能会对性能产生一定的影响，因此在设计数据库时，应尽量避免过多或复杂的非等值连接操作。

案例演示：

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;
1
2
3

分类2：自连接 vs 非自连接

• 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

题目：查询employees表，返回“Xxx works for Xxx”

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;
1
2
3
4

练习：查询出last_name为 ‘Chen’ 的员工的 manager 的信息。

SELECT 
	emp.employee_id ,
	emp.last_name,
	mgr.employee_id,
	mgr.last_name
from 
	employees emp,	
	employees mgr
WHERE 
	emp.manager_id = mgr.employee_id
AND emp.last_name = "chen";
1
2
3
4
5
6
7
8
9
10
11

分类3：内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

• 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

  如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
  \Typora\typora-user-images\1693800130350.png" alt=“1693800130350” style=“zoom:67%;” />

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

• 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

  如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。