Preliminaries
Welcome to our SQL tutorial! We’re going to get our hands dirty and write some basic SQL queries. At the end of the tutorial, you’ll know how to create/delete databases in MariaDB, create/delete/change database tables, insert/delete/modify records in a table, and do some user management so your other developers can securely access your database with the appropriate permissions. We’ll assume that you have a working installation of MariaDB on whatever distribution you’re running and that you’ve configured a non-root sudo account. If you haven’t done so, check out our MariaDB section in the Community.
What is SQL?
Structured Query Language (SQL) is a programming language that was designed to manage data stored in a relational database management system (RDBMS). It provides a standardized and structured way to perform create, read, update, and delete operations to database tables. Although each RDBMS vendor might introduce their own features or offer different data types for their record, SQL is similar enough that once you’ve learned the basics its simple to get started with another.
Some Basics
Typically, one accesses databases through the command-line client distributed by the database vendor, or through a GUI. We’ll show you how to use the command line to interact with the MariaDB database client.
Within the client, the first logical grouping of data is called a ‘database’. Within each database are a number of tables. Within each of these tables there are records. How one chooses to partition the data is ultimately a design choice.
A database has a number of tables that all have a different structure defined on them. Each table has a definition called a ‘schema’ that outlines what fields each record in a table can define. For example, if you had a ‘friends’ table, you might define a schema that has the fields ‘name’, ‘age’, ‘gender’, ‘job’. Each record in your ‘friends’ table would then be allowed to set those fields and would correspond to one ‘friend’. It might be helpful to visualize a table as a 2-D table on a piece of paper with rows and columns. The ‘records’ in the table define a row, and the ‘fields’ in the table define a column. Before you ask, you’re able to set some fields to NULL if some record in your data doesn’t explicitly have a value for one of the fields.
Within a database, there can be many tables with various relationships between them. Continuing with our example from above, you could define an ‘activity’ table with ‘name’, ‘difficulty’, and ‘description’ fields. Then, you could add a field in your ‘friends’ database named ‘favourite_activity’ and map that field to one record in the ‘activities’ table. This clearly allows you with a lot of flexibility and power! You can create numerous tables within a database and set complex relationships between them. Although we won’t cover relations in this tutorial, it gives you an insight into the true power of SQL.
Authenticating and Creating Users
In order to perform actions and interact with the database server, we’ll need to authenticate with the MariaDB command line tool. Depending on whether or not you set a root password during the installation of MariaDB or while running mysql_secure_installation (highly recommended), you’ll need to enter a password. Run the following command if you set a root password,
mysql -u root -p
and you’ll be prompted for the password. If you didn’t set a password, just run
mysql -u root
and you should see the MariaDB prompt:
MariaDB [(none)]>
We’re going to begin by creating a non-root user to login with for security purposes. The reason for this is the same as why we create a non-root user for our unix operating system — it helps prevent potentially catastrophic changes to your database server. Run the following command in the MariaDB command prompt:
CREATE USER 'myUser'@'localhost' IDENTIFIED BY 'myPassword';
You should replace ‘myUser’ and ‘myPassword’ appropriately. This will create a new user that can authenticate with the MariaDB client using the password you provided. However, this user has no access privileges yet. All it can do is authenticate with the client and poke around. Run the following command to give the new user all access privileges
GRANT ALL PRIVILEGES ON *.* to 'myUser'@'localhost' WITH GRANT OPTION;
This gives read, write, and delete privileges to all databases and tables to the user you just created. Additionally, the ‘WITH GRANT OPTION’ expression allows your new user to also manage permissions for those databases and tables. To allow your changes to take effect you must run the follow command following every administrative change
FLUSH PRIVILEGES;
Now you’re ready to log in with your new user . type ‘\q’ into the MariaDB prompt to quit the shell and login with your new user by running the following
mysql -u {myuser} -p
replacing the brackets by the name of the user you created earlier. You’ll be prompted for the password you defined for that account. If everything worked well, you should see the MariaDB prompt!
Creating and Destroying
Matter is never created or destroyed. However, we can perform create, read, update, and delete operations on our SQL server with the new user we just created. With the user you just created, enter the following into your MariaDB shell
SHOW DATABASES;
You’ll be shown a list of different databases you have read access to. If you’re starting from a fresh installation of MariaDB you’ll likely only have a few databases such as ‘mysql’ and ‘information_schema’ visible. These are administrative databases that the SQL server manages for configuration. You can actually manage user accounts within the ‘mysql’ database, although its not recommended.
Let’s create our very own database that we can play around with as much as we want.
CREATE DATABASE db_name;
Now if you run 'SHOW DATABASES;' you’ll be shown your database. Since we haven’t inserted anything into our database yet, we can delete it without any negative repercussions. The SQL command for deleting tables and databases is DROP. Run the following command
DROP DATABASE db_name;
If you list the databases using the show command, the database you created earlier should be gone.
Create a new database again and we’ll create tables and perform some queries. To start using a database, simply run
USE db_name;
replacing db_name appropriately. Your MariaDB prompt should change to reflect the database you’re currently working with. To check which tables exist in a database (which should be none at the moment), run
SHOW tables;
As you can see, the basic structure of the SQL language is very simple to get started. The complexity arises later when you must perform highly-complex queries on datasets with intricate relationships.
Your basic knowledge of a ‘schema’ must now be used to create a table in the database. For the rest of this tutorial I will be using a database called ‘life’ which has tables ‘friends’ and ‘activities’. I’m first going to create the ‘friends’ table that will initially consist of 4 fields
MariaDB [users]> CREATE TABLE friends (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), age INT, job VARCHAR(15), birthday DATE); Query OK, 0 rows affected (0.01 sec)
What I’ve done with this command is defined the new ‘friends’ table and structured the data that is allowed to be stored in a record by setting the schema. In this example, my schema has fields ‘id’, ‘name’, ‘age’, ‘job’, and ‘birthday’. Each one of the fields has a data type associated with it that allows us slightly control what gets inserted into the database. You can look up the precise definition of what an INT, VARCHAR (its a string), and DATE are in the MariaDB documentation.
If you ever forget what your table schema looks like, you can run the ‘DESCRIBE table_name;’ command. You’ll get a pretty-printed outline of your different fields and their properties.
MariaDB [life]> DESCRIBE friends; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | | age | int(11) | YES | | NULL | | | job | varchar(15) | YES | | NULL | | | birthday | date | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
Each field looks fairly simple except for the ‘id’ field. When created the table, we specified that the ‘id’ field should never be null and should be auto incremented. This is helpful when assigning id’s to users since this requires that every user has an id and that no user will have the same id as another (since the ids are always being incremented). In addition, we set the ‘id’ to be the primary key of every record in this table. This means that when we’re defining relations between tables, we can use the primary key to reference a reference in another table. We’ll come back to this concept later.
Let’s start adding some records into our table. Run the following command in the prompt
INSERT INTO friends (name, age, job, birthday) VALUES ("alex", 21, "student", "1994-06-03");
Let’s take a look at what our table looks like so far. The command for reading all records and fields is SELECT * FROM table_name;.
MariaDB [life]> SELECT * FROM friends; +----+------+------+---------+------------+ | id | name | age | job | birthday | +----+------+------+---------+------------+ | 1 | alex | 21 | student | 1994-06-03 | +----+------+------+---------+------------+ 1 row in set (0.00 sec)
As you can see, all the fields are populated. Even though I didn’t specify what id number I wanted for the record I just added, MariaDB added it for me because I specified that the field should be non-null. Play around with this a little bit and add a few more records in before we start deleting a few users or querying users depending on their age or birthday.
Here’s what my ‘friends’ table looks like after a few more friends are added
MariaDB [users]> select * from friends; +----+-------------+------+--------------+------------+ | id | name | age | job | birthday | +----+-------------+------+--------------+------------+ | 7 | alex | 20 | love | 1994-08-18 | | 8 | sean | 20 | jazz | 1994-09-09 | | 9 | ted | 25 | business | 1994-09-09 | | 10 | jean-claude | 25 | cheese maker | 1994-12-09 | +----+-------------+------+--------------+------------+ 4 rows in set (0.00 sec)
Perhaps I wanted to see all users over the age of 21. I could construct the following SQL query
MariaDB [users]> SELECT * FROM friends WHERE age >= 21; +----+-------------+------+--------------+------------+ | id | name | age | job | birthday | +----+-------------+------+--------------+------------+ | 9 | ted | 25 | business | 1994-09-09 | | 10 | jean-claude | 25 | cheese maker | 1994-12-09 | +----+-------------+------+--------------+------------+ 2 rows in set (0.00 sec)
The syntax is simple enough. Now instead of getting all the fields of the records that match my query, let’s try and just see the name, age, and job.
MariaDB [users]> SELECT name, age, job FROM friends WHERE age >= 21; +-------------+------+--------------+ | name | age | job | +-------------+------+--------------+ | ted | 25 | business | | jean-claude | 25 | cheese maker | +-------------+------+--------------+ 2 rows in set (0.00 sec)
Its pretty easy to infer a pattern from the two queries that a basic SQL query looks like
SELECT field1, field2, field3, ... FROM table_name WHERE predicate_clause
Suppose I wanted to order the users in descending order in terms of their birthday. I would run the following command
MariaDB [users]> SELECT * FROM friends ORDER BY birthday DESC; +----+-------------+------+--------------+------------+ | id | name | age | job | birthday | +----+-------------+------+--------------+------------+ | 10 | jean-claude | 25 | cheese maker | 1994-12-09 | | 8 | sean | 20 | jazz | 1994-09-09 | | 9 | ted | 25 | business | 1994-09-09 | | 7 | alex | 20 | love | 1994-08-18 | +----+-------------+------+--------------+------------+ 4 rows in set (0.00 sec)
SQL orders dates lexicographically so newer dates are ‘greater’ than older dates. If you wanted to sort them the other way around, simply pass the ‘ORDER BY birthday’ keyword the ‘ASC’ parameter.
Perhaps one of your friends has had an epiphany and changed careers. Maybe ‘ted’ thought he should pursue more worldly pursuits other than business — so he decides to take up social work. We can update his ‘job’ field by running the following
MariaDB [users]> UPDATE friends SET job = "social worker" WHERE name = "ted"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
If I take a look at my table again,
MariaDB [users]> SELECT * FROM friends; +----+-------------+------+---------------+------------+ | id | name | age | job | birthday | +----+-------------+------+---------------+------------+ | 7 | alex | 20 | love | 1994-08-18 | | 8 | sean | 20 | jazz | 1994-09-09 | | 9 | ted | 25 | social worker | 1994-09-09 | | 10 | jean-claude | 25 | cheese maker | 1994-12-09 | +----+-------------+------+---------------+------------+ 4 rows in set (0.00 sec)
we’ll see that ted’s job description has been updated.
We live in the glorious age of instant messaging and forms of communication other than carrier pidgeon. We should get with the times an add an ’email’ field to our ‘friends’ table. We can do so by running the following command:
ALTER TABLE friends ADD email VARCHAR(30);
It will add the field ’email’ at the end of all the other fields and set its data type as a string limited to 30 characters.
It’s stupid to store both the age and the birthday in our table. We lose no information by deleting the ‘age’ field. Let’s go ahead and do that.
MariaDB [users]> ALTER TABLE friends DROP age; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 MariaDB [users]> SELECT * FROM friends; +----+-------------+---------------+------------+ | id | name | job | birthday | +----+-------------+---------------+------------+ | 7 | alex | love | 1994-08-18 | | 8 | sean | jazz | 1994-09-09 | | 9 | ted | social worker | 1994-09-09 | | 10 | jean-claude | cheese maker | 1994-12-09 | +----+-------------+---------------+------------+ 4 rows in set (0.00 sec)
Creating Relations
Let’s create another table ‘activities’ which we’ll use later to assign a ‘favourite_activity’ field to.
MariaDB [users]> CREATE TABLE activities (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), difficulty VARCHAR(10), description VARCHAR(100)); Query OK, 0 rows affected (0.01 sec)
I’ve gone ahead and added a few records that we’ll use to make relationships.
MariaDB [users]> select * from activities; +----+------------+------------+--------------------------------------------------------------------------------------+ | id | name | difficulty | description | +----+------------+------------+--------------------------------------------------------------------------------------+ | 1 | soccer | easy | The best sport in the world. Played with 1 ball, 2 nets, and 22 players | | 2 | basketball | medium | Played with a ball and two hoops. Need 10 players to play a full match. Must be tall | | 3 | baseball | hard | deceptively hard sport. personally not my favourite | +----+------------+------------+--------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
We specify relationships through a construct known as a foreign key. Essentially, we map the primary key of the object we want to associate with a ‘friend’ to the foreign key of the ‘friend’. First we have to create a column in our ‘friends’ table that will store the id of the person’s favourite activity
ALTER TABLE friends add aid INT;
I’ve created a column in the ‘friends’ table called ‘aid’ which will store the activity id of the person’s favourite activity. Now I need to map a relationship between that column and the ‘id’ field of the ‘activities’ table
MariaDB [users]> ALTER TABLE friends
-> ADD CONSTRAINT fk_favactivity
-> FOREIGN KEY (aid)
-> REFERENCES activities(id);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
With this command, I’ve added a constraint (which is essentially just a name for a foreign key relationship) called fk_favactivity which makes the ‘aid’ column a foreign key that references the ‘id’ field of ‘activities’. We’re almost done — all we need to do is set someone’s aid to match the id of an activity
MariaDB [users]> UPDATE friends
-> SET aid=2
-> WHERE friends.name="ted";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [users]> SELECT * from friends;
+----+-------------+---------------+------------+------+
| id | name | job | birthday | aid |
+----+-------------+---------------+------------+------+
| 7 | alex | love | 1994-08-18 | NULL |
| 8 | sean | jazz | 1994-09-09 | NULL |
| 9 | ted | social worker | 1994-09-09 | 2 |
| 10 | jean-claude | cheese maker | 1994-12-09 | NULL |
+----+-------------+---------------+------------+------+
4 rows in set (0.00 sec)
I’ve given ‘ted’ an ‘aid’ value of 2, which matches the ‘basketball’ record in the ‘activities’ table. As a final example, let’s try and find which users actually have specified that they have a favourite activity. We will be using a construct called an ‘inner join’. Run the following command
MariaDB [users]> SELECT friends.id, friends.name, friends.job, activities.name, activities.difficulty, activities.description FROM friends INNER JOIN activities ON friends.aid=activities.id; +----+------+---------------+------------+------------+--------------------------------------------------------------------------------------+ | id | name | job | name | difficulty | description | +----+------+---------------+------------+------------+--------------------------------------------------------------------------------------+ | 9 | ted | social worker | basketball | medium | Played with a ball and two hoops. Need 10 players to play a full match. Must be tall | +----+------+---------------+------------+------------+--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
An inner join essentially combines the columns of both tables and allows you to specify how you would like to filter which records are outputted using the ‘ON’ keyword. In this example, I’ve done an inner join on our two tables and filtered the records in which the friends.aid is equal to an activities.id.
The full scope of table joins in SQL is much beyond the scope of this tutorial. For more information, check out this link.
Conclusion
We’ve only touched the surface of SQL — you’ll quickly find that as your database structure grows, the length and complexity of your queries will only increase. As a result, you’ll be able to extract meaningful information from your highly interrelated data and be able to serve the data in whichever way is useful to your application. You can check out the official MariaDB documentation for more detailed information about different keywords and more information about complex queries.