1. Integer
Represents an integer. Usually, id is set to integer int
Storage range (- 214748364821483647)
Emphasis (the width limit after integer is not the storage width at all, but the display width)
For example, here we prove it through four codes:
1.create table t1(id int(1));
insert into t1 values (111111);
From the query statement:
mysql> select * from t1; +--------+ | id | +--------+ | 111111 | +--------+
2.create table t2(id int(10));
insert into t2 values (111111)
From the query statement:
mysql> select * from t2; +--------+ | id | +--------+ | 111111 | +--------+
You can know its true display by adding constraints:
3.create table t3(id int zerofill);
insert into t3 values (1);
From the query statement:
mysql> select * from t3; +------------+ | id | +------------+ | 0000000001 | +------------+
4.create table t4(id int(5) zerofill);
insert into t4 values (1);
According to the query:
mysql> select * from t4; +-------+ | id | +-------+ | 00001 | +-------+
So you don't need to add width after creating an integer
2. Floating point type (decimal)
Floating point type is divided into single precision, double precision and accurate precision.
The maximum number of digits is 255 and the maximum number of decimals is 30
Single precision:
float(255,30)
Double precision:
double(255,30)
Accuracy (decimal is the most accurate)
decimal(60,30)
The maximum number of numbers is 65 and the maximum number of decimals is 30
Let's create three tables to show the accuracy:
create table t5(x float(255,30));
create table t6(x double(255,30));
create table t7(x decimai(60,30));
insert into t5 values(1.1111111111111111111111111111111111111111111111);
According to the query:
mysql> select * from t5; +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+
insert into t6 values(1.1111111111111111111111111111111111111111111111);
According to the query:
mysql> select * from t6; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+
insert into t7 values(1.1111111111111111111111111111111111111111111111);
According to the query:
mysql> select * from t7; +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+
3. Date type:
year 1999
data 1999-11-11
time 08:30:00
datetime/timestamp 1999-11-11 08:30:00
Let's create a show below
create table t8(
id int,
name varchar(16),
a_year year,
b_date date,
c_time time,
reg_time datetime
);
3.1 insert current time:
insert into t8 values (1,'dahai,now(),now(),now(,)now());
According to the query:
mysql> select * from t8; +------+-------+--------+------------+------------+---------------------+ | id | name | a_year | b_date | class_time | reg_time | +------+-------+--------+------------+------------+---------------------+ | 1 | dahai | 2022 | 2022-01-23 | 12:57:32 | 2022-01-23 12:57:32 | +------+-------+--------+------------+------------+---------------------+
3.2 custom digital time
insert into t8 values (1,'dahai',2000,20001111,083000,20001111083000);
According to the query:
mysql> select *from t8; +------+-------+--------+------------+------------+---------------------+ | id | name | a_year | b_date | class_time | reg_time | +------+-------+--------+------------+------------+---------------------+ | 1 | dahai | 2000 | 2000-11-11 | 08:30:00 | 2000-11-11 08:30:00 | +------+-------+--------+------------+------------+---------------------+
3.3 custom string time:
insert into t8 values (1,'dahai','1999','2000-11-11','08:30:00','2000-11-11 08:30:00');
According to the query:
mysql> select *from student; +------+-------+--------+------------+------------+---------------------+ | id | name | a_year | b_date | class_time | reg_time | +------+-------+--------+------------+------------+---------------------+ | 1 | dahai | 1999 | 2000-11-11 | 08:30:00 | 2000-11-11 08:30:30 | +------+-------+--------+------------+------------+---------------------+
datetime/timestamp
In many practical application scenarios, these two date types of MySQL can meet our needs, and the storage accuracy is seconds,
But in some cases, they will show their own advantages and disadvantages. Let's summarize the differences between the two date types.
1. The date range of datetime is 1001-9999, and the time range of TIMESTAMP is 1970-2038.
2.DATETIME uses 8 bytes of storage space, while TIMESTAMP uses 4 bytes. Therefore, TIMESTAMP has higher space utilization than DATETIME.
3. The default value of datetime is null; The field of TIMESTAMP is not null by default, and the default value is CURRENT_TIMESTAMP. (write constraint is required after version 8.0)
Example:
create table time(x timestamp defult current_timestamp)
insert into time vluses();
According to the query:
mysql> select *from time; +---------------------+ | x | +---------------------+ | 2022-01-23 13:13:20 | +---------------------+
4. Character type
char: fixed length
char(5)
varchar: Lengthen
varchar(5)
Note: the width here refers to the number of limited characters
Same point:Width refers to the maximum number of characters stored,It cannot be stored normally if it exceeds
difference
char(5)
'm'--->'m ' 5 Characters
varchar(5)
'm'----> 'm' 1 One more character bytes Is a description of the data)
char(5)
dahai|aa |xxx |f |
varchar(5)
1 bytes+dahai|1 bytes+aa|1 bytes+xxx|1 bytes+f|
varchar(5) mostly uses it. In most cases, the data stored is less than the width of the constraint
5. Add two more knowledge points: enum and set
enum enumeration is one of many, like python boolean type,
The set set is one or more
Example:
create table student_table( id int, name,varchar(20), sex enum('man','woman'), hobbies set('read','play','music') );
insert data
insert into student_table values(1,,'dahai','man','reay,music');
According to the query:
mysql> select *from student;
+------+-------+------+------------+
| id | name | sex | hobbies |
+------+-------+------+------------+
| 1 | dahai | man | read,music |
+------+-------+------+------------+