7 days of WordPress plugins, themes & templates - for free!* Unlimited asset downloads! Start 7-Day Free Trial
FREELessons: 16Length: 1.5 hours

Next lesson playing in 5 seconds

Cancel
  • Overview
  • Transcript

3.2 Select Query Basics

Getting data into your tables is great! But it's only a part of the story. Once you get the data into your tables, you are going to need to know how to get it out again. To see how to do that, let's start by discussing the basics of the SELECT query.

Related Links

3.2 Select Query Basics

Now that we have a couple of basic records in our table, we can talk a little bit more about retrieving data or selecting data out of a table. Now, we're gonna start with a single table and it's gonna be fairly simple. Can we get a little bit more intricate later on but for now we had one table and we have to rows in it. So, let's just talk about picking apart this concept of a select statement a little bit more. So as you can see here, I have select * from guest, which is gonna give me every single column that is in the guest table and show me all of the data. But let's say that I don't necessarily want all the data or I wanna be a little bit more specific about the data that I want. So as I mentioned before, the star is a wild card, which means give me everything. But I don't always want that, so what's another options that I can use. Well, instead of specifying that star I can say, just give me a couple of the columns that I want. So let's start off with getting all of them and the way that I can do that is by specifying that I'm buying name. I can say id, first_name, last_name, and confirmed. Now if I were to do that, I'm gonna get the same response back, because I'm naming all of columns that I want. But maybe I don't want everything, maybe in this case, it doesn't really matter to me what their last name is, I don't really want last_name. And maybe I don't really care about the id for now. I really only want to see the first names and confirm. So I can go ahead and do that, now I'm only gonna get back these specific columns that I asked for which is kinda nice. So I can be very specific about the data that I'm getting back. Now, I may also only want to know who is coming and who isn't coming. And maybe I wanna be able to separate those things into separate lists so that I can maybe send thank yous to the people who came, or maybe send regret letters to say I'm sorry you couldn't come to the people that couldn't come, and I wanna maybe to pull those things apart. And the way that we do that is by filtering our queries a little bit. Now there's so many different ways that you can do that, but I'm gonna show you the absolute basics to just kinda get you up and started. So let's go back to getting everything, so let's say I wanted to get everything. But now, instead of getting everything, I only wanna get a list of the people who are confirmed. And so the way that we're gonna do that, is we're going to bring back our select statement. And actually before I get too far, let's go ahead and clear this. And let's say if I wanted to get only the selected people back that are coming, I can be a little bit more specific. So I'm gonna say select, give me all of the columns from guest, but in this case, instead of ending there, I wanna add a bit of a filter on here. So I'm gonna drop down to the next line, and the way that I start to filter things out is by using the where clause, so I'm gonna say where. And now I can say where certain values in certain columns are equal to something or greater than something and you can start to do these comparison operators. So in this case I only wanna know which ones are confirmed. So I'm gonna say I wanna know all of the people, all of their information from guest where the confirmed column has a value equal to yes. So let's go ahead and clear that off. So now you can see here that I am getting back a list of only the people that are coming, that have confirmed. So as you can see here, I have back one record. And I can do the opposite and I can say, now I wanna get a list of all the people that aren't coming so I can change that where clause to be no. So I will get this in here as well, so I'll only get this one record that has the confirmed value of no. So and I can continue to do these types of things. Now, if you wanna get a little bit more elaborate in your where clause and do a little bit more filtering you can. So you don't only have to specify one column and one value, you can start to chain these things together using additional operators, the and and the or. So let's go ahead and start to put that in here. So let's say, I only have these two columns in here but let's say I wanted to get whoever was confirmed. So I can say, where confirmed = yes and I can also put an and clause in here, and I can say, and, and let's just make something up, let's say, last_name is equal to null. But in this case, you don't really say is equal to null, you would say shere the last name is null. So this is kind of like a specialty selector that you can use if you wanna check the null, or whether or not the value in a column is null. So now I wanna get the list of people that are confirmed that I don't know their last name. So let's go ahead and hit Enter, and you're going to see I got back an empty set and that's true because I have one row here that is confirmed Derek. But I have a last name here. So if I would have modified that and say I wanna know all the people that are confirmed that have a last name equal to Jensen, then you'll see I'll get one record back. So I can start to chain these things together and I can also do things like an or, so I can say where confirmed equal to y, and then I can throw in here an or confirmed equal to no. Now this is a little bit of a convoluted example, I don't know why you would wanna do a query like this, but just to show you that you can chain these things together. We can say where, the confirmed is either yes or no. So by using a very simple where clause, you can start to tease apart the data that you're retrieving with your select statement on a very simple straightforward table.

Back to the top