Article directory
MySQL databaseinner join multi-table usage? left left join usage efficiency
Mysql use of connection
In the previous chapters, we have learned how to read data from one table, which is relatively simple, but in real applications it is often necessary to read data from multiple data tables.
In this chapter we will show you how to use MySQL's JOIN to query data in two or more tables.
You can use Mysql's JOIN in SELECT, UPDATE and DELETE statements to join multi-table queries.
JOIN is roughly divided into the following three categories according to its function:
- INNER JOIN (inner join, or equijoin): Get records with matching fields in two tables.
- LEFT JOIN (left join):Get all records in the left table, even if there are no matching records in the right table.
- RIGHT JOIN (right join): Contrary to LEFT JOIN, it is used to get all records in the right table, even if there are no corresponding matching records in the left table.
Using INNER JOIN in Command Prompt
We have two tables tcount_tbl and chenweiliang_tbl in the chenweiliang database.The data of the two data tables are as follows:
Examples
Try the following examples:
test instance data
mysql> use chenweiliang; Database changed mysql> SELECT * FROM tcount_tbl; +---------------+--------------+ | chenweiliang_author | chenweiliang_count | +---------------+--------------+ | 陈沩亮博客 | 10 | | chenweiliang.com | 20 | | Google | 22 | +---------------+--------------+ 3 rows in set (0.01 sec) mysql> SELECT * from chenweiliang_tbl; +-----------+---------------+---------------+-----------------+ | chenweiliang_id | chenweiliang_title | chenweiliang_author | submission_date | +-----------+---------------+---------------+-----------------+ | 1 | 学习 PHP | 陈沩亮博客 | 2017-04-12 | | 2 | 学习 MySQL | 陈沩亮博客 | 2017-04-12 | | 3 | 学习 Java | chenweiliang.com | 2015-05-01 | | 4 | 学习 Python | chenweiliang.com | 2016-03-06 | | 5 | 学习 C | FK | 2017-04-05 | +-----------+---------------+---------------+-----------------+ 5 rows in set (0.01 sec)
Next we will use MySQLINNER JOIN (you can also omit INNER and use JOIN, the effect is the same)To connect the above two tables to read the value of the chenweiliang_count field corresponding to all the chenweiliang_author fields in the chenweiliang_tbl table in the tcount_tbl table:
INNER JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.00 sec)
The above SQL statement is equivalent to:
WHERE clause
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a, tcount_tbl b WHERE a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | +-------------+-----------------+----------------+ 4 rows in set (0.01 sec)
MySQL LEFT JOIN
MySQL left join is different from join. MySQL LEFT JOIN will read all the data in the left data table, even if the right table has no corresponding data.
Examples
Try the following examples to chenweiliang_tbl for the left table,tcount_tbl For the right table, understand the application of MySQL LEFT JOIN:
LEFT JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a LEFT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | 5 | FK | NULL | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
In the above example, LEFT JOIN is used. This statement will read all the selected field data of the data table chenweiliang_tbl on the left, even if there is no corresponding field value of chenweiliang_author in the table tcount_tbl on the right.
MySQL RIGHT JOIN
MySQL RIGHT JOIN will read all the data in the data table on the right, even if there is no corresponding data in the table on the left.
Examples
Try the following examples to chenweiliang_tbl for the left table,tcount_tbl For the right table, understand the application of MySQL RIGHT JOIN:
RIGHT JOIN
mysql> SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a RIGHT JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author; +-------------+-----------------+----------------+ | a.chenweiliang_id | a.chenweiliang_author | b.chenweiliang_count | +-------------+-----------------+----------------+ | 1 | 陈沩亮博客 | 10 | | 2 | 陈沩亮博客 | 10 | | 3 | chenweiliang.com | 20 | | 4 | chenweiliang.com | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)
RIGHT JOIN is used in the above example, this statement will read all the selected field data of the data table tcount_tbl on the right, even if there is no corresponding field value of chenweiliang_author in the table chenweiliang_tbl on the left.
Using JOIN in PHP script
The mysqli_query() function is used in PHP to execute SQL statements, you can use the same SQL statement as above as a parameter to the mysqli_query() function.
Try the following example:
MySQL ORDER BY test:
<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT a.chenweiliang_id, a.chenweiliang_author, b.chenweiliang_count FROM chenweiliang_tbl a INNER JOIN tcount_tbl b ON a.chenweiliang_author = b.chenweiliang_author';
mysqli_select_db( $conn, 'chenweiliang' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>陈沩亮博客 MySQL JOIN 测试<h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>作者</td><td>登陆次数</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
echo "<tr><td> {$row['chenweiliang_id']}</td> ".
"<td>{$row['chenweiliang_author']} </td> ".
"<td>{$row['chenweiliang_count']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>Hope Chen Weiliang Blog ( https://www.chenweiliang.com/ ) shared "Multi-table usage of MySQL database inner join? left Left Join Usage Efficiency", it will help you.
Welcome to share the link of this article:https://www.chenweiliang.com/cwl-488.html