RHEL7: Perform simple SQL queries against a database.

Note: This is an RHCE 7 exam objective.

Prerequisites

First, you need to install a MariaDB database.
Then, you have to create a simple database schema.

Initial Connection

Now, you need to connect to your database (here called test):

# mysql -u user -p test
Enter password: your password
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]>

You can check the presence of the table, previously created, called addresses:

MariaDB [test]> desc addresses;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | YES  |     | NULL    |       |
| name    | varchar(20)      | YES  |     | NULL    |       |
| address | varchar(40)      | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Data Insertion

You can insert some data into the table called addresses:

MariaDB [test]> insert addresses values(1,"James","address1");
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> insert addresses values(2,"Bill","address2");
Query OK, 1 row affected (0.02 sec)

Data Selection

Now, you can get James‘ address:

MariaDB [test]> select address from addresses where name="James";
+----------+
| address  |
+----------+
| address1 |
+----------+
1 row in set (0.00 sec)

You can also get all the records ordered by name in an ascending order (ASC is the default order and can be omitted):

MariaDB [test]> select * from addresses order by name ASC;
+------+-------+----------+
| id   | name  | address  |
+------+-------+----------+
|    2 | Bill  | address2 |
|    1 | James | address1 |
+------+-------+----------+
2 rows in set (0.00 sec)

Data Update

You can replace the name “Bill” with “John“:

MariaDB [test]> update addresses set name="John" where name="Bill";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from addresses order by name DESC;
+------+-------+----------+
| id   | name  | address  |
+------+-------+----------+
|    2 | John  | address2 |
|    1 | James | address1 |
+------+-------+----------+
2 rows in set (0.00 sec)

Note: DESC specifies a descending order.

Data Removal

You can also delete James‘ record:

MariaDB [test]> delete from addresses where name="James";
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select * from addresses;
+------+------+----------+
| id   | name | address  |
+------+------+----------+
|    2 | John | address2 |
+------+------+----------+
1 row in set (0.00 sec)

This is a very basic tutorial. A lot of other operations are available for a database software like MariaDB. A complete documentation can be found on the MySQL website.