mysql各种连接总结

mysql各种连接总结

术语解释：
1、内连接: 只连接匹配的行
2、左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
3、右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
4、全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
5、交叉连接: 笛卡尔积

例子：

CREATE DATABASE test_link DEFAULT CHARACTER SET 'utf8';

use test_link;

CREATE TABLE A (
 id varchar(50) NOT NULL,
 name varchar(50) NOT NULL,
 PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE B (
 id varchar(50) NOT NULL,
 addr varchar(50) NOT NULL,
 a_id varchar(50) not null,
 PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into A(id, name) values
('1', 'zhang'),
('2', 'li'),
('3', 'wang');

insert into B(id, addr, a_id) values
('1', 'beijing', '1'),
('2', 'shanghai', '3'),
('3', 'nanjing', '10');

mysql> select * from A;
+----+-------+
| id | name  |
+----+-------+
| 1  | zhang |
| 2  | li    |
| 3  | wang  |
+----+-------+
mysql> select * from B;
+----+----------+------+
| id | addr     | a_id |
+----+----------+------+
| 1  | beijing  | 1    |
| 2  | shanghai | 3    |
| 3  | nanjing  | 10   |
+----+----------+------+
左右连接
mysql> select A.name, B.addr from A left join B on A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
| li    | NULL     |
+-------+----------+
mysql> select A.name, B.addr from A right join B on A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
| NULL  | nanjing  |
+-------+----------+
内连接inner join
mysql> select A.name, B.addr from A inner join B on A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
+-------+----------+
自然连接
mysql> select A.name, B.addr from A, B where A.id = B.a_id;
+-------+----------+
| name  | addr     |
+-------+----------+
| zhang | beijing  |
| wang  | shanghai |
+-------+----------+
mysql> select * from A, B;
+----+-------+----+----------+------+
| id | name  | id | addr     | a_id |
+----+-------+----+----------+------+
| 1  | zhang | 1  | beijing  | 1    |
| 2  | li    | 1  | beijing  | 1    |
| 3  | wang  | 1  | beijing  | 1    |
| 1  | zhang | 2  | shanghai | 3    |
| 2  | li    | 2  | shanghai | 3    |
| 3  | wang  | 2  | shanghai | 3    |
| 1  | zhang | 3  | nanjing  | 10   |
| 2  | li    | 3  | nanjing  | 10   |
| 3  | wang  | 3  | nanjing  | 10   |
+----+-------+----+----------+------+
全连接
mysql> select A.name, B.addr from A full join B;
ERROR 
mysql> select * from A full outer join B;
ERROR 
mysql> select * from A full join B;
+----+-------+----+----------+------+
| id | name  | id | addr     | a_id |
+----+-------+----+----------+------+
| 1  | zhang | 1  | beijing  | 1    |
| 2  | li    | 1  | beijing  | 1    |
| 3  | wang  | 1  | beijing  | 1    |
| 1  | zhang | 2  | shanghai | 3    |
| 2  | li    | 2  | shanghai | 3    |
| 3  | wang  | 2  | shanghai | 3    |
| 1  | zhang | 3  | nanjing  | 10   |
| 2  | li    | 3  | nanjing  | 10   |
| 3  | wang  | 3  | nanjing  | 10   |
+----+-------+----+----------+------+
笛卡尔积连接
mysql> select * from A cross join B;
+----+-------+----+----------+------+
| id | name  | id | addr     | a_id |
+----+-------+----+----------+------+
| 1  | zhang | 1  | beijing  | 1    |
| 2  | li    | 1  | beijing  | 1    |
| 3  | wang  | 1  | beijing  | 1    |
| 1  | zhang | 2  | shanghai | 3    |
| 2  | li    | 2  | shanghai | 3    |
| 3  | wang  | 2  | shanghai | 3    |
| 1  | zhang | 3  | nanjing  | 10   |
| 2  | li    | 3  | nanjing  | 10   |
| 3  | wang  | 3  | nanjing  | 10   |
+----+-------+----+----------+------+

 

posted on 2018-09-28 15:54 wenlin_gk 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/wenlin-gk/p/9719135.html