What are the most used MySQL functions?

The 10 most used MySQL functions are: COUNT() for counting rows, SUM() for summing values, AVG() for averages, MAX() and MIN() for finding extremes, CONCAT() for string concatenation, SUBSTRING() for text extraction, DATE_FORMAT() for date formatting, IF() for simple conditionals, and CASE for complex conditionals. These functions cover most common database operations.

How do I use COUNT() function in MySQL?

Use COUNT(*) to count all rows, COUNT(column) to count non-NULL values, or COUNT(DISTINCT column) to count unique values. Example: SELECT COUNT(*) FROM users; counts all users, SELECT COUNT(DISTINCT user_id) FROM orders; counts unique users who made orders.

What is the difference between IF() and CASE in MySQL?

IF() is for simple true/false conditions: IF(condition, value_if_true, value_if_false). CASE is for multiple conditions: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default END. Use IF() for simple conditions, CASE for complex multi-condition logic.

How do I format dates in MySQL?

Use DATE_FORMAT(date, format) function. Common formats: '%Y-%m-%d' for YYYY-MM-DD, '%M %d, %Y' for "January 15, 2024", '%H:%i:%s' for time. Example: SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;

MySQL 10 Most Used Functions: Complete Guide with Examples

MySQL functions are essential building blocks for database queries. Understanding the most used functions and how to apply them effectively can dramatically improve your database operations. This guide covers the 10 most used MySQL functions with detailed explanations, real-world examples, and best practices.

Whether you're calculating aggregates, manipulating strings, formatting dates, or implementing conditional logic, these functions will handle most of your MySQL query needs.

10 Most Used MySQL Functions

COUNT()

Aggregate

Counts the number of rows in a result set or non-NULL values in a column

Syntax: COUNT(expression)

Examples:

Count all rows

SELECT COUNT(*) FROM users;

Returns total number of rows in users table

Count non-NULL values

SELECT COUNT(email) FROM users;

Counts only rows where email is not NULL

Count with condition

SELECT COUNT(*) FROM orders WHERE status = 'completed';

Counts orders with completed status

Count distinct values

SELECT COUNT(DISTINCT user_id) FROM orders;

Counts unique user IDs in orders table

Use Cases:

Counting records
Data validation
Statistics
Reporting

💡 Pro Tip:

Use COUNT(*) for total rows, COUNT(column) for non-NULL values, COUNT(DISTINCT column) for unique values

SUM()

Aggregate

Calculates the sum of numeric values in a column

Syntax: SUM(column)

Examples:

Sum all values

SELECT SUM(amount) FROM transactions;

Returns total sum of all transaction amounts

Sum with condition

SELECT SUM(price * quantity) AS total FROM order_items WHERE order_id = 123;

Calculates total value for specific order

Sum with GROUP BY

SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id;

Calculates total spent per user

Use Cases:

Financial calculations
Revenue totals
Inventory sums
Performance metrics

💡 Pro Tip:

SUM() ignores NULL values. Use COALESCE() if you want to treat NULL as 0

AVG()

Aggregate

Calculates the average (mean) of numeric values in a column

Syntax: AVG(column)

Examples:

Average value

SELECT AVG(price) FROM products;

Returns average price of all products

Average with condition

SELECT AVG(rating) FROM reviews WHERE product_id = 456;

Calculates average rating for specific product

Average per group

SELECT category, AVG(price) AS avg_price FROM products GROUP BY category;

Shows average price per product category

Use Cases:

Performance metrics
Ratings and reviews
Statistical analysis
Quality metrics

💡 Pro Tip:

AVG() ignores NULL values. For weighted averages, use SUM() / COUNT()

MAX()

Aggregate

Returns the maximum value from a column

Syntax: MAX(column)

Examples:

Maximum value

SELECT MAX(price) FROM products;

Finds the highest price in products table

Maximum date

SELECT MAX(created_at) FROM orders;

Finds the most recent order date

Maximum per group

SELECT category, MAX(price) FROM products GROUP BY category;

Shows maximum price for each category

Use Cases:

Finding highest values
Latest dates
Peak performance
Top records

💡 Pro Tip:

Works with numbers, dates, and strings. For dates, returns most recent date

MIN()

Aggregate

Returns the minimum value from a column

Syntax: MIN(column)

Examples:

Minimum value

SELECT MIN(price) FROM products;

Finds the lowest price in products table

Minimum date

SELECT MIN(created_at) FROM users;

Finds the oldest user registration date

Minimum per group

SELECT department, MIN(salary) FROM employees GROUP BY department;

Shows minimum salary for each department

Use Cases:

Finding lowest values
Oldest dates
Baseline metrics
Minimum requirements

💡 Pro Tip:

Works with numbers, dates, and strings. For dates, returns earliest date

CONCAT()

String

Concatenates two or more strings together

Syntax: CONCAT(str1, str2, ...)

Examples:

Basic concatenation

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

Combines first and last name with space

With separator

