MySQL (2)

MySQL's engine:

InnoDB: Features a row lock design, supports foreign keys, and supports non-locking reads similar to Oracle, where the default read operation does not generate locks. The default storage engine above mysql5.5.8

MyISAM: Does not support transaction, table lock design, full-text indexing, mainly for some OLAP database applications, before the MySQL 5.5.8 version is the default storage engine (except for Windows version).

Memory: The engine in RAM,

Blackhole: Black hole engine

Specifies the engine for the table class: CREATE table User (id int) Engine=innodb;

　　

#.frm is the framework structure for storing data tables #. IBD is a MySQL data file #. MyD is the extension # of the data file for the MyISAM table. Myi is the extension of the index of the MyISAM table # Two storage engines found after the table structure, no data #memory, after restarting MySQL or restarting the machine, the data in the table is emptied #blackhole, inserting any data into the table, is equivalent to throw into the black hole, the table never stored records

Copy MySQL table: CREATE TABLE User select * from db2.a1; You must specify a detailed directory

Integer: Tinyinit int bigint function: Store age, rank ID various numbers [unsigned] [Zerofill] plus either of the two former symbols, the default is no latter is the visible number of digits,

tinyint: Small integer

Signed: 128 to 127

No symbols: 0 to 225

int: Holds values in a range of data, followed by the value in parentheses to see the number of digits

There are symbols:
-2147483648 ~ 2147483647
Unsigned:
0 ~ 4294967295

bigint: Big Data's

There are symbols:
-9223372036854775808 ~ 9223372036854775807
Unsigned:
0 ~ 18446744073709551615

Floating point type:

Float double fixed-point number type: Dec equals decimal

Function: Store salary, height, weight, physical parameters, etc.

float[(m,d)] [UNSIGNED] [Zerofill]
#参数解释: Single-precision floating-point numbers (non-accurate decimal values), M is full length, D is the number of decimal places. M maximum value is 255,d maximum of 30

#有符号:
-3.402823466E+38 to-1.175494351e-38,
1.175494351E-38 to 3.402823466E+38

#无符号:
1.175494351E-38 to 3.402823466E+38
#精确度:
As the number of decimals increases, the accuracy becomes inaccurate * * *


-------------------------DOUBLE-----------------------
double[(m,d)] [UNSIGNED] [Zerofill]

#参数解释: Double-precision floating-point numbers (non-accurate decimal values), M is full length, and D is the number of decimal places. M maximum value is 255,d maximum of 30

#有符号:
-1.7976931348623157E+308 to-2.2250738585072014e-308
2.2250738585072014E-308 to 1.7976931348623157E+308

#无符号:
2.2250738585072014E-308 to 1.7976931348623157E+308

#精确度:
With the increase in decimals, accuracy is higher than float, but it can become inaccurate * * *

======================================
--------------------DECIMAL------------------------
decimal[(m[,d]) [unsigned] [Zerofill]

#参数解释: The exact decimal value, M is the total number of integer parts (minus sign), and D is the number of decimal places. The M maximum value is 65,d maximum of 30.


#精确度:
With the increase of decimals, accuracy is always accurate * * *
This type is required for precise numerical calculations
The reason that decaimal can store exact values is because they are stored internally as strings.

Time:

Year Data time Datatime

The time now () function

About timestamp

Note is the string when writing: (1, ' Alex ', "1995", "1995-11-11", "11:11:11", "2017-11-11 11:11:11"),

Character type:

Char: fixed length, simple rough, wasted space, fast access speed,

Character Length range: 0-255 (one Chinese is a character, is a UTF8 encoded 3 bytes)
Store:
When a value of type char is stored, a space is padded to the right to satisfy the length
For example: Specify a length of 10, save >10 characters will be error, save <10 characters will be filled with space until enough 10 characters to store
VARCHAR: variable length, precision, space saving, slow access speed; space Count character

Character Length range: 0-65535 (if greater than 21845 will prompt for other types.) MySQL line maximum limit is 65535 bytes, character encoding is utf-8:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)
Store:
The varchar type stores the actual contents of the data, does not fill with spaces, and if ' AB ', the trailing spaces are saved.
Emphasis: The varchar type will prefix the real data with 1-2bytes, which is used to represent the bytes bytes of real data (1-2bytes maximum of 65,535 digits, exactly the maximum byte limit of MySQL to row, which is sufficient)
A 1Bytes prefix is required if the true data <255bytes (1bytes=8bit 2**8 The maximum representation of the number is 255)
A 2Bytes prefix is required if the true data >255bytes (2bytes=16bit 2**16 The maximum representation of the number is 65535)

Retrieval:
The trailing space will be saved, and the contents including the blanks will be displayed normally when the query is retrieved or queried.

Function: Length () View number of bytes char_length () view characters

Common operations: Select X,char_length (x), Y,char_length (y) from T1; Character and byte length

mysql> SET sql_mode = ' pad_char_to_full_length ';

SELECT @ @sql_mode; View the current mode

Too long enough words to use TEXT6 million words

Longtext

Enumerations and collections

Radio enum

Multi-Select Set

mysql> CREATE table t4 (
ID int,
Name varchar (50),
--Sex enum (' Male ', ' female '),
-Fav set (' Play ', ' read ');

Full constraint:
Not NULL is not empty default value is defined by itself

Unique single column cannot be duplicated

Union unique is not the same
   
    create table Dep2 (
        ID int unique,
       name char (6) Unique
    
    );
    
    
    create table Dep2 (
        ID int,
       name char (6),
        Unique (ID),
       Unique (name)
    
    );
    
    
     combination only one of them is the same
     create table DEP3 (
       ID int,
        name Char (6),
       Unique (id,name)
    
    );

Primary key: Single-row to make the Master Riedo column key (composite primary key)

Equivalent to NOT NULL unique each table must have one primary key

Auto_increment Self-increment

#全局设置步长 are valid.
Set global auto_increment_increment=5;

# Set Start offset
Set global auto_increment_offset=3;

　　

Delete from T1; #如果有自增id, the new data is still the same as the beginning of the last one before deletion.

TRUNCATE table T1; The data volume is large, the deletion speed is faster than the previous one, and starts directly from zero.

Primary key (primary key);

Single-Column primary key

CREATE TABLE t3 (
ID int PRIMARY KEY,
Name Char (6) NOT NULL

);

Multi-column PRIMARY key:

CREATE TABLE t4 (
ID int,
Name Char (6),
Primary key (ID),
Primary key (name)
);

#复合主键

CREATE TABLE T5 (
ID int,
Name Char (6),
Primary KEY (Id,name)

);

Foreign key (foreign key)

1, first create the associated table (main table);

CREATE TABLE DEP (
ID int primary KEY auto_increment,
Name varchar (ten) is not NULL,
Descripe varchar (+) NOT NULL
);

2, then create the associated table (from the table); Note the last, number.

CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name varchar (ten) is not NULL,
The age int is not NULL,
dep_id int,
test_id int,
Constraint FK_DEP foreign KEY (dep_id) references dep (ID)
ON DELETE Cascade
On UPDATE Cascade,
Constraint FK_DEP foreign KEY (test_id) references test (ID)
ON DELETE Cascade
On UPDATE Cascade,

);

　　

　　

MySQL (2)