MySQL Arithmetic Operators

Arithmetic operators are the operators to perform calculations. The data can be numeric data stored in database tables. MySQL Arithmetic Operators can also be used between the table columns, constant numeric values or combination of these. The following examples will need these operators.

  • To find total salary of an employee you will add the columns storing components of employee salary in the table.
  • To increase salary of all employees by 20%, you will find total salary and add 20% of it to calculated salary.

Let’s discuss MySQL Arithmetic Operators in detail.

Plus (+)

Plus operator is use to add numeric values or fields of a table. 

SELECT 4+5 FROM DUAL;

Minus(-)

This operator is use to subtract numeric values or fields of a table. 

SELECT 4-5 FROM DUAL;

Multiply(*)

Multiply operator is use to Multiple numeric values or fields of a table. 

SELECT 4-5 FROM DUAL;

Divide(/)

Divide operator is use to divide two numeric values or fields of a table. 

SELECT 67/5 FROM DUAL;

Modulo(% or MOD)

Modulo operator is use to find the remainder by dividing one numeric value with another or fields of a table. 

SELECT 67%5 FROM DUAL;

Examples of MySQL Arithmetic Operators

Here we have created emp  table with three columns having numeric values. The following examples will demonstrate the use of MySQL Arithmetic Operators on these columns

SELECT *, da+hra+med AS 'Total Salary' FROM emp;

This SELECT query displays all the columns of the table along with total of columns da, hra and med using + (plus) operator

MySQL Arithmetic Operators - Plus
SELECT *, da*10/100 AS 'Deductions', da+hra+med AS 'Gross Salary', da+hra+med-da*10/100 AS 'Net Salary' FROM emp;

This query uses + (plus), – (minus) and *(multiply) operators to calculate deductions as 10% of da. The net salary is difference between sum of da, hra and med and deductions already calculated. 

operators
SELECT *, da+hra+med AS 'Gross Salary', da+hra+med+(da+hra+med)*25/100 AS 'New Salary' FROM emp;

This query uses + (plus), – (minus), /(divide) and *(multiply) operators to calculate deductions. It is taken as 10% of da. The net salary is the difference of sum of da, hra and med and deductions already calculated.  You will also observe that all the calculated values using table columns are given aliases to convey the correct meaning of calculated values.

All Operators