insert, update, delete, create mariadb databases records

As we see installation and configuration of mariadb server which is the new replacement for MySql Server. After installation we have to create database, Create users, grant permissions to users, Insert, update, delete, create mariadb databases records. Create tables and insert, update and delete data.

How to Connect to Mariadb Server from CLI

[root@TechTutorials ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

To connect mariadb server, we have to use mysql command only. Mariadb will use same features as like MySql.

How to check existing list of databases in mariadb

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| contacts |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)

To type commands in mariaDB always we have to use ; (semicolon) after every command to close the command. If we not use ;(semicolon) in command, command will not work.

MariaDB [(none)]> create database emploees;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| contacts |
| emploees |
| employees |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)

above command is used to create database

Check and Create Tables in Database

connect <DATABASE NAME>; to use existing database. We can also use use <DATABASE NAME>; .

MariaDB [(none)]> connect emploees;
Connection id: 3
Current database: emploees

MariaDB [emploees]> use emploees;
Database changed

create table with specified column names and list the tables. int (integer values) only number 0-9 are allowed. varchar (alpha and number allowed) a-z, 0-9.

MariaDB [emploees]> show tables;
Empty set (0.00 sec)

MariaDB [emploees]> create table employee (id int(10), name varchar(50), empid varchar(20), number int(10), mail_id varchar(10));
Query OK, 0 rows affected (0.00 sec)

MariaDB [emploees]> show tables;
+--------------------+
| Tables_in_emploees |
+--------------------+
| employee |
+--------------------+
1 row in set (0.00 sec)

To list the columns of the table we have to use below command, describe <TABLE NAME>; will list the column names

MariaDB [emploees]> describe employee;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| empid | varchar(20) | YES | | NULL | |
| number | int(10) | YES | | NULL | |
| mail_id | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

How to Insert data into the table

inserting the data into the tables using below command, Insert the values id=1, name=Ravi, empid=1234, number=9700056884, mail_id=admin@arkit.co.in

MariaDB [emploees]> insert into employee values("1", "Ravi", "1234", 9700056884, "admin@arkit.co.in");
Query OK, 1 row affected, 2 warnings (0.00 sec)

MariaDB [emploees]> select * from employee;
+------+------+-------+------------+------------+
| id | name | empid | number | mail_id |
+------+------+-------+------------+------------+
| 1 | Ravi | 1234 | 2147483647 | admin@arki |
+------+------+-------+------------+------------+
1 row in set (0.00 sec)

How to Update existing values using update sql statement

Now example is update the employee id from 1234 to 9780, Here we take an base as id column because if we have empid value as 1234 for any other employee that value will also change if you run update statement without taking base value, to avoid changing other records we set base as id column.

MariaDB [emploees]> update employee set empid = 9780 where id='1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [emploees]> select * from employee;
+------+------+-------+------------+------------+
| id | name | empid | number | mail_id |
+------+------+-------+------------+------------+
| 1 | Ravi | 9780 | 2147483647 | admin@arki |
+------+------+-------+------------+------------+
1 row in set (0.00 sec)

Delete record from table

To delete one record from table we use below sql statement

MariaDB [emploees]> select * from employee;
+------+-------+-------+------------+------------+
| id | name | empid | number | mail_id |
+------+-------+-------+------------+------------+
| 1 | Ravi | 9780 | 2147483647 | admin@arki |
| 2 | Kumar | 1434 | 2147483647 | admin1@ark |
+------+-------+-------+------------+------------+
2 rows in set (0.00 sec)

MariaDB [emploees]> delete from employee where id='1';
Query OK, 1 row affected (0.00 sec)

MariaDB [emploees]> select * from employee;
+------+-------+-------+------------+------------+
| id | name | empid | number | mail_id |
+------+-------+-------+------------+------------+
| 2 | Kumar | 1434 | 2147483647 | admin1@ark |
+------+-------+-------+------------+------------+
1 row in set (0.00 sec)

insert, update, delete, create mariadb databases records

How to Create user  and grant permissions to database

create user name as “ravi” and grant permissions to “employee”

MariaDB [emploees]> create user ravi@'%' Identified by 'password';
Query OK, 0 rows affected (0.01 sec)

above command is used to create a user in mariadb username=ravi

MariaDB [emploees]> grant select,update,delete,insert on employee.* to ravi@’%’;
Query OK, 0 rows affected (0.00 sec)

Granting the permissions (select, update, delete, insert) to user ravi on table employee.

MariaDB [emploees]> SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'ravi');
+------------------------------------------------------+
| EXISTS(SELECT 1 FROM mysql.user WHERE user = 'ravi') |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

Above command is used to check whether  user is exists or not

That’s it about insert, update, delete, create mariadb databases records post insert, update, delete, create mariadb databases records

Related Articles

10 reasons to migrate databases from MySql to Mariadb

Installation and configuration of MariaDB

insert, update, delete, create mariadb databases records