Third, MySQL data type

. MySQL data type
Numeric Type statement size range (signed) range (unsigned)
Tiny integer tinyint 1k-128~127 0~255
Small integer smallint 2k-32768~32767 0~65535
Middle integer mediumint 3k-2 (23) to (23)-1 0~2 (24)-1
Large integer int 4k-2 (31) to (31)-1 0~2 (32)-1
Maximal integer bigint 8k-2 (63) to (63)-1 0~2 (64)-1
Single-number floating point float 4k
Double-precision floating-point number double 8k

Note: When using the unsigned modifier, the corresponding field only holds a positive number
When the value is not sufficient to specify the width, fill in the left fill
Width is only the display width, and the size of the stored value is determined by the type
Fill in 0 instead of space when using keyword Zerofill
The field value is treated as 0 when the field value does not match the type
Only the maximum/minimum value is saved when the value is out of range


1. Use of data types
Float (n,m)
Total number of digits in N numbers
Number of bits in M-decimal
CREATE TABLE GZ (gz float (7,2));

char (width) fixed length (default width is 1)
varchar (width) becomes longer (width must be specified)
CREATE TABLE Stuinfo3 (name varchar (10),
Age tinyint unsigned,
GZ Float (7,2)
);

CREATE TABLE t14 (id int);
CREATE TABLE T12 (ID int (3));
CREATE TABLE T12 (ID int (3) zerofill);
INSERT into T12 values (10000);


2. Date/Time Type
Year YYYY
20XX 1~69
19xx 70~99
0000 0
Date 2014-11-20 Yyyy-mm-dd
Time 18:30:00 HH:MM:SS
Date Time Datetime/timestamp 2014-12-30 14:50:00
YYYY-MM-DD HH:MM:SS
Examples:
CREATE TABLE T15 (
Name Char (10),
Birthday date,
S_start year,
Up_class time,
meetting datetime
);

INSERT INTO T15
Values
("Jim", 20141120,1985,090000,20141005164000);

3: Time function
(Gets the system's current specified month day)
Now () year () month () Day () time ()
Select Now ();
Select year (now ());

INSERT INTO T15
Values
("Jim", Now (), now (), now (), now ());


Datetime/timestamp
CREATE TABLE T16 (
time1 datetime,
Time2 timestamp//Do not assign a value to a field, use system time to assign a value to the field.
);

Insert into T16 (time1) values (20151104103024);

4. Enumeration type
(refers to the value of the field can only be selected within the enumeration range)
Enum ("Value 1", value 2, value N) can only select 1
Set ("Value 1", value 2, value N) can select one to multiple

Examples:
CREATE TABLE t18 (
Name Char (10),
Sex enum ("Boy", "Girl", "no"),
Likes set ("book", "film", "Music", "Football", "game")
);

INSERT INTO t18
Values
("Tom", "Boy", "Book,film,game");

INSERT into t18 values ("Lucy", 2, "music,film");


5. Constraint conditions
NULL allowed: null (default)
Null is not allowed: NOT NULL
Set Defaults: Default

Example: 1
CREATE TABLE t19 (
Name Char (TEN) is not NULL,
Sex enum ("Boy", "Girl", "no");

Example 2:
Set the default value for a field Dealut value
CREATE TABLE T20 (
Name Char (TEN) is not NULL,
The Sex enum ("Boy", "Girl", "no") is not NULL, the default "boy",
Age tinyint unsigned not null defalut 23);

This article is from the "Zhengerniu" blog, make sure to keep this source http://liufu1103.blog.51cto.com/9120722/1656826

Third, MySQL data type