【MySQL】多表操作 —— 外键约束

目录

  • 多表关系
    • 一对一关系
    • 一对多/多对一关系
    • 多对多关系
  • 外键约束
    • 基本概念
    • 一对多/多对一
      • 创建外键约束
      • 外键约束下的数据操作
        • 数据插入
        • 数据删除
      • 删除外键约束
    • 多对多
      • 创建外键约束
      • 外键约束下的数据操作
        • 数据插入
        • 数据删除
      • 删除外键约束

多表关系

MySQL 多表之间的关系可以概括为:

  • 一对一
  • 一对多/多对一
  • 多对多

一对一关系

例子:一个学生只有一张身份证,一张身份证只能对应一学生

在任一表中添加唯一外键,指向另一方主键,确保一对一关系

一般一对一关系较少见,遇到一对一关系的表最好是合并表

在这里插入图片描述

一对多/多对一关系

例子:一个部门有多个员工,一个员工只能对应一个部门

实现原则:在多的一方建立外键,指向一的一方的主键

在这里插入图片描述

多对多关系

例子:一个学生可以选择很多门课,一门课也可以被很多学生选择

原则:多对多关系实现需要借助第三张中间表,中间表至少包含两个字段,将多对多的关系,拆成一对多的关系,中间表至少要有两个外键,这两个外键分别指向原来两张表的主键

在这里插入图片描述

外键约束

基本概念

MySQL 外键约束(foreign key)是表的一个特殊字段,经常与主键一起使用,对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)

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

在这里插入图片描述

定义一个外键时,需要遵守下列规则:

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

一对多/多对一

创建外键约束

方式 1:创建表时设置

在 create table 语句中,通过 foreign key 关键字来指定外键

格式如下:

[constraint 外键名] foreign key (外键字段名[,外键字段名2,...]) references 主表名(主键字段名[,主键字段名2,...])

代码示例:

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

结果如下:

在这里插入图片描述

方式 2:创建表后设置

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

格式如下:

alter table 从表名 add constraint 外键名 foreign key (外键字段名) references 主表名(主键字段名)

代码示例:

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) -- 员工所属部门
);
alter table emp add constraint dept_fk foreign key (dept_id) references dept(deptno);

结果如下:

在这里插入图片描述

外键约束下的数据操作

数据插入

代码示例:

insert into dept values ('1001','研发部'),('1002','销售部');
insert into emp values ('1','张三',20,'1001'),('2','李四',20,'1001'),('3','王五',21,'1002'),('4','赵六',22,'1002'),('5','孙七',24,'1003');

结果如下:

在这里插入图片描述

由于 emp 表的外键列插入了 dept 表主键列中没有的数据,导致报错

注意事项:

  • 必须先给主表添加数据
  • 从表添加数据时,外键列的值不能随便写,必须依赖主表的主键列
数据删除

代码示例:

delete from dept where deptno = '1001';

结果如下:

在这里插入图片描述

由于 dept 表中主键列中的 1001 被 emp 表中的外键列依赖,所以删除会报错

注意事项:

  • 删除表要先删除从表才能删除主表

  • 主表的数据被从表依赖时,不能删除,没有被依赖的数据则可以删除

  • 从表的数据可以随便删除

删除外键约束

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

格式如下:

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

代码示例:

alter table emp drop foreign key dept_fk;

结果如下:

在这里插入图片描述

多对多

在多对多关系中,A 表的一行对应 B 表的多行,B 表的一行对应 A 表的多行,需要新增加一个中间表,来建立多对多关系

在这里插入图片描述

student 表和 course 表都是主表,student_course 表是从表,也就是说,在多对多关系中,可以有多个主表

创建外键约束

从表对每个主表都设置一个外键约束

代码示例:

create table if not exists student(sid int primary key auto_increment, -- 学生学号name varchar(20), -- 学生姓名age int -- 学生年龄
);
create table if not exists course(cid int primary key auto_increment, -- 课程编号cname varchar(20) -- 课程名
);
create table if not exists student_course(sid int ,cid int,score double -- 分数
);
alter table student_course add foreign key (sid) references student(sid);
alter table student_course add foreign key (cid) references course(cid);

