MySQL (2)

Source: Internet
Author: User
Tags float double

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)

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.