MySQL关联查询

刺骨的言语ヽ痛彻心扉 2022-08-20 05:24 196阅读 0赞

在实际应用中，经常需要在一个查询语句中显示多张表的数据，这种多表数据记录关联查询，简称关联查询。

# 关系数据操作 #

连接操作是关系数据操作中专门用于数据库操作的关系运算。这里将先详细介绍关系数据操作中传统的三种运算：

*  并（UNION）
 *  笛卡尔积（CARTESIAN PRODUCT）
 *  连接（JOIN）

其中连接（JOIN）是专门针对数据库操作的运算。

## 并（UNION） ##

“并”操作就是把具有相同字段数目和字段类型的表合并到一起。比如存在两张用户表，其数据分别如下图所示：  
![连接查询][20160202175721009]    ![连接查询][20160202175813917]

将这两种用户表进行并操作后的数据如下：  
![连接查询][20160202180102857]  
可以发现并操作去掉了重复记录。

## 笛卡尔积（CARTESIAN PRODUCT） ##

笛卡尔积就是没有连接条件表关系返回的结果。笛卡尔积新关系的字段数为各个表字段数目的和，记录数为各个表记录的积。如下图所示：  
![连接查询][20160202180349023]

## 连接（JOIN） ##

为了便于用户的操作，专门提供了一种针对数据库操作的运算——连接（JOIN）。所谓连接就是在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。连接又分为三种：

*  内连接（INNER JOIN）
 *  外连接（OUTER JOIN）
 *  交叉连接（CROSS JOIN）

### 内连接（INNER JOIN） ###

就是在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配的条件可以分成三种：

*  自然连接（NATURAL JOIN）
 *  等值连接
 *  不等连接

这里将通过表示员工的表（t\_employee）和表示部门的表（t\_dept）来讲解各种内连接。这两张表的数据记录分别如下图所示：  
![连接查询][20160202181148058]    ![连接查询][20160202181211167]

**自然连接**操作就是表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。其笛卡尔积结果如下图：  
![连接查询][20160202181416918]  
根据相同名称字段值进行匹配后得到上图中用红色方框标记出来的数据行。再除去重复字段后得到的结果如下：  
![连接查询][20160202181452559]

**等值连接**就是表关系的笛卡尔积中，选择所匹配字段值相等的记录，与自然连接相比保留重复字段，所以上面两个表等值连接后的数据如下图：  
![连接查询][20160202184131816]

**不等连接**就是表关系的笛卡尔积中所匹配字段不相等的数据记录，与等值连接的结果集互补，结果如下图：  
![连接查询][20160202184227665]

### 外连接（OUTER JOIN） ###

外连接就是在表关系的笛卡尔积中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为以下三种：

*  左外连接（LEFT OUTER JOIN）
 *  右外连接（RIGHT OUTER JOIN）
 *  全外连接（FULL OUTER JOIN）

这里将通过表示部门的表（t\_dept）和表示员工的表（t\_employee）来讲解各种外连接。这两张表的数据记录分别如下图所示：  
![连接查询][20160202184547260]    ![连接查询][20160202184604026]

**左外连接**就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。上面两个表的笛卡尔积的结果如下：  
![连接查询][20160202184651792]  
选择相匹配的数据记录，即上图中红色方框中的数据行。再关联左边表中不匹配的数据记录，即上图中红色细圈中的数据，去掉重复值。得出左外连接的结果如下：  
![连接查询][20160202184734839]

**右外连接**就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。用上面的t\_employee表右连接t\_dept表，结果如下：  
![连接查询][20160202184826576]

**全外连接**就是表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左右两边表中不匹配的数据记录。

# 内连接查询 #

在MySQL中可以通过两种方式来实现连接查询，早期的语法形式如下：

SELECT [field1, field2, ... ]
    FROM [table1, table2, ...]
    WHERE [condition1 AND|OR condition2 ...]

FIELD表示要查询的字段，在FROM子句中用逗号（,）区分多个表，在WHERE子句中通过逻辑表达式来实现匹配条件。

另一种是ANSI连接语法形式，其语法形式如下：

SELECT [field1, field2, ... ]
    FROM table1 INNER JOIN table2 [INNER JOIN table3]
    ON [join condition]

