Mysql is one of the most popular databases in IT world. It is open source and have rich features were paid databases provides. MariaDB is fork of the MySQL and very similar to it. We have already looked how to login and do simple steps in this post

Getting Help

In order to connect to a MySQL or MariaDB database server we will use mysql command. mysql command provides a lot of different options and features. We can list help information about mysql command with the -I option like below.

$ mysql -I 

As we can see hep information can be also listed with -? or --help options.

Connect to Local MySQL/MariaDB Database Server

Connecting to the local MySQL server is very easy. We will just use sudo command with the user root. We will provide root user with the -u option like below. If we do not provide the sudo we will get ERROR 1698 (28000):Access denied for user 'root'@'localhost' which is permission related error.

$ sudo mysql -u root

Connect to Remote MySQL/MariaDB Database Server

We can also connect to the remote MySQL server with the mysql command too. We will provide the remote system IP address or host name with the -h option. If we are providing host name we make sure that DNS works properly.

$ mysql -h 192.168.1.20 -u root

Show MySQL/MariaDB Databases

There is default databases like information_schema,mysql,… and user added databases. We will list currently existing databases with the show databases command. We will also add ; to the each command in order to set command end.

> show databases;

As we can see from screenshot there is 6 databases.

Create Database

If database we want to use is not exist we can create a new database with the create database command by providing database name. In this example we will create a database named poftut.

> create database poftut;

Select Database To Use Query

Before issuing queries we need to select the database we want to query. If not our query will not run on any database. We will use use command by providing database we want to query. In this example we will use poftut as database.

> use poftut;

As we can see that after successful database selection we will get Database changed message.

Show Tables

show command can be used to show tables too;

> show tables;

As we can see there is no table in the current database named poftut

Create Table

We need to create a table we will provide table name and field names with their types. We will create a table named Persons with fields PersoneID type of integer, …

> CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

As we can see from CREATE TABLE command result that Query OK which means we have successfully completed table creation.

Deleting/Dropping Table

We will delete table completely not just the row. Drop Table is the command with the table name.

> DROP Table Persons;

How To Use Mysql / MariaDB From Console? Infografic