Built-in Functions in MySQL

1. Introduction to Built-in Functions

• Built-in functions are keywords in MySQL that accept parameters and produce output.
• They are categorized into four types:
  • String functions
  • Numeric functions
  • Date functions
  • Aggregate functions

2. Categories of Built-in Functions

String Functions

• Functions that operate on string data types.
• Examples include:
  • UPPER(): Converts any character to uppercase.
    • Usage: SELECT UPPER(first_name) FROM Employees;
  • LOWER(): Converts any character to lowercase.
  • LENGTH(): Returns the length of a string.
    • Usage: SELECT LENGTH('welcome'); will return 7.
  • TRIM(): Removes specified characters from both sides of a string.
  • INSTR(): Returns the position of a character within a string.
    • Usage: INSTR('oracle', 'r') returns the position of 'r'.
  • SUBSTRING() (or SUBSTR()): Returns a substring from a string.
  • CONCAT(): Joins two or more strings together.
    • Usage: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Employees;

Numeric Functions

• Functions that work on numeric columns.
• Examples include:
  • ABS(): Returns absolute value.
  • SQRT(): Returns square root.
  • MOD(): Returns the remainder.
  • POWER(): Raises a number to a power.
  • TRUNCATE(): Truncates a number to a specified number of decimal places.
  • GREATEST(): Finds the greatest number among the provided list.
  • LEAST(): Finds the smallest number among the provided list.

Date Functions

• Functions that perform operations on date data types.
• Examples include:
  • CURRENT_DATE(): Returns the current date.
  • CURRENT_TIME(): Returns the current time.
  • NOW(): Returns the current date and time.
  • YEAR(): Extracts the year from a date.
  • MONTH(): Extracts the month from a date.
  • DAY(): Extracts the day from a date.

Aggregate Functions

• Functions that perform calculations on multiple rows of a single column, returning a single value.
• Examples include:
  • AVG(): Calculates average.
  • SUM(): Calculates total sum.
  • MIN(): Finds minimum value.
  • MAX(): Finds maximum value.
  • COUNT(): Counts the number of records.

3. Key Learning Points

• Each function category contains functions with similar syntaxes across different database systems (e.g., Oracle, SQL Server, DB2).
• SQL functions vary slightly in naming conventions between systems but serve similar purposes.
• Most functions return output that maintains the format of the columns they process (e.g., presenting uppercase values without altering original table values).

4. Resources for Further Learning

• Official MySQL documentation provides detailed information on all available functions and examples:
  • Link to documentation recommended for reference on specific function usage.

Summary

• MySQL built-in functions are essential for data manipulation and retrieval, organized into string, numeric, date, and aggregate functions, each serving unique purposes to facilitate database queries.

🎓