在FROM子句中使用“join … on …”关键字，连接条件写在关键字ON子句中。MySQL推荐使用该语法形式的连接。

按照匹配情况，内连接查询可以分为 **等值连接** 和 **不等连接** 两种，其中等值连接中存在一种特殊的情况叫**自连接**，这里将讲到这三种连接。

这一节也将用到表t\_dept和表t\_employee进行讲解，不过与上面相比对表t\_employee增加了表示上级编号的字段superior\_no，两个表的数据如下：  
![连接查询][20160202185301081]    ![连接查询][20160202185325957]

## 自连接 ##

自连接就是指表与其自身进行连接，下面将通过具体的实例来说明。

**【实例6-1】**查询员工的姓名和领导姓名。  
分析可知员工姓名和领导姓名都是表t\_employee的emp\_name字段，而领导关系表现在表中的emp\_no和superior\_no字段的对应关系。因为员工姓名和领导姓名都在同一张表中，因此需要将表进行自连接。具体SQL语句如下：

SELECT e.emp_name employeename ,s.emp_name superiorname FROM t_employee e INNER JOIN t_employee s ON e.superior_no = s.emp_no;

通过“SELECT FROM WHERE”关键字也可以实现，具体SQL语句如下：

SELECT e.emp_name employeename, s.emp_name superiorname FROM t_employee e, t_employee s WHERE e.superior_no = s.emp_no;

在MySQL中，提供了一种机制来为表取别名，将别名放在字段名或表名之后即可。其查询结果如下：  
![连接查询][20160202185806358]  
比较t\_employee表数据可知上级编号为NULL的数据行并不在结果集中。

## 等值连接 ##

内连接查询中的等值连接，就是在关键字ON后面的匹配条件中通过关系运算符（=）来实现等值条件。

**【实例6-2】**查询所有员工的编号、姓名和部门名称。  
分析可知员工编号和姓名在t\_employee表中，部门名称在t\_dept表中，并通过两个表中的dept\_no字段进行关联。具体SQL语句如下：

SELECT emp_no, emp_name, dept_name FROM t_employee e INNER JOIN t_dept d ON e.dept_no = d.dept_no;

通过“SELECT FROM WHERE”关键字实现的SQL语句如下：

SELECT emp_no, emp_name, dept_name FROM t_employee e, t_dept d WHERE e.dept_no = d.dept_no;

上面例子是2个表的连接，下面通过一个实例来说明多表（3张表）等值连接。

**【实例6-3】**查询有上级的员工的编号、姓名、部门名称、上级姓名。  
分析可知员工编号和姓名在t\_employee表，上级姓名需要自连接t\_employee表，部门名称在t\_dept表中，涉及到3张表。具体SQL语句如下：

SELECT e.emp_no, e.emp_name, dept_name, s.emp_name leadername FROM t_employee e INNER JOIN t_dept d ON e.dept_no = d.dept_no INNER JOIN t_employee s ON e.superior_no = s.emp_no;

通过“SELECT FROM WHERE”关键字实现的SQL语句如下：

SELECT e.emp_no, e.emp_name, dept_name, s.emp_name leadername 
    FROM t_employee e, t_dept d, t_employee s 
    WHERE e.dept_no = d.dept_no AND e.superior_no = s.emp_no;

查询结果如下：  
![连接查询][20160202190203219]

## 不等连接 ##

内连接查询中的不等连接，就是在关键字ON后的匹配条件中除了可以有等于关系运算符来实现的条件外，还包含“>”，“>=”，“<”，“<=”，“!=”等运算符号，语法形式与等值连接相同，只是条件不同而已，这里不再举例说明。

# 外连接查询 #

在MySQL中，外连接查询会返回操作表中至少一个表的所有数据记录，通过SQL语句“OUTER JOIN … ON … ”来实现。其语法形式如下：

SELECT [field1, field2, ... ]
    FROM table1 LEFT|RIGHT|FULL [OUTER] JOIN table2
    ON [join condition]

外连接查询分为如下三类：

*  左外连接
 *  右外连接
 *  全外连接

## 左外连接 ##

