【MYSQL】索引操作语法

索引的创建

1. 使用CREATE INDEX语句创建索引

   CREATE [UNIQUE] INDEX idx_name ON tbl_name (idx_col_name)

    

2. 使用CREATE TABLE语句创建索引

   [CONSTRAINT [symbol]] PRIMARY KEY [idx_name] (idx_col_name)
   
   {INDEX|KEY} [idx_name] (idx_col_name)
   
   [CONSTRAINT [symbol]]  UNIQUE [INDEX|KEY] [idx_name] (idx_col_name)
   
   [CONSTRAINT [symbol]] FOREIGN KEY [idx_name] (idx_col_name)

    

3. 使用ALTER TABLE语句创建索引

   ADD {INDEX|KEY} [idx_name] (idx_col_name)
   
   ADD [CONSTRAINT [symbol]] PRIMARY KEY (idx_col_name)
   
   ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [idx_name] (idx_col_name)
   
   ADD [CONSTRAINT [symbol]] FOREIGN KEY [idx_name] (idx_col_name)

    

索引的查看

SHOW {INDEX|INDEXED|KEYS}
    {FROM|IN} tbl_name
    [{FROM|IN} db_name]
    [WHERE expr]

索引的删除

1. 使用DROP INDEX 语句删除索引

   DROP INDEX idx_name ON tbl_name

    

2. 使用ALTER TABLE语句删除索引

   ALTER TABLE tbl_name DROP PRIMARY KEY
   
   ALTER TABLE tbl_name DROP INDEX idx_name
   
   ALTER TABLE tbl_name DROP FOREIGN KEY idx_name