On September 14th, 2017, we published revised versions of our Privacy Policy, Terms of Service and Website Use Policy and published a Cookie Policy. Your continued use of Lynda.com means you agree to these revised documents, so please take a few minutes to read and understand them.

Create a database

- [Instructor] In this movie,we're going to log into MySQL and create our first database.In order to interact with MySQL, we're going to do thatfrom our command line program.If you're on Windows,that program is going to be command prompt,on Mac it's going to be the Terminal.So here I am inside my Terminal applicationand the first thing we want to do,is make sure that we have MySQL installed.So if you don't have it installed already,you'll want to do that.Let's just check and see here, mysql version.You see I have Version 5.7 installed.It doesn't matter if your versionis slightly different from mine,MySQL in general works pretty much the samein all the versions.

Then we want to actually try to log in,to make sure that it's running.We try mysql -you and I have a root user created.Every MySQL installation has a root user by default.I'm going to hit return, and you'll seethat it doesn't open up for me,and that's because I didn't tell it that I had a password.By default, a lot of timesMySQL doesn't have a root password,but it's a good idea to make sure that you do have one.Secure that root user, because the root useris your most powerful MySQL user.It's the user that has the abilityto do anything inside MySQL.

So I'm going to also put the -p option after it,and that's going to allow me to provide a password.So now it's going to ask for my password, and there it is.Now I'm logged into MySQL.So I know I have MySQL installed and it's running.So now we're ready to work inside MySQL.What are the commands that we want to issue?There are four main commands we need to knowabout creating databases.The first is show databases, and that just simply shows usa list of the databases that are already there.

Notice that it has a semicolon at the end,all commands in MySQL should end in a semicolon.That's how MySQL knows that the command is done.The capitalization doesn't matter, but it's a best practiceand it makes your code more readable,so I tend to capitalize things.The second command is to create the database.All we have to do is tell itthe name of the database we want.Create database and then the name of the database.Capitalization does matter for the database nameand there should be no spaces in it,but you can use underscores.Then, when ready to actually use the database,we can use database name.

If we want to drop the database, or get rid of it,you use drop database and the database name.Use database name is really only if you didn't specifythe database when you logged in,or if you want to switch between databases.It's not something we're going to use very often,but if you have multiple databases,it allows you to move around between them,so that you're currently in an active database.So to begin with, let's just do show databases.You can see the databases that are there by default.These are the default MySQL databases.They're not for your use, they're there for MySQL to use,so don't mess with any of these basic ones that are there.

But we want to create our own.So create database and then let's call it globe_bank;That's it, that's all it took to create the database.Now I'm going to hit the up arrow.It'll allow me to go back to show databases again,and now you can see that my database is listed there.As I said, we can use the database,and that will switch us into it,so it's our current active database that we're working with,and we can also drop the database.Drop database globe_bank; and just like that,our database is gone.

Now hopefully we didn't have lots of data in there,because as soon as we say drop database,you can see, everything that was in that databasejust instantly disappears.It's a very powerful command.So we want to be careful about that.I'm going to hit the up arrow a couple of times,so that I can create my database again,and now show databases,you can see it's going to list it again.So now I have my database there.Now when we logged into MySQL right now,we did it as the root user.That's the most powerful user with lots of privilegesto perform actions inside MySQL,from creating tables to dropping them.

We could log in from our web applicationas the root use as well,but I think that's a really bad habit.It's much better to create a new MySQL userand assign it a password,and then grant access to our database to that user.It limits the scope of access, to just that single database.Now, we don't have access to everything,we only have access to the databasewe've granted privileges on.It's better security and there's no excuse not to do it,because it's easy to set up.All we have to do is type a command like this,grant all privileges on, and then the name of the databaseand then a period and then an asterisk.

The asterisk says we're granting privilegesto all tables on that database, and that's a best practice.Then there's a space after the asterisk,that you can't see because I dropped to a new line,and then it says to, and then we want to create a username.This is going to be a new user that we're creating.It can be any name you want,inside those single quotes and then @and then the IP address, or the locationthat that user would be allowed to log in from.We're going to use localhost.That means that only from this computercan that user log in.

They can't log in from some other foreign country,some other IP address and get into this database.Only our web app, which is locally hosted, can access it.That's another good security practice.Then there's a space at the end of that,and then the next line is, identified by password.If this user already exists in the database,we can leave out that last bit,but if it's a brand new user,this gives us the chance to say what the password isthat goes with that username.We can also take a lookat what grants have already been given to that user,using show grants for that username.

All right, so let's try adding a new userand granting privileges on our database to them.So again, my command is going to be grant all privilegeson, and then the name of the database,globe_bank. and then the asterisk, because it's all tables,to, I'm going to call my user webuser.You can call it anything you want,and then @localhost, and then a space, identified by,and then whatever password you want to use.

I'm going to use secretpassword, just to have a convenient one,but that's not a great password.You should hopefully pick a better password.So pick a good password that you can use for your user,and then let's hit return.Now we have a new user created called webuser,and that user can use their username and passwordto access our globe_bank database.

Resume Transcript Auto-Scroll

Author

Released

6/27/2017

PHP is a popular programming language and the foundation of many smart, data-driven websites. This comprehensive course from Kevin Skoglund helps developers learn to use PHP to build interconnected webpages with dynamic content which can pass data between pages. Learn how PHP can simplify the creation of forms, read and validate form data, and display errors. Kevin also covers the fundamentals of MySQL and how to use PHP to efficiently and securely interact with a database to store and retrieve data. Throughout the course, he provides practical advice and offers examples of best practices.