上面有说道外连接查询会返回操作表中至少一个表的所有数据记录，左外连接就是指新关系中执行匹配条件时，以关键字LEFT \[OUTER\] JOIN左边的表为参考表。这里用表t\_employee和表t\_dept进行说明，表的数据如下：  
![连接查询][20160202190506892]   ![连接查询][20160202190526018]

**【实例6-4】**查询所有员工的编号、姓名和领导姓名。  
分析可知该查询仍然是t\_employee表的自连接，但从数据中可以看出james位于公司最高级别，如果按照【实例6-2-2-1】中的内连接来查询，结果中是没有该数据信息的，所以这里要用到左外连接，其SQL语句如下：

SELECT e.emp_no, e.emp_name, s.emp_name AS superiorname FROM t_employee e LEFT OUTER JOIN t_employee s ON e.superior_no = s.emp_no

其结果如下：  
![连接查询][20160202190729287]  
可见左外连接把JOIN关键字左边的表数据全部显示出来了，没有匹配的数据则用NULL填充。

## 右外连接 ##

右外连接就是指新关系中执行匹配条件时，以关键字RIGHT \[OUTER\] JOIN关键字右边的表为参考表。

**【实例6-5】**查询所有员工的编号、姓名和部门名称。  
分析可知该查询需要关联表t\_employee和表t\_dept，其SQL语句如下：

SELECT e.emp_no, e.emp_name, d.dept_name FROM t_employee e RIGHT OUTER JOIN t_dept d ON e.dept_no = d.dept_no

由于t\_dept表中部门编号为50的部门是没有员工的，所以右外连接操作下该部门的记录仍显示，左边数据用NULL填充，其结果如下：  
![连接查询][20160202190918024]

# 合并查询数据记录 #

MySQL中通过关键字UNION来实现并操作，可将多个SELECT语句的查询结果合并在一起组成新的关系。其语法形式如下：

SELECT field1, field2, ... fieldn FROM table1
    UNION | UNION ALL
    SELECT field1, field2, ... fieldn FROM table2
    ...

这里通过两张消息表t\_msg\_2015和t\_msg\_2016对并操作进行说明，这两张表的数据如下图所示：  
![连接查询][20160202191116370]    ![连接查询][20160202191136120]

这两种表的字段数目和字段类型都相同，对两种表的结果集进行并操作，SQL语句如下：

SELECT * FROM t_msg_2015 UNION SELECT * FROM t_msg_2016;

执行结果如下：  
![连接查询][20160202191343114]

结果集中把相同的记录给合并了，如果不想合并可用关键字UNION ALL，SQL语句如下：

SELECT * FROM t_msg_2015 UNION ALL SELECT * FROM t_msg_2016;

执行结果如下：  
![连接查询][20160202191451030]

# 子查询 #

在MySQL中虽然可以通过连接查询实现多表数据查询，但是却不建议使用。因为连接查询的性能较差，因此出现了子查询来替代连接查询。

在“6.1关系数据库操作”章节有讲到，关联查询的SQL语句在执行过程中，首先会对表进行笛卡尔积操作，然后再选取符合匹配条件的数据记录。如果关联查询中的表的数据记录很大，则在笛卡尔积操作时会产生大量的内存消耗，导致性能下降。

所谓子查询，就是指在一个查询之中嵌套了其它的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含一个SELECT查询语句。这些嵌套着的查询语句称之为子查询语句。WHERE子句中的子查询一般返回单行单列、多行单列、单行多列数据记录，而FROM子句中的子查询一般返回多行多列数据记录，可以当做一张临时表。由此可根据子查询的返回结果将其分为以下四种：

*  单行单列子查询
 *  多行单列子查询
 *  单行多列子查询
 *  多行多列子查询

## 单行单列子查询 ##

这里同样以表t\_employee和表t\_dept来做说明，其数据如下：  
![连接查询][20160202191710409]  ![连接查询][20160202191739913]

**【实例6-6】**查询出工资比tom高的员工的编号、姓名和工资。  
分析可知这里要先查出员工tom的工资，然后通过该值来查询工资比tom高的员工的信息，其SQL语句如下：

SELECT emp_no, emp_name, sal FROM t_employee WHERE sal > ( SELECT sal FROM t_employee WHERE emp_name = 'tom' );

