MySQL Functions

MySQL provides a wide range of built-in functions that can be used to perform various operations on data. These functions can be categorized into several types, including string functions, numeric functions, date and time functions, and aggregate functions.

String Functions

String functions are used to manipulate and analyze string data. Some common string functions include:

  • CONCAT(): Concatenates two or more strings.
  • COMPARE(): Compares two strings and returns 0 if they are equal, a positive number if the first string is greater, and a negative number if the second string is greater.
  • SUBSTRING(): Extracts a substring from a string.
  • LENGTH(): Returns the length of a string.
  • UPPER(): Converts a string to uppercase.
  • LOWER(): Converts a string to lowercase.
  • TRIM(): Removes leading and trailing spaces from a string.
  • REPLACE(): Replaces occurrences of a substring within a string.
  • LEFT(): Returns the leftmost characters from a string.
  • RIGHT(): Returns the rightmost characters from a string.
  • INSTR(): Returns the position of the first occurrence of a substring in a string.

Numeric Functions

Numeric functions are used to perform operations on numeric data. Some common numeric functions include:

  • ABS(): Returns the absolute value of a number.
  • CEIL(): Rounds a number up to the nearest integer.
  • FLOOR(): Rounds a number down to the nearest integer.
  • ROUND(): Rounds a number to a specified number of decimal places.
  • POWER(): Returns the value of a number raised to the power of another number.
  • SQRT(): Returns the square root of a number.
  • MOD(): Returns the remainder of a division operation.
  • RAND(): Returns a random floating-point number between 0 and 1.
  • GREATEST(): Returns the largest value from a list of arguments.
  • LEAST(): Returns the smallest value from a list of arguments.
  • FORMAT(): Formats a number as a string with a specified number of decimal places and optional locale.

Date and Time Functions

Date and time functions are used to manipulate and analyze date and time data. Some common date and time functions include:

  • NOW(): Returns the current date and time.
  • CURDATE(): Returns the current date.
  • CURTIME(): Returns the current time.
  • DATE(): Extracts the date part of a date or datetime expression.
  • TIME(): Extracts the time part of a datetime expression.
  • YEAR(): Extracts the year from a date or datetime expression.
  • MONTH(): Extracts the month from a date or datetime expression.
  • DAY(): Extracts the day from a date or datetime expression.
  • HOUR(): Extracts the hour from a datetime expression.
  • MINUTE(): Extracts the minute from a datetime expression.
  • SECOND(): Extracts the second from a datetime expression.
  • DATE_ADD(): Adds a specified time interval to a date.
  • DATE_SUB(): Subtracts a specified time interval from a date.
  • DATEDIFF(): Returns the number of days between two dates.
  • TIMESTAMPDIFF(): Returns the difference between two datetime expressions in a specified unit (e.g., seconds, minutes, hours, days).
  • TIMESTAMPADD(): Adds a specified time interval to a datetime expression.
  • EXTRACT(): Extracts a specified part (e.g., year, month, day) from a date or datetime expression.
  • FORMAT(): Formats a date or datetime expression as a string according to a specified format.

FORMAT() Function

The FORMAT() function can be used to format both numeric and date/datetime values. When used with numeric values, it formats the number with a specified number of decimal places and optional locale. When used with date/datetime values, it formats the date or datetime according to a specified format string.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Format a number with 2 decimal places
SELECT FORMAT(12345.6789, 2); -- Output: '12,345.68'

-- Format a date with a specific format
SELECT FORMAT('2023-10-15 14:30:00', '%Y-%m-%d %H:%i:%s'); -- Output: '2023-10-15 14:30:00'
SELECT FORMAT('2023-10-15 14:30:00', '%M %d, %Y'); -- Output: 'October 15, 2023'
SELECT FORMAT('2023-10-15 14:30:00', '%W, %M %d, %Y'); -- Output: 'Sunday, October 15, 2023'

