MySQL Functions

MySQL provides a wide range of built-in functions that allow you to manipulate data directly in SQL queries. These functions can be categorized into string functions, numeric functions, date functions, and aggregate functions.

Categories of MySQL Functions

• String Functions - Text manipulation
• Numeric Functions - Mathematical operations
• Date Functions - Date and time manipulation
• Aggregate Functions - Calculations on multiple rows
• Control Flow Functions - Conditional logic
• System Functions - Database information

String Functions

Functions for manipulating text data:

String Function Examples

-- Combine first and last name
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- Extract domain from email
SELECT 
  email,
  SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;

-- Format phone numbers
SELECT 
  phone,
  CONCAT('+1-', SUBSTRING(phone, 1, 3), '-', 
         SUBSTRING(phone, 4, 3), '-', 
         SUBSTRING(phone, 7, 4)) AS formatted_phone
FROM contacts;

Numeric Functions

Functions for mathematical operations:

Numeric Function Examples

-- Calculate discount prices
SELECT 
  product_name,
  price,
  ROUND(price * 0.9, 2) AS discounted_price,
  ROUND(price * 0.9 * 1.18, 2) AS price_with_tax
FROM products;

-- Generate random sample
SELECT * FROM users ORDER BY RAND() LIMIT 10;

-- Calculate percentages
SELECT 
  department,
  COUNT(*) as total,
  COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees) as percentage
FROM employees
GROUP BY department;

Date Functions

Functions for date and time manipulation:

Date Function Examples

-- Calculate age from birthdate
SELECT 
  first_name,
  birth_date,
  YEAR(CURDATE()) - YEAR(birth_date) - 
  (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birth_date, '%m%d')) AS age
FROM employees;

-- Get orders from last 30 days
SELECT * FROM orders 
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Format dates for display
SELECT 
  order_id,
  DATE_FORMAT(order_date, '%W, %M %d, %Y') AS formatted_date,
  DATE_FORMAT(order_date, '%H:%i %p') AS order_time
FROM orders;

Aggregate Functions

Functions that operate on multiple rows:

Aggregate Function Examples

-- Department statistics
SELECT 
  department,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department;

-- List all products in each category
SELECT 
  category_name,
  GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM products p
JOIN categories c ON p.category_id = c.category_id
GROUP BY category_name;

Control Flow Functions

-- IF function
SELECT 
  name,
  salary,
  IF(salary > 50000, 'High', 'Low') AS salary_level
FROM employees;

-- CASE statement
SELECT 
  product_name,
  price,
  CASE 
    WHEN price > 1000 THEN 'Premium'
    WHEN price > 500 THEN 'Standard'
    ELSE 'Budget'
  END AS price_category
FROM products;

-- COALESCE (returns first non-null value)
SELECT 
  name,
  COALESCE(phone, email, 'No contact') AS contact_info
FROM customers;

Window Functions (MySQL 8.0+)

-- ROW_NUMBER for ranking
SELECT 
  department,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

-- Running total
SELECT 
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Custom Functions

-- Create a custom function
DELIMITER //
CREATE FUNCTION CalculateAge(birthdate DATE) 
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN TIMESTAMPDIFF(YEAR, birthdate, CURDATE());
END //
DELIMITER ;

-- Use custom function
SELECT 
  first_name,
  birth_date,
  CalculateAge(birth_date) AS age
FROM employees;

Best Practices

• Use built-in functions when possible (optimized)
• Avoid functions on indexed columns in WHERE clauses
• Use DATE_FORMAT for localized date displays
• Be mindful of timezone issues with date functions
• Test functions with NULL values
• Document complex function usage