Essential MySQL Console Commands Guide

Introduction

Using graphic tools to interact with database is very easy but in many cases you might find yourself in a situation that you don’t have way other than a simple CLI for example because you are connecting to the server via SSH. That’s why it is useful to know the basic commands to at least find your way around and get the job done.

Basic Connection and Help

Connecting to Database Server

To specify which host or database we are connecting to and what username and password we are using:

shell> mysql -u user-name -p database-name

Did you notice that we didn’t provide the password in the command after -p?
That’s because for security reasons it is better to not provide password in the command itself; Just in case if someone could access the history log in the server and find out the password.
If you don’t provide the password in the command you will be prompted to enter your password in the next line without printing on the screen.

Getting Help

No one can memorise every command; It is a good idea to know how to ask for help. For a list of commands type help in mysql console:

mysql> help

Database Operations

Viewing Databases

To view the list of all databases:

mysql> show databases;

Example output:

+------------------+
| Database         |
+------------------+
| information_schema|
+------------------+
1 rows in set (0.01 sec)

Creating a Database

To create a database:

mysql> create database database-name;

Example:

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

Deleting a Database

To delete a database: // DO NOT DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING

mysql> drop database database-name;

Switching Databases

To switch to a different database:

mysql> use test;

Example output:

Database changed

Table Operations

Viewing Tables

To view the list of existing tables within the current database:

mysql> show tables;

Example output:

Empty set (0.00 sec)

Creating Tables

To create a table:
Example:

mysql> CREATE TABLE `users` (
    ->   `id` int(11) NOT NULL,
    ->   `first_name` varchar(255) NOT NULL,
    ->   `last_name` varchar(255) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );

Output:

Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_test   |
+------------------+
| users            |
+------------------+
1 row in set (0.00 sec)

Viewing Table Structure

To view the table schema:

mysql> describe table-name;

Example:

mysql> describe users;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI | NULL    |       |
| first_name | varchar(255) | NO   |     | NULL    |       |
| last_name  | varchar(255) | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Viewing Table Creation Details

To show how a specific table was created:

mysql> show create table table-name;

Advanced Operations

Running SQL Files

Sometimes it is easier to save large and complex queries in a file and run it instead of logging into console every time:

shell> mysql database-name file-name

And if you are in MySQL console and want to run commands from a text file:

mysql> source file-name

Importing Data

Loading data from sql file into a database:

mysql -u user-name -p database-name file-name

Exporting Data

Dump database to a SQL file:

mysql> mysqldump -u user-name -p database-name > file-name

Exiting MySQL

To exit from mysql console just type exit:

mysql> exit