MySQL Functions
MySQL Functions
MySQL provides a wide range of built-in functions that allow you to perform operations on data directly in SQL queries.
Functions are categorized into String, Numeric, Date/Time, Aggregate, and Control Flow functions.
🔹 1. String Functions
| Function | Description & Example |
|---|---|
CONCAT(str1,str2,...) |
Concatenates strings: CONCAT('Hello',' ','World') → 'Hello World' |
UPPER(str) |
Converts string to uppercase: UPPER('vipul') → 'VIPUL' |
LOWER(str) |
Converts string to lowercase: LOWER('VIPUL') → 'vipul' |
LENGTH(str) |
Returns length of string: LENGTH('Hello') → 5 |
SUBSTRING(str,start,length) |
Extracts substring: SUBSTRING('Hello',2,3) → 'ell' |
TRIM(str) |
Removes leading/trailing spaces: TRIM(' Hi ') → 'Hi' |
REPLACE(str,from,to) |
Replaces substring: REPLACE('Hello','l','L') → 'HeLLo' |
🔹 2. Numeric Functions
| Function | Description & Example |
|---|---|
ABS(x) |
Returns absolute value: ABS(-10) → 10 |
ROUND(x, d) |
Rounds number x to d decimal places: ROUND(12.3456,2) → 12.35 |
CEIL(x) / CEILING(x) |
Rounds up: CEIL(12.3) → 13 |
FLOOR(x) |
Rounds down: FLOOR(12.7) → 12 |
POWER(x,y) |
Raises x to the power y: POWER(2,3) → 8 |
SQRT(x) |
Square root: SQRT(16) → 4 |
🔹 3. Date and Time Functions
| Function | Description & Example |
|---|---|
NOW() |
Current date and time: '2025-12-02 10:30:00' |
CURDATE() |
Current date: '2025-12-02' |
CURTIME() |
Current time: '10:30:00' |
DATE(column) |
Extracts date from DATETIME |
YEAR(date) |
Returns year: YEAR('2025-12-02') → 2025 |
MONTH(date) |
Returns month: MONTH('2025-12-02') → 12 |
DAY(date) |
Returns day: DAY('2025-12-02') → 2 |
DATE_ADD(date, INTERVAL n unit) |
Adds interval to date |
DATE_SUB(date, INTERVAL n unit) |
Subtracts interval from date |
DATEDIFF(date1,date2) |
Returns difference in days between two dates |
🔹 4. Aggregate Functions
| Function | Description & Example |
|---|---|
COUNT(column) |
Counts rows: COUNT(student_id) |
SUM(column) |
Sum of values: SUM(marks) |
AVG(column) |
Average: AVG(marks) |
MIN(column) |
Minimum value: MIN(marks) |
MAX(column) |
Maximum value: MAX(marks) |
🔹 5. Control Flow Functions
| Function | Description & Example |
|---|---|
IF(condition, value_if_true, value_if_false) |
Conditional logic: IF(marks>=50,'Pass','Fail') |
CASE WHEN ... THEN ... ELSE ... END |
Multiple conditions handling |
NULLIF(expr1, expr2) |
Returns NULL if expr1 = expr2 |
COALESCE(expr1, expr2,...) |
Returns first non-NULL value |
🔹 Example Query Using Multiple Functions
-
Combines string, numeric, date, and control flow functions.
🔹 Key Points
-
String, Numeric, Date, Aggregate, and Control Flow functions help manipulate data efficiently.
-
Functions can be used in SELECT, WHERE, ORDER BY, and GROUP BY clauses.
-
Combining multiple functions allows complex data processing directly in SQL.
-
Using functions improves query efficiency and reduces the need for post-processing in applications.
