mysql 使用技巧 where条件连接；inner join内连接；外连接(left outer join,right outer join)[亲测有效]

小小码 • 2023-02-18 19:00 • 代码基础 • 阅读 104

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说mysql 使用技巧 where条件连接；inner join内连接；外连接(left outer join,right outer join)[亲测有效],希望您对编程的造诣更进一步.

mysql 使用技巧 where条件连接；inner join内连接；外连接(left outer join,right outer join)

mysql的inner join等价于where条件连接查询

内连接

mysql 使用技巧 where条件连接；inner join内连接；外连接(left outer join,right outer join)[亲测有效]

inner join 省略形式 join

外连接

mysql 使用技巧 where条件连接；inner join内连接；外连接(left outer join,right outer join)[亲测有效]

左连接 left outer join 省略形式 left join

mysql 使用技巧 where条件连接；inner join内连接；外连接(left outer join,right outer join)[亲测有效]

右连接 right outer join 省略形式 right 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)

代码100分

接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

inner join

代码100分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的条件查询

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           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.01 sec)