MySQL Functions
MySQL Functions – Complete Guide with Examples
In MySQL, functions are built-in operations that perform calculations, manipulate data, and return a single value. They are widely used in SELECT queries, WHERE conditions, INSERT/UPDATE statements, and reports.
Understanding MySQL functions is essential for data analysis, backend development, and interviews.
What Are MySQL Functions?
A MySQL function:
Takes input values (arguments)
Performs an operation
Returns one value
Example
- Input :
'MySQL' - Output :
5
Types of MySQL Functions
MySQL functions are mainly divided into:
- String Functions
- Numeric (Mathematical) Functions
- Date & Time Functions
- Aggregate Functions
- Control Flow Functions
- Conversion Functions
1. MySQL String Functions
Used to manipulate text data.
Common String Functions
| Function | Description |
|---|---|
LENGTH() | Length of string |
UPPER() | Convert to uppercase |
LOWER() | Convert to lowercase |
CONCAT() | Join strings |
SUBSTRING() | Extract part of string |
TRIM() | Remove spaces |
REPLACE() | Replace text |
Examples
2. MySQL Numeric (Math) Functions
Used for mathematical calculations.
| Function | Description |
|---|---|
ABS() | Absolute value |
ROUND() | Round number |
CEIL() | Round up |
FLOOR() | Round down |
POWER() | Exponent |
MOD() | Remainder |
Examples
3. MySQL Date & Time Functions
Used to work with dates and time values.
| Function | Description |
|---|---|
NOW() | Current date & time |
CURDATE() | Current date |
CURTIME() | Current time |
DATE() | Extract date |
YEAR() | Extract year |
MONTH() | Extract month |
DATEDIFF() | Difference between dates |
Examples
4. MySQL Aggregate Functions (Very Important)
Used to summarize data (mostly with GROUP BY).
| Function | Description |
|---|---|
COUNT() | Count rows |
SUM() | Total |
AVG() | Average |
MIN() | Minimum |
MAX() | Maximum |
Examples
- Aggregate functions return one value per group.
5. MySQL Control Flow Functions
Used to add logic inside queries.
IF()
CASE
- Similar to
if-else - Very common in interviews
6. MySQL Conversion Function
Used to convert data types.
| Function | Description |
|---|---|
CAST() | Convert type |
CONVERT() | Convert type |
FORMAT() | Format numbers |
Examples
MySQL NULL Handling Function
| Function | Description |
|---|---|
IFNULL() | Replace NULL |
COALESCE() | First non-NULL |
Examples
Using Function in WHERE Clause
- Powerful filtering
- Index usage may be affected
Common Mistakes
- Using aggregate functions without
GROUP BY Ignoring NULL values- Overusing functions in WHERE clause
- Confusing
LENGTH()andCHAR_LENGTH()
Best Practices for MySQL Function
- Use aggregate functions with
GROUP BY Handle NULL values properly- Avoid functions on indexed columns in WHERE
- Use aliases (
AS) for readability - Keep queries simple and optimized
Interview Questions: MySQL Function
1. What are MySQL function?
Built-in operations that return a single value.
2. Difference between COUNT(*) and COUNT(column)?
COUNT(*) counts all rows, COUNT(column) ignores NULL.
3. What is COALESCE()?
Returns first non-NULL value.
4. Which function gives current date & time?NOW()
5. Can functions be used in WHERE clause?
Yes.
Summary
- MySQL function manipulate and analyze data
- String, numeric, date, aggregate functions are core
- Used heavily in SELECT and reports
- Essential for backend & interviews
- Improve query power and readability
Mastering MySQL function will make you confident in database queries, backend logic, and SQL interviews
