mysql 数据库约束条件与索引

2021/8/30 19:09:36

编程Tag： mysql table class 索引 id 主键 约束 约束条件

本文主要是介绍mysql 数据库约束条件与索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目录
  • 一 ：mysql 的约束条件
    • 1 什么是约束
    • 2 常见的约束条件
    • 3 约束条件的添加方式
      • 3.1 在建立新的表时候添加约束条件
      • 3.2 使用change修改表的字段时添加或删除，修改，约束条件
      • 3.3 使用modify 修改表字段属性时，添加，修改，删除 约束条件
    • 4 非空约束（not null)
      • 4.1 在新建表时添加not null
      • 4.2 在建立表之后，修改字段的类型，添加not null 约束
      • 4.3 删除非空约束
      • 4.4 null 和 not null 的效果
    • 5 唯一性约束（unique)(也是唯一性索引)
      • 5.1 创建表时设置唯一索引
      • 5.2 在建立表之后，修改表属性，添加唯一性约束
      • 5.3 删除唯一键约束
    • 6 主键约束(primary key)
      • 6.1 创建表时设置主键索引
      • 6.2 通过修改表结构设置主键约束
      • 6.3 删除主键约束
    • 7 外键约束
      • 7.1 先创建主表，主键
      • 7.2 创建外键
      • 7.3 测试从表与主表的关联
      • 7.4 通过修改表结构来设置外键
      • 7.5 删除外键
    • 8 默认约束（default)
      • 8.1 创建表时候设置默认约束
      • 8.2 通过修改 表字段来设置默认值
    • 9 自增约束(auto_increment)
      • 9.1 在创建按表的时候，设置自增长
      • 9.2 修改表结构，设置字段值自增长
  • 二： 索引
    • 1 数据库索引概念
      • 1.1 概念
      • 1.2 索引项和索引值
      • 1.3 索引的作用与副作用
      • 1.4 建立索引的原则依据
      • 1.5 数据库查询的过程
    • 2 索引的分类
    • 3 普通索引
      • 3.1 创建普通索引
      • 3.2 查看索引
    • 4 组合索引
    • 5 全文索引
      • 5.1 创建全文索引
      • 5.2 模糊查询匹配
    • 6 查看索引
    • 7 删除索引

一 ：mysql 的约束条件

1 什么是约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性


2 常见的约束条件

mysql 常见的额外约束条件有4 类6项

  1. Null 空值设置，：非空约束（not null）
  2. Key 键值设置： 唯一性约束 （unique） ,主键约束（primary key)PK, 外键约束(foreign key)FK
  3. Default 默认设置：默认值约束 (default)
  4. Extra 额外设置：自增约束(auto_increment)

3 约束条件的添加方式

3.1 在建立新的表时候添加约束条件

create table 表名(字段1 数值类型 约束条件， 字段2 数据类型 约束条件);

eg: 
create table class(id int not null , name char(20) not null );  
#设置id 字段和 name 字段值 允许为null

3.2 使用change修改表的字段时添加或删除，修改，约束条件

alter table 表名 change 旧的表字段 新的表字段 数据类型 约束条件

eg:
alter table  class change  id  id int null;   
#修改id 字段值 允许为null

当新旧表字段，值类型相同时，就是对约束条件的操作。

旧字段没有约束条件，如果值类型后面加了约束条件，则是为该字段添加约束条件

旧字段有约束条件，如果值类型后面跟了新的约束条件，则是为该字段修改约束条件

旧字段有约束条件，如果值类型后面没有加约束条件，则是为该字段删除约束条件


3.3 使用modify 修改表字段属性时，添加，修改，删除 约束条件

alter table 表名 modify 表字段 数据类型 约束条件 

eg
alter table class  modify  name char(20)  null;  
#修改name 字段值 允许为null


4 非空约束（not null)

此项有两个值 null 和 not null ,默认是null 。但是当字段为空时，数据无法参与运算。

非空约束(not null ) 确保当前列的值不为空值。

