The MySQL command line tool allows you to run queries and administer databases from the command line. In previous posts I have looked at the basics of using the MySQL command line tool and executing shell commands. In this post I will look at running queries and the output from these.
Running select queries
After you have logged into a database with the MySQL command line tool (covered in my using the MySQL command line tool post), you can run queries by simply typing them in at the command prompt. The query will not be executed until you either enter ; g or G and then press the <enter> key. This allows you to write a query across several lines and then execute it at the end by entering ; and then <enter>.
For example, if we have a table which stores country codes and names, we can query the data like so:
mysql> select country_code, name from countries order by name limit 0, 5;
The result set would then be displayed, showing something like below. It will be displayed in this horizontal format whether you use ; or g to execute the query. We will look at what G does in the next section below.
+--------------+----------------+ | country_code | name | +--------------+----------------+ | AF | AFGHANISTAN | | AL | ALBANIA | | DZ | ALGERIA | | AS | AMERICAN SAMOA | | AD | ANDORRA | +--------------+----------------+ 5 rows in set (0.00 sec)
Displaying the result set vertically
If your query result contains a lot of columns then the data will wrap if it is too long. This can make it difficult to find the information you are looking for because the output can be quite messy. The MySQL command line tool allows you to show the output vertically which overcomes this issue, using G to execute the query.
Running the same query as above with G:
mysql> select country_code, name from countries order by name limit 0, 5G
would look like this:
*************************** 1. row ***************************
country_code: AF
name: AFGHANISTAN
*************************** 2. row ***************************
country_code: AL
name: ALBANIA
*************************** 3. row ***************************
country_code: DZ
name: ALGERIA
*************************** 4. row ***************************
country_code: AS
name: AMERICAN SAMOA
*************************** 5. row ***************************
country_code: AD
name: ANDORRA
5 rows in set (0.00 sec)
MySQL Command Line Tool History
The MySQL command line tool keeps a history of the SQL queries you have run. On a Linux/Unix machine this is stored in your home directory in the file .mysql_history. When you are in the MySQL command line tool, you can go back through this history by using the up and down arrow keys. The up arrow moves you back through the history and the down arrow forward through the history again.
Clearing the current query
If you navigate through the history but decide you don’t want to run any of the queries, or if you’ve typed in a query that you decide you don’t want to run after all, you don’t need to use the backspace key to clear the query. Enter c and then press <enter> and the query will be cleared without running.
Summary
The MySQL Command Line Tool is a useful way to run queries from the command line. It’s easy to run select queries and display the results in either a horizontal or vertical format, and the queries run are kept in a history file which you can navigate through. If you don’t want to run a particular query after all you can use the c command to clear it. In the next post I will look at displaying database tables and table structure with the MySQL command line tool.