In the previous movie, we learned the syntax that we use to create, read, update and delete records in MySQL. Now let's start adding data to our subjects table and take our new MySQL commands out for a test drive. So as you can see I'm already inside MySQL and I've already got my database created and my subject's table created. And those are the columns there, it says field. Those are the columns that I've got to work with. Let's try some of the SQL that we just learned. So let's start by doing select and then asterisk from subjects. That's the simplest command that we can do for a read.
And it comes back and says there's nothing there. I don't get anything back there are no rows inside the subjects table. We have to add one. So let's try that with insert, sert into. And then our subjects. And then you'll remember that in parentheses we're going to provide the columns that we want to insert into. Now, I'm going to leave the ID column off. Remember it's going to auto increment that for us. So I don't need to value. I could. I could go ahead and tell it exactly what ID I wanted it to have. But it's better I think most times with that ID to let my SQL be in charge of it.
So, instead, I'm just going to skip straight to the menu name and then position and visible. Now I could keep typing here or I could just hit return and because I haven't hit a semicolon yet, mysql is waiting for the rest of the command. So values and then the values that I want for each one. So for menu name, I'm going to call this first one about Widget Corp. Now notice that I'm using single quotes around that. You want to use single quotes always in MySQL. Not double quotes. Single quotes. That's just the way that MySQL works. And then for the position we're going to have the number one. That could be in single quotes but because it's an integer I don't have to. So it's going to be in position one, and visible is a Boolean, and that's going to be true or false.
Now you could actually type TRUE all capitals or FALSE all capitals but those are just constants in mysql that reference either 0 or 1. So, if we want it to be visible, we just type a 1. If we don't, a 0. Most people, you just use 1 and 0 instead of true and false. And then a semicolon at the end to let it know that I'm done. And I'll hit return. You'll notice that it says query okay. One row affected. So there it is. It tells me that it changed one row. That it did something to one row. It'll always give you that status update to let you know how many rows were changed in the process.
Now let's try our statement from before. We can hit the up arrow, to go back to the Select all from subjects. Hit Return and you'll see that we get a nice table rendered back to us that shows all of the columns and the data that's in each one of those. And at the bottom it tells us one row in the set. Alright, so lets try adding in another one. For this one let's do insert into subjects again and I'm going to use my up arrow to back and find the one that I did previously. You'll see that now it's unwrapped it, so it's all in one line, that's fine and I'm going to keep the beginning part the same, I'm just going to change the values here.
I'll use my arrow to go back and instead of About Widget Corp, I'm going to call this one Products. It's position will be 2 and it will also be visible so it'll be a 1. And I'll just hit Return from anywhere on the line and it made that change. You can use the Up arrow to go back, and see now we have two of them. ID 1, and ID 2, position 1, and position 2. See how that works? Let's add a third one. We'll hit the Up arrow. And this one instead of products we'll make it services. I don't think we need to take a look at that one, but you can. I'm going to hit the up arrow again and I'm just going to add a fourth one real quick.
I'm going to call this one miscellaneous. I'm going to make it number 4 but I'm going to make it not visible. And now let's take a look at all of them. That's what they all look like, that's what I have in my database. All of them are marked visible except for this last one. It's marked not visible with the idea that that would not show to the public. It would not be in our navigation menu for them to select from. Now that we have one that's not visible, let's try a variation on this select. We say where visible equals one. Now I get back just the visible ones or where visible equals zero, I get back the ones that are not visible.
So, my conditions now are affecting the set of data that I get back. In addition to where we can have order by and we can sort them by position ascending. There we go. Returns the position ascending. If we go back and do position DESC, it would be descending position 3, 2, 1. See how they reversed? And of course we could have instead of by position, we could sort them by menu name. Now they'll be in alphabetical order. Now in all of these cases, we're expecting that it's going to give us back one or more rows.
Maybe only one row matches like when we ask for the ones that are not visible. Or maybe many rows match, like when we ask for the ones that are visible. But typically if we want to just get a single row back, then to do that we're going to say find the one from subjects were ID equals 2. We're going to specify the ID. In that case we know that we're only going to get one back or nothing back. They couldn't find it that ID didn't exist. We'll get nothing. But if it does exist we'll get one back. We'll never get two or three or four or anything else like that because only one thing can have this ID. It's an unique identifier.
Last of all let's just try specifying some of the columns we want. I'm going to go back to one of these earlier ones. And let's specify columns. Instead of saying that we want to have all of them with the asterisk, let's say we'd like to just see the menu name comma position. Hit Return and you'll see that it just returns columns for menu name and position, the other ones are just left off. So for any column that we wanted to include, we just specify it right after the select statement. Okay, and so we've done some playing with the read statements. Let's try and update now. So let's say that we want to update our subjects and I'm going to hit Return.
Again, it's waiting for me to put a semicolon, set, I'm going to set visible equal to 1 and hit Return again where ID equals 4. That's going to take that fourth item which is not visible and make it visable select all from subjects. Now you can see it changed it, it went from being not visible to being visible we updated it. Now let me just show you what happens if we say update subjects set visible equal to 0 we don't specify the ID. It's going to change it to all rows.
It says here rows matched 4. 4 rows affected. Take a look at this. You can see that they're all not visible now. It's very important to decide whether you're just changing one row in which case we need that ID to be specified. Or for it to be changing all rows. And we don't have to just have one row, we can actually use our where statement a little more creatively. We can say, set all of them to visible where id is less than 4. So we'll get the first three, there we are. Three rows changed, you can see that I changed these three.
So my where statement said everything with id less than, it matched these three and so that's what it updated. It updated for all of those at the same time, not the 4th one. And then now that we've worked with create, read and update, the last one is delete. So let's do delete from subjects where ID equals 4. And we'll just get rid of that last one there. One row affected. And we go back and look and sure enough, now it's gone. So no we've been able to successfully use our create, read, update and delete commands in MySQL to make changes to our subject's table. Feel free to go ahead and play around with it a little more, you can make other edits, updates, deletes, get comfortable with it. And when you're ready let's go to the next movie where we can talk about relational database tables.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98566 Viewers
61 Video lessons · 85854 Viewers
71 Video lessons · 69749 Viewers
56 Video lessons · 102056 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Your file was successfully uploaded.