# 索引操作语法 索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。 ## 1.准备环境 ```SQL create database demo_01 default charset=utf8mb4; use demo_01; CREATE TABLE `city` ( `city_id` int(11) NOT NULL AUTO_INCREMENT, `city_name` varchar(50) NOT NULL, `country_id` int(11) NOT NULL, PRIMARY KEY (`city_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `country` ( `country_id` int(11) NOT NULL AUTO_INCREMENT, `country_name` varchar(100) NOT NULL, PRIMARY KEY (`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2); insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1); insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1); insert into `country` (`country_id`, `country_name`) values(1,'China'); insert into `country` (`country_id`, `country_name`) values(2,'America'); insert into `country` (`country_id`, `country_name`) values(3,'Japan'); insert into `country` (`country_id`, `country_name`) values(4,'UK'); ``` ## 2.创建索引 语法 ： ```sql CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name(index_col_name,...) index_col_name : column_name[(length)][ASC | DESC] ``` 示例 ： 为city表中的city_name字段创建索引 ； ```sql mysql> create index city_name_index on city(city_name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` ## 3.查看索引 查看刚刚建立的索引。 ~~~ mysql> show index from city\G; *************************** 1. row *************************** Table: city Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: city_id Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: city Non_unique: 1 Key_name: city_name_index Seq_in_index: 1 Column_name: city_name Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.00 sec) ~~~ ## 4. 删除索引 语法 ： ``` DROP INDEX index_name ON tbl_name; ``` 示例 ： 想要删除city表上的索引city_name，可以操作如下： ~~~ mysql> drop index city_name_index on city; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 ~~~ ## 5. ALTER命令 ~~~ 1). alter table tb_name add primary key(column_list); 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL 2). alter table tb_name add unique index_name(column_list); 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次） 3). alter table tb_name add index index_name(column_list); 添加普通索引， 索引值可以出现多次。 4). alter table tb_name add fulltext index_name(column_list); 该语句指定了索引为FULLTEXT， 用于全文索引 ~~~