[MySQL]数据库的约束与表的设计

心已赠人 2024-04-01 17:19 64阅读 0赞

![8838d16318224d44b7907cba49d8a88d.gif][]

> ![c4304daa3a1846adadc000eea43efbf4.png][]专栏简介 :MySql数据库从入门到进阶.
> 
> ![1254c5b119a44cf0ae350489fb7736ad.png][]题目来源:leetcode,牛客,剑指offer.
> 
> ![4366a93aaa3142b5a0f16c7a2200339b.png][]创作目标:记录学习MySql学习历程
> 
> ![52a3437a932f4621b25533c330f7c6cb.png][]希望在提升自己的同时,帮助他人,,与大家一起共同进步,互相成长.
> 
> ![14aa2f0648ae44a299b8f9832af57d2c.png][]学历代表过去,能力代表现在,学习能力代表未来!

--------------------

#### 文章目录 ####

1.数据库约束

1.1 约束类型

1.2 null 约束

1.3 unique 唯一的约束

1.4 default 默认值约束

1.5 primary key 主键约束

1.6 primary key auto\_increment 自增主键

1.7 foreign key 外键约束

2.表的设计

2.1 一对一

2.2 一对多

2.3 多对多

--------------------

## 1.数据库约束 ##

*  **约束是关系型数据库提供的一种校验数据合法性的机制.**

### 1.1 约束类型 ###

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td><strong>约束类型</strong></td> 
   <td><strong>说明</strong></td> 
   <td><strong>示例</strong></td> 
  </tr> 
  <tr> 
   <td><strong>null</strong></td> 
   <td>使用not null 指定列不为空</td> 
   <td>name varchar(20) not null;</td> 
  </tr> 
  <tr> 
   <td><strong>unique</strong></td> 
   <td>指定列为唯一的,不重复的</td> 
   <td>name varchar(20) unique;</td> 
  </tr> 
  <tr> 
   <td><strong>default</strong></td> 
   <td>指定列为空时的默认值</td> 
   <td>name varchar(20) default "无名氏";</td> 
  </tr> 
  <tr> 
   <td><strong>主键约束</strong></td> 
   <td>not null 和unique 的结合</td> 
   <td>id int primary key;</td> 
  </tr> 
  <tr> 
   <td><strong>外键约束</strong></td> 
   <td>关联其他表的主键或唯一键</td> 
   <td>foreign key (字段名) references 主表(列)</td> 
  </tr> 
  <tr> 
   <td><strong>check约束</strong></td> 
   <td>保证列的值符合指定的条件</td> 
   <td>check(sex='男'or sex='女')</td> 
  </tr> 
 </tbody> 
</table>

--------------------

### 1.2 null 约束 ###

*  创建表时指定某列不为空.

**语法:**

create table 表名(字段1 类型 not null,字段2 类型...);

**示例:**

![ad30f8c8fe674b1fb70c77dfb2f8e650.png][]

--------------------

### 1.3 unique 唯一的约束 ###

*  创建表时指定某列为唯一的不重复的.

**语法:**

create table 表名(字段1 类型 unique,字段2 类型....);

**示例:**

![7a37187ab1674b9b9f0f5f960d40fe4a.png][]

--------------------

### 1.4 default 默认值约束 ###

*  指定列为空时的默认值

**语法:**

create table 表名(字段1 类型 default 默认值,字段2 类型....);

**示例:**

![bf78d7892bc149baaad83d10622e8d14.png][]

--------------------

### 1.5 primary key 主键约束 ###

*  创建表时指定列有唯一确定的标识符

**语法:**

create table 表名(字段1 类型 parmary key,字段2 类型...);

**示例:**

学生id 不为null 且不能重复.

![024dd6f3b5ac427190f89e3b5cc6b936.png][]

--------------------

### **1.6 primary key auto\_increment 自增主键** ###

*  对于整数类型的主键, 常搭配自增auto\_increment变为自增主键, 插入字段不给值时, 使用最 大值+1.

**语法:**

create table 表名(字段1 int primary key auto_increment,字段2 类型...);

**示例:**

![f8a9f57ce34f47b39a387bb05f87f70f.png][]

*  **分布式系统中唯一id生成算法**

对于较小的数据量, 所有的数据都在一台MySQL服务器上存储,此时自增主键可以很好的工作, 但如果涉及的数据量较为庞大, 需要将数据部署到多台MySQL服务器上, 这时再插入数据就无法保证自增主键的唯一性.因此MySQL内部采用分布式系统中唯一id生成算法.

**实现公式:**

*  时间戳+主机编号+随机因子 结合这三部分就可以得到一个全局唯一的id.

![438bc46e07d54320b17eb5e1314cf56e.png][]

--------------------

### 1.7 foreign key 外键约束 ###

*  MySQL外键是用来在两个表之间建立连接,可以是一列或多列,一个表可以有多个外键.

>  *  外键是本表的一个字段, 不是本表的主键, 但对于另一个表的主键.
>  *  定义外键之后不允许删除另一个表中具有关联关系的行.
>  *  主表(父表): 相关联字段中主键所在的表就是主表.
>  *  从表(子表): 相关联字段中外键所在的表就是从表.

**语法:**

*  **添加外键约束**

(constraint 外键名) foreign key (字段名1),(字段名2..) reference 主表名(主键列1,主键列2..)

*  **修改表时添加外键约束**

alter table 表名 add (constraint 外键名) foreign key (列名) references 主表名(列名);

