A database is a structure that holds the tables and data for your application or business. Using the command line in the terminal, this video demonstrates how to log into MySQL and the process of creating a database. You will also see how to structure commands and learn basic commands such as SHOW, CREATE, USE and DROP. Step by step, you will see how to create the database and access the database.
- View Offline
In this movie we're going to learn how to log into MySQL and then we're going to create our first database. Now low into MySQL from the command line. That's how we're going to interact with MySQL is from the command line. There are other tools that will let you connect to MySQL. We're going to be sticking with the command which is going to be the simplest version. From my Mac I'm going to be doing that from terminal. I've already asked it MySQL--version which just lets me know that MySQL is there, it's been found, and it's a reasonably recent version. If you don't get something useful back from doing that, you may need to go back to the installation configuration section and get yourself set up before you come back here. Once we have everything set up, and working though, then we can log into MySQL using MySQL.
And then the user that we want to log in as, -U, and then root. That's going to tell it that we want to log in as the root user, that's the default user and also the most powerful user in MySQL. And then we need to provide the password for root. We set up a password in the configuration section, so the password would be dash, dash, password equals, and you could write your password out there. Or it's more secure if we don't want to show it to use dash p and then we hit Return, It'll come up and ask us for that password. The password that I set up in configuration was johnpaulgeorgeringo.
All run together and all lower case. You may have set up something different. You'll want to use that here. So johnpaulgeorgeringo. When I hit Return it says welcome to MySQL. I'm now inside MySQL. You can tell that because there's a prompt here that says MySQL, and it's waiting for my commands. The commands all need to end with a semicolon. You can see that it says help semicolon here is the command that we use if we want to get help. Well, the first command we're going to learn is actually exit semicolon, and that will just exit us back out of it.
And quit would work also. Let's hit the up arrow, then, from the terminal, and we'll log back in, johnpaulgeorgeringo. Okay, so now it's here waiting for our commands. Let's learn our first four MySQL commands. The first command is Show Databases, and this tells MySQL, show me a list of all the databases that you're currently holding onto, that you know about. Remember, MySQL can hold more than one database. We're only going to be working with one database though. And we're going to need to create that database, it doesn't exist yet.
And we'll do that with Create Database followed by the name of the database that we want to use. Now the capitalization doesn't strictly matter, but it is a best practice that makes your code more readable. So, typically the commands that are for MySQL, the MySQL keywords, are going to be in all caps. And then your code, your field names and table names. Will be followed by that in whatever case you've got them in, and I prefer lowercase with underscores. And then all of these commands end with a semicolon, so once we've created the database, then we'll need to use the database. That'll switch us into the database so that we can start using it.
If we wanted, instead, to get rid of the database, we could drop it. That's the opposite of creating it. So, it's not delete, it's drop. And that drops not just the database, but all of the tables and all of the data that are in there. So, it's a big deal, you don't drop your database casually. So, let's try the first one of these. Show databases semicolon, and there you go. It gives me a list of the databases that MySQL knows about. It already has a few databases. These are databases that are there for its purposes. We don't need to worry about them. It doesn't have our database yet. So, let's create that Create Database and what should we call it. Well the application that we're going to be creating a little later on its going to be a content management system for a fictional company called widget corp.
So, that's what I'm going to call this widget corp with a semicolon after it. So, that's going to create this database for us. Now, I'm going to use the up arrow two times. And that'll take me back to the show databases command again. Save me from retyping it. I'll hit Return. And now, we see our database in that list. If we want to use that database, then we use the use command. And it switches us into it. So, now it says, database changed. Before we were just in MySQL generally, now we're actually in our database. And that makes it so that when we issue commands MySQL will know which database those commands are concerning. If we had more than one database, this is also how you would move between the different databases.
Now we could do drop database that was the other command that we learned. We could remove it and any tables that it contains but we won't do that now. And again you'll want to be careful, you don't want to drop a database that you don't mean to. When we connected to MySQL. We logged in as the root user and I mentioned that that's the most powerful user with lots of access privileges to perform actions inside MySQL from creating tables to dropping them. We could log in as root from our application too, but I think that's a rally bad habit. I think it's much better to create a new MySQL user, assign it a password and then grant access to our database to that user.
It limits the scope of access for that user to just a single database. And if that user was ever compromised they wouldn't have anything more than what's in that single database. It's better security and there's no excuse for not doing it because it's super easy to set up. All we have to do is have a simple command where we say grant all privileges on our database name, followed by a dot and an asterisk. That means all tables on that database, to whatever the username is, at localhost identified by password. The fact that it's from localhost means that the user can only log in from the same computer.
You won't be able to remotely log in from another IP address as this user, only locally. And then after we create those privileges, we can just check that they were there by using the command Show Grants For. And it'll show us the privileges that have been assigned for that user. Let's do this now. So I'm just going to clear my screen, bring it back up at the top, and let's type Grant All Privileges. Make sure you spell that correctly. Grant all privileges on, and then the name of our database. A period and an asterisk.
That means all tables on the database. And then, I'm going to type a space and hit Return. Now, I could just keep typing, but it will let me type a return, and then, since I haven't typed the semicolon yet, it expects that the command is still going. So, then, To and then, I'm going to give my new user a name here. This is the new user I'm inventing. I'm going to call it widget_cms at localhost. And then another space followed by a Return. And then identified by and then whatever password you want to use here.
So, I'm going to make this secret password. You'll want to use something that's a little better than that. But now I'm going to have two passwords on my system, my root user password, and then my widget_cms password, which is just secret password. So, I'll hit Return, and then if I want to see if that took place I can type show grants for widget_cms at. Local host. And there it shows, so there, you can see that widget cms has privileges. Now, don't worry about the specifics of what those privileges mean. The fact that we see anything there means that our command worked.
So, now let's try exiting. 'Kay, now let's try logging back in, this time MySQL -u widget_cms dash p. And let's do one other thing before we hit Return, which is that we can specify the database that we want it to drop us into. That is, it saves us from having to type use. We go into MySQL and instantly get switched in to this database. Most often, I think this is how you'll use the command. You'll go ahead and put yourself right in the database that you want to use. So now, I'll type my new password, secret password.
And there I am. Now, I'm inside. Now let's do, Show Databases. You can see that I don't see all of the same databases that I used to see. I have much more limited access now than I did before, that's a good thing. So, now that we have our database created, we're ready to create our first database table.
- What is PHP?
- Installing and configuring PHP and MySQL
- Exploring data types
- Controlling code with logical expressions and loops
- Using PHP's built-in functions
- Writing custom functions
- Building dynamic webpages
- Working with forms and form data
- Using cookies and sessions to store data
- Connecting to MySQL with PHP
- Creating and editing database records
- Building a content management system
- Adding user authentication