You are using a browser which is not compatible with CSS (for more information, see Tara's tutorial). Because of this, it is possible that our website may not appear correctly in your browser. We apologise for the inconvenience, and recommend you upgrade your browser to one which is compatible with CSS. For more information, please visit our Browser Upgrade page.

4WebHelp

Anatomy of a SQL Statement: Part 1 by Dave

Last updated: 02/05/2012
Biography: I've been excited to discover what the world of PHP has opened up for me on the web. I participate in a number of forums, learning and sharing knowledge.
See 1 more tutorial by Dave

The Anatomy of a SQL Statement: Part 1: SELECT FROM

The purpose of this tutorial is to break down the basics of a SQL SELECT statement. I intend for this to be the start of a series of hopefully interesting, entertaining, and just plain useful tutorials on relational databases and how they work, and how to work with them.

In The Beginning, The World Was Flat

First of all, just what is SQL? SQL stands for Structured Query Language. It was developed to manipulate data stored in a relational database structure. Prior to this concept most data files were stored as "flat files" and read in sequential order. That meant that in order to find a particular record, you either needed to read the entire file throwing away data you didn't want, or know the exact offset into the file. Think of this like playing a VCR tape. Unless you know the exact counter value where your taped copy of "Coupling" is, you have to fast forward through the tape looking for it. Time consuming, isn't it?

Contrast that with a DVD broken down into chapters. You have the ability to select the exact area of the disk you want to read. Much more efficient, isn't it! Certainly makes it easier to use. Relational databases solve a similar problem. I don't want to have to know where the data is, I just want to be able to select it. And that gets us to the point of the tutorial.

The Language of SQL

There are two types of SQL statements, DDL and DML. DDL stands for Data Definition Language, and includes language used to define the database structure. DDL includes things like

CREATE DATABASE foo...
CREATE TABLE bar...
CREATE INDEX bar_PK...

... and so on. We'll talk about those in another tutorial. DML stands for Data Manipulation Language, and is much more interesting. With DML you can insert, update, delete, or... yes, even SELECT data from your relational database. The SELECT statement is what you use to retrieve data from your database, afterall, if it was important enough to store, you might want it back and sometime, right?

The most basic select statement that can be written is

SELECT *
FROM foo

That's it. By the way, for this (and any future) tutorials, reserved words (words that are part of the SQL language) will be written in ALL CAPS in code segments, and other words will be in lower case. Now that we have that out of the way, what does this statement do?

Suppose you have a table named "foo" in your database. This table stores a number of interesting attributes about one of your customers. Well, in that case, the table is probably called customers, so we'll use that from now on. (What's a table? Another tutorial, where we get to talk about entities and relationships and attributes and stuff.) So to retrieve your customer information, you would write:

SELECT *
FROM customers

What does the * do? Well, in this case it seems that we're too lazy to specify exactly what sort of customer information we want, so we're asking for everything. The * (sometimes called "splat", probably because it looks like a bug on your windshield) is a shortcut that allows you you ask for everything from the table.

What if you only wanted the first and last name for your customer, as well as their phone number? In that case you could be more specific, as in:

SELECT cust_fname, cust_lname, phone
FROM customers

The "splat" has been replaced by a number of columns from the table. Instead of viewing the entire customer row, you've been more selective. See how that works? Each attribute (column) that you want to retrieve from the table is listed, separated by commas. Now, what about those names? "cust_fname" is a bit cryptic, don't you think? Oh sure, you could probably figure it out. But why not rename it with an alias?

SELECT cust_fname AS Customer_First_Name, cust_lname AS Customer_Last_Name, phone AS Phone_Number
FROM customers

This is an optional step, but if you're dealing with complex (or obtuse) result values, an alias can be a big help. It can certainly help make your PHP code easier to read later on.

So now you know how to retrieve customer information from your database table. What if your boss comes by and wants to know exactly how many customers are in the database? You don't have to retrieve every customer and count them, simply use the COUNT() function instead. That is still something you're selecting from the database, so it goes like this:

SELECT COUNT(*)
FROM customers

Remember the splat? (That's the * if you were paying attention earlier.) Since we don't really care to retrieve anything, this is a shorthand way to ask for a count of records from the customer table. If you have a large number of customers, a more efficient way to count them would be this:

SELECT COUNT(cust_id)
FROM customers

This assumes that each customer has been assigned a unique customer id (called cust_id) and that this value is mandatory (not null) in the database. Why would this be faster? Without going into too much detail, suffice it to say that counting the entire customer record can require retrieving the full 1,000 bytes from the database, while counting the unique ID counts just that one column. Depending on how your database is tuned, it may not even use the table at all, but count the INDEX instead. (What's an INDEX? Yet another tutorial subject...)

The COUNT() function is one of several group functions available in a SQL database. What if you wanted the first (oldest) and last (most recent) customer dates? That might look like this:

SELECT MIN(cust_add_date) AS First_Customer_Date, MAX(cust_add_date) AS Most_Recent_Customer_Date
FROM customers

Yes, you can use the same customer information more than once. In this case, the results of this SELECT statement will be a single row of data that will include the date that the very first customer was added, as well as the date the most recent customer was added. Note the use of an alias to make the expression more specific? Always a good idea. We'll cover these special functions in much more depth in a later tutorial when we cover the GROUP BY clause of a SELECT statement.

What if we want to return data from more than one table at a time? What if we want to restrict data from the customer table to rows that match certain criteria? What if... well, that's the subject for the next tutorial: The Anatomy of a SQL Statement: Part 2: WHERE, coming soon.

© 4WebHelp and Dave

Latest comments on this tutorial
Jan Pappas
Hi Dave,
This is a great intro to SQL. Do you mind if I share it with my co-workers (NOAA Fisheries)? Thanks for putting it together,
Jan  
bmabinijr
This is GREAT!
Where is Part 2?!
Dave Walters
Thanks Dave! Wow, you make it so simple that all I can say is 'got it'! Good work and will be looking for your next post. Maybe you already have, but you should put your information in an instructional book on PHP and MySql. They seen to be the way of the future in web site design. Thanks again for the contribution!
Totally Newbie
Learning SQL and finding it a mine field  - i need all the help i can get. Problem is i am learning it to teach!
Totally Newbie
Learning SQL and finding it a mine field  - i need all the help i can get. Problem is i am learning it to teach!
Anon
Sounds great so far! Can' wait for part 2.
bekki
Nice. I have a better understanding now ^_^
The Newbie
Heck yea, I am trying to run a game(in java) that needs to comunicte with the db, and my web host (Ipower) uses PHPMYADMIN n has SQL on it, but i understand jack squat.  I want to know were to typ all this CREATE foo and junk.  Dos or what?  I am realy in need of some major help
drathbun
Hi, I haven't had a chance to get over to this site for a while, but I have made some progress on part 2. I'll try to get it sent in shortly. -- Dave
chudgar
Very well done.  Would like more please!
Din
yesh, more MORE!
RS Phagura
Good introductory article. Please tell more about SQL.
drathbun
Hi, if you've read this far, then you probably read the tutorial that I wrote. I have in mind a number of additional topics, but would be interested to find out if there is actually an interest in them before completely blowing my free time. So please let me know what you think.

Dave

Add a new comment

This page is © Copyright 2002-2024, 4WebHelp. It may not be reproduced without 4WebHelp's prior permission.