MySQL进阶的增删改查操作,包含各类查询的详细解释和操作以及表的设计讲解

MySQL 表的增删改查 大家肯定跟我一样,看到CRUD这四个字母一头雾水,什么是CRUD呀?这里我就为大家一一讲解:CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写是数据库操作最基本的操作也是未来我们进入公司实习,最开始的最日常的工作由此可见,对我们未来涞水非常的重要所以,接下来就让我们来看看具体什么是CRUD吧。 阅读详情

2.表的设计

2.1表的分类

对于大多数企业级应用系统而言,数据库是整个应用系统的基石,因此数据库的表设计(也被称为schema设计)也就成为整个系统设计的重中之重。表设计的不好或者不合理,不仅会影响系统性能,而且会增加开发和集成的复杂性,甚至埋下隐患,最终会导致一系列的问题,例如数据不一致性问题等。概括而言,需要根据业务需求和系统功能,采用如下步骤设计表。

  1. 确定数据库类型。
  2. 判断表的类型,并据此构建表的列,确定表名。
  3. 设计表的主键以及表之间的关联关系。
  4. 优化设计,评估访问性能并根据查询模式添加索引。

应用系统往往涉及很多表,这些表的涉及存在先后顺序,而表之间也具有各种关联和依赖关系.因此,表的涉及并不是一蹴而就的,步骤2、3和4在大多数时候也不是清晰可分的,甚至整个设计也是一个循环迭代的过程,需要在多个步骤之间反复多次,以逐步的细化和优化。

基于所支持应用的不同,数据库可以划分为两大类,分别为面向分析型应用的 Online Analytical Processing (简称为OLAP)和面向事务型应用的 Online Transaction Processing (简称为OLTP)。OLAP用于 支持数据挖掘 、 统计报表 、 数据预测 等统计分析功能, 插入 、 更新 和 删除 这些写入操作较少,但是对于 数据读取 和 数据处理 的要求非常高。OLTP用于支持 事务处理 功能,需要全面满足操作的 原子性 (Atomicity)、 一致性 (Consistency)、 事务隔离性 (Isolation)和 持久性 (Durability),即所谓的ACID标准。

表设计的第一个步是根据业务需求,确定应用是 分析型 ,还是 事务型 ,从而确定数据库的类型是 OLAP ,还是 OLTP 。两种类型的数据库在设计理念和设计方法是完全不同的。本文主要讨论针对于OLTP的表设计,在下文中如果没有特别指出,那么所指的都是 OLTP 数据库。在大多数场景中,事务型应用也需要数据统计分析功能,但是通常并不会为此构建专门的 OLAP 数据库,而是基于现有的OLTP数据库支持这些统计分析功能。为此,需要数据库以 支持在线事务处理 为主,同时 辅助支持统计分析 功能。这种情况造成数据库的表往往可以划分为如下三类:

  • 业务表 ,其操作特点是 随机增 、 随机删 、 随机改 、 随机查 。业务表来源于需要应用系统处理的业务实体,业务实体所包含的基本属性和状态被称为 业务属性 ,其与业务表的列之间存在对应关系。
  • 日志表 ,其操作特点是 顺序增 、 不删 、 不改 、 多查 。日志表用于 记录原始日志或者历史数据 ,顾名思义,之所以被成为日志表,是因为这些日志和数据一旦生成,就不会被更改,也不会被删除。
  • 汇总表 ,其操作特点是 顺序增 、 不删 、 少改 、 多查 。对于数据统计分析功能,如果直接使用日志表无法满足性能要求,则可以引入汇总表。在本质上汇总表是以 预先汇总数据 和 存储汇总结果 为代价,来 提高数据统计分析功能的访问性能 。基于不同的业务场景和业务需求,既可以在将 数据插入日志表 后,实时地以 逐条方式插入或者更新汇总表 ,也可以 间隔一定周期 ,定时地以批量方式读取日志表中的数据并执行汇总操作,然后批量地将汇总结果插入汇报表。

2.2表的构建

对于业务表、日志表和汇总表三种不同类型的表,不仅所对应的功能不同,设计目标不同,而且表的构造过程也不尽相同。

