Home

Your premium source for custom modification services for phpBB

  logo

HomeForumsBlogMOD ManagerFAQSearch Log in

The phpBB Doctor is currently available for private clients only. At this time we are not accepting any new work until we can clear out our current backlog. Thank you for your patience.

 

Anatomy of a SQL Statement: Part I


 
Register or Login to Post    ForumsphpBB Anatomy
View previous topic :: View next topic  
Author Message
phpbbDoctor
Site Admin


Joined: 12 Apr 2005

Posts: 269

None Selected

Post Subject: Anatomy of a SQL Statement: Part I
Sat Aug 27, 2005 8:59 am

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. We 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 the introduction of relational databases 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 a specific episode 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. We don't want to have to know where the data is, we 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

Code:
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

Code:
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 users. Well, in that case, the table is probably called users, or even phpbb_users, 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 user information, you would write:

Code:
SELECT *
FROM phpbb_users


What does the * do? Well, in this case it seems that we're too lazy to specify exactly what sort of user data 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 name and email address for your user, as well as their unique ID? In that case you could be more specific, as in:

Code:
SELECT username
, user_email
, user_id
FROM phpbb_users

The "splat" has been replaced by a number of columns from the table. Instead of viewing the entire user 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? You might want to call your users "members" instead of users, so why not rename it with an alias?

Code:
SELECT username as MemberName
, user_email as Member_Email_Address
, user_id as Member_ID
FROM phpbb_users

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. To use an alias simply select the column value as before but follow it with the "as aliased_name" syntax. Aliases for column names are optional unless you retrieve the same column from two different tables... but we're not quite ready to talk about that yet.

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

Code:
SELECT COUNT(*)
FROM phpbb_users

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 users table. If you have a large number of users, a more efficient way to count them would be this:

Code:
SELECT COUNT(user_id)
FROM phpbb_users

This assumes that each user has been assigned a unique system ID (called user_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 user record can require retrieving the full record (all fields) 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) user registration dates? That might look like this:

Code:
SELECT MIN(user_regdate) AS First_Member_Date
, MAX(user_regdate) AS Most_Recent_Member_Date
FROM phpbb_users

Yes, you can use the same user 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 user was registered, as well as the date the most recent user 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 users 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.
Back to top
Display posts from previous:   
Register or Login to Post    ForumsphpBB Anatomy
Page 1 of 1 All times are GMT - 5 Hours

 
Jump to:  

HomeForumsMOD ManagerFAQSearch Register Log in
Not endorsed by or affiliated with the phpBB Group
Powered by phpBB © phpBB Group
Terms of Service
Web design by MomentsOfLight.com logo