Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the key and index-related knowledge points in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Primary key index relationship

1. Primary key: the only function of a primary key is to uniquely identify a row of data in a table. Divided into a single primary key and a federated primary key:

Single primary key: a row can be uniquely identified with only one column.

Union primary key: when using a column that can no longer uniquely identify a row, it is necessary to use multiple columns to uniquely identify a row, that is, a federated primary key. Multiple fields of the joint master key cannot be the same at the same time

two。 Index: the function of index is to improve the retrieval speed of data, which is divided into single index and joint index:

Single index: only use a column of data as the index, the default is index index, this column can contain duplicate data; if there is no duplicate data in a column, it is better to set an index in the form of unique, which is faster than index index, and use fulltext index on text data.

Joint index: in order to further improve the retrieval speed, when each retrieval needs to use multiple columns at the same time, the multi-column can be set as a joint index to improve the indexing speed. According to whether the multiple columns are unique or not, they can also be divided into index index and unique index.

It must be noted that the use of federated indexes should not break the use of fields. For example, only a | ab | abc can use federated index fields in abc federated indexes.

3. The primary key must be the index, but the index is not necessarily the primary key. A table can have only one primary key or federated primary key, but can have multiple indexes.

The primary key field must not be empty, but the index field can be empty.

Second, the relationship between foreign keys and main keys

1. A foreign key is a constraint marking that allows the values of fields in the table to be selected in the fields in the reference table.

two。 Conditions for creating foreign keys

The referenced field must be a primary key

The reference field and the referenced field must be the same data type

The storage engine of the table must be innodb

III. Grammatical format

1. Create a command format for the primary key

1.1. Create a master key when you create a table

Create table table name (field list, primary key (field name that needs to be set as the main key) auto_increment); # auto_increment is the keyword that needs to set the key field to auto-growth. If the key is not an integer type or does not need to grow automatically, this keyword is not needed. When you need to set a federated primary key, multiple field names that need to be set as key are separated by commas, and auto-growth `cannot be used.

Or

Create table table name (key field type primary key auto_increment, field list); # this method is not available when creating a federated key

1.2. Create a master key on a table that has already been created (if the data already exists in the table and the data violates the primary key constraint, the master key cannot be created and needs to be created after the data conflict is resolved)

Alter table table name add primary key (field name that needs to be set as the main key) auto_increment

two。 Create a command format for foreign keys

2.1. Create a foreign key when you create a table (if you meet the conditions for creating a foreign key)

Create table table name (field list, foreign key (fields that need to create foreign key constraints) references table name of the referenced table (referenced field of the referenced table) on update cascade on delete cascade) engine=innodb

If the foreign key needs to have a unique label, you can also add a primary key constraint to the foreign key.

2.2. Create a foreign key in an existing table

Alter table table name add foreign key (field name) references reference table name (reference field name) on update casecade on delete cascade) engine=innodb

3. Command format for creating an index

3.1. Create an index when you create a table (there can be multiple indexes, but two are added in the following example)

Create table table name (field list, index (field name already exists), index (field name that already exists); # if multiple existing field names of federated indexes are used, separate them, and remember that federated indexes cannot be set across fields

The default index name and field name are the same

3.2. Set the field in the existing table as the index field

Create index index name on table name (field name)

The index name can be named by itself. Generally, the field name is used as the index name.

4. The format of the command to create a unique index is similar to that of a normal index, as long as you ensure the uniqueness of the field record, you can use the unique keyword.

This is the end of the content of "what are the knowledge points related to keys and indexes in mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.