MySQL Function Tutorial

MySQL Function Tutorial

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:

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().

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.

If you do want to include the null values in your count, you can do so. Simply use the count(*) syntax like so:

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:

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:

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:


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.

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:

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.

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.

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.