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;