Databases & SQL · Class 12 · SQL Functions

MySQLfunctions⏱️ 8 min read

Math FunctionsOptional for Computer Science

A single-row function works on one value and gives back one value — unlike an aggregate, which swallowed a whole column. Three of them are arithmetic, and one has a trick in it.

Who this page is for

Math, text and date functions are in the Informatics Practices (065) Class 12 syllabus, Unit 2. They are not in the Computer Science (083) syllabus, so CS students can read this chapter for interest and will not be examined on it. Everything else in this track is common to both.

1You can try a function on its own

A select does not need a table when there is nothing to look up. That makes these functions easy to experiment with:

MySQL command line client

mysql> select power(2,3);

+------------+

| power(2,3) |

+------------+

| 8 |

+------------+

1 row in set (0.00 sec)

Two to the power three. The heading is the expression, as always with a calculated column.

2POWER(m, n) — m raised to n

MySQL command line client

mysql> select power(3,2);

+------------+

| power(3,2) |

+------------+

| 9 |

+------------+

1 row in set (0.00 sec)

mysql> select power(3,0);

+------------+

| power(3,0) |

+------------+

| 1 |

+------------+

1 row in set (0.00 sec)

The order matters

power(2,3) is 8 and power(3,2) is 9. The first number is the base, the second is the exponent — base first, exactly as you would read “2 to the power 3”. Swapping them is an easy mark to lose.

And anything to the power 0 is 1, which MySQL agrees with. The function is also spelled pow(); both work.

3ROUND(value, d) — round to d decimal places

MySQL command line client

mysql> select round(3.16745, 3);

+-------------------+

| round(3.16745, 3) |

+-------------------+

| 3.167 |

+-------------------+

1 row in set (0.00 sec)

mysql> select round(3.16745, 2);

+-------------------+

| round(3.16745, 2) |

+-------------------+

| 3.17 |

+-------------------+

1 row in set (0.00 sec)

Three places keeps 3.167; two places rounds the 4-then-5 up to 3.17. Ordinary rounding, doing what you expect.

With 0 as the second argument you get a whole number:

MySQL command line client

mysql> select round(3.1898745, 0);

+---------------------+

| round(3.1898745, 0) |

+---------------------+

| 3 |

+---------------------+

1 row in set (0.00 sec)

4The trick: a negative second argument

This is the part nobody guesses. A negative number of places rounds to the left of the decimal point — to the nearest ten, the nearest hundred, and so on:

MySQL command line client

mysql> select round(373.8898745, -1);

+------------------------+

| round(373.8898745, -1) |

+------------------------+

| 370 |

+------------------------+

1 row in set (0.00 sec)

mysql> select round(373.8898745, -2);

+------------------------+

| round(373.8898745, -2) |

+------------------------+

| 400 |

+------------------------+

1 row in set (0.00 sec)

round(373.8898745, 2)

373.89

two places after the point

round(373.8898745, -1)

370

nearest ten (373 → 370)

round(373.8898745, -2)

400

nearest hundred (373 → 400)

Read the second argument as a position: positive counts places to the right of the decimal point, negative counts to the left. Zero is the point itself.

5MOD(m, n) — the remainder

MySQL command line client

mysql> select mod(21,6);

+-----------+

| mod(21,6) |

+-----------+

| 3 |

+-----------+

1 row in set (0.00 sec)

21 ÷ 6 is 3 remainder 3, and mod() returns the remainder, not the quotient. It is the same job as % in Python.

The case worth checking is when the first number is smaller than the second:

MySQL command line client

mysql> select mod(10,13);

+------------+

| mod(10,13) |

+------------+

| 10 |

+------------+

1 row in set (0.00 sec)

13 does not go into 10 at all, so nothing is taken away and the whole 10 is left over. Whenever m < n, mod(m, n) is just m.

What mod is used for

Testing divisibility. mod(n, 2) = 0 means n is even; mod(id, 5) = 0 picks every fifth record. A where clause can use a function just as easily as a column.