所有类型的值都可以时null，包括int，float 等数据类型

当复制时，单引号'' 或双引号"" 里什么都没有时，此时表示是0个字符，而不是null，因为nul表示没有赋值


4.1 在新建表时添加not null

 create  table class(id int , student char(20) not null)

image-20210825234434497


4.2 在建立表之后，修改字段的类型，添加not null 约束

（注意，当存在表记录时，修改为not null 的字段的值不可以存在空值）

alter table class  change id  id int not null ;

image-20210825235934010

image-20210826003544115


4.3 删除非空约束

#将 id 字段的not null 改回null
alter table class  change id  id int ;



4.4 null 和 not null 的效果

#向 class 表的 student 字段添加值 'zhangsan‘ ，id 字段不进行赋值，则默认为null
insert into class(student) value('zhangsan');   

select * from class;

image-20210826000630920



#向class 表中添加表记录，但是只给id字段赋值，不给student字段赋值，则添加失败。
insert into class(id) value(1);

image-20210826000933225



#向class 表中插入表记录，id字段赋值为2 
#student字段值虽然单引号’‘里什么都没有，但是代表的是0字符，而不是null。因为null表示的是没有进行赋值
insert into class   value( 2,'');

image-20210826001827603




5 唯一性约束（unique)(也是唯一性索引)

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。

唯一约束不允许出现重复的值，但是可以为多个null。

同一个表可以有多个唯一约束，多个列组合的约束。

在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。

唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。


5.1 创建表时设置唯一索引

方法一：在设置字段名和数据属性时，就添加唯一索引

create table 表名(字段名1 数据类型1 约束条件，....);

#创建class 表，将id 字段和 id_card 字段 值设置为unique唯一性约束，不允许字段值重复
create table class1(id int unique,name char(20),id_card bigint unique);

image-20210826010851634


方法二： 在设置完字段名和属性后，在最后在添加唯索引项

create table 表名(字段名1 数据类型1，字段名2 数据类型2，unique(字段名1,字段名2...));

create  table class2(id int ,name char(20), unique(id));
#将id 字段设置为unqiue。
#如果想象设置多个，unique（） 括号中填写过个字段名，以逗号分隔。如unique(id,name)

image-20210827103328823


5.2 在建立表之后，修改表属性，添加唯一性约束

方法一

create unique index 索引名 on 表名（列名）；

#为class 表的id 字段创建唯一键
create unique  index   class_id_UNI  on class(id);

image-20210829212113380



方法二

alter table 表名 add unique 索引名(列名)

#为class表name 字段创建唯一键

alter table class add unique class_name_UNI(name);

image-20210829212432376



方法三

通过修改字段属性或者修改字段名。来添加或者修改约束类型

alter table class modify 字段 值类型 unique；

alter table 表名 change 字段 字段 值类型 unique

#为 class表的 score 字段 添加约束类型
alter table class  modify score int unique;
或者
alter table class change score score int unique;

image-20210829212846323



5.3 删除唯一键约束

alter table 表名 drop index 唯一键名;

#查看表的详细信息。得到唯一键名为 id
show create table class;
#删除名为id的唯一键
alter table class drop index id

image-20210830103143314


6 主键约束(primary key)

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。

当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。


主键约束（主键索引）与创建唯一性约束的方法相同


6.1 创建表时设置主键索引

create  table class2(id int primary key,card_id bigint,name char(20));
 create  table class3(id int,card_id bigint,name char(20),primary key(id));

image-20210829214700298



6.2 通过修改表结构设置主键约束

#为class 表的id 字段创建主键
alter table  class add primary key(id);
或者
alter table class modify id int primary key;
或者
alter table class change id id int primary key;

image-20210829215541267



6.3 删除主键约束

alter table 表名 drop primary key；

（1） 当字段只有主键约束，没有其他约束时，可以直接删除主键

#当字段没有其他约束时，可以直接删除主键
alter table class1 drop  primary key；

image-20210830100903062



