mysql约束及索引(创建及使用)

约束

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。

在mysql中约束有:

主键约束

唯一约束

非空约束

外建约束

1. 主键约束
   • 含义：唯一约束+为空约束，该字段上的数据不允许重复，也不允许为null
   • 添加主键约束 - - 创建表时添加(推荐)

# 创建表时添加主键
create table student(
	id int(3) primary key,
	name varchar(32)
)
# 添加主键约束
alter table student add primary key(id);
# 删除主键约束
alter table student drop primary key;

2. 唯一约束
   • 含义：该字段上的数据不允许重复，但是可以允许为null

# 添加唯一约束
alter table student add unique(id_card);
# 删除唯一约束
alter table student drop index (id_card);

3. 非空约束
   • 含义：该字段上的约束不允许为null

# 添加非空约束
alter table student modify name varchar(32) not null;
# 删除非空约束
alter table student modify name VARCHAR(32) null;

4. 外建约束
   • 外键约束（不使用，因为更新数据时，还要去查询该外键所属表中是否有该数据，效率低）
   • 含义：外键约束是保证一个或两个表之间的参照完整性，
   • 外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

# 添加外键约束
alter table student add foreign key(course_id) references course(id);

# 删除外键约束(外键名由show create TABLE student;查询出)
alter table  drop foreign key student_ibfk_1;

索引

索引其实就类似于一本书的目录，让查询变得更快

在mysql索引有:

主键索引

唯一索引

普通索引

全文索引

1. 主键索引
   • 含义：唯一约束+为空约束，该字段上的数据不允许重复，也不允许为null

# 添加主键索引
alter table 表名 add primary key(索引字段);
# 删除主键索引
alter table 表名 drop index 索引字段;

2. 唯一索引
   • 含义：该字段上的数据不允许重复，但是可以允许为null

# 添加唯一索引
alter table 表名 add unique(索引字段);
# 删除唯一索引
alter table 表名 drop index 索引字段;

3. 普通索引
   • 含义：没有主键索引和唯一索引对于字段的数据的约束，就是单单纯纯的查询快

# 添加普通索引
ALTER TABLE student ADD INDEX(name);
# 删除普通索引
drop index name on student;

4. 全文索引
   • 注意：不用
   • 索引的数据结构（InnoDB引擎）：B+Tree

总结:

1) 索引的优缺点:

• 优点: 查询快
• 缺点: 更新数据时，索引的数据结构跟着改变

2) 索引的分类：

• 聚簇索引：主键索引
• 非聚簇索引：唯一索引、普通索引、全文索引

3) 哪些字段该添加索引？

• 表中的唯一数据作为主键索引
• 表中经常被查询的字段作为唯一索引或普通索引

经验：查询数据中的条件尽可能使用索引

4) 索引什么时候会失效

1. 查询条件用到索引时，MySQL会判断是使用索引更快还是使用顺序查找更快，如果顺序查找更快就不使用索引
2. 联合索引时，第一个索引不满足需求，就不会找第二个索引
3. 使用模糊查询时底层不使用索引