Detailed explanation of the use of DECIMAL data type in MySQL

Detailed explanation of usage of MySQL fixed-point number type DECIMAL

In the database, it is often necessary to process precise values, such as monetary amounts or scientific calculations. In order to ensure calculation accuracy, MySQL provides the DECIMAL type for storing precise fixed-point values. This article will introduce the usage of DECIMAL type in MySQL in detail and provide specific code examples.

1. Introduction to the DECIMAL type
The DECIMAL type is a precise numerical type used to store values ​​with a fixed number of decimal places. It has the following characteristics:

1. For values ​​with fixed decimal places, the DECIMAL type can ensure the precision and accuracy of calculations.
2. The DECIMAL type can store a larger range of values ​​and can store up to 30 digits.
3. The DECIMAL type supports negative numbers and can specify the number of decimal places.

2. Definition of DECIMAL type
When creating a table, you can use the DECIMAL type to define fields. The syntax of DECIMAL is as follows:
DECIMAL(M, D)

Among them, M represents the total number of digits in the value, and D represents the number of digits in the decimal part. For example, DECIMAL(10, 2) allows storage of up to 10 digits, including 2 decimal places.

3. DECIMAL type application

1. Create a table and insert data
   The following is an example, create a table named products and insert it into the table Some sample data:

   CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DECIMAL(10, 2)
   );
    
   INSERT INTO products (name, price) VALUES
    ('Product A', 10.99),
    ('Product B', 20.50),
    ('Product C', 100.75);

2. Querying DECIMAL type data
   Querying DECIMAL type data is the same as querying other types of data. For example, you can use the SELECT statement to query data in the products table :

   SELECT * FROM products;

3. Use DECIMAL type for calculations
   DECIMAL type supports basic mathematical calculations such as addition, subtraction, multiplication and division. For example, you can calculate the sum of all product prices:

   SELECT SUM(price) FROM products;

4. Update DECIMAL type data
   Updating DECIMAL type data is the same as updating other types of data. For example, you can use the UPDATE statement Update the price of the product with a price of 20.50:

   UPDATE products SET price = 30.00 WHERE price = 20.50;

5. Delete DECIMAL type data
   Deleting DECIMAL type data is the same as deleting other types of data. For example, you can use the DELETE statement to delete prices. Products greater than or equal to 100:

   DELETE FROM products WHERE price >= 100.00;

IV. Summary
This article introduces the usage of the DECIMAL type in MySQL and provides specific code examples. Using the DECIMAL type ensures accurate calculation and storage of fixed-point values, which is especially suitable for scenarios where monetary amounts or scientific calculations need to be processed. When creating a table, you can define the total number of digits and the number of decimal places as needed. Using the DECIMAL type for operations such as querying, calculating, and updating is basically the same as other data types.

Reference materials:

• MySQL official documentation: https://dev.mysql.com/doc/refman/8.0/en/precision-math-decimal-characteristics.html

The above is the detailed content of Detailed explanation of the use of DECIMAL data type in MySQL. For more information, please follow other related articles on the PHP Chinese website!