How to use the decimal() function in SQL

How to use the decimal() function in SQL

In the SQL language, the decimal() function is used to define a function with fixed precision and number of decimal places. Numeric type. It is often used to process numerical data that requires precise calculations, such as monetary amounts, percentages, etc. In this article, we will introduce how to use the decimal() function and provide specific code examples.

The syntax of the decimal() function is as follows:

DECIMAL(precision, scale)

Among them, precision represents the total number of significant digits, and scale represents the number of digits after the decimal point.

The following are some examples to help understand the use of decimal() function.

1. Create a table containing decimal type:

   CREATE TABLE products (
     id INT,
     name VARCHAR(100),
     price DECIMAL(10, 2)
   );

   In the above example, we created a table named products where the price column has 10 valid digits Numbers and 2 decimal places.

2. Insert data into decimal type columns:

   INSERT INTO products (id, name, price)
   VALUES (1, '手机', 1999.99),
       (2, '电视', 2999.00),
       (3, '耳机', 99.95);

   In this example, we insert different numerical data for the price column in the products table. Note that the inserted numeric value must match the defined precision and scale, otherwise an error will be raised.

3. Query decimal type columns:

   SELECT * FROM products;

   After executing the above query statement, all records in the products table will be returned, including the values ​​of the id, name and price columns.

4. Perform precise calculations on decimal type columns:

   SELECT name, price * 0.1 AS discount_price
   FROM products;

   In the above example, the SELECT statement is used to calculate the discounted price of 10% of the original price, and The calculation result is named discount_price. This query can be used to calculate the discounted price of an item.

5. Update the value of decimal type column:

   UPDATE products
   SET price = price + 100; 

   In this example, we use the UPDATE statement to increase the value of the price column in the products table by 100. This example shows how to update decimal type columns.

6. Delete decimal type columns:

   ALTER TABLE products
   DROP COLUMN price;

   Through the above example, we can see how to use the ALTER TABLE statement to delete a column containing decimal type from the table. Please note that before deleting a column, make sure to back up important data.

   Summary:
   The decimal() function is a function in SQL used to define a numeric type with fixed precision and number of decimal places. By using this function we can store and calculate precise numerical data in the database. This article provides some specific code examples to help readers understand how to use this function. I hope this article can be helpful to readers when using the decimal() function in SQL development.

   The above is the detailed content of How to use the decimal() function in SQL. For more information, please follow other related articles on the PHP Chinese website!