w3resource logo


gallery w3resource

MySQL Mathematical functions, slides presentation

Secondary Nav

 

This presentation describes MySQL Mathematical functions with examples.

Transcript

MySQL Mathematical functions

MySQL ABS() function

MySQL ABS() returns the absolute value of a number.

Syntax : ABS(N)

Example : SELECT ABS(5);

Output : 5

Example : SELECT ABS(-5);

Output : 5

MySQL ACOS() function

MySQL ACOS() returns the arc cosine of a number. The function returns NULL when the value of the number is not between the range -1 to 1.

Syntax : ACOS(N)

Example : SELECT ACOS(1);

Output : 0

Example : SELECT ACOS(1.001);

Output : NULL

MySQL ASIN() function

MySQL ASIN() returns the arc sine of a number. The function returns NULL when the value of the number is not between the ranges -1 to 1.

Syntax : ASIN(N)

Example : SELECT ASIN(4);

Output : NULL

Here in the above example the value in the argument exceeding the range.

Example : SELECT ASIN(.4);

Output : 0.411516846067488

MySQL ATAN2() function

MySQL ATAN2() returns the arc tangent of two numbers for a point on a Cartesian plane. The reverse function is TAN().

Syntax : ATAN2(num1, num2)

Example : SELECT ATAN2(-3,2);

Output : -0.982793723247329

MySQL ATAN() function

MySQL ATAN() returns the arc tangent of a number.

Syntax : ATAN(N)

Example : SELECT ATAN(4);

Output : 1.32581766366803

Example : SELECT ATAN( - 4 );

Output : -1.32581766366803

MySQL CEIL() function

MySQL CEIL() returns the smallest integer value not less than the number specified as an argument.

Syntax : CEIL(N)

Example : SELECT CEIL(2.2536);

Output : 3

Example : SELECT CEIL(-2.2536);

Output : -2

MySQL CEILING() function

MySQL CEILING() returns the smallest integer value not less than the number specified as argument.

Syntax : CEILING(N)

Example : SELECT CEILING(2.2536);

Output : 3

MySQL CONV() function

★ MySQL CONV() converts a number from one numeric base number system to another numeric base number system.
★ When the argument defined is a NULL, the return value will be NULL.
★ The minimum base is 2 and maximum base is 36. If the base to be converted to is a negative number, the number is regarded as a signed number. Otherwise, it is treated as unsigned.

Syntax : CONV(num , from_base , to_base)

Example : SELECT CONV(15,10,2);

Output : 1111

The above statement the decimal number 15 converted to binary number.

Example :
SELECT CONV('b',16,10) 'Hex.to Dec.', CONV('b',16,2) AS 'Hex.to Binary';

Output :

Hex.to Dec. Hex. to Binary
--------------------------
        11           1011

The above statement shows the hexadecimal ‘b’ converted n decimal number and binary number.

Example : SELECT CONV(19,10,-16);

Output :

CONV(19,10,-16)
---------------
            13

The above statement converts the decimal 19 to a hexadecimal. Here the base is -16, so it is treated as a unsigned number.

MySQL COS() function

MySQL COS() returns the cosine of a number where the number is given in radians.

Syntax : COS(N)

Example : SELECT COS(1);

Output : 0.54030230586814

MySQL COT() function

MySQL COT() returns the cotangent of a number.

Syntax : COT(N)

Example : SELECT COT(7);

Output : 1.14751542240514

MySQL CRC32() function

★ MySQL CRC32() returns the cyclic redundancy check value of a given string as a 32-bit unsigned value.
★ When the argument is NULL the result is NULL.

Syntax : CRC32(expression)

Example : SELECT CRC32( ‘ String ’ );

Output : 2663297705

MySQL DEGREES() function

MySQL DEGREES() converts the value of degree in radians.

Syntax : DEGREES(N)

Example : SELECT DEGREES( 1.345 );

Output : 77.0628234450957

Example : SELECT DEGREES( PI() );

Output : 180

MySQL DIV() Operator

MySQL div operator is used for integer division.

Syntax : expression DIV num

Example : SELECT 12 DIV 3;

Output : 4

MySQL DIVISION() Operator

MySQL division operator is used for integer division.

Syntax : num / num

Example : SELECT 12 / 3;

Output : 4.0000

MySQL EXP() function

MySQL EXP() returns the value of the base of natural logarithm number e, raised to the power of a number specified as argument.

Syntax : EXP ( N )

Example : SELECT EXP (1) ;

Output : 2.71828182845905

Example : SELECT EXP (-1) ;

Output : 0.367879441171442

MySQL FLOOR() function

MySQL FLOOR() returns the largest integer value not greater than the specified number.

Syntax : FLOOR ( N )

Example : SELECT FLOOR (1.72) ;

Output : 1

The above statement shows the return value 1 is smallest the specified number 1.72.

Example : SELECT FLOOR (-2.72) ;

Output : -3

The above statement shows the return value -3 is smallest the specified number -2.72.

MySQL LN() function

★ MySQL LN() returns the natural logarithm of a number that is the base e logarithm of the number.
★ The return value will be NULL when the value of the number is less than or equal to 0.

Syntax : LN ( N )

