Shulou(Shulou.com)06/01 Report--

The index can be created in a CREATE TABLE statement, or you can add an index to the table using CREATE INDEX or ALTER TABLE alone. Deleting an index can be done using ALTER TABLE or DROP INDEX statements.

(1) use the ALTER TABLE statement to create the index.

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)

It includes normal index, UNIQUE index and PRIMARY KEY index. Table_name is the table name to increase the index, column_list indicates which columns are indexed, and the columns are separated by commas when there are multiple columns. The index name index_name is optional, and by default, MySQL assigns a name based on the first index 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.

Examples of creating an index are as follows:

Mysql > alter table table_test add index index_test1 (name)

Query OK, 2 rows affected (0.08 sec)

(2) add an index to the table using the CREATE INDEX statement.

Can add normal index and UNIQUE index. The format is as follows:

Create index index_name on table_name (column_list)

Create unique index index_name on table_name (column_list)

Examples of creating an index are as follows:

Mysql > create index index_test2 on table_test (age)

Query OK, 2 rows affected (0.08 sec)

Note: table_name, index_name, and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not optional. In addition, PRIMARY KEY indexes cannot be created with CREATE INDEX statements.

(3) delete the index.

Deleting an index can be done using ALTER TABLE or DROP INDEX statements. DROP INDEX can be processed as a statement within 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

Where, in the previous two statements, the index index_name in table_name is deleted. In the last statement, it is only used when deleting a PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so you do not need to specify an index name. If no PRIMARY KEY index is 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 an index with a combination of columns, if you delete one of the columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

Delete the index, as in the following code:

Mysql > drop index name on table_test

Query OK, 2 rows affected (0.08 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.