MYSQL_12_ Index Classification _ create _ Delete

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.