其查询结果如下：  
![连接查询][20160202191912163]

## 单行多列子查询 ##

WHERE子句中的子查询还可以返回单行多列的数据记录，不过这种子查询很少见。

**【实例6-7】**查询出工资、部门编号都与jay相同的员工信息。  
分析可知先要查出tom的工资和部门编号，然后根据这两个值来查询，其SQL语句如下：

SELECT * FROM t_employee WHERE (sal, dept_no) = ( SELECT sal, dept_no FROM t_employee WHERE emp_name = 'tom' );

查询结果如下：  
![连接查询][20160202192019195]

## 多行单列子查询 ##

该子查询语句一般会在主查询语句的WHERE子句里，通常会包含IN、ANY、ALL、EXISTS等关键字。

**1.带有关键字IN的子查询**

关键字IN表示查询条件为某个字段在某个结果集内。  
**【实例6-8】**查询出有员工的部门编号和部门名称。  
分析可知先要从t\_employee表中查询出所有出现了的部门编号集合，再从部门表t\_dept中查询处于这个集合中的部门信息，其SQL语句如下：

SELECT dept_no, dept_name FROM t_dept WHERE dept_no IN ( SELECT DISTINCT dept_no FROM t_employee );

关键字DISTINCT表示去掉重复记录，其查询结果如下：  
![连接查询][20160202192225525]

**2.带有关键字ANY的子查询**

关键字ANY用来表示主查询的条件为满足子查询返回查询结果中的任意一条数据记录，该关键字有三种匹配方式，分别如下。

*  =ANY：功能与关键字IN相同；
 *  >ANY（>=ANY）：比子查询结果中最小的还要大（大于等于）的数据记录；
 *  <ANY（<=ANY）：比子查询结果中最大的还要小（小于等于）的数据记录；

**【实例6-9】**查询员工编号、姓名和工资，这些员工的工资不低于部门编号为20的员工的工资。  
分析可知先要查询出部门编号为20的员工工资集合，然后查出工资大于等于集合中最小值的员工信息，其SQL如下：

SELECT emp_no, emp_name, sal FROM t_employee WHERE sal >= ANY ( SELECT sal FROM t_employee WHERE dept_no = 20 );

查询结果如下：  
![连接查询][20160202192708077]

**3.带有关键字ALL的子查询**

关键字ALL用来表示主查询的条件为满足子查询返回查询结果中所有数据记录，该关键字有一下两种匹配方式：

*  >ALL（>=ALL）：比子查询结果中最大的还要大（大于等于）的数据记录；
 *  <ALL（<=ALL）：比子查询结果中最大的还要小（小于等于）的数据记录；

**【实例6-10】**查询员工编号、姓名和工资，这些员工的工资高于部门编号为20的任何一位员工的工资。  
同样，先查询出部门编号为20的员工工资集合，然后查出工资大于集合中最大值的员工信息，其SQL如下：

SELECT emp_no, emp_name, sal FROM t_employee WHERE sal > ALL ( SELECT sal FROM t_employee WHERE dept_no = 20 );

查询结果如下：  
![连接查询][20160202193136996]

**4.带有关键字EXISTS的子查询**

关键字EXISTS是一个布尔类型，当子查询返回结果集时为TRUE，否则返回FALSE。查询时EXISTS对主查询中的表逐条查询，每次查询都会比较EXISTS的条件语句，当EXISTS条件语句返回TRUE时则返回主查询当前遍历到的记录，反之不返回。

**【实例6-11】**查询部门下有员工的部门编号和部门名称。  
SQL语句如下。执行时遍历t\_dept表中的每条记录，并判断EXISTS子查询中是否有返回结果。

SELECT * FROM t_dept d WHERE EXISTS ( SELECT * FROM t_employee e WHERE e.dept_no = d.dept_no );

查询结果如下：  
![连接查询][20160202193315731]

## 多行多列子查询 ##

该类子查询一般会在主查询语句的FROM子句里，被当做一张临时表的方式来处理。

**【实例6-12】**查询员工表t\_employee中各个部门的编号、名称、员工人数和平均工资。  
分析可知先在t\_employee表中查询出各个部门的编号、员工人数和平均工资，其SQL语句如下：

