Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Now we've created a data base and we've added our subjects table to that data base we're ready to start working with data. In order to do that we need to talk about CRUD. Specifically how we do CRUD in the context of MySQL. You'll remember that I told you CRUD was an acornym. It stands for Create, Read, Update, and Delete and those are the four main operations that we perform on databases. Now, all the other MySQL that we've been learning, all those other little statements, you don't need to commit those to memory. You can go look those up whenever you need them because you're not going to need them that often.
You'll go and set up a database and create a table at the beginning of your project, but then after that you're not going to be working with them much. You're mostly going to be doing create, read, update, and delete. So this is the syntax that you want to commit to memory. These are the things that are important that you want to stick to, because you're going to be using them 90 to 95 percent of the time. Not the other stuff. I still go back and look up some of the particulars of the other syntax some other time. So let's go take a look at how we do each of one these in MySQL. And let's start, not with create, but with read. And the reason why I want to start there, is because read is the one that you're going to use the very most. We read back data form, more often than we change it. And even when we do go to make a change, we typically read the data before the change.
And then read the data again after the change to make sure that it's right. So we're going to do lots and lots of database reading. And we're going to do that in mysql using the select statement. This is a typical syntax for select statement. Let's walk through it together. So to begin with we have Select. We're saying we're going to select some records from the database. That's telling it to read them. After that, we tell it what columns, we want it to include in the data that it brings back to us. An asterisk means bring back all columns, I want to see everything. We could also specify we could say, we just want to see ID comma menu name comma position comma. And then any other columns that are there, won't be sent back to us. It'll just return those columns.
So the asterik the one the you'll see the most often, but again you can specify a common delimited list of column names. And then from table, now between those two lines, I've got it broken up on two lines, MySQL doesn't mind that. But you're going to want to make sure that there always is a space between those two so that MySQL can tell the difference. And then the FROM tells it where we're pulling from. And then our table name and then the next two lines are optional. We could just simply say show me everything from the table. And it'll return all the records to you all columns.
Or we can specify, we can say, show me the rows from the table where, something is true. Column is equal to some_text. Now notice there's a single equals here. It's not a double equals like we do in PHP where column equals some_text and now all rows that match that condition will be returned to us. And then we also can specify the ORDER. Order by and tell it how we want it to sort it. They don't have to sorted in the default order, we have MySQL sort them for us. And so here, I've said sort it by column 1, ascending.
Ascending means from A to Z, A first, Z last. If we did descending, which would be desc, then it would sort them in reverse order, Z first and A last. Then there are lots of other options that we can add to this, our where statements can be a lot more complex. But this is the basic way that we pull back data from the database. So you're definitely going to want to commit this one to memory, because it's the one you're going to be using the most often of anything you do in SQL. So now let's look at how we do a create, and we do that with the SQL INSERT statement.
We INSERT INTO, the table name, and then we provide a list of the columns Inside parenthesis with commas between them. Then we're going to insert into, we don't have to insert data into every column, we don't have to list them all out. But we're going to list the ones that we're going to be using followed by the values, and then in parenthesis all the values that should go in those columns. And then of course it's common sense that those values need to be in the same orders as the columns. Otherwise it wouldn't know where to put them, so val1 is going to be into column1 and val2 is going to go into column2.
And of course there need to be the same number of values as there are columns. They're going to need to match up, and that's it. That's all there is to doing an SQL INSERT statement. Okay, so let's look at an update. So this means that there's already data in the database. We have a row for a record. Let's say it's a customer and we want to update their address. Well, we do that using the UPDATE statement. UPDATE table SET column1 equal to some_text. That's it, set that column, whatever it is, equal to some text. And if we had more of those, then we'd just put a comma followed by additional ones that we wanted to set. So each one would just be a key value pair with the equals sign in between, and then WHERE id equals 1.
We don't have to specify this but most often you will. If we were only updating one row in the table then we want to specify, we want to say update it where this id is 37. If we didn't specifiy that it would change all the customers. So suddenly we would have all our customers with the same address, because we didn't specify what id, what row we were looking for. So becareful about that. And then, last of all, we have DELETE. So this is an SQL DELETE statement. We say DELETE FROM table where id equals 1, or id equals 37.
It's going to delete that row from the database. Now, we don't have to use id as the specifier. We could say, DELETE FROM table where first name equals Kevin, then all rows where Kevin is equal suddenly disappear. They all get deleted at the same time, it can be very powerful but most often we're going to just be deleting a single row, we're going to identify it by it's unique id. So those are the four main ways that we're going to work with CRUD in MySQL. That's the syntax for it. So you can come back here if you need to refer to it later. We're going to be working on it more over the course of the next few movies. Don't worry if it seems like a lot to take in at first. Once you do these a few times, it's going to become second nature very quickly.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 99356 Viewers
56 Video lessons · 112620 Viewers
71 Video lessons · 81434 Viewers
131 Video lessons · 39096 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.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.