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的多表连查就到此为止了。