Example : SELECT LN (3) ;

Output : 1.09861228866811

Example : SELECT LN ( - 3 ) ;

Output : NULL

MySQL LOG() function

★ MySQL LOG() returns the natural logarithm of a number that is the base e logarithm of the number.

Syntax : LOG (N), LOG(B , N)

★ The return value will be NULL when N is less than or equal to 0.
★ When LOG() execute with two parameters it returns the logarithm of the N to the base B.
★ The return value will be NULL when the value of the N less than or equal to 0 or the value of B is less than or equal to 1.

Example : SELECT LOG(3) ;

Output : 1.09861228866811

Example : SELECT LOG(10,1000)

Output : 3

MySQL LOG2() function

MySQL LOG2() returns the natural logarithm of a number to the base 2

Syntax : LOG2 ( N )

Example : SELECT LOG2 (256) ;

Output : 8

MySQL LOG10() function

MySQL LOG10() returns the natural logarithm of a number to the base 10.

Syntax : LOG10 ( N )

Example : SELECT LOG10 (1000) ;

Output : 3

MySQL MOD() function

★ MySQL MOD() returns the remainder of a number divided by another number. This function also works on fractional values and returns the exact remainder.
★ The function returns NULL when the value of divisor is 0.

Syntax : MOD(N,M), N % M, N MOD M;

Example : SELECT MOD (17,5) ;

Output : 2

Example : SELECT MOD (17 MOD 5) ;

Output : 2

MySQL OCT() function

MySQL OCT() returns octal value of a decimal number.

Syntax : OCT(Num)

Example : SELECT OCT ( ‘55’ ) ;

Output : 67

MySQL PI() function

MySQL PI() returns the value of π(pi)

Syntax : PI( )

Example : SELECT PI ( ) ;

Output : 3.141593

MySQL POW() function

MySQL POW() returns the value of a number raised to the power of another number.

Syntax : POW(M,N )

Example : SELECT POW ( 3 , 2) ;

Output : 9

Example : SELECT POW (4 , - 2) ;

Output : 0.0625

MySQL POWER() function

MySQL POWER() returns the value of a number raised to the power of another number.

Syntax : POWER(M,N )

Example : SELECT POWER ( 3 , 2) ;

Output : 9

Example : SELECT POW (4 , - 2) ;

Output : 0.0625

MySQL RADIANS() function

MySQL RADIANS() converts the value of a number from degrees to radians. (pi radians equals to 180 degrees).

Syntax : RADIANS( M )

Example : SELECT RADIANS ( 270 ) ;

Output : 4.71238898038469

MySQL RAND() function

MySQL RAND() returns a random floating-point value between the range 0 to 1. When a fixed integer value is passed as an argument, the value is treated as a seed value and as a result, a repeatable sequence of column values will be returned.

Syntax : RAND( ) , RAND( M )

Example : SELECT RAND( ) ;

Output : 0.228926179212449

N.B. The value of RAND() function may be changed time to time

MySQL ROUND() function

MySQL ROUND() rounds a number up to a specific decimal places.

Syntax : ROUND(N,[D])

Example : SELECT ROUND(4.43) ;

Output : 4

Example : SELECT ROUND(- 4.53) ;

Output : - 5

Example : SELECT ROUND(- 4.535,2) ;

Output : - 4.54

MySQL SING() function

MySQL SIGN() returns the sign of a specific number.
Return 1 when the value of the argument is positive, returns -1 when the value of the argument is negative and return 0 when the value of the argument is 0.

Syntax : SIGN(X)

Example : SELECT SIGN(-145), SIGN(0), SIGN(145);

Output :

SIGN(-145)  SIGN(0) SIGN(145)
-----------------------------
        -1       0       1

MySQL SIN() function

MySQL SIN() returns the sine of the argument. The argument is given in radians.

Syntax : SIN(X)

Example : SELECT SIN(1);

Output : 0.841470984807897

MySQL SQRT() function

MySQL SQRT() returns the square root of a non-negative number of the argument.

Syntax : SQRT(X)

Example : SELECT SQRT(25);

Output : 5

Example : SELECT SQRT(-25);

Output : NULL

MySQL TAN() function

MySQL TAN() returns the tangent of the argument. The argument is given in radians.

Syntax : TAN(X)

Example : SELECT TAN(2.465);

Output : -0.803041315427368

MySQL TRUNCATE() function

MySQL TRUNCATE() returns a number after truncated to a certain decimal places.

Syntax : TRUNCATE(N , D)

Example : SELECT TRUNCATE(2.465,1);

Output : 2.4

Example : SELECT TRUNCATE(142.465,-2);

Output : 100

MySQL FORMAT() function

★ MySQL FORMAT() returns the number N to a format like ‘#,###,###.##’ rounded to a number of decimal places and returns the result as a string.
★ If there is no decimal point as a parameter, then default decimal place is defined as 0.

Syntax : FORMAT(N , D)

Example : SELECT FORMAT(12324.2573,3);

Output : 12,324.257



Join our Question Answer community to learn and share your programming knowledge.

Solve these problems:

Java: How to convert a string to an integer in Java?

C#: Loops in c#

SQL: JOIN using more than 5 tables