【MySQL】014

【MySQL】014

0、警醒自己

1、学习不用心，骗人又骗己；

2、学习不刻苦，纸上画老虎；

、学习不惜时，终得人耻笑；

4、学习不复习，不如不学习；

5、学习不休息，毁眼伤身体；

7、狗才等着别人喂，狼都是自己寻食物；

一、SQL标准中Join的类型

1、含义解释

①内连接仅选出两张表中相互匹配的记录，外链接会选出其他不匹配的记录，最常用的是内连接；

②左外连接：包含所有左表中的记录，甚至是右表中没有和它匹配的记录；

③右外连接：包含所有右表中的记录，甚至是左表中没有和它匹配的记录；

二、Inner Join（内连接）

1、概述

仅选出两张表中相互匹配的记录；

（join和inner join没有区别）

基于链接谓词将两张表（如A和B）的列组合在一起，产生新的结果表；

（红代表的是A和B共同拥有的字段，比如id、username等等，相当于将两张表组成一张表，再根据条件进行查询）

2、语法格式

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A inner join TableB B on A.Key = B.Key

、使用演示

相关表：

相关表的SQL语句：

代码语言：javascript代码运行次数：0运行复制

-- 创建user1表

CREATE TABLE `user1` (

  `id` int(11) OT ULL COMMET '主键',

  `user_name` varchar(255) DEFAULT ULL COMMET '姓名',

  `over` varchar(255) DEFAULT ULL COMMET '结局',

  PRIMARY KEY (`id`)

) EGIE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 插入数据到user1表

ISERT ITO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (1, '唐僧', '旃檀功德佛');

ISERT ITO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (2, '猪八戒', '净坛使者');

ISERT ITO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (, '孙悟空', '斗战胜佛');

ISERT ITO `zibo`.`user1`(`id`, `user_name`, `over`) VALUES (4, '沙僧', '金身罗汉');


-- 创建user2表

CREATE TABLE `user2` (

  `id` int(11) OT ULL COMMET '主键',

  `user_name` varchar(255) DEFAULT ULL COMMET '姓名',

  `over` varchar(255) DEFAULT ULL COMMET '结局',

  PRIMARY KEY (`id`)

) EGIE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 插入数据到user2表

ISERT ITO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (1, '孙悟空', '成佛');

ISERT ITO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (2, '牛魔王', '被降服');

ISERT ITO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (, '蛟魔王', '被降服');

ISERT ITO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (4, '鹏魔王', '被降服');

ISERT ITO `zibo`.`user2`(`id`, `user_name`, `over`) VALUES (5, '狮驼王', '被降服');

查询需求：

同时是取经四人表与孙悟空和他的结拜兄弟表中的人的最终结局（同时在A、B两表中的实体的数据）；

（两个表：A表和B表，A表中有孙悟空，B表中也有孙悟空，我们将A和B表连接组合，肯定是满足某一条件的数据进行整合，否则杂乱的数据无法排列，这个条件可以是名字相同，也可以是年龄相同等等）

SQL语句：

代码语言：javascript代码运行次数：0运行复制

select a.user_name,, from user1 a inner join user2 b on a.user_name = b.user_name;

执行结果：

三、Left Outer Join（左外连接）

1、概述

包含所有左表中的记录，甚至是右表中没有和它匹配的记录；

2、语法格式

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A left join TableB B on A.Key = B.Key

、补充

图示：

语法格式：

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A left join TableB B on A.Key = B.Key where B.key is null

4、演示

SQL语句：

代码语言：javascript代码运行次数：0运行复制

select a.user_name,, from user1 a left join user2 b on a.user_name = b.user_name

执行结果：

另一句SQL语句：

代码语言：javascript代码运行次数：0运行复制

select a.user_name,, from user1 a left join user2 b on a.user_name = b.user_name WHERE b.user_name is null;

执行结果：

还有一句SQL语句：

代码语言：javascript代码运行次数：0运行复制

select a.user_name,, from user1 a left join user2 b on a.user_name = b.user_name WHERE b.user_name is not null;

执行结果：

四、RightOuter Join（右外连接）

1、概述

包含所有右表中的记录，甚至是左表中没有和它匹配的记录；

2、语法格式

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A right join TableB B on A.Key = B.Key;

、演示

（左连接和有连接正好是相反的，所以非常简单，咱们不上图了，直接演示；）

SQL语句：

代码语言：javascript代码运行次数：0运行复制

select b.user_name,, from user1 a right join user2 b on a.user_name = b.user_name;

执行结果：

五、Full Join（全连接）

1、概述

全连接也就是左连接和右连接的合集；

(第一种情况)

(第二种情况)

2、语法格式

第一种情况：

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A full join TableB B on A.Key = B.Key;

第二种情况：

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A full join TableB B on A.Key = B.Key where a.key is null or b.key is null;

