Use of Mysql connection

Using Mysql connections

In the previous chapters, we have learned how to read data in a 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 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 join multi-table queries.

JOIN is roughly divided into the following three categories according to functions:

• INNER JOIN (inner join, or equivalent join): Get two tables Records of field matching relationships.

• LEFT JOIN (left join): Get all the records in the left table, even if there are no corresponding matching records in the right table.

• RIGHT JOIN (right join): Contrary to LEFT JOIN, it is used to obtain all records in the right table, even if there are no corresponding matching records in the left table.

The database structure and data download used in this chapter: php.sql.

Using INNER JOIN in the command prompt

We have two tables tcount_tbl and php_tbl in the PHP database. The data in the two data tables are as follows:

Examples

Try the following examples:

Test instance data

mysql> use php;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| php_author | php_count |
+---------------+--------------+
| PHP中文网  | 10           |
| PHP.CN    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 mysql> SELECT * from php_tbl;
+-----------+---------------+---------------+-----------------+
| php_id | php_title  | php_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | PHP中文网  | 2017-04-12      |
| 2         | 学习 MySQL  | PHP中文网   | 2017-04-12      |
| 3         | 学习 Java   | PHP.CN    | 2015-05-01      |
| 4         | 学习 Python | PHP.CN      | 2016-03-06      |
| 5         | 学习 C      | PHP           | 2017-04-05 |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

Next we will Use MySQL's INNER JOIN (you can also omit INNER and use JOIN, the effect is the same) to connect the above two tables to read all the php_author fields in the php_tbl table and the corresponding php_count field values ​​​​in the tcount_tbl table:

INNER JOIN

mysql> SELECT a.php_id, a.user_author, b.user_count
 FROM user_tbl a INNER JOIN tcount_tbl b ON a.user_author = b.user_author;
+-------------+-----------------+----------------+
| a.user_id | a.user_author | b.user_count |
+-------------+-----------------+----------------+
| 1           | PHP中文网    | 10             |
| 2           | PHP中文网     | 10             |
| 3           | PHP.CN     | 20             |
| 4           | PHP.CN      | 20             |
+-------------+-----------------+----------------+
 4 rows in set (0.00 sec)

The above SQL statement is equivalent to:

WHERE clause

mysql> SELECT a.user_id, a.user_author, b.user_count
 FROM user_tbl a, tcount_tbl b WHERE a.user_author = b.user_author;
+-------------+-----------------+----------------+
| a.user_id | a.user_author | b.user_count |
+-------------+-----------------+----------------+
| 1           | PHP中文网  | 10             |
| 2           | PHP中文网      | 10             |
| 3           |PHP.CN      | 20             |
| 4           | PHP.CN      | 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 there is no corresponding data in the right table.

Example

Try the following example, using php_tbl as the left table and tcount_tbl as the right table, to understand the application of MySQL LEFT JOIN:

RIGHT JOIN
mysql> SELECT a.user_id, a.user_author, b.user_count 
FROM user_tbl a RIGHT JOIN tcount_tbl b ON a.user_author = b.user_author;
+-------------+-----------------+----------------+
| a.user_id | a.user_author | b.user_count |
+-------------+-----------------+----------------+
| 1           | PHP中文网    | 10             |
| 2           | PHP中文网   | 10             |
| 3           | PHP.CN      | 20             |
| 4           | PHP.CN        | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

Above examples RIGHT JOIN is used in, this statement will read all selected field data in the data table tcount_tbl on the right, even if there is no corresponding php_author field value in the table php_tbl on the left.

MySQL RIGHT JOIN

MySQL RIGHT JOIN will read all the data in the right data table, even if there is no corresponding data in the left side table .

Example

Try the following example, using php_tbl as the left table and tcount_tbl as the right table, to understand the application of MySQL RIGHT JOIN:

RIGHT JOIN
mysql> SELECT a.user_id, a.user_author, b.user_count
 FROM user_tbl a RIGHT JOIN tcount_tbl b ON a.user_author = b.user_author;
+-------------+-----------------+----------------+
| a.user_id | a.user_author | b.user_count |
+-------------+-----------------+----------------+
| 1           | PHP中文网   | 10             |
| 2           | PHP中文网    | 10             |
| 3           |PHP.CN     | 20             |
| 4           | PHP.CN    | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+5 rows in set (0.01 sec)

RIGHT JOIN is used in the above example. This statement will read all selected field data from the data table tcount_tbl on the right, even if there is no corresponding user_author field value in the table user_tbl on the left.

Use JOIN in PHP script

Use the mysqli_query() function in PHP to execute SQL statements, you can Use the same SQL statement above as the argument to the mysqli_query() function.

Try the following example: