A friend of mine was just learning web programming, most web applications rely on databases to hold their data. I put this together as a way to introduce the basic concepts.

Databases are nothing more than a collection of records. Each record contains a predefined set of values. You can think of them as a fancy spreadsheet.

Lets consider this spreadsheet or table as though it was a database:

ID	FirstName	LastName	Organization	Position
1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
3	Bear	Walken	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative
5	Cannon	Vulcan	Lightning	Executive

The entire spreadsheet would be called a Table in a database, each row would still be called a Row, and each column is called a Field.

To interact with a database you submit text commands to it using SQL, Structured Query Language. All database commands from creating a table to adding and deleting rows are done through SQL. Each database server; Oracle, MySQL, Microsoft Access, etc, has subtle differences in the SQL statements they use. However, most of the common commands are the same.

Lets take a look at a few of the simpler commands. Lets assume that the database table shown above already exists and we’d like to work with it. First off we need to know the name of the table, for the purposes of the example lets pretend the name is Characters. The name would have been assigned to it when the table was created.

Now lets retrieve some data from the table. This is done with an SQL SELECT statement. This command looks like this:

SELECT * FROM Characters;

It is common practice to put all the SQL verbs in upper case letters. I’m going to follow that convention here so the SQL verbs are distinct from the other parts of the SQL statement. This isn’t required, SQL is not a case sensitive language.

Lets go over that statement piece by piece. The word SELECT defines this as a select statement, meaning we’d like to retrieve data from the database. Where the * is would normally be a list of fields you’d like to retrieve. Putting a * there means we’d like to get all of the fields. The FROM specifies which table we’d like to retrieve the data from, in this case its the Character table. All SQL statements end in a semi-colon, so its always included.

The database server would return all the rows that matched our SQL query. Since we didn’t provide any special filters, all of the rows would match. The server will not return the field names as part of the data, so what we would get back is this:

1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
3	Bear	Walken	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative
5	Cannon	Vulcan	Lightning	Executive

Lets assume that we want to get only the records whose Organization field is Milennion. We would add a WHERE clause to our SQL query. So it would look like this:

SELECT * FROM Characters WHERE Organization LIKE 'Millennion';

The word WHERE means we’d like to filter the rows returned, and the parts that follow define the criteria to use. The criteria are always set as a field name, a comparison, then a value. We’re using the comparison LIKE, which applies to text comparisons. The LIKE comparison will allow us to use wild cards as part of the value. Each SQL server uses different values for the wild cards, * and % are common characters used. We didn’t use wild cards so we’re looking for an exact match. The string we are comparing to is encased in single quotes to ensure its not confused with an SQL verb. With this statement the database server would return:

1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
3	Bear	Walken	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative

One last example of select statements before moving on. Lets only get the FirstName and LastName fields from records whose Organization is Millennion and whose Position is Executive. This query would look like this:

SELECT FirstName, LastName FROM Characters
          WHERE Organization LIKE 'Millennion' AND Position LIKE 'Executive';

The fields we want are just listed by their name and separated by commas. We’ve also added a second filter in the WHERE clause. Each criteria in the filter is separated by AND or OR. Using AND means that the record must match both criteria, using OR means the record can match either one. You can also use parenthesis to group the criteria so that the AND and OR can act on specific criteria. This statement would return:

Harry

MacDowell

Next lets take a look at updating database records. This is done with an UPDATE statement. Lets say we wanted to update all records so that the Organization field was Millennion. The statement would look like this:

UPDATE Characters SET Organization = 'Millennion';

Lets go over this piece by piece. The verb UPDATE means that we’d like to modify existing records. Then you have the name of the table we’d like to edit. The SET verb is followed by all of the fields we’d like to change. The fields are listed by field name, an equals sign, then the value we’d like the field to have. in this example we’d like it to make the value the text Millennion, once again the text value is enclosed in single quotes.

Update records will update every record in the table that matches the criteria in the statement. We didn’t provide any criteria so it will update every record. This statement would change the table to:

ID	FirstName	LastName	Organization	Position
1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
3	Bear	Walken	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative
5	Cannon	Vulcan	Millennion	Executive

Since all the other records already had Millennion in the Organization field, only one record is changed.

Just like the SELECT statement, you can supply a where clause to make sure only specific records are updated. For example, this statement:

UPDATE Characters SET Organization = 'Lightning' WHERE ID = 5;

Will change the table to:

ID	FirstName	LastName	Organization	Position
1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
3	Bear	Walken	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative
5	Cannon	Vulcan	Lightning	Executive

Just to point it out, the number in the where clause is not marked up in any way. Since no SQL verbs use numbers the database server knows how to treat them.

Nex I’d like to go over how to delete a row. This is done with a DELETE statement. This statement looks like this:

DELETE FROM Characters WHERE ID = 3;

Like the SELECT and UPDATE statements the DELETE statement uses a WHERE clause. Without that clause it will delete all the rows in the table, so you’ll almost never want to do a delete without one. After the example runs our database would look like:

ID	FirstName	LastName	Organization	Position
1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative
5	Cannon	Vulcan	Lightning	Executive

The last SQL statement I want to bring up here is the one to create new records, this is an INSERT statement. This one looks different from the previous ones. Here is an example:

INSERT INTO Characters (ID, FirstName, LastName, Organization)
          VALUES (6, 'Bunji', 'Kugashira', 'Millennion');

INSERT statements don’t use FROM, instead they use INTO to specify which table the new record belongs in. Then inside of parenthesis is a comma separated list of all the fields you’d like to put values when the record is created. Notice that not all of the values have to be listed, in the example I skipped the Position field. Then there is the word VALUES followed by a comma separated list of the values to place in the record. The field names and values must be in the exact same order. Once this command executes our table becomes:

ID	FirstName	LastName	Organization	Position
1	Harry	MacDowell	Millennion	Executive
2	Brandon	Heat	Millennion	Sweeper
4	Bob	Poundmax	Millennion	Intelligence Operative
5	Cannon	Vulcan	Lightning	Executive
6	Bunji	Kugashira	Millennion

Thats just the basics of working with SQL. Each of those statements can become a lot more complex depending on the structure of the database and what you need to do.