mysql/mariadb知识点总结(10):约束管理、索引管理、键管理语句

  • A+
所属分类:mysql  数据库

mysql/mariadb知识点总结(10):约束管理、索引管理、键管理语句

这篇文章总结了mysql中常用的约束管理、索引管理、键管理语句。

 

在本博客中,"mysql"是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。

mysql文章列表直达链接:mysql知识点总结

 

关系型数据库中的数据约束的概念为:数据约束可以理解为向数据表中插入数据时要遵守的限制规则。

mysql中的常用约束有主键约束、唯一键约束、外键约束、非空约束等,我们先列出其概念。

 

主键:primary key,表上一个或多个字段的组合,填入主键字段中的数据,必须不同于已经存在的其它行的相同字段上的数据,而且也不能为空;一个表只能存一个主键,一个主键可以由多个字段组成;

惟一键:unique key,表上一个或多个字段的组合,填入其中字段中的数据,必须不同于已经存在的其它行的相同字段上的数据,但可以为空;一个表可以有多个唯一键;

外键:foreign key,一个表中的外键字段中所能够插入的数据取值范围,取决于引用的另一个表上主键字段上的已经存在数据集合;

检查条件约束:check,自定义的逻辑表达式;

 

下面练习到的约束在创建表的时候就可以被定义(参考表管理语句,直达链接:表管理语句总结),此处只列出如何为已经存在的字段添加、删除、修改约束。

注意,在创建主键约束,唯一键约束的时候,会自动创建对应的索引。

 

非空约束

添加非空约束

设置非空字段。使用如下语句为testtb表中的name字段添加非空约束。

 

删除非空约束

去除非空约束只要将字段修改为可以为空即可,当然,如果字段有其他约束,不要忘了加载语句的后面。

 

 

自动增长

添加自动增长

为字段设置自动增长,同样既可以使用change,也可以使用modify,具体不同可参考"表管理语句"中的"修改表"部分。

如下语句表示为testtb表的id字段设置自动增长。

如下语句表示为testtb表的id字段设置自动增长。

 

删除自动增长

去除自动增长的语句可以结合着添加自动增长的语句查看。同样有两种方法,使用change或者modify均可。

 

 

主键约束

添加主键约束

使用如下两条语句中的任意一条均可为表设置主键,注意,一个表中只能有一个主键。

如下命令表示将testtb4中的id字段指定为表的主键,通过如下语句设置的主键的主键名称默认为PRIMARY。

 

删除主键约束

去除主键时需要注意,不能直接删除主键,需要先删除自动增长,然后再删除主键。

注意:如果对应的主键上存在自动增长,则不能直接删除主键,需先删除自动增长后,再使用如下语句删除主键。同理,如果当前表的主键已经被其他表中的字段当做外键,那么则需要先删除其他表中的主外键关系,才能删除当前主键。

 

  

唯一键约束

添加唯一键约束

使用如下语句,添加唯一键约束,唯一键的名称默认为字段名称,如果对一个字段重复创建唯一键,唯一键名称将会按照创建时的序号依次向后增加,但是针对同一个字段重复创建唯一键是没有任何意义的,也强烈不建议这样做。

使用如下语句在创建唯一键的时候,可以指定唯一键的名称,例如,指定唯一键的名称为uni_test。

 

删除唯一键约束

使用如下语句删除唯一键uni_test,注意,删除唯一键时使用的是index关键字。

 

 

查看约束

查看主键 / 查看唯一键 /查看外键

针对一张表而言,查看主键,查看外键,查看唯一键 可以使用同样一条语句完成。

如下语句表示显示出test1表中的主键信息、唯一键信息以及外键信息,也就是说test1表的所有键信息(不包含纯索引index信息)都会显示出来。

 

当然,除了上述方法,也可以只查看主键,比如最简单的方法,查看test1的表结构,即可查看test1表的主键。

如果不想查看主键,只想查看特定表的外键,则可以使用下面"查看主外键对应关系"中提到的方法。

 

查看主外键对应关系

如下语句表示查看test1这张表中引用了哪些表中的主键作为自己的外键。也可以理解为查看test1表的外键。

 

如下语句表示查看test2这张表的主键被哪些表引用成为了外键,可以与上一个示例对比查看。

 

 

外键约束

添加外键约束

使用如下两条语句设置外键,如下语句表示在testtb表中创建一个新的字段,tid,并且添加了一个外键,外键的名称叫testtb_tid_fk,外键中, testtb表中的tid字段引用了表testtb2表中的id字段。

 

删除外键约束

如果想要删除外键,必须先查询到对应外键的名称,才能去除外加,使用"查看主外键对应关系"中的方法,可以查看某张表中的所有外键,找到对应的外键名称后,根据外键名称删除即可。

例如,先使用如下语句查询到test4表中的外键有哪些,可能有多个,找到对应的外键名称,比如外键 test_tid_fk。

使用如下语句 ,表示删除test4表中的 test_tid_fk 外键。

 
 

索引管理

添加一个索引/创建一个纯粹的索引

这里所说的纯粹的索引指的就是index,不是unique index,只是单纯的index,使用如下语句表示为testtb表的name字段添加一个单纯的索引,索引的名字为ind_name。

 

当我们使用上述语句添加索引以后,使用show index from testtb;可以查看到对应的索引,但是在information_schema.KEY_COLUMN_USAGE 表中将不会存在同名的"约束",而当我们创建一个唯一键或者唯一索引的时候,不仅能在information_schema.KEY_COLUMN_USAGE表中看到对应名称的"约束",同时还能使用show index from testtb;查看到对应名称的索引,这就体现了"键/约束"与"索引"的不同,当我们创建一个约束的时候,会创建对应的索引,但是如果只是创建一个单纯的索引,"纯index",那么它则只是一个"索引"罢了。

 

我们还可以使用另外一种语法创建索引,语法如下。

CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [using index_type] ON tbl_name (index_col_name,...)

上述语法中,我们可以指定创建哪种特殊索引,也可以只使用index,表示创建一个纯索引,[using index_type]表示索引类型,可省略,省略后默认使用btree类型的索引,并且指明在哪个表的哪个字段上创建索引。index_col_name用于指明字段名称,示例如下,如下语句表示在testtb表的name字段上创建一个索引,索引的名称为ind_name,索引的长度为20(注意,20并不是表示name字段的长度,假如name字段长度为100,此处指定20表示取出name字段的左边开头的头20个字节作为索引),索引为降序排列,不指定desc时默认以升序排序。

如下示例语句表示创建一个联合索引。

 

重建索引

数据库长时间运行后,索引会有损坏的可能,这个时候,重建索引,可以提高数据的检索速度,所以,重建索引的操作在平常的维护工作中可能会被用到,不过mariadb中的innodb存储引擎不支持重建索引。

 

删除一个索引

使用如下语句可以删除testtb表中的纯索引以及唯一索引。

 

查看索引

查看testtb表中的所有索引

根据索引名称,查看testtb表中的部分索引,如下语句表示查看testtb表中以ind开头的索引。

 

weinxin
我的微信公众号
关注"实用运维笔记"微信公众号,当博客中有新文章时,可第一时间得知哦~
朱双印

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:2   其中:访客  1   博主  1

    • avatar 小c 2

      哥 外键约束 你的标题你打错了 写成外检了

        • avatar 朱双印 Admin

          @小c 感谢客官指正,错误已经修改~~