SELECT CONCAT_WS(' - ', city, state, country) AS location FROM addresses;

Uses CONCAT_WS for separator between values

With numbers

SELECT CONCAT('Order #', order_id, ' - $', total) AS order_info FROM orders;

Combines strings and numbers (auto-converted)

Use Cases:

Full names
Addresses
Display formatting
Dynamic strings

💡 Pro Tip:

Use CONCAT_WS() when you need a separator. CONCAT() returns NULL if any argument is NULL

SUBSTRING()

String

Extracts a substring from a string

Syntax: SUBSTRING(str, pos, len) or SUBSTRING(str FROM pos FOR len)

Examples:

Extract substring

SELECT SUBSTRING(email, 1, 5) AS prefix FROM users;

Extracts first 5 characters from email

Extract domain

SELECT SUBSTRING(email, POSITION('@' IN email) + 1) AS domain FROM users;

Extracts domain part from email address

Extract year from date string

SELECT SUBSTRING('2024-01-15', 1, 4) AS year;

Extracts year from date string

Use Cases:

Text extraction
Data parsing
String manipulation
Formatting

💡 Pro Tip:

Position starts at 1. Use SUBSTRING_INDEX() for delimiter-based extraction

DATE_FORMAT()

Date/Time

Formats a date value according to a specified format

Syntax: DATE_FORMAT(date, format)

Examples:

Format date

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS date FROM orders;

Formats date as YYYY-MM-DD

Readable format

SELECT DATE_FORMAT(created_at, '%M %d, %Y') AS formatted_date FROM orders;

Formats as "January 15, 2024"

With time

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS datetime FROM orders;

Formats date and time together

Use Cases:

Report formatting
Display dates
Date parsing
Localization

💡 Pro Tip:

Common format codes: %Y (year), %m (month), %d (day), %H (hour), %i (minute), %s (second)

IF()

Conditional

Returns one value if condition is true, another if false

Syntax: IF(condition, value_if_true, value_if_false)

Examples:

Simple condition

SELECT IF(price > 100, 'Expensive', 'Affordable') AS price_category FROM products;

Categorizes products based on price

Null handling

SELECT IF(email IS NULL, 'No email', email) AS user_email FROM users;

Replaces NULL email with default text

Nested IF

SELECT IF(score >= 90, 'A', IF(score >= 80, 'B', 'C')) AS grade FROM scores;

Nested IF for multiple conditions

Use Cases:

Conditional logic
Default values
Categorization
Data transformation

💡 Pro Tip:

For multiple conditions, use CASE WHEN instead of nested IF() for better readability

CASE

Conditional

Performs conditional logic with multiple conditions

Syntax: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default END

Examples:

Simple CASE

SELECT CASE status WHEN 'active' THEN 1 WHEN 'inactive' THEN 0 ELSE -1 END AS status_code FROM users;

Maps status values to codes

Searched CASE

SELECT CASE 
  WHEN age < 18 THEN 'Minor'
  WHEN age < 65 THEN 'Adult'
  ELSE 'Senior'
END AS age_group FROM users;

Categorizes users by age groups

CASE in calculations

SELECT 
  price * CASE 
    WHEN quantity > 10 THEN 0.9
    WHEN quantity > 5 THEN 0.95
    ELSE 1
  END AS discounted_price 
FROM order_items;

Applies discount based on quantity

Use Cases:

Complex conditions
Data categorization
Conditional calculations
Business logic

💡 Pro Tip:

CASE is more readable than nested IF(). Always include ELSE clause for safety

Function Categories

Aggregate Functions

Operate on multiple rows and return a single value:

COUNT() - Count rows or values
SUM() - Sum numeric values
AVG() - Calculate average
MAX() - Find maximum value
MIN() - Find minimum value

String Functions

Manipulate and format text data:

CONCAT() - Join strings together
SUBSTRING() - Extract parts of strings

Date/Time Functions

Format and manipulate dates:

DATE_FORMAT() - Format dates as strings

Conditional Functions

Implement conditional logic:

IF() - Simple true/false conditions
CASE - Complex multi-condition logic

Best Practices

1. Use Appropriate Aggregate Functions

Choose the right aggregate function for your use case. COUNT(*) for row counts, SUM() for totals, AVG() for averages. Always consider NULL values in your calculations.

2. Handle NULL Values Properly

Most aggregate functions ignore NULL values, but string functions may return NULL if any argument is NULL. Use COALESCE() or IFNULL() to handle NULLs explicitly.

3. Use CASE for Complex Logic

For multiple conditions, prefer CASE over nested IF() statements. CASE is more readable and maintainable for complex conditional logic.

4. Optimize String Operations

String functions like CONCAT() and SUBSTRING() can be expensive on large datasets. Consider indexing computed columns or using application-level string manipulation when possible.

Format Your MySQL Queries

Use our SQL Formatter tool to format, validate, and beautify your MySQL queries for better readability and debugging.

Open SQL Formatter