mysql-Column Properties

Data type (column type) numeric type

• Numeric data are numeric
• The system divides the numeric type into integers and decimal types

Integer type

• Because SQL needs to consider how to save disk space, because the system will be broken down into 5 types of integral type

1. Tinyint: Mini integer, using one byte storage, indicates a state of 256 (common)
2. smallint: Small integer with 2 bytes of storage, representing a maximum of 65536 states
3. Mediumint: Medium integer with 3 bytes of storage
4. int: standard integer with 4 bytes of storage (common)
5. bigint: Large integer with 8 bytes of storage

• Create a table of integral type

createtableifnotexists my_int(    int_1 tinyint,    int)charset utf8;

• Inserting data

-- 有效数据-- 查看数据为正确数据100,100insertintovalues(100,100);-- 无效数据: 类型限定-- 因为只能存储数值型，类型限制insertintovalues(‘a‘,‘188‘);    -- 错误: 超出范围-- 因为tinyint是有符号的，存储范围是-128-127，若是无符号则存储范围是0-255insertintovalues(255,10000);

• The numeric types in SQL are all signed by default: just the plus or minus
• When you need unsigned data, you need to qualify the data type-unsigned, unsigned starting from 0
• Add a field that is not signed

altertableadd int_3 tinyint unsigned;-- 增加一条数据,此时的255可以存储进去insertintovalues(127,222,255);

• The structure of the tinyint (3) Unsigned,tinyint (4) will exist after the column type is found in the Type column when the data structure is produced.
• tinyint (3) unsigned represents unsigned data, 3 represents the width of the display, 255 is the width of 3 bits, and the plus sign is omitted
• Tinyint (4) represents the signed data, 4 represents the display width (including the symbol), and 111 is a 4-bit display width (including the minus sign)
• The display width has no special meaning, just the default is to tell the user can display the form, although you can customize the length of the display width, but do not control its own stored data size, that is, the display width modification does not affect the stored data size
• The simple field width does not make sense, so it will be used with the 0 fill (zerofill), so that when the data width is not enough to display the width of the time, it will automatically fill with Zerofill, in front of which use 0 to fill the width of the required width of the display, Zerofill will cause the value to automatically become unsigned

-- 新增一个宽度为2的零填充行\altertableaddcolumn tinyint(2) zerofill;-- 插入数据insertintovalues(100,100,100,1),(200,200,200,2);-- 插入的数据1,2 会显示为01,02

• Zerofill Fit display width meaning: Ensure data format is consistent

Decimal type

• Floating-point types are classified as floating-point and fixed-point in SQL

1. Floating point: decimal point floating, limited precision, loss of precision
2. Ding typical: Fixed decimal point, precision fixed, no loss of precision

Floating point Type

• Floating-point data is a type of precision data that loses precision (rounding) beyond a specified range
• Float type: Float, double two types
• How to use floating-point type:

1. Direct float: Indicates no decimal part
2. Float (m,d): M for total length, D for fractional part length, integer part length equal to m-d

--Create a tableCreate Table if  not existsMy_float (float_1float,--No decimal partsFloat_2float(Ten,2),--Total length is 10, integer is 8Float_3float(5,2)) CharSet UTF8;--Inserting data--Inserting dataInsert  intoMy_floatValues(1000.10,1000.10,1000.10);--Meet the criteriaInsert  intoMy_floatValues(1234567890,12345678.90,1234.56);--Meet the criteriaInsert  intoMy_floatValues(3e38,3.01e7,1234.56);Insert  intoMy_floatValues(9999999999,99999999.99,9999.99);--Maximum value--Insert data beyond lengthInsert  intoMy_floatValues(123456,1234.123456768,123.9876543);--Decimal Section OKInsert  intoMy_floatValues(123456,1234.12,12345.56);--Integer part exceeded

• Floating-point data insertion: Integral parts cannot be longer than length, but fractional parts can be out of length (the system will automatically round up)
• If the floating-point number exceeds the accuracy range, it is bound to be rounded.
• Floating-point number if the integer part exceeds the specified length because of system carry, the system is allowed to be established

Fixed-point type

• Absolute guarantee that the integer part will not be rounded (no loss of precision)
• The fractional part may be rounded (in theory, no loss of precision in fractional parts)

--Create a fixed-point table, in contrast to floatCreate Table if  not existsMy_decimal (F1float(Ten,2), D1decimal(Ten,2)) CharSet UTF8;--Inserting dataInsert  intoMy_decimalValues(12345678.90,12345678.90);--Valid dataInsert  intoMy_decimalValues(1234.123456,1234.1234356);--Fractional part exceeded: OKInsert  intoMy_decimalValues(99999999.99,99999999.99);--no problem.Insert  intoMy_decimalValues(99999999.99,99999999.999);--Carry out of range

• The integer portion of the fixed-point number 一定 cannot exceed the length (rounding is not possible), and the length of the fractional part can be exceeded arbitrarily (system auto-rounding)
• Floating-point numbers are no problem if rounding results in length overflow, but the fixed-point number is not

String Type Char

• Fixed-length string: Disk (two-dimensional table) when defining the structure, the storage length of the final data is determined
• CHAR (L): l represents length, can be stored in characters, maximum length value can be 255
• Cahr (4): Requires 4*3=12 bytes in a UTF8 environment

varchar

• Variable length string: varchar, when allocating space, according to the maximum space allocation, but in fact, how much ultimately used, is based on the specific data to determine

• Varchar (L): L indicates that the length of the character theory is 65,536 characters, but it will be 1 to 2 bytes to determine the actual length of the storage, when the data is less than 255, will be more than 1 bytes to determine the actual storage length, when the data is more than 255, less than 65535, There will be 2 more funds to determine the actual storage length, but in fact if the length is more than 255, neither fixed nor long, use the text string

