UPDATE Query

The SQL statement UPDATE is a crossbreed between INSERT and DELETE. It doesn’t exactly do either of them, but it has similar properties of each. UPDATE searches for whatever record or records that satisfy its WHERE condition. Then the update statement overwrites each record’s columns that it was told to. The way the developers created the SQL update statement was brilliant and much better than the INSERT statement. Remember INSERT had those two separate lines in parentheses. Take a look at how much better UPDATE looks:

Updating SQL Records

Our table before:

id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15
Example
UPDATE table_name
SET username = ‘rustyNails’, password=’hammers’
WHERE username = ‘rustyMeerkat’
Result
id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyNails hammers 1995-09-15

Boom! You just changed a username and password. A highly insecure way to do it, but for our purposes, it will do. First, we start off by declaring the state with UPDATE to tell SQL were going to change some existing data. Next, we use the keyword SET followed by a column name set to a value. Then, you can use a comma and repeat that for as many columns as you want to update in those records.

Oddly enough, we tell SQL which records to update at the very end with our WHERE condition. Dreams and hearts have been broken by this organization. Don’t ever, ever, ever leave off the WHERE at the end unless you want to update every record. That was one of the saddest few seconds of my life. I left off the where statement at work while putting it into production, and watched in slow motion as the data was overwritten. Luckily, we have an pretty awesome backup system. Please never ever do it and always remember to back up your data before attempting data changing queries.

DELETE Query

Now, we meet the most dangerous SQL statement, DELETE. DELETE removes existing records from tables. Many nightmares have occurred because of the DELETE statement. I strongly recommend testing your DELETE statements in another test database before you run the command in the real database. SQL does not provide some magical way to recover deleted data because that is your job as a web developer. Programming and database languages are super smart, but they can’t save you from everything. Enough of the warnings, you will learn on your own… Example please:

Deleting Records in SQL

Our table before:

id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15
Example
DELETE FROM table_name
WHERE username = ‘rustyMeerkat’
Result
id username password birthday
1 bobdole32 secretP 1984-06-01

It’s pretty easy to understand; however, I need to stress Don’t leave off the WHERE. If you leave off WHERE, you will remove all of the records. The DELETE FROM is the standard introduction into the statement that SQL understands. As used before,WHERE is the conditional. Deleting isn’t very difficult, but please be careful. It is good practice to slowly build up your delete queries and quickly changing your DELETE statement to a SELECT statement to see what kind of data you will be deleting. After you see that the SELECT statement returned what you want to delete, you can finally switch it back to a DELETE statement.

Multiple Conditions

Enough of these simple short and sweet SQL Statements. It’s time to discover how to implement multiple conditions by using AND and OR in our queries. AND and OR are used in a very large amount of statements, especially user authentication. We have to check a username and a password. If you are familiar with programming logic, this tutorial will be a piece of cake for you. If not, it’s alright I will go into enough detail for you to understand.

  • AND – both conditions must be true
  • OR – either one of the conditions must be true

Using AND in SQL Statements

Our table before:

id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15
3 rustyMeerkat copyCat 1995-09-15
Example

SELECT *
FROM table_name
WHERE username = ‘rustyMeerkat’
AND password = ‘digholes’

Result
id username password birthday
2 rustyMeerkat digholes 1995-09-15

A standard SELECT is used until we reach the AND. We would have expected it to returned both records with ‘rustyMeerkat’ as the username. However, the AND says, “Hey, hold up. I have something else.” SQL then realizes, we have another condition and checks it. The aha moment occurs, and we return one record that satisfies both of the conditions. Make sure that you are certain when you use an AND statement. Nothing is more frustrating in SQL than having a large query that returns nothing when you run it. You just scratch your head and slowly raising your voice at SQL.

Using OR in SQL Statements

Our new table:

id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15
Example

SELECT *
FROM table_name
WHERE username = ‘rustyMeerkat’
OR password = ‘secretP’

Result
id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15

The SQL keyword OR is considerably different than AND because OR loves everyone while AND is a jerk. We still have two conditions, but this time we want the records that have a username of ‘rustyMeerkat’ or a password = ‘secretP’, which, in this case, is both records. Get comfortable with these two commands. You will use them with a fair chunk of the SQL you will be writing. It is also important to know that you can use them inside parentheses. That gives you even more control over your WHERE statement. Let’s take a look at what I am talking about:

