学习笔记

Appearance

Return to top

MySQL的完整性

保证存储到数据库中的数据都是正确的。

  • 如何保证数据的完整性：

    • 数据完整性的分类：试题完整性、域完整性、参照完整性

    • 无论哪种完整性都是在创建表时给表添加约束即可

实体完整性

什么是实体完整性？

表中的一行数据就是一行实体

如何保证实体完整性？

保证实体完整性就是保证每一行数据的唯一性

实体完整性的约束类型

  • 主键约束 （primary key）

  • 唯一约束 （unique）

  • 自动增长列 （auto_increment）

主键约束

主键用于唯一标识表中的每一条数据，和现实生活中的身份证很像

sql

create table person(
    id int primary key,
    name varchar(20)
);
  • 特征：
  1. 如果将某一个字段设置成了主键，那么这个字段的取值就不能重复了
  2. 如果将某一个字段设置成了主键，那么这个字段的取值就不能为NULL
  3. 一张表中只能有一个主键，不能出现多个主键
  4. 我们除了可以在字段的数据类型后面添加primary key，将这个字段 变成主键以外 还可以通过在最后面牺牲primary key(字段名称)的方式来指定主键
sql

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

联合主键

我们通过将表中的某个永远不重复的字段设置为主键，从而达到保存每一行数据的唯一性（实体完整性） 但是在企业开发中有时候可能找不到不重复的字段，此时我们还可以通过联合主键的方式来保证每一行数据的唯一性 联合主键就是同事将多个字段作为一个主键来使用

sql

create table person(
    name varchar(20),
    age tinyint,
    primary key(name,age)
)

注意点：

  • 联合主键并不是添加多个主键，而是将多个字段的值作为主键来使用

  • 也就是过去指定id为主键，那么id的取值不能重复

  • 而现在如果我们指定name和age为主键,那么name+age的值不能重复

唯一约束(unique)

唯一约束用于保证某个字段的值永远不重复

sql

create table person(
    id int unique,
    name varchar(20)
)

  • 和主键的异同：

    • 唯一约束和主键约束一样，被约束的字段的取值都不能重复
    • 主键在一张表中只能有一个，而唯一约束在一张表中可以有多个
    sql
        create table person(
        id int unique,
        name varchar(20) unique
    );
    • 主键的取值不能为NULL，而唯一约束的取值可以是NULL

自动增长约束(auto_increment)

自动增长约束的作用是让某个字段的取值从1开始递增，从而保证实体完整性

sql

create table person(
    id int auto_increment primary key,
    name varchar(20)
);

  • 如果某个字段是自动增长的，那么这个字段必须是主键才可以

  • 如果仅仅是主键，那么取值不呢那个为NULL，如果主键是自增长的，那么主键可以传值NULL或者DEFAULT

  • 在企业开发中我们如何选择主键：

    • 最少性：能用一个字段作为主键，就不要使用多个字段

    • 稳定性：能用不被操作（修改）的字段作为主键，就不要使用被操作的字段作为主键

    • 一般情况下我们会定义一个名称叫做id的字段，并且这个字段是整型的，并且这个字段是自动增长的来作为主键

修改约束

  1. 修改主键约束
sql

alter table 表名 add primary key(字段名称);

create table person (
    id int,
    name vachar(20)
);

alter table person add primary key(id);
  1. 修改唯一约束
sql

alter table 表名 add unique(字段名称);

create table person (
    id int,
    name vachar(20)
);

alter table person add unique(name);
  1. 自增长约束
sql

alter table 表名 modify 字段名称 字段类型 auto_increment;

create table person (
    id int,
    name varchar(20)
)

# 需要先设置主键

alter table person add primary key(id);

# 再设置自增

alter table person modify id int auto_increment;

域完整性

一行数据中的每个单元格都是一个域

如何保证域的完整性？

保证单元格数据的正确性，即是保证域的完整性

  • 正确使用数据类型

    • 人类的年龄不可能超过255岁，而且不能是负数，所以我们可以使用 tinyint unsigned
    • 人的性别只能是男、女，所以可以使用枚举类型
    • 要存储比较多的文字，为了保证不超出每一行最大的存储限制，我们可以使用大文本类型

  • 使用非空约束（not null）

  • 使用默认值约束（default）

示例：

  • 非空约束
sql

