深入 mysql，掌握一对一、一对多、多对多表设计、查询及级联操作

数据库表的基本概念与关系

数据库通常包含多个表，每个表存储特定类型的信息。例如：

  • 学生表：存储学生信息。
  • 老师表：存储老师信息。
  • 班级表：存储班级信息。

这些表通过各种关系连接，形成一个结构化的数据管理系统。主要的关系类型包括：

  • 一对多关系：如一个班级包含多名学生。
  • 多对多关系：如一个老师可教授多个班级，一个班级也可由多名老师教授。
  • 一对一关系：如一个用户对应一个身份证信息。

外键与表关联

外键是实现表之间关系的关键工具。通过外键，可以将两个表连接起来，实现数据的整合查询。

示例：用户表与身份证表的一对一关系：

  • 用户表（user）：
    • id：主键，自动递增。
    • name：用户名，字符串类型。
  • 身份证表（id_card）：
    • id：主键，自动递增。
    • card_name：身份证号，字符串类型。
    • user_id：外键，引用用户表的id。
 

sql
 

 

CREATE TABLE `user` (`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(45) NOT NULL,PRIMARY KEY (`id`)
);CREATE TABLE `id_card` (`id` INT NOT NULL AUTO_INCREMENT,`card_name` VARCHAR(45) NOT NULL,`user_id` INT,PRIMARY KEY (`id`),INDEX `card_id_idx` (`user_id`),CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
);
 

我们也可以选择可视化方式场景，选择 hello-mysql 数据库，点击建表按钮：
  
 

image.png
 


 分别添加 id、name 列：
  
 

image.png
 


 id_card 表：
  
 

image.png
 


 指定外键 user_id 关联 user 表的 id：
  
 

image.png
 


 还要选择主表数据 update 或者 delete 的时候，从表怎么办，这里我们先用默认的：
 

image.png
 

数据操作与查询
 

插入数据后，可以通过多表关联查询来查看关联数据：
 

 

-- 插入数据
INSERT INTO `user` (`name`)VALUES('张三'),('李四'),('王五');
INSERT INTO id_card (card_name, user_id) VALUES('110101199001011234',1),('310101199002022345',2),('440101199003033456',3);-- 多表关联查询
-- 选择 user 表的 id 和 name 字段，以及 id_card 表的 id 和 card_name 字段
SELECT user.id, name, id_card.id AS card_id, card_name 
-- 从 user 表开始查询
FROM user
-- 与 id_card 表进行内连接，连接条件是 user 表的 id 字段等于 id_card 表的 user_id 字段
JOIN id_card ON user.id = id_card.user_id;

 


  
 

image.png
 


 这就是多表关联查询，语法是 JOIN ON。
 

JOIN 类型解析
 

  • INNER JOIN（默认）: 只返回两个表中能关联上的数据。
  • LEFT JOIN: 返回左表（FROM 语句之后的表）的所有记录，即使右表中没有匹配的记录。
  • RIGHT JOIN: 返回右表（JOIN 语句之后的表）的所有记录，即使左表中没有匹配的记录。
 

例如，使用 RIGHT JOIN 的查询如下：
 

-- 选择 user 表的 id 和 name 字段，以及 id_card 表的 id 和 card_name 字段
SELECT user.id, name, id_card.id AS card_id, card_name
-- 从 user 表开始查询
FROM user
-- 通过 RIGHT JOIN 右连接 id_card 表，连接条件是 user 表的 id 字段等于 id_card 表的 user_id 字段
RIGHT JOIN id_card ON user.id = id_card.user_id;

 

此查询将返回所有 id_card 的数据，对于没有关联的 user 数据，其对应字段会显示为 null。
 我们更新下 id_card 表的级联方式：
  
 

image.png
 


 删除条 user 数据：
  
 

image.png
 


 再执行上面的右查询代码：
 

image.png
 

外键约束与级联操作
 

外键约束用于维护数据库表之间的完整性。常见的外键约束处理方式包括：
 

  • RESTRICT / NO ACTION:（通常是默认行为） 只有当从表没有关联记录时，才允许删除或更新主表的记录。
  • CASCADE: 如果主表（外键指向的表）的记录被删除或更新，从表（有外键的表）的相应记录也会被级联删除或更新。
  • SET NULL: 如果主表的记录被删除或其关键字段被更新，从表的外键字段会被设置为 null。
 

例如，如果设置外键约束为 CASCADE，并且更新了 user 表中某个 id，则 id_card 表中相应的 user_id 也会更新。如果删除了 user 表中的记录，所有关联的 id_card 记录也会被删除。
 

注意：在外键关系中，主表是被外键引用的表，而从表是包含外键的表。因此，当主表中的记录发生变更时，从表中依赖于这些记录的外键字段会受到影响。
 

一对多关系
 

一对多关系是数据库中常见的数据结构，其中一个实体可以关联多个其他实体，但这些被关联的实体仅指向单一实体。例如：
 

  • 作者与文章：一个作者可以写多篇文章，但每篇文章只能有一个作者。
  • 订单与商品：一个订单可以包含多个商品，但每个商品只属于一个订单。
  • 部门与员工：一个部门可以有多名员工，但每名员工只属于一个部门。
 

实现方式
 

在数据库中，通常通过在“多”的一方添加一个外键来实现一对多关系。
 例如，员工表中会包含一个指向部门表的外键 department_id。
 

 

 
 

-- 创建一个名为 `department` 的表，用于存储部门信息
CREATE TABLE `department` (`id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长`name` VARCHAR(45) NOT NULL,       -- 定义一个名为 `name` 的列，类型为 VARCHAR(45)，不允许为空PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);-- 创建一个名为 `employee` 的表，用于存储员工信息
CREATE TABLE `employee` (`id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长`name` VARCHAR(45) NOT NULL,       -- 定义一个名为 `name` 的列，类型为 VARCHAR(45)，不允许为空`department_id` INT,               -- 定义一个名为 `department_id` 的列，类型为 INT，用于存储员工所属部门的 IDFOREIGN KEY (`department_id`) REFERENCES `department`(`id`) ON DELETE SET NULL,            -- 设置外键，`department_id` 引用 `department` 表的 `id`，当部门被删除时，相关员工的 `department_id` 设置为 NULLPRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);

 

创建了两个表：
 

  1. 部门表 (department)： 
    • id：部门的唯一标识符，自动增长，作为主键。
    • name：部门的名称，存储类型为长度最大为 45 的字符串。
  2. 员工表 (employee)： 
    • id：员工的唯一标识符，自动增长，作为主键。
    • name：员工的姓名，存储类型为长度最大为 45 的字符串。
    • department_id：外键，指向部门表的 id，表示员工所属的部门。如果所属部门被删除，员工记录中的 department_id 将被设置为 NULL，表示不再属于任何部门。
 

多对多关系
 

多对多关系指两种实体相互间可以有多个关联。例如：
 

  • 文章与标签：一篇文章可以有多个标签，同一个标签可以标记多篇文章。
  • 学生与课程：一个学生可以选修多门课程，一门课程可以被多个学生选修。
  • 用户与角色：一个用户可以拥有多个角色，一个角色可以包含多个用户。
 

实现方式
 

多对多关系通常通过一个中间表来实现，这个表存储两个实体之间的关联：
 

 

 
 

-- 创建一个名为 `article` 的表，用于存储文章信息
CREATE TABLE `article` (`id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长，用于唯一标识每篇文章`title` VARCHAR(50) NOT NULL,      -- 定义一个名为 `title` 的列，类型为 VARCHAR(50)，不允许为空，用于存储文章的标题`content` TEXT NOT NULL,           -- 定义一个名为 `content` 的列，类型为 TEXT，不允许为空，用于存储文章的内容PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);-- 创建一个名为 `tag` 的表，用于存储标签信息
CREATE TABLE `tag` (`id` INT NOT NULL AUTO_INCREMENT,  -- 定义一个名为 `id` 的列，类型为 INT，不允许为空，自动增长，用于唯一标识每个标签`name` VARCHAR(50) NOT NULL,       -- 定义一个名为 `name` 的列，类型为 VARCHAR(50)，不允许为空，用于存储标签的名称PRIMARY KEY (`id`)                 -- 将 `id` 列设置为主键
);-- 创建一个名为 `article_tag` 的中间表，用于存储文章和标签之间的多对多关系
CREATE TABLE `article_tag` (`article_id` INT NOT NULL,         -- 定义一个名为 `article_id` 的列，类型为 INT，不允许为空，用于引用文章表的 `id``tag_id` INT NOT NULL,             -- 定义一个名为 `tag_id` 的列，类型为 INT，不允许为空，用于引用标签表的 `id`PRIMARY KEY (`article_id`, `tag_id`),  -- 将 `article_id` 和 `tag_id` 组合设置为主键，确保每对文章和标签的关系是唯一的FOREIGN KEY (`article_id`) REFERENCES `article`(`id`) ON DELETE CASCADE,  -- 设置外键，当引用的文章被删除时，相关的关联记录也会被自动删除FOREIGN KEY (`tag_id`) REFERENCES `tag`(`id`) ON DELETE CASCADE  -- 设置外键，当引用的标签被删除时，相关的关联记录也会被自动删除
);

 

  1. 文章表 (**article**)： 
    • id：文章的唯一标识符，自动增长，作为主键。
    • title：文章的标题，存储类型为长度最大为 50 的字符串。
    • content：文章的内容，存储类型为 TEXT，可以存储较长的文本。
  2. 标签表 (**tag**)： 
    • id：标签的唯一标识符，自动增长，作为主键。
    • name：标签的名称，存储类型为长度最大为 50 的字符串。
  3. 文章标签关联表 (**article_tag**)： 
    • article_id 和 tag_id：用于存储文章和标签之间的关系。每个字段分别引用 article 表和 tag 表的 id。
    • 通过设置复合主键（article_id, tag_id），确保每对文章和标签的关联是唯一的。
    • 外键约束带有 ON DELETE CASCADE 选项，意味着当删除文章或标签时，所有相关的关联记录也会自动被删除，这有助于维护数据库的完整性和一致性。
 

