MySQL functions are predefined code blocks that perform specific operations and return a result. They are categorized into four primary types: string functions, numeric functions, date functions, and conditional (flow control) functions.
String Functions
Commonly used string manipulation functions include:
| Function | Description |
|---|---|
concat(s1, s2, ...) |
Combines multiple strings into one. |
lower(str) |
Converts a string to lowercase. |
upper(str) |
Converts a string to uppercase. |
lpad(str, n, pad) |
Pads the left side of a string to length n using the pad string. |
rpad(str, n, pad) |
Pads the right side of a string to length n using the pad string. |
trim(str) |
Removes leading and trailing spaces. |
substring(str, start, len) |
Extracts a substring of len characters starting from position start. |
Examples:
-- String concatenation
SELECT concat('Data', 'base');
-- Convert to lowercase
SELECT lower('SQL');
-- Convert to uppercase
SELECT upper('query');
-- Left padding
SELECT lpad('5', 4, '0'); -- Result: '0005'
-- Right padding
SELECT rpad('5', 4, '!'); -- Result: '5!!!'
-- Trim spaces
SELECT trim(' Text ');
-- Extract substring
SELECT substring('MySQL Functions', 1, 5); -- Result: 'MySQL'
Numeric Functions
Frequently used numeric functions are:
| Function | Description |
|---|---|
ceil(x) |
Returns the smallest integer greater than or equal to x. |
floor(x) |
Returns the largest integer less than or equal to x. |
mod(x, y) |
Returns the remainder of x divided by y. |
rand() |
Generates a random floating-point number betweeen 0 and 1. |
round(x, y) |
Rounds x to y decimal places. |
Examples:
-- Round up
SELECT ceil(3.14); -- Result: 4
-- Round down
SELECT floor(3.99); -- Result: 3
-- Get remainder
SELECT mod(10, 3); -- Result: 1
-- Generate random number
SELECT rand();
-- Round to 2 decimal places
SELECT round(15.876, 2); -- Result: 15.88
Date and Time Functions
Common date and time functions include:
| Function | Description |
|---|---|
curdate() |
Returns the current date. |
curtime() |
Returns the current time. |
now() |
Returns the current date and time. |
year(date) |
Extracts the year from a date. |
month(date) |
Extracts the month from a date. |
day(date) |
Exrtacts the day from a date. |
date_add(date, INTERVAL expr type) |
Adds a specified time interval to a date. |
datediff(date1, date2) |
Returns the number of days between date1 and date2. |
Examples:
-- Get current date
SELECT curdate();
-- Get current time
SELECT curtime();
-- Get current timestamp
SELECT now();
-- Extract date parts
SELECT year(now()), month(now()), day(now());
-- Add 30 days to the current date
SELECT date_add(now(), INTERVAL 30 DAY);
-- Calculate days between two dates
SELECT datediff('2023-12-31', '2023-01-01');
Conditional Functions
These functions implement flow control directly within SQL queries.
| Function | Description |
|---|---|
if(condition, value_if_true, value_if_false) |
Returns one value if the condition is true, another if false. |
ifnull(value1, value2) |
Returns value1 if it is not NULL; otherwise, returns value2. |
CASE WHEN ... THEN ... END |
Evaluates conditions and returns a value when the first condition is met. |
Examples:
-- Simple IF condition
SELECT if(10 > 5, 'Yes', 'No'); -- Result: 'Yes'
-- Handle NULL values
SELECT ifnull(NULL, 'Replacement Value');
SELECT ifnull('Actual Value', 'Replacement Value');
-- Categorize data with CASE
SELECT
product_name,
CASE
WHEN price > 100 THEN 'Expensive'
WHEN price > 50 THEN 'Moderate'
ELSE 'Affordable'
END AS price_category
FROM products;
-- Score grading example
CREATE TABLE student_grades (
student_id INT,
student_name VARCHAR(50),
math_score INT,
science_score INT
);
INSERT INTO student_grades VALUES
(1, 'Alice', 92, 88),
(2, 'Bob', 71, 65),
(3, 'Charlie', 58, 91);
SELECT
student_name,
CASE
WHEN math_score >= 90 THEN 'A'
WHEN math_score >= 80 THEN 'B'
WHEN math_score >= 70 THEN 'C'
WHEN math_score >= 60 THEN 'D'
ELSE 'F'
END AS math_grade,
CASE
WHEN science_score >= 90 THEN 'A'
WHEN science_score >= 80 THEN 'B'
WHEN science_score >= 70 THEN 'C'
WHEN science_score >= 60 THEN 'D'
ELSE 'F'
END AS science_grade
FROM student_grades;