mysql 外键 一对多_第二百八十节，MySQL数据库-外键链表之一对多，多对多

MySQL数据库-外键链表之一对多，多对多

外键链表之一对多

外键链表：就是a表通过外键连接b表的主键，建立链表关系，需要注意的是a表外键字段类型，必须与要关联的b表的主键字段类型一致，否则无法创建索引

一对多：就是b表的某一个字段值对应a表外键里的多个值，前提是a表要与b表链表

65fb61cd9a8815ad44e828e2d24142e6.png

用Navicat 软件可视化外键链表

1、a表里创建外键连接b表的主键，首先检查a表里要设置外键的字段、和b表里的主键字段数据类型是否一致，两者数据类型必须一致，不然无法建立索引【重点】

2、Navicat 软件可视化打开a表，设计表-外键里设置

1166d436059ef3a53e03e2a25aa28ce7.png

3、设置后a表和b表就建立了外键链表，可以在a表的外键字段看b表的主键字段，说明他们两者的链表关系建立

86cbcc474f7a7e92bc6ebc8b8a60ccd0.png

外键链表约束

a表和b表链表后，两表之间建立了链表关系，a表受b表约束，也就是当a表添加或者修改一条数据时、这条数据的外键字段值如果是b表主键字段不存在的，将无法添加，会报错【重点】

97488022338f639d6b1f6cc9de90b42b.png

sql语句创建表并且设置外键链表

KEY 外键名称 (外键字段), #设置外键名称

CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 连接表名称 (连接表主键字段) #设置外键链表

