[MySQL] Note(5) Number Instructions: ABS, CEIL, FLOOR, MOD, POWER, SIGN, TRUNCATE, SQRT

Last Updated on 2021-05-16 by Clay

There are many useful instructions for processing values in SQL. Today I will introduce the following instructions:

• ABS(): Calculate the absolution
• CEIL(): Unconditionally round decimals to integer
• FLOOR(): Unconditionally round off decimals to integers
• ROUND(n1, n2): Round n1 to n2 decimal place
• TRUNCATE(n1, n2): Unconditionally round down n1 to the n2 decimal place
• MOD(n1, n2): Find the remainder of n1 divided by n2
• POWER(n1, n2): Find n1 to the power of n2
• SIGN(): Determine whether the value is positive or negative
• SQRT(): Return the root of the input value

Then we will actually test these instructions below.

Preparation

First, we assume that we have a table like the following:

Our following examples all use this table to demo.

ABS

Calculate the absolution.

select n, abs(n) from num where n = -5;

Output:

CEIL

Unconditionally round decimals to integer.

select n, ceil(n) from num where SID = 4;

Output:

FLOOR

Unconditionally round off decimals to integers.

select n, floor(n) from num where SID = 4;

Output:

ROUND

ROUND(n1, n2): Round n1 to n2 decimal place.

select n, round(n, 1) from num where SID = 4;

Output:

TRUNCATE

TRUNCATE(n1, n2): Unconditionally round down n1 to the n2 decimal place.

select n, truncate(n, 1) from num where SID = 4;

Output:

MOD

MOD(n1, n2): Find the remainder of n1 divided by n2.

select n, mod(n, 3) from num where n = 10;

Output:

10 / 3 = 1.

POWER

POWER(n1, n2): Find n1 to the power of n2.

select n, power(n, 2) from num where n = 10;

Output:

SIGN

Determine whether the value is positive or negative.

select n, sign(n) from num where n = -5;

Output:

SQRT

Return the root of the input value.

select n, sqrt(n) from num where n = 10;

Output: