MySQL数据库约束和设计

数据库约束

数据库约束是对表中数据进行一定规则或条件进行约束，其可以更好的保证我们添加数据的准确性和可靠性，其有非空约束、默认约束、唯一约束、外键约束、check约束等等

not null非空约束

例如我们创建一个学生表，并且学生的姓名不可以为空

create table student(
       id int,
	   name varchar(20) not null);//不可以为空

此时name这一列是不可以为空的，如果我们添加null的，此时就会报错

insert into student(id,name) values(1,null);

DEFALUT 默认约束

如果有一列没有添加数据，我们可以给它一个默认值
例如：有一个学生表，如果没有填写年龄，我们就默认为18，并且名字不可以为空

create table student(
       id int,
	   name varchar(20) not null,
	   age int default 18);//默认为18

此时的表结构如下,一些虽然没有默认值，但是其默认为null

insert into student(id,name) values (1,'张三');

//当我们手动将其age初始化为null时候，其就不会用默认值了
insert into student(id,name,age) values (2,'李四',null);

UNIQUE 唯⼀约束

就是此列数据不可以重复，只允许出现一次
例如：创建一个学生表，id是不可以重复，name不可以为空，age默认为18

--此时我们可以创建学生id是唯一的
create table student(
       id int unique,
	   name varchar(20) not null,
	   age int default 18);
desc student;

insert into student(id,name) values (1,'张三');

此时我们已经创建过id为1的了，如果在创建此时会爆出，id重复

PRIMARY KEY主键约束

其可以用来标识数据库中的记录
相当于not null和unique结合，因为其主键必须是唯一的，且不能为null
例如：我们写个学生表，id这一列非空且唯一约束

-- 创建一个学生表，id列唯一且不为空
use java;
create table student(
       id int not null unique,
	   name varchar(20) not null);
desc student;

此时我们id为not null非空 和 unique唯一约束，其表中为primary key外键约束

insert into student(id,name) values(1,'张三');
insert into student(id,name) values(1,'李四');

此时就会报错，因为其id是不可以重复的

但是通常我们还是使用primary key，并且可以添加自增主键让数据库维护主键的值

create table student(
			 id bigint primary key auto_increment,
			 name varchar(20) not null);

insert into student(id,name) values(null,'张三');
select id,name from student;

此时我们添加了自增主键，我们是可以添加null，此时他会在原本最大的id基础上进行+1，反之如果我们创建表的时候未添加auto_increment自增主键，我们如果添加id为空，则会报错

insert into student(id,name) values(3,'张三');

insert into student(id,name) values(null,'张三');

自增主键其中的id是不一定连续的，它的自增只会在原本最大的id基础上进行+1

此时我们已经有了id为3的，我们在添加就会出错

但是我们要注意其表中是不可以有多个主键

我们虽然不可以有多个主键，但我们可以一个主键指定多个列，主键的冲突是由上面多个列决定即复合主键

create table student(
       id int ,
	   name varchar(20), 
	   primary key(id,name));
desc student;

insert into student(id,name) values(1,'张三'),(2,'张三'),(1,'李四');

此时我们的主键是复合主键，只有当id和name都相同的时候，其才会出现主键错误

此时我们已经有了id = 1,name = 张三的这个数据，如果我们再一次进行添加，其就会出现异常

FOREIGN KEY 外键约束

1.两个表有之间有联系，这两个表可以称为主表和从表，
2.主表关联的列必须是主键约束或唯一约束
3.从表在进行添加数据时，其与主表的外键列必须是主键约束或唯一约束或为null
例如：一个学生表和一个班级表，其中必然有联系，一个一个学生对应一个班级，并且一个班级中有许多的学生，此时我们要将其两个表有关联，就要用到外键约束

use java;
drop table if exists student,class;

create table class(
       id bigint primary key auto_increment,
	   name varchar(20));
 -- 添加一些班级数据
insert into class(name) values ('java1'),('java2'),('java3'),('C++1'); 

-- 创建学生表
create table student(
			 id bigint primary key auto_increment,
			 name varchar(20),
			 age int default 18,
			 class_id bigint,
			 -- 外键,并且此时的变量的属性一样
			 foreign key(class_id) references class(id));
desc student;

此时我们将学生表和班级表进行了外键连接，因此我们在对学生进行添加数据的时候，其班级class_id这一列，在class中id这一列存在或者为null,也就表示此学生还没有分配班级

-- 给学生表添加数据
insert into student(name,class_id) values ('张三',1),('李四',2);

但是我们要注意此时我们是不可以添加学生表信息时候，此class表中class_id不存在的情况
例如我们添加class中不存在的id时候，其就因为外键约束而报错

但是我们是可以添加其外键列的值为null的，此处可以表示为该学生还未分配班级

insert into student(name,class_id) values('张三',null);

有了这个外键连接，我们在删除主表的数据的时候，必须确保从表没有数据用到了这个列的数据，否则会报错

delete from class where name = 'java3';
select id,name from class;

由于我们的学生表并没有学生在其java3班，因此我们这里可以随意删除

但是如果我们删除java1班就不行，此时张三是在java1班的，此时就会报错

我们在删除主表的时候，必须要先删除从表，使其外键并没有被使用，这样才可以删除主表

此时我们直接删除主表，其会因为还有学生表外键相连，因此不可以直接删除主表，需要把与其主表外键相连的从表，此时我们要先删除student表，在删除class表

-- 先删除从表，再删除主表
drop table student;
drop table class;

此时这样就可以删除了

CHECK约束

可以用于一个列或多个列，用于限制列中可以接收的数据值，从而使数据更加符合我们的要求，从8.0.16版本才开始全面支持这个的约束
例如：创建一个学生表其性别只可以是男和女

create table student(
       id bigint primary key auto_increment,
	   name varchar(20) not null,
	   gender char(1),
	   -- 此时的gender只有男和女这两种选择
	   check( gender = '男' or gender = '女'));

此时我们插入gender 为男或女都可以

insert into student(name,gender) values('张三','男'),('李四','女');
select id,name,gender from student;

但是如果我们填写的既不是男也不是女，其就会报错，会出现我们填写的数据不符合我们第一开始创建表的规则（这可以是男或女这两种性别）

--此时就会报错
insert into student(name,gender) values('赵六','1');

例：建新表，c1的值不能为0，c2的值必须⼤于0，c3的值不⼩于c2

create table temp(
			 c1 int check(c1 <> 0),
			 c2 int check(c2 > 0),
			 c3 int, 
			 check(c3 >= c2)); 

 -- c1不可以为0
insert into temp values(0,2,3);
# 不符合c3 >= c2
insert into temp values(1,3,2);
# 不符合c2 > 0
insert into temp values(1,0,3);

这三个结果都不符合要求，都会添加失败
添加符合要求的数据

-- 符合要求
insert into temp values(1,2,2);
select c1,c2,c3 from temp;

数据库设计

范式
数据库的范式是一组规则，在设计关系型数据库时候，我们要遵从一些规范要求，设计出合理的数据库
关系型数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF），越高的范式其对数据有更好的约束性，但是也并不是越高越好，因此我们日常使用通常只需满足到第三范式就行

第一范式

数据库表的每⼀列都是不可分割的原⼦数据项，⽽不能是集合，数组，对象等⾮原⼦数据。
第一范式是满足关系型数据库的基本要求，不满足就不是关系型数据库

例如：我们创建一个学生表，记录学生信息和学校信息，此时我们就会发现此时的学校可以继续拆分，此时就不满足第一范式
错误的

正确的，我们可以将学校的所有信息全部都放入学生表中，或者用一个外键将其学生表和学校表连接起来
这样每一列都是基本数据类型了

第二范式