业务表的目标是 减小冗余数据 ,以提高数据的 完整性 (Integrity)和 一致性 (Consistency),其构造过程如下所示。

  1. 归纳和抽象出需要持久化的业务实体 。业务实体是指在系统应用中需要依据业务规则/逻辑进行各种处理的对象,其中一部分业务实体需要 设计专门的表 ,以在数据库中存储相应的数据,而其他业务实体,或者衍生自需要持久化的业务实体,或者为临时性业务实体,因此无需为其设计表。
  2. 总结相应的业务属性 。不同于业实体,业务属性往往为基本的数据类型,例如 小数、整数、字符串 等。应用系统依据业务逻辑对业务实体执行一系列的处理,这些处理最终能够被分解为对于业务属性的增、删、改、查操作。
  3. 基于 业务实体 和 业务属性 , 设计对应的表和列 。表和表的列分别对应于业务实体和业务属性。根据功能需求,有时还会增加一些额外的列,例如created_time和status等,用于支持一些辅助性的处理功能。

冗余数据的字面意思是 多余的和不必要的数据 。冗余数据的一种 极端情况 是 重复数据 ,可能所在列名不同,甚至所在表也可能不同,但是在两列中对应的数据是完全相同的。冗余数据的另一种常见情况是 数据依赖 ,即一列数据可以由其他一列或者几列数据推导出来。例如商品表中的三列: 原价、现价和折扣 ,因为 现价=原价×折扣 ,所以其中任何一列都可以由其他两列计算而来,因此存在数据依赖,仅仅需要保留其中两列即可,具体去除那一列需要根据 业务需求 和 使用场景 来决定。此外,关系冗余也是一种常见的冗余数据,包括 部分依赖 和 传递依赖 。 部分依赖 是指在使用复合主键时非主键的列仅仅依赖于部分主键,而不是依赖于整个主键。 传递依赖 是指非主键的列 不直接 依赖于主键,而是依赖于其他非主键的列。为了消除冗余关系,需要根据具体情况,或者删除一些相关的列,或者 将冗余关系抽取出来独立建表 。无论属于那种数据冗余,都需要从业务逻辑和业务含义上来分析和判断,尝试更改一列数据,看看更改此列数据后,是否需要进一步地更改这个表或者其他表的列,以判断是否存在依赖此列的数据。对于业务表而言,一旦出现冗余数据,那么在更新部分冗余数据时,必需同时额外地更新冗余数据的其他部分,否则将会出现数据不一致问题。显而易见, 冗余数据将会大大增加数据更新的复杂性 ,所以必须在业务表中消除各种形式的冗余数据。

设计业务表 主键 时,采用如下原则。

  1. 建议优先使用 存在业务含义 的、 类型为整数 的并且 具有唯一性 的 一列或者多列 为主键,即现有的一列或者多列为整数类型并且 能够唯一地标识一行数据 ,那么就采用这一列或者多列做主键。如果主键为多列,那么也被称为复合主键。
  2. 如果不存在列满足设计原则1,那么建议 创建自增长主键 ,即设计由关键字 NOT NULL 和 AUTO_INCREMENT 修饰的整数列作为主键。

针对于业务表主键的设计原则,有如下几点说明。其一,之所以建议优先采用具有业务含义的主键,是因为可以获得更好的性能。 具有业务含义的列常常作为查询或者更新条件 ,作为主键可以 更加快速地定位数据在磁盘上的存储位置 。虽然自增长主键能够实现顺序插入,具有更好的插入性能,但是数据查询和数据更新的操作频率要远远大于数据插入操作, 减小查询和更新操作的时延 可以获得更好的数据库平均访问性能。其二,在一个表中没有业务含义的自增长主键,在其他表中就存在确定的业务含义,例如在如下代码片段中虽然member表的id是自增长主键,并没有业务意义,但是在表last_login中这个id(member_id)就具有了业务含义, 能够代表一个特定的member 。其三,如果是 复合主键 ,那么在复合主键列中 不同列的排列顺序需要仔细考量 ,一般依据查询模式,越经常作为查询条件的列,在主键中的位置越靠左( 越靠前 )。其四,ENUM、DATE、DATETIME、TIME和较短的CHAR这些类型所占的存储空间较小,在设计主键时可以等同于整型对待。其五,如果存在一列或者多列,其具有业务含义并且能够唯一标识一行数据,但不是整数( 通常为字符串 ),则有两种解决方案。方案一,如下代码片段中member表所示, email具有唯一性 ,但 不是整数 ,可以 采用自增长主键+唯一索引 的方式,其中如果为唯一索引为多列,那么在唯一索引中的排列顺序 依据复合主键的排列原则 来处理。方案二,类似于member2和email表,将多列中 非整数型的列 提取出来建立一个独立的表,假设表t1中存在三列能够唯一标识每行数据,分别为c1、c2和c3,其中c1为整数,c2和c3为VARCHAR类型,则从t1表中抽离两列c2和c3,分别建立两个新的字典表t2和t3,在新表t2和t3中采用自增长主键,并且在两个表中c2和c3所对应的列分别建立唯一索引,而在原表t1中采用t2表和t3表的主键t2_id和t3_id替换c2和c3列,并用c1和t2_id、t3_id三列做复合主键。

