= Introduction to MariaDB and MySQL
:Author: Seth Kenlon
:Email: <seth@opensource.com>
:Revision: 1.0
When you're writing an application or configuring one for a server, you eventually come to a point when you need to store persistent information.
Sometimes, a configuration file, such as an INI or LINK_TO_MY_YAML_ARTICLE[YAML file].
Other times, a custom file format designed in XML or JSON or similar is better.
But sometimes you need something that can validate input, search through information quickly, make connections between related data, and generally handle your user's work adeptly.
That's what a database is designed to do, and MariaDB (a fork of MySQL by some of the original developers of MySQL itself) is one of the best open source, entreprise-grade database available.
It's common to interact with a database through programming langauges.
For this reason, there are SQL libraries for Java, Python, Lua, PHP, Ruby, C++, and many many others.
However, before using these libraries, it helps to have an understanding of what's actually happening with the database engine, and why your choice of a database is significant.
This article introduces you to MariaDB and the `mysql` command so you can get familiar with the basics of how a database handles data.
If you don't have MariaDB installed, read and complete my LINK TO MARIADB-INSTALL ARTICLE[How to install MariaDB on Linux] article.
If you're not on Linux, use the install instructions provided on https://mariadb.org/download[mariadb.org/download].
== Interacting with MariaDB
You can interact with it using the `mysql` command.
First, verify that your server is up and running using the `ping` subcommand, entering your MariaDB password when prompted:
----[source,bash]
$ mysqladmin -u root -p ping
Enter password:
mysqld is alive
----
To make exploring SQL easy, open an interactive MariaDB session:
----[source,bash]
$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.
Commands end with ; or \g.
[...]
Type 'help;' or '\h' for help.
Type '\c' to clear the current input statement.
MariaDB [(none)]>
----
This places you in a MariaDB subshell, and so your prompt is now a MariaDB prompt.
Your usual Bash commands don't work here.
You must use MariaDB commands.
To see a list of MariaDB commands, type `help` (or just `?`).
These are only administrative commands for your MariaDB shell, though, so they're useful for customizing your shell, but they aren't a part of the SQL language.
== Learn the basics of SQL
The Structured Query Language (SQL) is named after what it provides: a method to inquire about the contents of a database in a predictable and consistent syntax in order to receive useful results.
SQL reads a lot like an ordinary English sentence, if not a little robotic.
For instance, if you've signed into a database server and you need to understand what you have to work with, type `SHOW DATABASES;` and press Enter for the results.
SQL commands are terminated with a semi-colon.
If you forget the semi-colon, MariaDB assumes you want to continue your query on the next line, at which point you can either continue your query or just terminate it with a semi-colon.
----[source,sql]
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
----
This reveals that you have 4 databases present: information_schema, mysql, performance_schema, and test.
To issue queries to a database, you must select a database for MariaDB to use.
This is done with the MariaDB command `use`.
Once you choose a database, your MariaDB prompt changes to reflect the active database.
----[source,sql]
MariaDB [(none)]> use test;
MariaDB [(test)]>
----
=== Show tables of a database
Databases contain _tables_, which can be visualized in the same way a spreadsheet is visualized: a series of rows (called _records_ in a database) and columns.
The intersection of a row and a column is called a _field_.
To see available tables contained in a database (you can think of them as tabs in a multi-sheet spreadsheet), use the SQL keyword `SHOW` again:
----[source,sql]
MariaDB [(test)]> SHOW TABLES;
empty set
----
The `test` database doesn't have much to look at, so use the `use` commnad to switch over to the `mysql` database.
----[source,sql]
MariaDB [(test)]> use mysql;
MariaDB [(mysql)]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
[...]
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.000 sec)
----
There are obviously a lot more tables in this database!
The `mysql` database is the system management database for this MariaDB instance.
It contains important data, including an entire user structure to manage database privleges.
It's an important database, and you don't always have to interact with it directly, but it's not uncommon to manipulate in SQL scripts.
It's also useful to understand the `mysql` database when learning MariaDB, because it can belp demonstrate some basic SQL commands.
=== Examine a table
The last table listed in this instance's `mysql` database is titled `user`.
This table contains data about users permitted to access the database.
Right now, there's only a root user, but you can add other users with varying privileges to control whether each user can view, update, or create data.
To get an idea of all the attributes a user in MariaDB can have, you can view column headers in a table:
----[source,sql]
> SHOW COLUMNS IN user;
MariaDB [mysql]> SHOW columns IN user;
+-------------+---------------+------+-----+----------+
| Field | Type | Null | Key | Default |
+-------------+---------------+------+-----+----------+
| Host | char(60) | NO | PRI | |
| User | char(80) | NO | PRI | |
| Password | char(41) | NO | | |
| Select_priv | enum('N','Y') | NO | | N |
| Insert_priv | enum('N','Y') | NO | | N |
| Update_priv | enum('N','Y') | NO | | N |
| Delete_priv | enum('N','Y') | NO | | N |
| Create_priv | enum('N','Y') | NO | | N |
| Drop_priv | enum('N','Y') | NO | | N |
[...]
47 rows in set (0.001 sec)
----
=== Create a new user for MariaD
Whether you need help from a fellow human to administer a database, or whether you're setting up a database for a computer to use (as in the case of a Wordpress, Drupal, or Joomla install), it's common to need an extra user account within MariaDB.
You can create a MariaDB user either by adding to the `user` table in the `mysql` database, or you can use the SQL keyword `CREATE` to prompt MariaDB to do it for you.
The latter features some helper functions so you don't have to generate all information manually.
----[source,sql]
> CREATE USER 'tux'@'localhost' IDENTIFIED BY 'really_secure_password';
----
=== View table fields in MariaDB
You can view fields and values in a table of your database with the `SELECT` keyword.
In this example, you've created a user called `tux`, so you select the columns in the `user` table:
----[source,sql]
> SELECT user,host FROM user;
+------+------------+
| user | host |
+------+------------+
| root | localhost |
[...]
| tux | localhost |
+------+------------+
7 rows in set (0.000 sec)
----
=== Granting privileges to a user in MariaDB
Looking at the column listing of the `user` table, you can explore a user's status further.
For instance, the new user `tux` doesn't have permission to do anything with the database.
Using the `WHERE` statement, you can view just the record for `tux`:
----[source,sql]
> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux | N | N | N |
+------+-------------+-------------+-------------+
----
Use the `GRANT` command to modify user permissions:
----[source,sql]
> GRANT SELECT on *.* TO 'tux'@'localhost';
> FLUSH PRIVILEGES;
----
Verify your change:
----[source,sql]
> SELECT user,select_priv,insert_priv,update_priv FROM user WHERE user='tux';
+------+-------------+-------------+-------------+
| user | select_priv | insert_priv | update_priv |
+------+-------------+-------------+-------------+
| tux | Y | N | N |
+------+-------------+-------------+-------------+
----
User `tux` now has privileges to select records from all tables.
== Joining data in MariaDB
So far, you've only interacted with the default databases.
For most people, it's rare to interact much with the default databases outside of user management.
More often than not, you'll create a database and populate it with tables full of custom data.
=== Create a MariaDB database
You may already be able to guess how to create a new database in MariaDB.
It's a lot like creating a new user:
----[source,sql]
> CREATE DATABASE example;
Query OK, 1 row affected (0.000 sec)
> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| example |
[...]
----
Make this new database your active one with the `use` command:
----[source,sql]
> use example;
----
=== Creating a table in a MariaDB database
Creating a table is more complex than creating a database because you must define column headings.
MariaDB provides many convenience functions for you to use when creating columns, including data type definitions, automatic incrementing options, constraints to avoid empty values, automated timestamps, and more.
Here's a simple table to describe a set of users:
----[source,sql]
> CREATE table IF NOT EXISTS member (
-> id INT auto_increment PRIMARY KEY,
-> name varchar(128) NOT NULL,
-> startdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.030 sec)
----
This table provides a unique identifier to each row, using an auto-increment function.
It contains a field for a user's name, which cannot be empty (or _null_), and generates a timestamp when the record is created.
Populate this table with some sample data using the `INSERT` SQL keyword:
----[source,sql]
> INSERT INTO member (name) VALUES ('Alice');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Bob');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('Carol');
Query OK, 1 row affected (0.011 sec)
> INSERT INTO member (name) VALUES ('David');
Query OK, 1 row affected (0.011 sec)
----
Verify the data in the table:
----[source,sql]
> SELECT * FROM member;
+----+-------+---------------------+
| id | name | startdate |
+----+-------+---------------------+
| 1 | Alice | 2020-10-03 15:25:06 |
| 2 | Bob | 2020-10-03 15:26:43 |
| 3 | Carol | 2020-10-03 15:26:46 |
| 4 | David | 2020-10-03 15:26:51 |
+----+-------+---------------------+
4 rows in set (0.000 sec)
----
=== Adding multiple rows at once
Now create a second table:
----[source,sql]
> CREATE table IF NOT EXISTS linux (
-> id INT auto_increment PRIMARY KEY,
-> distro varchar(128) NOT NULL,
Query OK, 0 rows affected (0.030 sec)
----
Populate it with some sample data, this time using a little `VALUES` shortcut so you can add multiple rows in just one command.
The `VALUES` keyword expects a list in parentheses, but can take multiple lists separated by commas:
----[source,sql]
> INSERT INTO linux (distro)
-> VALUES ('Slackware'), ('RHEL'),('Fedora'),('Debian');
Query OK, 4 rows affected (0.011 sec)
Records: 4 Duplicates: 0 Warnings: 0
> SELECT * FROM linux;
+----+-----------+
| id | distro |
+----+-----------+
| 1 | Slackware |
| 2 | RHEL |
| 3 | Fedora |
| 4 | Debian |
+----+-----------+
----
== Altering a table
You now have two tables, but as yet there's no relationship between the two.
They each contain independent data, but feasibly you might have a need to associate a member of the first table to a specific item listed in the second.
To do that, you can create a new column for the first table that corresponds to something in the second.
Because both tables were designed with unique identifiers (the auto-incrementing `id` field), the easiest way to connect them is to use the `id` field of one as a selector for the other.
Create a new column in the first table to represent a value in the second table:
----[source,sql]
> ALTER TABLE member ADD COLUMN (os INT);
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0
> DESCRIBE member;
DESCRIBE member;
+-----------+--------------+------+-----+---------+------+
| Field | Type | Null | Key | Default | Extra|
+-----------+--------------+------+-----+---------+------+
| id | int(11) | NO | PRI | NULL | auto_|
| name | varchar(128) | NO | | NULL | |
| startdate | timestamp | NO | | cur[...]| |
| os | int(11) | YES | | NULL | |
+-----------+--------------+------+-----+---------+------+
----
Using the unique IDs of the `linux` table, assign a distribution to each member.
Because the records already exist, you use the `UPDATE` SQL keyword rather than `INSERT`.
Specifically, you want to select one row and then update the value of one column.
Syntactically, this is expressed a little in reverse, with the update happening first and the selection matching last:
----[source,sql]
> UPDATE member SET os=1 WHERE name='Alice';
Query OK, 1 row affected (0.007 sec)
Rows matched: 1 Changed: 1 Warnings: 0
----
Repeat this process for the other names in the `member` table, just to populate it with data.
For variety, assign 3 different distributions across the 4 rows (doubling up on one).
=== Joining tables
Now that these two tables relate to one another, you can use SQL to display the associated data.
There are many kinds of joins in databases, but you can try them all once you know the basics.
Here's a basic join to correlate the values found in the `os` field of the `member` table to the `id` field of the `linux` table:
----[source,sql]
SELECT * FROM member JOIN linux ON member.os=linux.id;
+----+-------+---------------------+------+----+-----------+
| id | name | startdate | os | id | distro |
+----+-------+---------------------+------+----+-----------+
| 1 | Alice | 2020-10-03 15:25:06 | 1 | 1 | Slackware |
| 2 | Bob | 2020-10-03 15:26:43 | 3 | 3 | Fedora |
| 4 | David | 2020-10-03 15:26:51 | 3 | 3 | Fedora |
| 3 | Carol | 2020-10-03 15:26:46 | 4 | 4 | Debian |
+----+-------+---------------------+------+----+-----------+
4 rows in set (0.000 sec)
----
The `os` and `id` fields form the join.
You can imagine, in a graphical application, that the `os` field might be set by a drop-down menu, the values for which are drawn from the contents of the `distro` field of the `linux` table.
By using separate tables for unique but related sets of data, you ensure consistency and validity of data, and thanks to SQL, you have the ability to associate them dynamically later.
== Learning more
MariaDB is an enterprise-grade database.
It's designed and proven as a robust, powerful, and fast database engine.
Learning to use it interactively is a great first step toward managing it for web applications or using it through programming language libraries.
As a quick reference, download our LINK TO CHEATSHEET[MariaDB and MySQL cheat sheet].