2016-11-25-mysql-2

String type

char:255 Word

char (number of characters)

High efficiency

VarChar:(gbk<=32767 Characters | utf8<=21845 characters)

varchar (number of characters)

More flexible storage

Text: Long length

Tinytext,text,mediumtext,longtext

No need to specify length for storage of text areas and free input areas

Enum: Enum type, similar to radio. Only option values in options are inserted.

Enum (' value 1 ', ' Value 2 ')

Storage is stored as numbered (left to right), that is, the first option is stored in memory at 1, and so on.

Set: Collection type, similar to multiple selection. Only option values in options are inserted

Set (' Value 1 ', ' Value 2 ', ' Value 3 ',...)

There is no limit to the order in which values are inserted, stored as enum by location number.

Special Note: storage--

e.g. Set ("Value 1", "Value 2", "Value 3", "Value 4", "Value 5", "Value 6")

1 2 4 8 16 32

Storage is stored in the same way as the location number.

binary | varbinary | Blob

Binary text type, stored in binary form. Generally not used

Column properties define the properties of a column (field)

null | NOT NULL

: Defines whether a field can be empty (default is null)

Default

: Gives the field a default value that is populated with a default value when the field is not written to a value. General mates are not null used.

At this point, the value can be written directly to the default, without quotes wrapped.

Primary key

Primary key, which is set as the primary key for a field or field combination (multiple fields combined into a single key), and is generally set to a field that can be identified as the primary key, and a table can have only one primary key.

When used, a combination of primary keys is rarely used, and a field with no business logic to the table field is typically set up, and the field is given the primary key.

The primary key field cannot have duplicate values and cannot be empty.

The format is: Field primary key

: Primary key (field name) can only use this format when defining a field group

Unique key

Unique key, defined after the field value is not repeatable, can be null, you can define multiple (same table)

Auto_increment

Self-growth, the defined field is automatically written to add 1 after each row record is inserted.

Note: The use of a field must be an integral type and must exist in the index.

e.g. (Field Auto_increment

) auto_increment initial value;

The default is starting at 0, and when you need to specify a start value, add the Auto_increment initial value to the sentence after the table is created.

Note: The value can still be written manually after opening since growth, and each self-growth will have a previous edge of the maximum value of the growth base, that is, the original starting from 0, increased to 1 after manually inserting a row of data of 200, the next insertion row will be counted from 201, and even if all the table data is deleted, Does not empty the growth mechanism (the growth base is unchanged). Reset self-growth statement: TRUNCATE TABLE name.

Comment

Column comments, comments for the column, and after using the show create table name, the comment content is displayed in the table properties.

Index

Each table will have a list of sorted tables that are queried using these tables. The mechanism for indexing tables will vary depending on the storage engine.

Can improve query efficiency, reduce the speed of adding and deleting, occupy a certain amount of storage space.

The relationship between entity and entity

Single-to-one: when you design a table, the two entity tables have the same primary key field, and the primary key value corresponds to one by one.

One-to-many: when designing a table, there is a relationship between two entity tables-the primary key value in one table corresponds to multiple non-primary key values for another table.

Many-to-many: when designing a table, three entity tables exist, and the primary key values of the two entity tables are linked by a third table, respectively.

FOREIGN key

Currently only the INNODB storage engine is supported, the project is often not foreign keys, but borrowed its design ideas

ALTER TABLE name add foreign key foreign key definition

FOREIGN key foreign key

When a table's primary key value corresponds to another table's field value (non-primary key), then the table of the primary key field is called the parent table, and the other is called the child table (foreign key).

In other words, the value of the foreign key depends on the value of the primary key. Inserting a foreign key value must have data corresponding to it in the primary key.

Format: FOREIGN key (foreign key field) references primary key field table name (primary key field).

Master Table Update

On update[Cascade Operation] | Master Table Update

On delete[Cascade Operation] | Primary table Delete

Cascading Operations: 1.cascade | Synchronous operation, when the main table record is deleted or updated, the corresponding deletion or update from the table

2.set NULL | When the primary table record is deleted or updated, the foreign key field from the table is set to empty

3.restrict | Reject the update or delete of the primary table.

Delete foreign key

ALTER TABLE name drop FOREIGN key foreign key name

The foreign key name is not a field name here, and if the foreign key name is not defined when creating the foreign key, then the system will automatically assign a foreign key name. (View method show create TABLE)

Storage Engine

　　　　　　

The main choice of engine is to pay attention to the speed of its execution efficiency.

2016-11-25-mysql-2