CREATE TABLE member (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ...

  email VARCHAR(63) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE last_login (
  member_id INT UNSIGNED NOT NULL,
  ...

  PRIMARY KEY (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE member2 (
  email_id INT UNSIGNED NOT NULL,
  .
6.3 节深度拆解:Hermes Agent 多 Agent 协同执行链路的 4 层设计逻辑 拆解 Hermes 多 Agent 协同的 4 层工程化设计(协议层、编排层、执行层、适配层),聚焦协议层的强约束消息 Schema 如何解决 Agent 间语义不一致问题,并提供 `soul.md` 配置、GLM-4.7Flash 结构化输出验证及 v0.13.0/v0.14.0 版本实操避坑指南 阅读详情

相关推荐

STM32基于软件模拟IIC进行AHT21B温湿度采集

通过学习软件IIC来进行实操AHT21B采集温湿度。

Liu-FE的博客 3700

excel wind插件使用_Python和wind的交互—在债券中的应用

个人认为学会了怎么用python和wind交互以及怎么用python处理Excel数据,就基本能替换日常很多琐碎的债券工作,还不用担心Excel某个值改变了导致整个报表都毁了。事实上在python调用wind函数和在Excel中调用函数一样简单,只要会基本的操作就够了。下面详细的介绍如何用python和wind进行交互。首先安装好python+pycharm+wind。因为python...

weixin_39863918的博客 5332

基于表面肌电信号sEMG的手势识别——以Ninapro DB1数据集使用CNN网络识别为例

本文实验采用公共数据集Ninapro。该数据集数据量大,采集的动作丰富,每个单位数据集里面包括表面肌电信号、加速度计、手部运动学和动态数据等,记录了67名完整受试者和11名进行了至少50 次手部运动的截肢者的数据。NinaproDB1数据集包括27名完整的受试者(7 名女性和20名男性,其中2个左手和25 个右手) 的肌电信号。信号采集频率为100Hz。

树下一朵云的博客 2296

告别第三方工具!用Windows自带蓝牙玩转BLE设备调试(保姆级图文教程)

本文详细介绍了如何利用Windows自带的蓝牙功能进行BLE设备调试,无需第三方工具。从硬件准备、系统配置到实战操作,提供保姆级图文教程,帮助开发者高效完成设备发现、数据交互等全流程调试工作。特别适合物联网开发者和智能硬件爱好者。

o5p6q的博客 735

软考中级-软件设计师 知识点整理(一篇就过了 建议收藏)

软考中级-软件设计师上午题知识点总结

片言可决讼,半纸能缚龙 17万+

20 Embedding 与向量检索:语义为什么能变成距离

Embedding 是 RAG 语义检索的数学基础。本文用工程视角讲清文本如何变成向量,为什么语义相近会表现为距离相近,Top-K 检索如何工作,以及在模型选择、同模型约束、精确匹配和召回评估上的常见坑。

老于的笔记的专栏 6204

如何通过 GitHub Actions 使用 SSH 自动化部署到阿里云 ECS 实例

通过 GitHub Actions 和 SSH 自动化部署,可以显著提高你的开发效率和部署的可靠性。本文介绍了如何生成 SSH 密钥、将公钥添加到阿里云 ECS 实例、将私钥添加到 GitHub Secrets,以及编写 GitHub Actions 工作流来实现自动化部署。希望这篇文章对你有所帮助!

吃面不喝汤的博客 1514

智能车图像处理21-进阶篇13--正入十字补线2

这篇文章主要的正入十字补线与1基本相同,主要防止图像中双十字的问题。

taiyuezyh的博客 2037

win10 跳过U8环境检测的IE WebControls.rar

win10 安装u8的时候 提示IE WebControls 不通过,这个工具可以欺骗u8的检查。 跳过U8环境检测的IE WebControls。提示导入失败后,重新检测通过。

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

visio安装包

Microsoft Office Visio 是一款专业的制图软件。利用经过更新的形状、协作工具和数据链接图表,创建专业级图表以简化复杂的信息。

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

上一篇: Mybatis代理对象生成
下一篇: Spring整合Mybatis及Junit
JavaShark
博客等级 码龄4年 184粉丝 438原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值