在满⾜第⼀范式的基础上，不存在⾮关键字段对任意候选键的部分函数依赖。存在于表中定义了复合主键的情况下
候选键：是一列或多列在一起的身份表示，其也可以作为表的主键
非关键字段：不属于候选键的列
简单来说就是表中非关键字段的列对表都主键都依赖

例如：学⽣可以选修课程，课程有对应的学分，学⽣考试后每⻔课程会产⽣相应的成绩

错误案例

这个表中我们是用学号和课程名这个复合主键来唯一标识某一个学生某一个课程的成绩
依赖关系：此时的学生姓名、年龄、性别都是依赖于学号与不依赖于课程名，但是学分只依赖于课程名，但是成绩是即依赖于学号，又依赖于课程名
因此，对于符合主键的表，如果有一列数据只与复合主键的一列或其中几列有关系，此时就存在部分依赖，此时不满足第二范式

存在问题：
1.数据多余
上表中我们为了将张三的成绩全部放入表中，我们要重复的放入了其姓名、年龄、性别
2.更新、插入和删除数据时候可能会出现异常

正确案例
由于依赖的主键不一样，因此我们此时可以分为三个表
此时我们的学生表和课程表，此时只有一个主键，因此其天然就是满足第二范式的

第三范式

在满⾜第⼆范式的基础上，不存在⾮关键字段，对任⼀候选键的传递依赖。
例如：b列依赖于a列，c列依赖于b列，此时传递依赖会导致其c列也依赖于a列

例如： 要求学⽣表中记录学⽣所属的学院，在满⾜第⼆范式的基础上对学⽣表做出修改
错误案例

此表用id来标识，其学号、学生姓名、年龄、性别，学院和学院电话
此时存在传递依赖，因为此时可以通过学生id可以查询某个学生信息，但是其学院电话是依赖于学院的，如此依赖学院电话也依赖于id了
这就出现了传递依赖

正确案例
只需要将其分成俩个表学生表和学院表即可

实际关系图

在设计表中，我们要从实际中分析要知道要用几个表、每个表的属性、每个表的用途、表之间的关系等等

实体-关系图（Entity-RelationshipDiagram）简称E-R图
通常采用E - R图来表示每个表的关系
E-R图包含了以下三种基本成分：
• 实体：即数据对象，⽤矩形框表⽰，⽐如⽤⼾、学⽣、班级等。
• 属性：实体的特性，⽤椭圆形或圆⻆矩形表⽰，如学⽣的姓名、年龄等。
• 关系：实体之间的联系，⽤菱形框表⽰，并标明关系的类型，并⽤直线将相关实体与关系连接起来

此是存在
1 : 1 -> 1对1
例如：学校教务系统，一个学生有一个账号，一个账号对应一个学生
1 ：N -> 一对多
例如：一个学生属于一个学院，但是一个学院中有好多学生
M : N -> 多对多
一个学生可以选择多个课程，一个课程可以被多个学生所选择

练习设计表

考勤系统，包含员工表，考勤记录表
此时一个员工有多个考勤信息，所以其比例是1 ：N

#员工表
create table emp(
       id int primary key,
	   name varchar(20)
);
#考勤信息
create table info(
       id int primary key,
	   emp_id int,
	   info_data timestamp,
	   foreign key (emp_id) references emp(id)
);

车辆违章系统，包含用户表，车辆表，违章信息表。违章信息表中包含用户和车辆的违章信息
一个用户有多个车辆，一个车辆也可能有多次违章记录
用户与车辆1 ：N
用户与违章记录 1 ：N
车辆与违章记录1 ：N

create table user(
			 id int primary key,
			 name varchar(20)
);
create table cars(
			 id int primary key,
			 name varchar(20),
			 uesr_id int,
			 foreign key (user_id) references user(id)
);
create table info(
	id int primary key,
    user_id int,
    cars_id int,
    foreign key (user_id) references user(id),
    foreign key (cars_id) references cars(id)
);