ECA 236

1. ECA 236 Open Source Server Side Scripting MySQL Functions Open Source Server Side Scripting

2. MySQL functions • MySQL has many functions available to alter or format the data stored in tables • when using a MySQL function specify a column name • case insensitive • no space between function name and parentheses SELECT FUNCTION( column_name ) FROM table_name; SELECT column1, FUNCTION( column2 ), column3 FROM table_name; Open Source Server Side Scripting

3. MySQL text functions Open Source Server Side Scripting

4. MySQL text functions cont … Open Source Server Side Scripting

5. MySQL text functions cont … • CONCAT( ) • concatenates arguments • no default separator • CONCAT_WS( ) • CONCAT With Separator • first argument is designated separator SELECT CONCAT( last_name, ‘, ‘, first_name ) FROM users; SELECT CONCAT_WS( ‘ ‘, first_name, last_name) FROM users ORDER BY last_name; Open Source Server Side Scripting

6. alias • alias • temporary renaming of table or column – allows for quick reference • used with keyword AS • case sensitive • once defined, query must use alias SELECT CONCAT(last_name, ', ', first_name) AS fn FROM users ORDER BY fn; Open Source Server Side Scripting

7. MySQL text functions cont … • CONV( ) • converts numbers between different number bases • 3 arguments • number to convert • base to convert from • base to convert to SELECT CONV( ‘ffffff’, 16, 10 ); Open Source Server Side Scripting

8. MySQL text functions cont … • LEFT( ) • returns the designated number of characters from the left of the value • RIGHT( ) • returns the designated number of characters from the right of the value SELECT LEFT( last_name, 3 ) FROM users ORDER BY last_name; SELECT RIGHT( email, 4 ) FROM users; Open Source Server Side Scripting

9. MySQL text functions cont … • TRIM( ) • strips all white space from the beginning and end of the value • similar functions include LTRIM( ) and RTRIM( ) to trim the white space from only beginning or end • TRIM( ) can also be used to trim specified prefixes or suffixes SELECT TRIM( email ) FROM users; SELECT TRIM( LEADING ‘@’ FROM ‘@@@mushroom’ );SELECT TRIM( TRAILING ‘room’ FROM ‘mushroom’ ); Open Source Server Side Scripting

10. MySQL text functions cont … • UPPER( ) • converts the value to uppercase • LOWER( ) • converts the value to lowercase SELECT UPPER( last_name ) FROM users; SELECT LOWER( last_name ) FROM users; Open Source Server Side Scripting

11. MySQL text functions cont … • LPAD( ) • returns the value, left padded with the designated string, the designated number of characters • RPAD( ) • returns the value, right padded with the designated string, the designated number of characters SELECT LPAD( last_name, 16, ‘*’ ) FROM users; SELECT RPAD( last_name, 16, ‘*’ ) FROM users; Open Source Server Side Scripting

12. MySQL text functions cont … • SUBSTRING( ) • returns a substring of the value, the designated number of characters long, beginning at the designated position • LENGTH( ) • Returns the length of the column value SELECT SUBSTRING( last_name, 1, 3 ) AS ln FROM users ORDER BY ln; SELECT LENGTH( first_name ), LENGTH( last_name ) FROM users ORDER BY last_name; Open Source Server Side Scripting

13. MySQL numeric functions Open Source Server Side Scripting

14. MySQL numeric functions cont … Open Source Server Side Scripting

15. MySQL numeric functions cont … • ABS( ) • returns the absolute value • CEILING( ) • returns the next highest integer, based on the value SELECT ABS( -32 ); SELECT CEILING( 3.0000004 ); Open Source Server Side Scripting

16. MySQL numeric functions cont … • FLOOR( ) • returns the integer value of a number • FORMAT( ) • returns a number formatted with the designated number of decimal places, and commas every three spaces SELECT FLOOR( 3.0000004 ); SELECT FORMAT( 369635, 2 ); Open Source Server Side Scripting

17. MySQL numeric functions cont … • MOD( ) • returns modulus • RAND( ) • returns a random number between 0 and 1.0 SELECT MOD( 7, 4 ); SELECT RAND( ); Open Source Server Side Scripting

