| 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 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.
|
 Tips
Along with the mathematical functions listed here, a number of trigonometry, exponential, and other types of functions are available. Of course you can also use any of the mathematical operators: +, -, *, and /. The MOD() function is the same as using the percentage sign: SELECT MOD(9,2) SELECT 9%2 Once again, remember that functions can be applied to columns or to hard-coded values. The following queries are perfectly acceptable: SELECT ROUND(34.089, 1) SELECT SQRT(81) SELECT ABS(-8)
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. 
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).
|
Tips Because MySQL is such a fast and powerful application, you should try to let it handle most of the formatting of values. Beginning programmers will commonly retrieve values from MySQL and then use the programming language to format the data, which is less efficient. The NOW() and CURDATE() functions are often used to set the value for a date or datetime column when inserting records: [View full width] INSERT INTO invoices (client_id, invoice_date, invoice_amount, invoice_description) VALUES (23, NOW(), 3049.39, `blah blah')
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: 1. | Return the current date and time as Month DD, YYYY - HH:MM (Figure 6.15).
Figure 6.15. The DATE_FORMAT() function uses combinations of parameters to return a formatted date. 
SELECT DATE_FORMAT(NOW(),'%M %e, %Y - %l:%i');
Using the NOW() function, which returns the current date and time, I can practice my formatting to see what results are returned.
| 2. | Display the current time, using 24-hour notation (Figure 6.16).
Figure 6.16. If you are formatting just the time, you must use the TIME_FORMAT() function. 
SELECT TIME_FORMAT(CURTIME(),'%T');
Although the DATE_FORMAT() function can be used to format both the date and the time (or just the date), if you want to format just the time, you must use the TIME_FORMAT() function. This can be applied to a time value (such as CURTIME() returns) or a date-time value (from NOW()).
| 3. | Select every expense, ordered by date and amount, formatting the date as Weekday (abbreviated) Day Month (abbreviated) Year (Figure 6.17).
Figure 6.17. DATE_FORMAT() will apply only to stored date values; NULL values will not be affected (see the first record). 
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;
This is just one more example of how you can use these formatting functions to alter the output of a SQL query.
|
Tip
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. 
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 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. 
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: 1. | Find the largest invoice amount (Figure 6.22).
Figure 6.22. The MAX() function performs a type of grouping, even if the GROUP BY clause is not formally stated. 
SELECT MAX(invoice_amount) FROM invoices;
Since the MAX() function returns the largest value for a column, this is the easiest way to return the desired result. Another option would be to write
SELECT invoice_amount FROM invoices ORDER BY invoice_amount DESC LIMIT 1
| | | 2. | Determine how much has been spent under each expense category (Figure 6.23).
Figure 6.23. The SUM() function is used here with a GROUP BY clause to total up fields with similar expense_category_ids. 
[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);
To accomplish this task, I first use a left join to incorporate the name of the expense category into the results. Then I simply group all of the expenses together by category ID and summarize their respective amounts. MySQL will return a table of two columns: the total and the category.
| 3. | See how many invoices have been billed to each client (Figure 6.24).
Figure 6.24. The COUNT() function returns the number of records that apply to the GROUP BY criteria (here, client_id). 
SELECT COUNT(*) AS num, client_name FROM invoices LEFT JOIN clients USING (client_id) GROUP BY (clients.client_id) ORDER BY num DESC;
Whenever you need to determine how many records fall into a certain category, a combination of COUNT() and GROUP BY will get the desired result. With grouping, you can order the results as you would with any other query.
| | | 4. | Alter the query in Step 2 so that it reflects how many invoices are tied into each total amount (Figure 6.25).
Figure 6.25. By adding COUNT() to the query inFigure 6.23, I can also show how many expenses are included in each SUM() column. 
SELECT COUNT(*), SUM(expense_amount), expense_category FROM expenses LEFT JOIN expense_categories USING (expense_category_id) GROUP BY (expenses.expense_category_id);
Here I've used the COUNT() function again, along with an alias, to count each record in each grouping. You can apply multiple aggregate functions within the same query, although that syntax can get tricky.
|
Tips NULL is a peculiar value, as you've seen, and it's interesting to know that GROUP BY will group NULL values together, since they have the same nonvalue. When used as COUNT(columnname), only non-null values will be counted. When you use COUNT(*), all rows will be counted. The GROUP BY clause, and the functions listed here, take some time to figure out, and MySQL will report an error whenever your syntax is inapplicable. Experiment within the mysql client to determine the exact wording of any query you might want to run in an application. You can optionally use a WITH ROLLUP modifier in a GROUP BY query. It will summarize grouped columns for you (Figure 6.26). Figure 6.26. If you add WITH ROLLUP to the query in Figure 6.25, MySQL will summarize the total number of expenses and the total amount of expenses. The last listed record reflects these totals. (The word Travel under expense_category is actually meaningless on that line.) 
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(): 1. | Insert another user into the logins table (Figure 6.27).
Figure 6.27. To test LAST_INSERT_ID() (Figure 6.28), I'll add another record to the logins table. 
INSERT INTO logins (client_id, login_name, login_pass) VALUES (4, AES_ENCRYPT('homerjsimpson', 'w1cKet'), SHA1('D\'ohReMi'));
Make sure when working with encryption that you use the same encryption function and salt for every record in the table, or else you will have difficulty accurately retrieving data later.
| | | 2. | Retrieve that record's login_id (Figure 6.28).
Figure 6.28. LAST_INSERT_ID() is a specific function that returns only the number corresponding to the previous insert. 
SELECT LAST_INSERT_ID();
This command returns just one value in one column, which reflects the primary key inserted in Step 1. When using relational databases, use LAST_INSERT_ID() to ensure proper primary keytoforeign key integrity.
|
Tips
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(): 1. | List the different clients that have been billed (Figure 6.29).
Figure 6.29. DISTINCT() is somewhat like the aggregate functions in that it helps to group the returned records. 
SELECT DISTINCT(invoices.client_id), client_name FROM invoices, clients WHERE invoices.client_id = clients.client_id;
Instead of listing every client for every invoice, this query will list only each unique client. It also rules out any clients that have not yet been billed (the join accomplishes that).
| | | 2. | Count how many different clients have been billed (Figure 6.30).
Figure 6.30. Using DISTINCT() along with COUNT() returns the number of unique values in a table. 
|
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. Tips There are many other miscellaneous MySQL functions, such as DATABASE(). If you were to run the query SELECT DATABASE(), it would tell you which database is currently being used. The USER() function will tell you which user is currently being used. This is a combination of username@host. If you're familiar with the concept of castingforcibly changing the type of a piece of data, possibly changing the value of the data in the processrest assured that MySQL has several casting functions available. See the manual for specifics.
|