--CREATE TABLE `usr` (--`id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'id',--`yhm` char(255) NOT NULL COMMENT '用户名',--`xb` char(255) NOT NULL COMMENT '性别',--`nl` int(255) NOT NULL COMMENT '年龄',--`fzu` int(255) NOT NULL COMMENT '分组',--PRIMARY KEY (`id`),--KEY 外键名称 (外键字段), #设置外键名称--CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 连接表名称 (连接表主键字段) #设置外键链表--) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表-连表usr_fzu';

CREATE TABLE`usr` (

`id`int(20) NOT NULL AUTO_INCREMENT COMMENT 'id',

`yhm`char(255) NOT NULL COMMENT '用户名',

`xb`char(255) NOT NULL COMMENT '性别',

`nl`int(255) NOT NULL COMMENT '年龄',

`fzu`int(255) NOT NULL COMMENT '分组',PRIMARY KEY(`id`),KEY `usr-usr_fzu` (`fzu`),CONSTRAINT `usr-usr_fzu` FOREIGN KEY (`fzu`) REFERENCES`usr_fzu` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表-连表usr_fzu';

sql语句删除表的外键链表

ALTER TABLE 删除外键的表名称 DROP FOREIGN KEY 创建外键时的外键名称;#删除外键

--ALTER TABLE 删除外键的表名称 DROP FOREIGN KEY 创建外键时的外键名称;

ALTER TABLE usr DROP FOREIGN KEY usr_usr_fzu;

sql语句创建表的外键链表

ALTER TABLE 要创建外键的表 ADD CONSTRAINT 设置外键名称 FOREIGN KEY (外键字段) REFERENCES 要连接的表名称 (要连接表的主键id);#创建外键

--ALTER TABLE 要创建外键的表 ADD CONSTRAINT 设置外键名称 FOREIGN KEY (外键字段) REFERENCES 要连接的表名称 (要连接表的主键id);

ALTER TABLE usr ADD CONSTRAINT usr_usr_fzu FOREIGN KEY (fzu) REFERENCES usr_fzu (id);

sql语句连表查询

LEFT JOIN：左连接的意思

ON：接通

SELECT 显示字段 FROM 外键表 LEFT JOIN 连接表 ON 外键表.外键字段 = 连接表.连接表主键; #链表查询，将连接表的信息组合到外键表一起显示出来

a表 LEFT JOIN b表 ON a表.外键字段 = b表.主键字段

--LEFT JOIN：左连接的意思--ON：接通--SELECT 显示字段 FROM 外键表 LEFT JOIN 连接表 ON 外键表.外键字段 = 连接表.连接表主键; #链表查询，将连接表的信息组合到外键表一起显示出来

-- a表 LEFT JOIN b表 ON a表.外键字段 = b表.主键字段

SELECT * FROM usr LEFT JOIN usr_fzu ON usr.fzu = usr_fzu.id;

外键表

b3ac2a6beb8c2ac248c2376cf861d8f2.png

连接表

bb00b43723604baabd4a680249efc159.png

将连接表的信息组合到外键表一起显示出来

914073685052c5562629657d24e46552.png

sql语句连表加WHERE条件查询

a表 LEFT JOIN b表 ON a表.外键字段 = b表.主键字段 WHERE b表.字段 = '值';

举例：查询a表的外键值、属于b表主键值对应的、某个字段值的所有数据

5b80dc76104fdfc25027e7934d48be33.png

--a表 LEFT JOIN b表 ON a表.外键字段 = b表.主键字段 WHERE b表.字段 = '值';

SELECT * FROM usr LEFT JOIN usr_fzu ON usr.fzu = usr_fzu.id WHERE usr_fzu.usr_fzu = 'A';

dff1c3287cf0907d8879d57d62ef0697.png

可以看到只查询了属于A的数据

sql语句连表关键字说明

连表关键字有3种：

LEFT JOIN

RIGHT JOIN

INNER JOIN

使用方法：

a表 LEFT JOIN b表 ON a表.xx = b表.xx

a表 RIGHT JOIN b表 ON a表.xx = b表.xx

a表 INNER JOIN b表 ON a表.xx = b表.xx

注意：a表是外键表，b表是连接表

它们连表之间的区别

LEFT JOIN ：以LEFT JOIN前面的表为主，前面的表所有数据都会显示

a表 LEFT JOIN b表：以a表为主，只显示与a表有关联的b表数据

8d361ce2bbe2067bcb1cb94d06655310.png

ea8bf2bf74be7812a268af17e19cd1ef.png

SELECT * FROM usr LEFT JOIN usr_fzu ON usr.fzu = usr_fzu.id;

8888677add7d8b5c29a9a3aedf79a242.png

可以看到b表里的usr_fzu字段的C、D没有显示

b表 LEFT JOIN a表：以b表为主，b表里与a表无关联数据也会显示

8b01ce43b10ed4c1611d143794f5b3d6.png

SELECT * FROM usr_fzu LEFT JOIN usr ON usr.fzu = usr_fzu.id;

2635b11ed8633d4bd8482e236f9659a5.png

可以看到b表里的usr_fzu字段里与a表无关联的也显示了

RIGHT JOIN：是以RIGHT JOIN后面的表为主，显示后面表的所有数据

a表 LEFT JOIN b表：以b表为主

b表 LEFT JOIN a表：以a表为主

以上面列子相同，就不在举例了

INNER JOIN：不以哪个表为主，只要是两个表数据之间无关联的都不显示

a表 LEFT JOIN b表：两个表数据之间无关联的都不显示

b表 LEFT JOIN a表：两个表数据之间无关联的都不显示

以上面列子相同，就不在举例了

一张表两个外键链表

330820ce715200cf5970a2375eecc353.png

c8a2607f85c0402e3e8ddb783a967d31.png

7a61ae847da53351e6b0a18e93ede8aa.png

一张表两个外键链表的查询

SELECT

*

FROMusrLEFT JOIN usr_fzu ON usr.usr_fzu_id =usr_fzu.idLEFT JOIN usr_yans ON usr.usr_yans_id = usr_yans.id;

31610abaefdecb1774965eeaf54c0103.png

一张表两个外键链表的WHERE条件查询

SELECT

*

FROMusrLEFT JOIN usr_fzu ON usr.usr_fzu_id =usr_fzu.idLEFT JOIN usr_yans ON usr.usr_yans_id =usr_yans.id--链表查询，用户在usr_fzu.fzu = 'B' 并且在 usr_yans.yans = 'hong' 的用户

WHERE usr_fzu.fzu = 'B' AND usr_yans.yans = 'hong'

05ec09a73e55fbf2d494b3b178d6457a.png

重点：外键链表，无论是外键连接了几张表，其本质就是将外键连接的表组合到当前表成为一张表，来各种条件查询

重点：一般在链表查询字段、和链表取数据字段时，需要加上所操作的表名称.字段名称，如:usr_yans.yans(usr_yans表的yans字段)

字典表：外键连接的表一般被称为字典表

外键链表之多对多

1、多对多是通过一个关系表外键链表，将多个表建立关系

2、可以是1条数据对应多条数据，或者多条数据对应1条数据

3、也可以是多条数据对应多条数据

举例：创建男女恋爱关系多对多

一个男人可能跟多个女人谈过恋爱，一个女人可能跟多个男人谈过恋爱，通过链表多对多统计出某个男人有跟哪些女人谈过恋爱

1、建立一张表存放男人信息

2、建立一张表存放女人信息

3、建立一张表外键连接这两张男女信息表，记录男女的恋爱关系

5e8f2b8924dca1c265f81b95aed6dc91.png

创建外键链表关系

5e27a2d74c19d4b459646c88a3e307b1.png

将3张表组合一起查询

SELECT

*

FROMnan_nvLEFT JOIN nan ON nan_nv.nan_id =nan.idLEFT JOIN nv ON nan_nv.nv_id = nv.id;

10c91f0df78cefc2c49e307081f1bf02.png

将3张表组合一起加WHERE条件查询

如：查询与张三有过恋爱关系的女人

SELECT

*

FROMnan_nvLEFT JOIN nan ON nan_nv.nan_id =nan.idLEFT JOIN nv ON nan_nv.nv_id =nv.idWHERE nan.nan = '张三(男)';

471de57c03903769163d346ea116d5c3.png

版权声明：本文为weixin_28979339原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。