假设有以下文章和标签：
 

  • 文章1: ID = 1
  • 文章2: ID = 2
  • 标签A: ID = 1
  • 标签B: ID = 2
  • 标签C: ID = 3
 

文章到标签的关系如下：
 

  • 文章1 关联标签A 和 标签B
  • 文章2 关联标签A 和 标签C
 

那么 article_tag 表中的数据会是这样的：
 

 

 
 

+------------+--------+
| article_id | tag_id |
+------------+--------+
| 1          | 1      |
| 1          | 2      |
| 2          | 1      |
| 2          | 3      |
+------------+--------+

 

查询与操作
 

查询操作
 

使用 JOIN 语句可以查询出部门及其所有员工，或文章及其所有标签：
 

 

 
 

-- 查询部门及其员工
SELECT * 
FROM `department`  -- 从“department”表中选择数据，该表存储部门信息
JOIN `employee`    -- 通过 JOIN 操作与“employee”表连接
ON `department`.`id` = `employee`.`department_id`  -- 连接条件是部门表的 id 与员工表的部门 id 相匹配
WHERE `department`.`id` = 5;  -- 仅查询 id 为 5 的部门及其员工-- 查询文章及其标签
SELECT `t`.`name` AS 标签名, `a`.`title` AS 文章标题
FROM `article` `a`  -- 从“article”表中选择数据，该表存储文章信息，并使用别名 `a`
JOIN `article_tag` `at` ON `a`.`id` = `at`.`article_id`  -- 通过 JOIN 操作连接“article_tag”表，使用别名 `at`，连接条件是文章 ID 匹配
JOIN `tag` `t` ON `t`.`id` = `at`.`tag_id`  -- 再通过 JOIN 操作连接“tag”表，使用别名 `t`，连接条件是标签 ID 匹配
WHERE `a`.`id` = 1;  -- 仅查询 id 为 1 的文章及其对应的标签

 

