27-二十七、MySQL JOIN 进行多表查询-Java极客技术学习

前面我们学的都是单张表的操作，都比较简单，但数据往往是多张表组成的，往往需要从多张表中查找数据

这时候就会用到表连接，也就是两张或者多张表如何组成一张大表，然后从大表里查找数据

连表方式

我们先来看看两张表连接的情况，我们假设两张表，A 表和 B 表，如下图

当我们把两张表和在一起的时候，有一种拼合法只取表头颜色一样的拼在一起，不一样的扔掉，就像下图一样，这种拼接法，我们称之为 内连接

还有一种拼接法就是把 A 表中的都留下来，然后从 B 中找对应的颜色粘合在一起，就像下图一样，我们称之为 左连接

还有一种拼接法就是把 B 表中的都留下来，然后从 A 表里找对应的颜色粘合在一起,就像下图一样，我们称之为 右连接

最后一种拼接法，就是 A 表中的都留下，然后从 B 表中找对应的颜色粘合在一起，剩下的那些 B 表中的则全部排在最后，并不扔掉，就先下图一样，我们称之为 外链接

注意： 记住我刚刚说的查找过程，因为这是面试常考题目，就是先把一张表的记录查出来，然后根据记录一条一条去另一张表里找，这是不是很像我们堆积木游戏一样，表连接的本质过程也是这样，所以才导致了表连接很慢

如果有三张，四张，五张表也是同一个道理

这里有一点没演示到，就是如果任何一张表中有重复的表头颜色，那么也会重复显示一次

MySQL 中的连表

MySQL 支持上面四种连接中的前三种，第四种外连接不支持

可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询

连表方式	名称	说明
INNERJOIN	内连接	获取两个表中字段匹配关系的记录
LEFTJOIN	左连接	获取左表所有记录，即使右表没有对应匹配的记录
RIGHTJOIN	右连接	获取右表所有记录，即使左表没有对应匹配的记录

MySQL 中的 INNER JOIN 可以省略 INNER 关键字，也就是 JOIN == INNER JOIN

范例数据

可以在 mysql> 命令行中运行以下语句填充范例数据


