【十八，MySQL】进阶篇--索引语法

1，创建索引

create [unique | fulltext] index index_name on table_name (index_col_name,……);

2，查看索引

show index from table_name;

3，删除索引

drop index index_name on table_name;

create table tb_user(
    id int primary key auto_increment,
    name varchar(10),
    phone varchar(11),
    email varchar(50),
    profession varchar(50) comment '专业',
    age int ,
    gender char(1) ,
    status int,
    createtime date
)comment '用户表'charset = utf8;

drop table tb_user;
select * from tb_user;

insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(2,'曹操','13512341232','cao-cao@qq.com','通讯工程',26,'男',1,'2020-02-12');

insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) values
(1,'吕布','13512341234','bu-lv@qq.com','软件工程',25,'男',1,'2021-01-05');

# 1，name字段为名字，该字段的值可能会重复，为该字段创建索引 创建的是常规索引，不需要在index前指定，在查看索引时，索引类型也是默认BTREE(其实是B+TREE)
create index index_name on tb_user(name);

# 2，phone字段为手机号，非空，且唯一，为该字段创建索引
create unique  index index_phone on tb_user(phone);

# 3，为profession,age,status创建联合索引，字段的顺序有讲究
create index lianhe_name on tb_user(profession,age,status);

# 4，为email建立合适的索引来提升查询的效率
create index index_email on tb_user(email);

# 查看索引
show index from tb_user;
# 删除索引
drop index index_name on tb_user;