Writing data with SQL - Amazon Web Services (AWS) Tutorial

You may remember when I first started talking to you about using SQL, I mentioned that the data manipulation language has a few big commands. Select, and then there was insert, update and delete. Now in the previous episode, we looked at the select statement, saw some different examples. What I want to do now is show you a few examples of the insert, update, and delete statements and how they actually work. I'm going to use a simple table to make it easy for you to grasp what's actually taking place here. So rather than using a large complex data base, we use a simple example table that'll help you to really clearly see what's going on with these statements. So here we are in the my SQL workbench again. And I've created a new data base, or schema, called test. And I have a table in here called new table. It's a simple table. If we look at the actual table structure itself, we'll see there's not a whole lot in this table. And so if we run our select statement right here, where we select everything from new table, you can see the results. We have an id column, an fname column, and an lname column. Just to keep it nice and simple. And it's the only table that we actually even have in this entire data base. So, what I want to do is I want to add a new record for Dale Carpenter, and we'll give it an id, which is the primary key in this table of 200. So you'll notice here, insert in to new underscore table, and what are we going to insert, we're going to put in an idcol value, an fname value, and an lname value. So that's what this first set of parentheses does for us. Then we have the key word values, and then we have the actual values so 200 is equivalent to idcol, Dale is equivalent to fname, and carpenter is equivalent to lname. And of course we end with a semicolon. So I'll highlight that because if I don't it runs everything right? So I'll highlight that to just run that, and click on execute the selected portion. So now, if we run our select statement again, we ought to see that we have two records. Let's run it and notice I now have 100, which is Tom Carpenter, and 200 which is Dale Carpenter. I could of course continue adding more records with more insert statements. Now what I want to do, is I want to modify this first record, instead of it being Tom Carpenter, I want it to be Thomas Carpenter. So we're going to use the update statement. Again we specify new underscore table, and then we set fname to equal Thomas where the idcol is equal to 100. So I'm making sure I pick out the specific record that I want, which is the record with the primary key of 100. And I want to change only that records fname value to Thomas because I wouldn't want Dale Carpenter to also become Thomas Carpenter, so we'll highlight the line and let's see if that works. Down here below in the output I see the update was successful, let's run our select statement and see the results. And now you can see, 100, is Thomas Carpenter, indeed it worked. All right well the third of these DML statements that we want to use here for modifying data, is the delete statement. It actually deletes a record from the table. So we want to delete from new underscore table, where idcol has a value of 200. So all I want to do is delete that Dale Carpenter entry, not the Thomas Carpenter entry. So let's select the line, execute it to see if it works, it says there were no errors down below, let's run the select statement and see what we get. And there you see we're back to just Thomas Carpenter again. Of course if I rerun this insert statement here on the very first line, and then I run the select statement again, you'll see that the record is right back, that we had created. So, insert add new records, completely new records to the table. Update, modify existing records, you can modify one or more of the column properties at a time if you desire. And then delete, removes the record from the actual data base table. So always be careful with delete, because if you say delete asterisk from table, uh-oh not good, right? You've just said to delete everything. Because you didn't have a where clause, you should always have a where clause with a delete statement. In fact the my SQL workbench does have safe mode enabled by default. I disabled it for the demonstration, when it's enabled, you cannot run a delete statement. If you do not specify a where clause, and you cannot run an update statement if you do not specify a where clause because it's so easy to accidentally update every record or delete every record in a table. So to protect you from that it runs in a safe mode by default, you have to go into preferences and then SQL editor, and disable safe mode, in order to be able to run those very dangerous commands. So if you do want to purge an entire table, keep in mind that most data base systems actually have a special command for that, it's called truncate, and the truncate command is used when you want to purge an entire table. Rather than accidentally deleting everything with the delete statement.