• 第二百八十节,MySQL数据库-外键链表之一对多,多对多


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

    外键链表之一对多

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

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

    用Navicat 软件可视化外键链表

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

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

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

    外键链表约束

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

    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;

    外键表

    连接表

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

    sql语句连表加WHERE条件查询

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

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

    -- 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';

    可以看到只查询了属于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表数据

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

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

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

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

    可以看到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表:两个表数据之间无关联的都不显示

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

    一张表两个外键链表

      

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

    SELECT
        *
    FROM
        usr
    LEFT JOIN usr_fzu ON usr.usr_fzu_id = usr_fzu.id
    LEFT JOIN usr_yans ON usr.usr_yans_id = usr_yans.id;

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

    SELECT
        *
    FROM
        usr
    LEFT JOIN usr_fzu ON usr.usr_fzu_id = usr_fzu.id
    LEFT 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'

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

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

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

    外键链表之多对多

    1、多对多是通过一个关系表外键链表,将多个表建立关系
    2、可以是1条数据对应多条数据,或者多条数据对应1条数据
    3、也可以是多条数据对应多条数据

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

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

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

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

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

    创建外键链表关系

    将3张表组合一起查询

    SELECT
        *
    FROM
        nan_nv
    LEFT JOIN nan ON nan_nv.nan_id = nan.id
    LEFT JOIN nv ON nan_nv.nv_id = nv.id;

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

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

    SELECT
        *
    FROM
        nan_nv
    LEFT JOIN nan ON nan_nv.nan_id = nan.id
    LEFT JOIN nv ON nan_nv.nv_id = nv.id
    WHERE nan.nan = '张三(男)';

  • 相关阅读:
    css 边框添加四个角效果
    PS 怎么去掉噪点
    Packet Tracer——添加温度计
    zabbix监控mysql数据
    日常检测
    mysql的基本命令操作
    判断字典中是否有这个单词
    批量创建用户
    检测局域网ip存活状态
    spring boot gradle build:bootRepackage failed
  • 原文地址:https://www.cnblogs.com/adc8868/p/6950965.html
Copyright © 2020-2023  润新知