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 bothered 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 fucking 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