MySQL查询之多表联查详解

在关系型数据库中，为了减少数据的冗余通常我们会将数据分割在不同的表中。当我们需要将这些数据合成为一条的时候，就需要对表进行连接。

相关术语

• 主键（Primary Key）：全名 主键约束，是一列或者列的组合，其值是唯一的，一个值只对应一行
• 外键（Foreign Key）：用来在两个表的数据之间建立连接，它可以是一列或者多列。一个表可以有一个或多个外键

多表查询

多表查询结构图

详解

内连接

devops表

create table devops(
    -> userid int(11) primary key NOT NULL,
    -> name varchar(255) COLLATE utf8_unicode_ci,
    -> companyid varchar(255) COLLATE utf8_unicode_ci,
    -> key companyid(companyid)
    -> );

数据

+--------+--------------+-----------+
| userid | name         | companyid |
+--------+--------------+-----------+
|      1 | 贝克汉姆     | 1         |
|      2 | 舍普琴科     | 2         |
|      3 | 亨利         | 3         |
|      4 | 坎通纳       | 1         |
|      5 | 斯科尔斯     | 1         |
|      6 | 卡恩         | 4         |
|      7 | 戴维斯       | 5         |
+--------+--------------+-----------+
7 rows in set (0.01 sec)

company表

create table company(
    -> companyid int(11) auto_increment primary key NOT NULL,
    -> com_name varchar(255) COLLATE utf8_unicode_ci
    -> );

数据

+-----------+-----------+
| companyid | com_name  |
+-----------+-----------+
|         1 | 曼联      |
|         2 | AC米兰    |
|         3 | 阿森纳    |
+-----------+-----------+
3 rows in set (0.00 sec)

显示内连接语法 

SELECT 表1.列1，表2.列2 FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键  WHERE 条件；

执行结果

select devops.name,company.com_name from devops inner join company on devops.companyid=company.companyid;
+--------------+-----------+
| name         | com_name  |
+--------------+-----------+
| 贝克汉姆     | 曼联      |
| 坎通纳       | 曼联      |
| 斯科尔斯     | 曼联      |
| 舍普琴科     | AC米兰    |
| 亨利         | 阿森纳    |
+--------------+-----------+
5 rows in set (0.00 sec)

隐式内连接语法

SELECT 表1.列1,表2.列2 FROM 表1,表2 WHERE 表1.外键=表2.主键；

执行结果

select devops.name,company.com_name from devops,company where devops.companyid=company.companyid;
+--------------+-----------+
| name         | com_name  |
+--------------+-----------+
| 贝克汉姆     | 曼联      |
| 坎通纳       | 曼联      |
| 斯科尔斯     | 曼联      |
| 舍普琴科     | AC米兰    |
| 亨利         | 阿森纳    |
+--------------+-----------+
5 rows in set (0.00 sec)

显示和隐式其查询结果是一样的，那么他们有什么不同呢？

区别

相对而言，隐式连接语法简单，通俗易懂。但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显

外连接

与取得双方表中数据的内连接相比，外连接只能取其中一个表存在的数据，外连接又分为左连接和右连接两种情况；

左外连接

左外连接，顾名思义以左边的表位准，只查询左边表中存在的数据，前提两个表中的健值要一致。

语法

SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WHERE 条件;

执行结果

select devops.name,company.com_name from devops left outer join company on devops.companyid=company.companyid;
+--------------+-----------+
| name         | com_name  |
+--------------+-----------+
| 贝克汉姆     | 曼联      |
| 舍普琴科     | AC米兰    |
| 亨利         | 阿森纳    |
| 坎通纳       | 曼联      |
| 斯科尔斯     | 曼联      |
| 卡恩         | NULL      |
| 戴维斯       | NULL      |
+--------------+-----------+
7 rows in set (0.00 sec)

可以看到最后两个人的单位名称并不在company表中，也同时显示出来了。这就表明了，该语句会以左连接中的左表的全部数据位基准查询；

右连接

同理，与左连接相反，以右表为基准查询。

语法

SELECT 表1.列1,表2.列2... FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WHERE 条件；

在company表中插入一条数据新增一个devops中不存在的单位ID的单位名称

新的数据

+-----------+-----------+
| companyid | com_name  |
+-----------+-----------+
|         1 | 曼联      |
|         2 | AC米兰    |
|         3 | 阿森纳    |
|         6 | 利物浦    |
+-----------+-----------+
4 rows in set (0.00 sec)

 执行结果

select devops.name,company.com_name from devops right outer join company on devops.companyid=company.companyid;
+--------------+-----------+
| name         | com_name  |
+--------------+-----------+
| 贝克汉姆     | 曼联      |
| 坎通纳       | 曼联      |
| 斯科尔斯     | 曼联      |
| 舍普琴科     | AC米兰    |
| 亨利         | 阿森纳    |
| NULL         | 利物浦    |
+--------------+-----------+
6 rows in set (0.00 sec)

验证了以右表全部数据位基准查询，左表中并没有单位为利物浦的数据，显示为NULL。

自连接

在介绍子连接之前先介绍一些自连接

自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。

mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。

运行结果

select * from devops a, devops b where a.companyid=b.companyid AND a.name <> b.name;
+--------+--------------+-----------+--------+--------------+-----------+
| userid | name         | companyid | userid | name         | companyid |
+--------+--------------+-----------+--------+--------------+-----------+
|      4 | 坎通纳       | 1         |      1 | 贝克汉姆     | 1         |
|      5 | 斯科尔斯     | 1         |      1 | 贝克汉姆     | 1         |
|      1 | 贝克汉姆     | 1         |      4 | 坎通纳       | 1         |
|      5 | 斯科尔斯     | 1         |      4 | 坎通纳       | 1         |
|      1 | 贝克汉姆     | 1         |      5 | 斯科尔斯     | 1         |
|      4 | 坎通纳       | 1         |      5 | 斯科尔斯     | 1         |
+--------+--------------+-----------+--------+--------------+-----------+
6 rows in set (0.00 sec)

可以看到，将devops表中companyid 相同的所有数据都显示出来了。

语句解释：

[devops a, devops b] 意为给表devops设置两个表名 分别为a,b

 [a.companyid=b.companyid AND a.name <> b.name] 意为找出companyid 相同的但是name不同的数据

 自连接还是比较常见的一种方法，在一些查询单个表中有字段相同的数据的时候进行查询对比，例如商城中手机款式同为IPhone11的不同价格，经过某个地区的所有公交车等等。

子查询

子查询指的就是在一个查询之中嵌套了其他的若干查询，在使用select语句查询数据时,有时候会遇到这样的情况，在where查询条件中的限制条件不是一个确定的值，而是一个来自于另一个查询的结果。子查询一般出现在FROM和WHERE子句中。

语法

SELECT 列名1 ...FROM 表名 WHERE 列名 比较运算符 （SELECT 命令）;

例如我想查询单位名称为曼联的所有人员名单怎么办呢？

首先，devops表中没有单位名称只有id，company表中又没有人员名字，这就要借助于子查询，借助于内连接查询查出所有devops和company两表中的人员和单位名单，再根据使用select查询通过where条件 筛选出单位为曼联的人员名单。

查询语句及结果如下：

select * from(select devops.name,company.com_name from devops inner join company on devops.companyid=company.companyid)b where b.com_name='曼联';
+--------------+----------+
| name         | com_name |
+--------------+----------+
| 贝克汉姆     | 曼联     |
| 坎通纳       | 曼联     |
| 斯科尔斯     | 曼联     |
+--------------+----------+
3 rows in set (0.00 sec)

OK，MySQL的多表连查就到此为止了。