Topic: SQL

The SQL Operator LIKE is one of the most impressive parts of SQL. LIKE gives you the ability to search through a database for a particular value. When used in combination with SQL’s wildcards, you could potentially build a mediocre search engine. Of course, it would be wise to build an index first, but that’s more complicated stuff you don’t need to worry about just yet. LIKE is particularly useful for large content data types in SQL. Using like on numbers isn’t exactly a common practice.

SQL Wildcards

We really need to use wildcards for this tutorial, so let’s discuss the two common ones really quick

• % – any characters and any length
• _ – any characters, but only ONE character

We’ll see how to use these below, but basically you will just put them right before and/or after the value you are searching for.

Searching a Database Using SQL

Our table:

SELECT * FROM table_name
WHERE content LIKE ‘%am%’

We were searching for am, and we didn’t care what came before or after it, which is why am had the wildcard, %, before and after it. You might have noticed that we didn’t have our equal operator. That’s because LIKE is an operator and it replaces the equals sign. While the record containing ‘sam’ didn’t have anything after it, SQL still matched it. This is because the SQL wildcard takes zero to unlimited characters. What if were were looking only for the names, Pam or Sam, but weren’t smart enough to use an OR? Simple:

SELECT * FROM table_name
WHERE content LIKE ‘_am’

While this isn’t used very often, it is still useful to understand. The only difference in the two examples is that we removed the % wildcards and put a new wildcard, _, right in front of am. This wildcard just returns a match only if the entire record consists of 3 letters and the last 2 are am. Remember you can always put these wildcards before, after, or both around your search value and that it must be in quotes.

One of the most useful SQL keywords JOIN allows you to compare data across tables. There comes a time in every young developer’s life where they have created a ton of tables, but they need to match information from one to another table. For example, you may have a list of users, but you need to match them with a list of orders. JOIN makes this very possible and quite simple once you get the hang of it. However, this might be one of the most difficult tutorials for someone who is just learning SQL.

Before we get into using a JOIN, I should tell you there are actually 4 types of JOINs.

• JOIN – Returns values when values match in both tables
• LEFT JOIN – Returns all specified “left” values including the matches on the “right”
• RIGHT JOIN – Returns all specified “right” values including the matches on the “left”
• FULL JOIN – Returns all specified “left” values and “right” values

“Left” and “Right” Tables

When the creators made SQL, the envisioned sheets of paper and apparently when creating JOIN, they used what they saw. In the most primitive JOIN usage, your “left” table is the table selected first (usually after SELECT). Your “right” table is the one directly following the JOIN keyword(s). I know it is pretty complicated just bear with me until the example where it will all make sense. JOIN obviously requires two tables and they are:

Table: “Users”

Table: “Purchases”

All political jokes aside, let’s try to get all of our users including the their purchases. Example, please!

SELECT Users.first, Purchases.item 
FROM Users
LEFT JOIN Purchases
ON Users.username=Purchases.username
ORDER BY Users.first

Beautiful! You might be confused, and I most definitely was when I first learned about JOINs. For example, Users.first? What in the world is that? Remember we are working with 2 tables and SQL will get confused very quickly if we are telling it about columns, when it doesn’t know which table that contains that column. The Users is our first table, and first is a column in the “Users” table. Now, SQL is like “Oh! That column.” We repeat that idea to get our item column in our “Purchases” table. We pick FROM Users because we want “Users” to be our first (LEFT) table. This means that we want every single first column in our “Users” table now matter what is specified in the following JOIN.

Now, we get tricky. When then declare our 2nd (RIGHT) table with LEFT JOIN Purchases. But wait, we need to tell it why we are including a 2nd table. We use the SQL keyword ON to say we want to find matching values in our username column in the Users table with our other username column in our Purchases table. Then we wrap up with saying, “Hey, sort it by their first, which is in Users, really quick. Ultimately, if you know how to use a LEFT JOIN, you can use all of the rest. Go ahead and test out the others you little querying mastermind.

Dates and times are extremely frustrating data types and SQL doesn’t make them any easier to work with than other languages. While date and time are actually completely different data types, they are often merged into a datetime data type. SQL dates and times alone are pretty simple even though they require a special formatting. However, merging the two can be one of the most painful tasks you will face in your early years. A good rule of thumb is to only use a time and date together when you absolutely need to or will need to in the future. I’ll explain why dates and times complicates things in just a minute. First, let’s look at how easy life when you simply work with dates.

Working With SQL Dates

Our “Users” Table:

Let’s try to get all of our members before December 25, 2011.

SELECT *
FROM Users
WHERE member_since < '2011-12-25'

Alright, we said BEFORE December 25, 2011, which means poor Hillary isn’t included because she is on that day. Al barely made the cut, but old George was way before our cut off date. I suppose we should actually talk about what happened. Up to WHERE member_since < ‘2011-12-25’, we know what were doing. We could probably guess what is going on here, but I’ll beat the dead horse. We check our member_since column to see if it is less than, <, our date, which was written in single quotes as ‘2011-12-25’. Often when googling for help with dates, you see this syntax referred to as ‘YYYY-MM-DD’, where Y is for year, M is for month, and D is for day, obviously. Each repetition is a symbol of a blank character space for that element. So, you only want 2 digits for month because that makes sense, and maybe you don’t care about the first 2 characters of year. Instead you just write, YY to get 11. Easy enough, onto the wonderful DATETIMEs

