Mysqli 简明教程
MySQLi - Using Joins
在之前的章节中,我们一次从一个表中获取数据。这对于简单的数据获取已经足够了,但是在大多数现实世界的 MySQL 用法中,您通常需要在单个查询中从多个表中获取数据。
In the previous chapters, we were getting data from one table at a time. This is good enough for simple takes, but in most of the real world MySQL usages, you will often need to get data from multiple tables in a single query.
您可以在单个 SQL 查询中使用多个表。在 MySQL 中进行连接操作是指将两个或更多个表组合成一个表。
You can use multiple tables in your single SQL query. The act of joining in MySQL refers to smashing two or more tables into a single table.
您可以在 SELECT、UPDATE 和 DELETE 语句中使用 JOINS 来连接 MySQL 表。我们还将看到 LEFT JOIN 的一个示例,它不同于简单的 MySQL JOIN。
You can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables. We will see an example of the LEFT JOIN also which is different from the simple MySQL JOIN.
Using Joins at the Command Prompt
假设我们在 TUTORIALS 中有两个表 tcount_tbl 和 tutorials_tbl 。现在,请查看以下给出的示例 -
Assume we have two tables tcount_tbl and tutorials_tbl, in TUTORIALS. Now take a look at the examples given below −
Example
以下示例 -
The following examples −
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 中选取相应的教程数量。
Now we can write an SQL query to join these two tables. This query will select all the authors from table tutorials_tbl and will pick up the corresponding number of tutorials from the 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>
Using Joins in a PHP Script
PHP 使用 mysqli query() 或 mysql_query() 函数来使用联接从 MySQL 表中获取记录。这个函数接收两个参数,并在成功时返回 TRUE,并在失败时返回 FALSE。
PHP uses mysqli query() or mysql_query() function to get records from a MySQL tables using Joins. This function takes two parameters and returns TRUE on success or FALSE on failure.
Syntax
$mysqli→query($sql,$resultmode)
Sr.No. |
Parameter & Description |
1 |
$sql Required - SQL query to get records from multiple tables using Join. |
2 |
$resultmode Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used. |
首先使用以下脚本在 MySQL 中创建表并插入两条记录。
First create a table in MySQL using following script and insert two records.
create table tcount_tbl(
tutorial_author VARCHAR(40) NOT NULL,
tutorial_count int
);
insert into tcount_tbl values('Mahesh', 3);
insert into tcount_tbl values('Suresh', 1);
Example
尝试以下示例以使用连接获取两个表中的记录。−
Try the following example to get records from a two tables using Join. −
将以下示例复制粘贴为 mysql_example.php:
Copy and paste the following example as mysql_example.php −
<html>
<head>
<title>Using joins on MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'root@123';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli→connect_errno ) {
printf("Connect failed: %s<br />", $mysqli→connect_error);
exit();
}
printf('Connected successfully.<br />');
$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';
$result = $mysqli→query($sql);
if ($result→num_rows > 0) {
while($row = $result→fetch_assoc()) {
printf("Id: %s, Author: %s, Count: %d <br />",
$row["tutorial_id"],
$row["tutorial_author"],
$row["tutorial_count"]);
}
} else {
printf('No record found.<br />');
}
mysqli_free_result($result);
$mysqli→close();
?>
</body>
</html>
Output
访问部署在 Apache Web 服务器上的 mysql_example.php, 并验证输出。
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully.
Id: 1, Author: Mahesh, Count: 3
Id: 2, Author: Mahesh, Count: 3
Id: 3, Author: Mahesh, Count: 3
Id: 5, Author: Suresh, Count: 1
MySQL LEFT JOIN
MySQL 左连接不同于简单连接。MySQL LEFT JOIN 会更加考虑表中左侧的列。
A MySQL left join is different from a simple join. A MySQL LEFT JOIN gives some extra consideration to the table that is on the left.
如果我执行 LEFT JOIN ,我将获得所有匹配的记录,并且另外,我将获得连接表左侧中每个不匹配记录的额外记录:从而确保(在我的示例中)每个 AUTHOR 都被提及。
If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join: thus ensuring (in my example) that every AUTHOR gets a mention.
Example
尝试以下示例来了解 LEFT JOIN。
Try the following example to understand the 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 概念,并且在实际操作时会变得更加清晰。
You would need to do more practice to become familiar with JOINS. This is slightly a bit complex concept in MySQL/SQL and will become more clear while doing real examples.