MySQL--多表连接(内连接、外连接、交叉连接--笛卡尔积)

桃扇骨 2023-06-02 07:54 15阅读 0赞

@toc  
     **更多关于数据库知识请加关注哟~~。若需联系博主请私信或者加博主联系方式:**  
     **QQ:3327908431**  
     **微信：ZDSL1542334210**

前言：MySQL一系列操作猛如虎，代码易懂清晰，结构也简单，今天我们来谈一谈MySQL强大的多表连接，将表连接后，不但方便查询、处理，而且更加便于做数据分析，为我们省去了诸多麻烦，废话不多说，直接进入今天的讲解画面。\*\*

### 1、多表连接种类与语法 ###

#### 1.1、纵向拼接 ####

Union 拼接也称为纵向拼接，根据每个表共有的列的值匹配两个表中的行，要进行连接的两张表必须有相同的列名才可以，若两表字段不全相同或者顺序不同，那么就用查询来代替。语法为:

select * from t1 union select * from t2;  # 两表列名和列顺序完全相同
    
    select 列名称 from t1 union select 列名称 from t2;

#### 1.2、外联接 ####

该种连接方式是最常用也是比较难理解的一种连接方式。外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：  
        1）left join或left outer join左向外联接的结果集包括left outer子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值，但是需要指定主键id，该两个表格的主键名字要一样，语法为:

select * from t1 left join t2 on t1.id=t2.id;

2）right join或 right outer join右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

select * from t1 right join t2 on t1.id=t2.id;

#### 1.3、内联接 ####

inner join只显示两表中有关联的数据，就是当两个表没有相同主键时，不会显示结果。如下面的例子，当scores 里面的s\_id不全的时候，那么内连接就不会显示scores 没有的s\_id相应的行。

select * from stu inner join scores on stu.s_id = scores.s_id;

#### 1.4、交叉联接(笛卡尔积) ####

交叉联接中 join=full join 都是返回左表中的所有行，左表中的每一行与右表中的所有行组合，不需要指定主键。交叉联接也称作笛卡尔积。from 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。

select * from t1 join t2;  # 法一
    
    select * from t1 full join t2;# 法二

### 2、表格创建 ###

**创建学生信息表stu**

create table stu(s_id varchar(5),
       s_name varchar(5),
       s_sex varchar(1),
       s_age int(3),s_day date);
    
    insert into stu values
    ("001","李华","男",23,'1996-8-16'),
    ("002","王二","男",24,'1997-3-16'),
    ("003","赵敏","女",23,'1990-5-26'),
    ("004","张莹莹","女",22,'1995-2-16'),
    ("005","朱亚军","男",25,'1999-8-16'),
    ("006","马云","男",28,'1993-12-16');

**创建学生得分表scores**

create table scores (s_id varchar(5),
        c_id varchar(3),score float);
        
    insert into scores values 
    ("001","01",135),
    ("005","01",120),
    ("003","01",110),
    ("002","01",90),
    ("005","02",140),
    ("001","02",125.5),
    ("004","02",100),
    ("006","02",90),
    ("002","03",102),
    ("005","03",100.6),
    ("001","03",100),
    ("003","03",95.6),
    ("004","03",83),
    ("003","02",80),
    ("006","03",79.5);

**创建课程表coure**

create table coure(
    c_id varchar(2),t_id char(1),c_name varchar(10));
    
    insert into coure values 
    ("01","3","数据库原理"),
    ("02","2","统计学基础"),
    ("03","1","Python基础");

**创建教师信息表teacher**

create table teacher(
     t_id char(1),t_name varchar(5));
     
    insert into teacher values
      ("1","邓博"),
      ("2","崔博"),
      ("3","汪院长");

**创建t1表**

create table t1(s_id varchar(5),
       s_name varchar(5),
       s_sex varchar(1),
       s_age int(3),s_day date);
    
    insert into stu values
    ("007","走走走","男",105,'1996-8-16');

### 3、题目部分 ###

**题目一：将stu表和scores表左连接**

