• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Operators

← Previous Next →

In MySQL, operators are symbols or keywords that perform operations on one or more values. They are essential for filtering, calculating, comparing, and manipulating data in queries. Operators allow you to build complex conditions, perform arithmetic calculations, and control logic in SQL statements.

Understanding MySQL operators is fundamental for writing accurate and efficient queries.

Types of MySQL Operators

MySQL operators can be broadly categorized into the following types:

  1. Arithmetic Operators

  2. Comparison Operators

  3. Logical Operators

  4. Bitwise Operators

  5. Assignment Operators

  6. Special Operators (like LIKE, IN, BETWEEN, IS NULL)

1. Arithmetic Operators

Arithmetic operators are used to perform mathematical calculations on numeric values.

Operator Description Example
+ Addition SELECT 10 + 5;
- Subtraction SELECT 10 - 5;
* Multiplication SELECT 10 * 5;
/ Division SELECT 10 / 5;
% Modulus SELECT 10 % 3;

Example in a Table

SELECT name, score, score + 10 AS NewScore
FROM students;

  • Adds 10 points to each student’s score.

  • Arithmetic operators can be used in SELECT, UPDATE, and WHERE clauses.

2. Comparison Operators

Comparison operators are used to compare two values. They return TRUE or FALSE.

Operator Description Example
= Equal score = 90
!= or <> Not equal score != 50
> Greater than age > 18
< Less than age < 25
>= Greater than or equal score >= 60
<= Less than or equal score <= 100

Example

SELECT name
FROM students
WHERE score >= 90;

  • Returns students who scored 90 or more.

  • Comparison operators are often used with WHERE, HAVING, and JOIN conditions.

3. Logical Operators

Logical operators combine multiple conditions to return TRUE or FALSE.

Operator Description Example
AND Both conditions must be true age > 18 AND score >= 50
OR Either condition can be true age < 18 OR score < 50
NOT Negates a condition NOT(score >= 50)

Example

SELECT name
FROM students
WHERE age > 18 AND score >= 50;

  • Returns students who are older than 18 and have score 50 or higher.

4. Bitwise Operators

Bitwise operators perform operations at the binary level. They are less commonly used but useful in flag fields or settings stored as integers.

Operator Description Example
& Bitwise AND 5 & 3
` ` Bitwise OR
^ Bitwise XOR 5 ^ 3
~ Bitwise NOT ~5
<< Left shift 5 << 1
>> Right shift 5 >> 1

Example

SELECT 5 & 3 AS BitwiseAND;

  • Returns 1, which is the result of bitwise AND.

5. Assignment Operators

Assignment operators are used to assign values to variables or columns.

Operator Description Example
= Assign value SET @score = 90;
:= Assign value SET @score := 90;

Example

SET @total = 100;

  • Assigns the value 100 to the variable @total.

6. Special Operators

LIKE

  • Used for pattern matching in strings.

  • Wildcards: % for multiple characters, _ for a single character.

SELECT name
FROM students
WHERE name LIKE 'A%';

  • Returns students whose name starts with 'A'.

IN

  • Checks if a value exists in a list of values.

SELECT name
FROM students
WHERE id IN (1, 2, 3);

BETWEEN

  • Checks if a value lies within a range.

SELECT name
FROM students
WHERE score BETWEEN 50 AND 90;

IS NULL / IS NOT NULL

  • Checks for NULL values.

SELECT name
FROM students
WHERE age IS NULL;

Operator Precedence

MySQL evaluates expressions based on operator precedence:

  1. Arithmetic (* / % + -)

  2. Comparison (=, <>, !=, >, <, >=, <=)

  3. Logical (NOT, AND, OR)

Example

SELECT * 
FROM students
WHERE NOT age > 18 AND score >= 50;

  • MySQL evaluates NOT age > 18 first, then AND score >= 50.

Practical Examples

  1. Calculate total marks with bonus:

SELECT name, score + 5 AS TotalScore
FROM students;

  1. Filter students by multiple conditions:

SELECT name
FROM students
WHERE age > 18 AND score >= 60;

  1. Find students whose names start with A or B:

SELECT name
FROM students
WHERE name LIKE 'A%' OR name LIKE 'B%';

  1. Check enrollment in a list of courses:

SELECT name
FROM students
WHERE course_id IN (101, 102, 103);

  1. Find students with scores within a range:

SELECT name
FROM students
WHERE score BETWEEN 50 AND 90;

Summary of the Tutorial

  • MySQL operators perform arithmetic, comparison, logical, and bitwise operations.

  • Arithmetic operators handle mathematical calculations.

  • Comparison operators compare values and return TRUE/FALSE.

  • Logical operators combine multiple conditions.

  • Bitwise operators manipulate binary data.

  • Assignment operators store values in variables.

  • Special operators like LIKE, IN, BETWEEN, and IS NULL allow flexible filtering.

  • Understanding operator precedence ensures accurate query results.

Mastering MySQL operators is essential for writing precise, efficient, and flexible queries in everyday database operations.

Practice Questions

  1. Select all students whose score is greater than 80 using a comparison operator.

  2. Select students whose age is between 18 and 25 using the BETWEEN operator.

  3. Select students whose name starts with ‘A’ using the LIKE operator.

  4. Select students whose id is in the list 1, 2, 3, 4 using the IN operator.

  5. Select students whose score is not equal to 50 using the != operator.

  6. Select students who are older than 18 AND have a score above 60 using a logical operator.

  7. Select students who are younger than 18 OR have a score below 50 using a logical operator.

  8. Calculate a new column TotalScore by adding 10 to each student’s score using an arithmetic operator.

  9. Select students where age is NULL using IS NULL.

  10. Use a bitwise AND operator to find students whose flags column matches a specific binary pattern.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top