‫All right.
‫So once you have MySQL installed you can use the MySQL shell or the CLI.
‫It's a command line interface.
‫And we're going to use this just for now just to kind of get you used to making queries using SQL or
‫SQL.
‫Now I do have a gist file here.
‫It's a cheat sheet that I made.
‫I believe I made it quite, quite a while ago with my I think it was with my my SQL course, my crash
‫course on YouTube.
‫But this will show you how to do just about everything.
‫So I'm going to give you a much lighter tutorial right now, but if you want to check this out, the
‫the documentation will have a link to this if you want to look at it.
‫All right.
‫So let's just log in.
‫And I showed you how to do this in the last video we can do MySQL dash u root okay.
‫We're using the root user.
‫And then dash p.
‫And we want to enter our password which for me I don't have a root password.
‫So I'm just going to hit enter.
‫Now we're in the shell and we can clear it up with control or command L.
‫And let's start by just showing our databases.
‫Now you do have to put a semicolon at the end of your statements.
‫So show databases is a statement that obviously shows our databases.
‫And you'll probably see these by default not the dev but the rest of these.
‫And these are just system databases.
‫You're not going to touch those.
‫You're going to create your own new database.
‫So just for now, just to kind of learn, let's create a database called blog.
‫So we can use create.
‫And by the way you don't have to use all caps.
‫But that's kind of the convention when we're dealing with SQL code.
‫So that's what I'm going to do.
‫But you don't have to.
‫So let's say create database and then we'll name it Blog Semicolon.
‫There we go.
‫Now if I go and I hit the up key, I can go back to my past queries and I'll do show databases again.
‫And now you can see that the blog database is there.
‫So if you want to use a database to add or select or whatever the data in that database, then you can
‫do the use statement.
‫So I'm going to say use and then blog.
‫So now I've changed to that database and I can make queries.
‫Now we can see the tables.
‫If we do show tables.
‫And you'll see I have an empty set meaning I don't have any tables.
‫And when I say tables you can kind of think of it as like an Excel spreadsheet where you have tables
‫and columns or, I'm sorry, rows and columns, and you can think of a table as like a collection of
‫specific data.
‫So for instance, in a blog you're going to have a post table, you're probably going to have a users
‫table.
‫You might have a categories and comments table.
‫So all the grouped data okay.
‫So let's create a table.
‫So I'm going to say create table.
‫And I want to call it posts okay.
‫Now in your table you're going to have fields.
‫And your fields have to be defined with specific types.
‫There are some attributes you can add to certain fields.
‫So I'm going to open up some parentheses.
‫And I'm going to you know what I should probably do this.
‫Like in a text editor first, just so I can have have separate lines and then maybe I'll just post paste
‫it in.
‫So let me open up.
‫You know what?
‫I think we can do this test dot SQL.
‫Can we do that?
‫Yeah.
‫So let me just let me just type it out here and then we'll type it into the terminal or paste it into
‫the terminal.
‫So we can say create table post right and end it with a semicolon.
‫And in these parentheses is where we want to have our fields.
‫Now you're always going to have an ID field.
‫It's going to be a unique ID.
‫So no two posts will have the same ID.
‫And we need to give it a type.
‫And in in MySQL there's many different types.
‫But int is one of the most common.
‫That's an integer.
‫So it's a number.
‫Now we also want this ID to auto increment meaning that it'll start at one when we enter a new post.
‫And then if we enter another post it will go to two, enter another, it'll go to three and it'll just
‫keep incrementing.
‫So to have it do that we need to add auto increment.
‫It should be auto underscore increment.
‫I don't know why that popped up.
‫I believe it's underscore increment.
‫So in addition to that, we want it to be unique and we want it to be used as the primary identifier
‫or the primary key to identify a post.
‫So we also want to say primary key like that.
‫And then we'll put a comma.
‫Now we want to add our next field.
‫So I'm going to add a title and the title I want this to be a string.
‫Although string isn't a specific MySQL type.
‫What we're going to use is a varchar like that a variable character okay.
‫And then in parentheses we can put how long we want this to be.
‫And I believe 255 is the max.
‫So that's what I'll put.
‫And I want to make it so that this title can't be null.
‫So basically it has to be it has to have a value.
‫So we'll say not null.
‫Okay.
‫You don't have to do that.
‫It's just something that I usually do for most fields.
‫Now the next one we're going to do is the body.
‫So remember it's a post.
‫So we have a title a body and the body I don't want to limit to 255.
‫I want it to be longer.
‫So we can use the text type.
‫And we don't have to enter a number of characters.
‫And I'll also say not null for this.
‫All right then I want to have a created underscore at field.
‫This is very common.
‫This is just the date and time that it was created at.
‫And you can actually set this to the type of timestamp.
‫And then we can add a default value, which will be the current time or the current timestamp, so that
‫we don't even have to worry about it whenever we insert a post.
‫This will auto fill, so we do that by setting default and then setting that to this current underscore
‫timestamp.
‫All right.
‫So that's an example of a query that will create a table and create all of our fields.
‫So let's copy that.
‫And then let's go over here and let me just delete all this.
‫And then I'm going to paste that in.
‫And we'll run that and it says query okay.
‫Zero rows affected because we didn't affect any rows.
‫Rows are in the table and we have the table but no rows.
‫So let's say show tables again.
‫And you'll see.
‫Now we have a post table and we can add data to this table.
‫So the next thing I want to do is insert some data.
‫So again I'm going to go to I'll go back to VS code.
‫And by the way, these queries are in the in the docs for the course, so you can get them there as
‫well.
‫So I want to insert data.
‫So for that we can say insert into and again this doesn't have to be uppercase.
‫It's just the convention.
‫So we want to say insert into and then the name of the table which is post.
‫Then we want the fields that we want to add.
‫So let's add a title and a body.
‫Remember the id is autoincrement so we don't add that.
‫And the Createdat field has a default value of the current timestamp.
‫So we don't need that.
‫Then we're going to say values.
‫And in here is where we put the actual values.
‫So let's say post one and you want to put it in it's a varchar or a string.
‫You want to put it in quotes.
‫And for the body we'll say this is.
‫Post one and then a semicolon.
‫Okay.
‫So if I grab that and then we go to the terminal here and I paste that in we get query okay.
‫One row affected.
‫Let's insert one more.
‫So we'll say this is post two.
‫And we'll change the title here to post two.
‫And now let's select the data so that we can show the post we just added.
‫So I'm just going to clear this up with command L.
‫And then we can use the select statement.
‫So if we say select and let's say we want to get the title.
‫So we'll say select title from and then the name of the table post.
‫And if I do that you'll see title post one.
‫Post two.
‫Now in many cases you're going to want to get all of the columns, not just the title.
‫So what we can do is instead of select title, we can change that to an asterisk.
‫And that will get all of the different all the columns or all the fields ID title body.
‫And notice that the ID started at one, the second one went to two.
‫If we add another one, that'll go to three.
‫In fact we'll do that now let's say three.
‫And we'll change this title to post three.
‫And then if I do select all from post again, now we have three posts and created that has the current
‫date and time where we where we inserted the data.
‫All right.
‫So now you know how to create a database.
‫Create a table.
‫You know how to insert a post.
‫You can or I should say insert a record.
‫You know how to select records.
‫If you want to select a single post or a single record, then we could still use select.
‫We could say select all from posts and then we can use the Where clause.
‫So I'll say where the not the title where the ID usually you're going to select using the ID because
‫that's always going to be unique.
‫You could have two posts that have the same title.
‫So let's say where ID equals two.
‫And notice that it only gives me back the one with the ID of two.
‫So that's selecting a single, you know, a single row or a single record.
‫Now to update, let's say we want to change something.
‫We can say update.
‫Posts.
‫So we're updating the post table.
‫I want to set the body field equal to.
‫Let's say this is an updated post.
‫And then we can use where.
‫So we want to say where ID equals to.
‫So if I do that and then we do a select of ID to.
‫If you look at the body.
‫This is an updated post, so I just changed the body of that post.
‫So that's how we can update.
‫Now to delete we can say delete.
‫From posts where ID equals and we'll do two.
‫Now, if I try to select all from posts where ID equals two, I get empty set because there's no longer
‫post two.
‫If I select all, let's go back to Select all from post.
‫You'll see there's only 1 in 3.
‫And if I were to add another post it'll be four.
‫It's not going to go back and create another two just because I deleted it.
‫It'll keep it'll keep incrementing.
‫Now we can also insert multiple records at once.
‫So let's actually, you know, what I'll do is go back to here and we can say insert post or into post
‫title body values and let's say post.
‫We'll just do post.
‫What are we at for?
‫So say post for.
‫This is.
‫Pose four.
‫Okay, now all we have to do is put a comma and open up another set of parentheses.
‫Then we can do post five or whatever we want to call it.
‫The title and the body.
‫This is post five.
‫Okay.
‫So we can do it like that.
‫So if I grab this and then we go back.
‫And we paste that in.
‫Now we do a select all.
‫Now you can see post four and five were added so you can add multiple records at once.
‫So that's the that's the basics of querying your database.
‫That's those are Crud operations.
‫Create read update and delete.
‫Now if you want to drop the whole database or delete the whole database, you can do drop database.
‫And this will delete the whole blog database just to let you know.
‫So we can say drop database blog.
‫And now if I do show databases.
‫Blog is no longer there.
‫Okay.
‫And if you're not careful in your application, if you're not doing your queries right, other people
‫with bad intentions can actually do this.
‫They can put this in a form and have it get added to your query and drop your whole -- database.
‫So you've got to be really careful with that.
‫That's why you want to use PDO with prepared statements.
‫So that kind of stuff doesn't happen.
‫And I'll get more into that later on.
‫But that was just a quick crash course in just SQL queries and just using the shell so we can exit this.
‫And in the next video we're going to look at MySQL workbench, which is a GUI, a graphical user interface
‫where we can do this same type of stuff.