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

Source: Internet
Author: User
Tags create index mysql index

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.