Example

SELECT * FROM someTable
WHERE column1 = “pickles” AND (column 2 = “possible value 1″ OR column 2 = ” possible value 2″)

We already understand the column1 = “pickles”, but the AND with parentheses behind it is new to us. In the parentheses, we have 2 conditions separated by an OR statement. If either one of these are true, the condition after the AND statement will return true. So, we can still have the strict nature of AND, but we can provide options with OR to make our SQL query a little more robust.

ORDER BY and GROUP BY

Now, that we can extract data like little masterminds of SQL, but what good is getting data if we can’t organize or understand it? SQL’s ORDER BY, GROUP BY, and DISTINCT are a few of the cornerstones in SQL for managing and organizing the data received from the database. ORDER BY handles the order the records will be returned. GROUP BY finds similar results and clumps them together. DISTINCT prevents the return of duplicate data. Getting excited? Me too! Example time:

ORDER BY SQL Keyword

We’ll just use one big table for all of these:

id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15
3 rustyMeerkat copyCat 1995-09-15
4 bobdole32 secretP 1984-06-01
5 rustyMeerkat digholes 1995-09-15
6 bobdole32 copied 1984-06-01
Example
SELECT * 
FROM table_name
ORDER BY username
Result
id username password birthday
1 bobdole32 secretP 1984-06-01
4 bobdole32 secretP 1984-06-01
6 bobdole32 copied 1984-06-01
2 rustyMeerkat digholes 1995-09-15
3 rustyMeerkat copyCat 1995-09-15
5 rustyMeerkat digholes 1995-09-15

Ah, the power of ORDER BY still amazes me. Before we used the ORDER BY, it was just going to return all of the records with every column. By adding ORDER BY and setting it equal to username, SQL looks at the value type, which it sees that it is a varchar(data type in SQL to represent short strings). Since it is alphabetical, SQL will order them from a-z. That’s why ‘bobdole32’ appears first. Now, what if we wanted it to return from z-a? Two more important keywords are commonly used with ORDER BY, they are DESC for descending and ASC for ascending. SQL defaults with ascending order. You would just implement it like so: ORDER BY username DESC.

GROUP BY SQL Keyword

Example
SELECT * 
FROM table_name
GROUP BY username
Result
id username password birthday
1 bobdole32 secretP 1984-06-01
2 rustyMeerkat digholes 1995-09-15

Whew! Finally, we have more manageable results. If you look closely, you will see we lost a considerable amount of data in the process. We now only have 2 requires because we only had 2 different usernames. Now if I remember correctly, I don’t believe GROUP BY is as predictable as it looks in this example. I think that I heard somewhere that in those 2 groups of similar usernames, it actually selects a random record to display rather than simply choosing the first one. Typically, GROUP is used with functions. For instance, it might be used with a SUM() function to create one row that would tally up the age of all ‘rustyMeerkat’ usernames. Of course, we’ll get to that later. Basically, you just need to understand that using GROUP BY does what it says, it groups by the column name you give it.

DISTINCT SQL Keyword

Example
SELECT DISTINCT username
FROM table_name
Result
username
bobdole32
rustyMeerkat

DISTINCT is slightly awkward in it’s position, but I’ll explain why it must be there. DISTINCT is intended to be used directly after declaring the statement as a SELECT. This permits SQL to see what columns must be unique in order to show the results. If you were to put in multiple column names like, SELECT DISTINCT username, password, you would see 4 records. It is because it looks at all of the columns you specify and determines if in any given records are the same as another record. Since we added more columns, you are less likely to have matches, which means SQL will return more records. Pfft. That wasn’t too bad.

LIKE

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:

id content
1 I am some sample content
2 sam
2 This isn’t the best default text
Example
SELECT * FROM table_name
WHERE content LIKE ‘%am%’
Result
id content
1 I am some sample content
2 sam

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:

Example
SELECT * FROM table_name
WHERE content LIKE ‘_am’
Result
id content
2 sam

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.