MySQL provides rich string, math, and date functions for data transformation and calculations. Key categories: (1) String: CONCAT() joins, SUBSTRING() extracts, REPLACE() substitutes, UPPER()/LOWER() case. (2) Math: ROUND() rounds, CEIL()/FLOOR() round up/down, ABS() absolute, MOD() modulo. (3) Date: DATE_FORMAT() formats, DATE_ADD()/DATE_SUB() add/subtract, DATEDIFF() difference. (4) Aggregate: COUNT(), SUM(), AVG(), MIN(), MAX(). Performance: Functions in WHERE prevent index usage (slow), use range comparisons instead. Real-world: Amazon uses CONCAT() for names. Stripe uses ROUND() for currency. Google Analytics uses DATE_FORMAT() for reports.

**1. String Functions:**

**CONCAT() - Join Strings:**

```sql

-- Join first and last name

SELECT CONCAT(first_name, ' ', last_name) AS full_name

FROM users;

-- Returns: John Doe

-- Join with separator

SELECT CONCAT(city, ', ', state, ' ', zip) AS address

FROM addresses;

-- Returns: San Francisco, CA 94102

```

**SUBSTRING() - Extract Substring:**

```sql

-- Extract first 3 characters

SELECT SUBSTRING(product_code, 1, 3) AS category

FROM products;

-- product_code='LAP-123' returns 'LAP'

-- Extract from position to end

SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain

FROM users;

-- email='john@example.com' returns 'example.com'

```

**REPLACE() - Replace Substring:**

```sql

-- Replace spaces with hyphens

SELECT REPLACE(product_name, ' ', '-') AS slug

FROM products;

-- 'MacBook Pro' returns 'MacBook-Pro'

```

**UPPER()/LOWER() - Case Conversion:**

```sql

-- Convert to uppercase

SELECT UPPER(email) AS email_upper

FROM users;

-- 'john@example.com' returns 'JOHN@EXAMPLE.COM'

-- Case-insensitive search

SELECT * FROM products

WHERE LOWER(name) LIKE LOWER('%laptop%');