How to Create and Query MariaDB database

In this post, we will learn to examine, search, create, and change database information using Structured Query Language (SQL) and MariaDB statements.

Creating a Database

The installation of the MariaDB-client group provides a program called mysql. With this program, it is possible to connect to a local or remote MariaDB database server.

mariaDB create database
  1. Client to connect to the MariaDB database server.
  2. Option to specify the username for this connection.
  3. Username for this connection.
  4. Option to specify the host name for this connection. If not specified, the default value is localhost.
  5. Host name for this connection.
  6. Option to prompt for password.

A database in MariaDB is implemented as a directory. The default installation has four databases. To list the databases, run the command:

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
|  Database          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

To create a new database, run the command:

MariaDB [(none)]> CREATE DATABASE inventory;

After the creation of the new database, the next step is to connect to this database so that it can be populated with tables and data:

MariaDB [(none)]> USE inventory;

It is possible to switch between databases at any time with this command.

MariaDB (like all relational database systems) can have multiple tables per database. List the tables with the SHOW TABLES; command:

MariaDB [(none)]> USE mysql;
MariaDB [(none)]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

To list attributes (or the column names) from a table, use:

MariaDB [(mysql)]> DESCRIBE servers;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
42 rows in set (0.00 sec)

This output completely describes the data in the servers table in the mysql database. The Port attribute is stored as an integer, using a maximum of 4 digits, and defaults to 0.

The Key value is null for most of these attributes. Only the Server_name has a value: PRI. This sets the attribute as the primary key for the table. Primary keys are unique identifiers for the data in the table. No two entries can have the same primary key, and only one attribute may be set as the primary key. Primary keys are often used to link tables together and are an important concept when designing complex databases. There are also secondary keys, and composite keys (where multiple attributes together form the unique key). A deeper discussion of keys is beyond the scope of this course.

The Extra value is used to show any additional features of the attribute. This value can be complex, but a common one is auto_increment, which states that the value of this column will be incremented by 1 for each new entry made into the table. It is a common value for primary keys to have, as seen in later examples.

Using SQL (Structured Query Language)

Structured Query Language (SQL) is a special programming language designed for managing data held in relational databases. Some common SQL commands include insert, update, delete, and select.

To insert data into a table, the first step is to figure out the attributes of the table.

MariaDB [(inventory)]> DESCRIBE product;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(100) | NO   |     | NULL    |                |
| price           | double       | NO   |     | NULL    |                |
| stock           | int(11)      | NO   |     | NULL    |                |
| id_category     | int(11)      | NO   |     | NULL    |                |
| id_manufacturer | int(11)      | NO   |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

In this example, all attributes are required. To insert a new product, the command will be rather long and complicated:

MariaDB [(inventory)]> INSERT INTO product (name,price,stock,id_category,id_manufacturer) VALUES ('SDSSDP-128G-G25 2.5',82.04,30,3,1) ;
Query OK, 1 row affected (0.00 sec)

Delete a record with the delete statement:

MariaDB [(inventory)]> DELETE FROM product WHERE id = 1 ;
Query OK, 1 row affected (0.01 sec)

To update a record, use an update statement:

MariaDB [(inventory)]> UPDATE product SET price=89.90, stock=60 WHERE id = 5 ;
Query OK, 1 row affected (0.01 sec)

If the where clause is not specified, all records will be updated.

To read data records from the database, use the select statement:

MariaDB [(inventory)]> SELECT name,price,stock FROM product ;
+---------------------+--------+-------+
| name                | price  | stock |
+---------------------+--------+-------+
| ThinkServer TS140   | 539.88 | 20    |
| RT-AC68U            | 219.99 | 10    |
| X110 64GB           | 73.84  | 100   |
| SDSSDP-128G-G25 2.5 | 82.04  | 30    |
+---------------------+--------+-------+
4 rows in set (0.00 sec)

To select all attributes, use the wild card *:

MariaDB [(inventory)]> SELECT * FROM product;
+----+---------------------+--------+-------+-------------+-----------------+
| id | name                | price  | stock | id_category | id_manufacturer |
+----+---------------------+--------+-------+-------------+-----------------+
| 2 | ThinkServer TS140    | 539.88 | 20    | 2           | 4               |
| 3 | RT-AC68U             | 219.99 | 10    | 1           | 3               |
| 4 | X110 64GB            | 73.84  | 100   | 3           | 1               |
| 5 | SDSSDP-128G-G25 2.5  | 82.04  | 30    | 3           | 1               |
+----+---------------------+--------+-------+-------------+-----------------+
4 rows in set (0.00 sec)

Filter results with the where clause:

MariaDB [(inventory)]> SELECT * FROM product WHERE price > 100;
+----+-------------------+--------+-------+-------------+-----------------+
| id | name              | price  | stock | id_category | id_manufacturer |
+----+-------------------+--------+-------+-------------+-----------------+
| 2  | ThinkServer TS140 | 539.88 | 20    | 2           | 4               |
| 3  | RT-AC68U          | 219.99 | 10    | 1           | 3               |
+----+-------------------+--------+-------+-------------+-----------------+
2 rows in set (0.00 sec)

Common Operators for where Clauses:

OPERATOR DESCRIPTION
= Equal
<> Not equal. Note: In some versions of SQL, this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column