MySQL 约束解析和举例

107 阅读7分钟

数据完整性约束

数据的完整性约束(简称“约束”)是在表和字段上强制执行的数据检测规则,是为了防止不规范的数据进入数据库。当我们对数据进行 DML 操作时,数据库管理系统(DBMS)会自动按照我们设置的约束条件对数据进行检测,以保证数据存储的完整性和准确性

完整性约束分为 4 类:实体完整性约束、域完整性约束、参照完整行约束、用户自定义完整性约束。

  • 实体完整性约束:用来标识表中的每一条记录都代表一个实体,如主键约束;
  • 域完整性约束:用来针对单元格的约束,如非空约束等;
  • 参照完整行约束:指多表之间的对应关系,在一张表中执行数据插入、更新、删除等操作时,DBMS 都会跟另一张表进行对照,避免不规范的操作,以确保数据存储的完整性,如外键约束;
  • 用户自定义完整性约束:用户根据实际的要求来定义,在执行数据插入、更新等操作时,DBMS 会检查数据是否满足检查约束中的限定条件,避免不符合条件的操作,以保证数据存储的准确性,如检查约束(MySQL 不支持,Oracle 支持)。

在 MySQL 中支持的约束有 6 种,分别是:非空约束、主键约束、默认值约束、唯一约束、外键约束和自定义检查约束。

根据约束添加的位置,我们可以把约束分为两类:

  • 列级约束:即直接在定义的字段名和类型后面追加约束。但该方式只支持默认值约束、非空约束、主键约束、唯一约束。
  • 表级约束:即在各个字段定义完后进行添加。表级约束不支持非空约束和默认值约束。

非空约束

创建非空约束使用的语句是 NOT NULL,其作用是保证该字段不能为空

1.建完表然后添加

CREATE TABLE specialty (
specialty_name VARCHAR(20),
specialty_college VARCHAR(20)
);
ALTER TABLE specialty CHANGE COLUMN specialty_name specialty_name VARCHAR(20) NOT NULL;    # 添加非空约束

2.建表时候添加

CREATE TABLE specialty (
specialty_name VARCHAR(20) NOT NULL;    # 添加非空约束
specialty_college VARCHAR(20)
);

查看新建表结构:

DESC specialty;

输出结果:

图片描述

在我们插入数据的时候,如果该字段值为空就会出错,如下所示:

INSERT INTO specialty VALUES (NULL, '计算机学院');

输出结果:

图片描述

这里需要注意一点的是空值(NULL)与空字符串('')的区别。

  • 查询判定的方式不同:判定某值是否为 NULL,可以使用 IS NULL 或者 IS NOT NULL ;而判定某值是否空字符串,使用 =<> 来运算。
  • 是否参与运算:空值(NULL)是不参与运算的,而空字符串可以。
  • 是否占用空间:空值(NULL)是占用空间的,而空字符串('')是不占用空间的。所以在设计表时,我们的字段尽可能设置为 NOT NULL 约束。

主键约束

创建主键约束使用 PRIMARY KEY 语句,其作用是保证该字段的值具有唯一性.每张表只能创建一个主键,是该表中的数据在表内的唯一标识,且默认自带非空属性。

1.利用修改表的方式,创建主键约束。

修改 student 表,使 student_id 成为主键,具体实现 SQL 语句如下:

ALTER TABLE student MODIFY student_id INT PRIMARY KEY;

2.创建时候添加

CREATE TABLE student ( 
student_id INT, 
student_name VARCHAR(20),
student_specialty VARCHAR(20),
primary key (student_id)
);

查看表结构,Key 这一列出现 PRI 即设置成功:

DESC student;

输出结果:

图片描述

主键设置成功后,插入数据就要考虑它的唯一性和非空性

主键不能重复,需要我们在插入数据的时候知道待插入记录的主键是否已经存在。数据少且有序比较好确认,如果数据多且乱序,确认主键重复的问题就会变得很麻烦。

有没有办法在我们插入记录的时候,让主键自动保持唯一性呢? 答案就是使用自增序列(auto_increment),一张表有且只能有一个自增序列。自增序列一般和主键搭配使用,使用自增序列字段的类型为整型。

修改 student 表的主键,

使 student_id 具有自增属性。使用 DROP 语句删除已经存在的主键特性,其实现 SQL 语句如下:

ALTER TABLE student DROP PRIMARY KEY;

输出结果:

图片描述

再次设置 student_id 为主键,

且具有自增属性。其 SQL 语句如下:

ALTER TABLE student MODIFY student_id INT PRIMARY KEY AUTO_INCREMENT;

使用自增长的好处是不用担心主键会重复的问题。需要注意,在插入数据的时候,自增长字段的值使用 NULL 来代替

默认值约束

创建默认值约束使用的关键字是 DEFAULT,其作用保证该字段总会有值..设置所有学生的专业默认是大数据专业,其 SQL 语句如下:

ALTER TABLE student  MODIFY student_specialty VARCHAR(20) DEFAULT '大数据专业';

下列语句不给 student_specialty 字段指定值:

INSERT INTO student (student_id, student_name) VALUES (null, '小蓝');

因为默认值约束的存在,所以即便不给 student_specialty 字段指定值,系统也会自动插入默认值。查看插入后的结果如下:

SELECT * FROM student;

输出结果:

图片描述

唯一约束

创建唯一约束使用的关键字是 UNIQUE,其作用保证字段值的唯一性。唯一约束和主键约束的相同点都是保证值的唯一性。区别在于,唯一约束在一张表中允许出现多个,而主键约束只能有一个 查看它的表结构:

DESC specialty;

操作截图如下:

图片描述

现在执行如下语句将 specialty_name 这一列增加唯一约束,使其不得有重复值出现:

ALTER TABLE specialty ADD CONSTRAINT UNIQUE(specialty_name);

再次查看其表结构:

DESC specialty;

操作截图:

图片描述

如上图所示,specialty_name 这一行的 Key 有了一个 PRI ,这是主键的标识。

非空约束 + 唯一约束 = 主键。

外键约束

创建外键约束使用的关键字是 FOREIGN KEY,其作用为限制两个表的关系,保证表中该字段的值来自于关联表。外键约束必须使用表级约束的方式来定义。

现在执行如下语句,将学生表中的 student_specialty 设置为外键,关联 specialty 表中的 specialty_name :

ALTER TABLE student ADD FOREIGN KEY (student_specialty) REFERENCES specialty(specialty_name);

输出结果:

图片描述

关键字 REFERENCE 连接关联的表。需要注意,主表在插入记录的时候,外键的值必须能在关联表的关联列中找到,否则插入信息失败。

image.png

mysql函数里面的format使用参考 MySql不难,我们一起进步之函数篇 - 掘金 (juejin.cn)

format为转换的格式,包含格式如下:

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)

%Y 年, 数字, 4 位

%y 年, 数字, 2 位

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m 月, 数字(01……12)

%c 月, 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss )

%T 时间,24 小时(hh:mm:ss)

%S 秒(00……59)

%s 秒(00……59)

%p AM或PM

%w 一个星期中的天数(0=Sunday ……6=Saturday )

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天