 Hey everybody this is Brian and welcome to the 48th lamp tutorial. Today we're going to be discussing how to create a database and add some tables. So in the MySQL workbench we're just going to click the little database icon where it says create a new schema and we're just going to call this MyStore hit apply and this is the actual SQL code that's getting sent to the MySQL server create schema and then in single quotes MyStore. Now notice how a string needs to be in quotes create means we're creating something we're creating a schema or a database and its name is MyStore just hit apply all right now we have our database but there's no tables no views no routines so we're just going to right-click create table and we're gonna call this TBL customers. Notice how this first column here is automatically created for us ID TBL customers ID stands for identifier what we're doing here is we're making our identifier row we're going to select PK for primary key notice how the icon changes to a little key we've briefly talked about this in other tutorials primary key is a way of identifying this specific row of information think about it if you've got a thousand customers what it's the chances you're going to have four or five John Smiths well you need to know the differences between those John Smiths they need a unique number called a primary key so we'll select PK for primary key NN for not null meaning it cannot be empty UQ for unique meaning it must be unique number and we're going to jump over to AI auto increment so what we're saying here is that it's going to be the primary key can't be null has to be unique and it will auto increment itself whoo that's a mouthful now we're gonna say first name notice how it automatically fills in fair care 45 you remember from our previous tutorials that this is a text type and we're saying it can have up to 45 characters and we're gonna say last name I cannot type tonight and let's just say age now age is a number type so we don't want to text type in here so you just drop down you could say int but that would be kind of wasting space I don't know many people that old and you see wow there is a lot of data types we didn't cover the reason for that is there's so many that well we'll never really use them in these tutorials like multi-polygon we're not gonna use that we are gonna use tiny int though because that's 0 to 255 so you can feel free to explore things like indexes that's a way of how the data is actually stored foreign keys we're not gonna really get into this right now but a foreign key would be think of like you have a purchase order for your store you have a customer and a product a customer is buying a product well you'll have a purchase order table and each row in the purchase order will have its own primary key the foreign keys would be the customer ID and the product ID triggers you can actually write triggers so that if something happens like this row is deleted it'll actually go out and do other things partitioning we're not even gonna touch and options you can go in and just kind of explore some of the other options go back to columns hit apply and this is the actual sequel man it's gonna be generated for us create table and then here's our database name my store dot table name that's basically called a namespace what we're doing is saying database dot table name and then we're going to in parentheses that's our command who that's a lot of stuff look at that now we have a table called customers so let's go in here and just explore this select 1000 rows and there's no data in here because we haven't added anything yet so that and not show us how you do that we're just gonna make another one here we'll make a oops I just clicked edit table did I did create table we're gonna say TBL products and we're just gonna say a product name now some standards would actually have you separate out like that put an underscore right so let's say cost and we're just gonna go down here find the double there we go so now we have a primary key a name actually let's just call that description and cost we're just gonna apply there's our statement and there we go so now we've got two tables we have our customers and our products pretty neat pretty simple to understand now we can actually go in and edit the data whoops for this and see how it says select star from database table well this is called a select statement this is how you actually select information from this and there's nothing in there because we haven't added anything yet we're gonna cover the insert statement real quick here we're going to send a sequel editor and notice how you have select all insert update delete and create a select statement will select information we're saying select all we're gonna get into select in another tutorial it's a little complex insert is a little easier you're just inserting information into the database update means you're updating existing information and delete means you're deleting it now create means you're gonna actually create the table so what we want is insert oh hi kitty oh kitten's awake I can hear me on we're gonna get rid of this because that's our primary key it's auto increment I swear I am invisible to this cat until I start recording notice how I added these quotes first name and last name because there's string types you need quotes so we're just gonna say Brian and you add your name really kitty go find a hobby go go tear apart the Christmas tree I'm trying to record a tutorial here sorry cat really wants my attention so the structure of this is insert into database table name and then our fields and notice how they're in in parentheses then we have a value list this looks an awful lot like an array doesn't it well you'd be right it is an array and then our values and they have to line up one for one for example our first value is our first field our second one is our second one third one is our third one notice how these are in single quotes because there's strings basically they labels all right we've executed that now let's go back out here select from some information you see how we have our our little record in there notice how there's a one in there even though we never added a one that's because that was the auto increment that's our primary key field let's add another row in here we're just gonna change this data and now we have two of them notice how it says one two because that's our auto increment field we have Brian and Heather and they're two different ones now let's add Heather again just to see what happens notice how there's two Heather Karens but the primary key is different for each one that's why you need a primary key to distinguish between these two heathers although as my daughter would say there can be only one so that's all for this tutorial we're gonna get a little in depth on the other commands here very soon thank you for watching and I hope you found this educational entertaining