 Hey everybody, this is Brian. Welcome to the second eTutorial. Today we're actually going to be covering MySQL and how to work with it. I'm not going to cover the installation, but we're going to cover creating the database. Because the three steps to building your application are you create the database, you generate the code, and then you customize the code. So we're on step one creating the database. And I should preface this with we're not going to get into the nitty-gritty of SQL statements because I personally hate SQL. It's like an abomination and I can't stand it. So we're going to use something called MySQL Workbench, which is a graphic user interface for this. It just makes a lot of the work very painless. So go ahead and download MySQL Workbench. And if you already know databases, feel free to skip this tutorial. We're not going to cover anything related. This is strictly building the database. So we're going to load up MySQL Workbench. If you don't already have a local connection, click the little plus button and just name it local standard TCP. Give it a 127.0 to 0 to 1 address and then the port and then the username and password. Now the password will be whatever you use to set up MySQL with. If you're against using the MySQL Workbench, you're going to have to find a different tutorial on how to do this because all of MySQL's command line base. And I am not a huge fan of command line, although I know some people are. Anyways, once you have your connection set up, which I already have one set up here, you can just double click and pop goes right in. So we need to create a schema. Now different database platforms have different naming conventions. For example, in Microsoft SQL Server, they are called databases. Well in MySQL, which is free, you can get MySQL anywhere, each one of these little buckets is called a schema. But this big blue area is actually a database. So your database holds schemas which holds all the other information. So we're just going to create a new schema and we're going to create a pet store. Leave the correlation to default and just hit apply. And it shows you the actual language used. The reason why we're using a graphic user interface is because you would have to type this out and then it can be kind of buggy. And then, you know, you may have to debug and figure out what's going on, et cetera, et cetera. I'm a big fan of just doing it the easy way. So we're going to let it generate it for us. Notice how we now have a pet store schema. Now inside of our schema, we have tables, views, store procedures, and functions. Well a table is what we're really going to be focused on. That's the heart of your schema here. It's going to hold all the data. Think of a table as a bucket. And every item that you throw in that bucket has attributes called columns. Views are a way of taking multiple tables and making them kind of look merged together and then you can call them over and over again. Store procedures, that's a way of putting code in the database and having the database execute that code with a simple call. And functions is an advanced topic we're really not going to get into. So tables, we're just going to create a table. And let's call this accounts. We're going to make a list of accounts. Our pet store is going to be where people can join and they can basically sell their own pet because everybody wants to sell their pet. This is an ingenious website and I'm obviously going to make millions of dollars. Anyways, notice how there's a little key icon on this first column and it was auto-generated force, ID accounts. That's what's called a primary key. It's what identifies this object that we're going to put into the table. So we want this to be a primary key, not null, unique and auto-incremented. Those are the ones you really need to focus on. And instead of int, I usually do big int, although I'm sure I'll get feedback saying you don't really need to do that and it's a waste, et cetera, et cetera. The difference being every data type has a specific size and once you bypass that size, if it's an int or a big int or whatever, it'll flip back to zero and repeat it. Because this is unique, you cannot have two of the same numbers. If this went over its limit and then flipped back and put a number one in there, you'll get an error message. Now int is a huge number and big int is a massive number. It's like counting all the stars in the universe or something like that. Alright, so we want to do email. And notice how this is a very care 45. It stands for variable character and we can have a limit of 45 characters. I'm going to just do 255 just because I like giving people a lot of room to type. And we want to make it not null, meaning it's mandatory that they enter something. Same thing with password. How embarrassing. I couldn't spell password. It's been a long day. Now, if you kind of scroll through, you see there's a lot of different data types here. So we're not going to cover all these. Just know that the fundamental types are really you have a string or a character or a bit or a number of some type, whether it's big int or decimal float or tiny, et cetera, et cetera. And there's other types like dates and timestamps and things like that. Alright, so now we have, let's see here, we have email password. Let's say first name. I'm just going to kind of whip through here. Address, city, zip. And we're going to add phone. You can see how you can just very quickly kind of flesh out this database. Now, if you were to actually write the SQL code, sure, some people out there could write this a lot faster, but then you got to sit there and tweak it and debug it and et cetera, et cetera. I just like using the GUI. It's just a lot easier. Alright, so we are going to just hit apply. Now, this little action pane out here will actually show you what happens when you hit apply. But it's going to generate the code for you. And this is what you would have had to have typed out. Yeah, so now you see why we just use the GUI. And if you're really interested in learning all that code, there are tons of really good tutorials out there. We're just not going to cover it for the sake of time. It's a whole series in itself. And it said, apply changes to account. Notice that little green checkbox. That means it was successful. There would be a little red X if there was a problem. So now we have our accounts table. And this is what it would look like. Notice how it's blank. There's nothing in there. And this is the query language you use to select those or to actually view those. We're going to create two more tables. Create table. And we're going to call it categories. And we're going to say name. You can type it or select it. It's probably a little more bug-free if you select it. So we're going to have categories of animals, like dogs, cats, birds, fish. Things of that nature. And we're going to make the actual pets it int. That's not even a type big int. And we're going to just say name, description. Date posted. Alright, so we've got our name. We're going to say that's 255. Description. We're going to actually do what's called a text type. If I can find it in here somewhere. It's actually kind of off the screen, but text. Text is basically just an unlimited field. There's really no limit to it. It'll just keep going and going. I'm sure there's a hard limit in there somewhere. In other words, decimal. Damn you. Why are you doing this to me? Probably because I chose the decimal function and not the decimal type. There we go. Picture, we're not going to actually store the raw picture in the database. We're just going to store a file path to it. The reason for that is it makes our database easier to export if it's not actually stored in the database. I know some people like to store the data in the database. It saves them writing some code. But I actually want to show how to upload a picture and work with the file system. Date time for the date posted. Let's see here. What do we want to do this? I'm trying to think if there's anything else we want to put in here. While I'm thinking of that, I'm just going to check some of these boxes. Date posted. We should probably put a category in here. Let's move this up here. Notice how you can drag and drop and move things around. We want to set that to not null. We're just going to hit apply. It'll generate the SQL for us. Notice how it executes successfully. If there was an error, you'll have to figure out what was going on, and that can be kind of frustrating. We've got our three tables or our three buckets here. We have accounts, we have categories, and we have pets. What we want to do here is to see this ID category. That's actually not even the right type. Big int. Let's change that. What we're going to do is create what's called a foreign key relationship. We talked about this primary key. How this will identify this specific object. We want to have some categories. Dogs, cats, birds, fish. We don't want to actually type out the word dog, cat, bird, fish because we may change the category name from birds to exotic birds or something like that. What we want to do is we want to take the primary key ID categories from the categories table and shove it right into the pets table. We're going to create a foreign key constraint or a foreign key relationship. Make sure that's set to not null, and then we're going to go to foreign keys, and we're going to click here. It's going to say FK underscore pets 1. That means it's creating a foreign key in the pets table, and this is the first one. The reference table or the table we're pointing to, notice how we're in pets, is we're going to point to, you guessed it, categories. The category, and it's going to use the reference column categories. Now you can choose a different one, but it's going to take the primary key of that record by default. And then we have some different actions here. We have on update, on delete. Now what does this mean? On update means that if we updated the primary key of the categories record, it would also update it in, you guessed it, the pets table. Realistically, we would never update a primary key, but we want to cascade this just in case somebody gets a little, get a little freaky with our database sometime. What cascade will do is it'll take that change and then cascade it through the pets table. So let's say we have a category of called cats, which is an ID of 12, and we change that to 2. It's going to go through all the pets and then change all the 12s to 2. Now delete, if we delete an entire category and we cascade it, we're going to delete any animal that's in that category. So if we have 200 dogs and we delete the dogs category, we're going to accidentally delete 200 records out of our pets. You know, we're going to delete the 200 dogs. We don't want to do that. So instead, we want to set null, meaning it'll just flip it so there's no data there whatsoever for that specific column. But all the other, like the name, the picture, all that will still be there. We're going to hit apply, and there's the code it would generate. And if you get something other than this, 9 times out of 10, it's because you already have information in there. And you've got to do what's called a skip foreign key constraint. I'm not going to really show you how to do that. It's a little bit advanced, and you can kind of screw up your database if you do it wrong. Just know that you can bypass some of the security measures. All right, so we have got our tables in here. We're going to just add some stuff. So we're just adding some categories here. Now notice how these all say ID category of null. That means it hasn't been created yet. When we hit apply, it's going to insert those records in there. And voila, we now have a primary key auto-generated force. Now each one of these will be unique. So they'll never be two number twos, or two number threes, or whatever. There can only be one. Now, if we create a pet, let's select these rows. Notice how we have this ID category in here, and we've created that foreign key relationship. So this ID category points to the categories table. So instead of typing the word dog, we would put the number in there. So let's actually just say Fido. Fido is like an international dog name, isn't it? Test, Fido, whoops. Fido, let's say Fido costs $50 and 25 cents. We're not going to put a picture of Fido up here, and actually post that. Notice how there was some problems here. This is what happens if you go to enter something, and you don't have all the information you need. Notice the first thing is, cannot add or update a child row, foreign key constraint fails. What that means is we have to add that foreign key in there. So we need the foreign key of, well you guessed it, right here. So let's take dogs, and ta-da, we have now applied that. Now notice how, even though we said date posted could not be null, and we set it to null right before we clicked it, it still went blanked it out, because it's going to automatically nullify that type. So now we have an invalid date here. So let's just go. I can never remember if it's the month or the day. I think it's month and then day. Yeah, it is. And then here's the time, and we're just going to say it happened at midnight exactly. So there you go. So what we've got now is this category, ID of one, is linked to the categories, dog, yeah, the dog category. There we go. So if we were to make a cat, let's call it Mittens, because I like the name Mittens. Say Mittens costs $5, he's a very cheap cat. He just sits around all day. And I'm just going to leave that null so it'll auto-generate so you'll see it again. Notice how we have the foreign key constraint failed. What was cats? Anybody? I think it was two. Yep, we're going to apply that. And ta-da, we're done. So that's how a foreign key constraint fails. Now, or fails, it works. It fails if you have invalid data. Like if we take this and we say 88, there's no such thing as an 88 in here. It'll let you put it in the editor, but when you go to hit apply, it's going to check your foreign key constraints and say, hey, there's a problem. You can't do that. So when we get into E, I'm going to actually show you how to do enable relations where when you pull up a category, you can pull up all the related data to it, meaning, let's say you want cats, it'll show you all of the cats and all of the dogs. Or if we have more information in here, like let's say categories, select rows. Like if we wanted to put breed next to here too, so you could say dogs and then breeds and then have different types of breeds, you could actually break that out further. All right, so that's it for this tutorial. I hope you found this educational entertaining. By no means is this an all encompassing tutorial. This is just a primer so that you know what we're doing and how we got here. And that's the first step in years, actually getting your database up there. If you have questions, comments, concerns, be sure to join the Voidrealms Facebook group. There is, I think we were pushing 325 members and I'm not joking. Every time I go out there and post a question, I answered it within literally like 15, 20 minutes. It's kind of crazy. It's kind of awesome, but it's kind of crazy at the same time. Thanks for watching.