数据准备
1、建2张表
# 姓名表
create table table_name(
id int(11) primary key auto_increment,
user_id int(11) default 0,
name varchar(5) default ''
);
# 年龄表
create table table_age(
id int(11) primary key auto_increment,
user_id int(11) default 0,
age int(11) default 0
);
2、原始数据
# user_id, name, age
(1, "小赵", 21),
(2, "小钱", 22),
(3, "小孙", 23),
将6条数据分为两部分插入到数据库中
# 名字表少一条 user_id = 3
insert into table_name(user_id, name)
values(1, "小赵"), (2, "小钱");
# 年龄表少一条 user_id = 2
insert into table_age(user_id, age)
values(1, 21), (3, 23);
3、查看数据
mysql> select * from table_name;
+----+---------+--------+
| id | user_id | name |
+----+---------+--------+
| 1 | 1 | 小赵 |
| 2 | 2 | 小钱 |
+----+---------+--------+
mysql> select * from table_age;
+----+---------+------+
| id | user_id | age |
+----+---------+------+
| 1 | 1 | 21 |
| 3 | 3 | 23 |
+----+---------+------+
1、INNER JOIN（内连接）
mysql> select a.user_id, name, age
-> from table_name as a inner join table_age as b
-> on a.user_id=b.user_id;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 1 | 小赵 | 21 |
+---------+--------+------+
2、LEFT JOIN （左连接）
mysql> select a.user_id, name, age
from table_name as a left join table_age as b
on a.user_id=b.user_id;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 1 | 小赵 | 21 |
| 2 | 小钱 | NULL |
+---------+--------+------+
3、RIGHT JOIN（右连接）
mysql> select b.user_id, name, age
from table_name as a right join table_age as b
on a.user_id=b.user_id;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 1 | 小赵 | 21 |
| 3 | NULL | 23 |
+---------+--------+------+
4、UNION（全连接）
mysql> select a.user_id, name, age
from table_name as a left join table_age as b
on a.user_id =b.user_id
union
select b.user_id, name, age
from table_name as a right join table_age as b
on a.user_id =b.user_id;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 1 | 小赵 | 21 |
| 2 | 小钱 | NULL |
| 3 | NULL | 23 |
+---------+--------+------+
5、LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）
mysql> select a.user_id, name, age
-> from table_name as a left join table_age as b
-> on a.user_id=b.user_id
-> where b.user_id is null;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 2 | 小钱 | NULL |
+---------+--------+------+
6.RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）
mysql> select b.user_id, name, age
-> from table_name as a right join table_age as b
-> on a.user_id=b.user_id
-> where a.user_id is null;
+---------+------+------+
| user_id | name | age |
+---------+------+------+
| 3 | NULL | 23 |
+---------+------+------+
7、OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）
mysql> select a.user_id, name, age
-> from table_name as a left join table_age as b
-> on a.user_id =b.user_id
-> where b.user_id is null
-> union
-> select b.user_id, name, age
-> from table_name as a right join table_age as b
-> on a.user_id =b.user_id
-> where a.user_id is null;
+---------+--------+------+
| user_id | name | age |
+---------+--------+------+
| 2 | 小钱 | NULL |
| 3 | NULL | 23 |
+---------+--------+------+
8、笛卡尔积
mysql> select * from table_name join table_age;
+----+---------+--------+----+---------+------+
| id | user_id | name | id | user_id | age |
+----+---------+--------+----+---------+------+
| 1 | 1 | 小赵 | 1 | 1 | 21 |
| 2 | 2 | 小钱 | 1 | 1 | 21 |
| 1 | 1 | 小赵 | 2 | 3 | 23 |
| 2 | 2 | 小钱 | 2 | 3 | 23 |
+----+---------+--------+----+---------+------+
总结
|
操作 |
关键字 |
解释 |
图示 |
|||
|
内连接 |
INNER JOIN |
A ∩ B A \cap B A∩B |
并集去交集 |
UNION WHERE |
A ∪ B − A ∩ B A \cup B - A \cap B A∪B−A∩B |
|
