7 days of WordPress plugins, themes & templates - for free!* Unlimited asset downloads! Start 7-Day Free Trial
FREELessons: 16Length: 1.5 hours

Next lesson playing in 5 seconds

Cancel
  • Overview
  • Transcript

1.3 General MySQL Commands

Before getting into the nitty-gritty of MySQL, it will be beneficial to understand some fundamental concepts. In this lesson, you'll learn some basic commands that will let you figure out what databases are available in your instance of MySQL, as well as how to display which tables are currently in each of those databases.

1.3 General MySQL Commands

Now before you can actually get it and really start doing anything with MySQL, especially from the command prompt, you have to be able to get a little bit of a lay of the land. And so that's what I wanna take a few moments to teach you in this lesson. So some of the basic commands that you're gonna need to understand, is to be able to list the databases that are currently in your installation of MySQL, how to get into those databases, and then how to take a look at what tables are currently there. So let's go ahead and start with listing the databases that are currently on your installation of MySQL. And actually, what you're gonna see here is most of these commands are actually fairly simple. So, what we're gonna start with is a show command. And we're gonna use show databases. Now, you're gonna need to end these lines typically with a semi colon, which is going to be a little bit confusing for you if you haven't really gotten used to using programming languages that use semicolons before. So if you forget to do that and you just simply hit Enter, you're gonna see this little line here. And what this means is, what you can do in the command prompt of MySQL, is you're actually able to create multi-line commands, which is actually kind of useful. And to be able to execute those commands or execute those queries in a way that MySQL knows that you're done entering in your command, is when you enter in a semicolon. So once I do that and hit Enter, you're gonna see it shows the databases that come pre-installed. Now you can also see if I do show databases with a semicolon on one line, you get the same response. So you can see here by default, I have four databases that are installed on my instance of MySQL. These are all internal databases that are used by MySQL, to manage a lot of different things to have information about these schemas that are in your databases. The actual internal MySQL database that tracks a lot of things like users for your systems. So if you had problems logging in with root, that's because the data that was stored in the user table didn't match, what you had going on in your system, and so on, and so forth. So these are really databases that are used for internal purposes. There are very rare occasions where you'll ever wanna even touch any of these. So really, for this course, you're really not gonna need to do anything with them. But, what you will need to know is eventually how you're going to select one of these databases so that you can actually do something with it or inspect what's going on in them. The way that you actually select a database, or use a database, is through the use command. So as an example here, let's say I wanted to take a look at the MSyQL database. I could say use MySQL. And once again, I'm gonna use the semi-colon there. And once I issue that command it's going to read the table information for that particular database, and then it's gonna tell you that the database has changed. So now, everything that I do is gonna be within the context of that MySQL database. So at this point, I can go ahead and take a look and see what tables I have in that database, so I could say show tables. Now when I do that, you're gonna see all the tables that are currently in the MySQL database. And as you can see, there's quite a few. Lots of internal information is held within these tables. And as you can see, all the way down here at the bottom we have users, so that's where all the user information for your database is gonna be stored. So this is really nice to be able to see. But one of the other things that's gonna be nice to be able to see eventually, is exactly what's going on in each of these tables. What do these tables look like? And the way that you kind of look into these tables is with the explain command. So I can say explain user, and when I do that, you're gonna see a lot of information here. You're gonna see the name of the field or the name of the column within that database, what type is there, so you can see I have character type of length 60, character of length 32. Are you able to put nulls into those columns? A lot of times you're gonna see nulls here, which means I'm gonna have to fill those pieces of information in. The key, this is gonna become very important. What is the primary key of the table? We'll talk about that in an upcoming lesson, is there a default value? So in a lot of cases, you'll see there's no default value here, or maybe null is a default value, or zero, or whatever have you, as well as some extra information. So, if you're ever kind of looking through the databases, and you find a table, and you wanna see a little bit more about what's going on with a particular table in there, you can use the explain command. And now we're gonna take a look at this a little bit later on when we start creating our own databases and our own tables in those databases. And it's gonna be nice to be able to see what's going on in there, to just validate that you've created those tables correctly. But just as a nice little precursor to that, you can see how to do that. So at any point here, I could go ahead, and I could do my show databases again. And I could say, all right, well maybe I don't wanna use MySQL anymore, maybe I wanna take a look and say use Sys, that way I can switch context over to Sys. And I can do the same thing, and I can say, show tables. And I can see all the tables in the Sys. And as you can see, there's a lot of things in here. So once again, those built in databases, you're really not gonna have to worry about, but to at least understand how to get in and out, list the databases, use a particular database, show the tables. What does the table look like, show the databases, show the tables, the use command as well as explained is going to be very helpful for you to kind of traverse the landscape and see exactly what's going on.

Back to the top