It’s been a great MySQL tutorial series so far and now we’re going to dig even further into the language. The introduction to MySQL, data types, declarative nature, and using data manipulation language is a great foundation to start practicing some queries that are just slightly more advanced. Don’t worry, we’re taking baby steps here but there are a bunch of concepts that we need to get our feet wet with. In this episode we’ll be taking a closer look at things like common functions, math operations, dates and times, and lots more. Let’s jump right into the action.
Common MySQL Functions
There are many functions built into MySQL that you will find very helpful in your day to day MySQL adventures. First off though, let’s have a look at the two tables we’ve been working with so far in this MySQL series. We’ve take the liberty to add more data to our tables since the last episode, see if you can update your tables using only the MySQL command line as practice using the DML commands we’ve covered so far. Our friends and houses tables currently look like so:
mysql> select * from friends; +------------+------------+------------+------------+----------+ | first_name | last_name | cell_phone | birthday | house_id | +------------+------------+------------+------------+----------+ | Brenda | Penblanc | 719-4521 | 1970-02-15 | 5 | | Dave | Chappelle | 977-5555 | 1973-08-24 | 4 | | Emmet | Brickowski | 987-1244 | 2013-07-15 | 3 | | Frankie | Farmington | 978-1235 | 1977-04-17 | 6 | | Jack | Penblanc | 581-1841 | 1967-04-23 | 5 | | Jacob | Olivio | 512-3411 | 1972-10-01 | 7 | | Jim | Jones | 423-1423 | 1985-07-24 | 1 | | Mary | Olivio | 412-9823 | 1972-11-11 | 7 | | Sheila | Farmington | 591-8522 | 1977-07-12 | 6 | | Sofia | Vergara | 487-4612 | 1972-07-10 | 2 | +------------+------------+------------+------------+----------+ 10 rows in set (0.00 sec) mysql> select * from houses; +----------+---------------------------------+----------------+ | house_id | address | home_telephone | +----------+---------------------------------+----------------+ | 1 | 54 Main Street, Conway NH | 153-3487 | | 2 | 742 Applewild, San Francisco CA | 487-1235 | | 3 | 836 Shark Ave, Chatham MA | 876-4576 | | 4 | 88 Bundy Drive, Stowe VT | 746-9487 | | 5 | 87 Highcrest St, Orlando FL | 198-4723 | | 6 | 92 Buzzfeed Lane, Denver CO | 258-4723 | | 7 | 12 Dunpoto Street, Boston MA | 248-4523 | +----------+---------------------------------+----------------+ 7 rows in set (0.00 sec)
So it looks like we now have 10 friends which live in seven houses. Some of our friends are a couple, so they live together. Again, go ahead and use the MySQL command line to update your tables to reflect something similar to what we have here. If you’re having trouble, just remind yourself of the commands you’ll want to use in our Data Manipulation Language episode.
count()
Let’s say we want to see how many rows are in a table. How can we do this? Well, whenever we do a select * from table type statement, it does also return the number of rows in the table. This is not ideal however for rows that may have hundreds of thousands of records, not to mention if there are null values. To count the number of entries with non null values, we can use count().
mysql> select count(home_telephone) from houses; +-----------------------+ | count(home_telephone) | +-----------------------+ | 7 | +-----------------------+ 1 row in set (0.00 sec)
Here we are able to count how many home phone numbers exist in our houses table. Note that all you have to do is pass in the name of the column that you are trying to operate on. Lest you not believe that this function does indeed only count non null values, let’s go ahead and decide that one of our couples does not believe in having a home telephone and set their telephone record to null. The Olivios are about to save a bundle on their phone bill.
mysql> update houses set home_telephone=null where house_id=7; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from houses; +----------+---------------------------------+----------------+ | house_id | address | home_telephone | +----------+---------------------------------+----------------+ | 1 | 54 Main Street, Conway NH | 153-3487 | | 2 | 742 Applewild, San Francisco CA | 487-1235 | | 3 | 836 Shark Ave, Chatham MA | 876-4576 | | 4 | 88 Bundy Drive, Stowe VT | 746-9487 | | 5 | 87 Highcrest St, Orlando FL | 198-4723 | | 6 | 92 Buzzfeed Lane, Denver CO | 258-4723 | | 7 | 12 Dunpoto Street, Boston MA | NULL | +----------+---------------------------------+----------------+ 7 rows in set (0.00 sec) mysql> select count(home_telephone) from houses; +-----------------------+ | count(home_telephone) | +-----------------------+ | 6 | +-----------------------+ 1 row in set (0.00 sec)
If you do want to include the null values in your count, you can do so. Simply use the count(*) syntax like so:
mysql> select count(*) from houses; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec)
We can see we’re back to 7, because this method is including the null value. Another useful way to apply the count method to our data is to combine it with the distinct keyword. In our friends table, some of the people have the same last name. Let’s say we want to find out how many unique last names there are in our friends table. First we’ll count all of our friends, then we’ll count the number of unique last names among our friends. Check it out:
mysql> select count(last_name) from friends; +------------------+ | count(last_name) | +------------------+ | 10 | +------------------+ 1 row in set (0.00 sec) mysql> select count(distinct last_name) from friends; +---------------------------+ | count(distinct last_name) | +---------------------------+ | 7 | +---------------------------+ 1 row in set (0.00 sec)
Excellent! We can see that we have ten friends in total, and they collectively have seven different last names.
Getting a Sum or Average
There are two MySQL functions we can use to look at numeric data within a table, and get the sum or average of that data. In our tables, we’ll use the house_id as an example. We’ll get the sum and average of this record from both the friends table and the houses table. You’ll see that the function name gets used as the column name in the output, but you can specify what you would like the name to be by making use of an alias. Both ways are shown here:
mysql> select sum(house_id), avg (house_id) from houses; +---------------+----------------+ | sum(house_id) | avg (house_id) | +---------------+----------------+ | 28 | 4.0000 | +---------------+----------------+ 1 row in set (0.00 sec) mysql> select sum(house_id), avg(house_id) from friends; +---------------+---------------+ | sum(house_id) | avg(house_id) | +---------------+---------------+ | 46 | 4.6000 | +---------------+---------------+ 1 row in set (0.00 sec) mysql> select sum(house_id) as `sum`, avg (house_id) as `average` from houses; +------+---------+ | sum | average | +------+---------+ | 28 | 4.0000 | +------+---------+ 1 row in set (0.00 sec) mysql> select sum(house_id) as `sum`, avg(house_id) as `average` from friends; +------+---------+ | sum | average | +------+---------+ | 46 | 4.6000 | +------+---------+ 1 row in set (0.00 sec)
Checking Minimum and Maximum Values
In our friends table each person has a birthday. We can make use of the min and max functions to see who is the oldest and youngest among our friends. Once again, we’ll use an alias as well to show how to customize the column name in our output. Check it out:
mysql> select min(birthday) from friends; +---------------+ | min(birthday) | +---------------+ | 1967-04-23 | +---------------+ 1 row in set (0.00 sec) mysql> select max(birthday) from friends; +---------------+ | max(birthday) | +---------------+ | 2013-07-15 | +---------------+ 1 row in set (0.00 sec) mysql> select min(birthday) as oldest from friends; +------------+ | oldest | +------------+ | 1967-04-23 | +------------+ 1 row in set (0.00 sec) mysql> select max(birthday) as youngest from friends; +------------+ | youngest | +------------+ | 2013-07-15 | +------------+ 1 row in set (0.00 sec)
Dates and Math in MySQL
In almost any web based application worth its salt, you are going to be dealing with calculating dates and times, differences in dates and times, calculating math operations, and much more. Think of a site like Reddit where you can sort links based on a period of time. Maybe you like to use Facebook which does immense data processing of timeframes, or even while doing a Google search you can search in the past week, six months, year, or all time. We’re going to need to operate on our data in similar ways in order to build more useful applications so let’s take a look at a few common ways to operate on dates, time, and math in MySQL.
datediff()
This is a handy function for calculating the difference between two dates. It takes two dates which it will subtract with the most recent date being provided as the first parameter. For example, we can check how much time is between July 4th 2012 and December 25th 2013. First we’ll check how many days are between the two dates, then we’ll convert that value to a YYYY-MM-DD format by using the from_days function. Note that you can also chain functions together if you like to reduce typing.
mysql> select datediff('2013-12-25','2012-07-04');
+-------------------------------------+
| datediff('2013-12-25','2012-07-04') |
+-------------------------------------+
| 539 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select from_days(539);
+----------------+
| from_days(539) |
+----------------+
| 0001-06-23 |
+----------------+
1 row in set (0.00 sec)
mysql> select from_days(datediff('2013-12-25','2012-07-04'));
+------------------------------------------------+
| from_days(datediff('2013-12-25','2012-07-04')) |
+------------------------------------------------+
| 0001-06-23 |
+------------------------------------------------+
1 row in set (0.00 sec)
Nice One! Here we can see that there are 539 days in between July 4th of 2012 and Christmas day of 2013. By applying that value to the from_days function, we’ll come up with a difference of one year, six months, and twenty three days. Finally, by rewriting the initial datediff expression and wrapping it in the from_days function, we can get the difference in year, month, days format in one shot.
Current Dates and Times
There are a handful of functions that deal with current dates and times such as current_date(), current_time(), utc_timestamp(), current_timestamp(), now(), and sysdate(). We can test them out just like this:
mysql> select current_date(), current_time(); +----------------+----------------+ | current_date() | current_time() | +----------------+----------------+ | 2014-09-02 | 13:21:07 | +----------------+----------------+ 1 row in set (0.00 sec) mysql> select utc_timestamp(), current_timestamp(), now(), sysdate(); +---------------------+---------------------+---------------------+---------------------+ | utc_timestamp() | current_timestamp() | now() | sysdate() | +---------------------+---------------------+---------------------+---------------------+ | 2014-09-02 17:21:42 | 2014-09-02 13:21:42 | 2014-09-02 13:21:42 | 2014-09-02 13:21:42 | +---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)
There is a difference between how the now() and sysdate() functions work. now() runs at the beginning of a query and keeps the same value throughout the lifetime of that query. sysdate() on the other hand, gives you the exact value at that specific point in time, regardless of where in the query the operation is. We can use the sleep() function to show how this works.
mysql> select utc_timestamp(), current_timestamp(), now(), sysdate();
+---------------------+---------------------+---------------------+---------------------+
| utc_timestamp() | current_timestamp() | now() | sysdate() |
+---------------------+---------------------+---------------------+---------------------+
| 2014-09-02 17:21:42 | 2014-09-02 13:21:42 | 2014-09-02 13:21:42 | 2014-09-02 13:21:42 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select sysdate(), now(), sleep(2.7), sysdate(), now()G
*************************** 1. row ***************************
sysdate(): 2014-09-02 13:31:09
now(): 2014-09-02 13:31:09
sleep(2.7): 0
sysdate(): 2014-09-02 13:31:12
now(): 2014-09-02 13:31:09
1 row in set (2.72 sec)
Simple Math Functions
In MySQL we can do simple math like addition, subtraction, and division. There are also functions for dealing with power, square root, modulo, and so on. Let’s test out a few of these functions here.
mysql> select 5 + 7 as `sum`, 99 - 25 as diff, 22 * 8 as product, 34/2 as division; +-----+------+---------+----------+ | sum | diff | product | division | +-----+------+---------+----------+ | 12 | 74 | 176 | 17.0000 | +-----+------+---------+----------+ 1 row in set (0.00 sec) mysql> select pow(5,3), sqrt(1255), 5 % 3 as modulo; +----------+------------------+--------+ | pow(5,3) | sqrt(1255) | modulo | +----------+------------------+--------+ | 125 | 35.4259791678367 | 2 | +----------+------------------+--------+ 1 row in set (0.00 sec) mysql> select abs(2-8), ceil(9/7), floor(7/6); +----------+-----------+------------+ | abs(2-8) | ceil(9/7) | floor(7/6) | +----------+-----------+------------+ | 6 | 2 | 1 | +----------+-----------+------------+ 1 row in set (0.00 sec) mysql> select log10(2000), log2(512); +--------------------+-----------+ | log10(2000) | log2(512) | +--------------------+-----------+ | 3.3010299956639813 | 9 | +--------------------+-----------+ 1 row in set (0.00 sec)
Granular Queries Conclusion
Once again, when learning MySQL or anything else for that matter, it pays to put rubber to road so to speak. In other words and in our case, open up the MySQL console and start testing out all of the various functions and dml statements we have been covering in this series. In this episode we covered many of the common MySQL functions that you are sure to run across. We saw how to count values, get sums and differences, calculate averages and much more.
