MySQL supports a wide variety of operators for performing calculations, comparisons, logical operations, and more. Below is an overview of the different types of operators:
MySQL Arithmetic Operators
These operators are used to perform basic arithmetic operations:
| Operator | Description | Example |
|---|---|---|
+ |
Add | SELECT 5 + 2; |
- |
Subtract | SELECT 5 - 2; |
* |
Multiply | SELECT 5 * 2; |
/ |
Divide | SELECT 5 / 2; |
% |
Modulo (remainder) | SELECT 5 % 2; |
MySQL Bitwise Operators
Bitwise operators work on bits and perform bit-by-bit operations:
| Operator | Description | Example |
|---|---|---|
& |
Bitwise AND | SELECT 5 & 3; (Result is 1) |
| ` | ` | Bitwise OR |
^ |
Bitwise exclusive OR | SELECT 5 ^ 3; (Result is 6) |
MySQL Comparison Operators
These operators are used to compare values:
| Operator | Description | Example |
|---|---|---|
= |
Equal to | SELECT * FROM table WHERE age = 30; |
> |
Greater than | SELECT * FROM table WHERE age > 30; |
< |
Less than | SELECT * FROM table WHERE age < 30; |
>= |
Greater than or equal to | SELECT * FROM table WHERE age >= 30; |
<= |
Less than or equal to | SELECT * FROM table WHERE age <= 30; |
<> |
Not equal to | SELECT * FROM table WHERE age <> 30; |
MySQL Compound Operators
Compound operators perform an operation and assign the result to a variable in one step:
| Operator | Description | Example |
|---|---|---|
+= |
Add equals | x += 10; |
-= |
Subtract equals | x -= 10; |
*= |
Multiply equals | x *= 10; |
/= |
Divide equals | x /= 10; |
%= |
Modulo equals | x %= 10; |
&= |
Bitwise AND equals | x &= 10; |
^= |
Bitwise exclusive OR equals | x ^= 10; |
| ` | =` | Bitwise OR equals |
MySQL Logical Operators
Logical operators are used to combine multiple conditions:
| Operator | Description | Example |
|---|---|---|
ALL |
TRUE if all of the subquery values meet the condition | SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category='A'); |
AND |
TRUE if all the conditions separated by AND are TRUE |
SELECT * FROM table WHERE age > 30 AND city='New York'; |
ANY |
TRUE if any of the subquery values meet the condition | SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE category='A'); |
BETWEEN |
TRUE if the operand is within the range of comparisons | SELECT * FROM table WHERE age BETWEEN 20 AND 30; |
EXISTS |
TRUE if the subquery returns one or more records | SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id); |
IN |
TRUE if the operand is equal to one of a list of expressions | SELECT * FROM table WHERE age IN (30, 40, 50); |
LIKE |
TRUE if the operand matches a pattern | SELECT * FROM customers WHERE name LIKE 'A%'; |
NOT |
Displays a record if the condition(s) is NOT TRUE | SELECT * FROM table WHERE NOT city='Paris'; |
OR |
TRUE if any of the conditions separated by OR are TRUE |
SELECT * FROM table WHERE age > 30 OR city='New York'; |
SOME |
TRUE if any of the subquery values meet the condition | SELECT * FROM products WHERE price > SOME (SELECT price FROM products WHERE category='A'); |
Summary
- Arithmetic Operators: Perform basic math operations.
- Bitwise Operators: Perform bitwise operations on integer values.
- Comparison Operators: Compare values (e.g., equal to, greater than).
- Compound Operators: Combine an operation and assignment in one step.
- Logical Operators: Combine multiple conditions for filtering results (e.g., AND, OR, NOT).