以Mysql
一、索引和约束的区别?
1.作用不同
约束的作用:
作用:是为了保证数据的完整性而实现的摘自一套机制,它具体的根据各个不同的数据库的实现而有不同的工具(约束);
索引的作用:
作用:快速定位特定数据,提高查询效率,确保数据的唯一性,快速定位特定数据;可以加速表和表之间的连接,实现表与表之间的参照完整性,使用分组和排序语句进行数据检索时,可以显著减少分组和排序的时间全文检索字段进行搜索优化;
2.生效规则不同
1、约束是在事务结束的时候执行;
2、索引是立即执行,而不管事务状态或引擎的日志模式。
在使用约束的时候,不要删除约束创建的索引。不要试图通过sysconstraints表的数据行来删除约束,一定要通过alter语句来实现。
索引可以转变成约束,但是反过来不可以。你可以在一个表上先创建唯一索引,然后再创建约束,反过来却不可以。
在上面的方法创建的约束中,应该先删除约束,然后再删除索引。
直接说结论:
1、索引是索引,约束是约束。创建唯一约束时,会自动创建唯一索引;创建唯一索引时,不会创建唯一约束。
2、允许在约束上建立外键,不允许在索引上建立外键。
二、索引和约束的使用(创建和删除)
1.索引
1.1主要的索引有:
1、主键索引(primary key)
2、唯一索引(unique)
3、常规(普通)索引(index/key)
4、全文索引(fulltext)
1.2‘创建’索引的主要方式有一下三种:
1.在创建表示创建索引(通常是创建主键时使用)
如,创建一张学生表 其中, KEY name (first_name,last_name) 给这两个字段添加了‘普通索引’
CREATE TABLE student(
id int auto_increment PRIMARY KEY,
first_name VARCHAR(16),
last_name VARCHAR(16),
id_card VARCHAR(20),
information text,
INDEX name (first_name,last_name)
);
我们也可以通过 show index from '表名' 来查看对应表的索引情况,如下:
show index from student;
2.使用CREATE INDEX 命令创建索引
CREATE INDEX index_name ON table_name (column_list);
其中 ‘index_name’ 索引名,‘table_name’ 表名 , ‘column_list’ 字段名
还是对于Student表,来使用此方法创建一个唯一索引:
CREATE UNIQUE INDEX unique_name ON student(id_card);
注意:CREATE 这种方式可对表增加普通索引或 UNIQUE 索引。(但是,不能创建 PRIMARY KEY 索引)
3.使用 ALTER TABLE 命令去增加索引
格式如下:
ALTER TABLE table_name ADD INDEX index_name (column_list);
例子:
给Student表的 ‘ information’ 字段添加‘全文索引’
ALTER TABLE student ADD FULLTEXT INDEX i_name (information);
注意:‘ALTER TABLE’ 这种添加字段的方式相对‘CREATE INDEX’ 是允许添加 ‘主键索引’的。
1.3索引的删除方式(常见两种)
1.与CREATED INDEX 相对应的
DROP INDEX index_name ON table_name;
2.与ALTER TABLE 相对应的
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
1.4索引失效
注意:当使用like时,%在前时,索引失效。
SELECT id FROM student WHERE last_name LIKE '龙%'; --这个查询会使用索引(有索引的前提)
SELECT id FROM student WHERE last_name LIKE '%龙'; --这个查询不会使用索引(无论有无索引)
2.约束
2.1主要的约束有(Mysql)
1、非空约束:not null;
指示某列不能存储 NULL 值
2、唯一约束:unique();uk
unique约束的字段,要求必须是唯一的,但null除外;
3、主键约束:primary key();pk
主键约束=not null + unique,确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
4、外键约束:foreign key () references (参考),fk
保证一个表中的数据匹配另一个表中的值的参照完整性。语法:constraint fk_dept_id foreign key (dept_id) references dept(dept_id);
5、自增约束:auto_increment
6、默认约束:default
给定默认的值
7、检查性约束:check
保证列中的值符合指定的条件。
注意:
在添加 ‘唯一约束’和‘主键约束’的时候会自动创建‘唯一索引’和‘主键索引’,而且随着自动创建的‘唯一索引’和‘主键索引’是不能单独删除的,必须删除约束(跟着约束一起自动删除)。
2.2约束的使用(创建和删除)常见两种方式
方式一:
CREATE TABLE teacher(
id int AUTO_INCREMENT , #这里添加了自增约束
phone BIGINT NOT NULL , #非空约束
email VARCHAR(50),
#----下列为后续添加 用 CONSTRAINT
category VARCHAR(50) DEFAULT '普通老师', #默认值约束
CONSTRAINT u_name unique(email), #唯一约束
CONSTRAINT p_k PRIMARY KEY (id), #主键约束
CONSTRAINT f_id FOREIGN KEY teacher(id) #外键约束
REFERENCES student(id)
);
方式二:'ALTER TABLE'
1.外键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key
2.非空约束
添加:alter table table_name modify 列名 数据类型 not null
删除:alter table table_name modify 列名 数据类型 null
3.唯一约束
添加:alter table table_name add unique 约束名(字段)
删除:alter table table_name drop key 约束名
4.自动增长
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int
5.外键约束
添加:alter table table_name add constraint 约束名 foreign key(外键列)
references 主键表(主键列)
删除:
第一步:删除外键
alter table table_name drop foreign key 约束名
第二步:删除索引
alter table table_name drop index 索引名
约束名和索引名一样
6.默认约束
添加:alter table table_name alter 列名 set default '值'
删除:alter table table_name alter 列名 drop default
注意:
外键约束添加的条件有,1.主表必须事先存在; 2.主表必须有主键; 3.外键中列的数目必须和主表中主键的列队数目一样; 4.主表的主键不允许为空。
总结
1.约束是为了保证表数据的完整性,索引是为了提高查询效率,两者作用不一样。其次种类也不一样。
2.ALTER ABLE相对 CREATE INDEX 这种添加索引的方式,是可以允许添加主键索引的。
3.查询时使用LIKE的时候,%在前会导致索引失效。
4.要添加外键约束的话要遵循创建条件。
5.要删除外键约束的话,要先删除外键再删除索引(索引和外键约束同名)。
6.删除关联的表的主表的前提要删除外键所在的表。