MariaDB – Basic MySQL CRUD Operations

In the previous article we saw how to create a new database called ‘online_shop’ and then a table inside this new database called ‘customers’. Now we’re going to show how to create/read/update/delete (crud) content in a table.

Let’s see what the situation is:

[root@mariadb-client ~]# mysql -u root -p'rootpassword' -h mariadb-server.example.com
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.56-MariaDB MariaDB Server

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

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

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customers          |
| mysql              |
| online_shop        |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> USE online_shop
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [online_shop]> SHOW TABLES;
+-----------------------+
| Tables_in_online_shop |
+-----------------------+
| customers             |
+-----------------------+
1 row in set (0.01 sec)

MariaDB [online_shop]> DESCRIBE customers;
+------------------+----------+------+-----+---------+-------+
| Field            | Type     | Null | Key | Default | Extra |
+------------------+----------+------+-----+---------+-------+
| userID           | int(11)  | YES  |     | NULL    |       |
| userFirstName    | char(25) | YES  |     | NULL    |       |
| userLastName     | char(25) | YES  |     | NULL    |       |
| userEmailAddress | char(50) | YES  |     | NULL    |       |
+------------------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MariaDB [online_shop]> SELECT * FROM customers;
Empty set (0.00 sec)

MariaDB [online_shop]>

Now let’s add a few rows, which is done like this:

MariaDB [online_shop]> INSERT INTO customers (userID,userFirstName,userLastName,userEmailAddress) VALUES (1,"Peter","Parker","spiderman.gmail.com");
Query OK, 1 row affected (0.01 sec)

MariaDB [online_shop]> INSERT INTO customers (userID,userFirstName,userLastName,userEmailAddress) VALUES (2,"Tony","Stark","ironman.gmail.com");
Query OK, 1 row affected (0.01 sec)

MariaDB [online_shop]> INSERT INTO customers (userID,userFirstName,userLastName,userEmailAddress) VALUES (3,"Steve","Rogers","captain_america.gmail.com");
Query OK, 1 row affected (0.01 sec)

MariaDB [online_shop]> INSERT INTO customers (userID,userFirstName,userLastName,userEmailAddress) VALUES (4,"Bruce","Banner","the_hull.gmail.com");
Query OK, 1 row affected (0.00 sec)


MariaDB [online_shop]> SELECT * FROM customers;
+--------+---------------+--------------+---------------------------+
| userID | userFirstName | userLastName | userEmailAddress          |
+--------+---------------+--------------+---------------------------+
|      1 | Peter         | Parker       | spiderman.gmail.com       |
|      2 | Tony          | Stark        | ironman.gmail.com         |
|      3 | Steve         | Rogers       | captain_america.gmail.com |
|      4 | Bruce         | Banner       | the_hull.gmail.com        |
+--------+---------------+--------------+---------------------------+
4 rows in set (0.00 sec)

We can filter the output using the ‘WHERE’ clause, e.g.:

MariaDB [online_shop]> SELECT * FROM customers WHERE userLastName='Stark';
+--------+---------------+--------------+-------------------+
| userID | userFirstName | userLastName | userEmailAddress  |
+--------+---------------+--------------+-------------------+
|      2 | Tony          | Stark        | ironman.gmail.com |
+--------+---------------+--------------+-------------------+
1 row in set (0.01 sec)

To delete a row we do:

MariaDB [online_shop]> DELETE FROM customers WHERE userEmailAddress='captain_america.gmail.com';
Query OK, 1 row affected (0.00 sec)

MariaDB [online_shop]> SELECT * FROM customers;
+--------+---------------+--------------+---------------------+
| userID | userFirstName | userLastName | userEmailAddress    |
+--------+---------------+--------------+---------------------+
|      1 | Peter         | Parker       | spiderman.gmail.com |
|      2 | Tony          | Stark        | ironman.gmail.com   |
|      4 | Bruce         | Banner       | the_hull.gmail.com  |
+--------+---------------+--------------+---------------------+
3 rows in set (0.00 sec)

To update a row, e.g. change Bruce Banner’s email address, we do:

MariaDB [online_shop]> UPDATE customers SET userEmailAddress='the_incredible_hull.gmail.com' WHERE userFirstName='Bruce' AND userLastName='Banner'
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [online_shop]> SELECT * FROM customers;
+--------+---------------+--------------+-------------------------------+
| userID | userFirstName | userLastName | userEmailAddress              |
+--------+---------------+--------------+-------------------------------+
|      1 | Peter         | Parker       | spiderman.gmail.com           |
|      2 | Tony          | Stark        | ironman.gmail.com             |
|      4 | Bruce         | Banner       | the_incredible_hull.gmail.com |
+--------+---------------+--------------+-------------------------------+
3 rows in set (0.00 sec)

We combined 2 Where clauses to reduce ambiguity. We could have also used the UserID instead.

Take the RHCSA Quiz

This article is part of our RHCSA Study guide (click on the yellow tab on the far left). By the end of this article you should be able to answer the following questions:

In these question we’ll assume we have a table called ‘customers’ which has 4 columns userID, userFirstName, userLastName, and userEmailAddress


What is the command to add new entry 1,Peter,Parker,spiderman.gmail.com?

INSERT INTO customers (userID,userFirstName,userLastName,userEmailAddress) VALUES (1,”Peter”,”Parker”,”spiderman.gmail.com”);

What is the command to list all entries where userLastName is equal to 'Parker'?

SELECT * FROM customers WHERE userLastName=’Stark’;

What is the command to change the email address to 'peter_parker.gmail.com' for all entries where userFirstname is 'Peter' and userLastName is 'Parker' ?

UPDATE customers SET userEmailAddress=’peter_parker.gmail.com’ WHERE userFirstName=’Peter’ AND userLastName=’Parker’

What is the command to delete all entries with the email address spiderman.gmail.com?

DELETE FROM customers WHERE userEmailAddress=’spiderman.gmail.com’;