数据库概论之MySQL表的增删改查 - 进阶版本1

加勒比海涛

已于 2022-12-12 22:53:38 修改

阅读量1.1k

点赞数 9

分类专栏：数据库文章标签：数据库 mysql java

于 2022-12-10 10:41:46 首次发布

本文链接：https://blog.csdn.net/m0_53117341/article/details/128262596

版权

数据库专栏收录该内容

13 篇文章 2 订阅

订阅专栏

MySQL表的增删改查 - 进阶1

1、数据库约束
2、表的设计

大家好，已经好久没更新了 , 学校的学业有点忙 , 没有额外的精力去进行更新了 , 假期开始了 , 我们也要开始努力了
数据库相对来说难度不是那么高，大家只要勤加练习、熟记语法，我相信学好数据库不是什么问题，博主会从0剖析，逐步讲解数据库的知识点，并且会举很多实例。最重要的是，博主不会采用软件，使用最原始的方式 -> 命令行来讲解，这样讲解的好处是逐个语句进行书写，不会造成读者思路跟不上的问题！

1、数据库约束

约束就是数据库在使用的时候，对于里面存储的数据提出的限制和要求，程序员可以通过约束来对数据进行更好的校验~

1.1 约束类型

约束类型	说明
NOT NULL	指示某列不能存储 `NULL` 值。
UNIQUE	保证某列的每行必须有唯一的值。
DEFAULT	规定没有给列赋值时的默认值。
PRIMARY KEY	`NOT NULL` 和 `UNIQUE` 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY	保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK	保证列中的值符合指定的条件。对于`MySQL`数据库，对`CHECK`子句进行分析，但是忽略`CHECK`子句。

以上这些约束，都是针对于某个列单独设置的。只是针对这个列来说是这样的，不同的列之间没有影响

1.2 NULL约束

NOT NULL：指示某列不能存储 NULL 值。 (数据不能为空)

正常情况下 , 一个表的每个字段是允许为空的

当我们创建表的时候 , 指定某一列为 not null 的时候 , 我们就不能插入空值了

当我们尝试插入空值的时候，就会报错

NOT NULL还可以同时给多个列设置，不仅仅是一个列

1.3 UNIQUE约束

UNIQUE ：保证某列的每行必须有唯一的值。(数据唯一，插入的数据不能重复，是针对于列来说的)

默认情况下 , 表里面的数据是可以重复的

当我们指定某一列为 unique 的话 , 插入重复的值就会报错

那么当我们id都输入成1的时候，我们发现报错了。看一下错误信息：

ERROR 1062 (23000): Duplicate entry '1' for key 'id'

Duplicate：重复的

entry：条目(记录)

意思是：重复记录了值为1的id

我们之前就见过entry，Map entrySet，此处的entry也是条目的意思。

Map里面存的是键值对，Map又没有实现Interable接口，如果想遍历一个Map就需要先把Map给转成Set，Set里面的元素就叫做一个entry，就包含一个键和一个值

那么MySQL怎么发现新输入的数据是和之前的数据是重复的呢？其实这种这样的插入过程，是和之前学过的二叉树的插入过程是一样的，这里面的内容也和后面要讲的索引是相关联的。之后 , 我们再详细讲解 .

1.4 DEFAULT约束

DEFAULT :设定默认值。

默认的默认值是 NULL

可以通过 default 约束来修改这里的默认值的取值 .

我们仍然是只插入了id这一列，但是name使用的是我们的设置的默认值匿名人士

1.5 PRIMARY约束

PRIMARY KEY ：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

主键约束：相当于数据的唯一身份标识，类似于身份证号、学号

不能为空 : 相当于 not null
不能重复 : 相当于 unique
一个表里面只能有一个主键

这个是我们日常开发最常用的约束！

创建表的时候，很多时候都需要指定主键~

注意事项 :

对于一个表来说，只有一个列能被指定为主键
设定为主键的，既不能为空，也不能重复
对于主键，最典型的用法就是直接使用1、2、3、4递增的方法来进行表示
由于主键必须要填 , 而且还不能重复 . MySQL 为我们提供了一个叫自增主键的方式 , 帮助我们自动生成主键的值

