当前位置:   article > 正文

基础篇6、多表查询

多表查询


尚硅谷MySQL数据库教程-讲师:宋红康

我们缺乏的不是知识,而是学而不厌的态度


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

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

前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。

比如:员工表和部门表,这两个表依靠“部门编号”进行关联。

1.1、案例说明

在这里插入图片描述

从多个表中获取数据:

employees 表获取 employee_id 和 department_id 字段,departments 表获取 department_name 字段。

在这里插入图片描述

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

在这里插入图片描述

查询结果:

mysql> SELECT last_name, department_name FROM employees, departments;
+-------------+----------------------+
| last_name   | department_name      |
+-------------+----------------------+
| King        | Payroll              |
| King        | Recruiting           |
| King        | Retail Sales         |
| King        | Government Sales     |
| King        | IT Helpdesk          |
| King        | NOC                  |
| King        | IT Support           |
| King        | Operations           |
| King        | Contracting          |
| King        | Construction         |
| King        | Manufacturing        |
| King        | Benefits             |
| King        | Shareholder Services |
| King        | Control And Credit   |
| King        | Corporate Tax        |
| King        | Treasury             |
| King        | Accounting           |
| King        | Finance              |
| King        | Executive            |
| King        | Sales                |
| King        | Public Relations     |
| King        | IT                   |
| King        | Shipping             |
| King        | Human Resources      |
| King        | Purchasing           |
| King        | Marketing            |
| King        | Administration       |
......
| Gietz       | Administration       |
+-------------+----------------------+
2889 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
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

分析错误情况:

mysql> SELECT COUNT(employee_id) FROM employees;
+--------------------+
| COUNT(employee_id) |
+--------------------+
|                107 |
+--------------------+
1 row in set (0.01 sec)
#输出107行

mysql> SELECT COUNT(department_id)FROM departments;
+----------------------+
| COUNT(department_id) |
+----------------------+
|                   27 |
+----------------------+
1 row in set (0.00 sec)
#输出27行

mysql> SELECT 107*27 FROM dual;
+--------+
| 107*27 |
+--------+
|   2889 |
+--------+
1 row in set (0.00 sec)
#输出2889
  • 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

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

1.2、笛卡尔积(或交叉连接)的理解

笛卡尔乘积是一个数学运算。

假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。

组合的个数即为两个集合中元素个数的乘积数。

在这里插入图片描述

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、案例分析与问题解决

  • 笛卡尔积的错误会在下面条件下产生
    • 省略多个表的连接条件(或关联条件)
    • 连接条件(或关联条件)无效
    • 所有表中的所有行互相连接
  • 为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件
  • 加入连接条件后,查询语法:
#在 WHERE子句中写入连接条件。
SELECT table1.column, 
       table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件
  • 1
  • 2
  • 3
  • 4
  • 5
#案例:查询员工的姓名及其部门名称
mysql> SELECT last_name, department_name FROM employees JOIN departments WHERE employees.department_id = departments.department_id;
+-------------+------------------+
| last_name   | department_name  |
+-------------+------------------+
| Whalen      | Administration   |
| Hartstein   | Marketing        |
| Fay         | Marketing        |
| Raphaely    | Purchasing       |
......
| Gietz       | Accounting       |
+-------------+------------------+
106 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 在表中有相同列时,在列名之前加上表名前缀

2、多表查询分类讲解

2.1、等值连接 vs 非等值连接

2.1.1、等值连接

在这里插入图片描述

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
  • 6
  • 7

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/915410
推荐阅读
相关标签
  

闽ICP备14008679号