Preformatting the results returned by a query makes your data more usable and can cut down on the amount of programming interface required. To do so, you make use of MySQL's built-in functions, first introduced in Chapter 6, "MySQL Functions." Of course, you can also use functions in your clauses to limit what records are returned, to group them, and more. Table B.10 shows the functions used with strings. Table B.11 has most, but not all, of the number-based functions. Table B.12 lists date-related functions. Table B.13 has the formatting parameters for the DATE_FORMAT() and TIME_FORMAT() functions. Table B.14 covers the aggregate or grouping functions. Table B.15 discusses most of the encryption functions and Table B.16 is the catchall for miscellaneous functions. Table B.10. These functions are used for manipulating string values, in columns or otherwise.Text Functions |
---|
Function and Usage | Purpose |
---|
FIND_IN_SET(string, set) | Returns a positive number if string is found in set; returns 0 otherwise. | LEFT(string, x) | Returns the leftmost x characters from a string. | LENGTH(string) | Returns the length of the string. | LOCATE(substring, string) | Returns the first instance of substring in string, if applicable. | LOWER(string) | Turns the string into an all-lowercase format. | LTRIM(string) | Trims excess spaces from the beginning of the string. | REPLACE(string, find, replace) | Returns the string with every instance of find substituted by replace. | RIGHT(string, x) | Returns the rightmost x characters from a string. | RTRIM(string) | Trims excess spaces from the end of the stored string. | STRCMP(string1, string2) | Returns 0 if the strings are the same, 1 or 1 otherwise. | SUBSTRING(string, start, length) | Returns length characters from string beginning with start (indexed from 1). | trIM(string) | Trims excess spaces from the beginning and end of the string. | UPPER(string) | Capitalizes the entire string. |
Table B.11. Here are some of the numeric functions MySQL has, excluding the trigonometric and more esoteric ones.Numeric Functions |
---|
Function and Usage | Purpose |
---|
ABS(num) | Returns the absolute value of num. | CEILING(num) | Returns the next-highest integer based upon the value of num. | FLOOR(num) | Returns the integer value of num. | FORMAT(num, y) | Returns num formatted as a number with y decimal places and commas inserted every three spaces. | GREATEST(num1, num2, num3...) | Returns the greatest value from a list. | LEAST(num1, num2, num3...) | Returns the smallest value from a list. | MOD(x, y) | Returns the remainder of dividing x by y (either or both can be a column). | POW(x, y) | Returns the value of x to the y power. | RAND() | Returns a random number between 0 and 1.0. | ROUND(x, y) | Returns the number x rounded to y decimal places. | SIGN(num) | Returns a value indicating whether num is negative (1), zero (0), or positive (+1). | SQRT(num) | Calculates the square root of num. |
Table B.12. MySQL uses several different functions for working with dates and times in your databases. In the usage examples, dt could represent a date, a time, or a datetime.Date and Time Functions |
---|
Function and Usage | Purpose |
---|
HOUR(dt) | Returns just the hour value of dt. | MINUTE(dt) | Returns just the minute value of dt. | SECOND(dt) | Returns just the second value of dt. | DATE(dt) | Returns just the date value of dt. | DAYNAME(dt) | Returns the name of the day of dt. | DAYOFMONTH(dt) | Returns just the numerical day of dt. | MONTHNAME(dt) | Returns the name of the month of dt. | MONTH(dt) | Returns just the numerical month value of dt. | YEAR(dt) | Returns just the year value of dt. | DATE_ADD(dt, INTERVAL x type) | Returns the value of x units added to dt. | DATE_SUB(dt, INTERVAL x type) | Returns the value of x units subtracted from dt. | ADDTIME(dt, t) | Returns the value of t time added to dt. | SUBTIME(dt, t) | Returns the value of t time subtracted from dt. | DATEDIFF(dt, dt) | Returns the number of days between the two dates. | TIMEDIFF(dt, dt) | Returns the time difference between the dates or dates and times. | CONVERT_TZ(dt, from_zone, to_zone) | Converts dt from one time zone to another. | CURDATE() | Returns the current date. | CURTIME() | Returns the current time. | NOW() | Returns the current date and time. | UNIX_TIMESTAMP(dt) | Returns the number of seconds since the epoch or since the date specified. |
Table B.13. The DATE_FORMAT() and TIME_FORMAT() functions make use of these special characters to format a date or time.DATE_FORMAT() and TIME_FORMAT() Parameters |
---|
Term | Meaning | Example |
---|
%e | Day of the month | 131 | %d | Day of the month, two digit | 0131 | %D | Day with suffix | 1st31st | %W | Weekday name | SundaySaturday | %a | Abbreviated weekday name | SunSat | %c | Month number | 112 | %m | Month number, two digit | 0112 | %M | Month name | JanuaryDecember | %b | Month name, abbreviated | JanDec | %Y | Year | 2002 | %y | Year | 02 | %l | Hour | 112 | %h | Hour, two-digit | 0112 | %k | Hour, 24-hour clock | 023 | %H | Hour, 24-hour clock, two-digit | 0023 | %i | Minutes | 0059 | %S | Seconds | 0059 | %r | Time | 8:17:02 PM | %T | Time, 24-hour clock | 20:17:02 | %p | AM or PM | AM or PM |
Table B.14. The grouping functions are frequently tied to a GROUP BY clause to aggregate values in a column.Grouping Functions |
---|
Function and Usage | Purpose |
---|
AVG(column) | Returns the average of the values of the column. | COUNT(column) | Counts the number of rows. | COUNT(DISTINCT column) | Counts the number of distinct column values. | GROUP_CONCAT(values) | Returns a concatenation of the grouped values. | MIN(column) | Returns the smallest value from the column. | MAX(column) | Returns the largest value from the column. | SUM(column) | Returns the sum of all of the values in the column. |
Table B.15. Different encryption functions are available as of new releases of MySQL, so know what version you are using!Encryption Functions |
---|
Function | Version | Notes |
---|
MD5() | 3.23.2 | Returns a 32-digit hash. | SHA1() | 4.0.2 | Returns a 40-digit hash. | AES_ENCRYPT() | 4.0.2 | Encrypts data using AES algorithm. | AES_DECRYPT () | 4.0.2 | Decrypts AES_ENCRYPT() data. | ENCODE() | 3.x | Older encryption function. | DECODE() | 3.x | Decrypts ENCODE() data. | DES_ENCRYPT() | 4.0.1 | Encrypts data using DES algorithm, requires SSL. | DES_DECRYPT () | 4.0.1 | Decrypts DES_ENCRYPT() data. | ENCRYPT() | 3.x | May not be available on Windows; no decryption possible. | PASSWORD() | 3.x | Used by the mysql database; do not use yourself. |
Table B.16. These various functions are for everything from encryption to concatenation.Other Functions |
---|
Function and Usage | Purpose |
---|
CONAT(column1, ' - ', column2) | Combines the elements in parentheses into one string. | CONCAT_WS(' - ', column1, column2) | Combines the elements with the one common separator. | DATABASE() | Returns the name of the database currently being used. | LAST_INSERT_ID() | Returns the previous auto-incremented value. | USER() | Returns the name of the user of the current session. |
Most every function can be applied either to the value retrieved from a column or to a literal one: SELECT ROUND (3.142857, 2) SELECT ROUND (columnname) FROM tablename |