MySQL中多表查询、表连接（内连接和外连接）

文章目录

表与表的关系

一对一关系

一对多关系

多对多关系

表与表之间的连接

笛卡尔积

什么是笛卡尔积：

内连接：

1、通过where关键字进行关联

2、通过inner join on进行关联

外连接：

1.左连接（left join）

2.右连接right join

子查询

---

有时候我们所需要的数据不止在一张表中，需要多个表做结合的查询，这时就需要进行表连接。

表与表的关系

多表连接主要需要决定关系：方向性、主附关系

一对一关系

在一对一关系中，A表中的一行最多只能匹配B表中的一行，反之亦然，创建的就是一对一关系。

一对多关系

一对多关系是最常见的一种关系，A表中的一行可以匹配B表中的多行，但是B表中的一行只能匹配A表中的一行。

例如：存在部门表和人员表之间具有一对多的关系，每个部门有很多的员工，但是每个员工只属于一个部门

只有当一个相关列是一个主键或者具有唯一性约束时，才能创建一对多的关系。

多对多关系

在多对多的关系中，A表中一行可以匹配B表中的多行，反之亦然。

要创建这种关系，需要定义第三张表，称之为结合表，他的主键由A表和B表外部键组成

 

表与表之间的连接

笛卡尔积

什么是笛卡尔积：

数学上，有两个集合A={a,b},B={1,2,3},则两个集合的笛卡尔积={{a,1}, {a,2}, {a,3}, {b,1}, {b,2}, {b,3}} 列出所有情况,一共是2*3=6条记录；

在数据库中，笛卡尔积是多表查询没有连接条件时返回的表结果。

笛卡尔积的元素是元组，关系A和关系B的笛卡尔积可以记为（AXB），如果A表a条，B表为b条，那么A和B的笛卡尔积为（a+b）列数，有（a*b）行的元素集合。检索出来的条目是将第一个表中的行数乘以第二个表中的行数。

避免全笛卡尔积 ：在 where 加入有效的连接条件；

消除笛卡尔积：使用等值连接和非等值连接；

内连接：

内连接称之为普通连接或自然连接，按照连接条件，返回两张表中的满足条件的记录。

内连接查询的特征：只关联表与表中能够匹配到的数据信息，才能有对应的查询结果，如图所示：

 内连接提供了两种表与表之间的连接方式：

1、通过where关键字进行关联

格式一：
select 表1.列名1，表2.列名1 
from 表名1，表名2 
where 表1.列名1=表2.列名1;

注意：

• 表与表之间建立起关联的列，列名可以不一样，但是这两个列的数据类型和内容必须保持一致
• 要查询的字段，必须要在字段的前面加上表名或者表的别名

2、通过inner join on进行关联

inner是可以省略的，可写可不写。

格式二：
select 表1.列名1，表2.列名1 
from 表名1 [inner] join 表名2 
on 表1.列名1=表2.列名1;
 

一般用inner join  on 关键字进行的内连接，又可分为等值连接，非等值连接。

等值连接：条件中只包含“=”时的连接，称之为等值连接。

非等值连接：连接条件中除了等号外，还用了其它的比较运算符（>,<,...），来比较连接列的列值。

外连接：

外连接特征：至少会返回出一个表的所有内容。

外连接又分为左连接和右连接。

在from的后面，先写的表为左表、后写的表为右表。

1.左连接（left join）

左连接是对左表不加限制，以左表中的数据为基准；使用left outer join 关键字对多个表进行连接，outer可省略不写，左连接的查询结果中包含左表的所有行和右表中的匹配行。若是在右表中找不到匹配行，则使用NULL代替显示。

格式：
select 表名.列名
from 表名1     
left [outer] join 表名2
on <连接条件>；

表1 是左表，连接条件可以是表1.列名1=表2.列名

2.右连接right join

右连接是对右表不加限制，以右表的数据为基准；使用right outer join 关键字对多个表进行连接，右连接返回的是右表的所有行和左表的匹配行，若在左表找不到匹配行，则返回NULL代替显示。

格式：
select 表名.列名
from 表名1     
right [outer] join 表名2
on 表1.列=表2.列；

子查询

子查询：指的是在查询语句里面嵌套查询语句，子查询语句放在where条件中，子查询的select语句总是使用圆括号括起来。

格式：
嵌套一个子查询语句：
select 列名 from 表名 where 列名 in（
  select 列名 from 表名 where 条件）；
  
嵌套两个子查询语句：
select 列名 from 表名 where 列名 in（
  select 列名 from 表名 where 列名 in（
    select 列名 from 表名 where 条件））；