当前位置:网站首页>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
边栏推荐
- LeetCode_ DFS_ Medium_ 1254. Count the number of closed islands
- How to render web pages
- Machine learning (VI) -- Bayesian classifier
- Pctp test experience sharing
- Go language self-study series | golang structure as function parameter
- [indexof] [lastIndexOf] [split] [substring] usage details
- RSA encryption and decryption signature verification
- Find the sum of simple types of matrices
- I don't understand time, timestamp and time zone. Look at this article
- Valgrind and kcache grind use run analysis
猜你喜欢
Common errors of VMware building es8
Strength comparison vulnerability of PHP based on hash algorithm
DJ music management software pioneer DJ rekordbox
《数字电子技术基础》3.1 门电路概述、3.2 半导体二极管门电路
Go language learning notes - exception handling | go language from scratch
[in-depth good article] detailed explanation of Flink SQL streaming batch integration technology (I)
The K neighbors of each sample are obtained by packet switching
Arbre de dépendance de l'emballage des ressources
Multi view depth estimation by fusing single view depth probability with multi view geometry
Vivo, hardware safe love and thunder
随机推荐
[in-depth good article] detailed explanation of Flink SQL streaming batch integration technology (I)
653. Sum of two IV - input BST
A must see wechat applet development guide 1 - basic knowledge
DMP engine work summary (2021, lightsaber)
How to protect open source projects from supply chain attacks - Security Design (1)
npm ERR! network
LeetCode 1611. The minimum number of operations to make an integer 0
Number theory to find the sum of factors of a ^ B (A and B are 1e12 levels)
[indexof] [lastIndexOf] [split] [substring] usage details
Kettle experiment conversion case
How to render web pages
[C language] document operation
Principle of synchronized implementation
Distributed message oriented middleware framework selection - Digital Architecture Design (7)
Kettle experiment (III)
Kettle experiment
Cross domain configuration error: when allowcredentials is true, allowedorigins cannot contain the special value "*“
Leetcode-199 - right view of binary tree
基于ThinkPHP5版本TRC20-资金归集解决方案
Mini - exercice MySQL (seulement pour les débutants, pas pour les non - débutants)