MySQL Functions
MySQL provides a rich set of built-in functions that allow you to manipulate data, perform calculations, format strings, and work with dates and times efficiently. Understanding these functions is essential for writing effective queries and getting the most out of your database.
Introduction to MySQL Functions
MySQL functions are predefined operations that perform specific tasks on data values. These functions take input values (arguments), process them, and return a result. They help simplify complex operations and enable you to transform data without writing extensive code.
MySQL functions can be categorized into several groups:
- String Functions - Manipulate text data
- Numeric Functions - Perform mathematical calculations
- Date and Time Functions - Work with date and timestamp values
- Aggregate Functions - Operate on sets of rows to calculate a single result
- Control Flow Functions - Implement conditional logic
Let's explore each category with practical examples.
String Functions
String functions allow you to manipulate text data in various ways.
Common String Functions
CONCAT()
The CONCAT() function joins two or more strings together.
SELECT CONCAT('Hello, ', 'MySQL', '!') AS greeting;
Output:
+---------------+
| greeting |
+---------------+
| Hello, MySQL! |
+---------------+
LENGTH()
The LENGTH() function returns the length of a string in bytes.
SELECT LENGTH('MySQL') AS string_length;
Output:
+---------------+
| string_length |
+---------------+
| 5 |
+---------------+
UPPER() and LOWER()
These functions convert strings to uppercase or lowercase.
SELECT
UPPER('hello world') AS uppercase,
LOWER('HELLO WORLD') AS lowercase;
Output:
+-------------+-------------+
| uppercase | lowercase |
+-------------+-------------+
| HELLO WORLD | hello world |
+-------------+-------------+
SUBSTRING()
The SUBSTRING() function extracts a portion of a string.
-- Syntax: SUBSTRING(string, start_position, length)
SELECT SUBSTRING('MySQL Function', 1, 5) AS substring_result;
Output:
+------------------+
| substring_result |
+------------------+
| MySQL |
+------------------+
Practical Example: Formatting Names
Let's apply string functions to format customer names in a database:
CREATE TABLE customers (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO customers VALUES
(1, 'john', 'doe', '[email protected]'),
(2, 'jane', 'smith', '[email protected]');
-- Format names with proper capitalization
SELECT
id,
CONCAT(UPPER(SUBSTRING(first_name, 1, 1)),
LOWER(SUBSTRING(first_name, 2))) AS formatted_first_name,
CONCAT(UPPER(SUBSTRING(last_name, 1, 1)),
LOWER(SUBSTRING(last_name, 2))) AS formatted_last_name,
email
FROM customers;
Output:
+----+-------------------+------------------+------------------------+
| id | formatted_first_name | formatted_last_name | email |
+----+-------------------+------------------+------------------------+
| 1 | John | Doe | [email protected] |
| 2 | Jane | Smith | [email protected] |
+----+-------------------+------------------+------------------------+
Numeric Functions
Numeric functions help perform mathematical operations on numeric data types.
Common Numeric Functions
ROUND()
The ROUND() function rounds a number to a specified number of decimal places.
SELECT
ROUND(123.456, 2) AS two_decimals,
ROUND(123.456, 0) AS no_decimals,
ROUND(123.456, -1) AS tens_place;
Output:
+--------------+--------------+------------+
| two_decimals | no_decimals | tens_place |
+--------------+--------------+------------+
| 123.46 | 123 | 120 |
+--------------+--------------+------------+
ABS()
The ABS() function returns the absolute value of a number.
SELECT
ABS(15) AS positive_result,
ABS(-15) AS negative_result;
Output:
+----------------+----------------+
| positive_result | negative_result |
+----------------+----------------+
| 15 | 15 |
+----------------+----------------+
CEIL() and FLOOR()
These functions round numbers up or down to the nearest integer.
SELECT
CEIL(45.1) AS ceiling_value,
FLOOR(45.9) AS floor_value;
Output:
+---------------+-------------+
| ceiling_value | floor_value |
+---------------+-------------+
| 46 | 45 |
+---------------+-------------+
Practical Example: Price Calculations
Let's use numeric functions for some e-commerce price calculations:
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
cost DECIMAL(10,2),
retail_price DECIMAL(10,2)
);
INSERT INTO products VALUES
(1, 'Laptop', 600.00, 999.99),
(2, 'Smartphone', 300.50, 649.50),
(3, 'Headphones', 25.50, 59.99);
-- Calculate profit margin
SELECT
name,
cost,
retail_price,
retail_price - cost AS profit,
ROUND((retail_price - cost) / cost * 100, 1) AS profit_percentage
FROM products;
Output:
+------------+--------+--------------+--------+------------------+
| name | cost | retail_price | profit | profit_percentage |
+------------+--------+--------------+--------+------------------+
| Laptop | 600.00 | 999.99 | 399.99 | 66.7 |
| Smartphone | 300.50 | 649.50 | 349.00 | 116.1 |
| Headphones | 25.50 | 59.99 | 34.49 | 135.3 |
+------------+--------+--------------+--------+------------------+
Date and Time Functions
MySQL provides several functions to work with dates and times.
Common Date Functions
NOW() and CURDATE()
NOW() returns the current date and time, while CURDATE() returns just the current date.
SELECT
NOW() AS current_datetime,
CURDATE() AS current_date;
Output (example):
+---------------------+-------------+
| current_datetime | current_date |
+---------------------+-------------+
| 2023-07-25 15:30:45 | 2023-07-25 |
+---------------------+-------------+
DATE_FORMAT()
The DATE_FORMAT() function formats a date as specified.
SELECT DATE_FORMAT('2023-07-25', '%W, %M %d, %Y') AS formatted_date;
Output:
+----------------------------+
| formatted_date |
+----------------------------+
| Tuesday, July 25, 2023 |
+----------------------------+
DATEDIFF()
The DATEDIFF() function returns the number of days between two dates.
SELECT DATEDIFF('2023-12-31', '2023-01-01') AS days_in_year;
Output:
+--------------+
| days_in_year |
+--------------+
| 364 |
+--------------+
Practical Example: Working with Order Dates
Here's how to use date functions with an orders table:
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATETIME,
delivery_date DATETIME
);
INSERT INTO orders VALUES
(1, 101, '2023-06-15 08:30:00', '2023-06-18 14:45:00'),
(2, 102, '2023-06-20 12:15:00', '2023-06-23 10:30:00'),
(3, 103, '2023-06-25 17:45:00', NULL);
-- Calculate delivery times and format dates
SELECT
id,
customer_id,
DATE_FORMAT(order_date, '%M %d, %Y at %h:%i %p') AS formatted_order_date,
DATEDIFF(delivery_date, order_date) AS delivery_days,
CASE
WHEN delivery_date IS NULL THEN 'Pending'
ELSE 'Delivered'
END AS status
FROM orders;
Output:
+----+-------------+------------------------------+--------------+----------+
| id | customer_id | formatted_order_date | delivery_days | status |
+----+-------------+------------------------------+--------------+----------+
| 1 | 101 | June 15, 2023 at 08:30 AM | 3 | Delivered |
| 2 | 102 | June 20, 2023 at 12:15 PM | 3 | Delivered |
| 3 | 103 | June 25, 2023 at 05:45 PM | NULL | Pending |
+----+-------------+------------------------------+--------------+----------+
Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single value.
Common Aggregate Functions
COUNT()
The COUNT() function returns the number of rows in a specified table or column.
-- Assuming the customers table from earlier examples
SELECT COUNT(*) AS total_customers FROM customers;
Output:
+----------------+
| total_customers |
+----------------+
| 2 |
+----------------+
SUM()
The SUM() function calculates the sum of values in a numeric column.
-- Using the products table from earlier
SELECT SUM(retail_price) AS total_retail_value FROM products;
Output:
+-------------------+
| total_retail_value |
+-------------------+
| 1709.48 |
+-------------------+
AVG(), MIN(), and MAX()
These functions calculate the average, minimum, and maximum values respectively.
SELECT
AVG(retail_price) AS average_price,
MIN(retail_price) AS minimum_price,
MAX(retail_price) AS maximum_price
FROM products;
Output:
+---------------+---------------+---------------+
| average_price | minimum_price | maximum_price |
+---------------+---------------+---------------+
| 569.83 | 59.99 | 999.99 |
+---------------+---------------+---------------+
Practical Example: Sales Analysis
Let's analyze sales data using aggregate functions:
CREATE TABLE sales (
id INT PRIMARY KEY,
product_id INT,
sale_date DATE,
quantity INT,
total_amount DECIMAL(10,2)
);
INSERT INTO sales VALUES
(1, 1, '2023-06-01', 2, 1999.98),
(2, 2, '2023-06-05', 3, 1948.50),
(3, 3, '2023-06-10', 5, 299.95),
(4, 1, '2023-06-15', 1, 999.99),
(5, 3, '2023-06-20', 2, 119.98);
-- Group sales by product and calculate statistics
SELECT
p.name AS product_name,
COUNT(s.id) AS total_sales,
SUM(s.quantity) AS units_sold,
SUM(s.total_amount) AS revenue,
ROUND(AVG(s.total_amount), 2) AS average_sale
FROM sales s
JOIN products p ON s.product_id = p.id
GROUP BY p.name
ORDER BY revenue DESC;
Output:
+-------------+-------------+------------+---------+--------------+
| product_name | total_sales | units_sold | revenue | average_sale |
+-------------+-------------+------------+---------+--------------+
| Laptop | 2 | 3 | 2999.97 | 1499.99 |
| Smartphone | 1 | 3 | 1948.50 | 1948.50 |
| Headphones | 2 | 7 | 419.93 | 209.97 |
+-------------+-------------+------------+---------+--------------+
Control Flow Functions
Control flow functions allow you to add conditional logic to your queries.
Common Control Flow Functions
IF()
The IF() function returns one value if a condition is TRUE, and another value if it's FALSE.
-- Syntax: IF(condition, value_if_true, value_if_false)
SELECT
name,
retail_price,
IF(retail_price > 500, 'Premium', 'Standard') AS product_tier
FROM products;
Output:
+------------+--------------+--------------+
| name | retail_price | product_tier |
+------------+--------------+--------------+
| Laptop | 999.99 | Premium |
| Smartphone | 649.50 | Premium |
| Headphones | 59.99 | Standard |
+------------+--------------+--------------+
CASE
The CASE expression provides more complex conditional logic.
SELECT
name,
retail_price,
CASE
WHEN retail_price < 100 THEN 'Budget'
WHEN retail_price BETWEEN 100 AND 500 THEN 'Standard'
ELSE 'Premium'
END AS price_category
FROM products;
Output:
+------------+--------------+----------------+
| name | retail_price | price_category |
+------------+--------------+----------------+
| Laptop | 999.99 | Premium |
| Smartphone | 649.50 | Premium |
| Headphones | 59.99 | Budget |
+------------+--------------+----------------+
IFNULL() and COALESCE()
These functions handle NULL values by providing default alternatives.
-- IFNULL() replaces NULL with the specified value
SELECT IFNULL(NULL, 'Default Value') AS result;
-- COALESCE() returns the first non-NULL value in a list
SELECT COALESCE(NULL, NULL, 'First non-NULL value', 'Another value') AS result;
Output:
+---------------+
| result |
+---------------+
| Default Value |
+---------------+
+---------------------+
| result |
+---------------------+
| First non-NULL value |
+---------------------+
Practical Example: Order Status Reporting
Let's create a comprehensive order report using control flow functions:
-- Create status report for orders
SELECT
o.id,
c.first_name,
c.last_name,
DATE_FORMAT(o.order_date, '%Y-%m-%d') AS order_date,
CASE
WHEN o.delivery_date IS NULL THEN 'Processing'
WHEN DATEDIFF(o.delivery_date, o.order_date) <= 2 THEN 'Express Delivery'
ELSE 'Standard Delivery'
END AS delivery_type,
IFNULL(DATE_FORMAT(o.delivery_date, '%Y-%m-%d'), 'Pending') AS delivery_status
FROM orders o
JOIN customers c ON o.customer_id = c.id;
Output:
+----+------------+-----------+------------+-------------------+-----------------+
| id | first_name | last_name | order_date | delivery_type | delivery_status |
+----+------------+-----------+------------+-------------------+-----------------+
| 1 | john | doe | 2023-06-15 | Standard Delivery | 2023-06-18 |
| 2 | jane | smith | 2023-06-20 | Standard Delivery | 2023-06-23 |
| 3 | john | doe | 2023-06-25 | Processing | Pending |
+----+------------+-----------+------------+-------------------+-----------------+
Custom Functions in MySQL
MySQL also allows you to create your own functions using the CREATE FUNCTION statement. Custom functions are useful when you need to perform complex operations repeatedly.
Here's a simple example of creating a custom function to calculate a discounted price:
DELIMITER //
CREATE FUNCTION calculate_discount(price DECIMAL(10,2), discount_percentage INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE discounted_price DECIMAL(10,2);
SET discounted_price = price - (price * discount_percentage / 100);
RETURN discounted_price;
END //
DELIMITER ;
-- Using the custom function
SELECT
name,
retail_price,
calculate_discount(retail_price, 15) AS sale_price
FROM products;
Output:
+------------+--------------+------------+
| name | retail_price | sale_price |
+------------+--------------+------------+
| Laptop | 999.99 | 849.99 |
| Smartphone | 649.50 | 552.08 |
| Headphones | 59.99 | 50.99 |
+------------+--------------+------------+
Summary
MySQL functions are powerful tools that allow you to manipulate and process data effectively within your database queries. In this guide, we've covered:
- String Functions for text manipulation (CONCAT, LENGTH, UPPER, LOWER, SUBSTRING)
- Numeric Functions for mathematical operations (ROUND, ABS, CEIL, FLOOR)
- Date and Time Functions for working with temporal data (NOW, CURDATE, DATE_FORMAT, DATEDIFF)
- Aggregate Functions for calculating summary statistics (COUNT, SUM, AVG, MIN, MAX)
- Control Flow Functions for implementing conditional logic (IF, CASE, IFNULL, COALESCE)
- Custom Functions for creating your own specialized operations
By mastering these functions, you can write more efficient and expressive SQL queries, reducing the need for post-processing in your application code.
Practice Exercises
To solidify your understanding of MySQL functions, try these exercises:
- Create a query that formats customer names and extracts their email domain (the part after @).
- Calculate the age of customers based on their birth dates (create a customers table with birth dates if needed).
- Generate a tiered pricing report based on product categories and prices.
- Write a query that identifies products with below-average or above-average pricing.
- Create a custom function that calculates the total price after tax for a given amount and tax rate.
Additional Resources
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)