20 Common MySQL Functions and Their Usage

MySQL functions are reusable SQL statements or procedures that return values, greatly improving code readability and maintainability. This guide presents twenty frequently used MySQL functions with descriptions and concrete examples.

1. CONCAT – concatenates multiple strings. SELECT CONCAT('Hello', ' ', 'World'); Result: 'Hello World' 2. UPPER – converts a string to uppercase. SELECT UPPER('hello world'); Result: 'HELLO WORLD' 3. LOWER – converts a string to lowercase. SELECT LOWER('HELLO WORLD'); Result: 'hello world' 4. SUBSTRING – extracts a substring from a string. SELECT SUBSTRING('Hello World', 1, 5); Result: 'Hello' 5. TRIM – removes leading and trailing spaces. SELECT TRIM(' Hello World '); Result: 'Hello World' 6. LENGTH – returns the length of a string. SELECT LENGTH('Hello World'); Result: 11 7. ROUND – rounds a number to the nearest integer. SELECT ROUND(3.14159); Result: 3 8. FLOOR – rounds a number down. SELECT FLOOR(3.14159); Result: 3 9. CEILING – rounds a number up. SELECT CEILING(3.14159); Result: 4 10. RAND – generates a random number between 0 and 1. SELECT RAND(); Result: a value such as 0.531752817766 11. NOW – returns the current date and time. SELECT NOW(); Result: '2022-08-08 16:32:28' 12. DATE_FORMAT – formats a date according to a pattern. SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); Result: '2022-08-08 16:32:28' 13. YEAR – extracts the year from a date. SELECT YEAR('2022-08-08'); Result: 2022 14. MONTH – extracts the month from a date. SELECT MONTH('2022-08-08'); Result: 8 15. DAY – extracts the day of the month from a date. SELECT DAY('2022-08-08'); Result: 8 16. HOUR – extracts the hour from a time. SELECT HOUR(NOW()); Result: 16 17. MINUTE – extracts the minute from a time. SELECT MINUTE(NOW()); Result: 32 18. SECOND – extracts the second from a time. SELECT SECOND(NOW()); Result: 28 19. SUM – calculates the total of a numeric column. SELECT SUM(price) FROM products; Result: 1162.57 20. COUNT – counts rows in a result set. SELECT COUNT(*) FROM orders; Result: 54 By mastering these functions, developers can perform efficient data manipulation, aggregation, and formatting tasks within MySQL.