MySQL 多表查询中的内连接和外连接

一般情况下一个网站的数据库通常会有多张数据表组成,这些数据表分别存储网站不同区域的内容。数据表之间可以用类似 ID 的字段来关联,在查询的时候可以通过一条语句来查询多张表的内容。例如 论坛的数据库,帖子和用户信息就是分开存储的,在查询的时候通过 ID 来关联就能查询出 帖子内容 和 对应的 用户信息。

MySQL 的多表查询有多种关联方式,这里主要写的是 内连接外连接。其它的以后再写。

演示表格

这里使用两张数据表来演示,表名分别为 usermessage。下面是两张表的内容:

user

+----+--------+------------------+------------+
| id | gender | email            | name       |
+----+--------+------------------+------------+
|  1 |      0 | 2585@qq.com      | Jack       |
|  2 |      0 | 232454@qq.com    | Mark       |
|  3 |      1 | 245522@qq.com    | Alice      |
|  4 |      1 | 121455@qq.com    | Jerry      |
|  5 |      0 | 588855@163.com   | John       |
|  6 |      0 | 785588@gmail.com | Kennedy    |
|  7 |      0 | mysql@qq.com     | Eisenhower |
|  8 |      1 | jjjiw@yahoo.com  | Ruby       |
+----+--------+------------------+------------+

message

+----+---------+-----------------+----------+
| id | user_id | content         | time     |
+----+---------+-----------------+----------+
|  2 |       1 | My Name is Jack |  1574586 |
|  3 |       2 | My Name is Mark |   548548 |
|  4 |       1 | Are you OK      | 45888966 |
|  5 |       1 | Hello Thank you |   458856 |
|  6 |       2 | Awesome         |   558856 |
+----+---------+-----------------+----------+

这是两张比较简单的数据表,这两张表之间唯一有关系的就是 user 表的 idmessage 表的 user_id

内连接

内连接是 MySQL 中用的比较多的一种连接查询,内连接可以返回两张表中满足关联条件的行。下面查询 user 表 和 message 表,用 user 表的 id 来关联 message 表的 user_id

SELECT * FROM
user INNER JOIN message
ON
user.id = message.user_id

下面是查询结果:

+----+--------+---------------+------+----+---------+-----------------+----------+
| id | gender | email         | name | id | user_id | content         | time     |
+----+--------+---------------+------+----+---------+-----------------+----------+
|  1 |      0 | 2585@qq.com   | Jack |  2 |       1 | My Name is Jack |  1574586 |
|  2 |      0 | 232454@qq.com | Mark |  3 |       2 | My Name is Mark |   548548 |
|  1 |      0 | 2585@qq.com   | Jack |  4 |       1 | Are you OK      | 45888966 |
|  1 |      0 | 2585@qq.com   | Jack |  5 |       1 | Hello Thank you |   458856 |
|  2 |      0 | 232454@qq.com | Mark |  6 |       2 | Awesome         |   558856 |
+----+--------+---------------+------+----+---------+-----------------+----------+

user 表和 message 表都有一个 id,前面的 iduser 表的,后面的 idmessage 表的。

user 表虽然有很多行但这里只查询出两行,只有在 message 表中有 user_iduser 才会被查询出来。

其中的 user INNER JOIN message 就是让 user 表来连接 message 表。user.id = message.user_id 就是通过 user 表的 id 来关联 message 表的 user_id

下面查询 Mark 的用户信息和 Mark 的留言:

SELECT * FROM
user INNER JOIN message
ON
user.id = message.user_id
WHERE
name = 'Mark'

查询结果:

+----+--------+---------------+------+----+---------+-----------------+--------+
| id | gender | email         | name | id | user_id | content         | time   |
+----+--------+---------------+------+----+---------+-----------------+--------+
|  2 |      0 | 232454@qq.com | Mark |  3 |       2 | My Name is Mark | 548548 |
|  2 |      0 | 232454@qq.com | Mark |  6 |       2 | Awesome         | 558856 |
+----+--------+---------------+------+----+---------+-----------------+--------+

下面调转一下两张表的顺序:

SELECT * FROM
message INNER JOIN user
ON
message.user_id = user.id

下面是查询结果:

+----+---------+-----------------+----------+----+--------+---------------+------+
| id | user_id | content         | time     | id | gender | email         | name |
+----+---------+-----------------+----------+----+--------+---------------+------+
|  2 |       1 | My Name is Jack |  1574586 |  1 |      0 | 2585@qq.com   | Jack |
|  3 |       2 | My Name is Mark |   548548 |  2 |      0 | 232454@qq.com | Mark |
|  4 |       1 | Are you OK      | 45888966 |  1 |      0 | 2585@qq.com   | Jack |
|  5 |       1 | Hello Thank you |   458856 |  1 |      0 | 2585@qq.com   | Jack |
|  6 |       2 | Awesome         |   558856 |  2 |      0 | 232454@qq.com | Mark |
+----+---------+-----------------+----------+----+--------+---------------+------+

