一、创建和查看索引
1、创建和查看普通索引
普通索引:创建索引时,不附加任何限制条件(唯一、非空等限制)。该类型的索引可以创建在任何数据类型的字段上。
(1)创建表时创建普通索引
【语法形式】
create table table_name(
属性名1 数据类型,
属性名2 数据类型,
属性名3 数据类型,
index|key 索引名(属性名1 【(长度)】 【ASC|DESC】)
);
【实例】
#选择数据库
mysql> use test;
#创建索引
mysql> create table t_deptest(
-> deptno int,
-> dname varchar(20),
-> index index_deptno(deptno)
-> );
#校验索引是否创建成功
mysql> show create table t_deptest \G
#校验索引是否被使用
mysql> explain select * from t_deptest where deptno=1\G
注:\G后边不跟分号。否则会有no query specified错误
(2)在已经存在的表上创建普通索引
【语法形式】
create index 索引名 on 表名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> create index index_deptno on t_dept(deptno);
(3)通过SQL语句ALTER TABLE 创建普通索引
【语法形式】
alter table table_name add index 索引名(属性名 【(长度)】 【ASC|DESC】)
【实例】
mysql> alter table t_dept add index index_deptno(deptno);
2、创建和查看唯一索引
所谓唯一索引,就是在创建索引时,限制索引的值必须是唯一的,通过该类型的索引可以更快速的查询某条记录。根据创建索引方式,可以分为自动索引和手动索引两种。
- 自动索引:在数据库中设置完整性约束时,该表会被自动创建索引;
- 手动索引:手动在表上创建索引;
(1)创建表时创建唯一索引
【语法形式】
create table table_name(
属性名 数据类型,
属性名 数据类型,
unique index 索引名(属性名 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create table t_dept1(
-> deptno int unique,
-> dname varchar(20),
-> unique index index_deptno(deptno)
-> );
(2)在已经存在的表上创建唯一索引
【语法形式】
create unique index 索引名 on 表名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> create unique index index_deptno on t_dept1 (deptno) ;
(3)通过SQL语句ALTER TABLE 创建唯一索引
【语法形式】
alter table table_name add unique index|key 索引名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> alter table t_dept add unique index index_deptno(deptno);
3、创建和查看全文索引
全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上,以便能够快速查询数据量较大的字符串类型的字段。只能在存储引擎为MyISAM的数据库表上创建全文索引。
(1)创建表时创建全文索引
【语法形式】
create table table_name(
属性名1 数据类型,
属性名2 数据类型,
属性名3 数据类型,
fulltext index|key 索引名(属性名 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create table t_dept3(
-> deptno int,
-> dname varchar(20),
-> fulltext index index_dname(dname)
-> ) ENGINE=MyISAM;
(2)在已经存在的表上创建全文索引
【语法形式】
create fulltext index 索引名 on 表名 (属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> create fulltext index index_name on t_dept3(dname);
(3)通过SQL语句ALTER TABLE 创建全文索引
【语法形式】
alter table table_name add fulltext index 索引名(属性名 【(长度)】 【ASC|DESC】);
【实例】
mysql> alter table t_dept3 add fulltext index index_doname(dname);
4、创建和查看多列索引
所谓多列索引,是指在创建索引时,所关联的字段不是一个字段,而是多个字段。虽然可以通过所关联的字段查询,但是只有查询条件中使用了所关联字段中的第一个字段,多列索引才会被使用。
(1)创建表时创建多列索
【语法形式】
create table table_name(
属性名 数据类型,
属性名 数据类型,
index|key 索引名 (属性名1 【(长度)】 【ASC|DESC】,
…………
属性名m 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create table t_dept4(
-> deptno int,
-> dname varchar(20),
-> loc varchar(20),
-> key index_dname_loc(dname,loc)
-> );
(2)在已经存在的表上创建多列索引
【语法形式】
create index 索引名 on 表名(属性名1 【(长度)】 【ASC|DESC】,
…………
属性名m 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> create index index_dname_loc on t_dept4(dname,loc);
(3)通过SQL语句ALTER TABLE 创建全文索引
【语法形式】
alter table table_name add index|key 索引名(属性名1 【(长度)】 【ASC|DESC】,
…………
属性名m 【(长度)】 【ASC|DESC】)
);
【实例】
mysql> alter table t_dept4 add index index_dname_loc(dname,loc);
5、删除索引
【语法】
drop index index_name on table_name
【实例】
mysql> drop index index_dname_loc on t_dept;