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

This article will explain the example analysis of MySQL key value for you in detail. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

MySQL key value Overview key value Typ

Select the key value according to the data storage requirements

Index General Index

Unique unique index

Fulltext full-text index

Primary key primary key

Foreign key foreign key

Index introduction

What is the index?

-- A catalogue similar to a book

-sort the fields in the table

-- Index types include: Btree (binary tree), B+tree, hash

Advantages and disadvantages of index

The index is a little bit

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

-- it can speed up the query of data.

Index shortcoming

However, when the data in the table is added, deleted and modified, the index should also be adjusted dynamically, which reduces the maintenance speed of the data.

-- Index needs to take up physical space

MySQL key values use Index normal index

Use Rul

-- A table can have multiple index fields

-- the value of the field can be duplicated and the NULL value can be assigned.

-- the field used as a query condition is usually set to the index field

-- the index field is marked MUL

Create an index when creating a table

-- index (field name), index (field name).. ..

Mysql > create table library name. Table name (field list, index (field name), index (field name))

Create an information table, specifying name and age in the fields as indexes (values in fields name and age to be sorted)

Mysql > create table db2.t2 (- > name char (10),-> age int,- > class char (7),-> email char (30),-> index (name), index (age)->); mysql > desc db2.t2

View index information

Basic View

-- desc library name. Table name; / / check the key column

View in detail

-- show index from table name\ Gbot MySQL > show index from db2.t2\ G / / because there are two fields as indexes, there are two columns below, and\ G shows the output in column form.

Mysql > show index from db2.t2; / / the following figure shows the effect of not adding\ G

Delete index

-- drop index index name on library name. Table name

Mysql > drop index age on db2.t2; / / delete index agemysql > desc db2.t2

Mysql > show index from db2.t2\ G / / only index name is left, index age has been deleted

Store data in table T2 in the library db2:

Mysql > insert into db2.t2 values ("bob", 19, "B180601", "stu1@163.com"); mysql > insert into db2.t2 values ("tom", 19, "B180602", "stu2@163.com"); mysql > insert into db2.t2 values ("lucy", 19, "B180603", "stu3@163.com"); mysql > insert into db2.t2 values ("jack", 19, "B180604", "stu4@163.com"); mysql > select * from db2.t2 / / the data in the table is sorted in the order in which the records in the table are inserted.

The information of index sorting is in t2.frm and t2.ibd files.

[root@DB ~] # ls / var/lib/mysql/db2

Note: when no index is set, the query table data is matched row by row according to the table order; after setting the index, it is searched according to the sort of the index (such as BTree, B+Tree, hash).

Create an index in an existing table

-- create index index name on table name (field name)

Mysql > use db2;mysql > show tables

Mysql > desc db2.stuinfo

Mysql > create index name on db2.stuinfo (name); / / Index names are generally the same as field names. Index names can also be named mysql > desc db2.stuinfo.

Mysql > show index from db2.stuinfo\ G; / / View the details of the index

Primary key primary key

Use Rul

-- duplicate field values are not allowed, and NULL values are not allowed to be assigned

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

Multiple fields are used as primary keys, called compound primary keys, and must be created together

The flag of the primary key field is PRI

Primary key is usually used with auto_increment

-- the field that uniquely marks the record in the table is usually set as the primary key

[record number field]

Create a primary key when creating a table

-- primary key (field name)

Mysql > create table T8 (name char (5) primary key, id int)

Format 1:

Mysql > create table db2.t3 (name char (10) primary key,age int); / / set the name field as the primary key, but after setting the primary key, automatically set whether the constraint is empty to NO (that is, null is not allowed) mysql > desc db2.t3

Format 2:

Mysql > create table db2.t4 (name char (10), age int,primary key (name)); / / you can also create the field first, and finally specify which field is the primary key mysql > desc db2.t4

Insert data into table T3

Mysql > insert into db2.t3 values ("bob", 19); mysql > select * from T3