*  **删除外键约束**

alter table 表名 drop forigen key (外键名);

**示例:**

*  MySQL表中, 没有默认对两张有关联的表提供检查机制, 例如:下图中插入非法班级, 不会报错. 因此需要引入外键约束.

![643c2eb866d34a688b36c4a917fb55cc.png][]

*  加入外键约束之后再插入非法数据就会报错.

![355b9b781c95404b81ba397b15fb3398.png][]

![6a0d258f53644b0db55adcfeb9055cc3.png][]

*  非法修改已插入的数据也会报错.

![4552b0ef7da844beb830534fb0a5257b.png][]

**Tips:**

>  *  父表必须已存在数据库中或是当前正在创建的表.
>  *  父表中的主键不能出现空值, 但外键可以.所以通常用primary key来约束主键.
>  *  外键中列是数目和数据类型必须和父表的主键一一对应.
>  *  父表限制子表的插入,字表也约束父表的删除.(互相制约)

*  **父表中元素的删除:**

实际应用中删除父表中数据的方式通常是逻辑删除. 当一个商品下架之后, 为了让客户可以查看到历史订单, 所以不能直接删除商品信息. 通常需要在建表之初预留商品下架的字段, 商品下架之后, 给商品下架的字段标记赋值达到伪删除的效果.

![9b372c2f5631476f9fd3283daa17dc61.png][]

--------------------

## 2.表的设计 ##

基本思路:

> 1.  先明确实体
> 2.  再明确实体之间的联系
> 3.  根据上述内容, 嵌套到下面的公式中, 就可做出表.

示例:

粗略设计一个学生教务管理系统.

> 1.  确定实体为: 学生, 课程, 学生账号, 学生班级
> 2.  学生与课程之间存在关系, 学生与账号之间存在关系, 学生与班级之间存在关系.
> 3.  学生与课程:多对多, 学生与账号:一对一, 学生与班级:一对多.

--------------------

### 2.1 一对一 ###

一个人只能有一个身份证号码, 一个身份证号码只对应一个人.

![2c0dd8e3e2a64b9b8720bfc9120c2244.png][]

--------------------

### 2.2 一对多 ###

一个班级有多个学生, 一个学生只对应一个班级.

![c8d29fd98511423a9969e2b7fd020ce6.png][]

![a7d7a604fb7b47aea8ff8d10c2f28282.png][]

--------------------

### 2.3 多对多 ###

一个学生可以选择多个课程, 一个课程也可以被多个学生选择.

![eff35004d417432684ca7d4b836245fb.png][]

![a498b9da279e428e88b5539c348c271f.png][]

--------------------

![abd5f229cb134f7bb9bafb4726753ac7.jpeg][]

--------------------

##  ##

[8838d16318224d44b7907cba49d8a88d.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b752879655534217a657195a9ca15744.gif
[c4304daa3a1846adadc000eea43efbf4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3f7ea151b23a43409b71add7fa49f053.png
[1254c5b119a44cf0ae350489fb7736ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/530ab6f75f1743f098b7e4ef4a9627cd.png
[4366a93aaa3142b5a0f16c7a2200339b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0708ed44063e4ac684f0fec074efd2f5.png
[52a3437a932f4621b25533c330f7c6cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a780af49745d46999eb67edfd6cf8291.png
[14aa2f0648ae44a299b8f9832af57d2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/13b8038f1ba243fb8a18d7d913081c2a.png
[ad30f8c8fe674b1fb70c77dfb2f8e650.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/3b5ebea1496645e7bfe1d1cd849082e6.png
[7a37187ab1674b9b9f0f5f960d40fe4a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c6048783069f4b2b8aee92a67b5e73a8.png
[bf78d7892bc149baaad83d10622e8d14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/b9d7a787882d4c6785aba7ee9512c764.png
[024dd6f3b5ac427190f89e3b5cc6b936.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/5d3cd220f94545da939f54ca64431511.png
[f8a9f57ce34f47b39a387bb05f87f70f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d36271cd93fe48d9ae717e72bc9671ee.png
[438bc46e07d54320b17eb5e1314cf56e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/63bf7c4ec190409f80b48dd3bdc01a21.png
[643c2eb866d34a688b36c4a917fb55cc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/f344b7e0a3904c79a55973331e62115b.png
[355b9b781c95404b81ba397b15fb3398.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/c89a165e90b24be187f59823259b5acc.png
[6a0d258f53644b0db55adcfeb9055cc3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/a235250bd22542389790a9b8e797a663.png
[4552b0ef7da844beb830534fb0a5257b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/33b2dac1ea4843c2bed079b95940be8d.png
[9b372c2f5631476f9fd3283daa17dc61.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/2a30bd1f1c31429fa9196a433758f7f5.png
[2c0dd8e3e2a64b9b8720bfc9120c2244.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/61eba49e3791477a9845f668b00e4fe7.png
[c8d29fd98511423a9969e2b7fd020ce6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/77aa9943b78244899c68cbeecf09dee6.png
[a7d7a604fb7b47aea8ff8d10c2f28282.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4407d5a5f9504bb28dca3a470f56e1e0.png
[eff35004d417432684ca7d4b836245fb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/dab0b5d46816434f9af9cdded59bf831.png
[a498b9da279e428e88b5539c348c271f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/8963d0d8a2564888aa901eb8de4f708d.png
[abd5f229cb134f7bb9bafb4726753ac7.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/ca21749a2ff14453a96138bb98986011.jpeg