|
JOIN : 在两个或者多个表中查询数据,可以在 SELECT ,UPDAYE 和 DELETE 的语句中使用 MYSQL 的JOIN 联合多表查询。
INNER HOINF(内连接,或等值链接) : 获取两个表中字段匹配关系的记录。
LEFT JOIN (左连接 ) 获取左表中所有记录,即使右表没有队友的匹配记录。
RIGH JOIN(右连接) 与LEFT JOIN 相反,用于获取右表所有记录即使左表没有对应匹配的记录。
先来个测试数据- mysql> use RUNOOB;
- Database changed
- mysql> SELECT * FROM tcount_tbl;
- +---------------+--------------+
- | runoob_author | runoob_count |
- +---------------+--------------+
- | 菜鸟教程 | 10 |
- | RUNOOB.COM | 20 |
- | Google | 22 |
- +---------------+--------------+
- 3 rows in set (0.01 sec)
-
- mysql> SELECT * from runoob_tbl;
- +-----------+---------------+---------------+-----------------+
- | runoob_id | runoob_title | runoob_author | submission_date |
- +-----------+---------------+---------------+-----------------+
- | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
- | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
- | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
- | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
- | 5 | 学习 C | FK | 2017-04-05 |
- +-----------+---------------+---------------+-----------------+
- 5 rows in set (0.01 sec)
复制代码
第一测: 使用 MYSQL 的 INNER JOIN(也可以省略 INNER 直接使用 JOIN 效果一样)连接两张表 来读取 RUNOOB_TBL表中所有 RUNOOB_AUTHOR 字段在 TCOUNT_TBL 表中对应的 runoob_count 字段值:- mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
- +-------------+-----------------+----------------+
- | a.runoob_id | a.runoob_author | b.runoob_count |
- +-------------+-----------------+----------------+
- | 1 | 菜鸟教程 | 10 |
- | 2 | 菜鸟教程 | 10 |
- | 3 | RUNOOB.COM | 20 |
- | 4 | RUNOOB.COM | 20 |
- +-------------+-----------------+----------------+
- 4 rows in set (0.00 sec)
复制代码 就相当于 WHERE 语句 - SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
复制代码 LEFT JOIN 与 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 | 菜鸟教程 | 10 |
- | 2 | 菜鸟教程 | 10 |
- | 3 | RUNOOB.COM | 20 |
- | 4 | RUNOOB.COM | 20 |
- | 5 | FK | NULL |
- +-------------+-----------------+----------------+
- 5 rows in set (0.01 sec)
复制代码
RIGHTJOIN 与 上述相反 为右侧全表 - 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 | 菜鸟教程 | 10 |
- | 2 | 菜鸟教程 | 10 |
- | 3 | RUNOOB.COM | 20 |
- | 4 | RUNOOB.COM | 20 |
- | NULL | NULL | 22 |
- +-------------+-----------------+----------------+
- 5 rows in set (0.01 sec)
复制代码
PHP中的用法: 老套路 MYSQLI_QUERY()- <?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.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author
- mysqli_select_db( $conn, 'RUNOOB' );
- $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['runoob_id']}</td> ".
- "<td>{$row['runoob_author']} </td> ".
- "<td>{$row['runoob_count']} </td> ".
- "</tr>";
- }
- echo '</table>mysqli_close($conn);
- ?>
复制代码
|
|