Code:
Introduction to SQL
By Niels|nielsosky@gmail.com
==================================================================================================
This tutorial may be redistributed and hosted on other servers as long as it remains completely intact and full credit is given to me, Niels
==================================================================================================
Shoutouts
Special thanks to Subby for doing some much needed proofreading! Also, thanks to Mu, Zugg, sumone, Terencentanio, eclipse, Tele, elecktricity, and everyone else at the Binary Universe forums.
==================================================================================================
SQL, pronounced either as the letters S-Q-L or as "sequel", stands for Structered Query Language, and is used to communicate with databases. While there are many different database management systems, such as MySQL, Postgre SQL, and the list goes on, the SQL used to communicate with them is all based on a standard, ANSI SQL, which is what this tutorial is about. There are places where a slightly different SQL query is used from one DBMS (database management system) to the next, and I'll point those places out when we run across them, but they are few and far between.
If you're intimidated by the idea of learning a new programming language, SQL, never fear. SQL is much easier to pick up than a true programming language, such as Java, C++ or Perl, and the syntax is very close to English, as you'll soon see. This tutorial only covers the very basics, and when you've completed this there will be room to go farther. However, if you read a couple paragraphs of this and decide it's no help, and sucks, there are two things you should do. One, you should quit reading it, and go read another SQL tutorial. Two, you should contact me telling me that it was hard to follow, or just plain sucked. I encourage you to contact me with your feedback. Without further ado, let's start learning SQL!
SQL uses tables to organize data. Tables in SQL consist of columns and rows. Rows can also be referred to as 'records', the two terms can be used interchangeably, and I will use them so throughout this tutorial. A simple table listing information about graphics design software, might look like the following:
graphics
------------
software price
------------------------------
Photoshop 649
Paint Shop 119
Flash 499
GIMP 0
The name of the table is graphics, and the columns are Software and Price. The base of SQL queries is the SELECT statement, which is used to extract data from a table. The SELECT statement is used as follows:
SELECT column
FROM table;
The SELECT part tells the database which columns to select. Let's say we wanted to select the columns software and price from our table. We would use 'SELECT software, price'. The FROM clause tells us which table to select the given columns from. To continue with our graphics example, we would end it with 'FROM graphics' to show that we wanted the columns software and price selected from the table graphics. Thus, to select the software and price columns (which is actually all the data in this case), we use:
SELECT software, price
FROM graphics;
Broken down into English, this says "Select the columns 'software' and 'price' from the table 'graphics'. See what I mean when I say SQL is very English-like? However, if we want to select all the columns, like in our example here, we don't have to type each one out. We can simply use the wild card, *. The following query means exactly the same thing as the previous one we used, and returns the same results:
SELECT *
FROM graphics;
Note that as good practice you should always end your statements with a semicolon. Although not all database management systems require this, some do. Just as some do, a special few don't, so if you're getting errors even when you're sure you've typed everything correctly, try it without the semicolon. Let's take this example a step further now. Suppose we want to only select products that cost less than $200. We could use the WHERE clause:
SELECT *
FROM graphics
WHERE price < 200;
The WHERE clause says "where this statement is true". Thus, the previous query into English, it says "Select all rows form the table 'graphics' where the column 'price' is less than 200. However, we can use other operators, not just the less than operator with the WHERE clause. The following is a table of other conditional operators we can use:
< less than
> greater than
= equal to
<= less than or equal to
>= greater than or equal to
!< not less than
!> not greater than
<> not equal to
!= not equal to
BETWEEN between
If you've ever used a programming language, you will be familiar with most of these operaters. If you haven't ever used a programmig language, most of these are self explanatory. Just stick the operator you wish to use in where we previously used the less than operator. For example, to find all products that cost more than $200, we would use the following query:
SELECT *
FROM graphics
WHERE price > 200;
The <> and != operators both mean 'not equal to', and can usually be used interchangeably. The exception to this is that some databases do not support both, check the documentation for the database you're dealing with just to make sure, or if you can't be ****ed to do that, simply experiment with both operators. The following two queries mean the same thing:
SELECT *
FROM graphics
WHERE price <> 800;
SELECT *
FROM graphics
WHERE price != 800;
The BETWEEN operator is one of the only operators listed that may not be self explanatory, although it does in fact have an English-like syntax. It checks wether a number is in a given range. Eg:
SELECT *
FROM graphics
WHERE price BETWEEN 1 AND 200;
This returns all rows, or in this case graphics editing programs, that are between 1 and 200 dollars. The syntax should be pretty self-explanatory: BETWEEN beginning_of_range AND end_of_range.
However, these operators are limiting. We can create even more powerful queries using the AND, OR, and IN operators. So far we've only specified one condition in the WHERE clause. Now we're going to specify two. The AND operator is used with two operands, and is true if both operands are true, otherwise it is false. For example, take the following code:
SELECT *
FROM graphics
WHERE price = 0 AND software = 'GIMP';
This returns all records where the columns software is equal to "GIMP" and the price is $0. As you can probably tell, this returns the software GIMP. However, let's say that we want to return the GIMP and Photoshop. We could use the OR operator for this. The OR operator is much like the AND operator in that it accepts two operands. The only difference is that it is true if either one of the conditions specified is true, so they don't both have to be true. Thus, to stick with our example of selecting the GIMP and Photoshop:
SELECT *
FROM graphics
WHERE software = 'Photoshop' OR software = 'GIMP';
We can also use the IN operator to do the same thing we do with the OR operator. The following query accomplishes exactly the same thing as the query we used previously with the OR operator:
SELECT *
FROM graphics
WHERE software IN ('Photoshop', 'GIMP');
Note that each one of the values to test the specified column with are seperated by perenthesis.
Now let's say that we want to find all the records where the software name begins with the letter P. In this case we know that the only values in our table where this is true is with 'Photoshop' and 'Paint Shop'. However, we do not always know what all the fields in our table our. For example, say we want to select all the usernames in a bulletin board system database that begin with P. We do not know beforehand who the registered users will be. Even in this example, perhaps we will be adding more software to the table. To accomplish our task, we can use the LIKE operator along with wildcard filtering. We used the * wildcard to represent all columns, we now use the wildcard * to represent any number of characters from 0 on up. This is in fact what it really says in all the SQL SELECT statements where we use it, and since it matches all columns of 0 or more characters, it thus matches all columns. So to find all software beginning with the letter P, we would use the following query:
SELECT *
FROM graphics
WHERE software LIKE 'P*';
The LIKE operator says not to do an exact equality search (an exact equality search in this case would return all the software called P*), but to do a search for any software where the name consists of P and then any characters after it. We can also use multiple wildcards. For example, the following query returns all software that has the letters 'ash' anywhere in it:
SELECT *
FROM graphics
WHERE software LIKE '*ash*';
Depending on your DBMS and how you have it configured, case sensitivity may be a problem or may not. If it is case sensitive, then '*Ash*' does not match '*ash*'. Also depending on which DBMS you're using, you may also be able to use the % wildcard instead of the * wildcard. They both mean the same thing. We could rewrite the previous query as the following to demonstrate the use of both wildcards:
SELECT *
FROM graphics
WHERE software LIKE '%ash%';
So far we've dealt only with retrieving data from the database using the SELECT statement, and filtering retrieved data. However, SQL also allows us to modify the contents of a database. We can insert new fields into a table using the INSERT INTO statement. To use the INSERT INTO statement, we specify which columns to insert the new data into, and what the new columns should contain, using the following syntax:
INSERT INTO table_name (column_a, column_b, column_c)
VALUES value_a, value_b, value_c;
So now, suppose we want to add a row for Macromedia Fireworks into our graphics table, we would use the following syntax:
INSERT INTO graphics (software, price)
VALUES ('Fireworks', 299);
If we don't specify the columns they are to be inserted into, they will be inserted into the columns form left to right, so seeing as we inserted values into the columns software and price in our graphics example, and that's the order these columns are in left to right, we could use the same INSERT INTO statement to accomplish exactly the same thing:
INSERT INTO graphics
VALUES ('Fireworks', 299);
And voila, our graphics table now looks like this:
graphics
------------
software price
------------------------------
Photoshop 649
Paint Shop 119
Flash 499
GIMP 0
Fireworks 299
If we were keeping a table of graphics software, we might need to change some of the values in the table, for example suppose that Adobe decided to rename Photoshop AdobeDraw. Yeah, I know it's a lame example, but it's an example nonetheless =p. We would then have to use the UPDATE statement. The UPDATE statement consists of three parts, the UPDATE statement where you declare what table to update, in the format UPDATE tablename, the SET command which dictates what to set the new values to, and the good ol' WHERE clause to dictate which field needs to be updated. So altogether, to update something, we use the following syntax:
UPDATE tablename
SET columnname = updated_value
WHERE some_columns = some_value;
Now let's take the example of updating Photoshop to AdobeDraw, we use the following statement:
UPDATE graphics
SET software = 'AdobeDraw'
WHERE price = 649;
and to update it back...
UPDATE graphics
SET software = 'Photoshop'
WHERE price = 649;
That's the UPDATE statement for you. Ok, now, remember how we added Fireworks to the table earlier? Well, guess what? We're sick of having TWO Macromedia products on there, so we're going to get rid of it (Fireworks). To do this, we need to use the DELETE statement. The DELETE statement is surprisingly similar to the SELECT statement. First we start off with the keywork DELETE to signify that we are deleting something. Next we use the FROM clause to show which table to delete it from, and finally we use the WHERE clause to show which records we want deleted. So to delete Fireworks, we use the following statement:
DELETE FROM graphics
WHERE software = 'Fireworks';
Now by this time you're probably sick of the whole ****ing graphics table, so guess what? We're going to delete all the rows in the whole table! To delete all the rows in a table, without deleting the table itself, we simply leave out the WHERE clause. So, here's the statement we use:
DELETE FROM graphics;
With no more table, and having covered the basics of SQL, this is the end of my Introduction to SQL. I will probably be writing a second part to this. I hope this helped, have fun with SQL, and don't be a scriptkiddie!
THE END
-Niels
HTML: 
Reply With Quote