00:00Hello everybody, in this lesson we're going to be learning about the select statement in MySQL.
00:04The select statement is used to work with columns and specify what columns you want to see in your
00:09output. The first thing that we need to do is open up a tab or an editor window. So let's
00:14come right
00:14up here to the left hand side and we're going to create a new tab and I'm going to zoom
00:19in just a
00:20little. Now what we need to do is we need to select the actual table that we're going to be
00:25querying
00:26off of. If you remember from the very first lesson when we set everything up, we came over here and
00:31we right clicked and did select rows limit 1000. We're not going to do that, we're going to actually
00:36write it out. So what we need to do to select that table, the employee demographics table,
00:42is we need to select everything. That's what the star means. The star means everything, all tables,
00:49all rows. Now we do have a limit on here, we have a limit to 1000 rows. So if we
00:55had a table that had
00:5550,000 rows, this limiter would be an issue. It would still limit it to 1000 rows. We would have
01:01to change that to 2000, 5000, probably all the way up to 50,000 if we wanted to view everything.
01:08If we had say a million rows, we would need to come up here and say don't limit and it
01:13would give us a
01:13million rows. The reason they do this is mostly to keep the processing time low. If you have a million
01:19rows, it's going to take a long time for the output to actually appear. So let's come right back here.
01:25The next thing that we need to do is we need to say select everything. And now we need to
01:29say
01:29where we're selecting it from. So we're going to come right down here and we're going to say
01:34from, and now we need to specify what table. And we're going to say employee underscore
01:41demographics. And at the end, we need a semicolon. Now, why do we need a semicolon? This is going to
01:47tell MySQL that this is the end of this query. So if we write another one down here, which we
01:53will in
01:54just a second, it'll be able to distinguish between the two queries. We're going to go ahead
01:58and we're going to run this. And we'll just use this execute right here instead of this one.
02:03And there we have our entire table. So we were able to get our table. Now, there is one thing
02:08that is potentially wrong depending on what you're using it for. But what we didn't do is we did not
02:14specify the actual database before it. We only specified the table. And this works perfectly fine.
02:21Because if you look over here on this left-hand side, we have Parks and Recreation. It's in
02:26black. It's bold. That means that we're hitting off of this database. What's going to happen though,
02:32if we come down here to the Sys database and we double click on it. Now this database is highlighted.
02:37So now when we're selecting this table, we're trying to select this table from the Sys database.
02:43Let's go ahead and try this. If you notice, we have no output. Let's come right down here
02:49and pull this up. It's going to say employee sys.employee underscore demographics doesn't
02:56exist. So it's assuming that we're highlighting this Sys database. That means we're trying to
03:02pull from that database. Now we can still have this highlighted and still select the correct
03:08database by saying Parks underscore and underscore Recreation. And let me spell that right. Dot.
03:17So now we're selecting everything from Parks and Recreation dot employee demographics. If we run
03:23this, we do get the correct output. That's just something to consider, especially when you're
03:29working with a lot of databases and a lot of tables. It's usually best practice to actually put
03:35the database in front of the table name. Although throughout this lesson, we probably won't be
03:41doing that every time since we're only going to be using this Parks and Recreation database.
03:45Let's go ahead and double click this. So we have this highlighted again.
03:48And let's click all of this. Let's copy all of this. We're going to come down just a little bit
03:54right here. Now so far, we've only selected everything, but we don't have to do that. We can
03:59actually just select one column if we would like to. For example, if we got rid of that star,
04:04we say first underscore name. We're selecting the first name column from this table. If we highlight
04:12this query and we hit the execute button with the eye, now we are only going to return in our
04:19output
04:19all of the first names. And we can add a lot more. Let's actually look at all these. We can
04:25separate
04:26multiple columns with a comma. So we can do first name, comma, last underscore name. And then we could do
04:32birth underscore date. So now we have three separate columns. Let's go ahead and run this.
04:39And now we have first name, last name, and birth date in our output. Now the way we just wrote
04:44it
04:44is all on one line. And that's perfectly acceptable because MySQL is going to read it the exact same
04:49as if we did it in a different format, as long as it's still in this order. But sometimes you'll
04:55see
04:55it like this, where it's select first name, comma, last name, comma, birth date, all on different rows.
05:02Now there's a lot of different use cases for this or reasons for this, but it typically can be easier
05:07to read. Also, if you're doing any type of functions or calculations in the select statement, it's easier
05:13to separate those out on its individual row. Now again, we won't always be doing this, but it does help
05:20sometimes if you're doing that, it just makes it easier to visualize. For example, if we added the
05:25age, so let's add age in here and let's run this. Let's say we were doing a calculation where we
05:30wanted to add, you know, 10 years to their age. So we'll say age and we'll actually create a new
05:36row for this or new column. We'll do age plus 10. So now we can easily see that we're doing
05:43plus 10
05:43here. And this is another thing that you can do in the select statement, things like calculations.
05:48So if we go up here and we run this, we'll now have an age column, but we'll also have
05:54an age
05:54plus 10 column where it just adds 10 to the age. And we can at least visualize and really easily
06:00see
06:00this when we're doing these calculations. Now, something really important to know about any type
06:05of calculations, any math within MySQL is that it follows the rules of PEMDAS. Now PEMDAS is written
06:12like this. It's P-E-M-D-E-S. Now what I just did right here with this pound or
06:18this hashtag
06:19is actually create a comment. So this code isn't going to actually run, but it's just for note
06:24taking or seeing things in your actual editor window. I'll come back to comments in just a
06:29second, but just wanted to explain what that was. Now what PEMDAS is, is the order of operations
06:35for arithmetic or math within MySQL. This stands for parentheses, exponent, multiplication, division,
06:44addition, and subtraction. So this is the order that these calculations are going to run in the
06:49execution engine that MySQL has. So if I do age plus 10, and we'll put that all in parentheses,
06:56and then we come over here and we add times 10. So we're doing plus 10 here and then a
07:01times 10 here.
07:02What's going to actually happen is it's going to say age plus 10. So 44 plus 10 equals 54.
07:08Then we're multiplying times 10. The parentheses executes first because it comes first in this
07:15order. Parentheses. Multiplication comes next because it's this one. And then anything else
07:20after that, if we did, you know, plus 10, we'd run this. And you'll notice that it still follows
07:26the logic. 10 was just added at the very end to all of these outputs. Now let's go right back
07:31up here.
07:32Let's select everything again from this table. Let's pull up this table so we can see it a little
07:37better. And let's go down. Because the last thing that I want to show you is something called
07:43distinct. Now this is really, really useful and you use this a lot in MySQL. What distinct is going
07:49to do is it's going to select only the unique values within a column. Let's go ahead and copy
07:56this employee demographics. Bring it right down here. Let's say select and let's do first underscore
08:03name. So now we're just selecting the first name. Let's come right down here.
08:11There we go. So now we're selecting just the first name from this column. Now these are all
08:16unique values. So if we come right here and we say distinct, nothing should happen to this table
08:23because these are all unique values. Let's go ahead and run this. As you can see, the output looks
08:28exactly the same. But what if we were to do something like gender? So let's come here. Let's
08:35do gender. Let's run this. It keeps going down. I don't know why it's doing that. But now we have
08:41male and female. Now these are not all unique. We have female, female, female, and female. The rest
08:47are males. So there's only two unique values here. So if we come right here and we say distinct
08:52gender, now there should only be two in the output, male and female. Let's go ahead and run this.
08:59So now we get male and female in our output. Now this works perfectly in one column, but what happens
09:05if we have two columns? So let's do first underscore name comma gender. Let's go ahead and run this.
09:13Now the combination of first name and gender are no longer unique. Now Leslie and female are being
09:21grouped together and it's taking the distinct between both of these columns. So when we're only
09:26working with gender, it's only looking at this one column for both male and female. So it reduces it
09:31down to the only two unique values. But because we added the first name, all of these values are
09:37unique. So therefore the name plus the gender combination is always going to be unique. The very
09:43last thing that I want to show you in this lesson doesn't actually pertain to the select statement,
09:47but I want to save this code. Let's say we wanted to update this or upload this into our GitHub
09:52or save this and send it to somebody. We can do that. We can save it by clicking this save
09:57button
09:57right here. I'm going to go ahead and click this. And now we're in our MySQL beginner series folder.
10:03I'm just going to save this and I can save this as anything I want. So I'm going to say
10:06two dot
10:07select statement tutorial. So now when I save this, you'll notice that the name gets changed up here
10:15to two dot select statement tutorial. Let's exit out of this. I'm going to open up and now I'm going
10:20to
10:21come here to the select statement tutorial. I'm going to open it. And now I have our code again,
10:26exactly as we had it written before. I just wanted to show that to you in case you wanted to
10:30save your
10:30code as you go throughout this series, because that's usually what I do when I'm working with
10:35this stuff or learning these things. I like to save my code as I go along. So with that being
10:39said,
10:40that is the end of the select statement. In the next lesson, we're going to be learning about the
10:44where statement where we can actually filter our data.
