在前面的章节中，我们一次从一个表中获取数据.这对于简单的浏览非常有用，但在大多数现实世界的MySQL用法中，您通常需要在单个查询中从多个表中获取数据.
您可以在多个表中使用您的单个SQL查询.加入MySQL的行为是指将两个或多个表粉碎成一个表.
您可以在SELECT，UPDATE和DELETE语句中使用JOINS来加入MySQL表.我们将看到一个LEFT JOIN的例子，它与简单的MySQL JOIN不同.
假设我们有两个表 tcount_tbl 和 tutorials_tbl ，在TUTORIALS中.现在看看下面给出的示例 :
以下示例 :
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT * FROM tcount_tbl; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | mahnaz | NULL | | Jen | NULL | | Gill | 20 | | John Poul | 1 | | Sanjay | 1 | +-----------------+----------------+ 6 rows in set (0.01 sec) mysql> SELECT * from tutorials_tbl; +-------------+----------------+-----------------+-----------------+ | tutorial_id | tutorial_title | tutorial_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 1 | Learn PHP | John Poul | 2007-05-24 | | 2 | Learn MySQL | Abdul S | 2007-05-24 | | 3 | JAVA Tutorial | Sanjay | 2007-05-06 | +-------------+----------------+-----------------+-----------------+ 3 rows in set (0.00 sec) mysql>
现在我们可以编写一个SQL查询来连接这两个表.此查询将从表 tutorials_tbl 中选择所有作者，并从 tcount_tbl 中选取相应数量的教程.
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a, tcount_tbl b -> WHERE a.tutorial_author = b.tutorial_author; +-------------+-----------------+----------------+ | tutorial_id | tutorial_author | tutorial_count | +-------------+-----------------+----------------+ | 1 | John Poul | 1 | | 3 | Sanjay | 1 | +-------------+-----------------+----------------+ 2 rows in set (0.01 sec) mysql>
您可以在PHP脚本中使用上述任何SQL查询.您只需要将SQL查询传递给PHP函数 mysql_query()，然后您将以通常的方式获取结果.
以下示例 :
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author';
mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo "Author:{$row['tutorial_author']} <br> ".
"Count: {$row['tutorial_count']} <br> ".
"Tutorial ID: {$row['tutorial_id']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>MySQL左连接与简单连接不同. MySQL LEFT JOIN为左边的表格提供了额外的考虑.
如果我执行 LEFT JOIN ，我会得到所有匹配的记录以同样的方式和IN ADDITION我得到了连接左表中每个不匹配记录的额外记录:从而确保(在我的例子中)每个AUTHOR都被提及.
尝试以下示例来了解LEFT JOIN.
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b -> ON a.tutorial_author = b.tutorial_author; +-------------+-----------------+----------------+ | tutorial_id | tutorial_author | tutorial_count | +-------------+-----------------+----------------+ | 1 | John Poul | 1 | | 2 | Abdul S | NULL | | 3 | Sanjay | 1 | +-------------+-----------------+----------------+ 3 rows in set (0.02 sec)
您需要做更多练习才能熟悉JOINS.这在MySQL/SQL中稍微有点复杂，在做实例时会更加清晰.
