How To Use Mysql / MariaDB From Console?
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
In order to connect to a MySQL or MariaDB database server we will use
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
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.
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
> 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 command can be used to show tables too;
> show tables;
As we can see there is no table in the current database named
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.
We will delete table completely not just the row. Drop Table is the command with the table name.
> DROP Table Persons;