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