select * from stu left join scores on stu.s_id=scores.s_id;
    # 答案：
    001	李华	男	23	1996-8-16	001	01	135
    005	朱亚军	男	25	1999-8-16	005	01	120
    003	赵敏	女	23	1990-5-26	003	01	110
    002	王二	男	24	1997-3-16	002	01	90
    005	朱亚军	男	25	1999-8-16	005	02	140
    001	李华	男	23	1996-8-16	001	02	125.5
    004	张莹莹	女	22	1995-2-16	004	02	100
    006	马云	男	28	1993-12-16	006	02	90
    002	王二	男	24	1997-3-16	002	03	102
    005	朱亚军	男	25	1999-8-16	005	03	100.6
    001	李华	男	23	1996-8-16	001	03	100
    003	赵敏	女	23	1990-5-26	003	03	95.6
    004	张莹莹	女	22	1995-2-16	004	03	83
    003	赵敏	女	23	1990-5-26	003	02	80
    006	马云	男	28	1993-12-16	006	03	79.5

**题目二：将stu表和scores表和coure表做连接**  
分析：多表连接无非就是多了几张表，其具体语法为:

select * from t1 left join t2 on ti.id=t2.id 
    left join t3 on t2.id=t3.id left join t4 on t3.id=t4.id ... ;

这里要保证信息不丢失，且这里列名不同，采用左连接，根据数据的特点可知，有：

select * from stu left join scores on stu.s_id=scores.s_id 
         left join coure on scores.c_id=coure.c_id;
    # 答案：
    001	李华	男	23	1996-8-16	001	01	135	01	3	数据库原理
    005	朱亚军	男	25	1999-8-16	005	01	120	01	3	数据库原理
    003	赵敏	女	23	1990-5-26	003	01	110	01	3	数据库原理
    002	王二	男	24	1997-3-16	002	01	90	01	3	数据库原理
    005	朱亚军	男	25	1999-8-16	005	02	140	02	2	统计学基础
    001	李华	男	23	1996-8-16	001	02	125.5	02	2	统计学基础
    004	张莹莹	女	22	1995-2-16	004	02	100	02	2	统计学基础
    006	马云	男	28	1993-12-16	006	02	90	02	2	统计学基础
    003	赵敏	女	23	1990-5-26	003	02	80	02	2	统计学基础
    002	王二	男	24	1997-3-16	002	03	102	03	1	Python基础
    005	朱亚军	男	25	1999-8-16	005	03	100.6	03	1	Python基础
    001	李华	男	23	1996-8-16	001	03	100	03	1	Python基础
    003	赵敏	女	23	1990-5-26	003	03	95.6	03	1	Python基础
    004	张莹莹	女	22	1995-2-16	004	03	83	03	1	Python基础
    006	马云	男	28	1993-12-16	006	03	79.5	03	1	Python基础
    007	走走走	男	105	1996-8-16

**题目三：将stu、scores、coure以及teacher表做连接**

select * from stu left join scores on stu.s_id=scores.s_id 
        left join coure on scores.c_id=coure.c_id left join teacher on coure.t_id=teacher.t_id;

**题目四：将stu表和t1表纵向连接;**

select * from stu union  select * from t1;
    # 答案
    001	李华	男	23	1996-8-16
    002	王二	男	24	1997-3-16
    003	赵敏	女	23	1990-5-26
    004	张莹莹	女	22	1995-2-16
    005	朱亚军	男	25	1999-8-16
    006	马云	男	28	1993-12-16
    007	走走走	男	105	1996-8-16

**题目五：将teacher表和coure表纵向连接;**  
分析：由于两表只有教师id这一列相同，但是这样会出现重复结果，于是利用union all不去重复，所以要实现纵向连接就只有如此

select t_id from teacher union all  select t_id from coure;
    # 答案
    1
    2
    3
    3
    2
    1

### 4、文末彩蛋–轻松一刻 ###

**大家都知道嘛，男人到了17、18岁呢难免有点那啥，你比方说我好朋友小熊就出现过这事儿，以前高中时候嘛，我们班长就谈了一女朋友，而且他两还合租了，然后过几个周我们一起吃饭，由于好几周没见又瘦了，结果小熊调侃到：“你小子早晚得死在女人身上！”然后班长撇下嘴说到：“你早晚得死自己右手里！”，然后小熊默默低下头，看了看自己的右手，老茧确实比以前厚了许多…就是这么个情况嘛。**

**今天到这里就结束了哟//每篇文章都有文末彩蛋–轻松一刻哟~加关注学习更多MySQL知识！谢谢观看，我是Jetuser-data**

**链接: \[https://blog.csdn.net/L1542334210\]**  
**CSND:L1542334210**  
![在这里插入图片描述][20190928182613800.png]  
**祝大家工作顺利！阖家欢乐！**

[20190928182613800.png]: /images/20230601/21bf2222e9c14e44a7748bc289c110a9.png