（2） 当字段有其他约束时，先删除其他约束，在删除主键

#先通过修改表字段属性，删除自增约束
alter table class modify id int ;
#再删除id 字段的主键约束
alter table class drop primary key;

image-20210830101419284




7 外键约束

外键约束是用来加强两个表（主表和从表）的一列或多列数据之间的连接的，可以保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

创建外键约束的顺序是先定义主表的主键，然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用，被约束的从表中的列可以不是主键，主表限制了从表更新和插入的操作。

主表和从表关联的字段要具有相同的数据类型，字符长度，参数类型

只有当主表中主键字段存在某个数值，从表的才可以添加相应的表记录

主表删除时表记录时，如果该项数值在从表中被使用，则需要先将从表中的占用删除，才可以在主表中删除相应的表记录


create table 表名（字段名列表, foreign key(字段名) references 表名（字段名）on update cascade on delete cascade)engin=innodb;

foreign key(字段名)：设置从表的外键字段

references 表名（字段名) ：设置关联的主表的主键字段

on update cascade : 同步更新,主表字段跟新，从表也相应的更新

on delete cascade: 同步删除，当主表的记录删除，则从表引用该字段的表记录也自动删除

engin=innodb: 设置表的存储引擎为innodb,如果默认引擎时innodb，可以省略此项



7.1 先创建主表，主键

#创建表project,并设置 id 字段为 主键
create  table project(id int  primary key,subject char(30));

#向project 表插入3条记录
insert into project(id,subject) 
    -> values
    -> (1,'chinese'),
    -> (2,'math'),
    -> (3,'English');

image-20210829222449996



7.2 创建外键

#创建表class，设置id 字段为主键，设置fail 字段为外键，关联到project表的id 字段。并且，同步更新，同步删除
create table class
    -> (id int primary key,
    -> name char(20),
    -> fail int,
    -> foreign key(fail) references project(id)
    ->  on update cascade 
    -> on delete cascade );

image-20210829225516866



7.3 测试从表与主表的关联

 #插入记录测试   
insert into  class values (1,'zhangsan',2);
insert into  class values (2,'lisi',2);
insert into  class values (3,'wangwu',3);
insert into  class values (4,'zhaoliu',4); #此条记录插入失败

image-20210829225814546



#自动同步测试
#主表将id 字段，值为2的表记录更改，将id字段值变为4，查看从表的fail 字段值
update  project  set id=4 where id=2;
 select * from class;
 
 #自动删除测试
 #主表将 id字段为 4 的表记录都删除，查看从表的表记录情况
 delete from project where id=4;
 select * from class;

image-20210829230525472

image-20210829230829046



7.4 通过修改表结构来设置外键

alter table 外键表名 add [constraint 外键名] foreign key(外键字段) references 主键表(主键表主键字段)；

image-20210829232537779



7.5 删除外键

alter table 表名 drop foreign key 外键名

alter table 表名 drop index 索引名

在创建外键约束的同时，也会创建外键索引。想要完全删除外键，需要两个一起删除


(1) 先查看外键名

#查看建表的详细信息
show create table class1;
#得到外键名为 class1_ibfk_1

image-20210830093516309



(2)删除外键约束

#删除外键  class1_ibfk_1 
alter table class1 drop foreign key class1_ibfk_1;
#查看class1表的详细信息
show create table class1;

image-20210830095007759



（3） 删除外键索引

#删除外键索引 id
alter table class1 drop index id;
#查看表class1 的详细信息
show create table class1;

image-20210830095429989




8 默认约束（default)

若在表中定义了默认值约束，用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为NULL。

设置默认值的字段，默认值的类型必须和字段的值类型一样

8.1 创建表时候设置默认约束

create table 表名(字段名 值类型 default 默认值);

#创建表class，并设置字段 address的默认值为 'not known'
create table class(id int ,name char(20) ,address  char(40) default 'not known');

#插入表记录，并且不给address 字段赋值
insert into class(id,name) values(1,'zhangsan');

select * from class;

