Mysql外键约束详解(一对多)教程

外键是相对于主键说的,是建立表之间的联系的必须的前提。MySQL会自动为所有表的主键进行索引,但是外键字段必须由用户进行明确的索引。

 

外键约束 + 索引 可以实现一对一、一对多、多对多表的关系。非常的重要!下面就来详细的给大家分享一下:

 

一、什么是Mysql外键?

当我们在数据库中创建表的时候,有可能某些列中值内容量很大,而且重复。

 

例如:创建一个学生表,按学校年纪班级分,表的内容大致如下:

id name caption
1 xxx x学校x年级x班级
2 ooo x学校x年级x班级
3 zzz z学校x年级x班级
4 ddd y学校x年级x班级

我们看出来对应的caption对应的值很长,而且重复量很大,这样就很不合适。

 

因此我们考虑将复杂重复的部分单独拿出来分成2个表:

第一张表:

id caption
1 x学校x年级x班级
2 y学校x年级x班级
3 z学校x年级x班级

 

第二张表:

第二张表在之前的基础上修改的。

id name partment
1 xxx 1
2 ooo 2
3 zzz 3
4 ddd 1

 

这样看起来就很简洁了,我们将很长的且重复的部分拿出来,然后规定编号,创建学生表的时候,学生对应的partment只需要取学校对应的id便可,这样同时这2张表也就会关联起来。

 

说明:

1、他们的关联关系;表2中的partment和表1的id联系再一起。

2、表1的数据会对应表2中的多条数据,这就叫一对多。

一对一:例如个人的邮箱,每个用户的邮箱都是不同的,即用户与邮箱之间的关系为一对一关系。

一对多:一个父亲可以有多个亲生孩子,一个孩子只能有一个生父。

多对多:一个老师可以教多个学生,一个学生可以有多个老师教。

 

问题:此时,两张表是相对独立的,都可以各自插入自己的数据,这样做很合适的,并没有实质行的关联起来。

因此,必须要将其限制。表2的partment数据必须是表1中有的,不然,就不让其增加。

这里(partment)就引入了一个名词:外键

 

说明:

1、外键:一个表接收另一个表的主键。

2、partment外键的是表1中的id。

3、当我们创建了外键,则系统默认会为我们添加,相应的约束,如:表2中的partment数据必须是表1中id有的;表2和表1就关联起来了。

具体可参考:Mysql外键约束Cascade、NO ACTION、Restrict、SET NULL的区别

 

二、mysql外键约束怎么写?mysql添加外键约束

主要是简单的讲一下mysql外键怎么设置?以及mysql创建外键与mysql删除外键方法。

 

第一种方法:用 Navicat 添加外键约束

Navicat是一套快速、可靠并价格相当便宜的数据库管理工具,专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。我个人常用的是Navicat for MySQ产品。

 

1、创建part表:创建part表

 

2、创建person表:创建person表

 

3、创建外键

注意:创建外键时,互相对应的表中的数据类型必须一样

 

第一步:首先在要创建外键的表上右键,选设计表。

第二步:进入设计表,在右边显示表行信息,然后点击外键:外键

 

第三步:选择“字段”,会出现一个选择框,选择你要设置外键的列;

外键

 

第四步:选择“参考表”,选择要外键要关联的表;

说明:

参考数据库:因为两个数据库在同一个目录下,所以这里可以不用选择;默认是同目录下的。

参考表

 

第五步:选择“参考字段”;选择参考表中要设为外键的列;

注意:添加外键,各个表中关联的列的数据类型必须一样,不然不能成功。

参考字段

 

第六步:“删除时”与“更新时”,这里我就不详细说了,主要是外键约束的一个规定吧!

Mysql外键约束Cascade、NO ACTION、Restrict、SET NULL

具体请看:Mysql外键约束Cascade、NO ACTION、Restrict、SET NULL的区别

 

第七步:保存(CTRL + S)。

 

第二种方法:mysql 添加/删除 外键约束语句,mysql 添加/删除 外键SQL命令

 

1、创建part:

create table part(
	nid int not null auto_increment primary key,
	caption varchar(32) not null
)

 

2、创建person:

create table person(
  nid int not null auto_increment,
  name varchar(32) default null,
  email varchar(32) default null,
  part_nid int,
  primary key(nid),
  constraint fk_person_part foreign key (part_nid) references part(nid)
)

