MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Funtions and Operators / Question No: 233

What are mathematical functions in MySQL?

Answer No: 233

Numeric functions perform several types of operations, such as rounding, truncation, trigonometric calculations, or generating random numbers.

The ROUND() function performs rounding of its argument. The rounding method applied to the fractional part of a number depends on whether the number is an exact or approximate value:

  • For positive exact values, ROUND() rounds up to the next integer if the fractional part is .5 or greater, and down to the next integer otherwise. For negative exact values, ROUND() rounds down to the next integer if the fractional part is .5 or greater, and up to the next integer otherwise. Another way to state this is that a fraction of .5 or greater rounds away from zero and a fraction less than .5 rounds toward zero:
    mysql> SELECT ROUND(28.5), ROUND(-28.5);
    +-------------+--------------+
    | ROUND(28.5) | ROUND(-28.5) |
    +-------------+--------------+
    | 29          | -29          |
    +-------------+--------------+
    
  • For approximate values, ROUND() uses the rounding method provided in the C library used by the MySQL server. This can vary from system to system, but typically rounds to the nearest even integer:
    mysql> SELECT ROUND(2.85E1), ROUND(-2.85E1);
    +---------------+----------------+
    | ROUND(2.85E1) | ROUND(-2.85E1) |
    +---------------+----------------+
    |            28 |            -28 |
    +---------------+----------------+
    

FLOOR() and CEILING() Functions

FLOOR() returns the largest integer not greater than its argument, and CEILING() returns the smallest integer not less than its argument:

mysql> SELECT FLOOR(-14.7), FLOOR(14.7);
+--------------+-------------+
| FLOOR(-14.7) | FLOOR(14.7) |
+--------------+-------------+
|          -15 |          14 |
+--------------+-------------+

mysql> SELECT CEILING(-14.7), CEILING(14.7);
+----------------+---------------+
| CEILING(-14.7) | CEILING(14.7) |
+----------------+---------------+
|            -14 |            15 |
+----------------+---------------+

ABS() and SIGN() Functions

ABS() and SIGN() extract the absolute value and sign of numeric values:

mysql> SELECT ABS(-14.7), ABS(14.7);
+------------+-----------+
| ABS(-14.7) | ABS(14.7) |
+------------+-----------+
| 14.7       | 14.7      |
+------------+-----------+

mysql> SELECT SIGN(-14.7), SIGN(14.7), SIGN(0);
+-------------+------------+---------+
| SIGN(-14.7) | SIGN(14.7) | SIGN(0) |
+-------------+------------+---------+
|          -1 |          1 |       0 |
+-------------+------------+---------+

SIN, COS, TAN, PI, DEGREES and RADIANS Functions

A family of functions performs trigonometric calculations, including conversions between degrees and radians:

mysql> SELECT SIN(0), COS(0), TAN(0);
+--------+--------+--------+
| SIN(0) | COS(0) | TAN(0) |
+--------+--------+--------+
|      0 |      1 |      0 |
+--------+--------+--------+

mysql> SELECT PI(), DEGREES(PI()), RADIANS(180);
+----------+---------------+-----------------+
| PI()     | DEGREES(PI()) | RADIANS(180)    |
+----------+---------------+-----------------+
| 3.141593 |           180 | 3.1415926535898 |
+----------+---------------+-----------------+

RAND Function

To generate random numbers, invoke the RAND() function:

mysql> SELECT RAND(), RAND(), RAND();
+------------------+------------------+------------------+
| RAND()           | RAND()           | RAND()           |
+------------------+------------------+------------------+
| 0.55239934711941 | 0.16831658330589 | 0.18438490590489 |
+------------------+------------------+------------------+

Related MySQL FAQs to the Above FAQ

What-is-MySQL-date-format-and-its-specifiers What is MySQL date format and its specifiers?

How-to-get-comma-separated-all-values-in-a-single-row-against-each-ID How to get comma separated all values in a single row against each ID?

How-to-concat-values-in-MySQL How to concat values in MySQL?

How-to-convert-time-zone-in-MySQL How to convert time zone in MySQL?

What-are-comparison-functions-in-MySQL What are comparison functions in MySQL

How-to-set-conditions-on-query-data-with-CASE-construct How to set conditions on query data with CASE construct?

How-to-set-control-conditions-on-query-data-with-IF-funtion How to set control conditions on query data with IF funtion?

How-are-aggregate-functions-in-MySQL How are aggregate functions in MySQL?

What-are-string-functions-in-MySQL What are string functions in MySQL?

What-are-temporal-or-date-time-functions-in-MySQL What are temporal or date time functions in MySQL?

What-are-NULL-value-related-functions-in-MySQL What are NULL value related functions in MySQL?

How-does-union-work-in-MySQL How does union work in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.