How to Query a Table Using MySQL CLI

Let’s walk through the steps to create a new table in a MySQL database using the MySQL client.

1. Invoke mysql shell

Given a HOST and USER, we can log into our MySQL client using the following command.

mysql -h HOST -u USER -p

With this command, we’ll be prompted to enter a password:

Enter password:

2. List all databases

Let’s list all available databases on the MySQL server host.

SHOW DATABASES;

3. Switch to a database

To access a specific database, we’ll use USE.

USE DatabaseName;

4. View current database

Let’s verify that we’ve switched to the desired database.

SELECT DATABASE();

This should print the database we switched to above.

5. List all tables

Let’s list all tables in this database.

SHOW TABLES;

6. Run a query!

Let’s run a simple SELECT query.

SELECT * FROM tableName;

6.1. Pretty print in vertical mode (ego command)

We can pretty print in vertical mode using the ego command (\G) instead of a semicolon ;.

SELECT * FROM tableName \G

The results will be listed in vertical mode, each column value printed on a separate line.

6.2. Scroll through results with pager less

We can also scroll through our results with arrow keys using pager less with the -S flag.

pager less -S;
SELECT * FROM tableName;

Once we’re done with the pager, we can go back to the ordinary stdout output with nopager.

nopager