Mysql Join usage tutorial

Usage of Mysql Join

In the previous chapters, we have learned how to read data from one table. This is relatively simple, but in real applications it is often necessary to read data from multiple data tables. Read data in.

In this chapter we will introduce to 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 combine multiple table queries.

Below we will demonstrate the difference between the use of MySQL LEFT JOIN and JOIN.

Using JOIN in the command prompt

We have two tables tcount_tbl and tutorials_tbl in the TUTORIALS database. The data of the two data tables are as follows:

Try the following example:

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>

Next we will use MySQL's JOIN to connect the above two tables to read all the tutorial_author fields in the tutorials_tbl table in the tcount_tbl table Corresponding tutorial_count field value:

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 JOIN in PHP script

Use the mysql_query() function in PHP to execute SQL statements. You can use the same SQL statement above as mysql_query() function parameters.

Try the following example:

<?php$dbhost = &#39;localhost:3036&#39;;$dbuser = &#39;root&#39;;$dbpass = &#39;rootpassword&#39;;$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die(&#39;Could not connect: &#39; . mysql_error());}$sql = &#39;SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count        FROM tutorials_tbl a, tcount_tbl b        WHERE a.tutorial_author = b.tutorial_author&#39;;mysql_select_db(&#39;TUTORIALS&#39;);$retval = mysql_query( $sql, $conn );if(! $retval ){  die(&#39;Could not get data: &#39; . mysql_error());}while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){    echo "Author:{$row[&#39;tutorial_author&#39;]}  <br> ".         "Count: {$row[&#39;tutorial_count&#39;]} <br> ".         "Tutorial ID: {$row[&#39;tutorial_id&#39;]} <br> ".         "--------------------------------<br>";} echo "Fetched data successfully\n";mysql_close($conn);?>

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 there is no corresponding data in the right table.

Example

尝试以下实例，理解MySQL LEFT JOIN的应用：
root@host# mysql -u root -p password;Enter password:*******mysql> use TUTORIALS;Database changedmysql> 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)

LEFT JOIN is used in the above example. This statement will read all selected field data in the left data table tutorials_tbl, even if there is no corresponding tutorial_author in the right table tcount_tbl. field value.

【Related Recommendations】

1. Special Recommendation:"php Programmer Toolbox" V0.1 version Download

2. Free mysql online video tutorial

3. Those things about database design

The above is the detailed content of Mysql Join usage tutorial. For more information, please follow other related articles on the PHP Chinese website!