MySQL index and DELETE index SQL statement detailed

Create and delete indexes

The creation of the primer can be done in the CREATE TABLE statement, or you can add indexes to the table separately with the CREATE INDEX or ALTER TABLE. The deletion index can be implemented using ALTER TABLE or DROP INDEX statements.
(1) Use the ALTER TABLE statement to create an index.
The syntax is as follows:

The code is as follows	Copy Code
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, which contains 3 forms of index creation, TABLE_NAME is the table name to be indexed, column_list indicates which columns are indexed, and columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name to the first indexed column. In addition, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.
An example of creating an index is as follows:

The code is as follows	Copy Code
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 normal and unique indexes. The format is as follows:

The code is as follows	Copy Code
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 the ALTER TABLE statement, and the index name is not selectable. In addition, you cannot create a primary key index with the CREATE INDEX statement.
(3) Delete index.
The deletion index can be implemented using ALTER TABLE or the DROP INDEX statement. DROP index can be handled as a statement inside ALTER TABLE in the following format:

The code is as follows	Copy Code
DROP INDEX index_name on table_name; ALTER TABLE table_name DROP INDEX index_name; ALTER TABLE table_name drop PRIMARY key;

Of these, in the previous two statements, the index index_name in table_name was deleted. In the last statement, it is only used in the deletion of the primary key index, because a table can 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 you delete a column from the table, the index is affected. For indexes of multiple-column combinations, if you delete one of the columns, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.
Delete an index operation, such as the following code:

The code is as follows	Copy Code
Mysql> DROP index Shili on TPSC; Query OK, 2 rows affected (0.08 sec) Records:2 duplicates:0 warnings:0