MySQL约束知识点实例分析

这篇文章主要介绍“MySQL约束知识点实例分析”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL约束知识点实例分析”文章能帮助大家解决问题。数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中 存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，SQL
规范以约束的方式对
表数据进行额外的条件限制
。从以下四个方面考虑：实体完整性（Entity Integrity
）
：例如，同一个表中，不能存在两条完全相同无法区分的记录域完整性（Domain Integrity
）
：例如：年龄范围
0-120
，性别范围
“
男
/
女
”引用完整性（Referential Integrity
）
：例如：员工所在部门，在部门表中要能找到这个部门用户自定义完整性（User
–
defined Integrity
）
：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5
倍。约束是表级的强制规定。可以在
创建表时规定约束（通过
CREATE TABLE

语句）
，或者在
表创建之后通过
ALTER TABLE
语句规定约束
。根据约束数据列的限制，
约束可分为：单列约束
：每个约束只约束一列多列约束
：每个约束可约束多列数据根据约束的作用范围
，约束可分为：列级约束
：只能作用在一个列上，跟在列的定义后面表级约束
：可以作用在多个列上，不与列一起，而是单独定义
位置支持的约束类型是否可以起约束名列级约束：列的后面语法都支持，但外键没有效果不可以表级约束：所有列的下面默认和非空不支持，其他支持可以（主键没有效果）根据约束起的作用
，约束可分为：
1. NOT NULL 非空约束，规定某个字段不能为空2. UNIQUE 唯一约束，规定某个字段在整个表中是唯一的3. PRIMARY KEY 主键(非空且唯一)约束4. FOREIGN KEY 外键约束5. CHECK 检查约束6. DEFAULT 默认值约束注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果 查看某个表已有的约束 ： # information_schema数据库名（系统库）
# table_constraints表名称（专门存储各个表的约束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = ‘表名称’;
限定某个字段/
某列的值不允许为空
NOT NULL 1. 默认，所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型
2. 非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空
（只有列级约束，没有表级约束）
3. 一个表可以有很多列都分别限定了非空
4. 空字符串”不等于NULL，0也不等于NULL 语法格式：举例：语法格式：举例：语法格式：举例：用来限制某个字段/
某列的值不能重复。UNIQUE1.同一个表可以有多个唯一约束。2.唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。3.唯一性约束允许列值为空。并且允许存在多个NULL值。4.在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。5.MySQL会给唯一约束的列上默认创建一个唯一索引。语法格式：1.列级约束2.表级约束举例：语法格式：注：字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段的组合是唯一的举例：语法格式：1.在 create table 时添加复合唯一约束2. 在 alter table 时添加复合唯一约束字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的举例：1.添加唯一性约束的列上也会自动创建唯一索引。2.删除唯一约束只能通过删除唯一索引的方式删除。3.删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。4.如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和() 中排在第一个的列名相同。也可以是自定义唯一性约束名。语法格式：查看表从索引：举例：用来唯一标识表中的一行记录。primary key主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。1. 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。2. 主键约束对应着表中的一列或者多列（复合主键）3. 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。4. MySQL的主键名总是
PRIMARY
，就算自己命名了主键约束名也没用。5. 当创建主键约束时，系统默认会在所在的列或列组合上建立对应的
主键索引
（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。6. 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性语法格式：1.列级模式2.表级模式（注：
MySQL的主键名总是
PRIMARY
，就算自定义了主键约束名也没用
）举例：多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。语法格式：字段1和字段2的组合是唯一的，也可以有更多个字段举例：字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键语法格式：举例：删除主键约束，不需要指定主键名，因为一个表只有一个主键，
删除主键约束后，非空还存在。
（但在实际开发中，不会去删除表中的主键约束）语法格式：举例：某个字段的值自增auto_increment1. 一个表最多只能有一个自增长列2. 当需要产生唯一标识符或顺序值时，可设置自增长3. 自增长列约束的列必须是键列（主键列，唯一键列）4. 自增约束的列的数据类型必须是整数类型5. 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接 赋值为具体值语法格式：举例：非法创建：正确创建方式：插入数据:特殊情况(不推荐此写法)：语法格式：举例：语法格式：举例：在MySQL 8.0
之前，自增主键
AUTO_INCREMENT
的值如果大于
max(primary key)+1
，在
MySQL
重启后，会重 置AUTO_INCREMENT=max(primary key)+1
，这种现象在某些情况下会导致业务主键冲突或者其他难以发 现的问题。 下面通过案例来对比不同的版本中自增变量是否持久化。案例：对于MySQL5.7版本：然后重启MySQL57服务器：（以管理员的身份运行）从结果可以看出，新插入的0
值分配的是
4
，按照重启前的操作逻辑，此处应该分配
6
。出现上述结果的主要原因是自增主键没有持久化。在MySQL 5.7系统中，对于自增主键的分配规则，是由
InnoDB
数据字典内部一个
计数器
来决定的，而该计数器只在
内存中维护
，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。对于MySQL8.0版本：然后重启MySQL80服务器：（以管理员的身份运行）从结果可以看出，自增变量已经持久化了。MySQL 8.0将自增主键的计数器持久化到
重做日志
中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB
会根据重做日志中的信息来初始化计数器的内存值。限定某个表的某个字段的引用完整性。比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。FOREIGN KEY主表（父表）：被引用的表，被参考的表从表（子表）：引用别人的表，参考别人的表例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。1. 从表的外键列，
必须引用/参考主表的主键或唯一约束的列因为被依赖/被参考的值必须是唯一的2. 在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。3. 创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表4. 删表时，先删从表（或先删除外键约束），再删除主表5. 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖 该记录的数据，然后才可以删除主表的数据6. 在“从表”中指定外键约束，并且一个表可以建立多个外键约束7. 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误。8.
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）9. 删除外键约束后，必须 手动 删除对应的索引语法格式：– FOREIGN KEY: 在表级指定子表中的列– REFERENCES: 标示在父表中的列(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样举例：正确的创建方式：错误的创建方式：添加数据：修改数据：删除数据：一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那么，就要用修改表的方式来补充定义。语法格式：1. Cascade
方式
：在父表上
update/delete
记录时，同步
update/delete
掉子表的匹配记录2. Set null方式
：在父表上
update/delete
记录时，将子表上匹配记录的列设为
null
，但是要注意子 表的外键列不能为not null3. No action方式
：如果子表中有匹配的记录，则不允许对父表对应候选键进行
update/delete
操作4 .Restrict方式 （默认）
：同no action
， 都是立即检查外键约束5. Set default方式
（在可视化工具
SQLyog
中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb
不能识别如果没有指定等级，就相当于Restrict方式。对于外键约束，最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式。举例：（以 on update cascade on delete set null

为例）1.创建表2.添加数据3.修改数据4.删除数据流程如下：(1)第一步先查看约束名和删除外键约束（
2）第二步查看索引名和删除索引。（注意，只能手动删除）#查看某个表的索引名注意： 删除外键约束后，必须 手动 删除对应的索引举例：问题
1
：如果两个表之间有关系（一对一、一对多），比如：员工表和部门表（一对多），它们之间是否
一定要建外键约束？答：不是的问题
2
：建和不建外键约束有什么区别？答：建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的
引用完整 性
，只能依
靠程序员的自觉
，或者是
在
Java
程序中进行限定
。例如：在员工表中，可以添加一个员工的信息，它的部门指定为一个完全不存在的部门。问题
3
：那么建和不建外键约束和查询有没有关系？答：没有拓展：在 MySQL
里，外键约束是有成本的，需要消耗系统资源。对于大并发的
云主机域名免费试用 SQL
操作，有可能会不适 合。比如大型网站的中央数据库，可能会
因为外键约束的系统开销而变得非常慢
。所以，
MySQL
允 许你不使用系统自带的外键约束，在
应用层面
完成检查数据一致性的逻辑。也就是说，即使你不 用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。【 强制 】不得使用外键与级联，一切外键概念必须在应用层解决。说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的
student_id
则为外键。如果更新学 生表中的 student_id
，同时触发成绩表中的
student_id
更新，即为级联更新。外键与级联更新适用于
单机低并发
，不适合
分布式
、
高并发集群
；级联更新是强阻塞，存在数据库
更新风暴
的风险；外键影响 数据库的
插入速度
。检查某个字段的值是否符号xx
要求，一般指的是值的范围CHECKMySQL5.7 可以使用
check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告。MySQL 8.0中可以使用check约束了
。给某个字段/
某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。DEFAULT语法格式：方式1：方式2：说明：默认值约束一般不在唯一键和主键列上加举例：语法格式：注：1.如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了。2.同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语句中保留默认值约束，否则就删除了。举例：语法格式：1.删除默认值约束，也不保留非空约束2.删除默认值约束，保留非空约束举例：面试
1
、为什么建表时，加
not null default ”
或
default 0答：不想让表中出现null
值。面试
2
、为什么不想要
null
的值答:
（1）不好比较。
null
是一种特殊值，比较时只能用专门的
is null
和
is not null
来比较。碰到运算符，通常返回null
。（2）效率不高。影响提高索引效果。因此，我们往往在建表时 not null default ”
或
default 0面试
3
、带
AUTO_INCREMENT
约束的字段值是从
1
开始的吗？在MySQL
中，默认AUTO_INCREMENT的初始 值是1，每新增一条记录，字段值自动加1
。设置自增属性（AUTO_INCREMENT）的时候，还可以指定第 一条插入记录的自增字段的值，这样新插入的记录的自增字段值从初始值开始递增，如在表中插入第一 条记录，同时指定id
值为
5
，则以后插入的记录的
id
值就会从
6
开始往上增加。添加主键约束时，往往需要 设置字段自动增加属性。面试
4
、并不是每个表都可以任意选择存储引擎？
外键约束（FOREIGN KEY）不能跨引擎使用。(主表和从表用的引擎要相同)MySQL支持多种存储引擎，每一个表都可以指定一个不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。关于“MySQL约束知识点实例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注开发云行业资讯频道，小编每天都会为大家更新不同的知识点。

相关推荐: MySQL的用户如何管理

这篇“MySQL的用户如何管理”文章的知识点大部分人都不太理解，所以小编给大家总结了以下内容，内容详细，步骤清晰，具有一定的借鉴价值，希望大家阅读完这篇文章能有所收获，下面我们一起来看看这篇“MySQL的用户如何管理”文章吧。用户名就不用解释了，就是我们登录时…