Mysql > insert into db2.t3 values ("bob", 21); / / because the field name is the primary key, the value in the field name cannot be duplicated. There is already a value of bob in the name field in the table, so you can no longer assign the bob value ERROR 1062 (23000): Duplicate entry 'bob' for key' PRIMARY'mysql > insert into db2.t3 values (null,21) to the field name. / / error reason. Because field name is the primary key, field name is not allowed to assign NULL value ERROR 1048 (23000): Column 'name' cannot be null

Create a primary key in an existing table

-- format

Mysql > alter table library name. Table name add primary key (field name); mysql > desc db2.t2

Mysql > select * from db2.t2

/ / We want to set the field name as the primary key, so we need to check the value of the name field in the original table to make sure there are no NULL values and duplicate values. As shown in the figure above, the name field in table T2 has no null and duplicate values, or you can use the following method mysql > select name from db2.t2 where name is null / / check which values in the name field are null. The output shows that there is no Empty set (0.01sec) mysql > alter table db2.t2 add primary key (name). / / set the field name as the primary key mysql > desc db2.t2

Delete primary key

-- alter table table name drop primary key

Note: if there is a self-increasing attribute before removing the primary key, it must be removed first.

Create a compound primary key

-- alter table table name add primary key (field name list)

Note: when multiple fields are used as primary keys, they are called compound primary keys, and the values in these fields as primary keys cannot be repeated at the same time when inserting records. For example, two values of field class are classA, and both values of field name cannot be tom, that is, field name cannot have the same name, can be tom and lucy, but cannot both be tom

Create a compound primary key and set both the class field and the name field as the primary key

Mysql > create table db2.t5 (- > class char (7),-> name char (10),-> money enum ("no", "yes"),-> primary key (class,name)->); mysql > desc db2.t5

Mysql > insert into db2.t5 values ("B180601", "bob", "yes"); mysql > select * from db2.t5

Mysql > insert into db2.t5 values ("B180601", "bob", "yes"); / / error reason, because field class and field name are both primary keys, so the values of these two fields cannot be repeated at the same time ERROR 1062 (23000): Duplicate entry 'B180601UBB' for key 'PRIMARY'mysql > insert into db2.t5 values ("B180602", "bob", "yes") / / two primary key fields. As long as the value of one field is not duplicated, the other primary key fields can be inserted into mysql > insert into db2.t5 values ("B180602", "tom", "yes"); mysql > select * from db2.t5.

Note: there is a problem at this time, if there are two people with the same name in the same class, but the class field and name field are both primary keys, because the values of these two primary key fields cannot be repeated at the same time, the data cannot be inserted at this time. The solution is to delete the primary key of these two fields first, then delete the table data, and then create a stu_num (student number) field and set the stu_num, class and name fields as the primary key together.

1) Delete the primary key:

Mysql > alter table db2.t5 drop primary key (name); / / error. Since field class and field name are used as primary keys together, you cannot delete only one field when deleting. You must also delete ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near'(name)'at line 1 mysql > desc db2.t5; / / found that the primary key has been deleted.

2) create a new field stu_num:

Mysql > alter table db2.t5 add stu_num char (9) first; / / add a new field stu_num and put it in the first column of the table

Mysql > select * from db2.t5; / / since the field stu_num is to be set as the primary key, the value of the field stu_num cannot be NULL, so we need to empty the table data

3) clear the table data:

Mysql > delete from db2.t5;mysql > select * from db2.t5; / / output result shows that T5 table is empty table Empty set (0.00 sec)

4) create a compound primary key in the existing table:

Mysql > alter table db2.t5 add primary key (stu_num,class,name); mysql > desc db2.t5

Mysql > insert into db2.t5 values ("B18060101", "B180601", "bob", "yes"); mysql > insert into db2.t5 values ("B18060102", "B180601", "bob", "yes"); mysql > select * from db2.t5; / / three fields are primary keys, as long as the values of one of the primary keys are not duplicated, the data can be inserted successfully.

/ / solve the problem of inserting data with the same name in the same class by adding the stu_num field of the student number to make all three fields primary keys.

Primary key used with auto_increment (self-increment)

Mysql > create table T8 (id int primary key auto_increment, name char (5))

Note: the premise to set self-increment is that the field must be a primary key to set self-increment; the field that sets self-increment must be numeric, and the character type cannot be self-incremented.

