When you’re all done running queries, just type quit.
Now let’s put this all together. MySQL is a database software that runs on a
server and listens to port 3306. We tell the mysql program that there
is database software running on some computer and that it’s waiting for us
to talk to it on port 3306. In this case, the database is right on our computer,
so we use localhost instead of the IP address of some other machine.
We also give it a username and password that it uses to log into the MySQL
We can actually shorten the command that opens the connection to MySQL:
mysql -u root -p
We’re removed the -h and --port options, because the program uses
localhost and port 3306 by default.
Once we’ve connected to the MySQL server, it contains many databases. We
created one, and we can get a full list using the SHOWDATABASES query:
To actually make queries to our database, we have to use it:
Inside our database, we created one table. We can see all our tables using
the SHOWTABLES query:
If you want to see what columns a table has, try the DESCRIBE query:
Nice! But here’s a secret: all those commands and queries we just reviewed
aren’t really that important. Yea, you need to understand them, but you
won’t be creating databases or even tables that often. 99% of the time, you’ll
be adding, updating, reading or deleting data. So if you want to get dangerous
with MySQL, focus on the INSERT, UPDATE, SELECT and DELETE commands.
So far, I’ve been making you communicate with MySQL directly using its native
language: queries. But there are also some pretty nice GUI’s to help you
see your data, build queries, and even make tables.
The most popular is probably PHPMyAdmin. It’s actually a website, writen
in - surprise! - PHP! It runs on your local computer, and if you installed
XAMPP, you can already access it by going to http://localhost/phpmyadmin:
Oh, and if this doesn’t work, make sure that Apache is running. For XAMPP, you
can do this in its control panel - we turned Apache off in episode 1, just
to prove how we weren’t using it for our site.
PHPMyAdmin is easy, and there’s plenty of docs online for it. Let’s navigate
to our database and table to check out the data. It even helps us filter
the results and show the query that it’s using with MySQL behind the scenes.
While we’re here, let’s add the rest of the columns we need on the pet
table. These will be the same as what we have in the pets.json file, so
we’ll add age, weight, bio and image:
Beyond choosing the data type, each column has some other options. These
are less important, but you can google them if you’re curious.
So now our table is setup and we have an easy way to see and play with our
data. Behind the scenes, queries are being sent to our MySQL server software,
PHPMyAdmin is just taking care of that for us.