MySQL Operators
MySQL Operators
In MySQL, operators are symbols or keywords used to perform operations on values or expressions in queries. Operators are essential for calculations, comparisons, logical operations, and string manipulations.
Operators in MySQL can be categorized into five main types:
1️⃣ Arithmetic Operators
Used for mathematical calculations.
| Operator | Description | Example |
|---|---|---|
+ |
Addition | SELECT 5 + 3; → 8 |
- |
Subtraction | SELECT 5 - 3; → 2 |
* |
Multiplication | SELECT 5 * 3; → 15 |
/ |
Division | SELECT 10 / 2; → 5 |
% |
Modulus (remainder) | SELECT 10 % 3; → 1 |
Example:
2️⃣ Comparison Operators
Used to compare values. Returns TRUE or FALSE.
| Operator | Description | Example |
|---|---|---|
= |
Equal to | marks = 90 |
!= or <> |
Not equal to | marks != 90 |
> |
Greater than | marks > 80 |
< |
Less than | marks < 80 |
>= |
Greater than or equal to | marks >= 80 |
<= |
Less than or equal to | marks <= 80 |
BETWEEN |
Between a range | marks BETWEEN 70 AND 90 |
IN |
Value in a list | dept IN ('IT','HR') |
LIKE |
Pattern matching | name LIKE 'J%' |
IS NULL |
Check if value is NULL | marks IS NULL |
Example:
3️⃣ Logical Operators
Used to combine multiple conditions.
| Operator | Description | Example |
|---|---|---|
AND |
All conditions must be TRUE | marks > 80 AND dept='IT' |
OR |
Any condition can be TRUE | marks > 90 OR dept='HR' |
NOT |
Negates the condition | NOT dept='Finance' |
Example:
4️⃣ Bitwise Operators
Operate on binary values.
| Operator | Description | Example |
|---|---|---|
& |
Bitwise AND | 5 & 3 → 1 |
|
Bitwise OR | |
^ |
Bitwise XOR | 5 ^ 3 → 6 |
~ |
Bitwise NOT | ~5 → -6 |
<< |
Left shift | 5 << 1 → 10 |
>> |
Right shift | 5 >> 1 → 2 |
5️⃣ Assignment Operators
Used to assign values to variables.
| Operator | Description | Example |
|---|---|---|
= |
Assign value | SET @x = 5; |
:= |
Assign value (alternative) | SET @y := 10; |
🔹 Key Points
-
Arithmetic → for calculations (
+,-,*,/,%) -
Comparison → for comparing values (
=,!=,<,>,BETWEEN,IN,LIKE) -
Logical → combine conditions (
AND,OR,NOT) -
Bitwise → operate on bits (
&,|,^,~,<<,>>) -
Assignment → assign values (
=,:=)
