• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Functions

← Previous

In MySQL, functions are built-in operations that let you perform calculations, format data, and manipulate strings, numbers, or dates directly within SQL queries. They help you save time and make queries more powerful by allowing you to process data without needing extra application logic.

This tutorial will explain the main categories of MySQL functions, how they work, and how you can use them in real-world queries.

What Are MySQL Functions?

A function in MySQL is a predefined operation that takes input values (called arguments), performs a specific task, and returns a result.

You can use functions in the SELECT, WHERE, GROUP BY, or ORDER BY clauses to process and format data.

For example:

SELECT UPPER('hello');

Output:

HELLO

Here, UPPER() is a MySQL function that converts text to uppercase.

Types of MySQL Functions

MySQL offers many built-in functions grouped into categories based on the type of operation they perform:

  1. String Functions

  2. Numeric Functions

  3. Date and Time Functions

  4. Aggregate Functions

  5. Control Flow Functions

  6. System Functions

Let’s go through each type in detail with examples.

1. String Functions

String functions are used to handle and manipulate text values. You can use them to change case, extract parts of a string, or measure its length.

Common String Functions

Function Description Example Output
LENGTH(str) Returns length of a string LENGTH('MySQL') 5
UPPER(str) Converts to uppercase UPPER('database') DATABASE
LOWER(str) Converts to lowercase LOWER('HELLO') hello
CONCAT(str1, str2, …) Joins multiple strings CONCAT('My','SQL') MySQL
SUBSTRING(str, start, length) Extracts part of a string SUBSTRING('Database', 1, 4) Data
REPLACE(str, from_str, to_str) Replaces text REPLACE('MySQL', 'SQL', 'DB') MyDB
TRIM(str) Removes spaces from both ends TRIM(' PHP ') PHP

Example:

SELECT CONCAT(UPPER(first_name), ' ', LOWER(last_name)) AS full_name
FROM users;

This query combines first and last names while formatting them in a specific case.

2. Numeric Functions

Numeric functions perform mathematical calculations on numeric data. They’re useful in reports, analytics, and calculations like totals or averages.

Common Numeric Functions

Function Description Example Output
ABS(x) Absolute value ABS(-7) 7
ROUND(x, d) Rounds number to d decimals ROUND(12.3456, 2) 12.35
CEIL(x) Rounds up to nearest integer CEIL(3.1) 4
FLOOR(x) Rounds down FLOOR(3.9) 3
MOD(x, y) Returns remainder MOD(10, 3) 1
POWER(x, y) x raised to y POWER(2, 3) 8
RAND() Returns random number RAND() e.g. 0.583

Example:

SELECT product_name, ROUND(price * 1.18, 2) AS price_with_tax
FROM products;

This calculates product prices with an 18% tax, rounding the result to two decimals.

3. Date and Time Functions

Date and time functions let you work with values like dates, times, and timestamps. They help in generating reports, tracking events, or filtering data based on time.

Common Date Functions

Function Description Example Output
NOW() Current date and time NOW() 2025-10-23 12:45:00
CURDATE() Current date CURDATE() 2025-10-23
CURTIME() Current time CURTIME() 12:45:00
YEAR(date) Extracts year YEAR('2025-10-23') 2025
MONTH(date) Extracts month MONTH('2025-10-23') 10
DAY(date) Extracts day DAY('2025-10-23') 23
DATE_ADD(date, INTERVAL n unit) Adds interval DATE_ADD('2025-10-23', INTERVAL 7 DAY) 2025-10-30
DATEDIFF(date1, date2) Difference in days DATEDIFF('2025-10-30','2025-10-23') 7

Example:

SELECT first_name, hire_date, DATEDIFF(CURDATE(), hire_date) AS days_worked
FROM employees;

This query calculates how many days each employee has worked.

4. Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value. They’re used with the GROUP BY clause to summarize data.

Common Aggregate Functions

Function Description Example
COUNT() Counts rows COUNT(*)
SUM() Adds up values SUM(salary)
AVG() Average value AVG(salary)
MIN() Smallest value MIN(price)
MAX() Largest value MAX(price)

Example:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

This query returns the average salary for each department.

5. Control Flow Functions

Control flow functions add logic to SQL queries, similar to conditions in programming.

Function Description Example Output
IF(condition, true_value, false_value) Returns one of two values IF(10 > 5, 'Yes', 'No') Yes
IFNULL(expr, alt_value) Returns alt if expr is NULL IFNULL(NULL, 'N/A') N/A
NULLIF(expr1, expr2) Returns NULL if equal NULLIF(10,10) NULL
CASE Multiple conditions See example  

Example:

SELECT name,
CASE 
  WHEN marks >= 90 THEN 'A'
  WHEN marks >= 75 THEN 'B'
  WHEN marks >= 60 THEN 'C'
  ELSE 'Fail'
END AS grade
FROM students;

This assigns grades based on marks.

6. System Functions

System functions return information about the MySQL server, database, or user.

Function Description Example Output
USER() Current user USER() root@localhost
DATABASE() Current database name DATABASE() school_db
VERSION() MySQL version VERSION() 8.0.35

Example:

SELECT DATABASE(), USER(), VERSION();

Using Multiple Functions Together

You can combine functions to perform complex operations in a single query.

Example:

SELECT UPPER(name) AS student_name,
ROUND(AVG(marks), 2) AS average_marks,
YEAR(admission_date) AS admission_year
FROM students
GROUP BY admission_year;

This query:

  • Converts names to uppercase

  • Calculates the average marks

  • Groups data by admission year

Best Practices for Using MySQL Functions

  • Use aggregate functions with GROUP BY for summaries.

  • Avoid applying functions directly in the WHERE clause on large datasets (it slows queries).

  • Always use ROUND() for decimals when displaying results.

  • Use IFNULL() to handle missing or NULL values gracefully.

  • Format dates using DATE_FORMAT() when you need readable output.

Summary of the Tutorial

MySQL functions make it easier to analyze, calculate, and format data directly within SQL.
In this tutorial, you learned about:

  • String functions for text manipulation

  • Numeric functions for calculations

  • Date and time functions for temporal data

  • Aggregate functions for summaries

  • Control flow functions for conditional logic

  • System functions for database info

By combining these, you can write clean, powerful queries that handle data smartly without extra programming.

Practice Questions

  1. Create a table named students with columns id, first_name, last_name, and marks. Insert at least 3 records.

  2. Write a query to display the full name of each student by combining first_name and last_name using CONCAT().

  3. Use the UPPER() and LOWER() functions to display all student names in uppercase and lowercase.

  4. Create a table products with columns product_name and price. Write a query to round all prices to 2 decimal places using ROUND().

  5. Write a query to find the total and average price of all products using SUM() and AVG().

  6. Insert a table employees with hire_date column. Write a query to calculate the number of days each employee has worked using DATEDIFF().

  7. Use IF() or CASE to assign grades (A, B, C, Fail) to students based on their marks.

  8. Create a table orders with order_date. Write a query to find orders made in the current month using MONTH() and CURDATE().

  9. Write a query to replace the word “Old” with “New” in a column product_name using REPLACE().

  10. Display the current database, user, and MySQL version using DATABASE(), USER(), and VERSION() functions.

← Previous

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