image-20210829233905426

image-20210829234225849



8.2 通过修改 表字段来设置默认值

alter table 表名 modify 字段 值类型 default 默认值；

alter table 表名 change 字段 字段 值类型 default 默认值;

这两条命令同时也可以删除默认值。只要将default 后面的默认值设置为NULL即可

#将class 表的name 字段设置默认值为'not known'
alter table class modify name char(40) default 'not known';
#将class 表的 id 字段设置默认值为0
 alter table class change id  id int  default '0';

image-20210829234959542




9 自增约束(auto_increment)

自增约束(AUTO_INCREMENT)可以约束任何一个字段，该字段不一定是PRIMARY KEY字段，也就是说自增的字段并不等于主键字段。

但是PRIMARY_KEY约束的主键字段，一定是自增字段，即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。

当插入第一条记录时，自增字段没有给定一个具体值，可以写成DEFAULT/NULL，那么以后插入字段的时候，该自增字段就是从1开始，没插入一条记录，该自增字段的值增加1。当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，就在第一条记录该自增字段的值的基础上每次增加1。

自增约束要设置在数值类型为整形的字段上，且字段时主键，外键，唯一键的一种


9.1 在创建按表的时候，设置自增长

create table 表名(字段 数值类型 主键或外键或者唯一键 auto_increment);

#设置class 表id 字段为整型，主键，且自增长
create table class(id int  primary key auto_increment , name char(20));

#插入记录测试
insert into  class(name) values('zhangsan');
insert into  class(name) values('lisi');

image-20210830003201848

image-20210830003439965

image-20210830003944740

image-20210830004502058



9.2 修改表结构，设置字段值自增长

alter table 表名 modify 字段 int auto_increment；

alter table 表名 change 字段 字段 int auto_increment;

此命令也可以删除字段值自增长。当字段值存在自增长设置时，使用这两条命令时，不跟上auto_increment,即表示删除 字段的自增长设置

#设置 class表的id 字段自增长（id 字段必须已经设置了主键或者外键，或者唯一键，并且时整型）
alter table class modify id int auto_increment;
或者
alter table class change id id int auto_incremnet;

image-20210830005018997

image-20210830005036737




二： 索引

1 数据库索引概念

1.1 概念

  1. 索引时一个排序的列表，在这个列表中的值存储着索引的值和包含这个值的数据所在行的物理地址
  2. 使用索引后，可以不用扫描全表来定位某行数据，而是先通过索引表找到改行数据对饮的物理地址，然后访问相应的数，加快数据库的查询速度
  3. 索引可以类比为书籍的目录，可以根据目录中的页码来快速找到所需的内容。
  4. 索引时表中一列过着若干列值排序的方法
  5. 建立索引的目的是加快表记录的查找或排序

1.2 索引项和索引值

  • 索引项是数据库里面的table 中的字段；
  • 索引值是字段里面存储的数据（值）

1.3 索引的作用与副作用

  • 索引的作用
    • ​ 索引主要有两个作用，加快搜索速度和排序
  • 索引的副作用
    • 索引需要占用额外的磁盘空间
    • 在插入和修改数据时，需要花费更多的时间,因为索引也要随之改动

1.4 建立索引的原则依据

索引随可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

  • 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。
  • 记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。
  • 经常与其他表进行连接的表，在连接字段上应该建立索引。
  • 唯一性太差的字段不适合建立索引。
  • 更新太频繁地字段不适合创建索引。
  • 经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
  • 在经常进行 GROUP BY、OPDER BY 的字段上建立索引
  • 索引应该建在选择性高的字段上。
  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。
  • 在读比较多的表，适合建立索引。

1.5 数据库查询的过程

  1. 在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行
  2. 没有有索引的情况下扫描全表来定位某行的数据


2 索引的分类

索引有普通索引，唯一索引，主键索引，外键索引，组合索引，全文索引。

唯一键约束就是唯一索引(unique key)，

主键索引(primary key)，外键索引(foreign key) ,在创建主键约束，外键约束时候，会自动创建