用法：在主键后面加上auto_increment

当我们设定好主键之后，此时插入的记录就可以不指定自增主键的值了(直接使用null表示即可)，交给MySQL自己分配即可

自增主键也可以手动指定id，一旦指定之后，后续插入的数据的id就是从你指定的数字之后来排的
MySQL 要想进行自增 , 就要记录下来当前的 id 已经记录到哪里了 . 而且还要保证 , 自增之后必须是不重复的.

那么 MySQL 里面简单粗暴的方法 , 就是直接记录当前自增主键里面的最大值 , 这样就能保证自增主键速度快还不重复 .

虽然浪费了一定的存储资源 , 但是好处还是很多的 .

对于这个浪费 , 其实只有在数据库里面有海量的数据里面 , 才会影响到正常的使用 .

正常情况 , 数据库里面是基本没有那么多的数据的 .

那么自增主键是要保证生成的 id 是唯一的 , 不重复的 . 但是如果数据库是分布式部署的(分布式的意思就是数据量太大 , 一台机器存不下 , 需要用多台设备来存储数据) , 这个时候自增主键就有可能出现问题

MySQL服务器很容易就知道自己当前主机上 id 的最大值 , 但是有多台机器的话 , 就没办法保证了

举个栗子 : 结婚随份子 , 你有可能提前给新娘新郎了 , 但是没给写礼账的人 , 新郎新娘也忘记了告诉写礼账的人 , 这样他们三个的信息就没互通 , 是不知道他们三个之间的最大值的

所以 , MySQL 生成自增主键在分布式系统上就难以满足需求了

程序员们又想到了一个办法 :
```
唯一id = 时间戳(ms) + 机房编号/机器编号 + 随机因子
//这里面的 + 代表字符串拼接,不是算数运算
```
未来我们见到的分布式唯一 id 算法 , 就长这个样子 .

往数据库中不断插入数据 , 插入数据的时间不同 , 时间戳也就不同 .

假如说 , 恰巧在同一时间插入的 , 我们还可以通过机房编号/机器编号来区分.

最后 , 再加上随机因子 , 这样重复的概率非常非常小了

那就真的一模一样恰巧了 , 那么我们怎么办?

错误思路 :

再去生成一次不就得了

那么你怎么知道这次生成的唯一id之前没生成过 , 你不得需要把之前生成的唯一id存储起来 , 那么查找不又浪费时间了吗.

正确思路 :

就这么地了! 因为一模一趟的概率真的是太小太小了 , 几乎近似于0了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hDvK0Kpx-1670639701725)(https://jialebihaitao.oss-cn-beijing.aliyuncs.com/img/%E5%B0%8F%E7%8C%AB%E5%88%86%E9%9A%94%E7%AC%A6.png)]

注意：我们想要使用自增主键，就必须确保想操作的列必须是可以输入null的，即不能把这个列的约束设置成not null的

但是个人测试 , 这样子其实是可以的

1.6 FOREIGN KEY外键约束

1.6.1 语法

foreign key(字段名) references 主表(列)

外键约束其实就是针对两张表进行了关联。

外键约束，描述的就是两张表的两个列之间的"依赖关系"，子表依赖于父表(子表引用自父表)，要求子表当中对应记录得在父表当中存在

举个栗子：

学生表依赖了班级表，那么我们就把学生表称为"子表"，班级表称为"父表"

这种情况下就可以使用外键约束来描述这种关系

通过这个例子，我们需要知道：

每个学生都得有一个具体的班级，这个班级得存在！

如果学生表这里出现一个记录，班级id是1000，此时这样的数据就是非法的

来看一个例子：

首先，我们创建一个班级表，设定班级编号为主码约束。

接下来构造一个学生表，来指定一下外键约束

create table student (studentId int primary key auto_increment,name varchar(20),classId int,foreign key(classId) references class(classId));
-- student表里面的classId 引用了 class表里面的classId列

我们接下来看两个表的结构

我们可以来插入数据了

此时我们可以发现，这条记录中指定的classId为1，在class表中存在的话，可以插入

在插入之前，MySQL会先拿着这个classId去class表里面查询一下，看看是否存在，存在才插入成功

在父表为空的情况下 , 直接往子表里面插入数据是会报错的 .

那么接下来，我们尝试插入一下classId为100的情况

我们发现，插入失败~ 错误信息如下

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sql0423`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))

这条记录，指定的classId为100，在class表中不存在，不能插入成功

那么我们再试两组数据

这两次插入，classId2和3均在class表的classId列中存在，因此就能插入成功

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0m1DPVMH-1670639701729)(https://jialebihaitao.oss-cn-beijing.aliyuncs.com/%E5%B0%8F%E7%8C%AB%E5%88%86%E9%9A%94%E7%AC%A6.png)]

那么不仅仅是新增的时候要考虑到外键约束，新增成功的数据如果进行修改，也是会失败的

注：a child row代表子表

修改成功的案例：

student表中的classId是可以重复存在的，只要在class表里面存在即可

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZyCIY1Ec-1670639701729)(https://jialebihaitao.oss-cn-beijing.aliyuncs.com/%E5%8D%9A%E5%AE%A2%E5%88%86%E9%9A%94%E7%AC%A6-%E6%9C%88%E4%BA%AE.png)]

外键约束，同样也约束着父表，当父表中的某个记录被子表依赖着的时候，此时尝试进行修改或者删除，也会出现错误

a parent row ：父表

那么正确写法是这样的：

目前班级1里面是没有同学的，即没有被子表依赖，就可以修改或者删除。

注意:直接删表也是不行的

1.6.2 工作原理

在子表中插入新的记录的时候，就会先根据对应的值，在父表中先查询，查询到之后才能够执行后续的插入

那么这里的查询操作，可能是一个成本较高的操作(比较耗时) . 如果查询操作触发了遍历表 , 这是十分低效的 . 但是查询触发了索引 , 这就相对快不少 .

外键约束其实要求，父表中被依赖的这一列，必须要有索引，有了索引就能大大的提高查询速度

那么这个索引，就是class表的classId这一列，需要是primary key或者unique(有了这俩约束的列，就会自动创建出索引了)

那么我们想象一个场景：淘宝购物

按照上面描述的外键约束的关系，就可以认为商品表就是父表，订单表就是子表，那么订单表中的商品id需要在商品表的商品id当中存在

那么我今天买了个衣服，穿着感觉不错，一段时间之后，准备再次入手，却发现这个商品下架了(也就是被商品表中删除了)。

我们刚才不是说外键约束，一旦约定好了外键约束关系，此时父表当中有关联的数据不是不能随便删除吗？(如果某个记录被子表依赖了，此时就无法删除了。)

那这是怎么回事？

其实我们采取的办法，不是真正的删除，而是逻辑上的删除

我们可以给商品表加一个列isOk，那么如果这一列值为1，就代表这是一条有效记录，如果这一列值为0，就代表这是一个无效记录(相当于被删除了)

那么有一个问题，假如删除的话，这也不是真的删除啊。只不过是isOk这一列被存为了0 , 那不还是占据了硬盘空间的吗 ?

实际上，现在硬盘空间不值钱，存了就存了。其实目前人力成本是会更高的。

2、表的设计

所谓了"数据库设计"、"表的设计"其实就是根据实际的问题场景，把表给创建出来了

那么给你一个问题场景，如何设计数据库？如何设计表？

一个好方法就是：先找出这个数据库场景当中涉及到的"实体"(对象)，再找出它们之间的关系

一个典型的场景：

学生管理系统：

表示学生的基本信息
表示班级的基本信息
表示学生学习的课程的基本信息

对于咱们找到的实体来说，就需要创建对应的表来表示相关信息(那么每个实体表，里面的基本信息以及需求都是很好确定的)，但是很多时候实体和实体之间并不是孤立的，而是存在对应关系。那么这样的对应关系，也需要体现在表中(实体之间的关系，这个是隐含的，需要我们挖掘出来，这里实体之间的不同的关系，会对表的设计产生影响)

其实分析实体之间的关系就是造句子