Example: create a table with a self-increasing primary key

Mysql > create table db2.t6 (- > stu_num int primary key auto_increment,-> name char (10),-> age int->); mysql > desc db2.t6

Mysql > insert into db2.t6 (name,age) values ("bob", 19); mysql > select * from db2.t6

Mysql > insert into db2.t6 (name,age) values ("tom", 20); mysql > insert into db2.t6 (name,age) values ("lucy", 19); mysql > select * from db2.t6

Mysql > insert into db2.t6 values (3, "lucy", 19); / / error reason: because the field stu_num is the primary key, the value cannot be repeated ERROR 1062 (23000): Duplicate entry'3' for key 'PRIMARY'mysql > insert into db2.t6 values (9, "lucy", 19); / / it can be a number other than 1x3, either in order or out of order mysql > select * from db2.t6

Mysql > insert into db2.t6 (name,age) values ("jack", 25); / / if you are inserting a piece of data that does not assign a value to stu_num, then the default value of field stu_num is incremented from the maximum number, that is, the default value of field stu_num is 10 instead of 4mysql > select * from db2.t6

Example: if you empty the table record and insert data, does the value of the field stu_num increase from 1 or 10?

Mysql > delete from db2.t6; / / clear the table record mysql > select * from db2.t6; / / the output shows the empty table Empty set (0.00 sec) mysql > insert into db2.t6 (name,age) values ("bob", 18); / / insert a data mysql > select * from db2.t6 / / although all the data in the table is deleted, the last number will be recorded, and then the inserted data will start to increase from the last recorded number.

Mysql > insert into db2.t6 (name,age) values (null, "tom", 18); / / the primary key cannot be empty, but here we assign the primary key field stu_num as NULL, but do not report an error, because null is equivalent to no value assigned to the field stu_num, and no value is assigned so it will be assigned in a self-increasing way, so the assignment NULL here will not report an error.

Mysql > select * from db2.t6

Example: usually set the field in the table that uniquely identifies the record as the primary key, such as the record number field

Mysql > alter table db2.stuinfo add id int primary key auto_increment first;mysql > desc db2.stuinfo

Mysql > select * from db2.stuinfo

Foreign key foreign key

Foreign key function

When inserting a record, the field value is selected within the range of the values of another table field

Use Rul

-- Table storage engine must be innodb

-- create table library name. Table name (.) Engine=innodb; / / specifies the engine for table storage

-- the fields of the table in which the record is inserted should be the same as the field type of the referenced table

-- the value of the referenced field must be unique and cannot be empty, so it must be the primary key of the index type (primary key)

Create a foreign key

-- create table table name (field name list, foreign key (field name) references library name. Table name (field name) / / specify foreign key on update cascde / / synchronous update on delete cascade / / synchronous delete) engine=innodb / / specify storage engine-5

Create an employee table as a reference table:

Mysql > create table db2.yuangong (- > id int primary key auto_increment,-> name char (20),-> sex enum ("boy", "girl")->) engine=innodb;mysql > desc db2.yuangong

Mysql > insert into db2.yuangong (name,sex) values ("bob", "boy"); mysql > insert into db2.yuangong (name,sex) values ("lucy", "girl"); mysql > select * from db2.yuangong

Create a payroll setting foreign key, using the employee table as a reference table:

Mysql > create table db2.gongzi (- > id int,-> salary float (7 id 2),-> foreign key (id) references db2.yuangong (id)-> on update cascade-> on delete cascade->) engine=innodb;mysql > desc db2.gongzi

/ / as shown in the figure, after the foreign key is successfully created, the index is automatically created and the data is sorted, so the flag is the mark of the ordinary index. We can check whether it is a foreign key in another way: mysql > show create table db2.gongzi; / / View the command to create the table.

Mysql > insert into db2.gongzi values (1pm 10000); mysql > insert into db2.gongzi values (2Jing 20000); mysql > select * from db2.gongzi