DROP TABLE IF EXISTS `tbl_language`;
DROP TABLE IF EXISTS `tbl_rank`;
CREATE TABLE IF NOT EXISTS `tbl_language`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(64) NOT NULL,
   `url` VARCHAR(128) NOT NULL,
   `founded_at` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `tbl_rank`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(64) NOT NULL,
   `month` VARCHAR(7) NOT NULL,
   `rank` TINYINT NOT NULL,
   `rate` VARCHAR(32) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tbl_language` VALUES
    (1,'Python','https://www.ycbbs.vip','1991-2-20'),
    (2,'PHP','http://www.php.net','1994-1-1'),
    (3,'Ruby','https://www.ruby-lang.org/','1996-12-25'),
    (4,'Kotlin','http://kotlinlang.org/','2016-02-17');
INSERT INTO `tbl_rank` VALUES
    (1, 'Python','2018-04',4,'5.083%'),
    (2, 'PHP','2018-04',6,'4.218%'),
    (3, 'Ruby','2018-04',11,'2.018%'),
    (4, 'Java','2018-04',1,'15.777%'),
    (5, 'Python','2018-03',4,'5.869%'),
    (6, 'PHP','2018-03',7,'4.010%'),
    (7, 'Ruby','2018-03',12,'2.744%'),
    (8, 'Java','2018-03',1,'14.941'),
    (9, 'Python','2018-02',4,'5.168%'),
    (10, 'PHP','2018-02',7,'3.420%'),
    (11, 'Ruby','2018-02',10,'2.534%'),
    (12, 'Java','2018-02',1,'14.988%');

tbl_language 表中的数据如下


+----+--------+----------------------------+------------+
| id | name   | url                        | founded_at |
+----+--------+----------------------------+------------+
|  1 | Python | https://www.ycbbs.vip        | 1991-02-20 |
|  2 | PHP    | http://www.php.net         | 1994-01-01 |
|  3 | Ruby   | https://www.ruby-lang.org/ | 1996-12-25 |
|  4 | Kotlin | http://kotlinlang.org/     | 2016-02-17 |
+----+--------+----------------------------+------------+

tbl_rank 表中的数据如下


+----+--------+---------+------+---------+
| id | name   | month   | rank | rate    |
+----+--------+---------+------+---------+
|  1 | Python | 2018-04 |    4 | 5.083%  |
|  2 | PHP    | 2018-04 |    6 | 4.218%  |
|  3 | Ruby   | 2018-04 |   11 | 2.018%  |
|  4 | Java   | 2018-04 |    1 | 15.777% |
|  5 | Python | 2018-03 |    4 | 5.869%  |
|  6 | PHP    | 2018-03 |    7 | 4.010%  |
|  7 | Ruby   | 2018-03 |   12 | 2.744%  |
|  8 | Java   | 2018-03 |    1 | 14.941  |
|  9 | Python | 2018-02 |    4 | 5.168%  |
| 10 | PHP    | 2018-02 |    7 | 3.420%  |
| 11 | Ruby   | 2018-02 |   10 | 2.534%  |
| 12 | Java   | 2018-02 |    1 | 14.988% |
+----+--------+---------+------+---------+

在命令提示符中使用 INNER JOIN

接下来我们在 mysql> 命令行中演示上面说到的 MySQL 支持的三种连表方式

我们把 tbl_language 当作 A 表，也就是左表，把 tbl_rank 当作 B 表也就是右表，而颜色就是两张表中相同的字段 name

INNER JOIN

下面的语句用于查找 tbl_language 和 tbl_rank 中 name 字段相同的所有记录


SELECT `tbl_language`.name,url,rate  FROM `tbl_language` INNER JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;

运行结果如下


MariaDB [ycbbs]> SELECT `tbl_language`.name,url,rate  FROM `tbl_language` INNER JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;
+--------+----------------------------+--------+
| name   | url                        | rate   |
+--------+----------------------------+--------+
| Python | https://www.ycbbs.vip        | 5.083% |
| PHP    | http://www.php.net         | 4.218% |
| Ruby   | https://www.ruby-lang.org/ | 2.018% |
| Python | https://www.ycbbs.vip        | 5.869% |
| PHP    | http://www.php.net         | 4.010% |
| Ruby   | https://www.ruby-lang.org/ | 2.744% |
| Python | https://www.ycbbs.vip        | 5.168% |
| PHP    | http://www.php.net         | 3.420% |
| Ruby   | https://www.ruby-lang.org/ | 2.534% |
+--------+----------------------------+--------+
 rows in set (0.00 sec)

为什么有 9 条记录而不是 3 条 ?

因为 tbl_rank 中有 9 条记录的 name 和 tbl_language 表中的相同，两者取最多的那个


SELECT `tbl_language`.name,url,rate  FROM `tbl_language` JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;

运行结果如下


MariaDB [ycbbs]> SELECT `tbl_language`.name,url,rate  FROM `tbl_language` JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;
+--------+----------------------------+--------+
| name   | url                        | rate   |
+--------+----------------------------+--------+
| Python | https://www.ycbbs.vip        | 5.083% |
| PHP    | http://www.php.net         | 4.218% |
| Ruby   | https://www.ruby-lang.org/ | 2.018% |
| Python | https://www.ycbbs.vip        | 5.869% |
| PHP    | http://www.php.net         | 4.010% |
| Ruby   | https://www.ruby-lang.org/ | 2.744% |
| Python | https://www.ycbbs.vip        | 5.168% |
| PHP    | http://www.php.net         | 3.420% |
| Ruby   | https://www.ruby-lang.org/ | 2.534% |
+--------+----------------------------+--------+
 rows in set (0.00 sec)

还可以使用 WHERE 语句来代替 INNER JOIN，下面这条语句的执行结果和它们一样


SELECT `tbl_language`.name,url,rate  FROM `tbl_language`,`tbl_rank` WHERE `tbl_language`.name = `tbl_rank`.name;

运行结果


MariaDB [ycbbs]> SELECT `tbl_language`.name,url,rate  FROM `tbl_language`,`tbl_rank` WHERE `tbl_language`.name = `tbl_rank`.name;
+--------+----------------------------+--------+
| name   | url                        | rate   |
+--------+----------------------------+--------+
| Python | https://www.ycbbs.vip        | 5.083% |
| PHP    | http://www.php.net         | 4.218% |
| Ruby   | https://www.ruby-lang.org/ | 2.018% |
| Python | https://www.ycbbs.vip        | 5.869% |
| PHP    | http://www.php.net         | 4.010% |
| Ruby   | https://www.ruby-lang.org/ | 2.744% |
| Python | https://www.ycbbs.vip        | 5.168% |
| PHP    | http://www.php.net         | 3.420% |
| Ruby   | https://www.ruby-lang.org/ | 2.534% |
+--------+----------------------------+--------+
 rows in set (0.00 sec)

MySQL LEFT JOIN

LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据

下面的语句会读取 tbl_language 中所有的记录，然后查找 tbl_rank 中的 name 记录一一对应，如果没找到则空，如果找到多条，则重复多次


SELECT `tbl_language`.name,url,rate  FROM `tbl_language` LEFT JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;

运行结果如下


MariaDB [ycbbs]> SELECT `tbl_language`.name,url,rate  FROM `tbl_language` LEFT JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;
+--------+----------------------------+--------+
| name   | url                        | rate   |
+--------+----------------------------+--------+
| Python | https://www.ycbbs.vip        | 5.083% |
| PHP    | http://www.php.net         | 4.218% |
| Ruby   | https://www.ruby-lang.org/ | 2.018% |
| Python | https://www.ycbbs.vip        | 5.869% |
| PHP    | http://www.php.net         | 4.010% |
| Ruby   | https://www.ruby-lang.org/ | 2.744% |
| Python | https://www.ycbbs.vip        | 5.168% |
| PHP    | http://www.php.net         | 3.420% |
| Ruby   | https://www.ruby-lang.org/ | 2.534% |
| Kotlin | http://kotlinlang.org/     | NULL   |
+--------+----------------------------+--------+

总共 10条记录 = INNER JOIN 的 9 条 + 左边多余的 1 条

MySQL RIGHT JOIN

RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据

下面的语句会读取 tbl_rank 中所有的记录，然后查找 tbl_language 中的 name 记录一一对应，如果没找到则空，如果找到多条，则重复多次


SELECT `tbl_language`.name,url,rate  FROM `tbl_language` RIGHT JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;

运行结果如下


MariaDB [ycbbs]> SELECT `tbl_language`.name,url,rate  FROM `tbl_language` RIGHT JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;
+--------+----------------------------+---------+
| name   | url                        | rate    |
+--------+----------------------------+---------+
| Python | https://www.ycbbs.vip        | 5.083%  |
| Python | https://www.ycbbs.vip        | 5.869%  |
| Python | https://www.ycbbs.vip        | 5.168%  |
| PHP    | http://www.php.net         | 4.218%  |
| PHP    | http://www.php.net         | 4.010%  |
| PHP    | http://www.php.net         | 3.420%  |
| Ruby   | https://www.ruby-lang.org/ | 2.018%  |
| Ruby   | https://www.ruby-lang.org/ | 2.744%  |
| Ruby   | https://www.ruby-lang.org/ | 2.534%  |
| NULL   | NULL                       | 15.777% |
| NULL   | NULL                       | 14.941  |
| NULL   | NULL                       | 14.988% |
+--------+----------------------------+---------+

总计 12 条记录 = INNER JOIN 的 9 条 + tbl_rank 多出来的 3 条

在 PHP 脚本中使用 JOIN

在 PHP 脚本中使用 JOIN 和在 PHP 中使用 SELECT FROM 没有什么不同，因为 JOIN 本身就是 SELECT 中的一部分

PHP 可以使用 PDO::query() 函数来查询某个表中的数据

PDO::query() 函数原型

PDO::query() 有四个函数重载


PDOStatement PDO::query ( string $statement )
PDOStatement PDO::query ( string $statement , int $PDO::FETCH_COLUMN , int $colno )
PDOStatement PDO::query ( string $statement , int $PDO::FETCH_CLASS , string $classname , array $ctorargs )
PDOStatement PDO::query ( string $statement , int $PDO::FETCH_INTO , object $object )

如果成功，PDO::query() 返回 PDOStatement 对象，如果失败返回 FALSE

参数

参数	说明
statement	要被预处理和执行的SQL语句，查询中的数据应该被妥善地转义

第二个参数有以下几个可选值，默认为 PDO::FETCH_BOTH

值	说明
PDO::FETCH_ASSOC	返回一个索引为结果集列名的数组
PDO::FETCH_BOTH	默认，返回一个索引为结果集列名和以0开始的列号的数组
PDO::FETCH_BOUND	返回TRUE，并分配结果集中的列值给PDOStatement::bindColumn()方法绑定的PHP变量
PDO::FETCH_CLASS	返回一个请求类的新实例，映射结果集中的列名到类中对应的属性名。如果fetch_style包含PDO::FETCH_CLASSTYPE（例如：PDO::FETCH_CLASS\|PDO::FETCH_CLASSTYPE），则类名由第一列的值决定
PDO::FETCH_INTO	更新一个被请求类已存在的实例，映射结果集中的列到类中命名的属性
PDO::FETCH_LAZY	结合使用PDO::FETCH_BOTH和PDO::FETCH_OBJ，创建供用来访问的对象变量名
PDO::FETCH_NUM	返回一个索引为以0开始的结果集列号的数组
PDO::FETCH_OBJ	返回一个属性名对应结果集列名的匿名对象

我们使用默认的 PDO::FETCH_BOTH 获取所有数据，其它方式请移步我们的 PHP 基础教程


<?php 
$sql= "SELECT `tbl_language`.name,url,rate  FROM `tbl_language` INNER JOIN `tbl_rank` ON `tbl_language`.name = `tbl_rank`.name;";
$dbh = new PDO('mysql:host=127.0.0.1;dbname=ycbbs', 'root', '');    
$stmt = $dbh->query($sql);
foreach($stmt as $row)
{
    var_dump($row);
}

输出结果如下


$ php main.php                                       
array(6) {
  ["name"]=>
  string(6) "Python"
  [0]=>
  string(6) "Python"
  ["url"]=>
  string(19) "https://www.ycbbs.vip"
  [1]=>
  string(19) "https://www.ycbbs.vip"
  ["rate"]=>
  string(6) "5.083%"
  [2]=>
  string(6) "5.083%"
}
array(6) {
  ["name"]=>
  string(3) "PHP"
  [0]=>
  string(3) "PHP"
  ["url"]=>
  string(18) "http://www.php.net"
  [1]=>
  string(18) "http://www.php.net"
  ["rate"]=>
  string(6) "4.218%"
  [2]=>
  string(6) "4.218%"
}
array(6) {
  ["name"]=>
  string(4) "Ruby"
  [0]=>
  string(4) "Ruby"
  ["url"]=>
  string(26) "https://www.ruby-lang.org/"
  [1]=>
  string(26) "https://www.ruby-lang.org/"
  ["rate"]=>
  string(6) "2.018%"
  [2]=>
  string(6) "2.018%"
}
array(6) {
  ["name"]=>
  string(6) "Python"
  [0]=>
  string(6) "Python"
  ["url"]=>
  string(19) "https://www.ycbbs.vip"
  [1]=>
  string(19) "https://www.ycbbs.vip"
  ["rate"]=>
  string(6) "5.869%"
  [2]=>
  string(6) "5.869%"
}
array(6) {
  ["name"]=>
  string(3) "PHP"
  [0]=>
  string(3) "PHP"
  ["url"]=>
  string(18) "http://www.php.net"
  [1]=>
  string(18) "http://www.php.net"
  ["rate"]=>
  string(6) "4.010%"
  [2]=>
  string(6) "4.010%"
}
array(6) {
  ["name"]=>
  string(4) "Ruby"
  [0]=>
  string(4) "Ruby"
  ["url"]=>
  string(26) "https://www.ruby-lang.org/"
  [1]=>
  string(26) "https://www.ruby-lang.org/"
  ["rate"]=>
  string(6) "2.744%"
  [2]=>
  string(6) "2.744%"
}
array(6) {
  ["name"]=>
  string(6) "Python"
  [0]=>
  string(6) "Python"
  ["url"]=>
  string(19) "https://www.ycbbs.vip"
  [1]=>
  string(19) "https://www.ycbbs.vip"
  ["rate"]=>
  string(6) "5.168%"
  [2]=>
  string(6) "5.168%"
}
array(6) {
  ["name"]=>
  string(3) "PHP"
  [0]=>
  string(3) "PHP"
  ["url"]=>
  string(18) "http://www.php.net"
  [1]=>
  string(18) "http://www.php.net"
  ["rate"]=>
  string(6) "3.420%"
  [2]=>
  string(6) "3.420%"
}
array(6) {
  ["name"]=>
  string(4) "Ruby"
  [0]=>
  string(4) "Ruby"
  ["url"]=>
  string(26) "https://www.ruby-lang.org/"
  [1]=>
  string(26) "https://www.ruby-lang.org/"
  ["rate"]=>
  string(6) "2.534%"
  [2]=>
  string(6) "2.534%"
}

希望读者能够给小编留言，也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发，我想请你帮我两个小忙：

关注我们的 GitHub 博客，让我们成为长期关系
把这篇文章分享给你的朋友 / 交流群，让更多的人看到，一起进步，一起成长！
关注公众号 「方志朋」，公众号后台回复「666」免费领取我精心整理的进阶资源教程

JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区，是给开发者用的 Hacker News，技术文章由为你筛选出最优质的干货，其中包括：Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者，你每天都可以在这里找到技术世界的头条内容。

本文著作权归作者所有，如若转载，请注明出处

转载请注明：文章转载自「 Java极客技术学习 」https://www.javajike.com

标题：27-二十七、MySQL JOIN 进行多表查询

链接：https://www.javajike.com/article/1590.html

打赏

微信扫一扫

0 0

27-二十七、MySQL JOIN 进行多表查询

连表方式

MySQL 中的连表

范例数据

在命令提示符中使用 INNER JOIN

INNER JOIN

MySQL LEFT JOIN

MySQL RIGHT JOIN

在 PHP 脚本中使用 JOIN

PDO::query() 函数原型

参数

看完两件小事

相关推荐

请登录