I know it may feel like we're throwing the kitchen sink at you, and it's tough. Especially in the early days, this all can be very overwhelming, because every single thing is a brand new concept to you. So as I said before, don't worry, everybody, literally every single person feels that way. But with that said, I'm going to throw something else at you. So as it turns out, PHP is just one piece of the puzzle. So just like HTML is one piece, CSS is one piece. PHP is another piece. And then your database is yet another piece. Now so far, yeah, we've just learned some of the basics, some of the 101 basics of the Why Databases Matter 0:31And then your database is yet another piece. Now so far, yeah, we've just learned some of the basics, some of the 101 basics of the language. But what about when we are dealing with actual data? When the user wants to create a task, how can you remember that task, so that when they return to your website a week later, they can still see that task? Well, this stuff just doesn't happen magically with the language. It has to be stored in a database. And you can think of a database very much like maybe like an Excel spreadsheet. It's kind of like that, okay? Choosing and Installing MySQL 0:56And you can think of a database very much like maybe like an Excel spreadsheet. It's kind of like that, okay? Now throughout your career, you're going to learn about a bunch. So like here, we had MySQL, or some call it MySQL. But you might also learn about SQLite, or NoSQL, or MongoDB, or really, there's quite a few. And all of them, in a different way, solve this problem. Now in this series, because it's most common, even in 2016, I'm going to show you about MySQL. Now, very likely, you may have it installed already.MySQL. Now, very likely, you may have it installed already. You can run MySQL and see if that boots up. In this case, I'm using 5.7. Now if you installed MAMP, or WAMP, or XAMPP, like we talked about in the first lesson, you're going to have that ready to go. So you can use this command. I'm going to hit Control D, by the way, to exit out. Otherwise, if you used Homebrew to install PHP, you may need to install this manually. Let's do brew search MySQL.Otherwise, if you used Homebrew to install PHP, you may need to install this manually. Let's do brew search MySQL. Now in this case, you can see with that green check mark, I already have that. This is how I pull it in. But for you, yeah, you may need to do brew install MySQL to fetch that. Then once it's done, you're all set to go. So that means you can do, once again, MySQL. Now let's log in with the root username. Now as you'll find with databases, there's a lot of security and things like that to consider.Now as you'll find with databases, there's a lot of security and things like that to consider. But I don't really want you to worry about it too much just yet. So we're going to log in as the root user. You can type it like this or give it a space. Same thing. Next, if you're familiar with MySQL, you might have already set a password, in which case you could set that here. Otherwise, for the root user, very often, there is no password. Okay, we're in. Creating a Database 2:38Otherwise, for the root user, very often, there is no password. Okay, we're in. So if I run show databases, so notice here, there's nothing PHP-specific here at all. We're using SQL at this point. And one of the commands we can run is show databases. Now in my case, I already have a couple. So like Laracast testing and Laracast. Those are actually the databases specifically for this website, at least locally. For yours, you'll probably just see two or three and maybe information schema. Okay, well, why don't we create your very first database?For yours, you'll probably just see two or three and maybe information schema. Okay, well, why don't we create your very first database? And in general, your database will correspond to your website. So very often, one website, one database. Now we've been toying with this idea of to-dos, right? That's kind of a common introductory example for folks. So why don't we do this? We don't want a database specifically for tasks because think about it. You could have tasks, but what about comments on tasks? And maybe you can vote on a task.You could have tasks, but what about comments on tasks? And maybe you can vote on a task. So if 50 people vote on your task, that will bump it up to the top, which makes it the thing you're doing next. But you have users that you can assign tasks to. So all of those are like different components or the way we'd refer to them is tables. Each of those would represent a different table, a users table, a to-dos table or tasks table, a votes table, a comments table. Each of those are like their own unique spreadsheets that can communicate with one another. Now for the database name, yeah, frequently it may correspond to the name of your webEach of those are like their own unique spreadsheets that can communicate with one another. Now for the database name, yeah, frequently it may correspond to the name of your web application or your website. So maybe the website you're building is called mytodo or something like that, mytodo.com. Okay. Well, let's create the database for that. Create database mytodo. Now if we ran that, notice that it went on to a new line. And that's because in MySQL, just like PHP, just like some other languages, you have to end the command with a semicolon.And that's because in MySQL, just like PHP, just like some other languages, you have to end the command with a semicolon. And that declares I'm done with this particular action or command. Okay. Query okay. We're all set. So now take a look. If I run show databases again, and a quick little tip, if you press up, it'll cycle through your previously triggered commands. So if I hit up again, I go back to show databases.through your previously triggered commands. So if I hit up again, I go back to show databases. And now you can see mytodo in there. Okay. So now I don't want to work with the Leracast database. I want to use the mytodo database. So I say that, use mytodo. And think of that almost like opening up a folder. We're going to use this database now. Let's see what tables we have. Creating Tables and Columns 5:14We're going to use this database now. Let's see what tables we have. So we've created an empty database, but there's nothing in it whatsoever. Why don't we create our first table? Now I get it, writing some of this code is a little funky, just as a heads up. What you'll learn is as you advance in your career, you're going to learn about abstractions that will make all of this a lot more enjoyable and easy to write, so that you're not always having to open this up and then write, create table, and then remember all of the column types and stuff like that. So just rest assured there are ways to make it easier in the future, but for now, it'stypes and stuff like that. So just rest assured there are ways to make it easier in the future, but for now, it's good to learn the manual way. Okay. What do we want for our first table? Like I said, you could have a users table, a votes table, a comments table, or a to-dos table. Let's create that. What is the makeup of this table? And once again, just imagine an Excel spreadsheet.What is the makeup of this table? And once again, just imagine an Excel spreadsheet. What kind of columns would you want? Well, you might want the description of the to-do, right? So let's add that. Next, you would want to know whether it has been completed or not. So why don't we stick with these two for now? But if I run this, not going to work. You have an error in your SQL syntax. Okay, fair enough.You have an error in your SQL syntax. Okay, fair enough. So here's the deal. For every column type you declare, you also have to specify what type of content is it. So are we dealing with, and this can be once again a little tricky, but are we dealing with an integer or a string of text or a variable number of characters or are we dealing with a set of available items you could choose from? That would be an enum. You could also choose from a Boolean. Once again, there's a big list of them.You could also choose from a Boolean. Once again, there's a big list of them. It can be kind of overwhelming at first. So that's why we're starting with these two. The description should be a description of the task, and we don't know how many characters that would be. So I'm going to use the text type. And that way, if you want a paragraph to describe this to-do, that's okay. Next, completed, well, as it turns out, your database is going to think of it in terms of zero or one.Next, completed, well, as it turns out, your database is going to think of it in terms of zero or one. So zero for false, one for true. But we could also write Boolean here just to make it a little more explicit on our end what we expect here. A Boolean represents true or false. Now, if I run this, sure enough, it will work and we get query okay. And if we show our tables, we can see to-dos. We can also say describe to-dos, and that's going to give you the makeup of it. All right.We can also say describe to-dos, and that's going to give you the makeup of it. All right. So description is text, completed is set to tiny int, that is one character long. So once again, zero or one, false or true. But there are some other things here. We can see null. What does that mean? That means are we allowed to add a new row to this table where this value is essentially empty? We haven't talked about null.empty? We haven't talked about null. But think of null as nothing, the absence of anything. It's a null value. Well, in this case, we've said for both of them, yeah, you can create a new row that doesn't have a description. And yeah, you can create a new row where the completed value is nothing. But if you think about it, that's not quite right. So let's do this. Drop table to-dos, and that will delete it entirely.So let's do this. Drop table to-dos, and that will delete it entirely. So now, once again, show tables. And don't forget, I hit up a few times to go back between those. We've deleted it. So if I hit up, we're going to create the table again. But now we can say text, not null. You have to give me something, otherwise I'm going to throw an error. Okay. So now if we describe to-dos, those will be set to null.Okay. So now if we describe to-dos, those will be set to null. Okay. But you know what? I'm going to drop the table one more time because I want to show you one other thing. Okay. If I hit Command K on the Mac, I can clear the screen. I'll hit up a few times. We're going to add one more row, and this can be kind of tricky. We're going to add an ID here, which is incredibly common, and most of your tables will have Adding IDs and Primary Keys 9:04We're going to add one more row, and this can be kind of tricky. We're going to add an ID here, which is incredibly common, and most of your tables will have it. So the ID is basically an auto-incrementing number for each row in your table. So for example, the row with an ID of 1 consists of this particular to-do, and then the row with an ID of 2 consists of that to-do, and then the row with an ID of 3. So basically, every time you add a new to-do item, the ID will go up by 1. So 1, 2, 3, 4, 5. And we use this for a number of reasons, but most importantly, to serve as the identifier for the row, and particularly, this will be used to track down the row later.And we use this for a number of reasons, but most importantly, to serve as the identifier for the row, and particularly, this will be used to track down the row later. We'll talk about that. So imagine you have lots and lots of tasks, and you want a way to say, give me the row that has this description. Well, that's kind of awkward. Instead, you would just want to say, give me the row that has the ID of 25. That's the one I want. Or give me the row that has an ID of 4. That's the one I want.Or give me the row that has an ID of 4. That's the one I want. So this is incredibly common, and you'll do it for most of your tables. The ID will be an integer. It should auto-increment. But now, I'm going to add one more here, and it's going to confuse the heck out of you. It confused me as well. So what is a primary key, and what is a foreign key? As you continue learning about MySQL on your own, these things, they're going to come up, and they're going to confuse you, which means, by the way, you're entirely normal.As you continue learning about MySQL on your own, these things, they're going to come up, and they're going to confuse you, which means, by the way, you're entirely normal. The primary key is the unique identifier for a row. And remember, when I say row, think of that Excel spreadsheet, where you add new rows in there. It's kind of the same thing. So if you think about it, for a task or to-do, you could have multiple rows with the exact same description, like go to the store, go to the store, go to the store. So we need some way to identify this row from that row. And if they both have the exact same description, they're almost the same thing.So we need some way to identify this row from that row. And if they both have the exact same description, they're almost the same thing. You know, there's no unique identifier. So that's what we use a primary key for, and generally, that's going to be assigned to your ID. This is how we say this row is always going to be unique. And the row with an ID of 4, well, there will never, ever be another row with an ID of 4. That other row will be an ID of 5 or 6, but not 4. Okay, so this looks pretty good to me. We're going to run this, but again, notice the basic format here.Okay, so this looks pretty good to me. We're going to run this, but again, notice the basic format here. A column name followed by its type and any modifiers, and then a comma, and then a new column name followed by its type and any modifiers, then a comma, and then another name followed by its type. All right? So let's go ahead and run this, and once again, query okay, which means it worked. If it didn't work on your end, hit up, and then fix your mistake and try it again. Show tables, then describe to-dos, and now we can see. We have our ID column.Show tables, then describe to-dos, and now we can see. We have our ID column. That is an integer. It may not be null. It is a primary key, and it will auto-increment. That means automatically the number for this column will increase each time a new row is added. Let's try it. We have a new syntax here now. So let's do a quick recap. Inserting and Querying Data 12:20We have a new syntax here now. So let's do a quick recap. Show databases shows you all of the databases in your system. Show tables will show you all of the tables in a database. Show database allows you to choose which database you're working with. Create table allows you to create a table, and now insert into table allows you to insert a new record or row. Okay, so we want a description and complete it. Now once again, why not the ID? Well that's because it will automatically increment, so you don't have to do anythingNow once again, why not the ID? Well that's because it will automatically increment, so you don't have to do anything there. It will automatically set that number and increase it by one for each new row. Values will be go to the store, and completed will be false. All right, query okay, and we know we've added a row because it says one row affected. So how can we see these rows? Well here's another one. Select. So we're not creating a table.Select. So we're not creating a table. We're not inserting into a new table. We are selecting from that table, and think of that as a way of saying show me the records. So select star. Think of star as your way of saying everything. Select star from the table called to do's, and now we have one record. Okay, so this is kind of cool. It's kind of fun once you learn the syntax, but doing it from the command line can definitely be a little tedious. Using a MySQL GUI 13:39It's kind of fun once you learn the syntax, but doing it from the command line can definitely be a little tedious. It's really good for you to learn the syntax because it's kind of the bedrock. Weirdly enough, it's kind of the bedrock of PHP or any language. You're often going to be working with a database, so it's really important that you learn the syntax. But nonetheless, there's lots of GUIs to make this easier. As always, just Google MySQL GUI or MySQL app, and you're going to find lots of different choices like a NaviCat or one I use is called SQL Pro. This seems to work really well for me.choices like a NaviCat or one I use is called SQL Pro. This seems to work really well for me. It's simple but very powerful. Another option is Querious. This is fantastic as well for the Mac. And yet another option would be NaviCat. And once again, you can tinker around with all of these or find another one altogether. Let me show you SQL Pro. Here we go. So you can see I have a handful of layer cast specific databases set up like my stagingHere we go. So you can see I have a handful of layer cast specific databases set up like my staging server, a development server, a production one. But let's add a new connection here. So we're going to call this local. The host, now very typically, this is just going to be set to local host. And if we run that, it's going to let you know, well, do you mean the IP for local host? Yes, we do. The username, well, the default we're using is root right now. And I said there's no password.The username, well, the default we're using is root right now. And I said there's no password. So let's add this to our favorites and then connect. And we're in. We can choose our database, my to-do, and then here's my table. Notice there's the record we added. So at this point, if you do want to add new records, you don't always have to go through that process of writing this out. It's kind of clunky, not fun. I could just click on the plus or double click and say, finish up screencast, completed isIt's kind of clunky, not fun. I could just click on the plus or double click and say, finish up screencast, completed is zero. Or I could update one, completed is one. And I can still tinker around with the syntax by going to query and saying things like select star from to-dos. Or let's target one just for fun. Give me all of the information for a row from the to-dos table where the unique identifier is one. ID equals one.is one. ID equals one. And now it returns exactly one match. Okay, so if this is all new to you, I think I've thrown a lot in your face and it's kind of overwhelming and you've already forgotten a bunch of those commands we learned at the beginning of the video. You're completely normal. This is standard stuff. So if you need to, go back and watch it again. And as always, the best way to learn is just dive in and start writing these commands outSo if you need to, go back and watch it again. And as always, the best way to learn is just dive in and start writing these commands out from scratch.
