Prev Next

MySQL Functions

MySQL provides a variety of built-in functions for performing operations on data. These functions can be categorized into numeric, string, date, and aggregate functions. They are essential for data manipulation and querying.

Key Topics

1. Numeric Functions

These functions are used to perform mathematical calculations on numeric values.

Example: Using ROUND and ABS

SELECT ROUND(123.456, 2) AS rounded_value, ABS(-123) AS absolute_value;

Code Explanation: ROUND rounds the number 123.456 to two decimal places, and ABS returns the absolute value of -123.

Additional Numeric Functions

1. CEIL() and FLOOR()

SELECT CEIL(123.456) AS ceiling_value, FLOOR(123.456) AS floor_value;

Code Explanation: CEIL rounds the number up to the nearest integer, and FLOOR rounds it down to the nearest integer.

2. TRUNCATE()

SELECT TRUNCATE(123.456, 1) AS truncated_value;

Code Explanation: TRUNCATE truncates the number 123.456 to one decimal place, resulting in 123.4.

2. String Functions

These functions are used to manipulate text strings.

Example: Using CONCAT and UPPER

SELECT CONCAT('Raja', ' Raja Chola') AS full_name, UPPER('chola') AS upper_case_text;

Code Explanation: CONCAT concatenates strings, and UPPER converts 'chola' to uppercase.

Additional String Functions

1. REPLACE()

SELECT REPLACE('Raja Raja Chola', 'Chola', 'The Great') AS modified_text;

Code Explanation: REPLACE replaces 'Chola' with 'The Great', resulting in 'Raja Raja The Great'.

2. LENGTH()

SELECT LENGTH('Raja Raja Chola') AS string_length;

Code Explanation: LENGTH returns the number of bytes in the string 'Raja Raja Chola'.

3. Date Functions

These functions are used to manipulate date and time values.

Example: Using NOW and DATE_ADD

SELECT NOW() AS current_datetime, DATE_ADD(NOW(), INTERVAL 5 DAY) AS future_date;

Code Explanation: NOW returns the current date and time, and DATE_ADD adds 5 days to the current date.

Additional Date Functions

1. DATEDIFF()

SELECT DATEDIFF('2024-11-03', '2024-11-01') AS days_difference;

Code Explanation: DATEDIFF returns the difference in days between the two dates.

2. DATE_FORMAT()

SELECT DATE_FORMAT('2024-11-03', '%W, %M %d, %Y') AS formatted_date;

Code Explanation: DATE_FORMAT formats the date in a readable format: 'Sunday, November 03, 2024'.

4. Aggregate Functions

These functions perform calculations on a set of values and return a single value.

Example: Using SUM and COUNT

SELECT SUM(reign_years) AS total_years, COUNT(*) AS number_of_kings FROM tamil_kings_auto_increment;

Code Explanation: SUM calculates the total reign years, and COUNT returns the number of kings in the table.

Additional Aggregate Functions

1. AVG()

SELECT AVG(reign_years) AS average_reign FROM tamil_kings_auto_increment;

Code Explanation: AVG calculates the average number of reign years.

2. MAX() and MIN()

SELECT MAX(reign_years) AS longest_reign, MIN(reign_years) AS shortest_reign FROM tamil_kings_auto_increment;

Code Explanation: MAX returns the longest reign in years, and MIN returns the shortest.

Best Practices

  • Use functions efficiently to optimize query performance.
  • Be cautious with functions that may impact performance, especially in large datasets.

Key Takeaways

  • MySQL functions simplify data manipulation and querying.
  • Different functions are available for numeric, string, date, and aggregate operations.
Prev Next