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