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

Overview of MySQL Index

Advantages of the index:

1. By creating a uniqueness index, you can ensure the uniqueness of each row of data in the database table.

two。 It can speed up the retrieval of data.

Index disadvantages:

1. When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

two。 The index needs to take up physical space.

Type of index:

1.INDEX: general index

2.PRIMARY KEY: primary key

3.UNIQUE: unique index

4.FOREIGN KEY: foreign key

5.FULLTEXT: group text index

1.INDEX: general index

1.1There can be multiple INDEX fields in a table.

The value of the 1.2field allows duplicates and can be assigned a NULL value.

1.3 the fields that are used as query criteria are often set to INDEX fields.

The KEY flag for the 1.4INDEX field is MUL.

Insert into library name. Table name values (values list); # insert table record

Select * from library name. Table name; view all records of the table

Example: specify index fields when creating a table-INDEX (field 1), INDEX (field 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 the created table

Format: create index index name on library name. Table name (field)

Delete the index field of the specified table

Format: drop index index name on library name. Table name

Example: mysql > create index xixi on ku.abc (id); mysql > drop index xixi on ku.abc

View the index information of the table

Format: show index from library name. Table name

Example: mysql > show index from ku.abc\ Gpoli.Indextrees type: BTREE (B-tree algorithm is used by default).

Default index type: BTREE (binary tree) hash B+Tree

2.PRIMARY KEY: primary key

2.1 there can be only one primary key field in a table

2.2 duplicates are not allowed in the values of the corresponding fields, and null values are not allowed.

2.3 if multiple fields are used as primary key, called compound primary keys, they must be created together.

The KEY flag for the primary key field is PRI.

2.5 is usually used with AUTO_INCREMENT.

2.6 it is common to set the fields in the table that can uniquely identify records as primary key fields. (for example, record number field)

Note:

Primary key used with auto_increment

The field value automatically increases by + 1

Primary key and numeric type

For example, when creating a table, specify the primary key fields-PRIMARY KEY (field name) 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 the created table

Format: alter table library name. Table name ADD PRIMARY KEY (field name)

Delete the PRIMARY KEY field of the specified table

Format: alter table library name. Table name DROP PRIMARY KRY

Note: if there is a self-increment attribute (AUTO_INCREMENT), it must be deleted first.

Example: 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 multiple UNIQUE fields in a table.

3.2 the values of the corresponding fields cannot be duplicated.

The KEY flag 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 disallow NULL, the field is restricted to the same primary key and KEY becomes PRI.

Example: specify UNIQUE fields-UNIQUE (field 1), UNIQUE (field 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) >) when creating the table; 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

Set the UNIQUE field in the created table

Format: create unique index unique field name on library name. Table name (field name)

Delete the UNIQUE field of the specified table

Format: drop index unique field name on library name. Table name (field name)

Example: mysql > drop index name on ku.abc3; # is the same as deleting the INDEX 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 of another table field.

Conditions for using foreign keys:

1. The storage engine for the table must be innodb.

two。 The field types of the two tables should be the same.

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)

The on update cascade on delete cascade # reference table is B, which is selected from the range of values in the field of Table B.

Example: hint: the referenced field must be a primary key type (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 foreign key field

Format: alter table table name drop foreign key constraint name

Example: mysql > show create table ku.abc4\ Gbot.. CONSTRAINT `xxx` # `xxx` is the constraint name. MySQL > alter table ku.abc4 drop foreigen key xxx

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.