DEV Community

Abhay Singh Kathayat
Abhay Singh Kathayat

Posted on

Essential MySQL Operators and Their Applications

Here’s a guide to essential MySQL operators and their applications, covering a wide range of operations for effective database management:


1. Arithmetic Operators

Used to perform mathematical operations.

Operator Description Example
+ Addition SELECT 5 + 3;
- Subtraction SELECT 10 - 7;
* Multiplication SELECT 4 * 2;
/ Division SELECT 8 / 2;
% Modulus (remainder of division) SELECT 10 % 3;

Applications:

  • Performing calculations in SELECT queries.
  • Generating derived columns.

2. Comparison Operators

Used to compare two values.

Operator Description Example
= Equal to SELECT * FROM users WHERE age = 30;
!= or <> Not equal to SELECT * FROM users WHERE age != 30;
< Less than SELECT * FROM users WHERE age < 30;
> Greater than SELECT * FROM users WHERE age > 30;
<= Less than or equal to SELECT * FROM users WHERE age <= 30;
>= Greater than or equal to SELECT * FROM users WHERE age >= 30;

Applications:

  • Filtering data in SELECT queries.
  • Validating input data in WHERE conditions.

3. Logical Operators

Used to combine multiple conditions.

Operator Description Example
AND Returns true if all conditions are true SELECT * FROM users WHERE age > 18 AND country = 'USA';
OR Returns true if any condition is true SELECT * FROM users WHERE age < 18 OR age > 65;
NOT Reverses the logical state of the condition SELECT * FROM users WHERE NOT (age = 30);

Applications:

  • Building complex queries.
  • Filtering data based on multiple criteria.

4. Bitwise Operators

Operate on binary representations of numbers.

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

Applications:

  • Working with flags or binary data.
  • Performing low-level data manipulations.

5. String Operators

Used for string manipulations.

Operator Description Example
+ Concatenation (not standard SQL) SELECT 'Hello' + ' World'; (depends on settings)
` `
LIKE Pattern matching SELECT * FROM users WHERE name LIKE 'J%';
NOT LIKE Negated pattern matching SELECT * FROM users WHERE name NOT LIKE 'J%';

Applications:

  • Searching for patterns in strings.
  • Manipulating or combining string data.

6. Assignment Operator

Used to assign a value to a variable.

Operator Description Example
:= Assign a value to a variable SET @x := 10;

Applications:

  • Storing intermediate results in variables.
  • Enhancing readability of complex queries.

7. Aggregate Functions with Operators

Used to calculate values from column data.

Function Description Example
COUNT() Returns number of rows SELECT COUNT(*) FROM users;
SUM() Returns sum of values SELECT SUM(salary) FROM employees;
AVG() Returns average value SELECT AVG(salary) FROM employees;
MIN() Returns minimum value SELECT MIN(salary) FROM employees;
MAX() Returns maximum value SELECT MAX(salary) FROM employees;

Applications:

  • Performing statistical analysis.
  • Summarizing data.

8. NULL-Related Operators

Used to handle NULL values.

Operator Description Example
IS NULL Checks if value is NULL SELECT * FROM users WHERE age IS NULL;
IS NOT NULL Checks if value is not NULL SELECT * FROM users WHERE age IS NOT NULL;

Applications:

  • Identifying missing or incomplete data.

9. IN and BETWEEN Operators

Simplify range and set checks.

Operator Description Example
IN Checks if value exists in a set SELECT * FROM users WHERE age IN (25, 30, 35);
NOT IN Checks if value does not exist in a set SELECT * FROM users WHERE age NOT IN (25, 30, 35);
BETWEEN Checks if value is in a range (inclusive) SELECT * FROM users WHERE age BETWEEN 20 AND 30;
NOT BETWEEN Checks if value is outside a range SELECT * FROM users WHERE age NOT BETWEEN 20 AND 30;

Applications:

  • Filtering data based on ranges or specific sets.

10. Other Useful Operators

Operator Description Example
EXISTS Returns true if subquery returns rows SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE users.id = orders.user_id);
CASE Conditional logic within queries SELECT CASE WHEN age > 18 THEN 'Adult' ELSE 'Minor' END AS category FROM users;

Applications:

  • Advanced filtering.
  • Conditional data formatting.

By mastering these operators, you can effectively manipulate and analyze data in MySQL, improving both the functionality and efficiency of your queries.

Heroku

Simplify your DevOps and maximize your time.

Since 2007, Heroku has been the go-to platform for developers as it monitors uptime, performance, and infrastructure concerns, allowing you to focus on writing code.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more