Numeric Functions

Besides the standard math operators that MySQL uses (for addition, subtraction, multiplication, and division), there are about two dozen functions dedicated to formatting and performing calculations on number columns. Table 6.2 lists the most common of these, some of which I will demonstrate shortly.

Table 6.2. Here are the most-used number functions, omitting the various trigonometric and exponential 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 digits.
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.

I want to highlight three of these functions: FORMAT(), ROUND(), and RAND(). The firstwhich is not technically number-specificturns any number into a more conventionally formatted layout. For example, if you stored the cost of a car as 20198.2, FORMAT(car_cost, 2) would turn that number into the more common 20,198.20.

ROUND() will take one value, presumably from a column, and round that to a specified number of decimal places. If no decimal places are indicated, it will round the number to the nearest integer. If more decimal places are indicated than exist in the original number, the remaining spaces are padded with zeros (to the right of the decimal point).

The RAND() function, as you might infer, is used for returning random numbers. Specifically, it returns a value between 0 and 1.0.

SELECT RAND()

A further benefit to the RAND() function is that it can be used with your queries to return the result in a random order.

SELECT * FROM tablename ORDER BY RAND()

To use numeric functions:

1.	Display the invoices by date, formatting the amounts as dollars (Figure 6.9). Figure 6.9. Applying two functions and the right formatting to the invoice_amount column generates the better amount values. [View full size image] [View full width] SELECT *, CONCAT('$', FORMAT (invoice_amount, 2)) AS amount FROM invoices ORDER BY invoice_date ASC\G Using the FORMAT() function, as just described, in conjunction with CONCAT(), you can turn any number into a currency format as you might display it in a Web page or application. Notice in the figure how the invoice amount is actually returned twice: once as part of all columns (*) and the second time in its formatted shape.
2.	Round each expense amount to the nearest dollar (Figure 6.10). Figure 6.10. The ROUND() function is useful in situations where decimal values do not matter. SELECT ROUND(expense_amount), expense_amount FROM expenses; The ROUND() function, when you do not specify a decimal argument, simply rounds every value to the nearest integer.
3.	Retrieve all of the client names in a random order twice (Figure 6.11). Figure 6.11. Running the same query twice with the ORDER BY RAND() clause returns the same results but in different order. SELECT client_id, client_name FROM clients ORDER BY RAND(); SELECT client_id, client_name FROM clients ORDER BY RAND(); Although this may not be the most practical use of the ORDER BY RAND() clause, it does give you an idea of how it works. While the RAND() function is not absolutely random, it is effective enough for most cases. Notice that you do not specify to which column RAND() is applied.

Date and Time Functions

The date and time column types in MySQL are particularly flexible and useful. But because many database users are not familiar with all of the available date and time functions, these options are frequently underused.

Whether you want to make calculations based upon a date or to return only the month name from a stored value, MySQL has a function for that purpose. Table 6.3 lists most of these functions.

Table 6.3. 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 (see the sidebar).
DATE_SUB(dt, INTERVAL x type)	Returns the value of x units subtracted from dt (see the sidebar).
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.

As you can tell, the many date and time functions range from those returning portions of a date column to those that return the current date or time. These are all best taught by example.

To use date and time functions:

1.	Display every invoice billed in April (Figure 6.12). Figure 6.12. Using the MONTH() function, I can narrow down my search results based upon the values in a date column. SELECT * FROM invoices WHERE MONTH(invoice_date) = 4\G Because April is the fourth month, this query will return only those invoices billed then. Another way of writing it would be to use WHERE MONTHNAME(invoice_date) = 'April' (although it's best to use numbers instead of strings wherever possible).
2.	Show the current date and time, according to MySQL (Figure 6.13). Figure 6.13. The CURDATE() and CURTIME() functions return the current date and time. The same can be accomplished with NOW(). SELECT CURDATE(), CURTIME(); To show what date and time MySQL currently thinks it is, you can select the CURDATE() and CURTIME() functions, which return these values. This is an example of a query that can be run without referring to a particular table name (or without even selecting a database).
3.	Display every expense filed in the past two months (Figure 6.14). Figure 6.14. Here I've used the CURDATE() function again (see Figure 6.13) to set a range of acceptable dates for selecting expenses. [View full size image] SELECT CONCAT('$', FORMAT (expense_amount, 2)) AS amount, expense_date, expense_category, expense_description FROM expenses AS e, expense_categories AS e_c WHERE (e.expense_category_id = e_c.expense_category_id) AND (expense_date BETWEEN SUBDATE(CURDATE(), INTERVAL 2 MONTH) AND CURDATE()); This query uses a lot of different techniques covered to this point. For starters, it's a simple join to incorporate the expense category along with the expense information. Second, I've converted the expense amount into a more readable format. Third, there are two conditions set in the WHERE clause: one for the join and another limiting results to the previous six months. The BETWEEN term is used to specify a range from two months ago, SUBDATE(CURDATE(), INTERVAL 2 MONTH), and today, CURDATE(), which rules out expenses that may have been prebilled (i.e., are in the future).

DATE_ADD() and DATE_SUB() The DATE_ADD() and DATE_SUB() functions, which are synonyms for ADDDATE() and SUBDATE(), perform calculations upon date values. The syntax for using them is FUNCTION(date, INTERVAL x type) In the example, date can be either an entered date or a value retrieved from a column. The x value varies, depending upon which type you specify. The available types are SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR. There are even combinations of these: MINUTE_SECOND, HOUR_MINUTE, DAY_HOUR, and YEAR_MONTH. To add two hours to a date, you would write DATE_ADD(date, INTERVAL 2 HOUR) To add two weeks from December 31, 2006: DATE_ADD('2006-12-31', INTERVAL 14 DAY) To subtract 15 months from a date: DATE_SUB(date, INTERVAL '1-3' YEAR_MONTH) This last query tells MySQL that you want to subtract 1 year and 3 months from the value stored in the date column.

Formatting the Date and Time

There are two additional date and time functions that you might find yourself using more than all of the others combined. These are DATE_FORMAT() and TIME_FORMAT(). There is some overlap between the two in that DATE_FORMAT() can be used to format both the date and the time, whereas TIME_FORMAT() can format only a time value. The syntax is

SELECT DATE_FORMAT(date_column, formatting') FROM tablename

The formatting relies upon combinations of key codes and the percentage sign to indicate what values you want returned. Table 6.4 lists the available date and time formatting parameters. You can use these in any combination, along with textual additions such as punctuation to return a date and time in a more presentable form.

Table 6.4. The terms for date and time formatting are not obvious, but this table lists the most important ones.

DATE_FORMAT() and TIME_FORMAT() Parameters
Term	Meaning	Example
%e	Day of the month	1-31
%d	Day of the month, two digit	01-31
%D	Day with suffix	1st-31st
%W	Weekday name	Sunday-Saturday
%a	Abbreviated weekday name	Sun-Sat
%c	Month number	1-12
%m	Month number, two digit	01-12
%M	Month name	January-December
%b	Month name, abbreviated	Jan-Dec
%Y	Year	2002
%y	Year	02
%l	Hour	1-12
%h	Hour, two digit	01-12
%k	Hour, 24-hour clock	0-23
%H	Hour, 24-hour clock, two digit	00-23
%i	Minutes	00-59
%S	Seconds	00-59
%r	Time	8:17:02 PM
%T	Time, 24-hour clock	20:17:02
%p	AM or PM	AM or PM

Assuming that a column called dt has the date and time of 2006-04-30 23:07:45 stored in it, common formatting tasks and results would be

• Time (11:07:02 PM)

  DATE_FORMAT(dt, '%r')

• Time without seconds (11:07 PM)

  DATE_FORMAT(dt, '%l:%i %p')

• Date (April 30th, 2006)

  DATE_FORMAT(dt, '%M %D, %Y')

To format the date and time:

SELECT DATE_FORMAT(NOW(),'%M %e, %Y - %l:%i');

SELECT TIME_FORMAT(CURTIME(),'%T');

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;

Encryption Functions

MySQL has several different encryption and decryption functions built into the software (Table 6.5). You may have already seen one of these, PASSWORD(), since it's used to encrypt the various user passwords for MySQL access. MySQL advises against using this one in your own tables, and it can be a cause of problems (see Appendix A, "Troubleshooting," for more).

Table 6.5. Different encryption functions are available as of new releases of MySQL, so know what version you are using!

Encryption Functions
Function	Added	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.

Two functions, MD5() and SHA1(), create what's called a hash: a fixed-length mathematical representation of data. So the MD5() of the word rabbit is a51e47f646375ab6bf5dd2c42d3e6181. If you store the hash of some data in your database, you can never retrieve the actual data back out (because the data itself was never stored). What you can do is later compare the stored value against the calculated hash of a submitted value. For example, you store the hash of a password and then, when a user logs in, calculate the hash of the login password and see if the two values are equal. These two functions are fine for low-security situations, but both are crackable, meaning they don't provide guaranteed security.

If you want true encryption, you'll need to use one of several functions also listed in the table. Each of these takes two arguments: the data to be encrypted or decrypted, and a salt argument. The salt is any random value that will make the encryption more unique and more secure. The same salt must be used to encode and decode data, though, so the salt must be stored or memorized somehow. Also, these encryption functions return binary data, which must be stored in a BLOB column type.

Of the true encryption functions, the most secure at the time of this writing are AES_ENCRYPT() and AES_DECRYPT(). An example of how you would use them in a query is:

INSERT INTO users (username, user_pass) VALUES ('trout', AES_ENCRYPT ('myPass1', 'NaCL#'))

To retrieve that stored data, you could use this query:

SELECT username, AES_DECRYPT(user_pass, 'NaCL#') FROM users

To encrypt and decrypt data:

1.	Create a new logins table (Figure 6.18). Figure 6.18. The logins table will be used to demonstrate encryption and decryption. CREATE TABLE logins ( login_id INT UNSIGNED NOT NULL AUTO_INCREMENT, client_id SMALLINT UNSIGNED NOT NULL, login_name TINYBLOB, login_pass CHAR(40), PRIMARY KEY (login_id), INDEX (client_id) ); Because none of the current tables would logically use encryption, I'm creating a new one. The premise behind this table is that each client can have any number of logins in order to access their account information. The table will contain a login_id, a login_pass, and a login_name. To associate each login with a client, the client_id is a foreign key here (relating to the primary key with the same name in the clients table). For extra security, the login_name will be encrypted and decrypted and must therefore be a BLOB type (I've chosen TINYBLOB). The password will be stored using the SHA1() function, which always returns a string 40 characters long. Hence, that column is defined as a CHAR(40). The primary key index and an index on the client_id (since it's a foreign key and will be used in joins and WHERE clauses) are added as well.
2.	Insert a new login record (Figure 6.19). Figure 6.19. I encrypt values when storing them. [View full size image] INSERT INTO logins (client_id, login_name, login_pass) VALUES (4, AES_ENCRYPT('larryeullman', 'w1cKet'), SHA1('larryPASS')); Here I am adding a new record to the table, using the SHA1() function to encrypt the password (larrypass) and AES_ENCRYPT() with a salt of w1cKet to encrypt the user name (larryeullman). If you are not using at least version 4.0.2 of MySQL, you'll need to change this query to use the MD5() and ENCODE() functions instead.
3.	Retrieve the user's information (Figure 6.20). Figure 6.20. and decrypt them for retrieval. [View full size image] [View full width] SELECT client_id FROM logins WHERE (login_pass = SHA1('larryPASS') AND AES_DECRYPT (login_name, 'w1cKet') = 'larryeullman'); Whenever you store a SHA1() or MD5() encrypted string, to make a comparison match, you simply use the same function again. If these match, the right password was entered; otherwise, no value will be returned by this query (Figure 6.21). Figure 6.21. These functions are case-sensitive, so entering a password incorrectly will return no results. [View full size image] Any value stored using an encryption function can be retrieved (and matched) using the corresponding decryption function, as long as the same salt is used (here, w1cKet).

Grouping Functions

The theory of grouping query results is similar to ordering and limiting query results in that it uses a GROUP BY clause. However, this is significantly different from the other clauses because it works by grouping the returned data into similar blocks of information. For example, to group all of the invoices by client, you would use

SELECT * FROM invoices GROUP BY client_id

The returned data is altered in that you've now aggregated the information instead of returning just the specific itemized records. So where the database might have seven invoices for one client, the GROUP BY would return all seven of those records as one. I did not discuss the idea in the preceding chapter because you will normally use one of several grouping (or aggregate) functions in conjunction with GROUP BY. Table 6.6 lists these.

Table 6.6. The grouping, or aggregate, functions are normally used with the GROUP BY clause in a SQL query.

Grouping Functions
Function and Usage	Purpose
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.
COUNT(column)	Counts the number of rows.
GROUP_CONCAT(values)	Returns a concatenation of the grouped values.

You can apply combinations of WHERE, ORDER BY, and LIMIT conditions to a GROUP BY, normally structuring your query like this:

SELECT columns FROM tablename WHERE clause GROUP BY columnname ORDER BY columnname LIMIT x

To group data:

SELECT MAX(invoice_amount) FROM invoices;

SELECT invoice_amount FROM invoices ORDER BY invoice_amount DESC LIMIT 1

[View full width]
SELECT SUM(expense_amount), expense_category FROM expenses LEFT JOIN expense_categories
 USING (expense_category_id) GROUP BY (expenses.expense_category_id);

SELECT COUNT(*) AS num, client_name
FROM invoices LEFT JOIN clients
USING (client_id)
GROUP BY (clients.client_id)
ORDER BY num DESC;

SELECT COUNT(*), SUM(expense_amount),
expense_category FROM expenses LEFT JOIN expense_categories
USING (expense_category_id)
GROUP BY (expenses.expense_category_id);

Other Functions

To conclude this chapter, I'll discuss two final functions that do not fit neatly into any of the earlier categories. The first, LAST_INSERT_ID(), is critical for working within a relational database. The second, DISTINCT(), assists you in adjusting what results to return from a table.

Using LAST_INSERT_ID

LAST_INSERT_ID() is a function that returns the value set by the last INSERT statement for a column that's automatically incremented. For example, in the accounting database example, the expense_categories table uses an expense_category_id column that was defined as a not-null, unsigned integer that was the primary key and would be auto-incremented. This meant that every time a NULL value was entered for that column, MySQL would automatically use the next logical value. LAST_INSERT_ID() will return that value. You can use it like so:

SELECT LAST_INSERT_ID()

To use LAST_INSERT_ID():

INSERT INTO logins
(client_id, login_name, login_pass) VALUES
(4, AES_ENCRYPT('homerjsimpson', 'w1cKet'), SHA1('D\'ohReMi'));

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() Confusion A common misunderstanding about LAST_INSERT_ID() is that it will return the last inserted value into the table, regardless of where it comes from. This is not true. The function will return only the last inserted ID for a query made by the current connection. Therefore, if ten scripts and three mysql clients are all connected to a database at the same time, and each inserts a value into a table and then recalls this value, each script or client will return only the ID correlating to that session's previous insert.

Using DISTINCT

The DISTINCT() function is used to weed out duplicate values and is normally applied to a column.

SELECT DISTINCT(columnname) FROM tablename

This is frequently used in conjunction with GROUP BY so that the number of unique records, based upon a category, can be retrieved and possibly counted.

To use DISTINCT():

SELECT DISTINCT(invoices.client_id),
client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;

SELECT COUNT(DISTINCT(client_id))
FROM invoices;

The combination of the COUNT() and DISTINCT() functions returns just the one number, rather than every applicable record.