MySQL Series 2------data type, index, key, storage engine

One, MySQL data type:

1. Numeric Type:

integer type: when using the unsigned modifier, only positive values are taken, the value is not sufficient to specify the width, and the left fill space

Integer types include: Tinyint,smallint,int, etc.

Float type: (5,2) total length and length after decimal point

Includes: float,double,decimal (fixed length, non-conformance length required input not entered)

2. Character type:

Char: fixed length, 255 characters, right with space, high efficiency

VARCHAR: variable length, allocated space by actual data size, mailbox available varchar

large Text type: text, blob

3. Date and Time type:

Date Time: DateTime (8 bytes) timestamp (4 bytes)

Date: Date 0001-01-01~9999-12-31

Year 1901-2155 when two digits are used: 01-69 means 20XX 70-99 means 19XX

time: HH:MM:SS, three bytes

4. Time function

Now (): Gets the system date time when this function is called

Month (), date (): Gets the specified month, date, time

5. Enumeration type: hobby, gender, professional

Field name Enum (value 1, value 2, value 3) a single selectable number is used to represent

Field Name Set (value 1, value 2, value 3) multiple selection

6. Field constraints: How to assign a value to a field

Null (NULL), NOT NULL (non-NULL), Default (defaults)

Second, MySQL Index

Advantages and disadvantages: Occupy physical storage space, slow down the writing speed, speed up the query

1, normal index: index

There can be multiple indexed fields whose values can be repeated and null, and in the table structure, the value of its key key is displayed as: Mul

Created: CREATE TABLE user (id int (3), index (ID));//CREATE table when creating

Create index ID on user1 (ID); Create in an existing table

Delete: Drop index ID on user1; Delete index as ID in User1 table

2. Unique indexes: The unique index is represented by the key value column: UNI

There can be more than one unique index in a table, the value of the corresponding field cannot be duplicated, and when not empty, the limit function

Is the same as the primary key.

created: Create unique index hz_id on S2 (hz_id);

Delete: Drop index hz_id on S2;

Third, primary key, composite primary key, foreign key

1. PRIMARY key: Primary key A table can have only one primary key, which is represented as: PRI

The corresponding field value cannot be empty.

create: ALTER TABLE name add primary key (field name);

Delete: ALTER TABLE name drop PRIMARY key;   

primary key with Auto_increment, the value of the field automatically grows

When you create a primary key, the auto_increment is appended to it, and when you delete the primary key ,

The first step is to remove the auto-increment property, which is to first modify the field's properties

2. Composite PRIMARY key: Multiple fields in the table together with the master key, to create together    

Create: ALTER TABLE S1 add primary key (Stu_id,name);

Delete: ALTER TABLE S1 drop PRIMARY key;

3, FOREIGN key use rules: The storage engine is InnoDB, type and width, the reference table: preferably a primary key

Created: foreign key (a table field name) references B table name (fields name)

On UPDATE cascade (Synchronous Update) on DELETE cascade; (Synchronous deletion)

Delete foreign key: ALTER TABLE name drop FOREIGN key constraint name;

FOREIGN Key Name query: Show CREATE TABLE name \g, can query

Four, MySQL Storage engine

What is the storage Engine: responsible for performing actual data I/O operations for the database,

Different storage engines, the way they store data is not the same,

In mysql5.7, the default storage engine is InnoDB.

1, view: Show create TABLE sys_in; View the storage engine for a table

Show engines; View supported and default storage engines for the database

2, modify the database default storage engine: VIM/ETC/MY.CNF

Default-storage-engine=myisam/innodb

3. Modify the default engine for an existing table:

ALTER TABLE name ENGINE=INNODB;

4. The difference between MyISAM and InnoDB

MyISAM: Support table-level lock, suitable for multi-read less write;. frm (table structure),. MYI (index),. MYD (data)

InnoDB: Support row-level lock, suitable for more write less read, support foreign key, support transaction, transaction Rollback,. frm (table structure). IBD (index + data)

MySQL Series 2------data type, index, key, storage engine