【Python】sql-内连接，左连接，右连接，union

内连接：

mysql> select * from book_wangjing as book_1 inner join user_wangjing as user_1 on book_1.id=user_1.id limit 2;

+----+------------------------+-------------+-------+--------------+----+---------------------+------------+---------------------+----------+

| id | book_name | book_author | price | publish_date | id | date1 | date2 | date3 | time |

+----+------------------------+-------------+-------+--------------+----+---------------------+------------+---------------------+----------+

| 1 | webdriver 框架实战指南 | 吴老师 | 100 | 2018-05-05 | 1 | 2018-05-05 00:08:24 | 2018-05-05 | 2018-05-05 00:08:24 | 00:08:24 |

| 2 | how google test | 李老师 | 99 | 2018-05-05 | 2 | 2018-05-05 00:08:54 | 2018-05-05 | 2018-05-05 00:08:54 | 00:08:54 |

+----+------------------------+-------------+-------+--------------+----+---------------------+------------+---------------------+----------+

2 rows in set (0.00 sec)

左连接：

mysql> select a.* from author_wangjing as a left join book_wangjing as b on a.author_name=b.book_author;

+----+-------------+--------+

| id | author_name | salary |

+----+-------------+--------+

| 1 | 吴老师 | 10000 |

| 3 | Mr Jackson | 15000 |

| 2 | 张老师 | 13000 |

+----+-------------+--------+

3 rows in set (0.07 sec)

右连接：

mysql> select * from user_wangjing as user_1 right join book_wangjing as book_1 on user_1.id=book_1.id;

+------+---------------------+------------+---------------------+----------+----+------------------------+-------------+-------+--------------+

| id | date1 | date2 | date3 | time | id | book_name | book_author | price | publish_date |

+------+---------------------+------------+---------------------+----------+----+------------------------+-------------+-------+--------------+

| 1 | 2018-05-05 00:08:24 | 2018-05-05 | 2018-05-05 00:08:24 | 00:08:24 | 1 | webdriver 框架实战指南 | 吴老师 | 100 | 2018-05-05 |

| 2 | 2018-05-05 00:08:54 | 2018-05-05 | 2018-05-05 00:08:54 | 00:08:54 | 2 | how google test | 李老师 | 99 | 2018-05-05 |

| 3 | 2018-05-05 00:08:55 | 2018-05-05 | 2018-05-05 00:08:55 | 00:08:55 | 3 | unit test | 李老师 | 1 | 2018-05-05 |

| 4 | 2018-05-05 00:08:56 | 2018-05-05 | 2018-05-05 00:08:56 | 00:08:56 | 4 | function test | 李老师 | 10 | 2017-11-10 |

| NULL | NULL | NULL | NULL | NULL | 5 | function test | Mr Jackson | 10 | 2013-06-10 |

+------+---------------------+------------+---------------------+----------+----+------------------------+-------------+-------+--------------+

5 rows in set (0.07 sec)

mysql> select a.* from author_wangjing as a right join book_wangjing as b on a.author_name=b.book_author;

+------+-------------+--------+

| id | author_name | salary |

+------+-------------+--------+

| 1 | 吴老师 | 10000 |

| 3 | Mr Jackson | 15000 |

| NULL | NULL | NULL |

| NULL | NULL | NULL |

| NULL | NULL | NULL |

+------+-------------+--------+

union：

mysql> select book_author from book_wangjing union select author_name from author_wangjing;

+-------------+

| book_author |

+-------------+

| 吴老师 |

| 李老师 |

| Mr Jackson |

| 张老师 |

+-------------+

4 rows in set (0.06 sec)

1、 内连接：结果集中出现的a.author_name和b.book_author必须同时在两个表存在

2、 左连接：结果集中出现的a.author_name必须在左表(author_wangjing表)存在且全部显示

3、 右连接：结果集中出现的b.book_author必须在右表(book_wangjing表)存在且全部显示

4、 union：做合并，使用union时左表的数据列要和右表的数据列一样多