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)