SELECT dept_no, COUNT(emp_no) emp_sum, AVG(sal) average_sal FROM t_employee GROUP BY dept_no

结果如下：  
![连接查询][20160202193453510]

再将该结果集看做一张临时表与部门信息表t\_dept进行关联查询，其最终SQL语句如下：

SELECT d.dept_name, e.dept_no, e.emp_sum, e.average_sal FROM t_dept d INNER JOIN ( SELECT dept_no, COUNT(emp_no) emp_sum, AVG(sal) average_sal FROM t_employee GROUP BY dept_no ) e ON d.dept_no = e.dept_no

查询结果如下：  
![连接查询][20160202193545826]

[20160202175721009]: https://img-blog.csdn.net/20160202175721009
[20160202175813917]: /images/20220731/c70baecb8a2f45d39b21a9bfd8de521f.png
[20160202180102857]: /images/20220731/ce2b28b48f574f74b36667329900372d.png
[20160202180349023]: /images/20220731/b0471d5b33fe46e19adcee6de3a0ed83.png
[20160202181148058]: https://img-blog.csdn.net/20160202181148058
[20160202181211167]: /images/20220731/ffb8ed43a15c439ebc834b83ccf54b15.png
[20160202181416918]: /images/20220731/4a7246cc50ab4462b39549076a52fb06.png
[20160202181452559]: /images/20220731/f73ee114a132486bb648fd889ea41c30.png
[20160202184131816]: /images/20220731/11e5b07ef0f64800b944db4af2ffc85e.png
[20160202184227665]: /images/20220731/0424c033efd14590b31300d2b66832e3.png
[20160202184547260]: https://img-blog.csdn.net/20160202184547260
[20160202184604026]: /images/20220731/732ef163cd3a43eab176a78ee1c66320.png
[20160202184651792]: /images/20220731/826e4aeaa1c541f9ab2add7d844ac195.png
[20160202184734839]: /images/20220731/f71350d10b334045849396a8691de045.png
[20160202184826576]: /images/20220731/f24188f0bbb644aeba557b5a89d9bf21.png
[20160202185301081]: https://img-blog.csdn.net/20160202185301081
[20160202185325957]: /images/20220731/0eed36c6471940a0936a0a90a601dfd6.png
[20160202185806358]: /images/20220731/903b488ec7774b99aa9fe61031764a6e.png
[20160202190203219]: /images/20220731/d054f3732e634f9a81ac9a9ba630671d.png
[20160202190506892]: https://img-blog.csdn.net/20160202190506892
[20160202190526018]: /images/20220731/9b91d49ec3794979b3ef86e1972758cf.png
[20160202190729287]: /images/20220731/8133439804aa4b8384bab9000dea04d8.png
[20160202190918024]: /images/20220731/46bab28189da4fd894374aa743c218ee.png
[20160202191116370]: https://img-blog.csdn.net/20160202191116370
[20160202191136120]: /images/20220731/dbbd1a77d3f14ade8076d2cfe8cc76b6.png
[20160202191343114]: /images/20220731/d0f7250ea6ef48da9d7411dc1d3c0e3c.png
[20160202191451030]: /images/20220731/573ef6330acc48ee981c39ef259d6498.png
[20160202191710409]: https://img-blog.csdn.net/20160202191710409
[20160202191739913]: /images/20220731/c02eef590fd14a3081d22783081cc17a.png
[20160202191912163]: /images/20220731/3b9ea92582964f0999cc3eb8e2775e24.png
[20160202192019195]: /images/20220731/e1da7ad769084da18ba6033af4c23c59.png
[20160202192225525]: /images/20220731/9f5e597a6e194cb7bdda0568f85a4b26.png
[20160202192708077]: /images/20220731/7d8dd7187a1b480d9ce76fd09feb457e.png
[20160202193136996]: /images/20220731/bf17969682a544d582c3cab738d5ef1a.png
[20160202193315731]: /images/20220731/ea451652b47a48a292acb5439ce8d0f8.png
[20160202193453510]: /images/20220731/7c2d9919ef87445cad3dfabfb55123f6.png
[20160202193545826]: /images/20220731/7c2ebde9fcef4e26a402a6af91a83e68.png