结果如下:

在这里插入图片描述

外键约束下的数据操作

多对多中的外键约束其实跟一对多/多对一中的外键约束没什么区别,这里就直接给出正确代码

数据插入

代码示例:

insert into student values (1,'张三',20),(2,'李四',20);
insert into course values (001,'语文'),(002,'数学'),(003,'英语');
insert into student_course values (1,001,99),(2,001,97),(1,002,100),(2,002,90),(1,003,79),(2,003,99);

结果如下:

在这里插入图片描述

注意事项:

  • 必须先给主表添加数据
  • 从表添加数据时,外键列的值不能随便写,必须依赖主表的主键列
数据删除

代码示例:

delete from student_course;

结果如下:

在这里插入图片描述

注意事项:

  • 删除表要先删除从表才能删除主表

  • 主表的数据被从表依赖时,不能删除,没有被依赖的数据则可以删除

  • 从表的数据可以随便删除

删除外键约束

代码示例:

alter table student_course drop foreign key student_course_ibfk_1;
alter table student_course drop foreign key student_course_ibfk_2;

结果如下:

在这里插入图片描述

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/34935.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

82.HarmonyOS NEXT 性能优化指南:从理论到实践

温馨提示:本篇博客的详细代码已发布到 git : https://gitcode.com/nutpi/HarmonyosNext 可以下载运行哦! HarmonyOS NEXT 性能优化指南:从理论到实践 文章目录 HarmonyOS NEXT 性能优化指南:从理论到实践1. 性能优化概述1.1 性能指…

树莓派急速安装ubuntu;映射磁盘与储存磁盘文件;ubuntu映射整个工程;保存系统工作状态

一、用途 在使用树莓派上下载ubuntu时,需要一张sd卡,当你需要给这张卡做备份的时候,可以是使用磁盘映射软件,从而达到备份的目的 同时有一些大佬发布了ubuntu的映射文件,可以直接使用该文件,然后还原他的整…

Qt 控件概述 QPushButton 与 QRadioButton

目录 QPushButton setIcon 设置图标 setShortCut 设置快捷键 setAutoRepeat : 设置是否连发 ​编辑RadioButton 槽函数 单选按钮的分组排异 QPushButton QPushButtoon继承于QAbstractButton(抽象类); QAbstract中与QPushButton关联性极大的属性 ​ setIcon…

HR9110 玩具单通道直流电机驱动器

1、描述 HR9110是应用于直流电机方案的单通道H桥驱动器芯片。HR9110的H桥驱动部分采用低导通电阻的PMOS和NMOS功率管。低导通电阻保证芯片低的功率损耗,使得芯片安全工作更长时间。此 外HR9110拥有低待机电流、低静态工作电流。这些性能使能HR9110易用于玩具方案。…

Mac 使用 Crossover 加载 Windows Steam 游戏库,实现 Windows/Mac 共享移动硬盘

Mac 使用 Crossover 加载 Windows Steam 游戏库,实现 Windows/Mac 共享移动硬盘 1. 在Crossover上安装Steam2. Steam容器加载移动硬盘3. 配置Steam库 前言:本文介绍了如何在Crossover上安装Steam并加载外接移动硬盘,实现在Window上下载的游戏…

ubuntu 24 安装 python3.x 教程

目录 注意事项 一、安装不同 Python 版本 1. 安装依赖 2. 下载 Python 源码 3. 解压并编译安装 二、管理多个 Python 版本 1. 查看已安装的 Python 版本 2. 配置环境变量 3. 使用 update-alternatives​ 管理 Python 版本 三、使用虚拟环境为项目指定特定 Python 版本…

沐数科技数据开发岗笔试题2025

描述性统计 标准差 答案: A 解析: 标准差 衡量数据集中数值变化或离散程度的一种度量。它反映了数据集中的各个数值与数据集的平均值(均值)之间的偏离程度。标准差越大,表明数据的分布越分散;标准差越小,表明数据…

ChatGPT-4

第一章:ChatGPT-4的技术背景与核心架构 1.1 生成式AI的发展脉络 生成式人工智能(Generative AI)的演进历程可追溯至20世纪50年代的早期自然语言处理研究。从基于规则的ELIZA系统到统计语言模型,再到深度学习的革命性突破&#x…

