Shulou(Shulou.com)06/01 Report--

Use of Mysql connections

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

In this section, 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 federate multi-table queries.

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

INNER JOIN (inner join, or equivalent join): gets the record of the matching relationship between the fields in two tables.

LEFT JOIN (left join): gets all the 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 the records in the right table, even if there is no corresponding matching record in the left table.

The database structure and data download used in this chapter: runoob-mysql-join-test.sql.

Use INNER JOIN from a command prompt

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

Example

Try the following example:

Test instance data

Mysql > use RUNOOB;Database changedmysql > SELECT * FROM tcount_tbl +-+-+ | runoob_author | runoob_count | +-+-+ | Rookie tutorial | 10 | RUNOOB.COM | 20 | Google | 22 | +-+- -+ 3 rows in set (0.01sec)

Mysql > SELECT * from runoob_tbl +-+ | runoob_id | runoob_title | runoob_author | submission_date | +- -+-+ | 1 | study PHP | Rookie course | 2017-04-12 | | 2 | study MySQL | Rookie course | 2017-04-12 | | 3 | study Java | RUNOOB.COM | 2015-05-01 | | 4 | study Python | RUNOOB.COM | 2016-03-06 | | 5 | study C | FK | 2017-04-05 | +- +-+ 5 rows in set (0.01sec)

Next, we use MySQL's INNER JOIN (you can also omit INNER and use JOIN, the same effect) to join the above two tables to read the runoob_count field values of all runoob_author fields in the runoob_ tcount_tbl table corresponding to the tcount_tbl table:

INNER JOIN

Mysql > SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl an INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author +-+ | a.runoob_id | a.runoob_author | b.runoob_count | +-+ | 1 | Rookie tutorial | 10 | 2 | Rookie tutorial | 10 | 3 | RUNOOB.COM | 20 | 4 | RUNOOB.COM | 20 | +-+ 4 rows in set (0.00 sec)

The above SQL statement is equivalent to:

WHERE clause

Mysql > SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author +-+ | a.runoob_id | a.runoob_author | b.runoob_count | +-+ | 1 | Rookie tutorial | 10 | 2 | Rookie tutorial | 10 | 3 | RUNOOB.COM | 20 | 4 | RUNOOB.COM | 20 | +-+ 4 rows in set (0.01 sec)

MySQL LEFT JOIN

MySQL left join is different from join. MySQL LEFT JOIN reads all the data from the data table on the left, even if the table on the right has no corresponding data.

Example

Try the following example to understand the application of MySQL LEFT JOIN with runoob_tbl as the left table and tcount_tbl as the right table:

LEFT JOIN

Mysql > SELECT a.runoob_id, a.runoob_author, b.runoob_count

FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author +-+ | a.runoob_id | a.runoob_author | b.runoob_count | +-+ | 1 | Rookie tutorial | 10 | 2 | Rookie tutorial | 10 | 3 | RUNOOB.COM | 20 | 4 | RUNOOB.COM | 20 | 5 | FK | NULL | +-+ 5 rows in set (0.01sec)

LEFT JOIN is used in the above example, and this statement reads all selected field data from the data table runoob_tbl on the left, even if there is no corresponding runoob_author field value in the table tcount_tbl on the right.

MySQL RIGHT JOIN

MySQL RIGHT JOIN reads all the data from the data table on the right, even if the table on the left has no corresponding data.

Example

Try the following example to understand the application of MySQL RIGHT JOIN with runoob_tbl as the left table and tcount_tbl as the right table:

RIGHT JOIN

Mysql > SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author +-+ | a.runoob_id | a.runoob_author | b.runoob_count | +-+ | 1 | Rookie tutorial | 10 | 2 | Rookie tutorial | 10 | 3 | RUNOOB.COM | 20 | 4 | RUNOOB.COM | 20 | NULL | NULL | 22 | +-+ 5 rows in set (0.01sec)

RIGHT JOIN is used in the above example, and this statement reads all the selected field data from the data table tcount_tbl on the right, even if there is no corresponding runoob_author field value in the left table runoob_tbl.

Using JOIN in PHP scripts

The mysqli_query () function is used in PHP to execute the SQL statement. You can use the same SQL statement above as the argument to the mysqli_query () function.

Try the following example:

MySQL ORDER BY Test:

The output is shown in the following figure:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.