SQL tutorial: Learn SQL on MariaDB

Follow this step-by-step guide to install MariaDB, load data, connect to a database, and execute SQL queries including table joins

MariaDB is popular fork of MySQL, founded by Monty Widenius, the main author of the original version of MySQL. As Serdar Yegulalp explains in his MariaDB tutorial: Get started with MariaDB, MariaDB has “many powerful features included by default rather than only available as add-ons, and many performance, usability, and security improvements that aren’t guaranteed to show up in MySQL.”

In this article, I’ll show you the basic elements of how to query a MariaDB database with SQL. After installing MariaDB (on the Mac in my case), we’ll walk through the steps of connecting to a database, loading data, and various queries including table joins. We’ll finish with a brief discussion of MariaDB extensions and some recommendations for further learning. 

Install MariaDB

Serdar lays out the options for installing MariaDB in his article, and provides some links to the MariaDB downloads and installation instructions. I’ll suggest also reading MariaDB’s Introduction to Relational Databases and Getting Started pages.

I installed MariaDB on an iMac, using Homebrew. Before the installation shown below, I ran brew update so that I would be sure to get the latest version of MariaDB.

Martins-iMac:~ mheller$ brew install mariadb
==> Installing dependencies for mariadb: openssl
==> Installing mariadb dependency: openssl
==> Downloading https://homebrew.bintray.com/bottles/openssl-1.0.2p.high_sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring openssl-1.0.2p.high_sierra.bottle.tar.gz
==> Caveats
A CA file has been bootstrapped using certificates from the SystemRoots
keychain. To add additional certificates (e.g. the certificates added in
the System keychain), place .pem files in
  /usr/local/etc/openssl/certs

and run
  /usr/local/opt/openssl/bin/c_rehash

openssl is keg-only, which means it was not symlinked into /usr/local,
because Apple has deprecated use of OpenSSL in favor of its own TLS and crypto libraries.

If you need to have openssl first in your PATH run:
  echo 'export PATH="/usr/local/opt/openssl/bin:$PATH"' >> ~/.bash_profile

For compilers to find openssl you may need to set:
  export LDFLAGS="-L/usr/local/opt/openssl/lib"
  export CPPFLAGS="-I/usr/local/opt/openssl/include"

==> Summary
🍺  /usr/local/Cellar/openssl/1.0.2p: 1,793 files, 12.3MB
==> Installing mariadb
==> Downloading https://homebrew.bintray.com/bottles/mariadb-10.3.9.high_sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mariadb-10.3.9.high_sierra.bottle.tar.gz
==> /usr/local/Cellar/mariadb/10.3.9/bin/mysql_install_db --verbose --user=mheller --basedir=/usr/local/Cellar/mariadb/10
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To connect:
    mysql -uroot

To have launchd start mariadb now and restart at login:
  brew services start mariadb

Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
🍺  /usr/local/Cellar/mariadb/10.3.9: 651 files, 174.4MB

The MariaDB installation includes the MySQL Utilities and the MySQL Workbench graphical client. Since I have an active JetBrains subscription, I also installed DataGrip, a multi-database client tool. If I were doing this exercise on Windows, I might well use the visual SQL query builder and SQL command line in Alpha Anywhere.

Connect to your MariaDB database

Before connecting to the database, first start the server. I chose to run the MariaDB server as an application rather than as a background service.

Martins-iMac:~ mheller$ mysql.server start
Starting MariaDB
.180929 19:46:59 mysqld_safe Logging to '/usr/local/var/mysql/Martins-iMac.local.err'.
180929 19:46:59 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
. SUCCESS!

You can connect using the mysql command line utility as shown below:

Martins-iMac:~ mheller$ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.9-MariaDB Homebrew

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

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

MariaDB [(none)]>

The mysql utility can show you help about itself and about the server.

MariaDB [(none)]> \h

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

MariaDB [(none)]> \q
Bye

When you’re done with the MariaDB server, you can shut it down and reclaim its resources.

Martins-iMac:~ mheller$ mysql.server stop
Shutting down MariaDB
.. SUCCESS!

Load data in MariaDB

