Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
MySQL provides a very complete set of numeric and arithmetic functions and operators. Here's are a few of the most common. For this lesson we'll be using the world database. We're just going to start with some arithmetic operators. So, if I say SELECT 7 plus 3. Then I'll get a result of 10. And if I SELECT 7 minus 3, I get 4. So these are just as you would expect, here's 7 times 3, and 7 divided by 3.
You notice that seven divided by three gets you a floating point number even though both of the operands are integers. If you want integer division, you use the DIV keyword as an operator and you get. A 2 and there is of course a reminder and you can use MOD for the reminder which stands for modulus or you can use a modulus symbol the percent sign, for the reminder and you get exactly the same result. There is no power operator so the.
Carrot does not do what you expect. So that is not 7 cubed. Rather, there's a function for power. And so you type POWER and put the operands in parenthesis, 7 to the 3rd power, like that. And you get 343, which if we just say 7 times 7 times 7, you'll see is 7 cubed. There's also a shortcut for power for compatibility. You can just say POW, because that is the keyword in some other database management systems.
There's an absolute value function ABS and so, absolute value of 7 is, of course. 7, and the absolute value of minus 7 is also 7. Or you can get the sign by typing SIGN and it'll give you a minus 1 for a negative sign, or a positive 1 for a positive sign. There's a function for converting from base to base, and this is actually pretty interesting.
It's called CONV for convert. And if I take the number, say 57, and convert it from base 10 to base 16, like that, then I get a 39. And if I take that 39. And, of course, the literal 39 here is defacto in decimal, but if I say that it's in base 16 like this, I can convert it from base 16 back to 10, and it will give me that 57.
And if I take the 57, again from base 10, and convert it to base 32. I get 1P, so the limit here is base 36, and that's because that is 10 plus the number of letters in the letters part of the ASCII character set. And so the limit of bases for the CONV function from base 2 to base 36. And so it's using letters for those numbers that are greater than.
The digits in the character set. So here, because 57 in base 32 is a 1, and then a large number, it's using the letter P for that large number. There's also a full set of functions for rounding. So, if I SELECT the number PI. You notice that we get 3.141593. And if we SELECT ROUND of PI, it will round it to the nearest whole number so we'll just get a 3.
And say we wanted the first 2 significant digits to the right of the decimal point, we get 3.14. Now you notice it's rounding. So even though the next digit is a 1, the one after it is a 5. So that 1 should round up to a 2 if I select 3 digits. So I get 3.142 like that. I can instead of rounding, I can say TRUNCATE. And so now, it'll simply truncate it to 3 digits rather than rounding up because the next digits are 5.
So we get 3.141, with truncate. There's also a ceiling and floor function, so the CEIL of PI is 4 so that just always rounds it up. And the FLOOR always rounds it down. So, finally, I want to show you about random numbers. MySQL comes with a fairly complete random number generator. Of course, for really great random numbers, you're going to need more than just a database engine but for most purposes.
It is very complete. So if I SELECT say, Name and a RANDOM number FROM Country. And I'm going to limit our query to 5 rows. You see we get a set of random numbers. And each time I press this, we get a different set of random numbers like that. I can select a literal number as a seed, and then I'll get a set of random numbers. But it's seeded with this 7 for the first random number, and so every time I press go with a fixed seed, you notice I get exactly the same set of random numbers.
So normally you'll use this without a seed unless you're generating your own random seed somehow. So MySQL provides a very complete set of numeric and arithmetic functions and operators, and you'll want to see the MySQL documentation for a complete reference.
Get unlimited access to all courses for just $25/month.Become a member