MySQL——表的约束以及设计（数据库三范式）

最新推荐文章于 2022-10-09 21:18:48 发布

啊夏同学

最新推荐文章于 2022-10-09 21:18:48 发布

阅读量331

点赞数

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/ShirleyZ1007/article/details/126564913

版权

表的约束以及设计（数据库三范式）

数据库约束：对于某一列的值能添加哪些内容做了一定的限制，这种限制手段就称为约束

常用约束类型

非空约束NOT NULL - 指示某列不能存储 NULL 值。

若在某一属性定义时规定该属性not null，则后续不能将空值存放在该属性中
```
create table if not exists test1(
    id int,
    name varchar(20) not null comment 'name属性不能存放空值'
);
```
1）插入

约定了非空约束的列在插入时若没有插入，则必须有默认值或者必须显示插入
```
-- 只添加姓名：成功
insert into test1(name) values('Lay');
-- 只添加id：失败
insert into test1(id) values(1);
-- Field 'name' doesn't have a default value
```
2）查看约束字段

通过desc查看非空约束的字段
```
desc test1;
```
3）在创建表之后添加非空约束

表约束都属于表结构上的修改：alter table … change …

步骤：

a. 将当前表中所有目标属性为null的行先删除或者修改为非空
```
update test1 set id=0 where id is null;
```
b. 给目标属性添加非空约束
```
alter table test1 change id id int not null;
```

唯一约束UNIQUE - 保证某列的每行必须有唯一的值。

唯一约束指的是对应字段是唯一的，不能重复，一个表的唯一约束可以有多个

1）插入

create table if not exists stu_unique(
    id int unique comment '此时id属性不能重复',
    name varchar(20) not null comment 'name属性不能存放空值'
);
-- id不同插入成功
insert into stu_unique values(1,'李大为'),(2,'杨树');
-- id重复插入失败
insert into stu_unique values(1,'白鹿');
-- Duplicate entry '1' for key 'id'

2）查看唯一约束

唯一约束：又称唯一键，是一个索引

show keys from tb_name;

3)唯一约束存放null的情况

null值不受唯一键约束

insert into stu_unique values(null,'夏洁');
insert into stu_unique values(null,'赵继伟');

4）创建表之后想修改某个字段为唯一约束

alter table tb_name add unique(属性名);

给name属性加上唯一约束

alter table stu_unique add unique(name);
show keys from stu_unique;

默认值约束DEFAULT - 规定没有给列赋值时的默认值。

1）规定了默认值的表，在插入数据时若没有指定该列，则使用默认值插入数据（隐式插入）
```
create table if not exists test2(
    id int unique not null,
    sex varchar(1) default '男'
);
insert into test2(id) values(1),(2);
```
2）若显式的对含有默认值约束的列插入null值，不会触发默认值
```
insert into test2 values(2,null);
```

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

一张表只能有一个主键primaty key = not null + unique

主键约束的列值不能重复，不能为null

主键约束可以由多个列共同构成，称为联合主键

1）创建主键约束和插入

a. 单个主键

create table if not exists stu_primary(
    id int primary key comment '学生学号作为主键',
    name varchar(20)
);
insert into stu_primary values(1,'李大为'),(2,'杨树');
-- id重复插入失败
insert into stu_primary values(1,'白鹿');
-- Table 'java_class.stu_primary' doesn't exist

b. 复合主键

create table if not exists stu_primary2(
    id int,
    name varchar(20),
    primary key(id,name) comment 'id-name为复合主键'
);

2）查看主键约束字段

show keys from stu_primary;

3）添加主键

前提：当前表中没有主键约束

a. 添加单个主键

alter table stu_primary add primary key(name);
-- 表中已有主键：Multiple primary key defined

b. 添加复合主键

alter table stu_primary add primary key(id,name);

必须id-name完全相同，部分属性不同就是不同的两条记录

4）删除表中主键

alter table tb_name drop primary key;

5）自增主键

主键不重复且不为空，且一般来说作为主键的列是int或者定长的char类型，因此可以将主键的增长交给数据库进行

create table if not exists auto_test(
    id int primary key auto_increment,
    name varchar(20)
);

a.自增主键的插入

