MySQL Build table

The building of a table is actually the process of declaring a column, building a table statement: The Create Tables table name (column name 1 column Type column property default value, column Name 2 column type column property default value, Index XX (xx)) engine= storage engine charset= character set; for example, create Tabl e stu (id int primary key auto_increment,name varchar () NOT NULL default ", age tinyint unsigned not null default 0,inde x ID (ID)) Engine=innodb Charset=utf8; Note that primary key and auto_increment table can only have one column, and the value of the primary column cannot be duplicated, auto_increment this column must be indexed ( Index/key).

Data type

Numeric-byte unsigned range

tinyint 1Byte 0-255

smallint 2Byte 0-65535

Mediumint 3Byte 0-16777215

int 4Byte 0-9223372036854775807

bigint 8Byte 0-18446744073709551615

An optional parameter for an integer column, unsigned (unsigned), Zerofill (0 padding, zerofill default attribute determined as unsigned), M (display width). For example, CREATE TABLE T1 (ID tinyint (5) Zerofill); Note that the M parameter is only meaningful if used with Zerofill.

Floating point Type

Float (m,d) unsigned zerofill,float range is -3.402823466E+38 to -1.175494351e-38,0,1.175494351e-38 to 3.402823466e+38,m is the total number of digits of precision , d is the number of digits after the decimal point, and if M,d is not written, float (single precision) is accurate to about 7 decimal places and double (double precision) is accurate to about 15 decimal, float/double will have precision loss, and decimal (fixed-point type) will be more accurate.

Character type

Char, fixed length, char (10) The storage space is 10 characters, each row's actual occupied space is 10 characters length, whether write is one or 2 or 8.

varchar, variable length, varchar (10) The storage space is 10 characters, each line of actual occupied space to see the characters written, plus 1-2 bytes to store the character end of the flag.

In general, 20 characters with more than char,20 characters with varchar, the speed of char faster.

The text can hold up to 65,535 characters for storing large pieces of text.

Blod Stores 2 of binary information, used to store images, audio and other 2 binary information, no loss of information.

enum enum type, defined, is within a certain enumeration range, for example, create table T1 (gender enum (' Male ', ' female ')); Notice that you can only insert 2 values for male or female.

The difference between set and Enum is that an enum can only insert a value from an enumeration range, and set can insert several values.

Date-Time Type

Year (1 bytes), Range 1901-2155.

Date dates, range 1000-01-01 to 9999-12-31, note that date must be a character type.

Time, the range -838:59:59 to 838:59:59, and note that timing must be a character type.

DateTime date time, range 1000-01-01 0:00:00 to 9999-12-31 23:59:59, note datetime must be a character type.

Timestamp timestamp, range 1970-01-01 0:00:00 to the current number of seconds, automatically updated by default.

MySQL Build table