Using DATETIME in SQL

Our New “Users” Table:

Now, we attempt to get all of our members that logged in before before 1:00PM December 24, 2011.

SELECT * 
FROM Users
WHERE last_login < '2011-12-24 13:00:00'

Poor George… Alright, exactly the same syntax as a last one with except we replaced the column name and used a DATETIME string to compare against. The DATETIME data type contains all of the same elements as the DATE data type plus a time string. Commonly referenced as ‘YYYY-MM-DD HH:MM:SS’ Again, in single quotations and with a space separating the date string from the time string, we create our DATE string for comparison. Hillary and Al both make the cut because they logged in before the ‘2011-12-24 13:00:00’ cut off DATETIME. It’s much easier here than it looks in practice. That’s because we put in the DATETIME string by hand. SQL has a few DATE functions you can play around with that you can replace our string with:

Important SQL DATE Functions

Note: SQL DATE functions differ greatly across SQL versions. These are MySQL.

• CURDATE() – gets the current date
• CURTIME() – gets the current time
• NOW() – gets both the date and time (in DATETIME format)

If you are not using MySQL, consider switching. Just kidding, kind of, but no worries, a simple query of Google will help you find the right SQL functions for your SQL database.

And so the DATETIMEs came to pass, and the light shined through the best part of SQL, functions. There are quite a bit of functions, and you might actually use the majority of them. I will just cover a basic overview of how to use a few functions, so you can easily use the rest when your tinkering around in SQL. Count, sum, and average are possibly the most common and in that order. However, sometimes you will use super helpful functions like max and min. Let’s discover what they look like, then we’ll use them.

Common SQL Functions

• count() – counts the number of records returned from the query
• sum() – adds the values in records returned from the query together
• avg() – averages values in records returned from the query
• max() – finds the highest value in all of the records returned from the query
• min() – finds the lowest value in all of the records returned from the query

Our “Users” Table:

SELECT COUNT(*) 
FROM Users

As you can see, all COUNT() does is count the values in the column specified inside the parentheses. However, we used the * wildcard, so it just counted all records.

SELECT SUM(views) 
FROM Users
WHERE id <= 2 

Now, we can use the SQL Functions on columns. We said give us the sum, sum(), of our views column, but only where the column id is less than or equal to 2. The 250 is the sum of Hillary and Al’s views because their id is less than or equal to 2. Note: math functions in SQL won’t do so well with strings.

SELECT AVG(views) 
FROM Users
WHERE id >= 2 

The SQL Function average, avg(), tells SQL to crunch our numbers in the views column and give us the average, but only where the id is greater than or equal to 2. This only averages George and Al’s views, which gives us 225.

SELECT MAX(views)
FROM Users

So, the SQL Function max, obviously short for maximum, returns 300 because it is the highest number in our views column.

SELECT MIN(views)
FROM Users

Exactly on the opposite spectrum of is the MIN(), short for minimum, SQL Function. This function grabs the lowest value of our views column.

And that’s all folks. Feel free to play around with the other SQL functions

Warning: Comparing dates can make you rip your hair out. The difference of two dates provides a difficult task because the people who made our time structure obviously did not care to consider the future programmers that have to deal with it every day. Using MySQL, the task is much easier if you know a few MySQL Functions like “DATEDIFF” and “TIMESTAMPDIFF”. The idea is that the MySQL functions do all of the hard work and you just input the particular values into the function. In this tutorial, we will compare 2 dates and output the dates in the difference of days and the difference of seconds.

MySQL DATEDIFF Function

SELECT DATEDIFF(‘2013-01-14 10:59:10′,’2013-01-17 11:50:34’)*24*60*60;
#259200

The DATEDIFF function is not as robust as it appears. We provided the trailing “*24*60*60” to convert the returned value into seconds. In the next example you will see why I have done this and why the TIMESTAMPDIFF is much more accurate. The DATEDIFF can be deceiving when I put in full date times, but I do not want to confuse you. The DATEDIFF could not care less about the time string (ex: “10:59:10”), in fact you should remove them to remind yourself of the difference. MySQL looks at our 2 dates and when it compares them it sees that we have the same year and month. However, the days are different so it subtracts the second date’s day from the first date’s day (17-14), which gives us 3. Now, 3*24*60*60 = 259200 like any good mathematician would tell you. That number is the difference of days in seconds, but not the difference of seconds between the two datetimes. The TIMESTAMPDIFF on the other hand can do a datetime second comparison:

MySQL TIMESTAMPDIFF Function

SELECT TIMESTAMPDIFF(SECOND,’2013-01-14 10:59:10′, ‘2013-01-17 11:50:34’);
#262284

The result, 262284, is the real seconds between the two dates. Of course, raw seconds makes it more difficult to compute days, weeks, or even years, but you will have a more accurate answer if you put in the work. The extra work is the very reason why you see many DATEDIFFs being used when TIMESTAMPDIFF should be used.

You should definitely see the difference between the arguments of DATEDIFF vs TIMESTAMPDIFF as they are opposite. I know I was a little sneaky flipping them without telling you, but the comparison is reversed between the functions. I have no idea why they are reversed, but the creators of MySQL must have felt it was important. Personally, I like the order of the DATEDIFF’s arguments as difference is the first number subtracted from the second, but who I am to judge?

I hope this helps clear up how to compare dates accurately in MySQL.