Mysql > insert into db2.gongzi values (3Jing 20000); / send salary report error to id for 3 employees, reason: assign value 3 to id field in gongzi table, but because foreign key is set in gongzi table, the id field of gongzi table refers to id field of yuangong table, so when assigning a value to id field of gongzi table, this value must be in the id field of yuangong table before it can be assigned.

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_ 1` FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Synchronous update

Change the value of the id field in the yuangong table from 2 to 8:

Mysql > select * from db2.yuangong

Mysql > update db2.yuangong set id=8 where id=2; / / change the id field in the yuangong table to 8, and synchronously update the records in the gongzi table where the id field is 2

Mysql > select * from db2.yuangong

Mysql > select * from db2.gongzi; / / found that after the value of the id field in the yuangong table was changed from 2 to 8, the value of the id field in the gongzi table also changed from 2 to 8

Synchronous deletion

Delete the id field value of 8 from the yuangong table:

Mysql > delete from db2.yuangong where id=8; / / delete the id field 8 in the yuangong table, and delete the record mysql > select * from db2.yuangong in the gongzi table id field 8 synchronously

Mysql > select * from db2.gongzi; / / found that when the id field value of yuangong table is 8, the id field value of gongzi table is also deleted.

Matters needing attention

Mysql > insert into db2.yuangong (name,sex) values ("jack", "boy"); mysql > select * from db2.yuangong

Mysql > insert into db2.gongzi values (350000); mysql > select * from db2.gongzi

Mysql > insert into db2.gongzi values (3Jing 30000); mysql > insert into db2.gongzi values (3Jing 30000); mysql > insert into db2.gongzi values (null,65000); mysql > select * from db2.gongzi

Note: as shown above, the field id of 3 is repeatedly assigned, and the field id is also assigned a null value. Because the values in the field id in the reference table yuangong are 1 and 3, the field id in the gongzi table can be assigned 1 or 3 repeatedly. This assignment is very unreasonable in a production environment, so we can solve the problem of repeated assignment and null assignment by setting the id field in the gongzi table as the primary key.

1) Delete the data of the gongzi table

Mysql > delete from db2.gongzi; / / Delete all records in the gongzi table mysql > select * from db2.gongzi; / / query table record is empty, indicating that the table record has been deleted Empty set (0.00 sec)

2) set the field id as the primary key

Mysql > desc db2.gongzi

Mysql > alter table db2.gongzi add primary key (id); / / set the id field in the gongzi table to the primary key mysql > desc db2.gongzi

3) carry out assignment test

Mysql > insert into db2.gongzi values (1jing10000); mysql > insert into db2.gongzi values (3jing30000); mysql > insert into db2.gongzi values (1pc10000); / / repeat assignment with an error because the primary key does not allow duplicate values ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'mysql > insert into db2.gongzi values (3d30000) / / repeat the assignment again with an error because the primary key does not allow duplicate values ERROR 1062 (23000): Duplicate entry'3' for key 'PRIMARY'mysql > insert into db2.gongzi values (null,30000); / / Null values are assigned and an error occurs because the primary key does not allow null values ERROR 1048 (23000): Column' id' cannot be nullmysql > insert into db2.gongzi values (230000) / / the id field in the yuangong table has no record with a value of 2, and values outside the reference table cannot be assigned to ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_ 1`FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Delete foreign key

-- alter table library name. Table name drop foreign key foreign key name

Mysql > show create table db2.gongzi

/ Note: the place where the red line is circled is the name of the foreign key

Mysql > alter table db2.gongzi drop foreign key gongzi_ibfk_1; / / remove foreign keys

Mysql > show create table db2.gongzi; / / look at the command to create the table and find that there is no record of creating foreign keys

At this point, we can insert values outside the range of values in the id field of the yuangong table.

Mysql > insert into db2.gongzi values (2je 30000); mysql > insert into db2.gongzi values (9je 30000); mysql > select * from db2.gongzi

Since the id field of the gongzi table is still the primary key, duplicate values and null values cannot be assigned

Mysql > insert into db2.gongzi values (9 for key 30000); ERROR 1062 (23000): Duplicate entry'9' for key 'PRIMARY'mysql > insert into db2.gongzi values (null,30000); ERROR 1048 (23000): Column' id' cannot be null on the "sample analysis of MySQL keys" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.