在 CREATE TABLE 语句中,通过 FOREIGN KEY 关键字来指定外键,具体的语法格式如下:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…]

REFERENCES <主表名> 主键列1 [,主键列2,…]

 

创建完之后两张表外键对应的关系:

外键对应的关系

 

分析:

1、从名字可以看出SQL语句对应的是什么位置的。

2、图中名(C对应代码中的CONSTRAINT)这行可以不用设,系统会默认帮我设置,但是最好设置,如果要删除外键的时候,就可以通过这个名字进行相应的操作。

 

mysql删除外键SQL语句

alter table person1 drop foreign key fk_person1_part1;

删除外键约束的语法格式如下所示:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

 

mysql添加外键SQL语句

alter table person1 add constraint fk_p1_t1 foreign key (part1_nid) references part1(nid);

在修改数据表时添加外键约束的语法格式如下:

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

 

三、mysql外键的作用

1、让数据库自己通过外键来保证数据的完整性和一致性。

2、能够增加ER图的可读性。

E-R图也称实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型。它是描述现实世界关系概念模型的有效方法。是表示概念关系模型的一种方式。

 

外键已经创建好了,现在具体来实际操作一下,看看外键约束究竟有什么作用?还是以上面两张表为例子:

 

此时part表中没有数据,如果此时你在person表中添加数据,结果如下:外键约束的作用

 

在part中添加数据,结果:外键约束的作用

此时再在,person中添加数据:

外键约束的作用

注意:

这里不能输入part中nid没有的值,不然也会报错如下:

外键约束报错

 

四、 mysql外键约束 实际应用

part表:

part表

 

person表:person表

 

需求:要找出person表中属于x学校的人?

 

第一种方法:用之前学的办法:

1、先去part中获取x对应的nid

2、然后再通过这个nid与parson中part_id对用的关系,查找出对应的name

select name from personwhere part_idin (select nid form partwhere caption ="x")

 

第二种方法:链表方法 left join

select * from personleft join parton person.part_id = part.nid

结果显示:

链表方法 left join

 

分析:

left join:相当于将part表匹配的部分直接移动到person的列后面,组合起来显示。

既然内容都合并了,那么此时我们再加上判断,就可以将要的数据获取了。

SQL语句:

select * from personleft join parton person.part_id = part.nidwhere part.caption ="x"

结果显示:

select *from personleft join parton person.part_id = part.nidwhere part.caption ="x"

 

说明:

之前我们用的 * 获取的是全部的信息,如果我们获取指定的信息,可以将其修改。

例如:只获取person的name语句:

select person.name from personleft join parton person.part_id = part.nidwhere part.caption ="x"

结果:

select person.name from personleft join parton person.part_id = part.nidwhere part.caption ="x"

 

注意:join连接的条件,使用 on 进行对接的,条件写在on后面。

Aleft join Bon a.xx = b.xx

 

left join的特点:

1、以A为主

2、将A中的所有数据罗列

3、B则只显示与A相对应的数据

 

问题:验证我们说的谁在前就谁为主,谁的数据就全部显示,我们将person和part换个位置?

select * from partleft join personon person.part_id = part.nid;

结果:

select * from partleft join personon person.part_id = part.nid;

 

right join

在谁显示所有数据的上来看,他和left join刚好相反,以后面的表为主,显示其所有的数据。

 

inner join

会将没有建立关系的数据忽略掉。不管谁在前,结果都是一样。

select * from personinner join parton person.part_id = part.nid;

结果:select * from personinner join parton person.part_id = part.nid;

 

总结:

1、这几个join可以写多个的,意思就是一个表可以同时有多个外键。

2、当选择的列名,是所有表中唯一的话,可以不用写前缀的表名。如:person.part_id就可以直接写成part_id。

3、上面的part表,有个别名叫,字典表。

    A+
发布日期:2021年04月16日 19:02:31  所属分类:MySQL
最后更新时间:2021-04-16 19:15:38
付杰
  • ¥ 29.99元
  • 市场价:888元
  • ¥ 69.0元
  • 市场价:69.0元
  • ¥ 1999.9元
  • 市场价:8999元
  • ¥ 99.0元
  • 市场价:99.0元

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: