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

水深无声 2024-03-31 17:11 32阅读 0赞

#### MySQL表的增删改查 - 进阶1 ####

*  1、数据库约束
 *   *  1.1 约束类型
     *  1.2 NULL约束
     *  1.3 UNIQUE约束
     *  1.4 DEFAULT约束
     *  1.5 PRIMARY约束
     *  1.6 FOREIGN KEY外键约束
     *   *  1.6.1 语法
         *  1.6.2 工作原理
 *  2、表的设计
 *   *  2.1 一对一
     *  2.2 一对多
     *  2.3 多对多

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

## 1、数据库约束 ##

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

### 1.1 约束类型 ###

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

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

### 1.2 NULL约束 ###

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

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

![image-20220718221255639][]

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

![image-20220501112807064][]

当我们尝试插入空值的时候，就会报错  
![image-20220501112854658][]

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

![image-20220501113844845][]

### 1.3 UNIQUE约束 ###

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

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

![image-20220718222203680][]

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

![image-20220718222511496][]

那么当我们`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

![image-20220501115150108][]

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

![image-20220501115429753][]

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

### 1.5 PRIMARY约束 ###

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

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

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

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

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

![image-20220501115901154][]

注意事项 :

1.  对于一个表来说，只有一个列能被指定为主键
    
    ![image-20220501120015506][]
2.  设定为主键的，既不能为空，也不能重复
    
    ![image-20220501120805343][]
3.  对于主键，最典型的用法就是直接使用1、2、3、4递增的方法来进行表示
4.  由于主键必须要填 , 而且还不能重复 . MySQL 为我们提供了一个叫自增主键的方式 , 帮助我们自动生成主键的值
    
    用法：在主键后面加上`auto_increment`
    
    ![image-20220501121045132][]
    
    当我们设定好主键之后，此时插入的记录就可以不指定自增主键的值了(直接使用`null`表示即可)，交给`MySQL`自己分配即可
    
    ![image-20220501121334857][]
    
    自增主键也可以手动指定`id`，一旦指定之后，后续插入的数据的`id`就是从你指定的数字之后来排的
    
    ![image-20220501121950564][]
    
    > MySQL 要想进行自增 , 就要记录下来当前的 id 已经记录到哪里了 . 而且还要保证 , 自增之后必须是不重复的.
    > 
    > 那么 MySQL 里面简单粗暴的方法 , 就是直接记录当前自增主键里面的最大值 , 这样就能保证自增主键速度快还不重复 .
    > 
    > 虽然浪费了一定的存储资源 , 但是好处还是很多的 .
    > 
    > 对于这个浪费 , 其实只有在数据库里面有海量的数据里面 , 才会影响到正常的使用 .
    > 
    > 正常情况 , 数据库里面是基本没有那么多的数据的 .
    > 
    > 那么自增主键是要保证生成的 id 是唯一的 , 不重复的 . 但是如果数据库是分布式部署的(分布式的意思就是数据量太大 , 一台机器存不下 , 需要用多台设备来存储数据) , 这个时候自增主键就有可能出现问题
    > 
    > > MySQL服务器很容易就知道自己当前主机上 id 的最大值 , 但是有多台机器的话 , 就没办法保证了
    > > 
    > > ![image-20220718224306294][]
    > > 
    > > 举个栗子 : 结婚随份子 , 你有可能提前给新娘新郎了 , 但是没给写礼账的人 , 新郎新娘也忘记了告诉写礼账的人 , 这样他们三个的信息就没互通 , 是不知道他们三个之间的最大值的
    > > 
    > > 所以 , 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)\]][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`的

![image-20220501122518187][]

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

![image-20220718225449608][]

### 1.6 FOREIGN KEY外键约束 ###

#### 1.6.1 语法 ####

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

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

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

举个栗子：

> ![image-20220501212957543][]
> 
> 学生表依赖了班级表，那么我们就把学生表称为"子表"，班级表称为"父表"
> 
> 这种情况下就可以使用外键约束来描述这种关系
> 
> 通过这个例子，我们需要知道：
> 
> 每个学生都得有一个具体的班级，这个班级得存在！
> 
> 如果学生表这里出现一个记录，班级`id`是1000，此时这样的数据就是非法的

