09 MySQL中的多表查询

骑猪看日落 2023-09-28 12:09 103阅读 0赞

**目录**

多表查询含义：从多个表中获得数据；

一、笛卡尔乘积 ：（应避免笛卡尔乘积）

二、等值连接：（条件中用‘=’号连接，如 where e.id = d.id;）

1.等值连接特点：

注意：

2.使用表别名

2.1表别名定义原则：

3.多表连接条件：

注意：多表查询中要用“表名.列名”，方便区分和查询

注意：

三、非等值连接：

1.非等值连接定义：

四、自连接：

1.自连接定义：连接一个表到它自己

五、交叉连接：（SQL99-1999年）（基本不使用）

六、自然连接（工作中用的少）

1.自然连接定义：

2.自然连接也可以被写为等值连接

七、内连接（SQL99中的内连接(INNER JOIN)）

1.语法：

2.用ON子句指定连接条件：

3.用ON子句指定更多的连接条件：

八、外连接

注意：MySQL不支持全外连接

1.左外连接(LEFT OUTER JOIN)

左外连接定义：

2.右外连接：

右外连接定义：

3.全外连接：

注意： MySQL 中不支持 FULL OUTER JOIN 连接，但可以使用 union 实现全外连接。

1.UNION（不包含重复数据）:

2.UNION ALL（包含重复数据）:

语法结构：

--------------------

## **多表查询含义：从多个表中获得数据；** ##

> ## **一、****笛卡尔乘积 ：****（应避免笛卡尔乘积）** ##
> 
> ![08be77368ca24e96ac9d038402e7f333.png][]
> 
> **当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积**
> 
> (Cartesian product)，其中所有行的组合都被显示。第一个表中的所
> 
> 有行连接到第二个表中的所有行。**一个笛卡尔乘积会产生大量的**
> 
> **行，其结果没有什么用。**你应该在 WHERE 子句中始终包含一个有
> 
> 效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的
> 
> 行。

> ## **二、等值连接：****（条件中用‘=’号连接，如 where e.id = d.id;）** ##
> 
> ![ed6ddbb901724f2f95b5f1d3eab940d7.png][]
> 
> ### **1.****等值连接特点：** ###
> 
> **（****1****）****多表等值连接的结果为多表的交集部分**；
> 
> （2） **n****张****表连接，至少需要n-1个连接条件**；
> 
> **（3）****多表不分主次，没有顺序要求；**
> 
> **（4）****一般为表起别名，提高阅读性和性能；**
> 
> **（5）****可以搭配排序、分组、筛选….等子句使用；**
> 
> #### **注意：** ####
> 
> **等值连接也被称为简单连接 (simple joins) 或内连接 (inner****joins)。**
> 
> ### **2.使用表别名** ###
> 
> ![6341311cffc84f1eaee347c30fd65e65.png][]
> 
> #### **2.1****表别名定义原则****：** ####
> 
> 表别名不易过长，短一些更好。
> 
> 表别名应该是有意义的。
> 
> **表别名只对当前的 SELECT 语句有效。**
> 
> ### **3.多表连接条件：** ###
> 
> ![c0cfa8c748ef419d8f7df138135c400a.png][]
> 
> #### **注意：****多表查询中要用“表名.列名”，方便区分和查询** ####
> 
> 例：select d.department\_name from employees e,departments d where e.dept\_id = d.department\_id and e.last\_name = 'King';
> 
> 表示：**查询雇员 King 所在的部门名称**。
> 
> 例；SELECT e.last\_name, d.department\_name, l.city FROM employees e, departments d, locations l **WHERE e.department\_id = d.department\_id** **AND d.location\_id = l.location\_id**;
> 
> 表示：显示每个雇员的 last name、departmentname 和 city。
> 
> #### **注意：** ####
> 
> 在等值连接中，**不给where条件即为等值连接**，**系统会自动匹配相同列相同值**，如：select \* from employees,departments;

