Built-Functions in MySqll (Advance Database System)

A report about built-in functions in MySql

1. ADVANCE DATABASE SYSTEM Functions (Built-In) Gonzales, Khurt Dhan V. Lledo, Denise Claire O. Macellones, Angelo F.

2. OVERVIEW Numeric functions String functions Date functions Calculating Dates and Times The IFNULL and COALESCE functions IF functions Case operator

3. What is Built-in Functions?

4. TYPES OF SQL BUILT-IN FUNCTIONS Numeric functions String functions Date functions

5. NUMERIC FUNCTIONS

6. NUMERIC FUNCTIONS ABS ROUND CEIL FLOOR MOD POWER SQRT GREATEST LEAST RAND

7. ABS SYNTAX: EXAMPLE: SELECT ABS ( number ); SELECT ABS (-15); The ABS() function returns the absolute value of a number.

8. ROUND SYNTAX: EXAMPLE: SELECT ROUND ( number, decimal, operations ); SELECT ROUND (123.4567, 2 ); The ROUND() function rounds a number to a specified number of decimal places. The ABS() function returns the absolute value of a number.

9. CEIL SYNTAX: EXAMPLE: SELECT CEIL ( number ); SELECT CEIL (6.2); SELECT CEILING ( number ); SELECT CEILING (6.2); The CEILING() function returns the smallest integer value that is larger than or equal to a number.

10. FLOOR SYNTAX: EXAMPLE: SELECT FLOOR ( number ); SELECT FLOOR (5.8); The FLOOR() function returns the largest integer value that is smaller than or equal to a number.

11. MOD SYNTAX: EXAMPLE: SELECT MOD (dividend, divisor); SELECT MOD (10, 3); MOD() is a mathematical function that calculates the remainder of the division of two numbers.

12. POWER SYNTAX: EXAMPLE: SELECT POWER ( number, power ); SELECT POWER (2, 3); The POWER() function returns the value of a number raised to the power of another number.

13. SQRT SYNTAX: EXAMPLE: SELECT SQRT ( number ); SELECT SQRT ( 16 ); The SQRT() function returns the square root of a number.

14. GREATEST SYNTAX: EXAMPLE: SELECT GREATEST (column 1, column 2,...); SELECT GREATEST (10, 20, 5, 30, 15); The GREATEST() it returns the greatest value in a list of expressions

15. LEAST SYNTAX: EXAMPLE: SELECT LEAST ( column 1, column 2,... ); SELECT LEAST (10, 20, 5, 30, 15); The LEAST() it returns the smallest value in a list of expressions.

16. RAND SYNTAX: EXAMPLE: SELECT RAND (); SELECT RAND (); The RAND() function returns a random number between 0 (inclusive) and 1 (exclusive).

17. STRING FUNCTIONS

18. STRING FUNCTIONS CHAR_LENGTH CONCAT UPPER | UCASE LOWER | LCASE SUBSTRING | SUBSTR TRIM REPLACE REVERSE LEFT | RIGHT

19. CHAR_LENGTH SYNTAX: EXAMPLE: SELECT CHAR_LENGTH (); SELECT CHAR_LENGTH (“Functions”); The CHAR_LENGTH() function return the length of a string (in characters). Note : This function is equal to the CHARACTER_LENGTH() function.

20. CONCAT SYNTAX: EXAMPLE: SELECT CONCAT (); SELECT CONCAT (“ Sql ”, “is”, “ fun!”) AS ConcatenatedString ; The CONCAT() function adds two or more strings together.

21. CONCAT SYNTAX: EXAMPLE: SELECT CONCAT (); SELECT CONCAT (“ Sql ”, “is”, “fun!”) AS ConcatenatedString ; The CONCAT() function adds two or more expressions together.

22. LENGTH SYNTAX: EXAMPLE: SELECT LENGTH (); SELECT LENGTH (); The LENGTH() is used to find the length of a word.