18. MySQL numeric functions cont … • ROUND( ) • returns the number rounded to the designated decimal places • SIGN( ) • returns a –1, 0, or 1 depending upon whether the number is negative, zero, or positive SELECT ROUND( 3.127458, 2 ); SELECT SIGN( -47 );SELECT SIGN( 0 ); SELECT SIGN( 29 ); Open Source Server Side Scripting

19. MySQL numeric functions cont … • SQRT( ) • calculates the square root of a number • TRUNCATE( ) • returns a number, truncated to the designated number of decimals SELECT SQRT( 64 ); SELECT TRUNCATE( 3.127458, 2 ); Open Source Server Side Scripting

20. MySQL numeric functions cont … • LEAST( ) • returns the smallest of at least 2 supplied arguments • GREATEST( ) • returns the largest of at least 2 supplied arguments SELECT LEAST( 2, 13, 8, 9); SELECT GREATEST( 2, 13, 8, 9); Open Source Server Side Scripting

21. MySQL date/time functions Open Source Server Side Scripting

22. MySQL date/time functions cont … Open Source Server Side Scripting

23. MySQL date/time functions cont … • DATE( ) • extracts the date part of a DATE or DATETIME expression • available in MySQL version 4.1.1 • TIME( ) • extracts the time part of a TIME or DATETIME expression • available in MySQL version 4.1.1 SELECT DATE( registration_date ) FROM users; SELECT TIME( registration_date ) FROM users; Open Source Server Side Scripting

24. MySQL date/time functions cont … • HOUR( ) • returns the hour of a value, in the range 0 – 23 • MINUTE( ) • returns the minute of a value, in the range 0 – 59 • SECOND( ) • returns the second of a value, in the range 0 – 59 SELECT HOUR( registration_date ) FROM users; SELECT MINUTE( registration_date ) FROM users; SELECT SECOND( registration_date ) FROM users; Open Source Server Side Scripting

25. MySQL date/time functions cont … • DAYNAME( ) • returns the name of the weekday of a value • DAYOFMONTH( ) • returns the day of the month of a value, in the range 1 – 31 • MONTHNAME( ) • returns the name of the month of a value SELECT DAYNAME( registration_date ) FROM users; SELECT DAYOFMONTH( registration_date ) FROM users; SELECT MONTHNAME( registration_date ) FROM users; Open Source Server Side Scripting

26. MySQL date/time functions cont … • MONTH( ) • returns the month of a value, in the range 1 – 12 • YEAR( ) • returns the year of a value in the range 1000 – 9999 SELECT MONTH( registration_date ) FROM users; SELECT YEAR( registration_date ) FROM users; Open Source Server Side Scripting

27. MySQL date/time functions cont … • ADDDATE( ) • used to perform calculations on date values • synonymous with DATE_ADD( ) • prototype : • returns the value of date after x units of type have been added ADDDATE( date, INTERVAL x type ) Open Source Server Side Scripting

28. MySQL date/time functions cont … • ADDDATE( ) • types include Open Source Server Side Scripting

29. MySQL date/time functions cont … • ADDDATE( ) • add 2 hours to a date/time value • add 3 weeks to a date/time value • add 18 months to a date/time value SELECT ADDDATE( registration_date, INTERVAL 2 HOUR ) FROM users; SELECT ADDDATE( registration_date, INTERVAL 21 DAY ) FROM users; SELECT ADDDATE( registration_date, INTERVAL ‘1-6’ YEAR_MONTH ) FROM users; Open Source Server Side Scripting

30. MySQL date/time functions cont … • SUBDATE( ) • similar to ADDDATE( ), but subtracts from value • CURDATE( ) • returns the current date • CURTIME( ) • returns the current time SELECT SUBDATE( registration_date, INTERVAL 1 DAY ) FROM users; SELECT CURDATE( ); SELECT CURTIME( ); Open Source Server Side Scripting

31. MySQL date/time functions cont … • NOW( ) • returns the current date and time • UNIX_TIMESTAMP( ) • returns the number of seconds since the Unix Epoch SELECT NOW( ); SELECT UNIX_TIMESTAMP( ‘2001-09-08 21:46:40’ ); Open Source Server Side Scripting