Learn some simple MySQL commands that will make your life as a Linux admin much easier.
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.
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 apt-get command, a yum command, 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 snar.co/foodsql. You can download that file, or use wget 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 wget 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 “food.sql”, you can just change the command to ./foodsql instead of ./food.sql. Both will work.
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 -p tells 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 “food”, and then exit. The CREATE and 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 database using 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 -e command 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.
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:
SHOW TABLES;
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.
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 database. To 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)
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 (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 SELECT-ing 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!