mysql5.6 Primary Use method study fourth day

MySQL data type

Integer data type

tinyint very small integer 1 bytes

smallint Small integer 2 bytes

Mediumint a medium-sized integer 3 bytes

bigint Large integer 8 bytes

Depending on the number of bytes used to calculate the range of values for each data type tinyint requires 1 bytes (8bits) to store, then the maximum value of tinyint unsigned number is 2^8-1, that is, the 255;tinyint signed maximum value is 2^7-1, that is, 127

When you create a field in a table in a database, you often encounter an int (11) such that the number in parentheses represents the display width specified by the data type, specifying the number of digits in the numeric value that can be displayed. For example, suppose you declare a field of type int:

Year Int (4)

This statement indicates that the data in the year field generally displays only 4 digits

Note: The display width is irrelevant to the value range of the data type. The display width only indicates that MySQL may display the maximum number of digits, the number of digits less than the specified width will be filled by spaces, if the value greater than the display width is inserted, as long as the system does not exceed the value range of the type integer, the value can still be inserted, and can be displayed. For example, by adding a value of 19999 to the Year field, when you use Select to query the value of the column, MySQL will display a full 5-digit 19999 instead of a 4-digit number.

Default display width added by the system

tinyint (4) smallint (6) mediumint (9) int (one) bigint (20)

In MySQL, floating and fixed-point numbers are used to represent decimals, floating-point types are float and double, and fixed-point types have decimal

Both floating-point and fixed-point types can be represented by (M,n), where M represents precision, represents a total number of digits, N represents a scale, and represents the number of decimal places.

A float requires 4 bytes of storage, a double requires 8 bytes of storage, and a decimal (m,n) requires m+2 bytes of storage.

Decimal is stored in a string, the maximum possible range of flexion is the same as a double, but its effective range is determined by the values of M and D.

Date and Time type

Datetime,date,timestamp,time,year

Each type has a valid range of values, and when a value is specified that is indeed illegal, the system will insert a value of 0.

The year type is a single-byte type that requires 1 bytes of storage and can be used in various forms to specify year values

1. Indicate the input format ' YYYY ' or YYYY in a four-bit string or four-bit number format

2. Year in two-bit string, range from ' 00 ' to ' 99 ' 00 ' ~ ' 69 ' for 2000 to 2069 ' 70 ' ~ ' 99 ' represented as 1970 to 1999, ' 0 ' and ' 00 ' represent the same value, all 2000

3. Year, in two-digit numbers, with a range of 1~99,1~69 representing 2001~2069,70~99 indicating that the 1970~1999,0 value will be converted to 0000, not 2000.

Note: Need to represent 2000, use ' 0 ' or ' 00 '

The time type is used to represent temporal information, which needs to be stored with 3 bytes and a format of ' HH:MM:SS '. The time type can represent an interval between a past time or two events, so it can be greater than 24 hours, or even negative.

Expression method: ' d HH:MM:SS ' non-strict syntax: ' HH:MM:SS ' hh:mm ' d hh:mm ' d HH ' SS ', d for day, take the value between 0~34. When the database is inserted, D is converted to an hour save, in the form "d*24+hh".

Presentation method: ' Hhmmss '

Note: If you use an illegal value when inserting data, the system is logged as 00:00:00

Note: Assigning a shorthand value to the time column should be noted: if there is no colon, the rightmost two bits are assumed to represent seconds if MySQL interprets the value. ' 1112 ' and 1112 are expressed as 00:11:12, ' 12 ' and 12 are represented as 00:00:12. Conversely, if you use a colon in the time value, you will be treated as a normal person's understanding of ' 11:12 ' means 11:12:00, not 00:11:12

Note: In the use of ' D HH ' format, the hour must use a double-digit value, if it is less than 10 hours, should be preceded by 0, for example ' 3 02 ' is translated into 74:00:00

The date type is only dated and has no time portion and requires 3 bytes of storage. The table stores the format as ' Yyyy-mm-dd '. When assigning a value to a date type, you can use the following form:

1. ' Yyyy-mm-dd ' or ' YYYYMMDD ' range ' 1000-01-01 ' ~ ' 9999-12-3 '

2. ' Yy-mm-dd ' or ' YYMMDD ' string format represents the date YY value 00~69 (2000~2069), 70~99 (1970~1999)

3. Date in YY-MM-DD or YYMMDD number format, same as above

4. Use Current_date or Now () to insert the current system date.

DateTime type storage requires 8 bytes, table stores date format ' Yyyy-mm-dd HH:MM:SS '

1. ' Yyyy-mm-dd HH:MM:SS ' or ' YYYYMMDDHHMMSS ' string format value, value range ' 1000-01-01 00:00:00 ' ~ ' 9999-12-3 23:59:59 '

2. ' Yy-mm-dd HH:MM:SS ' or ' YYMMDDHHMMSS ' string format representation of the date, YY is 00~69,70~99.

3.yy-mm-dd the date and time represented by the HH:MM:SS or YYMMDDHHMMSS number format

The display format of the timestamp is the same as DateTime, the display width is fixed at 19 characters, the storage requires 4 bytes, but the range of timestamp column is less than the value range of datetime, for ' 1970-01-01 00:00:00 ' utc~ ' 2038-01-19 03:14:07 ' UTC

String Type Char,varchar,binary,varbinary,blob,text,enum,set

You can store string data, or you can store binary data for other data, slices, and sounds.

mysql5.6 Primary Use method study fourth day