2.2 Creating and Deleting Tables
Once you have a database or two to work with, it's time to turn your attention to tables. Tables are where you are going to wind up storing all of your data. In this lesson, you'll learn the basic commands that will allow you to create and delete basic tables.
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
2.2 Creating and Deleting Tables
So now it's time to start adding some information to our test bit database. And basically the idea here is that we wanna create a record keeping system for an upcoming party. Maybe for a holiday, maybe we're having friends over. It's a potluck or something like that. And we wanna be able to track the people that we have invited to see if they're coming, who they are, what they're bringing and being able to keep track of all that information. And that's kind of the way that we're going with this database structure that we're going to build out. So the first thing that we wanna do is work on the basics of creating a table, and defining what that table looks like. So we're going to start by making sure that we're using our test database. And if you recall from before, we're gonna use the use command, we'll say use test. So now you see database changed, so now we know we're using test. And now we want to create a new table. Just like when we created that database, we're gonna use the create command again, but this time we're going to use the table. We want to create a table. And we do need to define the name of this table, and we're going to call it guest. Now, depending on how your naming conventions are, a lot of the naming conventions that I've kinda been brought up using as far as databases go is that when you define a table, you define it singularly. Now you could say guests, but a lot of the tables and database structures that I've used in the past have been singular. So that's just kinda the way that I do it. But know that you can create tables and name them really anything that you want. It's really not something to get caught up in this early in the game. So just kinda name it what you want. So like I said we're going to say that we're gonna create a guest table. And now we need to define the columns that are in the table. So let's start fairly simplistically, and then we'll tweak it as we go. So what we wanna do is we want to define the columns that we are working with. And the way that we do that is we, using open parenthesis, and now we can start defining these columns. Now, we're gonna start by giving the column a name and then a data type. And I'll leave you a link in the description that's gonna tell you all about the different data types. I'm not gonna go through all of them but you're gonna know that there's quite a few of them out there. And we're gonna talk about a couple of them here that are fairly common. So normally when you're creating a table, you are gonna want to have some sort of unique identifier for the person or for the record in that table so that you can refer back to it later on. And this is gonna become very important when we start talking about foreign keys and relationships between tables. But let's just kind of sort of plant the seed now. So the first thing that I want to do now is I wanna create a new column called ID and this is going to be an integer. Now, you can make the argument that this should be a string or whatever have you or some long [INAUDIBLE] or whatnot and that's all of designed decision up to you. For our particular application, an integer will be just fine. That will contain enough values so that we can invite hundreds of thousands and millions of people to our party which is not gonna be an issue. So then what you do is you comma separate all of these. Now I could put these all out on one line, if I like but I can also take advantage of the fact that I can use the Enter key to go ahead and drop everything down so I can see things a little bit easier. So now I have an ID. Now I'm going to want to know the first name of the person, so I'll say first_name. Once again, this is a naming convention using the underscore and all lower case. In between words you can do this however you would like, and name these columns however you would like. And what data type are we gonna use here? Well, typically, this is gonna be a string, but there's really not a string data type in MySQL, or in T-SQL, which is the language that we're using what you're typically going to see is var car, or var char. And this is a variable length string that we're going to define here. And so what we need to do is we need to define in parentheses what's the max length of this. So let's say the max length of the first names is going to be 20. You can make this bigger, obviously, to handle longer names, or shorter names, or whatever have you. But varchar basically means that it can be any length, from 0 up to 20, and that's basically the concept. If you wanted to narrow it down to be a specific number of characters, then you would use the char or the char data type, which we're gonna use in just a moment. So now we're gonna track the first name, so we'll set a comma here, and then we'll have a last name. And we'll make this once again a var char, as well. This will be 20 and then lets go in and stick here a flag to make sure or to know if they've RSVP'd or confirmed. So we'll call this confirmed. And this one is going to be a, the concept here is a Boolean value. And there's a couple of different ways you could do this, you could make it an integer. You could do all sorts of different things. The way that I typically like to do it is by using a char or a character. And this is going to be one. So it's going to be absolutely the length of one. You can only put one character in there. And the way that I usually do this is I set the flag equal to y or n for yes or no, depending on if they've confirmed or not. So those are gonna be all the columns that I want in this particular table. You can add as many as you want, and you can work with other data types. And I'll leave that as an exercise for you. Now once you're done, you have to make sure that you close off this first parenthesis. So I'm gonna close that off. And like everything else, we're gonna end with a semicolon. So let's go ahead and hit that and you'll see query okay, zero rows affected. But now I can go ahead and say show tables and you'll see in here that I have a single table called guest and now I can take a look by using the Explain or actually there's another command that I didn't mention before called Describe each of them is basically going to do the same thing. So I've wanted to explain or describe guest I would now see what this table looks like. I would see I have an ID your first name last name confirmed. I can see the data types, I can see null, which is interesting, so we're gonna talk about that in just a minute. And key, which we're also gonna talk about in just a minute. So what we've got going on here is the the basic concept of a single table that's going to contain all of these guests. But we've left a little bit of extra work in there for ourselves. And so what we're gonna do in the next lesson is we're gonna talk about how we can actually modify this a little bit to make it a little bit easier to work with and take advantage of some of the built-in functionality that MySQL has for us. But one other thing I wanna touch on right now before we get that far is now we've created a table for our database called guest. I wanna talk about how to actually delete it, and just like we saw before instead of using the delete command we're actually going to use the drop command. So let me clear my screen and we're going to say drop table and we're gonna drop the guest table, and we see query okay so now I can say show tables. And we're gonna see that there's an empty set, so I don't have any tables now. All right, so now that we see the basic structure that I wanna use for that guest table, in the next lesson we're gonna talk about creating a table and using some other things to specify what is going to, what columns are gonna be null, what a not going to allow to be null, and also start to talk a little bit about the concept of primary keys.