Mysql database learning (2): data type (value type date and time type character _ MySQL

Mysql database learning (2): data type (numerical date and time string type) Mysql database learning (2): data type (value type date and time type character _ MySQL

Data type

Value type

Date and time type

String type

I. numeric type

Integer

Tinyint [M] [unsigned] [zerofill] // [] indicates optional. Here, M indicates the display width, which is not a value range. if the display width is not enough, fill it with 0.
Bool is the alias of tinyint (1)

Floating point type

IEEE 754 floating point representation

S: symbol bit

E: exponential bit

M: The ending digit, also known as the valid digit

N = (-1) ^ S * M * 2 ^ E

Float of a single precision can be 4 bytes in the range of-3.402823466E + 38 ~ 3.402823466E + 38, precision about 7 characters
Double precision 8 bytes, value range:-1.7976931348623157E + 308 ~ 1.7976931348623157E + 308, with an accuracy of about 15 characters

DOUBLE [(M, D)] [UNSIGNED] [ZEROFILL] // The total number of digits of M. D is the number of digits after the decimal point. here is the value range.
Real is the alias of double.

Fixed point type

DECIMAL [(M [, D])] [UNSIGNED] [ZEROFILL] // The default M value is 10, and the default D value is 0.
The value range of decimal is the same as that of double, but it has higher precision.
Decimal storage method, see: http://dev.mysql.com/doc/refman/5.1/zh/precision-math.html
Numeric is the alias of decimal.

SQL Code create table t_number(a tinyint, b tinyint unsigned);insert into t_number values(100, 200);insert into t_number values(-129, 200);insert into t_number values(128, 200);insert into t_number values(127, 200);insert into t_number values(127, -1);create table t_number2(a int(4) zerofill);insert into t_number2 values(123);insert into t_number2 values(123456);create table t_number3(a bool);create table t_number4(a float, b double);insert into t_number4 values(12345678.12345, 12345678.12345);insert into t_number4 values(12345678.12345, 123456789123456789.12345); /* 12345700 | 1.2345678912345678e17 */insert into t_number4 values(12345678.12345, 12345678912345.12345);create table t_number5(a double(5,2));/* -999.99 ~999.99 */create table t_number6(a decimal(30,6));insert into t_number6 values(123456789123456789.12345);create table t_number7(a decimal(5,2));/* -999.99 ~999.99*/

II. string type

Char (M)
Varchar (M) // Here M represents the number of characters

The length of the CHAR column is fixed to the length declared during table creation. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval.

The value in the VARCHAR column is a variable-length string. Length can be set to a value between 0 and 65,535. The maximum valid length of a VARCHAR is determined by the maximum row size and the character set used. For example, if the character set is uft8, a character must be stored in three bytes, and the number of characters can be saved is 65535/3 = 21845. In addition, a character location is required to store the number of characters, therefore, the maximum number of characters that can be entered is 21844. If latin1 is used, only one character is stored in one byte, and the number of characters that can be entered is 65532.

Binary/varbinary

The BINARY and VARBINARY classes are similar to CHAR and VARCHAR. The difference is that they contain BINARY strings. That is, they contain byte strings instead of character strings. This indicates that they do not have character sets and sort and compare the value values based on the column value byte.

String storage requirements

The VARCHAR, BLOB, and TEXT classes are variable-length type. The storage requirement for each type depends on the actual length of the column value (represented by L in the preceding table), rather than the maximum possible size of this type. For example, the VARCHAR (10) column can contain strings with a maximum length of 10. The actual storage requirement is the length of the string (L), plus a record string length byte. For the string 'abcd', L is 4 and the storage requires 5 bytes.
For CHAR, VARCHAR, and TEXT types, the values L and M in the preceding table should be interpreted as the number of characters, and the length of these types in the column definition indicates the number of characters. For example, to save a TINYTEXT value, it must contain L characters and 1 byte.

enum/setSQL Code create table t_enum(sex enum('male', 'female')); /* 1 2 */insert into t_enum values('male');insert into t_enum values('female');insert into t_enum values(1);select sex+0 from t_enum;create table t_set(favourite set('dog', 'cat', 'bird')); /* 1 10 100 */insert into t_set values('dog,cat');insert into t_set values('dog,bird');select favourite+0 from t_set;

Enumeration/set looks like a string in the past, and actually stores an integer.

III. date and time

Date and time look like strings, but they are saved as integers.

SQL Code create table t_datetime (a datetime); insert into t_datetime values ('2017-01-15 10:10:10 '); insert into t_datetime values ('2017-12-31 23:59:59 '); insert into t_datetime values ('2017-01-01 00:00:00 '); insert into t_datetime values ('2017-01-01 00:00:00 '); insert into t_datetime values ('99-01-01 00:00:00 '); insert into t_datetime values ('69-01-01 00:00:00'); create table t_timestamp (a timestamp ); insert into t_timestamp values ('2017-01-15 10:10:10 '); insert into t_timestamp values ('2017-01-19 03:14:07 '); insert into t_timestamp values ('2017-01-19 03:14:08 '); insert into t_timestamp values ('2017-01-19 11:14:07 '); insert into t_timestamp values ('2017-01-19 11:14:08 '); create table t_date (a date); insert into t_date values ('2017-01-01 '); create table t_time (a time);/* indicates the time difference */insert into t_time values ('23: 12: 12'); insert into t_time values ('2017: 12: 12 '); insert into t_time values ('3 23:12:12');/* 3 indicates 3 hours */create table t_year (a year); insert into t_year values (2000 ); insert into t_year values ('20140901'); insert into t_year values (2155 );

Refer:

Introduction to Database Systems

Mysql 5.1 Reference Manual

The above is Mysql database learning (2): data type (value type date and time type character _ MySQL content, for more information, please follow the PHP Chinese network (www.php1.cn )!