OK so what we've seen so far allows us to specify what columns we want name and age only or only easy
but it still gives us every single result in the table.
So if we have 10000 users in our database if we wanted to select one we don't have a way of doing that
right now.
We only can select all of them which really is not that useful.
In fact most of the time when you're selecting things you're doing it deliberately and you're trying
to find particular pieces.
You're not just trying to check in on your table make sure everything's OK.
You're usually trying to let's say sign someone in if it's an application you're trying to verify find
someone by their user name and check if their password matches that they entered into your website or
something like that where you're searching for specific data.
And to do that we need to use what's known as the WHERE clause.
So it's another magic keyword where it allows us to get specific.
It allows us to say I want all cats who are 4 years old or I want all cats who are Tabby's and those
are very simple examples.
But later on throughout this course we'll continue to expand your arsenal for select and for searching.
You'll be able to do things like I want all cats that are Tabby's between the age of 1 and 2 and whose
name starts with the letter B.
Let's say.
So we'll be able to get much more specific.
But for now we're focusing on the basics.
And as a side note I want to show you that we will use where all the time.
So it's not just for searching or for selecting But we'll need to use it when we're updating or deleting
things as well because we're trying to update every single thing or delete every single thing.
Usually we want to update something specific so we might say I want to update the cats where their age
is too and I want to make their age three versus I want to update every single cat to have an age of
three.
We could be more targeted using WHERE.
So we're going to start by seeing how we use it to select.
So here's an example.
Select the star from cats where age is for so notice the syntax here.
We have our same select star from CAS that will give us all cats but then we have this where age equals
four.
So what this will do is only select the data from the cat's table where the age column is equal to four.
And if we try that now if we look at our data actually you'll see we have two cats Ringo and egg who
are both four years old.
So if we do a select start from cats you see what we get.
But now if we modify that with our where age equals four semi-colon we only get those two results.
So we still get cat ID name breed and age because we you start that tells us the columns.
But now we only have two results because we used where to get specific.
One note noticed that when I refer to age here that is the column age.
There's no quotes around it.
It's just age.
So we can do the same thing here but this time I'm using name to drill down or to get specific.
So select star from cat.
Name is equal to egg.
And notice I use quotes here because it's no longer an integer like ages.
Name is far char.
It's text.
So we need those quotes so we can try that.
Now select star from cats where name equals egg.
Just like that you notice we get egg cat ID name breed and age.
One side note while I'm here you may come across as at some point.
What do you think will happen if I fully capitalize eg.
It still works.
So it's important to note that by default this is case insensitive.
So I could do lowercase g uppercase G and it still gives me the same data.
So we'll see ways around this later on.
Oftentimes it is useful though to not worry about case if you're dealing with things like an email address
where case doesn't actually matter.
So if a user types in their email with a capital letter at the beginning versus lower case it should
still be the same email in your database.
But we'll talk about that later.
I just wanted to point out that the thing case insensitivity So that's all there is for now to the where
clause.
Now there is a lot more that we will be seeing and working with like I mentioned at the beginning of
this lesson we will come back and use where all the time.
And in fact we're going to continue to use it for updating and deleting.
Like I said we need to specify what we want to delete and what we want to update.
But I just want to make it clear we're not done with where there's a lot of add on functionality that
we'll see in ensuing sections.
Perfect.