MySQL data table column type and column constraint one

MySQL data table when defining columns, you need to determine the data type of the column, as well as the related properties of the column or constraints, then the MySQL column type can be broadly divided into the following:

The column types can be categorized as follows:

1. Numeric type

Also divided into integers and decimals

2. Time and Date type

3. Character type

A little C-based students should not be very unfamiliar, here I will not explain too much, a little explanation on the line.

The whole number of parts

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/85/F1/wKiom1ewSx3Bs78UAAAx2PXwr1s455.png "title=" 1.png " alt= "Wkiom1ewsx3bs78uaaax2pxwr1s455.png"/>

The whole number of points to be aware of is the byte and signed and unsigned differences

We know 1byte=8bit, so 2 of the 8-time Square is 0-255.

There is a conversion of the students should know, then signed and unsigned is that he needs 1 bits to identify positive negative numbers, so to subtract 1 bits, natural is 7 bits, 2 7 times is 127, we use unsigned to identify as unsigned.

The larger the number of bytes, the more bits 8, 12, 24, 32, 64, and the minimum and maximum data are converted to 10.

0000 0000---1111 1111 conversion to 10 binary is 0-255 to 16 binary is 0-00FF

Then we create the field, we have to consider the approximate range of the value, such as we want to store a person's age should be tinyint is more appropriate, so do not waste space, then the age is no negative, so is unsigned.

As we create a student table

CREATE TABLE Student (

ID int,

Name varchar (5),

Age tinyint unsigned)

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/85/F2/wKiom1ewThfChuHYAAAiAnhHovA831.png "title=" 2.png " alt= "Wkiom1ewthfchuhyaaaianhhova831.png"/>

We found that after the creation, we looked at the type of the age field, followed by parentheses and 3, which indicates its display width, which means that we can create the field as well:

Age tinyint (3) indicates that the display width does not limit storage space

field name [Column Type] (display width)

Next there is a column property that is Zerofill 0 padding

We now insert a piece of data and view

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/85/F2/wKiom1ewT4eCFtVLAAASF2NV46c356.png "title=" 3.png " alt= "Wkiom1ewt4ecftvlaaasf2nv46c356.png"/>

We did not define its display width, and next we update one of its fields.

ALTER TABLE student modify age tinyint (2) Zerofill

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/85/F2/wKiom1ewT_LhdQJXAAASGjC6hHM699.png "title=" 3.png " alt= "Wkiom1ewt_lhdqjxaaasgjc6hhm699.png"/>

And then insert one more piece of data

INSERT into student values (null, ' Jack ', 8);

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/85/F2/wKiom1ewUGTir0cwAAAT-oKUQic307.png "title=" 3.png " alt= "Wkiom1ewugtir0cwaaat-okuqic307.png"/>

Check that it will automatically and add a number to the front 0 this is the use of Zerofill.

So we're going to specify that it's 2-bit wide, is it only possible to insert a value between 1-99?

Next we insert 123, can this value be inserted?

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/85/F1/wKioL1ewUOzAq8_nAAAhGeE9tUI700.png "title=" 3.png " alt= "Wkiol1ewuozaq8_naaahgee9tui700.png"/>

We found that pluggable was successful, stating that it did not limit storage space.

The same is true of other int,smallint,mediumint,bigint.

Next, let's learn about decimals.

Decimals are also divided into two categories, 1 is the floating point 2 is the fixed-point number

Floating-point numbers are single-precision and double-precision

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/85/F1/wKioL1ewUgDBJOHbAAAheG_9ptE619.png "title=" 3.png " alt= "Wkiol1ewugdbjohbaaaheg_9pte619.png"/>

Single-and double-precision they are 32-bit and 64-bit, the storage space is larger, but the storage accuracy is not very high, there will be loss of precision situation

For example, we create a single-precision and double-precision to test its accuracy

ALTER TABLE student Add Money float (8,2)//indicates that the number of decimal digits is two bits and the integer is 6 bits

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/85/F1/wKioL1ewU2CSVPA3AAAiL_BxIDk753.png "title=" 3.png " alt= "Wkiol1ewu2csvpa3aaail_bxidk753.png"/>

Next we insert a piece of data

INSERT into student values (null, ' Han ', 24,123456.12);

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/85/F2/wKiom1ewU9vQ59CGAAAnJLrcraI351.png "title=" 3.png " alt= "Wkiom1ewu9vq59cgaaanjlrcrai351.png"/>

We find that there is no problem, and then we insert the following data

INSERT into student values (null, ' Lili ', 35,1234567.1);

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/85/F2/wKiom1ewVF-DXxEvAAAg5hd2Z8g826.png "title=" 3.png " alt= "Wkiom1ewvf-dxxevaaag5hd2z8g826.png"/>

We found that when the integer part exceeded 6 digits, the data changed a lot, and the original inserted 1234567.1 did not actually insert successfully.

Then let's go back to the field.

ALTER TABLE student change Money Qian float;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/85/F2/wKiom1ewVSvA_M5EAAAcJav199o918.png "title=" 3.png " alt= "Wkiom1ewvsva_m5eaaacjav199o918.png"/>

We insert the following data

INSERT into student values (null, ' Titi ', 26,1234567890.1234567890);

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/85/F2/wKiom1ewVZiTBha9AAAnDaACvLg300.png "title=" 3.png " alt= "Wkiom1ewvzitbha9aaandaacvlg300.png"/>

We found that although the data seems to be inserted successfully, but when we retrieve the data display is not correct Ah, this is the loss of its accuracy, so that if we want to do a money-related system, whether the floating point is float,double or not, because the precision is not high, By testing we know that the precision of float is 6 bits, then the accuracy of Dobule is 16 bits

Next we test double

Altet table Student Change Qian money double;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/85/F2/wKioL1ewVpCyb7IDAAAlq2KAJH4639.png "title=" 3.png " alt= "Wkiol1ewvpcyb7idaaalq2kajh4639.png"/>

Next we also insert the following data

INSERT into student values (null, ' Keke ', 28,1234567890.1234567890);

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/85/F2/wKiom1ewV6yQ1VIWAAAvd9GLers480.png "title=" 3.png " alt= "Wkiom1ewv6yq1viwaaavd9glers480.png"/>

We found that the integral part of 10 bits is correct, but the fractional part is 7 digits.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/85/F2/wKioL1ewWEayyhdMAAA2ujn9w8E049.png "title=" 3.png " alt= "Wkiol1ewweayyhdmaaa2ujn9w8e049.png"/>

When we insert a 15-bit positive number, the number of decimal digits is only 2 bits, so its accuracy can be said to be 16 or 17 bits, if we insert a positive number more than 15 digits is

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/85/F2/wKioL1ewWLeil8D1AAArLucpFfU578.png "title=" 3.png " alt= "Wkiol1ewwleil8d1aaarlucpffu578.png"/>

We find that it shows that the scientific method is used to show the 16 of the 1.2345678901234568*10, so its effective range is 12345678901234568 even decimals are omitted.

Through the above tests we found that their storage data will be lost in accuracy. Therefore, we must consider their accuracy when developing the problem. The same floating-point number is also signed and unsigned, and we can define the appropriate integer and decimal digits at the time of definition.

The format of their definition is this:

Float (m,d)

A double (m,d)//m is a total length of D is a representation of the number of decimal digits

If we insert the data, whether it is an integer or a decimal more than the loss of precision, so it is the storage space is limited, and the integer display width is different Oh!

This article is from "Le Learning" blog, please make sure to keep this source http://lestudy.blog.51cto.com/6378140/1837851

MySQL data table column type and column constraint one