MySQL中常用SQL语句的编写(8)

12. 多表连查
在真正的应用中经常需要从多个数据表中读取数据。下面将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。
JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

我们在blog数据库中有四张表 author、article、tag、article_tag。数据表数据如下：

mysql> select * from author; +----+--------+------------+-------------+ | id | name | qq | phone | +----+--------+------------+-------------+ | 1 | 君惜 | 2298630081 | 18500178899 | | 2 | 糖糖 | 234567 | 13256987582 | | 3 | 琳琳 | 345678 | 15636589521 | | 5 | 李天星 | 5678911 | 13345607861 | | 6 | 王星 | 5678912 | 13345607862 | | 7 | 张星星 | 5678913 | 13345607863 | +----+--------+------------+-------------+ 6 rows in set (0.00 sec) mysql> select * from article; +----+--------------+----------------------------+-----------+---------------------+ | id | title | content | author_id | create_time | +----+--------------+----------------------------+-----------+---------------------+ | 1 | 流畅的python | Python各种拽 | 1 | 2017-09-12 16:36:41 | | 2 | 嘻哈 | 中国有嘻哈 | 2 | 2017-09-12 16:36:42 | | 3 | 严肃 | 你这辈子就是吃了太严肃的亏 | 3 | 2017-09-12 16:36:43 | +----+--------------+----------------------------+-----------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from tag; +----+------+ | id | name | +----+------+ | 1 | 技术 | | 2 | 娱乐 | | 3 | 文学 | +----+------+ 3 rows in set (0.00 sec) mysql> select * from article_tag; +----+------------+--------+ | id | article_id | tag_id | +----+------------+--------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------------+--------+ 3 rows in set (0.01 sec)

INNER JOIN 实例
使用 INNER JOIN查询article中author_id等于author的id的数据（这里SQL语句中INNER可以省略）：

mysql> select name, qq, phone, title, content, create_time from author as u join article as a on u.id=a.author_id; +------+------------+-------------+--------------+----------------------------+---------------------+ | name | qq | phone | title | content | create_time | +------+------------+-------------+--------------+----------------------------+---------------------+ | 君惜 | 2298630081 | 18500178899 | 流畅的python | Python各种拽 | 2017-09-12 16:36:41 | | 糖糖 | 234567 | 13256987582 | 嘻哈 | 中国有嘻哈 | 2017-09-12 16:36:42 | | 琳琳 | 345678 | 15636589521 | 严肃 | 你这辈子就是吃了太严肃的亏 | 2017-09-12 16:36:43 | +------+------------+-------------+--------------+----------------------------+---------------------+ 3 rows in set (0.00 sec)

以上SQL语句等价于：
where 子句

mysql> select name, qq, phone, title, content, create_time from author as u join article as a where u.id=a.author_id; +------+------------+-------------+--------------+----------------------------+---------------------+ | name | qq | phone | title | content | create_time | +------+------------+-------------+--------------+----------------------------+---------------------+ | 君惜 | 2298630081 | 18500178899 | 流畅的python | Python各种拽 | 2017-09-12 16:36:41 | | 糖糖 | 234567 | 13256987582 | 嘻哈 | 中国有嘻哈 | 2017-09-12 16:36:42 | | 琳琳 | 345678 | 15636589521 | 严肃 | 你这辈子就是吃了太严肃的亏 | 2017-09-12 16:36:43 | +------+------------+-------------+--------------+----------------------------+---------------------+ 3 rows in set (0.00 sec)

利用第三张表连接查询

mysql> select title as '书名', content as '内容', name as '标签', create_time as "创建时间" from article, tag inner join article_tag as at where at.article_id=article.id and at.tag_id=tag.id; +--------------+----------------------------+------+---------------------+ | 书名 | 内容 | 标签 | 创建时间 | +--------------+----------------------------+------+---------------------+ | 流畅的python | Python各种拽 | 技术 | 2017-09-12 16:36:41 | | 嘻哈 | 中国有嘻哈 | 娱乐 | 2017-09-12 16:36:42 | | 严肃 | 你这辈子就是吃了太严肃的亏 | 文学 | 2017-09-12 16:36:43 | +--------------+----------------------------+------+---------------------+ 3 rows in set (0.00 sec) mysql> select au.name as '作者', ar.title as '书名', ar.content as '内容', t.name as '标签', ar.create_time as '创建时间' from author as au, article as ar, tag as t inner join article_tag as at where au.id=ar.author_id and at.art icle_id=ar.id and at.tag_id=t.id; +------+--------------+----------------------------+------+---------------------+ | 作者 | 书名 | 内容 | 标签 | 创建时间 | +------+--------------+----------------------------+------+---------------------+ | 君惜 | 流畅的python | Python各种拽 | 技术 | 2017-09-12 16:36:41 | | 糖糖 | 嘻哈 | 中国有嘻哈 | 娱乐 | 2017-09-12 16:36:42 | | 琳琳 | 严肃 | 你这辈子就是吃了太严肃的亏 | 文学 | 2017-09-12 16:36:43 | +------+--------------+----------------------------+------+---------------------+ 3 rows in set (0.00 sec)