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:
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:
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:
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:
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 |
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 (
=,:=)
