mysql 使用技巧 where条件连接;inner join内连接;外连接(left outer join,right outer join)
程序员文章站
2022-04-15 23:27:28
mysql的inner join等价于where条件连接查询 内连接 inner join 省略形式 join 外连接 左连接 left outer join 省略形式 left join 右连接 right outer join 省略形式 right join 两张表内容: mysql> use ......
mysql的inner join等价于where条件连接查询
内连接
inner join 省略形式 join
外连接
左连接 left outer join 省略形式 left 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)
接下来我们就使用mysql的inner join(也可以省略 inner 使用 join,效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值:
inner join
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)
上一篇: go语言系列-常量