当前位置:网站首页>MySQL - Chapter 1 (data types in MySQL)
MySQL - Chapter 1 (data types in MySQL)
2022-04-23 09:20:00 【King_ nul】
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 .
MYSQL data type —— insist , It's possible
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 .
版权声明
本文为[King_ nul]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204230629527602.html
边栏推荐
- Employee probation application (Luzhou Laojiao)
- DMP engine work summary (2021, lightsaber)
- 108. 将有序数组转换为二叉搜索树
- 653. 两数之和 IV - 输入 BST
- RSA 加密解密签名验签
- About CIN, scanf and getline, getchar, CIN Mixed use of getline
- Go language self-study series | golang structure pointer
- [indexof] [lastIndexOf] [split] [substring] usage details
- How does kubernetes use harbor to pull private images
- npm ERR! network
猜你喜欢
Unfortunately, I broke the leader's confidential documents and spit blood to share the code skills of backup files
Experimental report on analysis of overflow vulnerability of assembly language and reverse engineering stack
《信息系统项目管理师总结》第八章 项目干系人管理
112. 路径总和
GoLand debug go use - white record
[SQL Server fast track] view and cursor of database
SAP 101K 411K 库存变化
Multi view depth estimation by fusing single view depth probability with multi view geometry
653. Sum of two IV - input BST
How to protect open source projects from supply chain attacks - Security Design (1)
随机推荐
3、 6 [Verilog HDL] gate level modeling of basic knowledge
Group Backpack
NPM reports an error: operation not allowed, MKDIR 'C: \ program files \ node JS \ node_ cache _ cacache’
Flink reads MySQL and PgSQL at the same time, and the program will get stuck without logs
Initial experience of talent plan learning camp: communication + adhering to the only way to learn open source collaborative courses
ALV树(LL LR RL RR)插入删除
成功的DevOps Leader 应该清楚的3个挑战
Applet error: should have URL attribute when using navigateto, redirectto or switchtab
LeetCode_ DFS_ Medium_ 1254. Count the number of closed islands
資源打包關系依賴樹
AQS & reentrantlock implementation principle
Kettle experiment conversion case
What is monitoring intelligent playback and how to use intelligent playback to query video recording
Mini - exercice MySQL (seulement pour les débutants, pas pour les non - débutants)
LGB, XGB, cat, k-fold cross validation
web页面如何渲染
Give the method of instantiating the object to the new object
Enter "net start MySQL" and "system error 5. Access denied" appears. Detailed explanation of the problem
[in-depth good article] detailed explanation of Flink SQL streaming batch integration technology (I)
STM32 and FreeRTOS stack parsing