MySQL 连接代码笔记

MySQL inner join 

mysql> select * from testA;
+-------+--------+
| class | action |
+-------+--------+
|     1 | 并     |
|     2 | 交     |
|     3 | 除     |
|     4 | 选择   |
|     2 | 投影   |
+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from testB;
+----+-------+---------+------------+
| id | name  | address | time       |
+----+-------+---------+------------+
|  1 | c语言 | 昨天    | 2011-09-09 |
|  2 | MySQL | 今天    | 2018-06-06 |
|  3 | C++   | 明天    | 2018-09-09 |
|  4 | GO    | 未来    | 2019-09-08 |
+----+-------+---------+------------+
4 rows in set (0.00 sec)

mysql> select testA.class, testB.address, testB.time
    -> from testA inner join testB on testA.class = testB.id;
+-------+---------+------------+
| class | address | time       |
+-------+---------+------------+
|     1 | 昨天    | 2011-09-09 |
|     2 | 今天    | 2018-06-06 |
|     2 | 今天    | 2018-06-06 |
|     3 | 明天    | 2018-09-09 |
|     4 | 未来    | 2019-09-08 |
+-------+---------+------------+
5 rows in set (0.50 sec)

//等价于

mysql> select testA.class, testB.address, testB.time
    -> from testA,testB where testA.class = testB.id;
+-------+---------+------------+
| class | address | time       |
+-------+---------+------------+
|     1 | 昨天    | 2011-09-09 |
|     2 | 今天    | 2018-06-06 |
|     2 | 今天    | 2018-06-06 |
|     3 | 明天    | 2018-09-09 |
|     4 | 未来    | 2019-09-08 |
+-------+---------+------------+
5 rows in set (0.00 sec)

MySQL left join

mysql> select * from testA;
+-------+--------+
| class | action |
+-------+--------+
|     1 | 并     |
|     2 | 交     |
|     3 | 除     |
|     4 | 选择   |
|     2 | 投影   |
+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from testB;
+----+-------+---------+------------+
| id | name  | address | time       |
+----+-------+---------+------------+
|  1 | c语言 | 昨天    | 2011-09-09 |
|  2 | MySQL | 今天    | 2018-06-06 |
|  3 | C++   | 明天    | 2018-09-09 |
|  4 | GO    | 未来    | 2019-09-08 |
+----+-------+---------+------------+
4 rows in set (0.00 sec)

mysql> select testA.class, testB.address, testB.time
    -> from testA left join testB on testA.class = testB.id;
+-------+---------+------------+
| class | address | time       |
+-------+---------+------------+
|     1 | 昨天    | 2011-09-09 |
|     2 | 今天    | 2018-06-06 |
|     3 | 明天    | 2018-09-09 |
|     4 | 未来    | 2019-09-08 |
|     2 | 今天    | 2018-06-06 |
+-------+---------+------------+
5 rows in set (0.86 sec)

MySQL right join

mysql> update testA set class = 8 where action = '除';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update testA set class = 6 where action = '投影';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from testA;
+-------+--------+
| class | action |
+-------+--------+
|     1 | 并     |
|     2 | 交     |
|     8 | 除     |
|     4 | 选择   |
|     6 | 投影   |
+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from testB;
+----+-------+---------+------------+
| id | name  | address | time       |
+----+-------+---------+------------+
|  1 | c语言 | 昨天    | 2011-09-09 |
|  2 | MySQL | 今天    | 2018-06-06 |
|  3 | C++   | 明天    | 2018-09-09 |
|  4 | GO    | 未来    | 2019-09-08 |
+----+-------+---------+------------+
4 rows in set (0.00 sec)

mysql> select testA.class, testB.address, testB.time
    -> from testA right join testB on testA.class = testB.id;
+-------+---------+------------+
| class | address | time       |
+-------+---------+------------+
|     1 | 昨天    | 2011-09-09 |
|     2 | 今天    | 2018-06-06 |
|  NULL | 明天    | 2018-09-09 |
|     4 | 未来    | 2019-09-08 |
+-------+---------+------------+
4 rows in set (0.00 sec)