删除操作
 

删除一篇文章时，与之关联的标签关系也会被自动删除，这是通过设置外键的 ON DELETE CASCADE 选项实现的：
 

DELETE FROM `article` WHERE `id` = 1;
 

 

 











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


如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！












相关文章










基于WOA-SVM的乳腺癌数据分类识别算法matlab仿真,对比BP神经网络和SVM








基于WOA-SVM的乳腺癌数据分类识别算法matlab仿真,对比BP神经网络和SVM




目录 
1.算法运行效果图预览 
2.算法运行软件版本 
3.部分核心程序 
4.算法理论概述 
4.1 支持向量机（SVM） 
4.2 WOA 
4.3 WOA优化SVM参数 
5.算法完整程序工程 1.算法运行效果图预览 
(完整程序运行后无水印) 2.算法运行软件版本 
matlab2022a 
3.部分核…




阅读更多...

















零工市场小程序：保障灵活就业








零工市场小程序：保障灵活就业




截止2024年高校毕业生达到1179万，在今年的经济情况下，就业市场就面临着比较大的压力，许多毕业生面临一时之间难以找到合适的工作的问题，那么求职者就会需要一份临时的工作来得到报酬，面对传统的找零工方式，…




阅读更多...

















用ASR PRO离线语音芯片和月饼盒做一个会跑会跳会说话的机器狗








