MySQL Functions

A MySQL function is a built-in operation that performs a specific task and returns a value.

SELECT UPPER('hello');

Result:

HELLO

The UPPER() function converts text to uppercase.

Functions make SQL queries more powerful and useful.

• Perform calculations
• Modify text
• Work with dates and times
• Calculate totals
• Count records
• Find maximum and minimum values
• Format data

The common syntax is:

FUNCTION_NAME(value);

Example:

SELECT LENGTH('MySQL');

Result:

5

Functions can be applied directly to table columns.

SELECT UPPER(name)
FROM students;

This converts every student's name to uppercase in the query result.

The UPPER() function converts text to uppercase.

SELECT UPPER('python');

Result:

PYTHON

Example with a table:

SELECT UPPER(name)
FROM students;

The LOWER() function converts text to lowercase.

SELECT LOWER('MYSQL');

Result:

mysql

Example:

SELECT LOWER(email)
FROM students;

The LENGTH() function returns the length of a string in bytes.

SELECT LENGTH('MySQL');

Result:

5

For ASCII characters, the byte length is normally the same as the character count.

CHAR_LENGTH() returns the number of characters in a string.

SELECT CHAR_LENGTH('Hello');

Result:

5

CHAR_LENGTH() is especially useful when working with multibyte character sets because it counts characters rather than bytes.

The CONCAT() function joins multiple strings together.

SELECT CONCAT('Hello', ' ', 'World');

Result:

Hello World

Example:

SELECT CONCAT(first_name, ' ', last_name)
FROM students;

The ROUND() function rounds a number to a specified number of decimal places.

SELECT ROUND(125.678, 2);

Result:

125.68

Example:

SELECT ROUND(fee, 2)
FROM students;

The CEIL() function returns the smallest integer greater than or equal to a number.

SELECT CEIL(12.3);

Result:

13

The FLOOR() function returns the largest integer less than or equal to a number.

SELECT FLOOR(12.9);

Result:

12

The ABS() function returns the absolute value of a number.

SELECT ABS(-50);

Result:

50

Example:

SELECT ABS(-125.50);

The MOD() function returns the remainder after division.

SELECT MOD(10, 3);

Result:

1

Because 10 divided by 3 leaves a remainder of 1.

The NOW() function returns the current date and time.

SELECT NOW();

The exact result depends on the MySQL server's current date and time.

The CURDATE() function returns the current date.

SELECT CURDATE();

Example result:

2026-09-21

The actual result changes according to the current date.

The CURTIME() function returns the current time.

SELECT CURTIME();

The result depends on the current MySQL server time.

The YEAR() function extracts the year from a date.

SELECT YEAR('2026-09-21');

Result:

2026

Example:

SELECT YEAR(admission_date)
FROM students;

The MONTH() function extracts the month number from a date.

SELECT MONTH('2026-09-21');

Result:

9

The DAY() function returns the day of the month from a date.

SELECT DAY('2026-09-21');

Result:

21

The COUNT() function counts rows or non-NULL values depending on how it is used.

SELECT COUNT(*)
FROM students;

This counts the number of rows in the students table.

You can also count non-NULL values in a particular column:

SELECT COUNT(email)
FROM students;

The SUM() function calculates the total of numeric values.

SELECT SUM(fee)
FROM students;

This returns the total fee from the selected rows.

The AVG() function calculates the average of numeric values.

SELECT AVG(fee)
FROM students;

This returns the average fee from the selected rows.

The MAX() function returns the largest value.

SELECT MAX(fee)
FROM students;

This returns the highest fee in the selected data.

The MIN() function returns the smallest value.

SELECT MIN(fee)
FROM students;

This returns the lowest fee in the selected data.

You can use multiple functions in the same query.

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(fee) AS average_fee,
    MAX(fee) AS highest_fee,
    MIN(fee) AS lowest_fee
FROM students;

This query produces several useful statistics at once.

Functions can be used together with WHERE to calculate results for selected records.

SELECT AVG(fee)
FROM students
WHERE course = 'Python';

This calculates the average fee only for Python students.

Aliases make function results easier to understand.

SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee
FROM students;

Here, total_students and total_fee are column aliases for the calculated results.

These categories will be covered in more detail in the upcoming MySQL lessons.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL
);

INSERT INTO students
(name, course, fee)
VALUES
('Amit Kumar', 'Python', 15000.00),
('Priya Singh', 'Java', 18000.00),
('Rahul Kumar', 'Python', 12000.00),
('Neha Sharma', 'PHP', 10000.00);

-- String function
SELECT UPPER(name) AS student_name
FROM students;

-- Numeric function
SELECT ROUND(fee, 0) AS rounded_fee
FROM students;

-- Aggregate functions
SELECT
    COUNT(*) AS total_students,
    SUM(fee) AS total_fee,
    AVG(fee) AS average_fee,
    MAX(fee) AS highest_fee,
    MIN(fee) AS lowest_fee
FROM students;

-- Function with WHERE
SELECT AVG(fee) AS python_average_fee
FROM students
WHERE course = 'Python';

This example demonstrates string, numeric, and aggregate functions in practical queries.