vulkanscenegraph显示倾斜模型(5.3)-相机

前言 在Vulkan中,相机的概念并非由API直接提供,而是由应用程序实现。相机的核心功能包括视图变换和投影变换:视图变换将世界坐标系中的物体转换到相机坐标系,投影变换则将相机坐标系中的物体转换到投影空间。在VSG(Vul…

【Pycharm】Pycharm无法复制粘贴,提示系统剪贴板不可用

我也没有用vim的插件,检查了本地和ubutnu上都没有。区别是我是远程到ubutnu的pycharm,我本地直接控制windowes的pycharm是没问题的。现象是可以从外部复制到pycharm反之则不行。 ctl c ctlv 以及右键 都不行 参考:Pycharm无法复制粘贴&…

MySQL 8 设置允许远程连接(Windows环境)

🌟 MySQL 8 设置允许远程连接(Windows环境) 在开发和部署应用时,经常需要从远程主机连接到MySQL数据库。默认情况下,MySQL仅允许本地连接,因此需要进行一些配置才能允许远程访问。今天,我将详细…

Prosys OPC UA Gateway:实现 OPC Classic 与 OPC UA 无缝连接

在工业自动化的数字化转型中,设备与系统之间的高效通信至关重要。然而,许多企业仍依赖于基于 COM/DCOM 技术的 OPC 产品,这给与现代化的 OPC UA 架构的集成带来了挑战。 Prosys OPC UA Gateway 正是为解决这一问题而生,它作为一款…

欢乐力扣:基本计算器

文章目录 1、题目描述2、思路代码括号 1、题目描述 基本计算器。  给你一个字符串表达式 s ,请你实现一个基本计算器来计算并返回它的值。  注意:不允许使用任何将字符串作为数学表达式计算的内置函数,比如 eval() 。 2、思路 本人也不太会&#xff0c…

SVN学习笔记

svn:版本控制软件 解决:1.协作开发 2.远程开发 3.版本回退 服务端软件: VisualSVN http://www.visualsvn.com 客户端软件:Tortoisesvn http://tortoisesvn.net/downloads 1.checkout(检出) 第一查更新数据到本地, 2.update&#xf…

Mysql表的查询

一:创建一个新的数据库(companydb),并查看数据库。 二:使用该数据库,并创建表worker。 mysql> use companydb;mysql> CREATE TABLE worker(-> 部门号 INT(11) NOT NULL,-> 职工号 INT(11) NOT NULL,-> 工作时间 D…

[ISP] 人眼中的颜色

相机是如何记录颜色的,又是如何被显示器还原的? 相机通过记录RGB数值然后显示器显示RGB数值来实现颜色的记录和呈现。道理是这么个道理,但实际上各厂家生产的相机对光的响应各不相同,并且不同厂家显示器对三原色的显示也天差地别&…

Cursor插件市场打不开解决

问题现象: cursor搜索插件的时候提示错误,无法搜索安装插件 error while fetching extensions.failed to fetch 问题原因 cursor默认安装使用的并不是vs code的插件市场,国内网络有时候打不开 解决 修改插件市场地址并重启cursor 打开cur…

R 语言科研绘图 --- 密度图-汇总

在发表科研论文的过程中,科研绘图是必不可少的,一张好看的图形会是文章很大的加分项。 为了便于使用,本系列文章介绍的所有绘图都已收录到了 sciRplot 项目中,获取方式: R 语言科研绘图模板 --- sciRplothttps://mp.…

安卓屏保调试

安卓屏保调试 - Wesley’s Blog 先看一下在设置点击屏保预览后的调用链(Android 14) #mermaid-svg-YQ66ef7zSvNutCCW {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-YQ66ef7zSvNutCCW .erro…

考研系列-408真题计算机网络篇(18-23)

写在前面 此文章是本人在备考过程中408真题计算机网络部分(2018年-2023年)的易错题及相应的知识点整理,后期复习也常常用到,对于知识提炼归纳理解起到了很大的作用,分享出来希望帮助到大家~ # 2018 1.停止-等待协议的…