数据库索引键uk_索引操作语法

# 索引操作语法

索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。

## 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， 用于全文索引

~~~