数据库操作2 - join从句的使用

1. 内连接（inner join）：将两张表公共的部分连接起来（取两张表的交集：A∩B）

-- select <select_list> from table_a a inner join table_b b on a.key = b.key;

select u1.user_name, u1.role role1, u2.role role2 from user1 u1 inner join user2 u2 on u1.user_name = u2.user_name;

-- 女帝 路飞の爱妻 王下七武海

1 inner join.png

2. 全外连接（full outer）

1. A+B

-- select <select_list> from table_a a full outer join table_b b on a.key = b.key;（A+B）

-- 由于 MySql 中不支持全连接，所以使用 union all 来解决

select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name
union all
select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name;

-- 女帝 路飞の爱妻 王下七武海
-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null
-- 女帝 路飞の爱妻 王下七武海
-- 红发 null  四皇成员一
-- 帝奇 null  四皇成员一
-- 百兽 null  四皇成员一
-- 玲玲 null  四皇成员一

2 full join 1.png

2. (A+B)-(A∩B)

-- select <select_list> from table_a a full outer join table_b b on a.key = b.key where a.key is null or b.key is null;（(A+B)-(A∩B)）

-- 同理，也使用 union all 来解决

select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name where u2.role is null
union all
select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name where u1.role is null;

-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null
-- 红发 null  四皇成员一
-- 帝奇 null  四皇成员一
-- 百兽 null  四皇成员一
-- 玲玲 null  四皇成员一

2 full join 2.png

3. 左外连接（left outer）

1. 查询出来a表全部记录：A

-- select <select_list> from table_a a left join table_b b on a.key = b.key;（查询出来a表全部记录：A）

select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name;

-- 女帝 路飞の爱妻 王下七武海
-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null

3 left join 1.png

2. 只存在于a表，不存在于b表中的数据：A-B

--select <select_list> from table_a a left join table_b b on a.key = b.key where b.key is null;（只存在于a表，不存在于b表中的数据：A-B）

select u1.user_name, u1.role role1, u2.role role2 from user1 u1 left join user2 u2 on u1.user_name = u2.user_name where u2.role is null;

-- 路飞 草帽の船长 null
-- 娜美 草帽航海士 null
-- 乔巴 草帽の船医 null

3 left join 2.png

4. 右外连接（right outer）

1. 查询出来b表全部记录：B

-- select <select_list> from table_a a right join table_b b on a.key = b.key;（查询出来a表全部记录：B）

select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name;

-- 女帝 路飞の爱妻 王下七武海
-- 红发 null  四皇成员一
-- 帝奇 null  四皇成员一
-- 百兽 null  四皇成员一
-- 玲玲 null  四皇成员一

4 right join 1.png

2. 只存在于b表，不存在于b表中的数据：B-A

-- select <select_list> from table_a a right join table_b b on a.key = b.key where a.key is null;（只存在于a表，不存在于b表中的数据：B-A）

select u2.user_name, u1.role role1, u2.role role2 from user1 u1 right join user2 u2 on u1.user_name = u2.user_name where u1.role is null;

-- 红发 null  四皇成员一
-- 帝奇 null  四皇成员一
-- 百兽 null  四皇成员一
-- 玲玲 null  四皇成员一

4 right join 2.png

5. 交叉连接（cross），笛卡尔连接（A*B）

select u1.user_name, u1.role, u2.user_name, u2.role from user1 u1 cross join user2 u2; 

-- 结果是 4*5=20 条数据

5 cross join.png

6. join的使用技巧：

假设将 两张表中同时存在的人物 的 role 属性，在 表1 中 改为 女儿岛国王，该如何操作？

update user1 set role = "女儿岛国王" where user1.user_name in 
(select u2.user_name from user1 u1 inner join user2 u2 on u1.user_name = u2.user_name);

-- 很容易想到上面这个语句，但是 MySql 不支持这个语句（1093 错误：不能更新在 from 从句中出现的表）
-- 这时，可以使用下面的方法进行更新

update user1 u1 join 
(select u2.user_name from user1 u1 inner join user2 u2 on u1.user_name = u2.user_name) tmp 
on u1.user_name = tmp.user_name set u1.role = "女儿岛国王";

select * from user1;

-- 1    路飞  草帽の船长
-- 2    娜美  草帽航海士
-- 3    女帝  女儿岛国王
-- 4    乔巴  草帽の船医

6 join 从句使用技巧1.png