WHCSRL 技术网

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

关注【郑大钱呀】【公】【众】【号】,我们一起交流,一起学习。

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
);
  • 1
  • 2
  • 3
  • 4

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

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 
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

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

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

alter table 从表表名 add constraint 外键名称 foreign key (从表的外键列名) references 主表名(主表的主键列名);
  • 1

示例代码如下:

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

2.1.3 外键表相关操作

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

-- 分类表,主表
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 );
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

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

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

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

3.1 多表查询

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

3.1.1 交叉连接查询

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

select * from A表,B表;
  • 1

示例代码:

SELECT * from classify,goods;
  • 1

结果如下:

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

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

3.1.2 内连接查询

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

SELECT * from classify,goods where c_id=classify_id;
  • 1

执行结果如下:

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

select * from 表1 inner join 表2 on 过滤条件;
  • 1

示例代码如下:

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

结果如下:

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

3.1.3外连接查询

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

select * from classify left outer join goods on c_id = classify_id ORDER BY c_id DESC;
  • 1

显示结果如下:

此时我们把left 改成right,那么他就会以右边的goods表为准了,此时D代码入下:

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

结果如下:

4.1 子查询

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

SELECT
	* 
FROM
	goods 
WHERE
	classify_id = ( SELECT c_id FROM classify WHERE c_name = '电器' );
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

结果如下:

5.1 结尾

本期的内容就到这里了,如有不足之处还请大家多多指正,欢迎大家留言、关注、转发、收藏,谢谢。

推荐阅读