到目前为止,我们一次只能从一个表中获取数据.这对于简单的操作来说很好,但是在大多数真实的MySQL使用中,您通常需要在单个查询中从多个表中获取数据.
您可以在单个SQL中使用多个表查询.加入MySQLi的行为是指将两个或多个表粉碎成一个表.
您可以在SELECT,UPDATE和DELETE语句中使用JOINS来连接MySQLi表.我们将看到一个LEFT JOIN的例子,它与简单的MySQLi JOIN不同.
假设我们有两个表 tutorials_bks 和 tutorials_inf ,在TUTORIALS中.完整列表在下面和下面给出;
尝试以下示例 :
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> SELECT * FROM tcount_bks; +----+---------+ | id | book | +----+---------+ | 1 | java | | 2 | java | | 3 | html | | 4 | c++ | | 5 | Android | +----+---------+ 5 rows in set (0.00 sec) mysql> SELECT * from tutorials_inf; +----+-------+ | id | name | +----+-------+ | 1 | sai | | 2 | johar | | 3 | raghu | | 4 | ram | +----+-------+ 4 rows in set (0.00 sec) mysql>
现在我们可以编写一个SQL查询来连接这两个表.此查询将从表 tutorials_inf 中选择所有名称,并从 tutorials_bks 中提取相应数量的教程.
mysql> SELECT a.id, a.name,b.id FROM tutorials_inf a,tutorials_bks b WHERE a.id = b.id; +----+-------+----+ | id | name | id | +----+-------+----+ | 1 | sai | 1 | | 2 | johar | 2 | | 3 | raghu | 3 | | 4 | ram | 4 | +----+-------+----+ 4 rows in set (0.00 sec) mysql>
在tutorials_bks表中,我们有5条记录,但在上面的示例中,它根据查询过滤并仅提供4条id记录
您可以在PHP脚本中使用上述任何SQL查询.您只需要将SQL查询传递给PHP函数 mysqli_query(),然后您将以通常的方式获取结果.
尝试以下示例 :
<?php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = ''; $dbname = 'TUTORIALS'; $conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname); if(! $conn ) { die('Could not connect: ' . mysqli_error()); } echo 'Connected successfully</br>'; $sql = 'SELECT a.id, a.name,b.id FROM tutorials_inf a,tutorials_bks b WHERE a.id = b.id'; if($result = mysqli_query($conn, $sql)) { if(mysqli_num_rows($result) > 0) { echo "<table>"; echo "<tr>"; echo "<th>id</th>"; echo "<th>name</th>"; echo "<th>id</th>"; echo "</tr>"; while($row = mysqli_fetch_array($result)){ echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['name'] . "</td>"; echo "<td>" . $row['id'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_free_result($result); } else { echo "No records matching your query were found."; } } else { echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn); } mysqli_close($conn); ?>
示例输出应该是这样的 :
Connected successfully id name id 1 sai 1 2 johar 2 3 raghu 3 4 ram 4
MySQLi左连接与简单连接不同. MySQLi LEFT JOIN额外考虑了左边的表格.
如果我进行LEFT JOIN,我会以相同的方式获得匹配的所有记录,并且我会得到更多在连接左表中的每个不匹配记录的额外记录 - 从而确保(在我的示例中)每个名称都得到提及 :
尝试以下示例以了解LEFT JOIN :
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql>SELECT a.id, a.name,b.id FROM tutorials_inf a LEFT JOIN tutorials_bks b ON a.id = b.id;
您需要做更多练习才能熟悉JOINS.这在MySQL/SQL中有点复杂,在做实例时会更加清晰.