MySQL约束、设计

 

文章目录

• • 数据库约束
  • • • 数据库约束的概述
      • 主键约束
      • 唯一约束
      • 非空约束
      • 检查约束 -- mysql不支持
      • 外键约束
      • 数据约束小结
  • 表与表之间的关系
  • 数据库设计
  • • • 数据规范化
      • 第一范式1NF
      • 第二范式2NF
      • 第三范式3NF
      • 三大范式小结

MySQL基础篇可转至： https://blog.csdn.net/weixin_57780057/article/details/124682555?spm=1001.2014.3001.5501

 

数据库约束

---

数据库约束的概述

约束的作用

• 对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束种类：

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check 注：mysql 不支持

主键约束

• 用来唯一标识数据库中的每一条记录；主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

创建主键

主键关键字： primary key
主键的特点：

1. 非空 not null
2. 唯一

创建主键方式：

1. 在创建表的时候给字段添加主键

• 字段名 字段类型 PRIMARY KEY

2. 在已有表中添加主键

• ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

主键操作

1. 删除主键

• alter table 表名 drop primary key;

2. 添加主键
   alter table 表名 add primary key(字段名);

3. 主键自增

• 1. AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型) ；默认地AUTO_INCREMENT的开始值是 1
• 2. CREATE TABLE 表名(
     列名 int primary key AUTO_INCREMENT
     ) AUTO_INCREMENT=起始值;
• 3. 创建好以后修改起始值
     ALTER TABLE 表名 AUTO_INCREMENT=起始值;
     alter table 表名 modify 字段名 int auto_increment; – 添加
     alter table 表名 modify 字段名 int; – 删除
• 4. DELETE：删除所有的记录之后，自增长没有影响。
     TRUNCATE：删除以后，自增长又重新开始。

 create table student 
 (sname char(20), 
 ssex char(20),
 ssno int
 );

desc student;
alter table student add primary key(ssno);

alter table student auto_increment = 20220001; -- 从该值自增

唯一约束

• 设置唯一约束,表中某一列不能出现重复的值

创建表时：

• 字段名 字段类型 UNIQUE

已有表：

• alter table 表名 add unique 约束名（字段); – 添加
• alter table 表名 drop key 约束名; – 删除

alter table student add unique st_u (sname);
alter table student drop key st_u;

非空约束

• 设置非空约束：某一列不能为 null。

创建表时：

• 字段名 字段类型 NOT NULL

已有表：

• alter table 表名 modify 字段名 数据类型 not null; – 添加
• alter table 表名 modify 字段名 数据类型 null; – 删除

 alter table student modify sname char(10) not null;	-- 添加

 alter table student modify sname char(10) null; -- 删除

默认值

创建表：

• 字段名 字段类型 DEFAULT 默认值

已有表：

• alter table 表名 modify 列名 类型 default 默认值;

1. 主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列。
2. 自增长只能用在主键上
   

检查约束 – mysql不支持

创建表：

• 字段名 字段类型 check(条件)

已有表：

• alter table 表名 add constraint 约束名 check (字段名和条件)

外键约束

• 在从表中与主表主键对应的那一列
  主表： 一方，用来约束别人的表
  从表： 多方，被别人约束的表

新建表时增加外键：

• [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

已有表增加外键：

• ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

删除外键

• ALTER TABLE 从表 drop foreign key 外键名称;

外键的级联

• 在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

级联操作语法	描述
ON UPDATE CASCADE	级联更新，只能是创建表的时候创建级联关系。更新主表中的主键，从表中的外键列也自动同步更新
ON DELETE CASCADE	级联删除

create table sc (ssno int, score int);
desc sc;
alter table sc add cno int;
desc sc;
alter table sc add primary key (ssno, cno);
alter table sc add constraint foreign key (ssno) references student (ssno);
alter table sc add constraint sc_1 foreign key (ssno) references student (ssno) on delete cascade on update cascade;
desc sc;

数据约束小结

在已有表添加约束：

• ALTER TABLE 表名 WITH NOCHECK ADD CONSTRAINT 约束名 约束类型 具体的约束说明。

约束名	关键字	说明
主键	primary key	唯一; 非空
默认	default	如果一列没有值，使用默认值
非空	not null	这一列必须有值
唯一	unique	这一列不能有重复值
外键	foreign key	主表中主键列，在从表中外键列

-- 仅仅示范作用~
create table sc
(sno int  primary key,
cno int primary key,
score int not null default 100,
id int not null unique;
foreign key (sno) references student(sno),
foreign key (cno) references course(cno)
);

表与表之间的关系

---

表与表的关系	关系的维护
一对多	主外键的关系
多对多	中间表，两个一对多
一对一	1) 特殊一对多，从表中的外键设置为唯一 2) 从表中的主键又是

一对多

• 一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

多对多

• 多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

一对一

• 一对一（1:1） 在实际的开发中应用不多.因为一对一可以创建成一张表

一对一的建表原则	说明
外键唯一	主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE
外键是主键	主表的主键和从表的主键，形成主外键关系

数据库设计

---

数据规范化

范式

规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

第一范式1NF

数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。

第二范式2NF

在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。
所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。

• 比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

特点：

1. 一张表只描述一件事情。
2. 表中的每一列都完全依赖于主键

概念：

1. 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
   例如：学号–>姓名。 （学号，课程名称） --> 分数
2. 完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
   例如：（学号，课程名称） --> 分数
3. 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
   例如：（学号，课程名称） – > 姓名
4. 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
   例如：学号–>系名，系名–>系主任
5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
   例如：该表中码为：（学号，课程名称）
   主属性：码属性组中的所有属性
   非主属性：除过码属性组的属性

第三范式3NF

在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。
简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则 C 传递依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y

三大范式小结

范式	特点
1NF	原子性：表中每列不可再拆分。
2NF	不产生局部依赖，一张表只描述一件事情
3NF	不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。