How to Decimal Values in MySQL
In this tutorial, we will learn how to use decimal values in a MySQL database.
It is common to use integers to keep count or list products, students, packages, employees, etc.
However, integers might not always suffice our needs. In such a case, we need to use decimal point numbers.
For example, while storing product profits in a sales table for a product-based company like Amazon, it would be common to use decimal values like 12.99
or 5.55555
. Such values play a key role in the statistical analysis of the products.
MySQL helps us store such decimal values in our tables using the DECIMAL()
function. This function takes in two parameters - the first is the total number of characters in the number, and the second is the total number of numbers after the decimal point (.
) in the number.
For example, if we want to store 12.99
, we can call the function DECIMAL(4,2)
. It would entail four numbers or characters in our overall number and two digits after the decimal point.
Similarly, to store the number 5.55555
, we can define our DECIMAL
function as DECIMAL(6,5)
. It would mean that the programmer has assigned five values after the decimal point.
Let us understand how this method works in greater depth.
We will begin by creating a table and inserting a row with the registration date for a student in it. Here we create a table, student_details
, along with a few rows.
-- create the table student_details
CREATE TABLE student_details(
stu_id DECIMAL(7,5) NOT NULL,
stu_firstName varchar(255) DEFAULT NULL
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName)
VALUES(1.2345,"Preet"),
(2.1212,"Rich"),
(3.54,"Veron"),
(4.67235,"Geo"),
(5.41132,"Hash"),
(6.66,"Sachin"),
(7.01,"David");
To view the entries in the data, we use the following code.
SELECT * FROM student_details;
Output:
stu_id stu_firstName
1.23450 Preet
2.12120 Rich
3.54000 Veron
4.67235 Geo
5.41132 Hash
6.66000 Sachin
7.01000 David
As we can see in the code above block, the column named stu_id
stores the identity number of a student in decimal value. It contains a maximum of seven characters with a maximum of five characters after the decimal point (.
).
Therefore, with the help of the INSERT INTO
statement and the DECIMAL()
function, we can efficiently add decimal point values to a column in a table in MySQL.
Related Article - MySQL Query
- How to Sort MySQL Data in Alphabetical Order
- How to Enable Slow Query Log in MySQL
- How to Calculate Percentage in MySQL
- Where vs Having in MySQL
- Nested Select Statements in MySQL for Enhanced Query
- Tiny Integer in MySQL