Preface ：
Before that, I will briefly introduce what is a database and the basic common database commands and DDL（MySQL Data definition language ）, The following will introduce the very important knowledge about database , be called 【 data type 】, Not just in MySQL There is a data type in the , It exists in many programming languages 【 data type 】 The term .
So what is a data type ？ I will introduce them one by one MySQL Data types and keywords of data types commonly used in 、 Scope, etc .

1、 What is data type

Let's first talk about the languages of various countries on earth , In China, our language is called Chinese , The characters used are called Chinese characters , There is English in the United States , Russia has Russian , Germany has German , Japan has Japanese , Each country has its own language and writing font .
data type , It's very similar to the writing fonts of various countries , Each type represents a different way of writing （ Input or output ）, But there are also very similar types, but the input and output are different , Like American English and British English , Simplified and traditional Chinese characters , From a certain point of view , They are all Chinese characters , But writing it out is different .
stay MySQL Multiple data types are supported in , It is mainly divided into three categories ：
① Numerical type
② Character
③ date / Time type

1.1、 Numerical type

Numerical data type is mainly used to store data of numerical class , Different data types , Different value ranges are provided , The greater the range of storage required , The more storage space you need .
Numerical type ： Integer types 、 Floating point decimal type 、 Fixed point decimal type

1.1.1、 Integer types

		|	 Type the name 		|	 Occupancy space ( byte )  			|	 Range 							|  explain 
		|	TINYINT		|	1  |	 It's symbolic ：-128~127、 No sign 0 ~255					|  Very small integers 
		|	SMALLINT	|	2  |	 A signed ：32768~32767、 Unsigned 0 ~65535				|  Small integers 
		|	MEDIUMINT	|	3  |	 A signed ：-8388608~803388607、 Unsigned 0 ~16777215		|  An integer of medium size 
		|	INT			|	4  |	- 2147483647~2147483647、 Unsigned 0 ~4294967295		|  Ordinary size integers 
		|	BIGINT		|	8  |	 This is very long 											|  Big integers 

As can be seen from the table , The least occupied byte is TINYINT type , Most of all BIGINT type , We can Calculate the range of data types according to the number of bytes occupied , The range of signed types is -(2 Of n-1 Power )-1 ~ (2 Of n-1 Power ), The unsigned type range is 0~(2 Of n Power -1),n It stands for ( Byte value *8bit), because 1 Bytes need 8 bit Storage space .

1.1.2、 practice

# First create a database 
mysql> CREATE DATABASE IF NOT EXISTS data_test;
# Access to database 
mysql> use data_test;
Database changed
# Create data table 
mysql> CREATE TABLE IF NOT EXISTS test(id INT(4),name varchar(5));
Query OK, 0 rows affected (1.38 sec)

In the table id The data type of the field is INT(4), there 4 Is the data width of the integer type , Specify the number of values that can be displayed , When the number of digits of the value is less than the specified width, it will be filled with spaces , When it's bigger than , As long as the value is not greater than the value range of the data type , The value will still be inserted , And it can show .

# Insert a line here id The value width is greater than the specified data type width 
mysql> INSERT INTO test VALUES(10000,"KING");
Query OK, 1 row affected (0.11 sec)
# Data table view ( The value can still be displayed )
mysql> SELECT * FROM test;
+-------+------+
| id    | name |
+-------+------+
| 10000 | KING |
+-------+------+
1 row in set (0.13 sec)

If no width is specified when creating the data table , The system will specify the default width value for each type .

# Create new table , Each field uses a different integer type , Do not set the width value 
mysql> CREATE TABLE test_2(x TINYINT,y SMALLINT,z MEDIUMINT,m INT,n BIGINT);
Query OK, 0 rows affected (0.30 sec)
# View table structure (DESC  The command is used to view the structure of the data table )
mysql> DESC test_2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | tinyint(4)   | YES  |     | NULL    |       |
| y     | smallint(6)  | YES  |     | NULL    |       |
| z     | mediumint(9) | YES  |     | NULL    |       |
| m     | int(11)      | YES  |     | NULL    |       |
| n     | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

When we create the data table without adding the display width , The system adds different width values , Ensure that all values within the value range can be obtained for each data type , The default display width of integer type is the same as that of the minimum value of signed number (- The number also takes up a width ).
notes ： Display width is only used to display the width of data insertion, which has nothing to do with the value range , It can't limit the occupied space .

1.2、 Floating point decimals and fixed-point decimals

There are integers in life , There must be decimals ,MySQL Floating point numbers and fixed-point numbers are used to represent decimals in .
Floating point type ：
①FLOAT： Single precision floating point type
②DOUBLE： Double precision floating point type
Point type ：
DECIMAL(M,D): Fixed point decimal type ( Compression of the “ Strictly ” Fixed-point number )
notes ： Both floating-point and fixed-point numbers can be used (M,N) Express ,M The total number of digits ,D Number of digits after decimal point .

1.2.1、 practice

# Create new table , Set the data type to floating-point and fixed-point ,M=5,D=2
mysql> CREATE TABLE float_test(x float(5,2),y DOUBLE(5,2),z DECIMAL(5,2));
Query OK, 0 rows affected (0.30 sec)
# insert data 
mysql> INSERT INTO float_test VALUES(123.45,123.45,123.45);
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO float_test VALUES(123.456,123.456,123.456);
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO float_test VALUES(123.4,123.4,123.4);
Query OK, 1 row affected (0.14 sec)
# Because the total number of digits is 5 digit , After the total number of digits exceeds , System error reporting , Say insert x,y,z The width of the value exceeds the set total number of digits 
mysql> INSERT INTO float_test VALUES(123456.456,123456.456,123456.456);
1264 - Out of range value for column 'x' at row 1
mysql> INSERT INTO float_test VALUES(123.45,1234.456,123456.456);
1264 - Out of range value for column 'y' at row 1
mysql> INSERT INTO float_test VALUES(123.45,123.45,123456.456);
1264 - Out of range value for column 'z' at row 1
# View the normally inserted data 
mysql> SELECT * FROM float_test;
+--------+--------+--------+
| x      | y      | z      |
+--------+--------+--------+
| 123.45 | 123.45 | 123.45 |
| 123.46 | 123.46 | 123.46 |
| 123.40 | 123.40 | 123.40 |
+--------+--------+--------+
3 rows in set (0.04 sec)

Looking at the inserted data, you can find , When the number exceeding the set total digits is after the decimal point , The system will display after automatic rounding , When the number of digits is less than the set total number of digits , The system automatically replenishes 0;

# Create new table , Set floating-point type and fixed-point type without precision and scale , Check its default values 
mysql> CREATE TABLE test_float(x FLOAT,y DOUBLE,z DECIMAL);
Query OK, 0 rows affected (0.28 sec)
# View table structure 
mysql> DESC test_float;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| x     | float         | YES  |     | NULL    |       |
| y     | double        | YES  |     | NULL    |       |
| z     | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

FLOAT and FOUBLE When precision is not specified , The actual accuracy will be used by default （ Determined by computer hardware and operating system ）,DECIMAL If the precision is not specified, it defaults to (10,0);
notes ： Stored as a fixed-point string , In scenes requiring high accuracy ( Such as currency 、 Scientific data, etc ) Use DECIMAL Type is better

summary ： When learning data types, it is inevitably boring , A wide variety , This is just one part of the data type module , About learning MySQL Knowledge , I suggest practicing more , Practice well before continuing , Practice while learning , The data type has not been written yet , Next write date and time type . Thank you for watching ！ If you have any suggestions, you are welcome to me .