MySQL 表关系、外键、多表查询、子查询

欢迎大家来到IT世界,在知识的湖畔探索吧!

1.1 表与表之间的关系

1.1.1 一对一

所谓一对一的关系就是，有两张表，这两张表的记录是一对一的关系，比如有两张表，一张表为个人信息表,有如下字段：身份证、姓名、年龄，一张为职位表，有如下字段：员工编号、职位，上面两张表的记录都是一对一的，一个人在职位表中，只有一条对应的记录。但是一对一的关系，没有什么价值，因为完全可以将两张表的数据合成一张表。

1.1.2 一对多

所谓一对多关系就是，有两张表，其中一张表中的记录对应另一张表的多条记录，比如在电商业务中，有两张表，一张表存储客户信息，另一张表存储订单信息，一个人可以有多个不同的订单，这就是一对多的关系。

1.1.3 多对多

多对多相比较于一对多而言，要复杂一些，我们还是以电商业务举例，有两张表，一张表为商品表，用来存放商品信息，另一张为订单表，用来存放订单信息，那我们在网上购物都知道，一个订单里可以有多个商品，而多个商品呢，又可以出现在不同的订单里，这样就形成了多对多的关系。

2.1 外键约束

2.1.1 什么是外键？

我们在之前的文章中，介绍过主键约束、唯一约束等等，但是没有说过外键约束，这是因为当时都是单张表，而外键约束呢，是需要两张表关联的，它描述的是两张表一对多的关系，而这两张表是有主从关系的。

我们还是以电商业务为例，有两张表，一张表为商品分类表，而另一张表是商品表，那么对于一个商品分类来说，它可以有多个商品，但是一个商品只能属于一个分类。

而外键就是描述这两张表之间的约束关系，比如商品的某个分类都不存在了，那么下面的商品也不会存在，所以这里的商品分类表就是主表，而商品信息表就是从表,那么我们这里可以将外键简单的概述为：主表对从表的限制和约束。

那么主表如何对从表进行约束呢，这主要是通过外键列,在从表中存在着一个列(外键列)，它对着主表中某一列(主键列)，从表外键列中的值，必须要受到主表的主键列的限制。

2.1.2 如何创建外键？

在MySQL中，创建外键的方式有两种，第一种是我们在创建表的时候，就将外键约束创建好，还有一种是两张表已经创建完成，此时可以在两张表原有的基础上增加外键约束。

下面我们先看一种创建外键的方式，我们首先创建一张主表,建表语句如下：

CREATE TABLE classify( c_id INT PRIMARY KEY, c_name CHAR(255) NOT NULL ); 

欢迎大家来到IT世界,在知识的湖畔探索吧!

之后我们再创建从表，并为从表添加主表的外键：

欢迎大家来到IT世界,在知识的湖畔探索吧!CREATE TABLE goods( g_id INT PRIMARY KEY, g_name CHAR(255) NOT NULL, classify_id INT, constraint fk_cid foreign key (classify_id) references classify(c_id) on delete cascade on update cascade ); 

这里我们添加了一个约束，其中从表的classify_id为外键列，对应着主表中的主键列(c_id),外键列的值受到主键列的约束，下面我们拆解一下建表语句中创建外键的核心语句：

constraint fk_cid foreign key (classify_id) references classify(c_id) on delete cascade on update cascade

• constraint:表示要创建一个约束
• fk_cid: 我们要创建外键的名称
• foreign key :则表示我们要创建一个外键
• classify_id: 表示为从表的那一列创建外键约束
• renferences classify(c_id):表示要关联classify 表的c_id列作为外键值得约束
• on delete(update) cascade：数据库外键定义的一个可选项，表示键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的,其中cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除。

下面我再看第二种创建外键的方式，即为已有的两张表创建外键,我们首先看下语法：

欢迎大家来到IT世界,在知识的湖畔探索吧!alter table 从表表名 add constraint 外键名称 foreign key (从表的外键列名) references 主表名(主表的主键列名);

示例代码如下：

alter table goods1 add constraint fk_classify_id foreign key (classify_id) references classify1(c_id);

2.1.3 外键表相关操作

插入数据: 我们首先向我们之前创建的两张表添加数据,SQL如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!-- 分类表，主表 INSERT INTO classify VALUES ( 1, '食品' ), ( 2, '衣服' ), ( 3, '家具' ), ( 4, '电器' ); -- 商品表，从表 INSERT INTO goods VALUES ( 1, '牛奶',1 ), ( 2, '羽绒服',2 ), ( 3, '毛衣',2 ), ( 4, '电风扇',4 ), ( 5, '空调',4 ), ( 6, '床',3 ), ( 7, '沙发',3 ), ( 8, '橙子',1 ), ( 9, '面包',1 );

由于存在着外键的约束，所以外键的值一定是要在主表的主键列中存在的，执行结果如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!

删除数据： 当存在外键约束的时候，删除数据有如下注意事项，当从表中有主表中相关联的数据时候，此时删除主表的数据是出现两种情况：

1. 如果外键有级联关系的话，那么你删除主表数据的时候，会把从表的相关数据都会删除的
2. 如果外键无级联关系，那么删除主表数据时候，是会报错，此时如果想删除主表的数据，需要先删除从表与其相关联的数据才可以。但是从表的数据是可以随便删除的。

3.1 多表查询

我们在前面的文章中，都是在单表中查询，现在我们来看下多表的查询，多表查询有如下几种连接关系。

3.1.1 交叉连接查询

交叉连接查询，很简单，就是将两张表的数据相乘，会得到一个笛卡尔的数据集合，举个例子，A表有5条数据，B表有10条数据，如果使用交叉连接查询，你会得到50条数据，语法如下：

select * from A表,B表;

示例代码：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * from classify,goods;

结果如下：

交叉连接查询，我们一般不会使用，因为会产生很多冗余、不正确的数据，比如我们结果中的第一条数据：

这羽绒服哪里是电器啊，这明显就不对嘛，所以交叉连接查询一般不会使用。

3.1.2 内连接查询

我们发现上面的数据有冗余以及不正确的数据，此时我们可以通过添加where条件来把不正确的数据过滤掉，这个我们就称之为内连接查询，示例代码如下：

SELECT * from classify,goods where c_id=classify_id;

执行结果如下：

这种内连接，我们称之为隐式内连接，还有一种显式内连接，语法如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!select * from 表1 inner join 表2 on 过滤条件；

示例代码如下：

select * from classify inner join goods on c_id=classify_id;

结果如下：

最后我们再总结一下，内连接，求得就是两张表的交集。

外连接查询

外连接查询，主要分为两种：左外连接(left join)、右外连接(right join) ,外连接的结果是以一张表为准，他把那张表的数据全部输出，而另一张的数据，如果有则显示，如果没有则补NULL。下面我们来看一个示例代码：

欢迎大家来到IT世界,在知识的湖畔探索吧!select * from classify left outer join goods on c_id = classify_id ORDER BY c_id DESC;

显示结果如下：

此时我们把left 改成right，那么它就会以右边的goods表为准了，此时D代码如下：

select * from classify right outer join goods on c_id = classify_id ORDER BY c_id DESC;

结果如下：

4.1 子查询

所谓子查询，就是在select语句中又嵌套一个select查询，我们首先看一个例子，感受一下，我们有两张表，一张表示商品分类表、一张表示商品表，比如我们想要查询商品表中分类属于电器的商品有哪些，示例代码如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT * FROM goods WHERE classify_id = ( SELECT c_id FROM classify WHERE c_name = '电器' );

结果如下：