MySQL Built-in Functions Explained

Overview of MySQL Built-in Functions

MySQL offers a variety of built-in functions that perform specific operations on data values.

These functions can be used in SELECT statements, WHERE clauses, and other parts of SQL queries.

• String functions: manipulate text data.
• Numeric functions: perform mathematical calculations.
• Date and time functions: handle date/time values.
• Aggregate functions: summarize data across rows.

String Functions

String functions allow you to manipulate text data in various ways, such as changing case, trimming spaces, or extracting substrings.

• CONCAT(): Concatenates two or more strings.
• LENGTH(): Returns the length of a string in bytes.
• LOWER() / UPPER(): Converts string to lowercase or uppercase.
• SUBSTRING(): Extracts a substring from a string.
• TRIM(): Removes leading and trailing spaces.

Numeric Functions

Numeric functions perform mathematical operations on numeric data types.

• ABS(): Returns the absolute value.
• ROUND(): Rounds a number to a specified number of decimal places.
• CEIL() / FLOOR(): Returns the smallest integer greater than or equal to, or largest integer less than or equal to a number.
• MOD(): Returns the remainder of division.

Date and Time Functions

Date and time functions help you manipulate and extract information from date and time values.

• NOW(): Returns the current date and time.
• CURDATE(): Returns the current date.
• DATE_FORMAT(): Formats a date according to a specified format.
• DATEDIFF(): Returns the difference in days between two dates.

Aggregate Functions

Aggregate functions perform calculations on multiple rows and return a single value.

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of values.
• AVG(): Calculates the average value.
• MAX() / MIN(): Returns the maximum or minimum value.

Practical Examples

Let's look at some practical examples demonstrating the use of built-in functions.