MySQL functions look similar to mathematical functions. The function itself has a name, and its transformation is performed on a target: FUNCTION(column). The basic syntax is:
SELECT FUNCTION(column) FROM table_name
You can select multiple columns, and perform the function on only one:
SELECT title, FUNCTION(artist) FROM albums
| Text Functions | ||
| Name | Usage | Purpose |
| LENGTH() | LENGTH(column) | Returns the length of the string in column. |
| LEFT() | LEFT(column, x) | Returns the left x characters from column. |
| RIGHT() | RIGHT(column, x) | Returns the right x characters from column. |
| TRIM() | TRIM(column) | Removes spaces from the beginning and end of column. |
| UPPER() | UPPER(column) | Capitalizes the string in column. |
| LOWER() | LOWER(column) | Renders the string in column in all lower-case |
| SUBSTRING() | SUBSTRING(column, start, length) |
Returns from column the substring beginning start number of characters from the beginning, of length from that point. |
| CONCAT() | CONCAT(column1, column2 [, column3]) |
Returns from column the substring beginning start number of characters from the beginning, of length from that point. |
•Note that the strings are numbered starting with 1, not with 0.
•There can be no spaces between the function name and the first parenthesis.
Select the first 3 digits of a phone number formatted like 505-555-6666:
SELECT LEFT(phone_number, 3) FROM clients; #Returns 505
Select the first 3 digits of a phone number formatted like (505) 555-6666:
SELECT RIGHT(LEFT(phone_number, 4), 3)FROM clients; #Returns 505
Select the first 3 digits of a phone number formatted like (505) 555-6666:
SELECT SUBSTRING(phone_number, 2, 3)FROM clients; #Returns 505
Select the longest album name from the list:
SELECT album_title FROM albums ORDER BY LENGTH(album_title) DESC LIMIT 1;
Concatenate column values with the CONCAT() function. Its basic syntax is:
CONCAT(column, column2)
You can include strings (including spaces) enclosed in single quotes:
CONCAT(last_name, ‘, ‘, first_name)
Once you’ve concatenated values in a query, you’ll need a way to refer to the new string. Use AS:
SELECT CONCAT(column, column2) AS new_value FROM table_name;
For example:
SELECT CONCAT(last_name, ‘, ‘, first_name) AS artist_name FROM artists;
| Numeric Functions | ||
| Name | Usage | Purpose |
| ABS() | ABS(column) | Returns the absolute value of column. |
| CEILING() | CEILING(column) | Returns the next-highest value from a column that contains a decimal. For instance, the value 1.25 will be returned as 2. |
| FLOOR() | FLOOR(column) | Returns the integer value from column. 1.25 will be returned as 1. |
| FORMAT() | FORMAT(column, x) | Returns column formatted with x decimal places, and with commas every three spaces: FORMAT(column, 2) returns 1,234.56 |
| MOD() | MOD(x, y) | Returns the modulus (remainder) of dividing x by y. |
| RAND() | RAND(column) | Returns a random number, which will be a decimal between 0 and 1.0 |
| ROUND() | ROUND(x, y) |
Returns x rounded to y decimal places. |
| SIGN() | SIGN(column) |
Returns -1, 0 or +1, indicating the sign of column. |
| SQRT() | SQRT(column) |
Returns the square root of column. |
Perform simple arithmetic using +, -, * and / :
SELECT invoice_amount*discount FROM invoices;
| Date and Time Functions | ||
| Name | Usage | Purpose |
| HOUR() | HOUR(column) | Returns only the hour of column. |
| MINUTE() | MINUTE(column) | Returns only the minute of column. |
| SECOND() | SECOND(column) | Returns only the second of column. |
| DAYNAME() | DAYNAME(column) | Returns the day name (e.g Monday) of column. |
| DAYOFMONTH() | DAYOFMONTH(column) | Returns the numeric day of the month (e.g. 25) of column. |
| MONTHNAME() | MONTHNAME(column) | Returns the month name of column (e.g. March). |
| MONTH() | MONTH(column) |
Returns numeric month of column (e.g. 10). |
| YEAR() | YEAR(column) |
Returns the numeric year of column. |
| ADDDATE() | ADDDATE(column, INTERVAL x type) | Returns the value of column plus x intervals of type: SECOND, MINUTE, HOUR, DAY, MONTH or YEAR. |
| SUBDATE() | SUBDATE(column, INTERVAL x type) | Returns the value of column minus x intervals of type: SECOND, MINUTE, HOUR, DAY, MONTH or YEAR. |
| CURDATE() | CURDATE() | Returns the current date only. |
| CURTIME() | CURTIME() | Returns the current time only. |
| NOW() | NOW() | Returns the current date and time. |
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP([date]) |
Returns the number of seconds since the epoch or since the date specified [optional]. |
Syntax for ADDDATE and SUBDATE:
ADDDATE(‘2002-1-1’, INTERVAL 30 DAY)
DO NOT FORGET THAT ADDDATE HAS THREE ( 3 ) Ds.
See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html for more details.
• Do not forget the %
• Date values can indeed be “0” : 2005-00-00
• Any other characters will go into the result without interpretation.
• TIME_FORMAT() functions like DATE_FORMAT() but only works for time values.
| Code | Description |
| %a | Abbreviated weekday name (Sun..Sat) |
| %b | Abbreviated month name (Jan..Dec) |
| %c | Month, numeric (0..12) |
| %D | Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, numeric (00..31) |
| %e | Day of the month, numeric (0..31) |
| %f | Microseconds (000000..999999) |
| %H | Hour (00..23) |
| %h | Hour (01..12) |
| %I | Hour (01..12) |
| %i | Minutes, numeric (00..59) |
| %j | Day of year (001..366) |
| %k | Hour (0..23) |
| %l | Hour (1..12) |
| %M | Month name (January..December) |
| %m | Month, numeric (00..12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %S | Seconds (00..59) |
| %s | Seconds (00..59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00..53), where Sunday is the first day of the week |
| %u | Week (00..53), where Monday is the first day of the week |
| %V | Week (01..53), where Sunday is the first day of the week; used with %X |
| %v | Week (01..53), where Monday is the first day of the week; used with %x |
| %W | Weekday name (Sunday..Saturday) |
| %w | Day of the week (0=Sunday..6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric (two digits) |
| %% | A literal ‘%’ character |
GROUP BY returns blocks of aggregated groups (functionally, one result per group) rather than ordering multiple results, as in ORDER BY.
SELECT * FROM albums GROUP BY artist_name;
SELECT COUNT(albums) FROM albums GROUP BY artist_name;
| Grouping Functions | ||
| Name | Usage | Purpose |
| MIN() | MIN(column) | Returns the smallest value of column. |
| MAX() | MAX(column) | Returns the largest value of column. |
| SUM() | SUM(column) | Returns the sum of all values of column. |
| COUNT() | COUNT(column) | Returns the count of values (rows) in column. |
• COUNT() counts only non-null values.
• GROUP BY will group NULL values together.
LAST_INSERT_ID() returns the last auto-incremented value for your user session.
Other user’s sessions will have their own LAST_INSERT_ID() value; these values are not shared between users.
INSERT INTO users VALUES (NULL, user_name);
Assuming the auto-increment value is 8,
SELECT LAST_INSERT_ID();
will return 8 .
The DISTINCT() function returns all unique values from a column. It will, for instance, eliminate multiple instances of client_name (if you’ve somehow gotten multiple instances).
SELECT DISTINCT(user_name) FROM clients;
Note that you can embed functions within functions, as well as performing arithmetic:
SELECT CONCAT(“$”, FORMAT(invoice_amount*discount, 2)) FROM invoices;
1. Open the mysql monitor: mysql –u root –p
2. Use the accounting database: USE accounting;
3. Add an extraneous white space to the left of a string:
UPDATE clients SET client_name=’ Nepotism Inc.’
WHERE client_name LIKE ‘Nepotism Inc.’;
Check by typing: SELECT client_name FROM clients;
4. To remove all white extraneous spaces, tabs, and returns from a field, type:
SELECT TRIM(client_name) FROM clients;
– The function only affects the SELECT view, not the actual data!
– MySQL function can be used in operations other than SELECT, like INSERT of UPDATE.
5. Permanently remove the extraneous white space to the left of a string:
UPDATE clients SET client_name=’Nepotism Inc.’
WHERE client_name LIKE ‘ Nepotism Inc.’;
Check by typing: SELECT client_name FROM clients;
6. View client phone numbers:
SELECT client_phone FROM clients;
7. View all the are codes of client phone numbers:
SELECT RIGHT(LEFT(client_phone,4),3) FROM clients
WHERE client_phone IS NOT NULL;
This query retrieves the four left most characters, then strips out the last three right most. Note that the SUBSTRING function would also work here. Type this:
SELECT SUBSTRING(client_phone,2,3) FROM clients
WHERE client_phone IS NOT NULL;
8. Find the longest expense category name, and make the results upper case:
SELECT LENGTH(expense_category), UPPER(expense_category) FROM expense_categories
ORDER BY LENGTH(expense_category) DESC;
9. To concatenate all of the client address information as one value, and display it as an alias:
SELECT client_name, CONCAT(client_street, ‘, ‘, client_city, ‘, ‘,client_state, ‘, ‘,client_zip)
AS address FROM clients;
10. Show every expense along with its description and category, using an inner join:
SELECT expense_amount, expense_date, CONCAT(expense_category, ‘: ‘, expense_description)
FROM expenses, expense_categories
WHERE expenses.expense_category_id = expense_categories.expense_category_id;
11. Show the ten most expensive invoices, along with the client name and identification number:
SELECT invoices.*, CONCAT(client_name, ‘ – ‘, clients.client_id) AS client_info FROM invoices
LEFT JOIN clients USING (client_id) ORDER BY invoice_amount DESC LIMIT 10;
Uses a left join, orders by invoice_amount, and limits results to 10 records.
12. — TO simplify the query in step 10 using aliases for table names:
SELECT expense_amount, expense_date, CONCAT(expense_category, ‘: ‘, expense_description)
FROM expenses AS e, expense_categories AS e_c
WHERE e.expense_category_id = e_c.expense_category_id;
13. To display invoices by date, formatting the amounts as dollars:
SELECT *, CONCAT(‘$’, FORMAT(invoice_amount, 2)) AS amount
FROM invoices ORDER BY invoice_date ASC;
14. To round each expense to the nearest dollar:
SELECT ROUND(expense_amount), expense_amount FROM expenses;
15. Retrieve all client names in a random order:
SELECT client_id, client_name FROM clients ORDER BY RAND();
Then run it again, and notice the difference…
SELECT client_id, client_name FROM clients ORDER BY RAND();
A use of this might be for showing random advertising banners on a website.
16. To show every invoice billed in April:
SELECT * FROM invoices WHERE MONTH(invoice_date) = 4;
17. Show the current date and time (according to MySQL):
SELECT CURDATE(), CURTIME();
Note, this is dependant on the System Clock being correct.
18. Display every invoice amount and client name billed in the last six months:
SELECT CONCAT(‘$’, FORMAT(invoice_amount, 2)) AS amount, client_name FROM invoices, clients
WHERE (invoices.client_id = clients.client_id) AND
(invoice_date BETWEEN SUBDATE(CURDATE(), INTERVAL 6 MONTH) AND CURDATE());
This is a join on the client’s name and invoice amount, invoice amount is converted to a more readable format. BETWEEN and the date functions specify a range between 6 months ago and today.
19. To return the current date and time, in a readable format:
SELECT DATE_FORMAT(NOW(), ‘%M %e, %Y – %l:%i’);
20. To return the current time in 24 hour notation:
SELECT TIME_FORMAT(CURTIME(), ‘%T’);
21. Select every expense, ordered by date and amount, formatting the date as Weekday Day Month Year.
SELECT DATE_FORMAT(expense_date, ‘%a %b %e %Y’)
AS the_date, CONCAT(‘$’, FORMAT(expense_amount, 2))
AS amount
FROM expenses
ORDER BY expense_date ASC, expense_amount DESC;