-- %e: Day of the month (1-31)
SELECT FORMAT('2023-10-05', '%e'); -- Output: '5'
-- %c: Month (1-12)
SELECT FORMAT('2023-10-15', '%c'); -- Output: '10'
-- %Y: Year (4 digits)
SELECT FORMAT('2023-10-15', '%Y'); -- Output: '2023
-- %H: Hour (00-23)
SELECT FORMAT('2023-10-15 14:30:00', '%H'); -- Output: '14'
-- %i: Minute (00-59)
SELECT FORMAT('2023-10-15 14:30:00', '%i'); -- Output: '30'
-- %s: Second (00-59)
SELECT FORMAT('2023-10-15 14:30:00', '%s'); -- Output: '00'
-- %M: Full month name
SELECT FORMAT('2023-10-15', '%M'); -- Output: 'October'

EXTRACT() Function

The EXTRACT() function is used to extract a specified part (e.g., year, month, day) from a date or datetime expression. It takes two arguments: the part to extract and the date/datetime expression.

1
2
3
4
5
6
7
8
9
10
11
12
-- Extract the year from a date
SELECT EXTRACT(YEAR FROM '2023-10-15'); -- Output: 2023
-- Extract the month from a date
SELECT EXTRACT(MONTH FROM '2023-10-15'); -- Output: 10
-- Extract the day from a date
SELECT EXTRACT(DAY FROM '2023-10-15'); -- Output: 15
-- Extract the hour from a datetime
SELECT EXTRACT(HOUR FROM '2023-10-15 14:30:00'); -- Output: 14
-- Extract the minute from a datetime
SELECT EXTRACT(MINUTE FROM '2023-10-15 14:30:00'); -- Output: 30
-- Extract the second from a datetime
SELECT EXTRACT(SECOND FROM '2023-10-15 14:30:00'); -- Output: 0

Encryption Functions

MySQL provides several functions for encrypting and decrypting data. These functions can be used to enhance the security of sensitive information stored in the database. Some common encryption functions include:

  • AES_ENCRYPT(): Encrypts a string using the AES algorithm.
  • AES_DECRYPT(): Decrypts a string encrypted with the AES algorithm.
  • MD5(): Returns the MD5 hash of a string.
  • SHA1(): Returns the SHA-1 hash of a string.
  • SHA2(): Returns the SHA-2 hash of a string with a specified bit length (e.g., 256, 512).
  • PASSWORD(): Returns a hashed version of a string using the MySQL password hashing algorithm.
  • ENCRYPT(): Encrypts a string using the Unix crypt() function.
  • DECRYPT(): Decrypts a string encrypted with the Unix crypt() function.
  • OLD_PASSWORD(): Returns a hashed version of a string using the old MySQL password hashing algorithm (not recommended for new applications).
1
2
3
4
5
6
7
8
9
10
-- Encrypt a string using AES
SELECT AES_ENCRYPT('my_secret_data', 'encryption_key'); -- Output: Encrypted binary data
-- Decrypt the previously encrypted string
SELECT AES_DECRYPT(AES_ENCRYPT('my_secret_data', 'encryption_key'), 'encryption_key'); -- Output: 'my_secret_data'
-- Get the MD5 hash of a string
SELECT MD5('my_secret_data'); -- Output: '5f4dcc3b5aa765d61d8327deb882cf99'
-- Get the SHA-1 hash of a string
SELECT SHA1('my_secret_data'); -- Output: '2bb80d537b1da3e38bd30361aa855686bde0ba'
-- Get the SHA-256 hash of a string using SHA2 function
SELECT SHA2('my_secret_data', 256); -- Output: '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d154'

Information Functions

Information functions are used to retrieve metadata about the database, tables, columns, and other database objects. Some common information functions include:

  • DATABASE(): Returns the name of the current database.
  • USER(): Returns the current MySQL user.
  • VERSION(): Returns the MySQL server version.
1
2
3
4
5
6
-- Get the name of the current database
SELECT DATABASE(); -- Output: 'my_database'
-- Get the current MySQL user
SELECT USER(); -- Output: 'username@hostname'
-- Get the MySQL server version
SELECT VERSION(); -- Output: '8.0.27'