可以显示的插入null，或直接不写，都会触发自增

insert into auto_test(name) values('Lay');
insert into auto_test values(null,'LayZ');
select * from auto_test;

b. 关于自增主键的删除

delete删除后的主键，下次再自增时是以是已经出现过的最大值为基准来自增，用过的不会再用

insert into auto_test values(null,'L');
delete from auto_test where id=2;
select * from auto_test;

insert into auto_test values(null,'LayZhang');
select * from auto_test;

delete from auto_test where id=4;
insert into auto_test values(null,'vivi');
select * from auto_test;

truncate删除表之后，会还原自增主键的值

truncate table auto_test;
insert into auto_test values(null,'LayZhang');
select * from auto_test;

c. 显式给自增主键插入一个值

下一次触发自增操作时，仍然还是以当前出现过的最大值为基准自增

insert into auto_test values(100,'vivi');
insert into auto_test values(null,'LayZ');
select * from auto_test;

1到100之间的值只能显式的插入

insert into auto_test values(51,'cici');
insert into auto_test values(null,'LayZ');
select * from auto_test;

插入错误后重新插入，插入错误的记录也会触发自增操作

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

外键约束涉及多个表之间的关联约束

foreign key (当前从表中的字段名) references 主表(列)

注：将mysql中的关键字作为属性名称，加上``括起来

create table if not exists class(
  id int primary key auto_increment,
  name varchar(20),
  `desc` varchar(100) comment '班级信息描述'
);
create table if not exists class(
  id int primary key auto_increment,
  name varchar(20),
  class_id int,
  foreign key(class_id) references class(id)
);

a. 外键约束的插入

学生表中的class_id关联class的id，学生表插入数据时，class_id要能正确插入，必须保证class表中id值存在

-- 插入失败
insert into student(name,class_id) values('LayZhang',1);
-- Cannot add or update a child row: a foreign key constraint fant fails
-- 给class添加班级后，重新插入成功
insert into class(name,`desc`) values('ceo','一个公司');
insert into student(name,class_id) values('LayZhang',1);
-- 插入错误后重新插入仍触发id的自增操作
insert into student(name,class_id) values('LayZ',2);
insert into student(name,class_id) values('L',2);
insert into student(name,class_id) values('Lay',1);
select * from student;

b. 外键约束的删除

插入时，先看主表，只有当主表中有这个属性值时，才能在从表中插入

删除时，先看从表，只有把从表中该属性关联的所有记录都删除后，才能在主表中删除

如：学生和班级，先有班级才能将学生分入，毕业时也要保证班里所有学生都毕业才能删除该班级

delete from class where id=1;
--  Cannot delete or update a parent row: a foreign key constraint fails

delete from student where class_id = 1;
delete from class where id=1;

CHECK(了解) - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。（MySQL8.0.16版本之后才支持check约束）
```
drop table if exists test_user; 
create table test_user ( 
	id int, 
	name varchar(20), 
	sex varchar(1), 
	check (sex ='男' or sex='女') 
);
```

三范式（了解）

第一范式：确保每列的原子性（设计表时，每一列都不可分解）

第二范式：当前变种所有属性都和主键相关。

第三范式：表中的所有属性都和主键直接相关而不是间接相关

第一范式保证表中所有列都是原子的、不可拆分的，第二三范式保证表中所有属性都和主键相关且直接相关。若某些属性和主键不相关或不直接相关时，需要拆分表，让拆分后的表都和主键相关

表间的三种关系：

一对一

一对多：在多的表中设置外键，关联一的表

(
id int,
name varchar(20),
sex varchar(1),
check (sex =‘男’ or sex=‘女’)
);



# 三范式（了解）

第一范式：确保每列的原子性（设计表时，每一列都不可分解）

第二范式：当前变种所有属性都和主键相关。

第三范式 ：表中的所有属性都和主键直接相关而不是间接相关

第一范式保证表中所有列都是原子的、不可拆分的，第二三范式保证表中所有属性都和主键相关且直接相关。若某些属性和主键不相关或不直接相关时，需要拆分表，让拆分后的表都和主键相关



表间的三种关系：

一对一

一对多：在多的表中设置外键，关联一的表

多对多：创建一个临时表记录关系