MySQL 多表查询

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

一.多表查询的前提条件

• 这些一起查询的表之间是有关联的(一对一,一对多),它们之间一定是有关联字段的,这个关联字段可能建立了外键,也可能没有

如:

员工表和部门表,依靠"部门编号"进行关联

• 案例: 有三个表,分别为Employees,Departments,Locations

以我们现有的知识,想查找一个员工的location_id,就得从Employees查询到department_id,再从Departments中查询到location_id,然后再从Locations中查询到location_id

SELECT *
FROM employees
WHERE last_name = 'Abel';
​
SELECT *
FROM departments
WHERE department_id = 80;
​
SELECT *
FROm location_id = 2500;

这种方式我们虽然查到了想要知道的结果,但是查询过程却十分麻烦.第一次查询服务器先向数据库发送指令,指明查询的人,数据库查询后返回其查询到的department_id信息;服务器接收到处理后再将department_id发送给数据库,数据库用这个信息查询出location_id,再将其返回给服务器,服务器接收到处理后,再向数据库发送location_id,数据库查询后才能返回结果.如此查询十分复杂,效率低下.因此,我们需要一种便捷的查询方式

• 问: 为什么不将所有的数据放在一个表中,这样岂不是可以直接查到?

当遇到大量数据需要存储时,放在一张表中查询时非常不方便,而且在遇到有空字段存储时造成大量空间的浪费,况且一张表存储过多的数据会导致查询时IO交互次数非常多,效率低下.因此,使用多表查询十分方便

二.多表查询的实现

1.错误方式: 每个员工都与每个部门匹配了一遍

SELECT employee_id,department_name
FROM employees,departments;

注: 此方式出现了笛卡尔积的错误,原因是缺少了多表的连接条件

2.笛卡尔积(也称交叉连接)的理解

笛卡尔积是一个数学运算,假设我们有两个集合x和y,那么x和y的笛卡尔积就是x和y的所有可能出现的组合,也就是一个对象来自于x,另一个对象来自于y的所有可能.组合的个数即为两个集合中元素个数的乘积数.

在SQL中,笛卡尔积也称为交叉连接,英文为CROSS JOIN .作用就是把任意表进行连接,即使这两张表不关联

3.分析

• 笛卡尔积的错误条件:

  • 省略多个表的连接条件(或者没有关联条件)

  • 连接条件无效

  • 所有表中的所有行互相连接

• 为了避免出现笛卡尔积的错误,我们需要在WHERE语句中加入有效的连接条件

4.正确的方式: 需要有连接条件

加入连接条件后的查询语法

SELECT *
FROM 
WHERE table1.column1 = table2.column2;

#两表连接

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;

• 当出现多表中有同一字段时,不能直接查询,应在SELECT语句中注明是哪一张表的字段

  SELECT employee_id,employees.department_id
  FROM employees,departments
  WHERE employees.`department_id` = departments.department_id;

  建议:

  从SQL优化的角度出发,建议多表查询时,每个字段都指明其所在的表

• 可以给表起别名,在SELECT语和WHERE中使用,方便查询,提高语句的可读性.给表起别名,类似于给字段起别名,但是起了别名之后,就必须在SELECT和WHERE语句中使用表的别名,而不能继续使用表的原名,因为起了别名后将覆盖了表原有的名称.

  SELECT t1.employee_id,t2.department_name
  FROM employees t1,departments t2
  WHERE t1.`department_id` = t2.department_id;

• 练习:

  查询员工的employee_id,last_name,department_name,city

  SELECT employee_id,last_name,department_name,city
  FROM employees e,departments d,location l
  WHERE e.`department_id` = d.department_id
  AND d.`location_id` = l.`location_id`;

  注意: 当有n个表查询时,则至少需要n-1个连接条件