Just Enough SQL

Today we will be talking about SQL. I’m sure you all have many questions, not the lease of which is probably along the lines of: “What is SQL?” You are also probably curious as to how knowing it can help you in some way. Will it let me control the universe? Rest assured, all of your questions will be answered in today’s tutorial. Not counting the ones that are not answer of course. Today’s course is aimed at giving you just enough information to work w/ a database. We will not be talking about advanced topics like relational databases, left/right joins, nested statements or even what a primary key is.

What is SQL? The simple answer is: Structured Query Language. Got it? You should know that it’s pronounced “Sea-Quill” or just “S-Q-L” neither is “correct”. Good, let’s move on. Oh, I can hear the wails already, “but… what does that mean?” The concept is very simple really. SQL is a language, used to query a database. I’ll say that again because it bears repeating: SQL is a language. SQL is not a database, it’s what you use to query the database. MySQL, MSSQL, Oracle, etc. Those are databases.

How Do I make a database/table? This actually tends to vary with each database you choose to go with, there are some commonalities – however we won’t be talking about them. This text is geared towards people who already have a database to work with. If you don’t you should probably start somewhere else. If you really want to know how to create a database I suggest you take a look at the documentation that surely came with your software. MySQL has an extensive collection of documentation on their website.

You now have a database and are ready to do… something with it. First off let’s define something. A database is a collection of tables, all the information is stored in tables and that’s what you’ll be preforming most of your operations on. Most everything you’re going to do with your tables boils down to a few commands, which invariably take an extraordinary number of permutations. These are “SELECT”, “INSERT”, “UPDATE” and “DELETE”. Now, I use the word “commands” here loosely. Before I start getting ahead of myself let’s lay out probably the most basic “statement” (that’s the industry term for your query) and dissect it:

SELECT

SELECT * FROM table;

Things to keep in mind while looking at this particular example include capitalization and the semi-colon. You will no doubt notice the keywords of the query are all capitalized, this is by convention and not required though you’ll almost never see a database designer/engineer not capitalize. As I mentioned the other thing you probably noticed is that the statement ends with a semi-colon, this is actually database specific but it’s so common that pretty much everyone accepts it as the end of a statement and is usually required.

I bet you want to know just what that statement will do though, don’t you? This statement will return every column for every row in the table. It’s how you, in SQL, say “show me everything”. SELECT is how you retrieve data from the table. While that’s nice to know it doesn’t seem very practical. How often do you really need *all* the data in your table dumped? Most often you only want some parts of the data. How you do that is by introducing conditional clauses. Here’s another SELECT statement:

SELECT `title`,`price` FROM `Books` WHERE `author`='John Doe';

