MySQL Mathematical functions, slides presentation

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