Find your way around MySQL console (command line)

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.

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.

Asking for 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

Once you are in…
To view the list of all databases:
mysql> show databases;
Example output:
+——————–+
| Database |
+——————–+
| information_schema |
+——————–+
1 rows in set (0.01 sec)

Create a database
To create a database:
mysql> create database <em>database-name</em>;
Example:
mysql> create database test;<br></br>Query OK, 1 row affected (0.01 sec)

Delete a database
To delete a database: // DO NOT DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING
mysql> drop database <em>database-name</em>;

Switching between databases
To switch to a different database:
mysql> use test;
Example output:
Database changed

How about tables?
To view the list of existing tables withing the current database:
mysql> show tables;
Example output:
Empty set (0.00 sec)

No table? Create one
To create a table:
Example:
mysql> CREATE TABLE users(<br></br> -> idint(11) NOT NULL,<br></br> -> first_namevarchar(255) NOT NULL,<br></br> -> last_name varchar(255) NOT NULL,<br></br> -> PRIMARY KEY (id)<br></br> -> );
Output:
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| users |
+—————-+
1 row in set (0.00 sec)

View table schema
To view the table schema:
mysql> describe <em>table-name</em>;
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)

More details please!
To show how a specific table was created:
mysql> show create table table-name;

Ok, that’s enough. Where is the exit?
To exit from mysql console just type exit:
mysql> exit

How about running more complex queries?
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 <em>database-name</em> file-name
And if if you are in MySql console and wants to run commands from a text file:
mysql> source <em>file-name</em>

I have data to populate
Loading data from sql file into a database:
mysql -u <em>user-name</em> -p <em>database-name</em> file-name

Take a dump
Dump database to a SQL file;
mysql> mysqldump -u <em>user-name</em> -p <em>database-name</em> > <em>file-name</em>

• Database