How To Perform Simple SQL Queries Against a Database

This quick guide will show you how to perform very simple SQL queries against a MariaDB/MySQL database as per the RHCE exam objective and is not designed to be an in depth SQL tutorial.

Here we will cover using basic SQL queries such as show, create, select, insert, update, delete, describe, and drop which will allow us to define a basic database schema.

First you will need to install MariaDB or MySQL before proceeding, in this example we are working with MariaDB however the SQL queries will be the same in either.

Note: It is recommended that you perform testing on a test database or data set rather than performing any examples here on a production database. It is also recommended that you create a backup of all databases first.

Simple SQL Queries

To work with MariaDB we’ll be using the structured query language (SQL).

First access the MariaDB/MySQL command line as below, you will be prompted for the password that you have configured in the installing MariaDB guide.

[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.44-MariaDB MariaDB Server

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

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

MariaDB [(none)]>

Show Databases

With the ‘show databases’ query we can view all available databases, by default you will have these databases after installing MariaDB.

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

Create Databases

Create a database using the ‘create database’ query followed by the name of your database. In this example the database that we are creating is named ‘users’.

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

Once the database has been successfully created, we can confirm with the ‘show databases’ query to see it listed.

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

Use the Database

Now that the database has been created, to actually perform queries on it we need to first use the database.

MariaDB [(none)]> use users;
Database changed

MariaDB [users]> 

Notice that this has changed our prompt from “none” to “users”, the name of the database which we have just selected. This shows us the current database that we are working with and are currently using, queries that we run will be performed against this database.

Create a Table

An empty database isn’t particularly useful, we’ll now create some example data starting with a table.

MariaDB [users]>  create table name (firstname VARCHAR(50) NOT NULL, lastname VARCHAR(50) NOT NULL, middlename VARCHAR(50));
Query OK, 0 rows affected (0.05 sec)

This creates a table called ‘name’ with 3 fields named ‘firstname’, ‘lastname’, and ‘middlename’. The ‘firstname’ and ‘lastname’ fields are set to NOT NULL meaning that there must be a value, while ‘middlename’ may be blank as this field will be optional. All fields are set to VARCHAR(50) allowing us to enter up to 50 characters in each field.

We can view this table with the ‘show tables’ command.

MariaDB [users]> show tables;
+-----------------+
| Tables_in_users |
+-----------------+
| name            |
+-----------------+
1 row in set (0.00 sec)

Describe the Table

While the previous show tables command shows us that there is a table in the users database called name, that isn’t very informative. We can see the table structure with the describe command as shown below.

MariaDB [users]> describe name;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| firstname  | varchar(50) | NO   |     | NULL    |       |
| lastname   | varchar(50) | NO   |     | NULL    |       |
| middlename | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The structure shows the fields are as we created them previously. The describe command is useful for getting an idea of a databases structure and viewing the schema in place.

Inserting into the Database

Now that we have a blank table we can insert some actual data with the ‘insert into’ query.

MariaDB [users]> insert into name (firstname, lastname) values ("bob", "user");
Query OK, 1 row affected (0.00 sec)

MariaDB [users]> select * from name;
+-----------+----------+------------+
| firstname | lastname | middlename |
+-----------+----------+------------+
| bob       | user     | NULL       |
+-----------+----------+------------+
1 row in set (0.00 sec)

In this example we have inserted a user named ‘bob user’. The data can be retrieved using the select command, in the above example we are selecting everything as specified by * from the name table, which is currently just one row.

Updating Data

Once data has been entered into the database it can be modified with the update query.

MariaDB [users]> update name set lastname="test" where lastname="user";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [users]> select * from name;
+-----------+----------+------------+
| firstname | lastname | middlename |
+-----------+----------+------------+
| bob       | test     | NULL       |
+-----------+----------+------------+
1 row in set (0.00 sec)

Here we have updated the lastname value of bob from user to test.

Deleting Data

Data from the database can also be deleted with the delete query. In this example we delete the bob row that we have just created by specifying that we want to delete from the name table for all instances where firstname is set to bob.

MariaDB [users]> delete from name where firstname="bob";
Query OK, 1 row affected (0.01 sec)

MariaDB [users]> select * from name;
Empty set (0.00 sec)

After deleting the row and running a select on the name table we can now see that it’s empty, however the table itself still exists.

We can also delete the table with the ‘drop table’ command as shown below, which will delete any data stored within the table and the table itself.

MariaDB [users]> drop table name;
Query OK, 0 rows affected (0.00 sec)

MariaDB [users]> show tables;
Empty set (0.01 sec)

Dropping a Database

Although we have deleted our only row in the name table and the table itself, the users database still exists. The users database can be deleted with the ‘drop database’ query which will delete the database and all data within including any other tables.

MariaDB [users]> drop database users;
Query OK, 1 row affected (0.05 sec)

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

Summary

SQL queries are simple yet allow us a high level of control over a database schema. Here we have demonstrated basic queries such as showing, creating and using a database, showing and creating tables, describing the structure of a table, inserting data, updating it and deleting it from a table.


This post is part of our Red Hat Certified Engineer (RHCE) exam study guide series. For more RHCE related posts and information check out our full RHCE study guide.

  1. So if I ask you to give a MariaDB user privileges so that it can query a database, what GRANT statement would you use?

    • Assuming the user already exists, something like:

      grant select on database.table to [email protected];
      flush privileges;

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>