sql join教程

首先默认你已经安装好mysql

• 导数数据 代码链接

mysql> create database joinn; # 创建数据库 mysql> use joinn; # 使用已创建的数据库 mysql> set names utf8; # 设置编码 mysql> source /home/lg/lg/mysql_better/join_learn/runoob-mysql-join-test.sql # 注意要把你的目录切换mysql> use joinn; mysql> show tables; +-----------------+ | Tables_in_joinn | +-----------------+ | runoob_tbl | | tcount_tbl | +-----------------+ 2 rows in set (0.00 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 | +-----------+---------------+---------------+-----------------+

mysql> select * from tcount_tbl;

+---------------+--------------+ | runoob_author | runoob_count | +---------------+--------------+ | 菜鸟教程 | 10 | | RUNOOB.COM | 20 | | Google | 22 | +---------------+--------------+ 3 rows in set (0.01 sec)

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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-----------+---------------+--------------+ 4 rows in set (0.00 sec)

from 后面我加了一个括号，可以理解一个整体

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); +-----------+---------------+--------------+ | runoob_id | runoob_author | 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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | +-----------+---------------+--------------+ 4 rows in set (0.00 sec)

leftjoin

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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | 5 | FK | NULL | +-----------+---------------+--------------+

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; +-----------+---------------+--------------+ | runoob_id | runoob_author | runoob_count | +-----------+---------------+--------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-----------+---------------+--------------+ 5 rows in set (0.00 sec)

下面这段sql 是两张表的笛卡尔积

mysql> SELECT * from tcount_tbl,runoob_tbl; +---------------+--------------+-----------+---------------+---------------+-----------------+ | runoob_author | runoob_count | runoob_id | runoob_title | runoob_author | submission_date | +---------------+--------------+-----------+---------------+---------------+-----------------+ | 菜鸟教程 | 10 | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | RUNOOB.COM | 20 | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | Google | 22 | 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 | | 菜鸟教程 | 10 | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | RUNOOB.COM | 20 | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | Google | 22 | 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 | | 菜鸟教程 | 10 | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | RUNOOB.COM | 20 | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | Google | 22 | 3 | 学习 Java | RUNOOB.COM | 2015-05-01 | | 菜鸟教程 | 10 | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | RUNOOB.COM | 20 | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | Google | 22 | 4 | 学习 Python | RUNOOB.COM | 2016-03-06 | | 菜鸟教程 | 10 | 5 | 学习 C | FK | 2017-04-05 | | RUNOOB.COM | 20 | 5 | 学习 C | FK | 2017-04-05 | | Google | 22 | 5 | 学习 C | FK | 2017-04-05 | +---------------+--------------+-----------+---------------+---------------+-----------------+ 15 rows in set (0.00 sec)

总结

以上是生活随笔为你收集整理的sql join教程的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。