编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255),
    unique index id_city_index(id,city)-- 在创建表时创建索引;为表的id和city字段建立复合唯一索引id_city_index
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州');
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州');
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');

create index name_index on student(name(5) desc);-- 普通索引;这里也可以直接写name
create unique index id_name_index on student(id,name);-- 创建唯一复合索引
-- 使用alter table语句和drop index 语句删除指定索引
alter table student drop index name_index;-- 有多个表时:drop index 索引名 on 表名;


show index from student;-- 不能这样写:show index id_city_index from student;