1 UseDb_book;2 --Section Fourth: Index classification3 --1. General Index4 --Such indexes can be created in any data type;5 --2. Uniqueness Index6 --use the unique parameter to set the limit that the value of the index must be unique when creating a unique index;7 --3. Full-Text Indexing8 --use the fulltext parameter to set, and the full-text index can only be created on fields of type Char,varchar,text;9 --The main function is to improve the speed of querying large string types; only the MyISAM engine supports the index, which is not supported by the MySQL default engine. Ten --4. Single-Column indexing One --in a table, you can create an index for a single field, a single-column index can be a normal index, or it can be unique, or it can be a full-text index; A --5. Multi-column index - --A multicolumn index is an index that is created on more than one field in a table. - --6. Spatial index the --Spatial indexes can be set using spatial parameters, and spatial indexes can only be built on spatial data types, which improves the efficiency of system acquiring spatial data . - --This index is supported only by the MyISAM engine, which is not supported by the MySQL default engine. - - + --Section Fifth: Creating an index - --5.1 Creating an index when creating a table + --format: A --CREATE Table Table name (property name data type [integrity constraint], at --Property name data type [integrity constraint], - -- ... - --Property name Data type - --[UNIQUE | Fulltext | SPATIAL]index KEY - --[Alias] (property name 1 [(length)][ASC | DESC]) - -- ); in - --1. Create a normal index to CREATE TABLET_user1 (IDINT, +UserNameVARCHAR( -), -PasswordVARCHAR( -), the INDEX(UserName) * ); $ Panax Notoginseng --2. Create a Uniqueness Index - CREATE TABLET_user2 (IDINT, theUserNameVARCHAR( -), +PasswordVARCHAR( -), A UNIQUE INDEXindex_username (userName) the ); + - --3. Create a full-text index $ --The default engine does not support $ - --4. Create a single-column index - --both 1 and 2 are single-column indexes the - --5. Creating Multi-column IndexesWuyi CREATE TABLET_user5 (IDINT, theUserNameVARCHAR( -), -PasswordVARCHAR( -), Wu UNIQUE INDEXIndex_username_password (Username,password) - ); About $ --6. Create a spatial index - --The default engine does not support - - --5.2 Creating an index on a table that already exists A --format: + --CREATE [unique| Fulltext| Spatial]index index name on table name (property name [(length)][asc| DESC]); the CREATE TABLET_user5_2 ( -Idint PRIMARY KEYAuto_increment, $UserNameVARCHAR( -), thePasswordVARCHAR( -) the ); the the CREATE INDEXIndex_username onT_user5_2 (UserName);--Normal Index - CREATE UNIQUE INDEXIndex_username2 onT_user5_2 (UserName);--Uniqueness Index in CREATE INDEXIndex_username_password onT_user5_2 (Username,password);--Multi-column index the the --5.3 Creating an index with the ALTER TABLE statement About --format: the --ALTER Table name add[unique| Fulltext| Spatial]index Index Name (property name [(length)][asc| DESC]); the CREATE TABLET_user5_3 ( theIdint PRIMARY KEYAuto_increment, +UserNameVARCHAR( -), -PasswordVARCHAR( -) the );Bayi the ALTER TABLET_user5_3ADD INDEXIndex_user (UserName);--Normal Index the ALTER TABLET_user5_3ADD UNIQUE INDEXIndex_user2 (UserName);--Uniqueness Index - ALTER TABLET_user5_3ADD INDEXIndex_user_password (Username,password);--Multi-column index - the --section Sixth: Deleting an index the --format: the --DROP index index name on table name; the DROP INDEXUserName onT_user1; - DROP INDEXIndex_username onT_user2; the DROP INDEXIndex_username_password onT_user5; the the --Delete a table94 DROP TABLET_user1; the DROP TABLET_user2; the DROP TABLET_user5; the DROP TABLET_user5_2;98 DROP TABLET_user5_3;
MYSQL_12_ Index Classification _ create _ Delete