> ## **三、非等值连接：** ##
> 
> ### **1.****非等值连接****定义：** ###
> 
> **不是用‘=’号连接的，如>、<、between and等**
> 
> **一个非等值连接是一种使用不等于作为连接条件**
> 
> 一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES
> 
> 表 和JOB\_GRADES A 表之间的关系有一个非等值连接例子。在两个
> 
> 表之间的关系是EMPLOYEES 表中的 SALARY 列必须是JOB\_GRADES 表的 LOWEST\_SALARY 和HIGHEST\_SALARY 列之间
> 
> 的值。使用不同于等于 (=) 的操作符获得关系。
> 
> 例：select e.last\_name,j.grade\_level from employees e ,job\_grades j where e.salary between j.lowest\_sal and j.highest\_sal;
> 
> 表示:查询所有雇员的薪水级别。

> ## **四、自连接：** ##
> 
> ### **1.****自连接****定义：****连接一个表到它自己** ###
> 
> 有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执行一个自连接。
> 
> ![79d63c017cec448cb433e7b44478a73b.png][]
> 
> ![8bec8ef3a39d46a9985cedd7b38714da.png][]
> 
> 图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表**，对于相同的表 EMPLOYEES，用两个别名，分别为worker 和 manager。**在该例中，**WHERE 子句包含的连接意味着“一个工人的经理号匹配该经理的雇员号**”。
> 
> 例：SELECT worker.LAST\_NAME W,manager.LAST\_NAME M from **employees worker,employees manager** where **worker.MANAGER\_ID =manager.EMPLOYEE\_ID;**
> 
> 表示：查询每个雇员的经理的名字以及雇员的名字，**雇员名字列别名为W经理列别名为M。**
> 
> 例：SELECT worker.LAST\_NAME,manager.LAST\_NAME from employees **worker**,employees **manager** where worker.MANAGER\_ID = manager.EMPLOYEE\_ID AND worker.LAST\_NAME = 'Fox';
> 
> 表示：查询Fox的经理是谁？显示他的名字。

> ## **五、交叉连接：（SQL99-1999年）（基本不使用）** ##
> 
> ![d91e82eec40c40a495c1ed0c16f01206.png][]
> 
> 例：select \* from employees cross join departments;
> 
> 表示：使用交叉连接查询 employees 表与 departments 表。

> ## **六、自然连接（工作中用的少）** ##
> 
> ![0d05b58511fc446dadcdd31882801739.png][]
> 
> ### **1.****自然连接****定义：** ###
> 
> 连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。
> 
> ![2c145498e7fd422fa47621a01db85824.png][]
> 
> ### **2.自然连接也可以被写为等值连接** ###
> 
> （图片中的自然连接等效于下面的等值连接）：
> 
> SELECT d.department\_id, d.department\_name, d.location\_id , l.city FROM departments d , locations l WHERE d.location\_id = l.location\_id;
> 
> 例：select e.last\_name,d.department\_name **from employees e natural join departments d;**
> 
> 表示：使用**自然连接查询**所有有部门的雇员的名字以及部门名称。

> ## **七、内连接（SQL99中的内连接(INNER JOIN)）** ##
> 
> ![92edbf282e70447295ff604453c56108.png][]
> 
> ### **1.语法：** ###
> 
> SELECT 查询列表;
> 
> FROM 表1 别名;
> 
> INNER JOIN 连接表(INNER关键字可省略);
> 
> ON 连接条件;
> 
> ### **2.****用ON子句指定连接条件****：** ###
> 
> ![024743d4e8b34b299355dc3bb2c9e991.png][]
> 
> ### **3.****用ON子句指定更多的连接条件****：** ###
> 
> ![d7946bfa04d14591a46e05f374689bf3.png][]
> 
> **INNER JOIN... ON....**
> 
> 例：select e.employee\_id,e.salary,d.department\_name from employees e inner JOIN departments d on e.department\_id = d.department\_id where e.last\_name = 'Fox';
> 
> 表示：查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

> ## **八、外连接** ##
> 
> ![1c44d8d48e8544a68dd2c9df815cd019.png][]
> 
> ### **注意：MySQL不支持全外连接** ###
> 
> **孤儿数据(Orphan Data)**
> 
> **孤儿数据是指**被连接的列的值为空的数据**（不匹配的数据）**。

> ### **1.左外连接(LEFT OUTER JOIN)** ###
> 
> ![656512ed6763469a842414c7195fd47c.png][]
> 
> #### **左外连接定义：** ####
> 
> **左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的**
> 
> **行，该查询也会取回 EMPLOYEES 表中所有的行****（即孤儿数据（为null）也会被取出）****。**
> 
> 例：select e.last\_name,d.department\_name
> 
> From employees e LEFT OUTER JOIN departments d
> 
> On e.dept\_id = d.department\_id;
> 
> 表示：查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