Conditional Functions

Conditional functions are used to perform conditional logic in SQL queries. Some common conditional functions include:

  • IF(): Returns one value if a condition is true and another value if it is false.
  • IFNULL(): Returns a specified value if the expression is NULL, otherwise returns the expression.
  • NULLIF(): Returns NULL if the two expressions are equal, otherwise returns the first expression.
  • CASE: Evaluates a list of conditions and returns one of multiple possible result expressions.
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Use IF function to return 'Adult' or 'Minor' based on age
SELECT name, age, IF(age >= 18, 'Adult', 'Minor') AS status FROM users;
-- Use IFNULL function to return 'N/A' if email is NULL
SELECT name, IFNULL(email, 'N/A') AS email FROM users;
-- Use NULLIF function to return NULL if age is 0
SELECT name, NULLIF(age, 0) AS age FROM users;
-- Use CASE statement to categorize age groups
SELECT name, age, CASE 
    WHEN age < 13 THEN 'Child'
    WHEN age >= 13 AND age < 18 THEN 'Teenager'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
END AS age_group FROM users;

Aggregate Functions

Aggregate functions are used to perform calculations on a set of values and return a single value. Some common aggregate functions include:

  • COUNT(): Returns the number of rows that match a specified condition.
  • SUM(): Returns the total sum of a numeric column.
  • AVG(): Returns the average value of a numeric column.
  • MIN(): Returns the minimum value of a column.
  • MAX(): Returns the maximum value of a column.
1
2
3
4
5
6
7
8
9
10
-- Get the total number of users
SELECT COUNT(*) FROM users; -- Output: Total number of rows in the users table
-- Get the sum of ages of all users
SELECT SUM(age) FROM users; -- Output: Total sum of ages
-- Get the average age of users
SELECT AVG(age) FROM users; -- Output: Average age
-- Get the minimum age of users
SELECT MIN(age) FROM users; -- Output: Minimum age
-- Get the maximum age of users
SELECT MAX(age) FROM users; -- Output: Maximum age

Window Functions

Window functions are used to perform calculations across a set of table rows that are related to the current row. They allow you to perform aggregate calculations without collapsing the result set. Some common window functions include:

  • ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition of a result set.
  • RANK(): Assigns a rank to each row within a partition of a result set, with gaps in ranking values when there are ties.
  • DENSE_RANK(): Similar to RANK() but without gaps in ranking values
  • LAG(): Provides access to a row at a specified physical offset before the current row.
  • LEAD(): Provides access to a row at a specified physical offset after the current row.
  • FIRST_VALUE(): Returns the first value in an ordered set of values.
  • LAST_VALUE(): Returns the last value in an ordered set of values.
1
2
3
4
5
function_name(arguments) over (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [ROWS BETWEEN frame_start AND frame_end]
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Use ROW_NUMBER to assign a unique sequential integer to each user
SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num FROM users;
SELECT name, age, Row_NUMBER() OVER (PARTITION BY age GROUP BY age ORDER BY name) AS row_num FROM users;
SELECT name, age, Row_NUMBER() OVER (PARTITION BY age GROUP BY age ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS row_num FROM users;
-- Use RANK to assign a rank to each user based on age
SELECT name, age, RANK() OVER ( ORDER BY age DESC) AS rank FROM users;
-- Use LAG to access the age of the previous user in the result set
SELECT name, age, LAG(age) OVER (ORDER BY age) AS previous_age FROM users;
-- Use LEAD to access the age of the next user in the result set
SELECT name, age, LEAD(age) OVER (ORDER BY age) AS next_age FROM users;
-- Use FIRST_VALUE to get the age of the youngest user
SELECT name, age, FIRST_VALUE(age) OVER (ORDER BY age) AS youngest_age FROM users;
-- Use LAST_VALUE to get the age of the oldest user
SELECT name, age, LAST_VALUE(age) OVER (ORDER BY age) AS oldest_age FROM users;

SELECT name, age, FIRST_VALUE(age) OVER w AS youngest_age FROM users
WINDOW w AS (ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);