最全MySQL8.0实战教程 11 MySQL的多表操作 11.1 多表关系【2 外键约束】

超、凢脫俗 2024-03-26 10:52 57阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  11 MySQL的多表操作
         *   *  11.1 多表关系
             *   *  11.1.3 外键约束
                 *   *  介绍
                     *  特点
                     *  操作 - 创建外键约束
                     *  操作-在外键约束下的数据操作
                     *  操作 - 删除外键约束

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 11 MySQL的多表操作 ####

##### 11.1 多表关系 #####

###### 11.1.3 外键约束 ######

###### 介绍 ######

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、李子、西瓜等 4 种水果，那么，你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜，其它的水果都是不能购买的。

![在这里插入图片描述][763d949be3e94e26bad2e631061d9c0c.png_pic_center]

###### 特点 ######

定义一个外键时，需要遵守下列规则：

*  主表必须已经存在于数据库中，或者是当前正在创建的表。
 *  必须为主表定义主键。
 *  主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。
 *  在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
 *  外键中列的数目必须和主表的主键中列的数目相同。
 *  外键中列的数据类型必须和主表主键中对应列的数据类型相同。

###### 操作 - 创建外键约束 ######

【方式1-在创建表时设置外键约束】

在 create table 语句中，通过 foreign key 关键字来指定外键，具体的语法格式如下：

[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]

实现：

create database mydb3; 
    
    use mydb3;
    
    -- 创建部门表【主】
    create table if not exists dept(
      deptno varchar(20) primary key ,  -- 部门号
      name varchar(20) -- 部门名字
    );
    
    -- 创建员工表【从】
    create table if not exists emp(
      eid varchar(20) primary key , -- 员工编号
      ename varchar(20), -- 员工名字
      age int,  -- 员工年龄
      dept_id varchar(20),  -- 员工所属部门
      constraint emp_fk foreign key (dept_id) references dept(deptno) -- 外键约束
    );

![在这里插入图片描述][564dfa58b20843158a66c1d0d158ee5d.png_pic_center]

查看约束

![在这里插入图片描述][d2c06e558346410d876035529a361900.png_pic_center]

![在这里插入图片描述][5a5da8c0842642369abbb452819f1abe.png_pic_center]

> 一对多关系

逆向表到模型

![在这里插入图片描述][d61c200af3224493b4e4a2aba97be4c9.png_pic_center]

【方式2 - 通过修改表结构设置外键约束】

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

alter table <数据表名> add constraint <外键名> foreign key(<列名>) references <主表名> (<列名>);

-- 创建部门表
    create table if not exists dept2(
      deptno varchar(20) primary key ,  -- 部门号
      name varchar(20) -- 部门名字
    );
    -- 创建员工表
    create table if not exists emp2(
      eid varchar(20) primary key , -- 员工编号
      ename varchar(20), -- 员工名字
      age int,  -- 员工年龄
      dept_id varchar(20)  -- 员工所属部门
    );
    -- 创建外键约束
    alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2(deptno);

![在这里插入图片描述][2c05bb521866402bafa50c1d27cde84f.png_pic_center]

同样的方法查看外键约束

![在这里插入图片描述][dc9955bce1bf440a81ac24350693f917.png_pic_center]

![在这里插入图片描述][bd01a03c06d245a195332ec2b5abd818.png_pic_center]

同样的一对多效果

###### 操作-在外键约束下的数据操作 ######

【1. 数据插入】

-- 1、添加主表数据
    -- 注意必须先给主表添加数据
    insert into dept values('1001','研发部');
    insert into dept values('1002','销售部');
    insert into dept values('1003','财务部');
    insert into dept values('1004','人事部');
    
    -- 2、添加从表数据
    -- 注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
         
    insert into emp values('1','乔峰',20, '1001');
    insert into emp values('2','段誉',21, '1001');
    insert into emp values('3','虚竹',23, '1001');
    insert into emp values('4','阿紫',18, '1002');
    insert into emp values('5','扫地僧',35, '1002');
    insert into emp values('6','李秋水',33, '1003');
    insert into emp values('7','鸠摩智',50, '1003'); 
    insert into emp values('8','天山童姥',60, '1005');  -- 不可以

![在这里插入图片描述][2ebf923b5fce4c359f08787b79eaf302.png_pic_center]

【2. 删除数据】

-- 3、删除数据
     /*
       注意：
           1：主表的数据被从表依赖时，不能删除，否则可以删除
           2: 从表的数据可以随便删除
     */
    delete from dept where deptno = '1001'; -- 不可以删除
    delete from dept where deptno = '1004'; -- 可以删除
    delete from emp where eid = '7'; -- 可以删除

![在这里插入图片描述][d6f447576b36447b9534a2973e16bd06.png_pic_center]

![在这里插入图片描述][e25bc18fc90d4c8eb2e4d6c8b647fabf.png_pic_center]

###### 操作 - 删除外键约束 ######

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系

格式：

alter table <表名> drop foreign key <外键约束名>;

实现：

alter table emp2 drop foreign key dept_id_fk;

![在这里插入图片描述][29faac2e29e44da9971e4362b24a6fc6.png_pic_center]

![在这里插入图片描述][d145a1d2c0a242c0a372eb9268360dc8.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[763d949be3e94e26bad2e631061d9c0c.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/479c9b364b5543abb46a0acd3c9f6b01.png
[564dfa58b20843158a66c1d0d158ee5d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8796105e6df140a6aeb86a10745098c8.png
[d2c06e558346410d876035529a361900.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/dd2f4b422cbe49eda226b50fa49eb97e.png
[5a5da8c0842642369abbb452819f1abe.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/b8af1b3b6d084c47aaae156afafe3a71.png
[d61c200af3224493b4e4a2aba97be4c9.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/990fcd1f46cc439d97792f5d8e0bcd87.png
[2c05bb521866402bafa50c1d27cde84f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/916dd40d049248eca1b1c2940a3564e9.png
[dc9955bce1bf440a81ac24350693f917.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/5ccc7d2ef3624167a117e45248dbae14.png
[bd01a03c06d245a195332ec2b5abd818.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ea4589ed8f9e422eafda74fd502b826c.png
[2ebf923b5fce4c359f08787b79eaf302.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/583329035c064811a0b319cfd96a0a32.png
[d6f447576b36447b9534a2973e16bd06.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/409a6988c76642e4bf699fe6a0061077.png
[e25bc18fc90d4c8eb2e4d6c8b647fabf.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f6fe0187aab04fb8b3458884b6f23c8d.png
[29faac2e29e44da9971e4362b24a6fc6.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/fa3fc068823f41b185bb76e2399eb7ac.png
[d145a1d2c0a242c0a372eb9268360dc8.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c7f6e41bf4eb439289a6ced1bfc66ed5.png