用ASR PRO离线语音芯片和月饼盒做一个会跑会跳会说话的机器狗




中秋节刚过，大家月饼盒应该还有，不要扔，可以做点小玩意。 
机器狗的创意来自B站石桥北的视频，他使用了一块ESP32芯片和打印件加四个舵机实现，应该说是比较复杂的，需要有3D打印机打印外壳，还得会…




阅读更多...

















Qt串口助手开发：基于多线程moveToThread方法串口通信工具








Qt串口助手开发：基于多线程moveToThread方法串口通信工具




介绍了一个基于Qt框架开发的简易串口助手，满足粉丝的需求。该项目展示了如何利用Qt的moveToThread方法实现多线程串口通信，确保数据接收和发送功能的流畅性。项目中的核心类包括SerialWorker类和MainWindow类，分别负责串口操作和用户界面交互…




阅读更多...

















【数据可视化】Arcgis api4.x 热力图、时间动态热力图、timeSlider时间滑块控件应用 (超详细、附免费教学数据、收藏！)








【数据可视化】Arcgis api4.x 热力图、时间动态热力图、timeSlider时间滑块控件应用 (超详细、附免费教学数据、收藏！)




1.效果 目录 
1.效果 
2.安装配置 
3.热力图 
4.TimeSlider滑块应用 4.1  时间滑块控件 4.2 添加控件 
5.时间动态热力图  2.安装配置 这里不教大家如何在前端框架使用arcgis api。不过npm安装、css如何引入、教学数据存放与图层加载的教程，可以浏览我之前发的一篇文…




阅读更多...

















高效财税自动化软件的特点与优势








高效财税自动化软件的特点与优势




随着企业管理信息系统和互联网的不断发展，企业对财务管理提出了更高的要求。为有效助力企业规范财务工作，提高工作效率和准确性，实现信息化管理，越来越多的企业选择引入RPA等高效财税自动化软件。本文金智维将围绕RPA高效财税自动…




阅读更多...

















LeetCode 2332.坐上公交的最晚时间 (双指针 + 贪心)








LeetCode 2332.坐上公交的最晚时间 (双指针 + 贪心)




给你一个下标从 0 开始长度为 n 的整数数组 buses ，其中 buses[i] 表示第 i 辆公交车的出发时间。同时给你一个下标从 0 开始长度为 m 的整数数组 passengers ，其中 passengers[j] 表示第 j 位乘客的到达时间。所有公交车出发的时间互不相同，…




阅读更多...

















基于SpringCloud的能源管理系统-能源管理平台源码-双碳平台源码-能管管理系统源码








基于SpringCloud的能源管理系统-能源管理平台源码-双碳平台源码-能管管理系统源码




一、介绍 
基于SpringCloud的能管管理系统-能源管理平台源码-能源在线监测平台-双碳平台源码-SpringCloud全家桶-能管管理系统源码 
二、软件架构  二、功能介绍  三、数字大屏展示  四、数据采集原理 五、软件截图




阅读更多...

















Mycat搭建读写分离








Mycat搭建读写分离




启动Mycat 进入 /mycat/conf/datasources目录下，修改prototypeDs.datasource.json文件 去mycat/bin目录用启动mycat  ./mycat start  (关闭mycat   ./mycat stop)连接mycat  默认端口8066 用户名root 密码123456 注意：这里ip设为null表示任何ip都可以访问…




阅读更多...

















【设计模式-组合】








【设计模式-组合】




**Composite Pattern（组合模式）**是一种结构型设计模式，旨在将对象组合成树形结构，以表示“部分-整体”的层次结构。这种模式允许客户端以统一的方式处理单个对象和对象集合，从而简化了树形结构的处理。 
核心思想 
组…