• Varchar (10): Store 10 Chinese characters, UTF8 environment, ten * 3 + 1 = (bytes)

? Storage of 3 Kanji: 3 * 3 + 1 = ten (bytes)

• Fixed length, variable length selection

• Fixed-length disk space is wasteful, but highly efficient. If the data is basically determined to be the same length, use fixed length, such as mobile phone number, ID card, etc.

• More disk space savings, but inefficient, if the data can not determine the length, different data changes, the use of variable length, such as name, address, etc.

Text, blob

• If the amount of data is large, more than 255 characters will typically use a text string
• Text strings are categorized according to the village's coarse data format: text and blobs
• Text: Storing text, binary data is actually a storage path
• BLOB: Store binary data, basic no

Enum

• Enumeration string: Enum. Design the most likely results beforehand, in fact the data stored must be one of the well-defined data, equivalent to the radio in HTML
• How enumerations are used
• Definition: Enum (List of possible elements), enum (' Man ', ' woman ', ' secrecy ')
• Use: Store data and store only the data defined above

--Create a tableCreate Table if  not existsMy_enum (Gender enum (' man ',' Woman ',' secrecy ')) CharSet UTF8;Insert  intoMy_enumValues(' man '),(' Woman '),(' secrecy ');--Insert SuccessInsert  intoMy_enumValues(' man ');--Error: No this element--Take the field results out of the +0 operation, you will find that gender+0 is the number of man,woman,secrecy respectively correspondingSelectGender +0, gender fromMy_enum;--values are inserted into the enumeration element so that inserting data 1 represents man,2 on behalf of woman,3 representative secrecyInsert  intoMy_enumValues(1),(2),(3);

• The role of enumerations

1. Canonical data format, data can only be one of the specified data
2. Save storage space (Enum has an alias: Radio box) because the enumeration stores values instead of the string itself

• Enumeration store, which is the number of all well-defined data in memory in the order in which elements appear, starting from 1, so you can also use numbers to insert data
• Enumeration principle: Enumeration in the data specification (when defined), the system will automatically establish a number and enumeration elements of the corresponding relationship (relationship to the log): Then when the data is inserted, the system automatically converts the characters to the corresponding digital storage, and then when the data extraction, The system automatically converts the value to the corresponding string display.
• Because the enumeration actually stores numeric values, you can insert them directly.

Set

• Collections are similar to enumerations, and are actually stored as numeric values rather than strings (collections are equivalent to checkboxes, multi-marquee)
• How to use Collections
• Definition: Set (element list)
• Use: You can use the elements in the list (multiple), separated by commas

--Create a tableCreate Table if  not existsMy_set (HobbySet(' Basketball ',' Football ',' Table tennis ',' Badminton ',' Volleyball ',' Billiards ',' tennis ',' Baseball ')) CharSet UTF8;--Inserting dataInsert  intoMy_setValues(' Football, billiards, tennis ');Insert  intoMy_setValues(3);--View collection dataSelectHobby +0, hobby fromMy_set;--98 turns into binary = 64 + 32 + 2 = 01100010, corresponding storage is football, billiards, tennis--3 turns into binary = 64 + 32 + 2 = 00000011, so insert into My_set values (3); The actual storage is ' basketball ', ' Football '-Reverses the order in which the elements appear, does not affect the storage, and the system follows the list of elements to match the orderInsert  intoMy_setValues(' tennis, billiards, football ');

• The collection of primary users is to standardize data and save space, because the background language maintenance costs are relatively high, so use very little

Time Date Type

• DateTime: Time date, format is Yyyy-mm-dd HH:ii:ss, the range is from 1000-9,999, there are 0 values: 0000-00-00 00:00:00
• Date: Dates, which is the day part of datetime
• Time: Period (segment), between a specified interval,-time to + time
• Timestamp: timestamp, not timestamp, only YYYY-MM-DD HH:ii:ss format starting from 1970 is exactly the same as DateTime
• Year: Vintage, two forms, years (2) and yearly (4): 1901-2156

--Create Time date tableCreate TableMy_date (D1 datetime,d2Date, D3 Time, D4timestamp, D5 Year) CharSet UTF8;--Inserting dataInsert  intoMy_dateValues(' 2015-9-28 11:50:36 ',' 2015-9-28 ',' 11:50:54 ',' 2015-9-28 11:51:08 ', -);--Time use negativeInsert  intoMy_dateValues(' 2015-9-28 11:50:36 ',' 2015-9-28 ',' -11:50:54 ',' 2015-9-28 11:51:08 ', -);Insert  intoMy_dateValues(' 2015-9-28 11:50:36 ',' 2015-9-28 ',' -211:50:54 ',' 2015-9-28 11:51:08 ', -);Insert  intoMy_dateValues(' 2015-9-28 11:50:36 ',' 2015-9-28 ','-2 11:50:54 ',' 2015-9-28 11:51:08 ', -);---2 last 2 days:-year can use 2-bit or 4-bitInsert  intoMy_dateValues(' 2015-9-28 11:50:36 ',' 2015-9-28 ',' 11:50:54 ',' 2015-9-28 11:51:08 ', the);Insert  intoMy_dateValues(' 2015-9-28 11:50:36 ',' 2015-9-28 ',' 11:50:54 ',' 2015-9-28 11:51:08 ', -);--Timestamp: Modify recordUpdateMy_dateSetD1 =' 2015-9-28 11:55:45 ' whered5 =2069;

mysql-Column Properties