前面我们学的都是单张表的操作,都比较简单,但数据往往是多张表组成的,往往需要从多张表中查找数据
这时候就会用到表连接,也就是两张或者多张表如何组成一张大表,然后从大表里查找数据
连表方式
我们先来看看两张表连接的情况,我们假设两张表,A 表 和 B 表,如下图
当我们把两张表和在一起的时候,有一种拼合法只取表头颜色一样的拼在一起,不一样的扔掉,就像下图一样,这种拼接法,我们称之为 内连接
还有一种拼接法就是把 A
表中的都留下来,然后从 B
中找对应的颜色粘合在一起,就像下图一样,我们称之为 左连接
还有一种拼接法就是把 B
表中的都留下来,然后从 A
表里找对应的颜色粘合在一起,就像下图一样,我们称之为 右连接
最后一种拼接法,就是 A
表中的都留下,然后从 B
表中找对应的颜色粘合在一起,剩下的那些 B
表中的则全部排在最后,并不扔掉,就先下图一样,我们称之为 外链接
注意: 记住我刚刚说的查找过程,因为这是面试常考题目,就是先把一张表的记录查出来,然后根据记录一条一条去另一张表里找,这是不是很像我们堆积木游戏一样,表连接的本质过程也是这样,所以才导致了表连接很慢
如果有三张,四张,五张表也是同一个道理
这里有一点没演示到,就是如果任何一张表中有重复的表头颜色,那么也会重复显示一次
MySQL 中的连表
MySQL
支持上面四种连接中的前三种,第四种外连接不支持
可以在SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询
连表方式 | 名称 | 说明 |
---|---|---|
INNER JOIN | 内连接 | 获取两个表中字段匹配关系的记录 |
LEFT JOIN | 左连接 | 获取左表所有记录,即使右表没有对应匹配的记录 |
RIGHT JOIN | 右连接 | 获取右表所有记录,即使左表没有对应匹配的记录 |
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://pottercoding.cn','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://pottercoding.cn | 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 [souyunku]>` 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://pottercoding.cn | 5.083% |
| PHP | http://www.php.net | 4.218% |
| Ruby | https://www.ruby-lang.org/ | 2.018% |
| Python | https://pottercoding.cn | 5.869% |
| PHP | http://www.php.net | 4.010% |
| Ruby | https://www.ruby-lang.org/ | 2.744% |
| Python | https://pottercoding.cn | 5.168% |
| PHP | http://www.php.net | 3.420% |
| Ruby | https://www.ruby-lang.org/ | 2.534% |
+--------+----------------------------+--------+
9 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 [souyunku]>` SELECT tbl_language.name,url,rate FROM tbl_language JOIN tbl_rank ON tbl_language.name = tbl_rank.name;
+--------+----------------------------+--------+
| name | url | rate |
+--------+----------------------------+--------+
| Python | https://pottercoding.cn | 5.083% |
| PHP | http://www.php.net | 4.218% |
| Ruby | https://www.ruby-lang.org/ | 2.018% |
| Python | https://pottercoding.cn | 5.869% |
| PHP | http://www.php.net | 4.010% |
| Ruby | https://www.ruby-lang.org/ | 2.744% |
| Python | https://pottercoding.cn | 5.168% |
| PHP | http://www.php.net | 3.420% |
| Ruby | https://www.ruby-lang.org/ | 2.534% |
+--------+----------------------------+--------+
9 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 [souyunku]>` SELECT tbl_language.name,url,rate FROM tbl_language,tbl_rank WHERE tbl_language.name = tbl_rank.name;
+--------+----------------------------+--------+
| name | url | rate |
+--------+----------------------------+--------+
| Python | https://pottercoding.cn | 5.083% |
| PHP | http://www.php.net | 4.218% |
| Ruby | https://www.ruby-lang.org/ | 2.018% |
| Python | https://pottercoding.cn | 5.869% |
| PHP | http://www.php.net | 4.010% |
| Ruby | https://www.ruby-lang.org/ | 2.744% |
| Python | https://pottercoding.cn | 5.168% |
| PHP | http://www.php.net | 3.420% |
| Ruby | https://www.ruby-lang.org/ | 2.534% |
+--------+----------------------------+--------+
9 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 [souyunku]>` 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://pottercoding.cn | 5.083% |
| PHP | http://www.php.net | 4.218% |
| Ruby | https://www.ruby-lang.org/ | 2.018% |
| Python | https://pottercoding.cn | 5.869% |
| PHP | http://www.php.net | 4.010% |
| Ruby | https://www.ruby-lang.org/ | 2.744% |
| Python | https://pottercoding.cn | 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 [souyunku]>` 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://pottercoding.cn | 5.083% |
| Python | https://pottercoding.cn | 5.869% |
| Python | https://pottercoding.cn | 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=souyunku', '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://pottercoding.cn"
[1]=>
string(19) "https://pottercoding.cn"
["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://pottercoding.cn"
[1]=>
string(19) "https://pottercoding.cn"
["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://pottercoding.cn"
[1]=>
string(19) "https://pottercoding.cn"
["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%"