3 普通索引

普通索引时最基本的索引类型，没有唯一性之类的限制


方法一： 直接创建索引

create index 索引名 on 表名(列名(length));

（列名（length））: length 是可选项。如果忽略length 值，则使用整个列的值作为索引。如果指定，使用列的前 length 个字符来创建索引，有利于减小索引文件的大小。在不损失精度的情况下，长度越短越好。

索引名：普通索引的索引名建议以 " _index" 为结尾


方法二： 修改表的方式创建

alter table 表名 add index 索引名(列名);


方法三：创建表的时候指定索引

create table 表名（字段名 数据类型 ......, index 索引名(列名);



3.1 创建普通索引

#创建表的时候，为score 字段设置索引
create table class(id int, name char(30),score int ,index class_score_index(score));

#修改表的方式，为name 字段创建索引
 alter table class add index class_name_index(name);
 
 #直接为class表id 字段创建索引
 create index class_id_index on class(id);

image-20210830105016223



3.2 查看索引

show index form 表名;

#查看表class 的索引
show index from class \G

image-20210830105509308




4 组合索引

组合索引（单列索引与多列索引）:可以是单列上创建的索引，也可以在多列上创建的索引。需要满足最左原则。因为select 语句的where 条件是依次从左向右执行的，所以在使用select语句查询时，where 条件的使用字段顺序必须和组合索引中的排序一致，否则索引不会生效


create table 表名 (字段名 属主类型 ......, index 索引名(列名1，列名2...));


#创建class 表，并将 id,name,score 字段设置为组合索引
create table class(id int,name char(20),score int, index class_MUL_index(id,name,score));

image-20210830110952579




5 全文索引

全文索引（fulltext) 适合在模糊查询时候使用，可以用于一篇文章中检查文本信息。

在mysql 5.6 之前只能用于MyISAM 引擎，5.6 之后，可以用于InnoDB 引擎。

全文索引可以子啊char，varchar 后者text 类型的列上创建。

每个表只允许有一个全文索引


方法一：直接创建全文索引

create fulltext index 索引名 on 表名(列名);


方法二： 创建表时候指定索引

create table 表名(字段名 数据类型 ..... ,fulltext 索引名(字段名));


方法三： 修改表方式创建

alter table 表名 add fulltext 索引名 on 表名(字段名)；


5.1 创建全文索引

 create table class(id int,name char(30),
    ->  tel bigint ,
    -> address varchar(50),
    -> info varchar(100),
    -> fulltext textindex(info));

image-20210830115045762



5.2 模糊查询匹配

select * from 表名 where match(字段名) against('匹配内容')；

 #插入三条表记录
 insert into class values (1,'zhangsan',1010110,'china','this is vip'),
    -> (2,'lisi',1010110,'china','this is super vip'),
    -> (3,'wangwu',1010110,'china','this is  vvip');

#在clss 表的info 字段，查询匹配 ‘vip’ 的表记录
select * from class where match(info) against('vip');

image-20210830115430291




6 查看索引

show index from 表名;

show key from 表名;


 show index from class \G

image-20210830115826209


字段 含义
Table ：表的名称

Non_unique ：如果索引不能包括重复词，则为 0；如果可以，则为 1

Key_name： 索引的名称

Seq_in_index： 索引中的列序号，从 1 开始

Column_name： 列名称

Collation ：列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）

Cardinality ：索引中唯一值数目的估计值

Sub_part： 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，为 NULL

Packed： 指示关键字如何被压缩。如果没有被压缩，则为 NULL

Null ：如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO

Index_type： 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）

Comment： 备注




7 删除索引

直接删除索引

drop index 索引名 on 表名;


修改表的方式删除索引

alter table 表名 drop index 索引名;


删除主键索引

alter table 表名 drop primary key



#查看表的详细信息，获取索引名
show create table class \G

#删除索引
drop index textindex on class;

image-20210830120550572



这篇关于mysql 数据库约束条件与索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程