SQL学习四、MYSQL索引的创建与删除

【SQL学习笔记】四、MYSQL索引的创建与删除
一、索引类型
1、普通索引
2、唯一索引
3、单列索引
4、多列索引
5、全文索引
6、空间索引
二、创建索引
1、普通索引

mysql> create table putong_index(id int,
    -> name varchar(20),
    -> sex boolean,
    -> index(id));

2、唯一索引

mysql> create table weiyi_index(id int,
    -> name varchar(20),
    -> unique index weiyisuoyin(id asc));

3、全文索引

mysql> create table quanwen_index(id int,
    -> address varchar(20),
    -> fulltext index quanwensuoyin(address))engine MyISAM;

4、空间索引

mysql> create table kongjian_index(id int,
    -> space geometry not null,
    -> spatial index kongjiansuoyin(space))engine MyISAM;

三、在已存在表中创建索引
已存在表students:

1、创建普通索引

 create index id_index on students(id asc);

2、创建唯一索引

create unique index stu_id_index on students(stu_id);

3、创建全文索引

 create fulltext index stu_email_index on students(stu_email);

4、创建空间索引

create spatial index address_index on students(address);

5、修改表结构来添加索引

 alter table students add fulltext index stu_name_index(stu_name);

四、删除索引

drop index stu_name_index on students;