Database Basics: Part 4
July 31, 2002
What is a SELECT statement?So, what does SELECT do? Very simply, it tells the DBMS that you want to get some data out of the database.
So, how does it work? SELECT can range from very simple to painfully complex. With SELECT there are a whole host of different options available to you that will allow you specify, calculate and arrange your data in an infinite number of ways.
Using a SELECT statement obviously begins with the SELECT command. Next, you will need to specify what column(s) of data you will be retrieving. For example, using our test table that we created say we want to get someone's email address and first name. You would let the DBMS know what you are looking for by starting your SELECT statement like this:
This tells the DBMS that we are getting information only from the "first_name" and "email" columns of our table.
So, what if I wanted to select all the columns in the table? Would I have to spell out each and every column in a table? Wouldn't that be a pain with a table that has a bunch of columns?
Yes, it would be a pain and no you don't have to specify each column. If you want to get all of the columns out of any table you only need to use just one character, "*", like this:
Alright, what if I want to get stuff out of a few different tables? Do I have to make a SELECT statement for each table?No, you can actually combine data from several different tables into one SELECT statement. However, this particular topic we are going to save for a bit later in the series.
The next part of the SELECT statement is the FROM clause.
What is a FROM clause?As you might have guessed, the FROM clause tells the DBMS what table to look in. Using our table we created in Part 2, our SELECT statement should look something like this now:
Can I use more than one table in the FROM clause?Yes, you can. This can be a more complex issue which I will explain later in the series along with selecting multiple column names from different tables.
Now, if you wanted to you could stop right here. Just add a semicolon to the end of the SELECT statement above and you would get every first name and email in the "contacts" table.
But what if you wanted to get data for people with the first name of "Paul"? Well, that's when the WHERE clause comes in to play.
What is a WHERE clause?A WHERE clause allows you to filter out any unwanted data so that the data you do get is exactly what you are looking for. Let's look for all of the people named "Paul" in our table.
So, what if I want to filter my data using a number like in the "contact_id" column? Can I do that?
Absolutely. To search for the person that has the "contact_id" of 3 your SELECT statement would look like this:
Can I filter data in other ways?You bet. There are a whole host of operators that you can use to make comparisons and filter data. Keep in mind, though, each DBMS can be different and the operators they choose to use can change from DBMS to DBMS.
Here are some of the most common operators that DBMSs use.
Operator Description = Compares the data to your criteria to see if it is equal <> Compares the data to your criteria to see if it is not equal < Compares the data to your criteria to see if the data is less than your criteria <= Compares the data to your criteria to see if the data is less than or equal to your criteria > Compares the data to your criteria to see if the data is greater than your criteria >= Compares the data to your criteria to see if the data is greater than or equal to your criteria IS NULL Checks the data to make sure that there is no data in the column
What's Next?Now you have enough basic information to create a database, create tables, enter data and retrieve data. Now is when it starts to get more interesting. We can start to get organized.