Database design is an art, not a science, and takes considerable experience to get right. Even expert database designers often have to modify their designs over time to match the application data usage pattern, for example by adding indexes for common queries, or by adding foreign key constraints to enforce referential integrity.

Rather than taking weeks (or months) to learn database design, let’s load a stock database that’s already in MariaDB/MySQL format. There are many options, including the sakila DVD store database that ships inside MySQL Workbench as a sample (see the screenshot below), but I want you to load a MySQL-compatible version of Northwind.

mariadb sakila IDG

The sakila DVD store database ships with MySQL Workbench. 

Browse to the Northwind extended Google Code archive and download the file Northwind.MySQL5.sql. Northwind was originally a Microsoft sample, but Valon Hoti has adapted it for MariaDB. I like Northwind mostly because I used to use it to teach SQL to developers, and became familiar with the data. The original database diagram is shown below.

mariadb northwind entity relationship diagram IDG

The entity-relationship diagram for the Northwind database. 

Entity-relationship diagrams like this one come out of tools; this image looks a lot like it was generated by SQL Server Management Studio. Essentially, the rectangles are tables, the columns are shown within the rectangles, and the primary and foreign keys are marked with key icons. The connections between tables are the foreign key constraints.

You can load the database into MariaDB using the mysql command line, the MySQL Workbench, or any other MySQL client you have. After restarting the server, I launched MySQL Workbench and tried to connect to MariaDB.

mariadb connection warning IDG

When I tested the connection, I got the warning above, as I expected, since the MySQL Workbench team doesn’t test against MariaDB. I continued anyway and got a confirmation that the connection worked:

mariadb connection ok IDG

I opened the connection and opened the SQL file I had just downloaded:

mariadb workbench open create script IDG

To successfully run the database definition script, I had to turn off the option to stop on error. Then executing the script gave me a good result:

mariadb create northwind IDG

Assuming that you’ve also gotten this far, we can now try querying the database using select statements. I’m going to switch away from MySQL Workbench to the mysql command line because I discovered some bugs in Workbench.

Simple SQL queries in MariaDB

To begin with, let’s try looking at a single table. From a command line run mysql -uroot and use the Northwind database as shown below.

Note that it’s safe to use the root user without a password for now, as this is a local-only database and we don’t need to set up security. If you ever change the MariaDB configuration to allow remote access, please at the very least create a strong password for root.

MariaDB [(none)]> use northwind;
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

What are the regions defined in the database? The select query should look like the following. I got the table name from the diagram above.

MariaDB [northwind]> select * from region;
+----------+----------------------------------------------------+
| RegionID | RegionDescription                                  |
+----------+----------------------------------------------------+
|        1 | Eastern                                            |
|        2 | Westerns                                           |
|        3 | Northern                                           |
|        4 | Southern                                           |
+----------+----------------------------------------------------+
4 rows in set (0.000 sec)

That was easy. The asterisk notation says to return all fields in the table. Suppose we just wanted to see the region names in alphabetical order. That requires specifying the desired field name, the name of which we learned from the previous query, and adding an order by clause. Ascending order is the default.

MariaDB [northwind]> select RegionDescription from region order by RegionDescription;
+----------------------------------------------------+
| RegionDescription                                  |
+----------------------------------------------------+
| Eastern                                            |
| Northern                                           |
| Southern                                           |
| Westerns                                           |
+----------------------------------------------------+
4 rows in set (0.037 sec)

Now let’s look at the product categories and their IDs:

MariaDB [northwind]> select CategoryID, CategoryName from categories;
+------------+----------------+
| CategoryID | CategoryName   |
+------------+----------------+
|          1 | Beverages      |
|          2 | Condiments     |
|          3 | Confections    |
|          4 | Dairy Products |
|          5 | Grains/Cereals |
|          6 | Meat/Poultry   |
|          7 | Produce        |
|          8 | Seafood        |
+------------+----------------+
8 rows in set (0.000 sec)

You might wonder why I didn’t show all the fields. As it happens, the last field in categories is an image in an old Windows format, and if you display it in mysql on a Mac you’ll see a bunch of random-looking characters.

Now let’s look at just the beverage products, which we just saw have category_id=1. That requires a where clause.

1 2 Page 1
Page 1 of 2