Mysql table creation and column attributes

Mysql table creation and column attributes

1. INTEGER (int, tinyint, smallint, etc)

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------

# Creating a table
Create table t1 (
Num TINYINT
) CHARSET utf8 engine myisam;
# Add a column of num2, whose type is tinyint and whose attribute is unsigned.
Alter table t1 ADD num2 tinyint unsigned;
# Add a column of num3, whose type is tinyint, whose attribute is zerofill filling and Its width is 4. This column is not within the limit.
Alter table t1 ADD num3 TINYINT (4) ZEROFILL;

# Add a num4 column with the int type and 4 width

Alter table t1 ADD num4 TINYINT (4 );

# Change the column type of num4 to int.
Alter table t1 MODIFY num4 INT (4 );

 

# Field maximum value (overflow) test
Insert into t1 VALUES (200 );
Insert into t1 VALUES (-128 );
Insert into t1 VALUES (-129 );
Insert into t1 VALUES (-1,200 );
Insert into t1 VALUES (-1,256 );
Insert into t1 VALUES (-128,-1, 2 );

 

2. float (float, double, decimal)

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------

Create table t2 (

Num FLOAT (6, 2)
) CHARSET utf8 engine myisam;
# Num FLOAT (6, 2), 6 indicates the maximum number of digits, 2 indicates the number of decimal places, so the maximum value he can represent is 9999.99
Insert into t2 VALUES (1234.56 );
# The following 12340 overflows. This location can only store up to 9999
Insert into t2 VALUES (12340 );

 

Note: float and double have precision loss. decimal is a fixed-point type and will not lose precision.

 

Iii. struct (char, varchar, text, enum, etc)

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------

If the char type is not M characters long enough, it will be followed by spaces. When you extract it, remove the spaces on the right.

Note: This indicates that if there is a space on the right side of the data itself, it will be lost.

# Char and varchar tests

Create table t3 (
N1 CHAR (10 ),
N2 VARCHAR (10)
) CHARSET utf8 engine myisam;

Insert into t3 VALUES ('ghostw', 'ghostw ');

Select concat (', n1,'), concat (', n2,') from t3;

We can see that the value stored in the n1 column is fixed length, and the space on the right is lost when it is obtained.

 

 

 Create table t4 (

Gender ENUM ('male', 'female ')
) CHARSET utf8 engine myisam;

Insert into t4 VALUES ('male ');
Insert into t4 VALUES ('female ');
# Man is not in the enumerated range and cannot be inserted
Insert into t4 VALUES ('Man ');

 

 

4. Test the time type (year, time, date, datetime, timestamp, unsigned int)

Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------
Create table t5 (
T1 YEAR,
T2 TIME,
T3 DATE,
T4 DATETIME,
T5 TIMESTAMP
) CHARSET utf8 engine myisam;

Insert into t5 (t1) VALUES ('32 ');
Insert into t5 (t1) VALUES ('20140901 ');
Insert into t5 (t1) VALUES ('20140901 ');
Insert into t5 (t1, t2, t3, t4) VALUES ('000000', '20: 02: 34', '2017-03-12 ', '2017-03-12 20:02:34 ');

Note: Generally, the time is stored in int type for ease of calculation. The timestamp type will automatically Insert the current time.