这篇文章给大家分享的是有关MySQL与PHP中的表连接怎么用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。
表连接
表连接 (JOIN) 是在多个表之间通过连接条件. 使表之间发生关联, 进而能从多个表之间获取数据.
表连接语法:
SELECT 表名1.列名, 表名2.列名 FROM 表1, 表2 WHERE 条件
表连接的几种方式:
内连接: join 或者 inner join
自连接: 同一张表内的连接
外连接:
左外连接, left join
右外连接, right join
全外连接, full join
数据 (table1):
数据 (table2):
内连接
内连接是从结果表中删除与其他被连接表中没有匹配的所有行. (交集)
格式:
SELECT A.c1, B.c2 FROM A JOIN B ON A.c3 = B.c3
例子:
<?php$conn = mysqli_connect("localhost", "root","admin","study");if ($conn) { echo "数据库链接成功\n";} else { echo mysqli_connect_error();}# SQL语句$SQL = "SELECT table1.name, table1.age, table2.class FROM table1 JOIN table2 ON table1.name = table2.name";# 执行$result = mysqli_query($conn, $SQL);# 查看是否执行成功if ($result) { echo "SQL 语句执行成功!\n";}else { echo mysqli_error($conn);}# 调试输出while ($line = mysqli_fetch_assoc($result)) { print_r($line);}# 关闭连接mysqli_close($conn);?>输出结果:
数据库链接成功
SQL 语句执行成功!
Array
(
[name] => 杨富贵
[age] => 18
[class] => 数学课
)
Array
(
[name] => 杨美丽
[age] => 19
[class] => 语文课
)
Array
(
[name] => 杨建国
[age] => 20
[class] => 英语课
)
左连接
包含做表的全部行 (不管右表是否存在与之匹配的行), 以及右表中全部匹配的行.
格式:
SELECT A.c1, B.c2 FROM A LEFT JOIN B ON A.c3 = B.c3
例子:
<?php$conn = mysqli_connect("localhost", "root","admin","study");if ($conn) { echo "数据库链接成功\n";} else { echo mysqli_connect_error();}# SQL语句$SQL = "SELECT table1.name, table1.age, table2.class FROM table1 LEFT JOIN table2 ON table1.name = table2.name";# 执行$result = mysqli_query($conn, $SQL);# 查看是否执行成功if ($result) { echo "SQL 语句执行成功!\n";}else { echo mysqli_error($conn);}# 调试输出while ($line = mysqli_fetch_assoc($result)) { print_r($line);}# 关闭连接mysqli_close($conn);?>输出结果:
数据库链接成功
SQL 语句执行成功!
Array
(
[name] => 杨富贵
[age] => 18
[class] => 数学课
)
Array
(
[name] => 杨美丽
[age] => 19
[class] => 语文课
)
Array
(
[name] => 杨建国
[age] => 20
[class] => 英语课
)
Array
(
[name] => 杨铁柱
[age] => 21
[class] =>
)
右连接
包含右表的全部行 (不管左表是否存在与之匹配的行), 以及左表中全部匹配的行.
格式:
SELECT A.c1, B.c2 FROM A RIGHT JOIN B ON A.c3 = B.c3
例子:
<?php$conn = mysqli_connect("localhost", "root","admin","study");if ($conn) { echo "数据库链接成功\n";} else { echo mysqli_connect_error();}# SQL语句$SQL = "SELECT table1.name, table1.age, table2.class FROM table1 RIGHT JOIN table2 ON table1.name = table2.name";# 执行$result = mysqli_query($conn, $SQL);# 查看是否执行成功if ($result) { echo "SQL 语句执行成功!\n";}else { echo mysqli_error($conn);}# 调试输出while ($line = mysqli_fetch_assoc($result)) { print_r($line);}# 关闭连接mysqli_close($conn);?>输出结果:
数据库链接成功
SQL 语句执行成功!
Array
(
[name] => 杨富贵
[age] => 18
[class] => 数学课
)
Array
(
[name] => 杨美丽
[age] => 19
[class] => 语文课
)
Array
(
[name] => 杨建国
[age] => 20
[class] => 英语课
)
Array
(
[name] =>
[age] =>
[class] => 历史课
)
全连接
包含左右两个表的全部行 (不管在另一个表中是否存在与之匹配的行).
格式:
SELECT A.c1, B.c2 FROM A LEFT JOIN B ON A.c3 = B.c3
UNION
SELECT A.c1, B.c2 FROM A RIGHT JOIN B ON A.c3 = B.c3
例子:
<?php$conn = mysqli_connect("localhost", "root","admin","study");if ($conn) { echo "数据库链接成功\n";} else { echo mysqli_connect_error();}# SQL语句$SQL = "SELECT table1.name, table1.age, table2.class FROM table1 LEFT JOIN table2 ON table1.name = table2.name UNION SELECT table1.name, table1.age, table2.class FROM table1 RIGHT JOIN table2 ON table1.name = table2.name";# 执行$result = mysqli_query($conn, $SQL);# 查看是否执行成功if ($result) { echo "SQL 语句执行成功!\n";}else { echo mysqli_error($conn);}# 调试输出while ($line = mysqli_fetch_assoc($result)) { print_r($line);}# 关闭连接mysqli_close($conn);?>输出结果:
数据库链接成功
SQL 语句执行成功!
Array
(
[name] => 杨富贵
[age] => 18
[class] => 数学课
)
Array
(
[name] => 杨美丽
[age] => 19
[class] => 语文课
)
Array
(
[name] => 杨建国
[age] => 20
[class] => 英语课
)
Array
(
[name] => 杨铁柱
[age] => 21
[class] =>
)
Array
(
[name] =>
[age] =>
[class] => 历史课
)
感谢各位的阅读！关于“MySQL与PHP中的表连接怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
