command statements for creating indexes, modifying indexes, and dropping indexes, MySQL index

View table already exists Index:show index from table_name;

Creating and deleting indexes
The creation of the index can be done in the CREATE TABLE statement, or you can add indexes to the table by using the CREATE INDEX or ALTER TABLE alone. Deleting an index can be accomplished using ALTER TABLE or the DROP INDEX statement.

(1) Create an index using the ALTER TABLE statement.
The syntax is as follows:
ALTER TABLE table_name ADD index index_name (column_list);
ALTER TABLE table_name add unique (column_list);
ALTER TABLE TABLE_NAME ADD PRIMARY key (column_list);
These include the normal index, the unique index, and the primary key index 3 create indexes, TABLE_NAME is the table name to increase the index, column_list indicates which columns are indexed, and columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name based on the first indexed column. In addition, ALTER TABLE allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.
An example of creating an index is as follows:
Mysql> Use TPSC
Database changed
Mysql> ALTER TABLE TPSC Add index Shili (TPMC);
Query OK, 2 rows affected (0.08 sec)
Records:2 duplicates:0 warnings:0

(2) Use the CREATE INDEX statement to add an index to the table.
can increase both ordinary and unique indexes. The format is as follows:
CREATE INDEX index_name on table_name (column_list);
Create unique index index_name on table_name (column_list);
Description: TABLE_NAME, index_name, and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not selectable. In addition, the primary key index cannot be created with the CREATE INDEX statement.

(3) Delete the index.
Deleting an index can be accomplished by using the ALTER TABLE or the DROP INDEX statement. DROP index can be handled as a statement inside the ALTER TABLE, in the following format:
DROP INDEX index_name on table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name drop PRIMARY key;
In the preceding two statements, the index index_name in table_name is removed. In the last statement, it is only used in the Delete primary key index, because a table may have only one primary key index, so you do not need to specify the index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index.
If a column is removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.
Delete the index operation, as in the following code:
Mysql> DROP index Shili on TPSC;
Query OK, 2 rows affected (0.08 sec)
Records:2 duplicates:0 warnings:0
The statement removes the index that was previously created with the name "Shili".

MySQL index, create index, modify index, delete index command statement