mysql SQL语言-创建/查看/删除索引

一、创建索引

1、 创建表的时候创建索引

mysql> create table student(
-> id bigint(20) not null auto_increment,
-> name char(20) not null,
-> age tinyint(3) not null default '0',
-> dept varchar(30) not null,
-> primary key(id),  -- 主键索引
-> key idx_name(name)); -- 普通索引

2、创建索引方式一

2.1 语法

普通索引 ： alter table <表名> add index <索引名称>(<字段名称>);
唯一索引 ： alter table <表名> add unique index <索引名称>(<字段名称>);
普通组合索引： alter table <表名> add index<索引名称>(<字段名称>,<字段名称>,...);
唯一组合索引：  alter table <表名> add unique  index<索引名称>(<字段名称>,<字段名称>,...);

2.2 案例

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table student add index idx_name(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc
-> student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3、创建索引方式二：

3.1 语法

普通索引： create index <索引名称> on <表名>(<表中字段>);
唯一索引： create  unique  index <索引名称> on <表名>(<表中字段>);
普通组合索引： create index <索引名称> on <表名>(<表中字段>,<字段名称>,...);
唯一组合索引： create  unique   index <索引名称> on <表名>(<表中字段>,<字段名称>,...);

3.2 案例

mysql> desc
-> student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> create index idx_dept on student(dept);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

二、查看索引

1、方式一：desc <表名>

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

PRI ：主键索引标识，MUL：普通索引标识，UNI：唯一索引标识

2、 方式二：show index from <表名>

mysql> show index from student \G;
*************************** 1. row ***************************
        Table: student
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: student
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: student
   Non_unique: 1
     Key_name: idx_dept
 Seq_in_index: 1
  Column_name: dept
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

三、删除索引

1、方式一：alter table <表名> drop index <索引名称>

1.1 语法

alter table <表名> drop index <索引名称>;

1.2 案例

	mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> alter table student drop index idx_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| age   | tinyint(3)  | NO   |     | 0       |                |
| dept  | varchar(30) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

2、方式二:drop index <索引名称> on <表名>

2.1 案例

mysql> drop index idx_name_dept on student;    
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0