3.1 Inserting Data Into Tables
Now that you have a table to start working with, let's add some data to it! In this lesson, you are going to learn the basic syntax of inserting data into tables with the
INSERT INTO query.
1.Introduction3 lessons, 13:51
2.Working With MySQL Databases and Tables3 lessons, 16:12
3.Basic Database Read and Write Operations5 lessons, 28:11
4.Relationships Between Tables4 lessons, 28:17
5.Conclusion1 lesson, 02:50
3.1 Inserting Data Into Tables
Now that we have our guest table, let's talk about trying to get some data into that table. And we're going to talk about this very generically and how you're basically going to insert data into any table. Now there's obviously some gotchas here and there, and we're going to talk about some of those later on. But for now, let's just ahead and just start with the basics. In order to get data into a table using T-SQL, you're gonna need to use an insert statement. And here is the basic structure that you're going to use. You're going to and start with insert, and then you want to say into. So I'm gonna insert data into a specific table. Now what table Am I going to insert into? I'm going to insert into guest. Now, when you are doing an insert statement, you're gonna want to specify what columns you are inserting data into. So, the way that we do that, is after we specify the name of the table, we're going to do an open and close parentheses. And inside there, we're gonna specify the names of the columns that we want to insert into. And in this case, I want to insert into first_name, last_name, and confirmed. Now, I'm skipping over the id. And the reason that I'm doing that is because before, like I mentioned, we are using the auto_increment functionality of MySQL. And you're gonna see that we're not gonna have to do that. So under normal circumstances, if you were doing just a straight table that didn't have any auto-incrementing or anything like that. You would want to specify all of the required fields that you would need to put in there. So that means all of the now knowable columns. So we're gonna start with inserting all of them, and then we'll talk about doing another variation of this insert here in just a moment. So we're going to insert into first_name. We're gonna insert into last_name. And we're going to insert into confirmed. Now, what so we wanna insert into there? Well, I'm gonna hit Enter so we can drop down to the second line. We're going to insert some values into those three columns, and the way we do that is by specifying the keyword values. And then we're gonna do another open and closed parenthesis here, and I'm gonna specify the values that I wanna put in there. Now because these are all variations on strings, they need to have double quotes. So I'm gonna say that the first name is going to be Derek. And the reason that it's the first name is cuz it corresponds with the first column that's referenced in our reference list here on the table. So we're gonna have first name be Derek, the last_name is going to be Jansen and confirmed is gonna be absolutely yes. So I'm gonna put in a Y, cuz I'm definitely coming to this party. And then we're going to use our semicolons. So let's go ahead and hit Enter and you're gonna see that the query is okay. Now just to do a very quick and dirty way of showing you how to get data out of it, so you can actually see that there's data in there. We're going to do a select query. So this is what's going to allow us to retrieve data out of our table, and we're going to talk about select a little bit more later on. But just so you can see that the data is actually in there, I'm going to say select, and I'm going to use the wildcard. So I'm going to say give me all of the columns. Select everything from our guest table. And by doing that, you're gonna see a list of records here, and I only have one in here at the time being. But this is gonna be enough to get started. You're gonna see all of the column names in the entire table 'cuz we selected everything. Select Star. And you're gonna see here that I have an ID of one. And the reason that I have that, and I didn't specify anything in here, Is because it's the primary key and it's auto incremented. So you're gonna see that this has a value of 1. Then we have here a first name of Derek, last name of Johnson, and confirmed as yes. All right, so let's try another insert statement. So I'm gonna use the up arrows, and I wanna insert into all of these again. But let's say I don't know the last name of somebody yet. So I'm simply going to remove that. So I'm going to insert into guest(first_name and confirmed), and let's go say that this person's name was Jane. And Jane, I don't know the last name, so I won't be inserting that. And Jane wasn't sure yet, so she said go ahead and put her down as a no, but maybe she'll change it later. So we'll go ahead and hit Enter again. And now if I were to go up and say give me everything from Guest, I can hit Enter. And you're gonna see here now that I have two records, two IDs, 1 and 2. First First name, Derek and Jane, last name, I know Derek's last name is Jensen but Jane, I don't know her last name yet. I'm gonna have to work on getting that information. And confirmed I have her down as a no, but she could come back and tell me later on that she T is in. So now you can see the basic process of inserting into tables using the insert into command. Where you're going to specify the name of the table, the columns that you want to insert into, as well as the values.