MySQL offers a wide range of built-in functions to facilitate data operations. On the basis of their use cases, these functions are divided into many categories, including date/time, string, numeric, aggregate functions. In this article, we will learn about MySQL functions in detail with help of examples.
String Functions
Text-based data can be manipulated and information can be retrieved using string methods.
1. UPPER() / LOWER()
It converts text to uppercase or lowercase.
Example
SELECT UPPER('hello');
SELECT LOWER('HELLO');
Output
HELLO
hello
2. CONCAT()
It combines multiple strings into one.
Example
SELECT CONCAT('Hello', ' ', 'World');
Output
Hello World
3. SUBSTRING()
It extracts a portion of a string.
Example
SELECT SUBSTRING('MySQL Functions', 1, 5);
Output
MySQL
4. LENGTH()
It returns the length of a string in bytes.
Example
SELECT LENGTH('Hello');
Output
5
5. REPLACE()
It replaces occurrences of a substring.
Example
SELECT REPLACE('Hello World', 'World', 'MySQL');
Output
Hello MySQL
Numeric Functions
Numeric functions perform mathematical calculations.
1. ROUND()
It rounds a number to the nearest whole or decimal place.
Example
SELECT ROUND(3.456, 2);
Output
3.46
2. CEIL() / FLOOR()
It CEIL() rounds up, FLOOR() rounds down.
Example
SELECT CEIL(3.2); SELECT FLOOR(3.7);
Output
4
3
3. MOD()
It returns the remainder of division.
Example
SELECT MOD(10, 3);
Output
1
4. POWER()
It raises a number to a given power.
Example
SELECT POWER(2, 3);
Output
8
5. RAND()
It generates a random number between 0 and 1.
Example
SELECT RAND();
Output
0.345678 (varies)
Output will be always different.
Also read about MySQL SHOW Statement
Date and Time Functions
Date functions manipulate and format date values.
1. NOW() / CURDATE() / CURTIME()
It fetches the current date and time.
Example
SELECT NOW(); SELECT CURDATE(); SELECT CURTIME();
Output
2025-01-31 22:18:30
2025-01-31
22:18:30
2. DATE_ADD() / DATE_SUB()
It adds or subtracts time intervals.
Example
SELECT DATE_ADD('2025-01-30', INTERVAL 7 DAY);
SELECT DATE_SUB('2025-01-30', INTERVAL 3 MONTH);
Output
2025-02-07
2024-10-31
3. DATEDIFF()
It finds the difference between two dates.
Example
SELECT DATEDIFF('2025-01-30', '2024-12-25');
Output
36
4. EXTRACT()
It extracts parts from a date.
Example
SELECT EXTRACT(YEAR FROM '2025-01-30');
Output
2025
Aggregate Functions
Aggregate functions are used to perform calculations on multiple rows.
1. COUNT()
It returns the number of records.
Example
SELECT COUNT(*) FROM users;
Output
50
2. SUM()
It adds up numeric values.
Example
SELECT SUM(marks) FROM users;
Output
245
3. AVG()
It calculates the average value.
Example
SELECT AVG(marks) FROM users;
Output
49
4. MAX() / MIN()
It returns the highest and lowest values.
Example
SELECT MAX(marks) FROM employees; SELECT MIN(marks) FROM employees;
Output
80
55
JSON Functions
MySQL provides built-in JSON functions for handling JSON data.
1. JSON_OBJECT()
It creates a JSON object.
Example
SELECT JSON_OBJECT('name', 'John', 'age', 30);
Output
{"name": "John", "age": 30}
2. JSON_EXTRACT()
It extracts values from JSON data.
Example
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
Output
"John"
Encryption and Hashing Functions
It handles encryption functions to secure sensitive data.
1. MD5()
It generates a 32-character hash.
Example
SELECT MD5('password');
Output
5f4dcc3b5aa765d61d8327deb882cf99
2. SHA1()
It generates a SHA-1 hash.
Example
SELECT SHA1('password');
Output
5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8
System Functions
VERSION()
It returns the MySQL version.
Example
SELECT VERSION();
Output
8.0.32
Conclusion
You can effectively work with strings, integers, dates, and JSON data thanks to MySQL's extensive function set. These features increase MySQL queries' adaptability and strength, making them essential for data analysis and database administration.
You may create SQL queries that are optimized, handle complicated data effectively, and enhance database speed.
