How do I access a MySQL database with the MySQL command line client?

I may have shown this before in other ways, but I wanted to take a moment to show how to use a MySQL database from the MySQL command line client.

Assuming that you are already logged into your MySQL database with the mysql command-line command, the basic command to work with an existing database is the use command, where you say something like this:

mysql> use my_database;

For instance, if you have a database named orders, you would declare that you want to start working with the orders database like this:

mysql> use orders;

A complete example

It seems helpful to new users if I show the full process, from the MySQL login to the part where you declare that you want to use a database, so I'll include that full process here next, with a few added comments along the way:

#------------------------------------------------
# START WITH THE MYSQL LOGIN FROM MY LINUX PROMPT
#------------------------------------------------

$linux_prompt> mysql -u root -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.51b MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


#-------------------------
# DISPLAY ALL MY DATABASES
#-------------------------

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |
| pizza_store        | 
| sam                | 
| spanglish          | 
| test               | 
+--------------------+
6 rows in set (0.08 sec)


#-----------------------------------------------------
# DECLARE THAT I WANT TO "USE" MY PIZZA_STORE DATABASE
#-----------------------------------------------------

mysql> use pizza_store;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


#----------------------------------------------
# LIST ALL THE DATABASE TABLES IN THIS DATABASE
#----------------------------------------------

Database changed
mysql> 
mysql> show tables;
+-----------------------+
| Tables_in_pizza_store |
+-----------------------+
| crust_sizes           | 
| crust_types           | 
| customers             | 
| orders                | 
| pizza_toppings        | 
| pizzas                | 
| toppings              | 
+-----------------------+
7 rows in set (0.00 sec)

MySQL login when you are not root

To log into a MySQL database when you are not the root user, you’ll still use a command similar to the one shown above. Just substitute your username for the "root" name, like this:

mysql -u my_username -p

Once again, the -p option tells MySQL to prompt you to enter the password for this MySQL user.