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

This article mainly introduces the knowledge of MySQL storage engine, hoping to supplement and update some knowledge, if you have any other questions you need to know, you can continue to follow my updated article in the industry information.

1. MySQL key value (limit how to assign values to fields)

1)

Index: similar to the "catalogue of books" tree catalogue

Disadvantages: slow down the speed of writing (insert update delete)

Take up physical storage space

Advantages: speed up query speed

2)

Use index

Use Rul

There can be multiple index fields in a table

The value of the field is allowed to be duplicated and NULL can be assigned.

Fields that are used as query conditions are often set to index fields

The key flag of the index field is MUL

View Index

Desc table name

Show index for table name

# View index details

Create an index

Create table table name (

Field Typ

Index (index name)

# create an index when creating a table

# default has the same name as field name

-> create table T21

-> name char (10)

-> age int

-> sex enum ('boy','girl')

-> index (name)

-> index (sex))

#

Create index Index name on Table name (field)

# existing tables create indexes

# Index name can be the same as field name

# default index type: BRREE (binary tree)

Create index name on T3 (name)

Drop index index name on table name

# Delete index

#

Primary key parimary key

Use Rul

There can be only one parimary key field in a table

Duplicates are not allowed for the corresponding field values, and assignment is not allowed.

If multiple fields are used as parimary key, called compound primary keys, one must be created.

The KEY flag of the main key field is dead PRI

Usually used with auto_increment

Often put the field bit primary key in the table that can uniquely identify the record

1)

-> create table T22 (- > create table T22 (

-> name char (10),-> name char (10) primary key

-> age int,-> age int

-> likes set,-> likes set ('axiomagy, encyclopedia, c') 0

-> primary key (name))

# add a primary key when creating a table

2)

Alter table table name add primary key (field)

# add primary keys to existing tables

3)

Alter table table name drop primary key

# Delete primary key

#

Compound primary key

Multiple fields act as primary keys together, and field values cannot be repeated at the same time.

1)

-> create table table name (

Cip char (15)

-> port smallint

-> status enum ('allow','deny') default' deny'

-> primary key (cip,port))

# create a compound primary key, which must be created together

2)

Alter table table name drop primary key

# Delete the compound primary key, which must be deleted at the same time

3)

Alter table T23 add primary key (cip,port)

# existing tables to create compound primary keys

#

The primary key is used with auto_increment to automatically increase the maximum value of the field by + 1, and the bit value type

1)

-> create table T24

-> id int (2) zerofill primary key auto_increment

-> name char (10)

-> class char (4)

-> index (name))

# create a table

Insert into T24 (name,class) values ('tom','1709')

# add information name class, and the id field in the table is automatically + 1.

2)

Alter table T24 modify id int (2) unsigned zerofill not null

Alter table t24 drop primary key

# to delete the primary key, change the aotu_increment first

#

Unique unique index

There can be multiple unique fields in a table

Duplicates are not allowed for corresponding field values.

The key flag of the unique field is uni

The value of the unique field is allowed to be null, and when it is modified to disallow null, this field limit is the same as the primary key

#

Foreign key: foreign key

Function:

Restricts the assignment of values to fields.

Values must be selected within the range of field values specified in the specified table.

Conditions:

The storage engine for the table must be innodb

Field types should be consistent

The referenced field must be one of the index types (primary key)

~

1)

Foreign key (field) references is referenced table (field)

On update cascade on delete cascade

# how to update synchronously and delete synchronously

Exercise:

Reference table

Mysql > create table jfb (

-> id int (2) primary key auto_increment

-> name char (10)

-> pay float (7 and 2)

->) engine=innodb

Insert into jfb (name,pay) values ('bob',20000), (' lucy',15000)

+-+

| | id | name | pay | |

+-+

| | 1 | bob | 20000.00 | |

| | 2 | lucy | 15000.00 | |

+-+

Foreign key table