> ### **2.右外连接：** ###
> 
> ![e2639de88af94974bfe8cffedc156de9.png][]
> 
> #### **右外连接****定义：** ####
> 
> **右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的**
> 
> **行，该查询也会取回 DEPARTMENTS 表中所有的行。**
> 
> 例：select e.last\_name,d.department\_name
> 
> From employees e RIGHT OUTER JOIN departments d
> 
> on e.DEPARTMENT\_ID = d.department\_id;
> 
> 表示：查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

> ### **3.全外连接：** ###
> 
> ![f853b396eebe40a5901ec595a161ba67.png][]
> 
> #### **注意： MySQL 中不支持 FULL OUTER JOIN 连接****，****但****可以使用 union 实现全****外****连接。** ####
> 
> #### **1.UNION****（不包含重复数据）****:** ####
> 
> 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了DISTINCT。
> 
> #### **2.UNION ALL****（包含重复数据）****:** ####
> 
> 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。
> 
> #### **语法结构****：** ####
> 
> **(SELECT 投影列 FROM 表名 LEFT OUTER JOIN 表名ON 连接条件)**
> 
> **UNION**
> 
> **(SELECT 投影列 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件)**
> 
> 例：(select e.last\_name,d.department\_name From employees e LEFT OUTER JOIN departments d one.department\_id = d.department\_id)
> 
> UNION 
> 
> (select e1.last\_name,d1.department\_name from employees e1 RIGHT OUTER JOIN departments d1 on d1.department\_id =e1.department\_id);
> 
> 表示:查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。

[08be77368ca24e96ac9d038402e7f333.png]: https://img-blog.csdnimg.cn/08be77368ca24e96ac9d038402e7f333.png
[ed6ddbb901724f2f95b5f1d3eab940d7.png]: https://img-blog.csdnimg.cn/ed6ddbb901724f2f95b5f1d3eab940d7.png
[6341311cffc84f1eaee347c30fd65e65.png]: https://img-blog.csdnimg.cn/6341311cffc84f1eaee347c30fd65e65.png
[c0cfa8c748ef419d8f7df138135c400a.png]: https://img-blog.csdnimg.cn/c0cfa8c748ef419d8f7df138135c400a.png
[79d63c017cec448cb433e7b44478a73b.png]: https://img-blog.csdnimg.cn/79d63c017cec448cb433e7b44478a73b.png
[8bec8ef3a39d46a9985cedd7b38714da.png]: https://img-blog.csdnimg.cn/8bec8ef3a39d46a9985cedd7b38714da.png
[d91e82eec40c40a495c1ed0c16f01206.png]: https://img-blog.csdnimg.cn/d91e82eec40c40a495c1ed0c16f01206.png
[0d05b58511fc446dadcdd31882801739.png]: https://img-blog.csdnimg.cn/0d05b58511fc446dadcdd31882801739.png
[2c145498e7fd422fa47621a01db85824.png]: https://img-blog.csdnimg.cn/2c145498e7fd422fa47621a01db85824.png
[92edbf282e70447295ff604453c56108.png]: https://img-blog.csdnimg.cn/92edbf282e70447295ff604453c56108.png
[024743d4e8b34b299355dc3bb2c9e991.png]: https://img-blog.csdnimg.cn/024743d4e8b34b299355dc3bb2c9e991.png
[d7946bfa04d14591a46e05f374689bf3.png]: https://img-blog.csdnimg.cn/d7946bfa04d14591a46e05f374689bf3.png
[1c44d8d48e8544a68dd2c9df815cd019.png]: https://img-blog.csdnimg.cn/1c44d8d48e8544a68dd2c9df815cd019.png
[656512ed6763469a842414c7195fd47c.png]: https://img-blog.csdnimg.cn/656512ed6763469a842414c7195fd47c.png
[e2639de88af94974bfe8cffedc156de9.png]: https://img-blog.csdnimg.cn/e2639de88af94974bfe8cffedc156de9.png
[f853b396eebe40a5901ec595a161ba67.png]: https://img-blog.csdnimg.cn/f853b396eebe40a5901ec595a161ba67.png