来看一个例子：

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

![image-20220501214136955][]

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

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

![image-20220501215151117][]

![image-20220501215203996][]

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

![image-20220501215628967][]

我们可以来插入数据了

![image-20220501215733078][]

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

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

> 在父表为空的情况下 , 直接往子表里面插入数据是会报错的 .
> 
> ![image-20220718230548955][]

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

![image-20220501220012936][]

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

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

![image-20220501220501614][]

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

那么我们再试两组数据

![image-20220501220737287][]

这两次插入，`classId`2和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)\]][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]

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

![image-20220501221030114][]

注：`a child row`代表子表

修改成功的案例：

![image-20220501221153322][]

> `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)\]

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

![image-20220501221644792][]

> `a parent row`：父表

那么正确写法是这样的：

![image-20220501221813602][]

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

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

![image-20220501221927293][]

#### 1.6.2 工作原理 ####

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

> 那么这里的查询操作，可能是一个成本较高的操作(比较耗时) . 如果查询操作触发了遍历表 , 这是十分低效的 . 但是查询触发了索引 , 这就相对快不少 .
> 
> 外键约束其实要求，父表中被依赖的这一列，必须要有索引，有了索引就能大大的提高查询速度
> 
> 那么这个索引，就是`class`表的`classId`这一列，需要是`primary key`或者`unique`(有了这俩约束的列，就会自动创建出索引了)

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

![image-20220502093433367][]

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

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

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

那这是怎么回事？

> 其实我们采取的办法，不是真正的删除，而是逻辑上的删除
> 
> 我们可以给商品表加一个列`isOk`，那么如果这一列值为1，就代表这是一条有效记录，如果这一列值为0，就代表这是一个无效记录(相当于被删除了)
> 
> 那么有一个问题，假如删除的话，这也不是真的删除啊。只不过是`isOk`这一列被存为了0 , 那不还是占据了硬盘空间的吗 ?
> 
> > 实际上，现在硬盘空间不值钱，存了就存了。其实目前人力成本是会更高的。

## 2、表的设计 ##

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

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

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

一个典型的场景：

> 学生管理系统：
> 
> 1.  表示学生的基本信息
> 2.  表示班级的基本信息
> 3.  表示学生学习的课程的基本信息

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

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

### 2.1 一对一 ###

举个栗子：

以学校的教务系统为例：

`student`表(学生的`id`，学生姓名，学生班级…)

`user`表(用户的账户，密码…)

那么造的句子就是：一个账户对应一个学生，一个学生也只有一个账户

那么在数据库中，怎么表示一对一的关系呢？

> 方法一：可以把这两个实体用一张表表示
> 
>     student(id,name,class,account,password);
> 
> 方法二：可以用两张表来表示，其中一张表就包含了令一个表的`id`
> 
> 根据这个关系，就可以随时找到某个账户对应的学生是谁，也能找到某个学生对应的账户
> 
> ![image-20220503195234662][]

### 2.2 一对多 ###

还是以学校的教务系统为例：

`student`表(学号，姓名…)

`class`表(班级编号，班级名称…)

那么造的句子就是：一个学生应该处于一个班级中，一个班级可以包含多个学生~

在数据库中，表示一对多的关系，也有两种典型方案：

> 方法一：在班级表当中，新增一列，表示这个班级里面的学生`id`组合
> 
> ![image-20220503203814927][]
> 
> 但是像`Redis`这样的数据库，就有数组类型，就可以考虑这种方法
> 
> 方法二：班级表不变，在学生表中新增一列`classId`
> 
> ![image-20220503204207785][]

### 2.3 多对多 ###

又是以学校的教务系统为例：

学生表(学号，姓名)

课程表(课程编号，课程名字)

学生和课程就是多对多的关系

那么造的句子就是：一个学生，可以选多个课程，一个课程同时也能包含多个学生(M个学生，可以选择N门课)

多对多的关系在数据库中只有一种办法

> 使用一个关联表，来表示两个实体之间的关系
> 
> ![image-20220503210504280][]

有的时候，为了更方便的表示/找到 实体之间的联系(尤其是比较复杂的场景)，我们还可以画`E-R`图(其实不咋用)，但是学校必考！

[65073f2a57364e6e8dcb487617e2182f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/b4f74ff5af2e404a9b57d3f20f36390e.png
[image-20220718221255639]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/2ad8bf5e5529486c8ecdefe26d9c7b9d.png
[image-20220501112807064]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e4ec38b612e8445186a815831de25768.png
[image-20220501112854658]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/3514c2a53a794afd940e2f48949ad6c9.png
[image-20220501113844845]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/96c0bcb59f05482297f18789ed81a22c.png
[image-20220718222203680]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/254add849f0b4d0986566b18ba5e60c9.png
[image-20220718222511496]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c19aa99034724bc5a72f2b897d1b3202.png
[image-20220501115150108]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/60efc7b29ce84f5f97bdfcff6f45e6fd.png
[image-20220501115429753]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/06cfceacf50d4d44be52eeb6c78a16cd.png
[image-20220501115901154]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/3d4e4fcba0f94d489a0aff3ffca10da6.png
[image-20220501120015506]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/22b7e48db51e43e1a0bfcf41558cb83e.png
[image-20220501120805343]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/719e76ad89004d0aa56a8792f819ce6f.png
[image-20220501121045132]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/9d21d89a8d3a4233a9044dccdfe06273.png
[image-20220501121334857]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c6ad7d6567ea4773baea923bcb35a086.png
[image-20220501121950564]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/9aac1c5c57a843259cd295fb5f9396bb.png
[image-20220718224306294]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fe654a057ed74c4888b7cec5f0241285.png
[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]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/de2a235622eb4bfe9713d068edd55012.png
[image-20220501122518187]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/36ccd74981a44d43b62f1d38109e4de1.png
[image-20220718225449608]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/dc6c4b439252433e8f8160928d30b48c.png
[image-20220501212957543]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ed1ffe3aa41e44e887727f66ee25a340.png
[image-20220501214136955]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/cdb0dc771ec04a08a30e9521f651fb50.png
[image-20220501215151117]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a1d95b2140a74dcdb8bc08f3204d4a3f.png
[image-20220501215203996]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f873e425ad374bd8a27f6765da2aeeb6.png
[image-20220501215628967]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f2a71caa11594606865c4126f599ad80.png
[image-20220501215733078]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4a7ff965de264b83bcff6586f1d88f91.png
[image-20220718230548955]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/42ccb5a70c564da19b5f0f1bc18c9bb3.png
[image-20220501220012936]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fedc73ef8040469083bf930ab0636473.png
[image-20220501220501614]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f5fdb0419016409e93f65506eb444b3d.png
[image-20220501220737287]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a6f59bab05b5417bb25fba9b92b3d238.png
[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]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f44dec7a5a7b4cc3a86739e6ca042637.png
[image-20220501221030114]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c4c7e8a33e404dc1a7dfbfac6940b687.png
[image-20220501221153322]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/b85b1c205f43478cb6f0f06f23c7283c.png
[image-20220501221644792]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/7d016da4d12c454e9eacea44c1084334.png
[image-20220501221813602]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c92be40acab5439f806879163164c1b8.png
[image-20220501221927293]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/117fd67a0af04a86b4a7e62c2ba2ef58.png
[image-20220502093433367]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a3283fc6b341496c94a3f4be19e6ca43.png
[image-20220503195234662]: https://jialebihaitao.oss-cn-beijing.aliyuncs.com/image-20220503195234662.png
[image-20220503203814927]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/acef93974d6d46a2a32298cc69f36f4b.png
[image-20220503204207785]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/b53c5002d4df4c74a47b07e5db9e0c21.png
[image-20220503210504280]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5e1b5dec2a244137b0b6bf0360682e94.png