The WHERE Clause
The first thing you may notice is that I'm now selecting just the emailAddress
fields. I may do something like this to feed a mailing list, for instance. However, the part we're more interested in is WHERE enabled = 1
. In effect, I'm asking for the email, first and last name only for rows where the enabled
column is 1. Users that are disabled (enabled =0
) would be excluded from this list. That way if I had any users that have been disabled for whatever reason, they wouldn't be getting my mailing.
Let's look at a slightly more complicated WHERE clause.
A More Complicated Where Clause
In this query, I added a second WHERE clause. You can add as many where clauses as you want. In this case I used AND, so any rows that are returned must have enabled = 1
and also have a firstName like 'Sat%'
. The like keyword lets you do a partial comparison. Here I'm asking for 'Sat%'. The percentage sign (%) is a wildcard. I'm basically asking for any row that has a firstName
that starts with the letters Sat. For the next example we'll get a bit more complicated. I'm adding some more rows in the table (not demonstrated in the tutorial) just so we can get more results.
An Even More Complex Where Clause... Plus Order
We'll start with the WHERE statement. Here I'm looking for accounts that are enabled=1
. Then I have an OR and parenthesis. That means either enabled=1
or whatever's in the parenthesis. In there we're checking if the lastName
like '%clinton' and the emailAddress
is like '%@theclintons%'. In effect, anyone that has their account enabled is returned. Additionally, anyone that has a last name that ends in clinton
and an email address that has @theclintons
somewhere in it is returned.
You can keeping adding and grouping WHERE clauses as much as you want. We'll get into more advanced options later. Following the WHERE clause we have the ORDER BY clause. This tells SQL in what order to return the results. I'm ordering the results by lastName
in an ASCending fashion. Yes, ASC stands for ascending. I'm then also ordering the results by the firstname in a DESCending fashion. So basically for any results that have the same lastName, it then orders these results by the firstName. You can see that we have three Clintons, so we then suborder the clintons based on their first name in a reverse-alphabetical order.
This was a pretty quick run down of simple select statements. They can get much, much more complicated. We'll go over some of that later. I recommend you play around with SELECT if you're having any doubts. It'll only get more complicated as we keep going. Next, we update our rows.