、演示问题

问题：

代码语言：javascript代码运行次数：0运行复制

select * from user1 a full join user2 b on a.user_name = b.user_name;

答案：

MySQL不支持全连接！

4、解决问题

原理：

全连接 = 左连接 + 有连接；

语法格式：

代码语言：javascript代码运行次数：0运行复制

select <select_list> from TableA A left join TableB B on A.Key = B.Key union all select <select_list> from TableA A right join TableB B on A.Key = B.Key;

SQL语句演示：

代码语言：javascript代码运行次数：0运行复制

select a.user_name,, from user1 a left join user2 b on a.user_name = b.user_name union all select b.user_name,, from user1 a right join user2 b on a.user_name = b.user_name;

执行结果：

另一句SQL语句：

代码语言：javascript代码运行次数：0运行复制

select a.user_name,, from user1 a left join user2 b on a.user_name = b.user_name union all select b.user_name,, from user1 a right join user2 b on a.user_name = b.user_name where a.user_name is null or b.user_name is null;

执行结果：

六、Cross Join（交叉连接或笛卡尔连接）

1、概述

交叉连接，又称笛卡尔连接，或叉乘，如果A和B是两个集合，他们的交叉连接就是A x B；

集合A中的每一条数据与集合B中的每一条数据进行匹配产生的结果集；

2、语法格式

代码语言：javascript代码运行次数：0运行复制

select * from TableA cross join TablkB;

、演示

SQL语句：

代码语言：javascript代码运行次数：0运行复制

select * from user1 cross join user2;

执行结果：

七、使用Join更新表

1、需求描述

将同时存在于user1和user2中的人的user1中的over字段更改为“齐天大圣”；

2、SQL语句实现（存在问题）

代码语言：javascript代码运行次数：0运行复制

UPDATE user1 
SET over = '齐天大圣' 
WHERE
	user1.user_name I ( SELECT b.user_name FROM user1 a IER JOI user2 b O a.user_name = b.user_name );

、执行结果

4、解决方案：

（join和inner join没有区别）

代码语言：javascript代码运行次数：0运行复制

UPDATE user1 a
JOI ( SELECT b.user_name FROM user1 a IER JOI user2 b O a.user_name = b.user_name ) b O a.user_name = b.user_name 
SET  = '齐天大圣';

5、执行结果：

八、使用Join优化子查询

1、优化前

SQL语句：

代码语言：javascript代码运行次数：0运行复制

SELECT
	a.user_name,
	,
	( SELECT  FROM user2 b WHERE a.user_name = b.user_name ) AS over2 
FROM
	user1 a;

执行结果：

2、优化后

SQL语句：

代码语言：javascript代码运行次数：0运行复制

SELECT
	a.user_name,
	,
	 AS over2 
FROM
	user1 a
	LEFT JOI user2 b O a.user_name = b.user_name;

执行结果（据说效率更高了）：

九、使用Join优化聚合子查询

1、打怪表相关SQL

代码语言：javascript代码运行次数：0运行复制

-- 创建打怪表
create table if not exists user_kills (
  id smallint unsigned primary key auto_increment,
  user_id smallint unsigned,
  timestr timestamp default CURRET_TIMESTAMP,
  kills smallint unsigned
);

-- 插入 user_kills 表数据
insert into user_kills(user_id, timestr, kills) values (2, timestamp('201-01-10'), 10);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('201-02-01'), 2);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('201-02-05'), 12);
insert into user_kills(user_id, timestr, kills) values (4, timestamp('201-01-10'), );
insert into user_kills(user_id, timestr, kills) values (4, timestamp('201-02-11'), 5);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('201-02-06'), 1);
insert into user_kills(user_id, timestr, kills) values (, timestamp('201-01-11'), 20);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('201-02-12'), 10);
insert into user_kills(user_id, timestr, kills) values (, timestamp('201-02-07'), 17);

2、需求描述

查询出取经四人组中打怪最多的日期；

、演示

SQL语句：

代码语言：javascript代码运行次数：0运行复制

SELECT
	a.user_name,
	,
	b.kills 
FROM
	user1 a
	JOI user_kills b O a.id = b.user_id 
WHERE
	b.kills =(
	SELECT
		MAX( c.kills ) 
	FROM
		user_kills c 
	WHERE
	c.user_id = b.user_id 
	);

执行结果：

优化后的SQL语句：

代码语言：javascript代码运行次数：0运行复制

SELECT
	a.user_name,
	,
	b.kills 
FROM
	user1 a
	JOI user_kills b O a.id = b.user_id
	JOI user_kills c O c.user_id = b.user_id 
GROUP BY
	a.user_name,
	,
	b.kills 
HAVIG
	b.kills = max( c.kills );

执行结果：

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2025-01-06，如有侵权请联系 cloudcommunity@tencent 删除前往查看mysql连接数据语法join