欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL查询之多表联查详解

程序员文章站 2022-05-09 13:53:37
...

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

相关术语

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

多表查询

多表查询结构图

MySQL查询之多表联查详解

详解

内连接

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