MySQL约束、数据库设计、备份与还原-02

最新推荐文章于 2023-07-13 11:28:37 发布

hrsweb

最新推荐文章于 2023-07-13 11:28:37 发布

阅读量109

点赞数 1

分类专栏： mysql java SQL

本文链接：https://blog.csdn.net/weixin_45007945/article/details/103161816

版权

java 同时被 3 个专栏收录

7 篇文章 0 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

SQL

3 篇文章 0 订阅

订阅专栏

第一节约束

1.1.数据库约束的概述

1.1.1.约束的作用

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

1.1.2.约束的分类

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

1.2.主键约束

主键的特点：非空且唯一。

（1）在创建表时，添加主键

create table stu{
id int primary kep,
name varchar(20)
}

（2）删除主键

– 错误 alter table stu modify id int ;
alter table stu drop primary key;

（3）在创建表后，添加主键

– 方法一
alter table stu add primary key(id);
– 方法二
alert table stu modify id int primary key;

1.3.自动增长

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
（1）创建表时，添加自动增长

create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
)auto_increment=100; – 设置初始值

（2）删除自动增长

alter table stu modify id int;

（3）创建表后，添加自动增长

alert table stu modify id int auto_increment;

1.4.唯一约束

什么是唯一约束：表中某列不能出现重复值
（1）创建表时，添加唯一约束

create table stu(
id int,
phone_number varchar(20) unique – 添加唯一约束
);

注意mysql中，唯一约束限定的列的值可以有多个null

（2）删除唯一约束

alter table stu drop index phone_number;

（3）创建表后，添加唯一约束

alter table stu modify phone_number varchar(20) unique;

1.5.非空约束

什么是非空约束：某一列的值不能为零

（1）创建表时，添加非空约束

create table stu(
id int,
name varchar(20) not null – name不能为空
);

（2）删除非空约束

alter table stu modify name varchar(20);

（3）创建表后，添加非空约束

alter table stu modify name varchar(20) not null;

1.6.外键约束

foreign key,让表于表产生关系，从而保证数据的正确性。
语法：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

（1）创建表时，天剑外键约束

-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
    id int primary key auto_increment,
    dep_name varchar(20),  
    dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表 
create table employee(  
	id int primary key auto_increment, 
	name varchar(20),  
	age int,  
	dep_id int  -- 外键对应主表的主键
	-- 创建外键约束  
	constraint emp_depid_fk foreign key (dep_id) references department(id) 
);

（2）删除外键约束

–外键的名称：emp_depid_fk
alter table employee drop foreign key emp_depid_fk;

（3）创建表后，添加外键约束

alter table employee add constraint emp_depid_fk foreign key (dep_id) references department (id);

1.6.1.级联操作

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

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

演示：

-- 重新创建employee表，添加级联更新和级联删除  
create table employee(  
id int primary key auto_increment, 
name varchar(20),  
age int,  
dep_id int,  -- 外键对应主表的主键  
-- 创建外键约束  
constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade
);

第二节表与表之间的关系

2.1.表关系的概念

现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！

表与表之间的三种关系
一对多：最常用的关系部门和员工
多对多：学生选课表和学生表，一门课程可以有多个学生选择，一个学生选择多门课程
一对一：相对使用比较少。员工表简历表，公民表护照表

2.2.一对多

一对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

2.3.多对多

多对多（m:n）例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

2.4.一对一

一对一（1:1）在实际的开发中应用不多.因为一对一可以创建成一张表。
在这里插入图片描述

第三节数据库设计的范式

3.1.数据规范化

3.1.1.什么是范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式

3.1.2.三大范式

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

3.2.第一范式（1NF）

3.2.1.概念

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

3.2.2.班级表

在这里插入图片描述

3.3.第二范式（2NF）

3.3.1.概念

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

第二范式的特点：

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

3.3.2.示例

借书证表：
分为两张表：

3.4.第三范式（3NF）

3.4.1.概念

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

3.4.2.示例：学生信息表

在这里插入图片描述

存在转递关系
学号——>所在学院——>学院地点
拆分成两张表

3.4.3.三大范式小结

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