23. UPPER | UCASE SYNTAX: EXAMPLE: SELECT UPPER (); SELECT UPPER (); The UPPER() function converts a string to upper-case.

24. LOWER | LCASE SYNTAX: EXAMPLE: SELECT LOWER (); SELECT LOWER (); The LOWER() function converts a string to lower-case.

25. SUBSTRING | SUBSTR SYNTAX: EXAMPLE: SELECT SUBSTRING (string, start, length); SELECT SUBSTRING (“Hello, World!”, 1, 4) AS ExtractString ; The SUBSTRING() function extracts some characters from a string.

26. TRIM SYNTAX: EXAMPLE: SELECT TRIM (); SELECT TRIM (' Hello, World! ') AS TrimmedString ; The TRIM() is used to cut the given symbol from the string

27. REPLACE SYNTAX: EXAMPLE: SELECT REPLACE (); SELECT REPLACE ("Hello, Chris!", "Chris", "John"); The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

28. REVERSE SYNTAX: EXAMPLE: SELECT REVERSE (); SELECT REVERSE (“Hello, everyone”); The REVERSE() function reverses a string and returns the result.

29. LEFT SYNTAX: EXAMPLE: SELECT LEFT ( string, number_of_char ); SELECT LEFT (“SQL is fun”, 3) AS ExtractString ; The LEFT() function extracts a number of characters from a string (starting from left).

30. RIGHT SYNTAX: EXAMPLE: SELECT RIGHT ( string, number_of_char ); SELECT RIGHT (“SQL is fun”, 3) AS ExtractString ; The RIGHT() function extracts a number of characters from a string (starting from right).

31. DATE FUNCTIONS

32. DATE FUNCTIONS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP

33. DATE FUNCTIONS CURRENT_DATE Returns current date CURRENT_TIME Returns current time CURRENT_TIMESTAMP Returns current timestamp

34. CURRENT_DATE / CURDATE() SYNTAX: SELECT CURRENT_DATE (); or SELECT CURDATE (); The CURRENT_DATE() function returns the current date. Note : The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric).

35. SYNTAX: SELECT CURRENT_TIME (); or SELECT CURTIME (); The CURRENT_TIME() function returns the current time. Note : The time is returned as "HH-MM-SS" (string) or as HHMMSS.uuuuuu (numeric). CURRENT_TIME/ CURTIME()

36. The CURRENT_TIMESTAMP() function returns the current date and time. Note : The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric). CURRENT_TIMESTAMP SYNTAX: SELECT CURRENT_TIMESTAMP ();

37. TIMESTAMP 2024-09-17 10:30:00 2024-09-17 10:30:00 DATE TIME

38. DATE AND TIME FUNCTIONS DATE Extracts the date part from a datetime expression (timestamp) TIME Extracts the time part from a datetime expression (timestamp)

39. DATE 2024-09-17 2024 17 YEAR DAY 09 MONTH

40. DATE AND TIME FUNCTIONS Extract the year from date or date or datetime expression YEAR MONTH DAY Extract the month from date or date or datetime expression Extract the day from date or date or datetime expression

41. TIME 12:00:00 12 00 HOUR SECOND 00 MINUTE

42. DATE AND TIME FUNCTIONS Extract the hour from time or datetime expression HOUR MINUTE SECOND Extract the minute from time or datetime expression Extract the second from time or datetime expression

43. CALCULATING DATES AND TIMES

44. DATE_ADD The DATE_ADD() function adds a time/date interval to a date and then returns the date. SYNTAX: SELECT DATEADD (date, INTERVAL value addunit );

45. DATE_SUB SYNTAX: SELECT DATE_SUB (date, INTERVAL value interval); The DATE_SUB() function subtracts a time/date interval from a date and then returns the date.

46. DATEDIFF SYNTAX: SELECT DATEDIFF (date1, date2); The DATEDIFF() function returns the number of days between two date values.