In-depth analysis of commonly used numerical and conditional functions of MYSQL

1. Numerical functions

1. ROUND(num, decimals) Round to the specified decimal places.

SELECT ROUND(3.1415, 2);  -- Output 3.14

2. ABS(num) takes the absolute value

SELECT ABS(-10);  -- Output 10

3. CEIL(num) / FLOOR(num) round up/down

SELECT CEIL(3.2), FLOOR(3.7);  -- Output 4 and 3

4. MOD(num1, num2) modulus (residual) or x % y

SELECT MOD(10, 3);  -- Output 1

5. RAND() generates a random number between 0 and 1.

SELECT RAND();  -- Output as 0.1234

6. Addition, subtraction, multiplication and division

2. Conditional function

1. CASE WHEN Multi-condition branch judgment

SELECT 
  name,
  CASE 
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

Among them >=90 must be the first, otherwise an error will be reported

2. IF(condition, true_value, false_value) simple condition judgment.

SELECT IF(score >= 60, 'Pass', 'Fail') FROM exams;

3. IFNULL(expr1, expr2) If expr1 is NULL, return expr2.

SELECT IFNULL(bonus, 0) FROM employees;  -- Will NULL Convert to 0

This is the end of this article about the detailed explanation of commonly used numerical functions and conditional functions of MYSQL. For more related contents of mysql numerical functions and conditional functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!