You can create an index using either of the following methods:

You can create an index on an existing table by creating an index at the same time.

Method 1: Create an index when creating a table.

When using this method to create an index, you can create multiple indexes (such as unique index, common index, and composite index) for a table at a time ), the syntax format is basically the same as the created syntax format (pay attention to the Code in the bold part ).

Create Table Name (

Field name 1 Data Type [constraints]

Field name 2 Data Type [constraints]

...

[Unique] [Fulltext] index [Index name](Field name [(Length)] [ASC | DESC])

) Engine = storage engine type default charset = Character Set Type

Note: "[]" indicates an option, "|" in "[]" indicates that each option is separated, and "()" indicates a required option.

The Length indicates the character length of the keyword in the index. The value of the keyword can be a part of the value of the field in the database table. This index is called "prefix Index ".

ASE and desc are optional parameters, indicating the ascending and descending order, respectively. However, these two optional parameters do not actually work, and the values of all keywords in the index are stored in ascending order.

 

Use the following SQL statement to create a book table with the storage engine MyISAM and the default Character Set GBK, the primary key ISBN, name of the title, brief introduction brief_intorduction, Price, and publication time publish_time are defined, the unique index isbn_unique, general index name_idex, full-text index brief_fulltext, and composite index complex_index are defined in the table.

Create Table Book (

ISBN char (20) primary key,

Name char (100) not null,

Brifef_introduction text not Noll,

Price decimal (6, 2 ),

Publish_time date not null,

Unique index isbn_unique (ISBN ),

Index name_index (name (20 )),

Fulltext index brief_fulltext (name, brief_introduction ),

Index complex_index (price, publish_time ),

) Engine = MyISAM default charset = GBK;

 

Method 2: Create an index on an existing table

There are two statement formats for creating an index on an existing table. The common feature of this statement syntax format is to guide the table on which to create an index. The syntax format is as follows:

Syntax format 1:

Create [unique | Fulltext] Index name on table name (field name [(length)] [ASE | DESC])

Syntax Format 2:

Alter table table name Add [unique | Fulltext] Index name (field name [(length)] [ASE | DESC])

For example, to add a full-text index to the course description Description field in the Course course table, you can use the following SQL statement:

Alter table coursr add fulltexr index description_fulltext (description );

This statement is equivalent:

Create Fulltext index description on course (description );

Delete table Index

The SQL statement is:

Drop Index name on table name