论坛或留言板只需要通过内连接就能同时输出留言和用户信息。

外连接

外连接又可以分为 左外连接右外连接

左外连接

左外连接 就是返回左表中的全部内容和右表中符合关联条件的内容。

下面还是查询 user 表和 message 表:

SELECT * FROM
user LEFT JOIN message
ON
user.id = message.user_id

下面是查询结果:

+----+--------+------------------+------------+------+---------+-----------------+----------+
| id | gender | email            | name       | id   | user_id | content         | time     |
+----+--------+------------------+------------+------+---------+-----------------+----------+
|  1 |      0 | 2585@qq.com      | Jack       |    2 |       1 | My Name is Jack |  1574586 |
|  2 |      0 | 232454@qq.com    | Mark       |    3 |       2 | My Name is Mark |   548548 |
|  1 |      0 | 2585@qq.com      | Jack       |    4 |       1 | Are you OK      | 45888966 |
|  1 |      0 | 2585@qq.com      | Jack       |    5 |       1 | Hello Thank you |   458856 |
|  2 |      0 | 232454@qq.com    | Mark       |    6 |       2 | Awesome         |   558856 |
|  3 |      1 | 245522@qq.com    | Alice      | NULL |    NULL | NULL            |     NULL |
|  4 |      1 | 121455@qq.com    | Jerry      | NULL |    NULL | NULL            |     NULL |
|  5 |      0 | 588855@163.com   | John       | NULL |    NULL | NULL            |     NULL |
|  6 |      0 | 785588@gmail.com | Kennedy    | NULL |    NULL | NULL            |     NULL |
|  7 |      0 | mysql@qq.com     | Eisenhower | NULL |    NULL | NULL            |     NULL |
|  8 |      1 | jjjiw@yahoo.com  | Ruby       | NULL |    NULL | NULL            |     NULL |
+----+--------+------------------+------------+------+---------+-----------------+----------+

查询出了所有 user 表的内容和一部分 message 表的内容,没有被 user 表的 id 关联到的 message 表就返回 NULL

下面查询 user 表中 nameJerry 的留言:

SELECT * FROM
user LEFT JOIN message
ON
user.id = message.user_id
WHERE
name = 'Jerry'

查询结果:

+----+--------+---------------+-------+------+---------+---------+------+
| id | gender | email         | name  | id   | user_id | content | time |
+----+--------+---------------+-------+------+---------+---------+------+
|  4 |      1 | 121455@qq.com | Jerry | NULL |    NULL | NULL    | NULL |
+----+--------+---------------+-------+------+---------+---------+------+

因为 左外连接 会返回左表中的所有内容,所以哪怕没有关联到右表也会返回内容。如果是 内连接 在没有关联到内容的情况下就不会返回数据。

右外连接

右外连接左外连接 相反,右外连接 会返回左表中符合关联条件的内容和右表中的所有内容。

下面还是查询 user 表和 message 表:

SELECT * FROM
user RIGHT JOIN message
ON
user.id = message.user_id

下面是查询结果:

+------+--------+---------------+------+----+---------+-----------------+----------+
| id   | gender | email         | name | id | user_id | content         | time     |
+------+--------+---------------+------+----+---------+-----------------+----------+
|    1 |      0 | 2585@qq.com   | Jack |  2 |       1 | My Name is Jack |  1574586 |
|    2 |      0 | 232454@qq.com | Mark |  3 |       2 | My Name is Mark |   548548 |
|    1 |      0 | 2585@qq.com   | Jack |  4 |       1 | Are you OK      | 45888966 |
|    1 |      0 | 2585@qq.com   | Jack |  5 |       1 | Hello Thank you |   458856 |
|    2 |      0 | 232454@qq.com | Mark |  6 |       2 | Awesome         |   558856 |
+------+--------+---------------+------+----+---------+-----------------+----------+

返回了右表中所有的内容和左表中符合关联条件的内容。因为右表的内容比左表的少,所以左表没有返回 NULL

以上就是 MySQL 的三种连接查询方式,一般在实际开发中 内连接 会用的多一些。

声明:本站所有文章,如无特殊说明或标注,均为本站原创发布。任何个人或组织,在未征得本站同意时,禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。

给TA打赏
共{{data.count}}人
人已打赏
MySQL

mysql删除重复数据,仅保留一条

2022-6-24 12:56:32

MySQL

MySQL 常用函数(上)

2023-3-10 14:44:37

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
搜索