MySQL—Some Handy Know-How
I recently was talking to someone over IRC who was helping me with a PHP app that was giving me trouble. The extremely helpful individual asked me to let him know the value of a certain field in a record on my MySQL server. I embarrassingly admitted that I'd have to install something like PHPMyAdmin or Adminer in order to find that information. He was very gracious and sent me a simple one-liner I could run on the command line to get the information he needed. I was very thankful, but admittedly embarrassed. I figured if I don't know how to get simple information from a MySQL server, there probably are others in the same boat. So, let's learn a little SQL together.
Get a Database
It turns out there are quite a few sample databases to download from the Internet. Unfortunately, they're all far more complicated than I'd like to use for demonstration purposes. So, I created a database. Although you don't have to have my database in order to follow along, it certainly will help if you do. So first, let's create a database and import my data.
The first thing you need to do is install MySQL. Depending on your
distribution, this either will be an
or a search in the GUI software center. I'll leave the installation
to you—feel free to use Google if you're struggling. The main
thing is to remember the root password you set during the installation
process. This isn't the same as the root password for your system; rather
it's the root user in your MySQL server. If you're using a live server,
just create a new user/password with access to create databases. I'm going to
assume you've just installed MySQL, and you know the root user's password.
When you work with MySQL on the command line, you use the "mysql" application. So in order to create the database for this example, type:
mysql -u root -p -e "CREATE DATABASE food"
You should be prompted for a password, which is the password you set during installation for the MySQL root user account. If you get an error about the database already existing, you can choose a new name for your database. Just realize that the name you pick will be what you'll use later when I refer to the "food" database.
Next, you need to get my data into your database. I have an SQL file stored
at http://snar.co/foodsql. You can download that file, or
on the command line to get it. If you use
wget, the resulting filename
might be "foodsql" or "food.sql", depending on
how your version of
works. Either filename will work, just make note of what you have so you
can change the command you're going to use below. To download and import
the data, type:
wget http://snar.co/foodsql mysql -u root -p food < ./food.sql
Remember, if your downloaded file is "foodsql" instead of
you can just change the command to
./food.sql. Both will work.
What Did You Just Do?
The mysql program can work either interactively or as a one-liner like
above. The first command created a database on your MySQL
server named "food", which you'll be using to follow along
with this article. The
-u flag allows you
to connect as a specific user—root in this case. Typing
mysql to ask you for a password. You also could have typed the password
on the command line like this:
mysql -u root -pmypassword -e "CREATE DATABASE food"
However, that bothers me for two reasons. One, the password
is displayed clearly on the screen, which just creeps me out. But also, you probably
noticed there's no space between the
-p and the actual password. That
wasn't a typo; that's how you actually must do it—weird. I usually
just have it prompt me for the password. The last part of the command
tells mysql to execute a command. I'll cover using commands interactively in
a bit, but here, you told it to create a database called
then exit. The
DATABASE don't have to be all caps, but
it's standard practice in the SQL world. If the word is a command
or a special word, it's all uppercase. If it's a piece of data or name,
it's lowercase. Again, it's just a convention, but I'll try to stick to
it. You should too, as it makes reading SQL stuff much easier.
The second section of code did two things. It downloaded my sample
wget, and then it dumped that SQL
data into the "food"
database. The downloaded file is just a text file. You can look at it, and
you'll see a bunch of SQL statements (with capitalized commands). Those
commands were piped in via STDIN and executed much like the
you used to create the initial database. The end result is that you have
a database called "food" on your local MySQL server, and it contains my
tables and data. Let's go check out the data.
Connect to the Database
If you've been following along, you've actually connected to MySQL and the database already, but you created single commands that executed and ended. To enter interactive mode, you simply type:
mysql -u root -p food
This will prompt you for your password and then log you in to the interactive mode of mysql, with the "food" database open for you to explore. You should get a prompt that looks something like this:
Welcome to the MySQL monitor. Commands end with ; or \g. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
To check whether you're logged in to the "food" database, and that the SQL import worked, let's look at the tables in the database. Type the following:
You should see this:
mysql> SHOW TABLES; +----------------+ | Tables_in_food | +----------------+ | fruit | | vegetable | +----------------+ 2 rows in set (0.00 sec)
Note that every command you'll enter must end with a semicolon. If you forget the semicolon, it just will go to the next line and expect you to type more commands. If you do that (I do it about half the time), just type a semicolon alone on the next line, and it will execute just as if you didn't forget the semicolon.
You should see the fruit and vegetable tables in the food database. If you don't, go back through the first steps, as something must have gone wrong. Read any error messages closely.
The next thing you'll do is look at the data in each table. To do that,
you'll use a new command,
SELECT, which in interactive mode just shows
the data you're "selecting" based on whatever criteria you
specify. So, type:
SELECT * FROM fruit;
In mysql, the asterisk is a wild card. So the
SELECT command is showing
everything "FROM" the "fruit" table. You then should see a visual display
of the entire table's worth of data:
mysql> SELECT * FROM fruit; +------------+-------+--------+ | name | size | color | +------------+-------+--------+ | lemon | small | yellow | | grape | small | purple | | apple | small | red | | banana | small | yellow | | watermelon | big | green | +------------+-------+--------+ 5 rows in set (0.01 sec)
Try doing the same thing with the vegetable database. You should see a similar set of data, but with vegetable information instead of fruit.
Filter the Produce!
Usually when you're manipulating a database full of data, you want to work only on a subset of the data. Officially that's called a "query", but don't let the database jargon scare you off. You're just going to use almost-English commands to filter results to meet your needs.
Let's say you want to see a list of only small vegetables. If you type:
SELECT * FROM vegetable WHERE size = "small";
you should see:
mysql> SELECT * FROM vegetable WHERE size = "small"; +--------+-------+--------+ | name | size | color | +--------+-------+--------+ | pea | small | green | | radish | small | red | | bean | small | green | | corn | small | yellow | +--------+-------+--------+ 4 rows in set (0.01 sec)
You'll notice "pumpkin" isn't listed, because its size is "big" instead of "small".
You also can just show the name of the vegetable that matches your query instead of showing all the fields. So if you type:
SELECT name FROM vegetable WHERE size = "big";
you simply should see:
mysql> SELECT name FROM vegetable WHERE size = "big"; +---------+ | name | +---------+ | pumpkin | +---------+ 1 row in set (0.00 sec)
This has the interesting result of showing the name of all the large vegetables without actually showing the size data. It obviously requires you to know a little about how the database is structured (so you knew the "size" information was there), but you can set filters based on data you don't actually display.
Up until this point, you've looked only at existing data. The interactive
mysql program also allows you to modify and add data in the
make a change, you use the
UPDATE command. So if you want to change corn
from a small vegetable to a big vegetable, you'd type:
UPDATE vegetable SET size = "big" WHERE name = "corn";
You should end up with corn that is now big instead of small, and so running the same command you ran earlier:
SELECT name FROM vegetable WHERE size = "big";
should result in something like this:
mysql> SELECT name FROM vegetable WHERE size = "big"; +---------+ | name | +---------+ | pumpkin | | corn | +---------+ 2 rows in set (0.00 sec)
You can do more than modify existing data, however; you also can add new data. It's a little more complicated than updating an existing value, but it's still fairly clear. Let's say you want to add honeydew to your database. To add a row to a table, type:
INSERT INTO fruit (name, color, size) ↪VALUES ('honeydew', 'green', 'big');
And then if you
SELECT everything from the fruit
table, you should see this:
mysql> SELECT * FROM fruit; +------------+-------+--------+ | name | size | color | +------------+-------+--------+ | lemon | small | yellow | | grape | small | purple | | apple | small | red | | banana | small | yellow | | watermelon | big | green | | honeydew | big | green | +------------+-------+--------+ 6 rows in set (0.00 sec)
Just the Tip of the Iceberg!
The mysql command-line interactive program is a very powerful way to access, display and even manipulate data without the need for any GUI at all. The basic commands are well worth learning, so if you're in a situation like I was, you can pull some MySQL data without installing a GUI tool to do it. That said, the GUI tools are great, and they can be used as a way to learn the command-line stuff. For example, Figure 1 shows Adminer (http://www.adminer.org) looking at the database. Searching, filtering, sorting and countless other SQL functions are easily accessible via drop-down menus. The really cool part is that Adminer shows you the exact query it used to get the results. So if you want to use a GUI tool to learn command-line options, Adminer is a great way to do so.
Figure 1. Adminer is an incredibly powerful tool, plus it teaches you what it's doing!
This silly little database of fruits and vegetables is obviously far
more simple than the sorts of databases you'll be troubleshooting for
Web applications. Thankfully, the concepts are exactly the same whether
you're searching through thousands of financial transactions or a handful
of produce items. It's also important to realize that just
records in a live database won't alter any data, so you don't have to
worry about ruining things just by looking. In fact, it's really good
practice to try building complex queries on existing databases just to
see if you can do it correctly. If you get stumped, just fire up Adminer
and see what you did wrong. Good luck, and happy databasing!