Now we have really really drilled down the information we want retrieved. In this example we want the title and price columns from the table “Books” (note: table names *are* case sensitive) where the author is John Doe. Another change you’ll notice is that column and database names are surrounded by backticks (`). This is so that the interpreter doesn’t confuse them for SQL reserved words. If you were to have a column named “select,” for example, and you didn’t escape it with backticks you’d quickly run into a parsing error. Let’s say that our return, while more refined, still returns multiple rows. Let’s look at a couple ways we can sort that information.

SELECT `title`,`price` FROM `Books` ORDER BY `title` DESC;
SELECT `title`,`price` FROM `Books` LIMIT 0,5;

The first of these examples shows us how to sort the data that is returned to us in descending (DESC) alphabetical order according to the book’s title. The ORDER BY clause, by the way, can take a number of arguments to settle ties. For example “ORDER BY `author`,`title`” would first sort by the author’s name then by the title of their books. Note: You don’t have to have ORDER BY in order to use DESC, (or the opposite: ASC – which is implied by default) it can be tacked on to the end of any statement to sort your data for you, very handy.

The next example does something a little different. Instead of sorting the information we get back it’s, instead, limiting how much we get back. Specifically it’s saying give me the first 5 results starting with the 0th (aka: first). You’ll notice I didn’t use an ORDER BY or ASC/DEC modifier. THIS IS A BAD IDEA! NEVER DO THAT! A database doesn’t store your information in any specific way. One day running that command may return one set of information and the next a completely different set. You must always include some sort of soring if you’re going to use LIMIT.

So far you’ve only seen WHERE used with a conditional equals. That is, when something is exactly something. Of course that’s not the only way you can use WHERE. Let’s look at the main five:

SELECT `title`,`price` FROM `Books` WHERE `isbn`='0672327651';
SELECT `title`,`price` FROM `Books` WHERE `isbn`!='0672327651';
SELECT `title`,`price` FROM `Books` WHERE `isbn`<0672327651';
SELECT `title`,`price` FROM `Books` WHERE `isbn`>'0672327651';
SELECT `title`,`price` FROM `Books` WHERE `title` LIKE 'MySQL%';

The first of these we already went over and doesn’t need any further explanation. The second will look very familiar to any programmers in the audience. The != is very common and means “not equal” in this case it’s like saying “Give me the titles and prices of every book that doesn’t have the isbn 0672327651. Then you have the less than/greater than ones (yes < = and >= are valid). The last one is a little special. LIKE is used to find stuff that is kinda like what you have. In this case we have the string ‘MySQL%’ which will return all the books that have a title that starts with “MySQL”. The percent sign % is what’s known as a “wild card” in this case for matching anything and everything (even nothing), in “standard” regex it’s the equivalent of the asterisk.

The other wildcards include the underscore (_) which matches anything once, brackets ([]) which specifies a set of characters it can match and can be negated w/ a prefixed ^. For example:

SELECT * FROM `table` WHERE `column` LIKE '[^AB]%'; 

That would match any entry in “table” where “column” does not start with either A or B. You should know MSSQL likes to be different and uses a * instead of a % and a ? instead of a _.

We can still modify our WHERE clauses a little more though. We can use “OR”, “AND” or “NOT” to further refine out queries. For example:

SELECT * FROM `table` WHERE `column` != 'Goat' AND `column2` != 666;

This should be pretty self explanatory, but just in case, really brief: It will return every row where column does not equal goat and where column2 does not equal 666.

In a nutshell, that should be enough to do 95% of your data retrieval. The next big one we’re going to look at is the INSERT statement. This will take almost no time, then we’ll move right onto UPDATE.

INSERT

INSERT lets you add data to your tables, very useful. It’s also very simple. The basic syntax is:

INSERT INTO `table` VALUES(`all`,`your`,`values`,`in`,`order`);

Now, most texts will tell you not to do this, as you have to keep everything in order and it’s really easy to screw stuff up. If you were to forget a column, even one that’s NULL, you’ll end up with errors. Then if you end up changing your database scheme down the line you’d have to rewrite all your code to reflect the changes in column names/numbers. The “correct” way goes like this:

INSERT INTO table (`column1`,`column2`) VALUES (`something`,`somethingelse`);

This has the main advantage of specifically correlating columns to data. Many times you’ll also have columns that have default values assigned. So you can save yourself some typing by only mentioning the values that need to be specified and letting the database fill in the rest.

UPDATE

Ok, told you it would be fast. As I promised we’re now going to talk about UPDATE. Also very useful, it allows you to modify existing data. Say you accidentally made a typeo when you INSERTed something. Or your database contains dynamic data like age,address,etc. Here’s how we go about doing that:

UPDATE `table` SET `column1`='something new', `column2`=3 WHERE `column3`="bob";

Straightforward. To give a column a new value you need to use UPDATE in conjunction with SET which tells you just what you want to change. It’s very important to remember your WHERE clauses here. Generally speaking if you’re updating you want to update just one record in the table. So make sure you have a tight WHERE clause. You might want to try it as a SELECT first until you get the hang of things to make sure you’ve got the specific record you want. If you were to have forgotten the WHERE on the previous statement it would have updated every row in the table. Bad UPDATE statements are a good way to totally screw up a table.

DELETE

The last big thing we’re going to talk about today is the DELETE statement. This statement looks a lot like the SELECT, just with the added peril of UPDATE. To delete a record from your table just use a statement like this one:

DELETE FROM `table` WHERE `column`="evil monkey";

You tell the database which table you’re deleting a record from, and then use a WHERE clause to specify where. Obviously screwing up the WHERE can have hazardous consequences, even worse than screwing up an UPDATE which might only screw up a couple columns.

Functions

Finally I want to talk about functions. Each database out there has a number of these, a lot are database specific, but there’s also a number of universals out there. COUNT() is a good example, or SUM(). If you want to know how many records your table has you can use the popular:

SELECT COUNT(*) FROM `table`;

Let’s say instead you want to find out how much you owe on all your bills:

SELECT SUM(`owe`) FROM `bills`;

You get the idea, there’s many different functions for just about anything you can come up with and you should check your documentation to find out what they support. You can also create your own functions, or what are known as “Stored Procedures”. The way you go about creating the, however, varies wildly by database and even table type within the database so check your documentation for further information. There’s even a whole book dedicated to stored procedures in MySQL.

What can I do w/ SQL?

SQL is very important in any modern website. If you’re going to do… any webdev short of little geocities (RIP) sites you’ll probably want to be using some kind of database. Every e-commerce site without exception uses some database. In fact, this little SQL intro was targeted at people working on webdev who probably already have a database and want to learn how to manipulate it for their site. You don’t have to be in to webdev to find a use for databases though. I personally keep databases for all my DVDs, Comics, Music and Books. I also keep one for a wishlist of books. Granted I have made a web front end for all of these, but there’s nothing that says you have to do that. If you start getting adventurous you can do lots of interesting things w/ SQL, even Solve Sudoku. I’m sure you’ll find some way you can put it to use. Thank you for your time.