create table person(
    id int,
    name varchar(20) not null
)
  • 默认值约束
sql

create table person(
    id int,
    name varchar(20) default 'test'
)

注意点：

如果传入传入的是 null 则还是NULL，不会使用默认值

insert into person values(1,default);

参照完整性

又称为引用完整性，主要用于保证多表之间引用关系正确性。

为什么要创建多张表

如果将所有的数据都放到一张表中，会出现大量冗余数据，所以为了降低数据库的体积，提升数据库的效率，我们需要根据自身需求对表进行拆分

什么时候出现冗余数据

mysql中的表的对应关系：

一对一

一般不会对表进行拆分

一对多（需拆分表）

需要拆分成两张表关联

  • 一个班有多个学生

  • 一个学生有多项成绩

多对多（需拆分表）

需要通过三张表来进行关联

  • 一个学生有多个老师

  • 一个老师有多个学生

如何保证参照完整性

默认情况下表与表之间是独立存在的，不会互相影响 也正是因为如此，默认情况下也不会检查表与表之间的依赖关系 所以为了保证表与表之间的参照完整性，我们可以通过外键来保证参照完整性

sql

create table stu(
    id int auto_increment primary key,
    name varchar(20),
    gender enum('男','女','妖')
);

create table grade(
    id int auto_increment primary key,
    km varchar(20),
    score double,
    uid int
);

什么是外键

如果一张表中有一个字段指向另一张表中的主键，就将该字段叫做外键。

foreign key(需要做外键的字段) references 需要绑定哪个表的主键的表的名称(主键id)

sql

create table grade(
    id int auto_increment primary key,
    km varchar(20),
    score double,
    uid int,
    foreign key(uid) references stu(id)
);

注意点

  • 只有InnoDB的存储引擎才支持外键约束
  • 外键的数据类型必须和指向的主键一致
  • 在一对多的关系中，外键一般定义在多的一方（一个学生有多门成绩，那么外键定义在成绩表中）
  • 定义外键的表我们称之为从表，被外键引用的表我们称之为主表

如何动态的添加外键

sql

alter table 从表名称 add foregin key(从表外键字段) references 主表名称(主表主键名称);

如何查看谁是外键

sql

show create table 从表名称;

注意点：

在查看表中外键的时候会有以下的一种格式：

CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `stu` (`id`)

解释如下：

  • 将uid变成外键，外键的名称是 grade_ibfk_1
  • uid的取值引用的是stu这张表中的id字段的值

如何动态删除外键

sql

alter table 从表名称 drop foregin key 外键名称;

注意点：

这里的外键名称与上面查出来的外键名称需要一致，如上面的grade_ibfk_1

外键的操作

  • 严格操作

    • 主表中不存在对应的数据，从表不允许添加数据
    • 从表引用主表数据，主表的数据不允许被删除
    • 从表引用主表数据，主表的被引用的数据不允许修改

  • 置空操作（null）

    • 在企业开发中，我们可能必须要删除主表中的数据，但是如果主表被删除了从表就不完整了
    • 所以在企业开发中，我们可以通过置空操作，在删除主表数据的同时删除从表中的关联数据
    sql
    
create table grade(
    id int auto_increment primary key,
    name varchar(20),
    uid int,
    foreign key(uid) references stu(id) on delete set null
)
    • 如果删除主表中的数据对应从表中的外键字段会被置空

  • 联级操作

    • 在企业开发中，我们可能必须要修改主表中的数据，但是如果主表被修改了从表数据就不完整了
    • 所以我们可以通过级联操作实现，在主表中修改数据从表中对应的也会自动修改
    sql
    
create table grade(
    id int auto_increment primary key,
    name varchar(20),
    uid int,
    foreign key(uid) references stu(id) on update cascade
)

多对多外键

在多对多表中存在一张关系表

学生表：

sql

create table stu(
    id int auto_increment primary key,
    name varchar(20)
)

教师表：

sql

create table grade(
    id int auto_increment primary key,
    name varchar(20)
)

关系表：

sql

create table rel(
    stuId int,
    gradeId int
)

alter table rel add foregin key(stuId) references stu(id);
alter table rel add foregin key(gradeId) references grade(id);

通过关系表保证了表的引用完整性和参照完整性。

Released under the MIT License.

Copyright © 2022-present Yanyu