MySQL Functions with query

MySQL Functions with Query – Complete Practical Guide

In MySQL Functions with query are used inside SQL queries to manipulate data, calculate values, format output, and apply logic.

Below is a function-wise breakdown with real queries, exactly how they appear in exams, interviews, and backend projects.

 1. String Functions (With Queries)

LENGTH()

 UPPER() / LOWER()

 CONCAT()

 SUBSTRING()

 TRIM()

 REPLACE()

 2. Numeric (Math) Functions (With Queries)

 ABS()

 ROUND()

 CEIL() / FLOOR()

 POWER()

 MOD()

 3. Date & Time Functions (With Queries)

 NOW()

 CURDATE() / CURTIME()

 YEAR(), MONTH(), DAY()

 DATEDIFF()

 4. Aggregate Functions (With Queries)

Assume table: students

 COUNT()

 SUM()

 AVG()

 MIN() / MAX()

 Aggregate with GROUP BY

 5. Control Flow Functions (With Queries)

 IF()

 CASE (Very Important)

 6. NULL Handling Functions (With Queries)

 IFNULL()

 COALESCE()

 7. Conversion Functions (With Queries)

 CAST()

 CONVERT()

 FORMAT()

8. Functions in WHERE Clause

 Common Interview Queries

Find students scoring above average

Replace NULL salary with 0

Count employees per department

Common Mistakes

•  Using aggregate functions without GROUP BY
•  Ignoring NULL values
•  Using functions on indexed columns in WHERE
•  Confusing WHERE and HAVING

 Best Practices

•  Use aliases (AS)
•  Handle NULLs properly
•  Use GROUP BY correctly
•  Prefer CASE over complex IFs
•  Keep queries readable

Summary

•  MySQL functions manipulate data
•  Used in SELECT, WHERE, GROUP BY
•  String, numeric, date & aggregate are most used
•  Essential for interviews & backend work
•  Practice with real queries