If you haven't already done so, please read some database tutorials. This SQL tutorial assumes that you understand the concepts of relational databases.
SQL is a TLA. That is, a Three Letter Acronym. It stands for Structured Query Language. SQL was developed in the 1970's by IBM and has since become the industry standard for accessing relational databases. It is a programming language, meaning that it has rules and a syntax of its own. The good news is that it is a very simple language to learn, and it is very similar to English. The bad news is that it has the potential to get complicated at a more advanced level. At this point, we will only be looking at the basics of SQL - the advanced stuff can come later.
There are many different forms of SQL, and most database companies (Oracle, IBM, Microsoft, Paradox, Sybase et al) have slightly different implementations of the language. There IS an ANSI standard, but in recent years, this standard has become so large that it is almost impossible for even the largest companies to implement the full standard. Thus, they tend to implement the CORE parts of the SQL standard, and in some cases add a few bits and pieces of their own. That said, if you know SQL, you can use it to access nearly any relational database with very few changes to your code.
As we said above, SQL is the standard way of interacting with databases. If you have information stored on a database somewhere, or you want to put information into a database, you will need to use SQL to make that interaction. Consider for a moment that you are running a web site which includes a members only area. How do people log in to this area? They use a form which requests their user name and password. The back end of this form checks the name and password against values in a database. If the password entered matches the password in the database for that user name, then the form lets them enter the restricted area. This interaction is done with SQL by creating what is called a 'query'. All transactions in SQL are called queries, whether they be actual queries in terms of retrieving information, or whether they relate to updating, deleting or creating sections of the database.
Let's use the above example, and say that we have a web site which contains a restricted 'members only' section to it, and we will use SQL to not only verify a users identity, but we will also use it to create dynamic sections of the site. First of all we need a database.
You can actually use SQL to create a database, even online, but we'll focus on the basic SQL interactions with the database in this tutorial. So we'll assume that you already have a database with several tables - users (which holds user data), news (which holds your news items), and photos (which holds the file names of your photos). We'll deal in this tutorial with how to retrieve and update data from these tables on a direct access level. You will then be able to access your database from a direct interaction with your DBMS, or by inserting your SQL into PHP or ASP pages.
SQL contains several types of statements. The ones we deal with here are part of the Data Manipulation Language (DML). SQL also includes a Data Definition Language (DDL), and several transaction and access control type statements, but these go beyond the scope of this tutorial.
The structure of an SQL query is as follows:
SELECT (list/all) FROM (table) WHERE (condition) GROUP BY (column) HAVING (condition) ORDER BY (order).
Seems a little confusing at first, but it is very similar to English once we start filling in some of the things in the brackets.
It is customary to use capital letters for SQL statements such as select, but SQL is not case sensitive. HOWEVER! The values in the database are. If you use a condition like ='Smith', this is not the same as ='smith', or ='SMITH'.
Okay, let's see what we've got stored in our users table. All we need in this is the first two part of the structure above - SELECT, and FROM.
SELECT * FROM users;
Pretty simple, hey! This query tells the database that we want to SELECT everything (*), from the table called users. Many DBMS' require the use of a semi-colon (;) at the end of a query, while others do not. Read your documentation, or simply try it out to see what works.
So our query will return something like this:
ID userName userPass dateJoined adminLevel email 1 jsmith monkey 01012001 1 jsmith@hotmail.com 2 mjones banana 05122002 4 mjones@yahoo.com 3 jdoe pickles 11062003 5 jdoe@excite.com 4 fnerk apples 01042001 1 fnerk@google.com
That gives us heaps of information (well, not that much in this example, but you can imagine how big this would be with a full sized database!). Let's say that instead we want to just find out the username and password of the people in the database. Again, a really simple query:
SELECT userName, userPass FROM users;
We are telling the database that we want the userName and userPass information from the table called users.
Our output would be something like this:
userName userPass jsmith monkey mjones banana jdoe pickles fnerk apples
Certainly cuts things down, doesn't it.
Next lets expand our query a little bit to the next keyword - WHERE.
Okay, say we want to know which users have an admin level of 1. Obviously you could use what you already know, and do a SELECT * FROM users;, but as we've seen above, this could quite easily get VERY large indeed.
What we need to do is narrow it down. We know what we want - userName -, and we know it's in the users table, so we've already got SELECT userName FROM users;, but we also have a condition - they must have an admin level of 1. So, build on the basic query and we get:
SELECT userName FROM users WHERE adminLevel = 1;
Once again, be aware that some databases treat numbers differently, and it will depend on how your adminLevel column is defined in the database, but as a general rule, letters or characters need quotes around them in a query, whilst numbers do not.
So, our query returns the following information:
userName jsmith fnerk
That tells us that they're the two people in the users table who have an adminLevel of 1.
The GROUP BY and HAVING keywords tend to be used in more advanced queries, so we'll move on to the ORDER BY keyword.
ORDER BY is exactly what it sounds like. You're telling the database that you want your results in a specific order. Let's use the above example again, selecting user names and passwords from the database, but this time, let's order it alphabetically by user name.
SELECT userName, userPass FROM users ORDER BY userName;
Simple? SQL knows how to order things like words and numbers (and even dates). The default behaviour of the ORDER BY keyword is ascending (ie: 0 - 9, a - z), but you can put things in descending order instead if you like.
The above query returns:
userName userPass fnerk apples jdoe pickles jsmith monkey mjones banana
If we wanted the names in descending order, we simply add DESC to the end of the query like so:
SELECT userName, userPass FROM users ORDER BY userName DESC;
This tells the database to give us the results in descending order.
userName userPass mjones banana jsmith monkey jdoe pickles fnerk apples
You've got a form in which the user signs in to view restricted content. Your form includes the fields "user name", and "password". You process the form in PHP or ASP, but you need to check the database to make sure that you've got the right person.
Your pseudo code would look like this:
1 get the user name and password from the form 2 get the password for that user name from the database 3 if the password in the form and the password in the database match go to restricted page else go to error page 4 end
You know how to write the code, or are going to check in one of our other quality tutorials, but how do you write the SQL for that second step?
We know we need the password, we know it's stored in the users table, and we know that the condition will be that it is the password for the relevant user name.
Let's say you've stored your user name from the form in a variable called "uName". Your SQL would look like this:
SELECT userPass FROM users WHERE userName = '" + uName + "';
See how we have a single quote around the variable name? Then there's a double quote to pause the SQL query and read the variable name, then another double quote to finish off the query. Imagine that the user name from the form is "fnerk". Your query as sent to the database looks like this:
SELECT userPass FROM users WHERE userName = 'fnerk';
The database returns a value of "apples", and you match it to the input from the form. Isn't SQL simple?!
SQL only really gets things from the database - what you do with them after that is your own business. However, SQL will also enable you to put things INTO the database. Again, it's pretty straightforward, but the syntax is slightly different this time.
INSERT INTO (table) VALUES (list);
This is about as easy as it gets. There's a couple of things to watch out for, but we'll get to those as we go along.
Okay, so you've got a new user, and you want them to be added to the database. We'll assume for integrity's sake that you require all fields to be completed in your database.
Your query would look something like this:
INSERT INTO users VALUES ('swonder','blaster','01012004',3,'swonder@masterblaster.com');
Notice that we don't insert the ID number in the query? This is because you should already have it set up as an auto-incrementing primary key. Try to include it in your query, and your query will fail. The other thing to note is that you might just want to use todays date as the dateJoined value in your table. In that case, you can substitute the date value for the date variable in your programming language (eg: $Date etc).
What else can INSERT do? Not much, but there's a couple of things to be aware of. If you have default values set up in your database (for example, your default adminLevel might be 5), you can use 'default' in place of any value which has a default. For example:
INSERT INTO users VALUES ('swonder','blaster','01012004',default,'swonder@masterblaster.com');
Another way to do exactly the same thing:
INSERT INTO users(userName, userPass, dateJoined, email) VALUES ('swonder','blaster','01012004','swonder@masterblaster.com');
Notice that in this example, we've specified which fields to update, and only included values for those fields. You can also use the 'null' value if this is allowed in your database.
You might want to insert multiple values into your database at the same time. Perhaps you're uploading a few pictures to your photos table.
INSERT will allow you to add more than one row by doing this:
INSERT INTO photos VALUES ('photo1.jpg','A Nice Photo','01012004'),
('photo2.jpg','Another Photo','02022004'),
('photo3.jpg','More photos','03032004');
You get the idea, right? This inserts three new rows into your photos table in your database with a filename of photoX.jpg, a short description, and the date taken for each one. The thing to be aware of is that if you have an error with ANY part of this statement, NONE of it will work!
There's two other basic SQL operations, before we finish this tutorial off and move on to some more intermediate SQL. They are UPDATE and DELETE. Can you guess what they do? UPDATE changes an existing row, and DELETE, strangely enough, will delete a row.
So, say your user wants to change their email address in the database. How? Well, UPDATE provides the answer.
Say fnerk wants his email address to become zeus@olympus.com, you would do something like this:
UPDATE users SET email = 'zeus@olympus.com' WHERE userName = 'fnerk';
Simple, isn't it. We're going to update the users table by make the email address read zeus@olympus.com for the row which has a userName of fnerk. You must be aware though, that if you had more than one user called fnerk (hopefully you check for this and make sure that you don't, but just in case), it will update the email address for every fnerk in the database.
Now lets say swonder is over your whole website, and wants to delete himself from the database. Simple process.
DELETE FROM users WHERE userName = 'swonder';
This quite simply deletes the row in your users table which has a userName value of 'swonder'. A quick warning! If you leave out the where clause like so:
DELETE FROM users;
It will delete EVERY ROW IN YOUR TABLE! Be careful with this command, and make sure your database is always fully backed up.
In the next tutorial, we'll look at more complex SELECT statements, including using the GROUP BY and HAVING clauses, and how to create counts of things in your database. We'll also have a look at some basic joins and subqueries.
Enjoy your SQL experience.