阅读更多...

















LLM应用实战: 文档问答系统Kotaemon-1. 简介及部署实践








LLM应用实战: 文档问答系统Kotaemon-1. 简介及部署实践




1.背景  
本qiang~这两周关注到一个很火的开源文档问答系统Kotaemon，从8月28日至今短短两周时间，github星标迅猛增长10K，因此计划深挖一下其中的原理及奥秘。 
本篇主要是Kotaemon的简介信息，涉及到主要特点，与传统文档…




阅读更多...

















MindShare PCIE 3.0 笔记-第一二章








MindShare PCIE 3.0 笔记-第一二章




MindShare 官网，地址如下: MindShare 
Chapter 1：PCIE 背景介绍 - PCI 总线模型 
1. 以 PCI 总线作为外设总线的 SOC 芯片架构 
下图展示了一个以 PCI 总线作为外设总线的 SOC 芯片架构(PCI 总线类似 AXI 下的 AHB？)：  
由上图可知…




阅读更多...

















虚拟机与物理机的文件共享








虚拟机与物理机的文件共享




之前往虚拟机里传文件都是直接拖拽或者借助工具上传，都不太方便，倘若物理机的文件直接能在虚拟机里读取使用，那多好啊~ 
1 虚拟机设置 
注意文件夹名称不要中文/空格 2 验证Kali下分享文件夹功能是否启用 
vmware-hgfsclient  
3 创建挂载目录…




阅读更多...

















数据库基础知识---------------------------（2）








数据库基础知识---------------------------（2）




MYSQL的存储过程 
就是数据库 SQL 语言层面的代码封装与重用 
语法格式 delimiter 自定义结束符号 create procedure 存储名({in,out,inout} 参数名,数据类型...) begin sql 语句 end 自定义结束符 delimiter; 变量定义 局部变量  用户自定义 仅在begin / end 块中有效 当将查询…




阅读更多...

















apach httpd多后缀解析漏洞








apach httpd多后缀解析漏洞




漏洞详情： 
httpd支持一个文件拥有多个后缀，并为不同后缀执行不同的指令。 那么，在有多个后缀的情况下，只要一个文件含有.php后缀的文件即将被识别成PHP文件，没必要是最后一个后缀。 
利用这个特性，可以绕过…




阅读更多...

















Linux硬连接、软连接和复制的区别








Linux硬连接、软连接和复制的区别




‌硬连接、软连接和复制在Linux系统中的主要区别体现在以下三点： 
文件链接的方式文件独立性文件系统的操作上。‌ 一、硬连接 
1. 硬连接是通过ln命令创建的，它为文件创建别名，与源文件共享同一inode号码，因此硬连接和源文件实际…




阅读更多...

















Mint Expedition Season 3 拉开帷幕：登顶高峰的时刻到了








Mint Expedition Season 3 拉开帷幕：登顶高峰的时刻到了




自 7 月 15 日 Mint Expedition 启动以来，Mint，一条专注于 NFT 行业的以太坊 Layer 2，日常交易量和交易额都出现了爆发式增长。这一成功离不开 Mint 社区的合作，包括 Minters、Web3 去中心化应用程序的开发者，以及大量…




阅读更多...

















02 ETH








02 ETH




以太坊与比特币有什么不同？ 
以太坊立足比特币创新之上，于 2015 年启动，两者之间有一些显著不同。 
比特币就仅仅是比特币；以太坊包括以太币，以太币才是和比特币对等的存在。以太坊是可编程的，所以你可以在…




阅读更多...

















示例：WPF中Grid显示网格线的几种方式








示例：WPF中Grid显示网格线的几种方式




一、目的：介绍一下WPF中Grid显示网格线的几种方式 二、几种方式 1、重写OnRender绘制网格线（推荐） 
效果如下： 实现方式如下： public class LineGrid : Grid{protected override void OnRender(DrawingContext dc){Pen…




阅读更多...

















SQL 多表联查








SQL 多表联查




目录 
1. 内联接（INNER JOIN） 
2. 左外联接（LEFT JOIN） 
3. 右外联接（RIGHT JOIN） 
4. 全外联接（FULL JOIN） 
5. 交叉联接（CROSS JOIN） 
6. 自联接&#xff0…




阅读更多...





























最新文章






























Copyright @ 2022~2023