MySQL Index overview

MySQL Index overview
Advantages of the index:
1. By creating a unique index, you can guarantee the uniqueness of each row of data in a database table.
2. You can speed up the retrieval of data.
Index Disadvantage:
1. When the data in the table is added, deleted and modified, the index is also maintained dynamically, reducing the maintenance speed of the data.
2. The index needs to occupy physical space.

Type of index:
1.INDEX: Normal Index
2.PRIMARY Key: Primary key
3.UNIQUE: Unique index
4.FOREIGN Key: Foreign key
5.FULLTEXT: Group text Index

1.INDEX: Normal Index
1.1 You can have more than one index field in a table.
The value of the 1.2 field allows for duplication and can be assigned a null value.
1.3 The fields that make the query criteria are often set to the index field.
The key flag for the 1.4INDEX field is mul.

Insert into library name. Table name values (value list); #插入表记录
SELECT * from library name. Table name; View all records for a table

例：建表的时候指定索引字段-INDEX（字段1），INDEX（字段2）...mysql>create database ku;mysql>create table ku.abc(>id    int(2) not null,>name  varchar(4)  not null,>age   int(3)  not null,>index(name),index(age)               >);mysql>desc ku.abc;Field  Type  Null  Key   Default   Extra    ..        ..      ..      MUL     ..          ..mysql>insert into ku.abc values(1,"wang",23);mysql>select * from ku.abc;

Set the index field in a table that has already been created
Format: Create index name on Library name. Table name (field);

Delete the index field for the specified table
Format: Drop index name on library name. Table name;

例：mysql>create index  xixi on ku.abc(id);mysql>drop  index  xixi on ku.abc;

View index information for a table
Format: Show index from library name. Table name;

例：mysql>show index from ku.abc\G;......Index_type: BTREE（默认使用B树算法）......

Index type used by default: BTREE (binary tree) hash b+tree

2.PRIMARY Key: Primary key
2.1 Only one primary key field in a table
2.2 The value of the corresponding field is not allowed to be duplicated, and null values are not allowed.
2.3 If more than one field is primary key, called a composite primary key, it must be created together.
2.4 The key flag of the primary key field is the PRI.
2.5 is usually used with auto_increment.
2.6 The fields that uniquely identify the record in the table are often set as the primary key field. (such as record number field)

Comments:
Primary KEY and Auto_increment
Field values automatically grow by +1
Primary key and numeric type

例：建表的时候指定主键字段-PRIMARY KEY(字段名)mysql>create table ku.abc2(>id  int(3)  auto increment,>name  varchar(5)  not null,>age   int(2)  not null,>primary key(id)>);mysql>desc ku.abc2;Field  Type  Null  Key  Default    Extra    ..        ..       ..     PRI     ..           ..mysql>insert into ku.abc2 values(2,"wang",23);mysql>select * from ku.abc2;

Set the primary key field in a table that has already been created
Format: ALTER TABLE library name. Table name ADD PRIMARY KEY (field name);

Delete the primary key field for the specified table
Format: ALTER TABLE library name. Table name DROP PRIMARY KRY;
Note: If you have a self-increment attribute (auto_increment), you must first delete it.

例：mysql>alter tabel ku.abc2  drop primary key;mysql>alter table ku.abc2 add primary key(id);

3.UNIQUE: Unique index
3.1 There can be more than one unique field in a table.
3.2 The value of the corresponding field is not allowed to be duplicated.
The key symbol for the 3.3UNIQUE field is uni.
The value of the 3.4UNIQUE field is allowed to be null, and when it is modified to not allow NULL, this field is restricted to the same primary key, and key becomes a pri.

例：建表的时候指定UNIQUE字段-UNIQUE(字段1)，UNIQUE(字段2)...mysql>create table ku.abc3(>id char(6),>name  varchar(4)  not null,>age int(3)  not null,>unique(id),unique(name),unique(age)>);mysql>desc ku.abc3;Field   Type    Null      Key    Default   Extra..          ..       YES       UNI     ..           ..mysql>insert into ku.abc3 values(3,"wang",23);mysql>insert into ku.abc values(4,"lisi",23);mysql>select * from ku.abc3;

To set a unique field in a table that has already been created
Format: Create unique index unique field name on Library name. Table name (field name);

Delete a unique field for the specified table
Format: Drop index unique field name on Library name. Table name (field name);

例：mysql>drop  index name on  ku.abc3; #于删除INDEX索引的方法相同。mysql>create unique  index name on ku.abc3(age);

4.FOREIGN Key: Foreign key
4.1 Let the value of the current table field be selected within the range of the value in another table field.

Use conditions for foreign keys:
1. The storage engine for the table must be InnoDB.
2. The field types of the two tables should be identical.
3. The referenced field must be a primary key type (PRIMARY key).

Basic usage:
Format: foreign key (field name of Table a) References table B (field name)
On the update cascade on the DELETE Cascade #参照表为B, which is selected from within the range of the values in the B table field.

例：提示：被参照字段必须要是主键类型(PRIMARY KEY)。mysql>create table  ku.abc4(>id  int(4),>name char(5),>foreign key(name),>references  abc3(name),>on update  cascade>on  delete cascade>engine=innodb>);

Delete a foreign key field
Format: ALTER TABLE name drop FOREIGN KEY constraint name;

例：mysql>show create table  ku.abc4\G;...CONSTRAINT `xxx`    #`xxx`里面是约束名.....mysql>alter table ku.abc4 drop foreigen key xxx ;

MySQL Index overview