Mysql > create table xsb (

-> num int (2)

-> name char (10)

-> class char (9)

-> foreign key (num) references jfb (id)

-> on update cascade

-> on delete cascade

->) engine=innodb

# num Field reference jfb (id)

Insert into xsb values (1), (2) (2)

# write xsb to data

+-+

| | num | name | class | |

+-+

| | 1 | bob | nsd1709 |

| | 2 | lucy | nsd1709 |

+-+

Insert into xsb values (3 recordings, tomatoes, andsd1709')

# cannot be created because the reference table jfb (id) does not have 3

Insert into xsb values (2 recording jerryboy recording 1709')

+-+

| | num | name | class | |

+-+

| | 1 | bob | nsd1709 |

| | 2 | lucy | nsd1709 |

| | 2 | jerry | nsd1709 |

+-+

The reference table jfb (id) has only two pieces of data, but it was added successfully because

Parameter 2 exists, so it is added successfully, but there are only two pieces of data in the reference table

To avoid this, we need to make xsb (num) unique.

Add a primary key to it

Delete from xsb where name='jerry'

# Delete duplicate data

Alter table xsb add primary key (num)

# set the primary key for xsb (num)

Jfb xfb

+-+

| | id | name | pay | | num | name | class | |

+-+

| | 1 | bob | 20000.00 | | 1 | bob | nsd1709 | |

| | 2 | lucy | 15000.00 | | 2 | lucy | nsd1709 |

+-+

Update jfb set id=8 where id=2

# change jfb id=2 to id=8

Jfb xfb

+-+

| | id | name | pay | | num | name | class | |

+-+

| | 1 | bob | 20000.00 | | 1 | bob | nsd1709 | |

| | 8 | lucy | 15000.00 | | 8 | lucy | nsd1709 | |

+-+

Delete from jfb where id=1

# Delete data with jfb id 1

Jfb xfb

+-+

| | id | name | pay | | num | name | class | |

+-+

| | 8 | lucy | 15000.00 | | 8 | lucy | nsd1709 | |

+-+

Delete from jfb

Error: the referenced table cannot be deleted at will

Delete foreign key

Show create table table name

# Foreign key names are automatically generated.

Alter table table name drop foreign key foreign key name

# deleting the name of a foreign key

#

Storage engine

MySQL database service software comes with the program.

Different storage engines have different functions and data storage methods.

Is the processor that processes the table

Table name .frm storage database structure

1)

Show engines

# View the storage engines supported by the database service

2)

Characteristics of storage engine

MyISAM:

Table name .MYI index information

Table name .MYI data

Table name .frm table structure

Features:

Support for table-level locks: locking a table

Transaction rollback is not supported

InnoDB

Table name. MYI table structure

Table name .idb data + index information

Features:

Support for row-level locks: locks only rows that are currently being accessed

Support for transaction rollback

Lock type: read lock, write lock

Select insert delete update

Tables that receive writes are suitable for using the InnoDB storage engine

Tables that receive read operations are suitable for using the MyISAM storage engine

Lock granularity: table-level lock row-level lock

Transaction: the process of a data access from the beginning to the end of the access

Transaction rollback: if an operation error occurs at any step during a data access, all operations will be restored.

Transaction characteristics: consistency, atomicity, isolation

Transaction log file: records the operations performed by the table of the InnoDB storage engine.

3)

Write / etc/my.cnf in the configuration file

Default_storage_engine= Storage engine name

# modify the default engine

Alter table table name engine= storage engine name

# modify the table engine

Create table table name (. ) engine=innodb

# set the storage engine of the table

Because it is set when no data is stored in the table.

#

MySQL service architecture: (8 functional modules)

Connection pool

Sql interface

Parsers: parsing command syntax

Optimizer: executing commands with minimum consumption

Query cache: store the data you have looked up

Storage engine

File system: hard disk

Administrative tools: commands provided after package

After reading the above knowledge about MySQL storage engine, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.