List of Functions and Practical Uses In MariaDB

MariaDB comes bundled with a lot of built-in functions that can be used to manipulate data within a column. If you are new to mariadb, you can read the following guide, doing this would help you understand the select statement even more:

1. Introduction To MariaDB
2. [MariaDB] Guide To Creating Databases and Tables
3. Altering MariaDB Tables
4. Indexes In MariaDB
5. Guide To Understanding INSERT Statement (MariaDB)
6. Guide To Selecting Data In MariaDB
7. Guide To Updating and Deleting Data In MariaDB
8. Joining and Subquerying Data (MariaDB)

There are mainly three categories of functions:

• String Functions: They are used for manipulating a string or to query information about a string, e.g formatting and converting text, finding, and extracting text from columns, replacing strings with another string, and so on.
• Date and Time Functions - They are used for formatting date and time values, they can also be used to extract specific values from a given date or time.
• Numeric Functions - Used for mathematical or statistical calculations on data.

In this guide, we would look into various functions in mariadb and their uses.

String Functions

MariaDB provides many built-in functions that are tailored to manipulating or extracting strings, but before we dive in, what's a string?

A string is a sequence of characters, words, or other data (e.g dollar sign, etc).

CONCAT() - Concatenating Strings

A popular and very useful function for pasting or concatenating together the contents of data fields with text is the CONCAT() function.

Suppose we have the following table:

+-----------------+-------------------------+
| artiste         | title                   |
+-----------------+-------------------------+
| Avicii          | Silhouette              |
| Beautiful Nubia | Higher Steps            |
| Beautiful Nubia | Tables Turn             |
| Fela            | Observation Is No Crime |
| Avicii          | Levels                  |
| Fela            | Colonial Mentality      |
| Asa             | Jailer                  |
+-----------------+-------------------------+

If for example, I wanna concatenate the artist and the title field like so: Avicii Silhouette

The following SQL statement would put them together:

SELECT CONCAT(artiste, ' ', title) AS SONGS
FROM cmusic;

| SONGS                        |
+------------------------------+
| Avicii Silhouette            |
| Beautiful Nubia Higher Steps |
| Beautiful Nubia Tables Turn  |
| Fela Observation Is No Crime |
| Avicii Levels                |
| Fela Colonial Mentality      |
| Asa Jailer                   |
+------------------------------+

This doesn't look fancy, or perhaps it doesn't work well with concatenating artiste and title name, although, it might work superbly if you want to join a user last_name and first_name.

For our purpose, we can use the CONCAT_WS(),  It will put together columns with a separator between each, Something Like this: so: Avicii - Silhouette

You can actually achieve it with only the CONCAT() function, but you'll have to add space around the separator:

SELECT CONCAT(artiste, ' - ', title) AS SONGS
FROM cmusic;

With the CONCAT_WS() function, you also need to use spaces:

SELECT CONCAT_WS(' - ', artiste, title) AS SONGS
FROM cmusic;

+--------------------------------+
| SONGS                          |
+--------------------------------+
| Avicii - Silhouette            |
| Beautiful Nubia - Higher Steps |
| Beautiful Nubia - Tables Turn  |
| Fela - Observation Is No Crime |
| Avicii - Levels                |
| Fela - Colonial Mentality      |
| Asa - Jailer                   |
+--------------------------------+

The difference between CONCAT() and CONCAT_WS() is that for CONCAT_WS(), you only need to include the separator once, and it would handle neatly separate the columns, e.g:

SELECT CONCAT_WS('|', artiste, title, music_id) AS 'SONGS'
FROM cmusic;

+--------------------------------+
| SONGS                          |
+--------------------------------+
| Avicii|Silhouette|1            |
| Beautiful Nubia|Higher Steps|2 |
| Beautiful Nubia|Tables Turn|3  |
| Fela|Observation Is No Crime|4 |
| Avicii|Levels|5                |
| Fela|Colonial Mentality|6      |
| Asa|Jailer|7                   |
+--------------------------------+

With CONCAT(), you'll do something like the followings:

SELECT CONCAT(artiste, '|', title, '|', music_id) AS 'SONGS'
FROM cmusic;

That's a lot of hassle.

Suppose we have a program that will import data, but it requires the fields to be separated by vertical bars. we could use a SELECT statement like so:

mysql -p --skip-column-names -e	\
"SELECT CONCAT_WS('|', artiste, title, music_id)
FROM marn_melodic.cmusic;" > songlist.txt

This example uses mysql with several options:

• The --skip-column-names option tells MySQL not to display the column headings, we want just the data separated by vertical bars
• The -e option says that what follows within quotes is to be executed.
• FROM marn_melodic.cmusic - marn_melodic is the name of the database from which we want to work on, and cmusic is the name of the table, you must specify the database name
• After the closing double quotes, we use > to redirect the results to a text file.

Here is the result:

devsrealm@blog:~$ cat songlist.txt
Avicii|Silhouette|1
Beautiful Nubia|Higher Steps|2
Beautiful Nubia|Tables Turn|3
Fela|Observation Is No Crime|4
Avicii|Levels|5
Fela|Colonial Mentality|6
Asa|Jailer|7

There is a potential problem with the SQL statement we used. If
a column has a NULL value, nothing will be exported and no bar will be put in the file to indicate an empty field.

We are exporting three columns of data, if somehow the 2nd column has a NULL value, it might cause an issue down the line in whatever places you intend to use the data, it would even be worse if you are exporting thousands of them.

To mitigate this, you can wrap each column name in an IFNULL() function. Then we can give a value to display if the column is NULL e.g unknown or a blank space, you can do that the following way:

mysql -p --skip-column-names -e	\
"SELECT CONCAT_WS('|', IFNULL(artiste, 'unknown'), 
                       IFNULL(title, 'uknown'),
                       IFNULL(mood, 'unknown')
                  )
FROM marn_melodic.cmusic;" > songlist.txt

If somehow, one of your columns have a NULL data, you'll get something as follows:

devsrealm@blog:~$ cat songlist.txt
Avicii|Silhouette|Dance
Beautiful Nubia|Higher Steps|unknown
Beautiful Nubia|Tables Turn|unknown
Fela|Observation Is No Crime|Dance
Avicii|Levels|Dance
Fela|Colonial Mentality|Dance
Asa|Jailer|unknown

Cumbersome to repeat IFNULL for every column, but better for spotting errors.

CONCAT() + FORMAT()

If we want to format a long number with commas every three digits and a period for the decimal point (e.g., $100,000.00), we can use the function FORMAT() function.

Suppose I have the following Table:

+----+----------+
| id | price    |
+----+----------+
|  1 | 10000624 |
|  2 | 17756756 |
|  3 | 28888877 |
|  4 | 39788686 |
+----+----------+

To format the price, I'll do something like so:

SELECT id, CONCAT('$', FORMAT(price, 2)) AS 'Prices'
FROM table1;

+----+----------------+
| id | Prices         |
+----+----------------+
|  1 | $10,000,624.00 |
|  2 | $17,756,756.00 |
|  3 | $28,888,877.00 |
|  4 | $39,788,686.00 |
+----+----------------+

In this statement, the CONCAT() will place a dollar sign in front of the numbers found in the price column, which will be formatted with commas by FORMAT(). The 2 within the FORMAT() stipulates two decimal places.

UCASE() & LCASE() - Upper Case  and Lower Case Functions

You'll mostly want to convert the text from a column to either all upper-case letters or all lower-case letters. For these situations, there are LOWER() and UPPER(), which can also be spelled LCASE() and UCASE(), respectively.

In the example that follows, the output of the first column is converted to upper-case and the second to lower-case:

SELECT UCASE(artiste) AS 'Artiste',
LCASE(title) AS 'Title'
FROM cmusic;

+-----------------+-------------------------+
| Artiste         | Title                   |
+-----------------+-------------------------+
| AVICII          | silhouette              |
| BEAUTIFUL NUBIA | higher steps            |
| BEAUTIFUL NUBIA | tables turn             |
| FELA            | observation is no crime |
| AVICII          | levels                  |
| FELA            | colonial mentality      |
| ASA             | jailer                  |
+-----------------+-------------------------+

QUOTE() Function

The QUOTE() function takes a string and returns it enclosed in single quotes. It also makes it input-safe by marking certain characters that could cause trouble in SQL statements or other programming languages.

The character it treats are single quotes, backslashes, null (zero) bytes, and Ctrl-Z characters. The QUOTE() function precedes each of these with a backslash so that they won’t be interpreted in some way you are not intending.

Suppose I have the following table:

+-------------+---------------+
| producer_id | producer_name |
+-------------+---------------+
|           1 | Avicii        |
|           2 | Cobhams       |
|           3 | Eni Obanke    |
|           4 | Felas' Band   |
+-------------+---------------+

and I intend to pass the values into another programming language, to do that, I'll have to escape the single quote in the producer_name column (i.e Felas') so as not to cause some weird errors, to do that, we can make use of the QUOTE() function:

SELECT 
    producer_id, 
    QUOTE(producer_name) AS 'Producer Name'
FROM cmusic_producers;

+-------------+----------------+
| producer_id | Producer Name  |
+-------------+----------------+
|           1 | 'Avicii'       |
|           2 | 'Cobhams'      |
|           3 | 'Eni Obanke'   |
|           4 | 'Felas\' Band' |
+-------------+----------------+

As you can see, the strings returned in the producer_name column are enclosed in single quotes, and any single quotes within the strings are escaped with a backslash. This can prevent errors if the value is passed to another program.

Trimming and Padding Strings

You can't always assume what your users would enter, for example, If a column is a CHAR data-type, a fixed-width column, then it may be necessary to trim any leading or trailing spaces from displays.

There are a few functions for trimming any leading or trailing spaces from the values of a column. One is the LTRIM() function, which eliminates any leading spaces to the left e.g

"    Book"becomes "Book"

The other is RTRIM, which removes any leading columns with spaces on the right e.g

"Book   "becomes "Book"

If you want the best of both worlds, you can use the TRIM() function, which can trim both left and right spaces.

Suppose, we have the following table:

+----+------------+---------+
| id | rec        | rec2    |
+----+------------+---------+
|  1 |    Book    | Paper   |
|  2 | Pen        |   Mouse |
|  3 |     Laptop | iPhone  |
|  4 | USB        |   Bag   |
+----+------------+---------+

We can clean it up this way:

SELECT 
    id, 
    QUOTE(LTRIM(rec)), 
    QUOTE(RTRIM(rec2))
FROM table2;

+----+-------------------+--------------------+
| id | QUOTE(LTRIM(rec)) | QUOTE(RTRIM(rec2)) |
+----+-------------------+--------------------+
|  1 | 'Book'            | 'Paper'            |
|  2 | 'Pen'             | '  Mouse'          |
|  3 | 'Laptop'          | 'iPhone'           |
|  4 | 'USB'             | '  Bag'            |
+----+-------------------+--------------------+

I am using the QUOTE function to see if there is any spaces, and as you can see, the RTRIM can't be used to remove a leading spaces, that is the function of the LTRIM, if you hate stress like me, just use the TRIM() function to get the best of both world:

SELECT 
    id, 
    QUOTE(TRIM(rec)), 
    QUOTE(TRIM(rec2))
FROM table2;

+----+------------------+-------------------+
| id | QUOTE(TRIM(rec)) | QUOTE(TRIM(rec2)) |
+----+------------------+-------------------+
|  1 | 'Book'           | 'Paper'           |
|  2 | 'Pen'            | 'Mouse'           |
|  3 | 'Laptop'         | 'iPhone'          |
|  4 | 'USB'            | 'Bag'             |
+----+------------------+-------------------+

Perfecto.

Here is another example, suppose we have the following table:

+----+---------+-------+----------+-----------+
| id | col1    | col2  | col3     | col4      |
+----+---------+-------+----------+-----------+
|  1 | ...Book | Paper | _Pascal_ |   James   |
|  2 | ...Book | Paper | _James_  |   Fred    |
|  3 | ...Book | Paper | _Conell_ |   Roswell |
+----+---------+-------+----------+-----------+

Here is an SQL statement that cleans up all the column:

SELECT 
    TRIM(LEADING '.' FROM col1),
    TRIM(TRAILING FROM col2),
    TRIM(BOTH '_' FROM col3),
    TRIM(col4)
FROM table3;

+-----------------------------+--------------------------+--------------------------+------------+
| TRIM(LEADING '.' FROM col1) | TRIM(TRAILING FROM col2) | TRIM(BOTH '_' FROM col3) | TRIM(col4) |
+-----------------------------+--------------------------+--------------------------+------------+
| Book                        | Paper                    | Pascal                   | James      |
| Book                        | Paper                    | James                    | Fred       |
| Book                        | Paper                    | Conell                   | Roswell    |
+-----------------------------+--------------------------+--------------------------+------------+

1. In the first TRIM() clause, the padding component is specified; the leading dots are to be trimmed from the output of col1.
2. The trailing spaces will be trimmed off of col2—space is the default.
3. Both leading and trailing underscores are trimmed from col3 above. Unless specified, BOTH is the default.
4. Leading and trailing spaces are trimmed from col4 in the statement here.

The trim functions can also be useful for cleaning data with the UPDATE statement, here is an example:

UPDATE table3
SET col4 = LTRIM( RTRIM(col4) ),
col4 = LTRIM( RTRIM(col4));

This trim both the leading and the trailing spaces for all the data in col4. Agaim, if you hate stress like me, you can just use only the TRIM() function like so:

UPDATE table3
SET col4 = TRIM(col4),
col4 = TRIM(col4);

Padding

When displaying data in forms, or when creating a summary box, it's sometimes useful to pad the data displayed with zeros or dots or some other filler.

This can be necessary when dealing with VARCHAR columns where the width varies to help the user to see the column limits.

There are two functions that may be used for padding: LPAD() and RPAD().

SELECT RPAD(col4, 8, '.') AS 'Right Pad',
LPAD(col2, 10, '_') AS 'Left Pad'
FROM table3;

+-----------+------------+
| Right Pad | Left Pad   |
+-----------+------------+
| James...  | _____Paper |
| Fred....  | _____Paper |
| Roswell.  | _____Paper |
+-----------+------------+

In the first SQL statement, dots are added to the right end of each part number. If the string is longer than the number we specified (in this case 8), the return value would be the string length. For example, "Fred" string length is 4, and so, 4 dots would be added to the right. If for example, we have "Fredland", no dot would be added.

In the second statement, Each col2 data will have under-scores preceding it.  If the string is longer than the number we specified (in this case 15), the return value would be the string length. For example, "Paper" string length is 5, and so, 10 underscores would be added to the left. If the string is equals or more than 15, no underscore would be added to the left.

Extracting Data

When there is a need to extract specific elements from a column, MariaDB has a few functions that can help.

You indicate the point from which to start selecting text and how much text you want to select. There are four functions that can help with this:

• LEFT(),
• MID(),
• RIGHT(), and
• SUBSTRING().

Note: The following piece is taken from: https://mariadb.com/kb/en/mariadb-string-functions/#extracting

Suppose a column in the table contacts contains the telephone numbers of sales contacts, including the area-codes, but without any dashes or parentheses. The area-code of each could be extracted for sorting with the LEFT() and the telephone number with the RIGHT() function.

SELECT LEFT(telephone, 3) AS area_code,
RIGHT(telephone, 7) AS tel_nbr
FROM contacts
ORDER BY area_code;

• In the LEFT() function above, the column telephone is given along with the number of characters to extract, starting from the first character on the left in the column.
• The RIGHT() function is similar, but it starts from the last character on the right, counting left to capture, in this statement, the last seven characters.
• area_code is reused to order the results set.

To reformat the telephone number, it will be necessary to use the SUBSTRING() function.

SELECT CONCAT('(', LEFT(telephone, 3), ') ',
SUBSTRING(telephone, 4, 3), '-',
MID(telephone, 7)) AS 'Telephone Number'
FROM contacts
ORDER BY LEFT(telephone, 3);

In this SQL statement, the CONCAT() function is employed to assemble some characters and extracted data to produce a common display for telephone numbers (e.g., (504) 555-1234).

• The first element of the CONCAT() is an opening parenthesis. Next, a LEFT() is used to get the first three characters of the telephone, the area-code. After that, a closing parenthesis, along with a space is added to the output.
• The next element uses the SUBSTRING() function to extract the telephone number's prefix, starting at the fourth position, for a total of three characters. Then a dash is inserted into the display.
• Finally, the function MID() extracts the remainder of the telephone number, starting at the seventh position. The functions MID() and SUBSTRING() are interchangeable and their syntax are the same. By default, for both functions, if the number of characters to capture isn't specified, then it's assumed that the remaining ones are to be extracted.

REPLACE() Function

You can use the replace function to replace every occurrence of a string with whatever you specify.  For example, if you want to replace "Bag" with "Bags" in a column, but only in the output, you can do the following:

SELECT REPLACE(rec2, 'Bag', 'Bags') AS Items
FROM table2;

+--------+
| Items  |
+--------+
| Paper  |
| Mouse  |
| iPhone |
| Bags   |
+--------+

The REPLACE() function extracts each data in rec2, and find if there is any with the name "Bag", if there is, it replaces it with "Bags". Otherwise, for all other rec2 data, it displays them unchanged.

We can use the REPLACE() function to replace actual data in the table,

Note: There is an alternative to this approach in the next section.

Suppose, we have the following table:

+----+-----------+
| id | rec2      |
+----+-----------+
|  1 | My Paper  |
|  2 | My Mouse  |
|  3 | My iPhone |
|  4 | My Bag    |
+----+-----------+

To replace all occurrence of the letter "My" to "This is My" we can do:

Note: The SELECT statement is used to simulate what is actually gonna be replaced, it doesn't replace the data in the table.

SELECT rec2 AS Original,
REPLACE(rec2, 'My', 'This is My') AS Replaced
FROM table2;

+-----------+-------------------+
| Original  | Replaced          |
+-----------+-------------------+
| My Paper  | This is My Paper  |
| My Mouse  | This is My Mouse  |
| My iPhone | This is My iPhone |
| My Bag    | This is My Bag    |
+-----------+-------------------+

To replace the actual data, you can do:

UPDATE table2
SET rec2 = REPLACE(rec2, 'My', 'This is My');

Locating Text Within a String (Plus Replacing)

MariaDB has a few built-in functions that can find characters within a string. These functions return the location where the search parameter was found.

Suppose we have the following table:

+----+-----------+
| id | col1      |
+----+-----------+
|  1 | My Book   |
|  2 | My Pen    |
|  3 | My Laptop |
+----+-----------+

and we would love to locate where the string "My" is located in col1, we can do it the following way:

SELECT 
    LOCATE('My', col1) As Item
FROM table4;

+------+
| Item |
+------+
|    1 |
|    1 |
|    1 |
+------+

It returns the position of the first occurrence of "My" in the given string (col1). We need to pass both the strings (i.e. substring, which is to be searched and the string, from which substring is to be searched) as arguments of the LOCATE() function.

To LOCATE the position of Book in "My Book", we can do:

SELECT 
    LOCATE('Book', col1) As Item 
FROM table4;

+------+
| Item |
+------+
|    4 |
|    0 |
|    0 |
+------+

If we want to insert or replace certain text from a column (but not all of its contents), we can combine INSERT() with the LOCATE() function.

We can do this too with the REPLACE function (as shown in the replace section above), it's just a matter of preference I'll say. I find REPLACE() to be faster than using INSERT() and LOCATE().

So, suppose, we want to replace all the "My" from the col1, we can first test how it would work:

SELECT
    INSERT(col1, LOCATE('My', col1), 2, '')
FROM table4;

+------------------------------------------+
| INSERT(col1, LOCATE('My ', col1), 2, '') |
+------------------------------------------+
|  Book                                    |
|  Pen                                     |
|  Laptop                                  |
+------------------------------------------+

The first element of the INSERT() function is the column I.e col1.

The second element which contains the LOCATE() is the position in the string that text is to be inserted.

The third element is optional; it states the number of characters to overwrite. In our case, it is "My", and that means 2 characters would be overwritten with ''(empty, note: it is 2 single quotes and not a double quote)

Incidentally, if 0 is specified, then nothing is overwritten, text is inserted only.

As for the LOCATE() function, the first element is the search text, and the second element is the column name. It returns the position within the column where the text is found. If it's not found, then 0 is returned. A value of 0 for the position in the INSERT() function negates it and returns the value of the name unchanged.

Because of the inclusion of LOCATE() for the starting location, the WHERE clause is not needed.

Now that we’ve verified that our combination of functions works correctly, we can update
the data by entering the following SQL statement:

UPDATE table4
SET col1 = INSERT(col1, LOCATE('My', col1), 2, '');

Now, check the data:

SELECT * FROM table4;

+----+---------+
| id | col1    |
+----+---------+
|  1 |  Book   |
|  2 |  Pen    |
|  3 |  Laptop |
+----+---------+

To append the "My" at the beginning of each data in the col1 column, we can do, the following:

Note: We are using SELECT to test how it works first

SELECT
INSERT(col1, LOCATE('', col1), 0, 'My')
FROM table4;

+-----------------------------------------+
| INSERT(col1, LOCATE('', col1), 0, 'My') |
+-----------------------------------------+
| My Book                                 |
| My Pen                                  |
| My Laptop                               |
+-----------------------------------------+

We can do an actual update by entering the following SQL statement:

UPDATE table4
SET col1 = INSERT(col1, LOCATE('', col1), 0, 'My');

String Lengths

Sometimes, you might want to know how long a string is, or a number of character in a string. Luckily, MariaDB has a function that return the length of a string.

This can be useful when adjusting formatting or making other decisions related to a string, and they are commonly used with functions like LOCATE() and SUBSTRING().

The function that return the number of  character in a String is CHAR_LENGTH() or CHARACTER_LENGTH.

Here is a simple example:

SELECT genre_description As 'Genre Description',
    CHAR_LENGTH(genre_description) As 'Character Length'
FROM cmusic_genre
WHERE genre_id = 1 \G

*************************** 1. row ***************************
Genre Description: Indie pop or Alt-pop is a music genre and subculture that combines guitar pop with DIY ethic in opposition to the style and tone of mainstream pop music.
 Character Length: 153

Suppose, you want to display a summary of the genre_description columns if it is longer than 20 characters, you can do it the following way:

SELECT genre_id AS 'Genre ID',
genre_name AS 'Genre Name',
    IF(CHAR_LENGTH(genre_description) > 20, 
        CONCAT(LEFT(genre_description, 20), '...'),
        genre_description) AS Summary
FROM cmusic_genre
WHERE genre_id = 1;

+----------+------------+-------------------------+
| Genre ID | Genre Name | Summary                 |
+----------+------------+-------------------------+
|        1 | Indie pop  | Indie pop or Alt-pop... |
+----------+------------+-------------------------+

Here we’re using CHAR_LENGTH() to count the number of characters in the genre_description column for the row selected.

We’re using the IF() function to determine whether the character length of the genre_description is greater than 20 characters. If it is, the function will return only 20 characters, and that is made possible by CONCAT and LENGTH, we concatenate the excerpt with the ellipsis (…) by using the CONCAT statement, otherwise, we just get the summary.

Remove the WHERE clause if you want it to affect the whole data, in the column.

Just counting the number of character is not really realistic, you can use your programming language function to count the word instead if you prefer.

Comparing and Searching Strings

MariaDB provides STRCMP() function a.k.a string compare that can let you compare two strings. It returns 0 if both of the strings are the same and returns -1 if the first argument is smaller than the second argument, and it returns 1 when the second argument is smaller than the first argument.

MariaDB [database]> SELECT STRCMP('Devsrealm', 'Devsrealm');
+----------------------------------+
| STRCMP('Devsrealm', 'Devsrealm') |
+----------------------------------+
|                                0 |
+----------------------------------+

MariaDB [marn_melodic]> SELECT STRCMP('Devsrelm', 'Devsrealm');
+---------------------------------+
| STRCMP('Devsrelm', 'Devsrealm') |
+---------------------------------+
|                               1 |
+---------------------------------+

MariaDB [marn_melodic]> SELECT STRCMP('Devsrealm', 'evsrealm');
+---------------------------------+
| STRCMP('Devsrealm', 'evsrealm') |
+---------------------------------+
|                              -1 |
+---------------------------------+

Date and Time Functions

One of the frustrating thing you'll deal with is managing data and time, there are lots of stuff that can go wrong, however, in this section we would explore various aspects of date and time functions in MariaDB.

Depending on the context, dates and time are temporal data, and to deal with this beast of data, you should most times use temporal data types to store date, time, and time-interval information.

While you can store this data in character strings, it is better to use temporal types for consistency, better comparison, and validation.

By using temporal data type columns, you can make use of several built-in functions offered by MySQL and MariaDB, so, before we get into the details of the functions, here are the data types that are available for recording date and time.

DATE & TIME Data Types

Example usage when creating table using DATE:

CREATE TABLE users
(user_id INT AUTO_INCREMENT PRIMARY KEY,
name_first VARCHAR(30),
name_last VARCHAR(30),
email_address VARCHAR(255),
membership_expiration DATE);

To insert data, you can do:

INSERT INTO users
(name_first, name_last, email_address, membership_expiration)
VALUES
('Blaise', 'Pascal', 'blaise@melodic.com', CURDATE()),
('Charles', 'Dawton', 'charles@melodic.com', CURDATE()),
('James', 'Peter', 'james@melodic.com', CURDATE()),
('Devsrealm', 'Guy', 'devsrealm@melodic.com', CURDATE()),
('Nubia', 'Putin', 'nubia@melodic.com', CURDATE());

Here is a select resut:

+---------+------------+-----------+-----------------------+-----------------------+
| user_id | name_first | name_last | email_address         | membership_expiration |
+---------+------------+-----------+-----------------------+-----------------------+
|       1 | Blaise     | Pascal    | blaise@melodic.com    | 2020-11-07            |
|       2 | Charles    | Dawton    | charles@melodic.com   | 2020-11-07            |
|       3 | James      | Peter     | james@melodic.com     | 2020-11-07            |
|       4 | Devsrealm  | Guy       | devsrealm@melodic.com | 2020-11-07            |
|       5 | Nubia      | Putin     | nubia@melodic.com     | 2020-11-07            |
+---------+------------+-----------+-----------------------+-----------------------+

Now that we have an idea of the date and time data types, let's explore how you might use them in a function:

Getting The Current Date and Time

This can be used for recording the current date and time in a column or modifying results based on the current date, or for displaying the date and time.

To return the current date and time on a server in a format that matches the format of the DATETIME data type, you do:

MariaDB [db]> SELECT NOW() AS 'Date & Time';
+---------------------+
| Date & Time         |
+---------------------+
| 2020-11-06 15:19:32 |
+---------------------+

If you have a column that supports the DATETIME data type, you can simply use the NOW() function, and that would automatically insert the current date and time into the column, e.g:

INSERT INTO cmusic_table
(music_id, last_updated)
VALUES (54, NOW( ));

Note: The NOW() function returns the date and time at the start of the SQL statement containing it, and once the execution of the SQL statement is done, it uses what has been returned.

If for example, you have an SQL statement that takes long time to execute, and you want to return the time that the SQL statement finishes, you can use the SYSDATE() function.

This is an example, which uses SLEEP() function to delay the execution for a given number of seconds:

SELECT NOW() AS 'NOW',
    SLEEP(5) AS 'Sleep For 5secs',
    SYSDATE() ,
    SLEEP(3) AS 'Sleep For 3secs',
    SYSDATE();

+---------------------+-----------------+---------------------+-----------------+---------------------+
| NOW                 | Sleep For 5secs | SYSDATE()           | Sleep For 3secs | SYSDATE()           |
+---------------------+-----------------+---------------------+-----------------+---------------------+
| 2020-11-06 14:31:24 |               0 | 2020-11-06 14:31:29 |               0 | 2020-11-06 14:31:32 |
+---------------------+-----------------+---------------------+-----------------+---------------------+
1 row in set (8.00 sec)

You can see it took 8sec to execute, the NOW function returns the date at the start of the execution, while the SYSDATE waits until the SQL statement completes.

You would rarely use this, but it is good when you execute complex SQL statement

If you only want date only, use CURDATE(), e.g:

MariaDB [db]> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2020-11-06 |
+------------+

For time only, use:

MariaDB [db]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:38:57  |
+-----------+

An example, showing NOW(), CURDATE(), CURTIME():

SELECT NOW(), CURDATE(), CURTIME();

MariaDB [marn_melodic]> SELECT NOW(), CURDATE(), CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2020-11-06 14:40:29 | 2020-11-06 | 14:40:29  |
+---------------------+------------+-----------+

Extracting Date and Time

There would be times when you don't want a full date or a time to the seconds, for this reason, mariadb comes with function that will extract any component of a temporary value you may want.

Suppose a column has a DATETIME data type, to extract the date only, you would use the DATE() function, and to extract the time component, you use the TIME() function.

Here is an example:

SELECT CONCAT_WS(' - ', artiste_name, title) AS 'Song',
DATE(last_played), 
TIME(last_played)
FROM cmusic
WHERE music_id = 566;

The DATE() function will only extract the date component in the last_played column, and the TIME() function will only extract the time component in the last_played column.

To extract only the hour, you use the HOUR() function, for minutes, you use the MINUTE() function, and for seconds, you use the SECOND() function. HOUR(), MINUTE(), and SECOND() may be used with DATETIME, TIME and TIMESTAMP columns.

E.g:

SELECT CONCAT_WS(' - ', artiste_name, title) AS 'Song',
DATE(last_played), 
TIME(last_played)
HOUR(last_played)
MINUTE(last_played)
SECOND(last_played)
FROM cmusic
WHERE music_id = 566;

*************************** 1. row ***************************
                SONG: ASA - JAILER
   DATE(last_played): 2020-11-06
   TIME(last_played): 15:55:56
   HOUR(last_played): 15
 MINUTE(last_played): 55
 SECOND(last_played): 56

To extract the year, month, and day, you use:

• YEAR()
• MONTH()
• DAY()

You have to give a date value as the argument for each column, in most case, it might be a column that contains a date, or you can just pass a string, e.g:

SELECT DATE('2020-11-06'),
YEAR('2020'),
MONTH('11'),
DAY('06');

As you have seen above, you can't pass a number(you can actually pass a number if it is orderly arranged, e.g 20201106), it has to be a string, enclosed in a single quote.

+--------------------+--------------+-------------+-----------+
| DATE('2020-11-06') | YEAR('2020') | MONTH('11') | DAY('06') |
+--------------------+--------------+-------------+-----------+
| 2020-11-06         |         NULL |        NULL |      NULL |
+--------------------+--------------+-------------+-----------+
1 row in set, 3 warnings (0.00 sec)

Warning (Code 1292): Incorrect datetime value: '2020'
Warning (Code 1292): Incorrect datetime value: '11'
Warning (Code 1292): Incorrect datetime value: '06'

You might have thought if you have YEAR('2020') it would return the year, well, YEAR, MONTH and DAY function are for extracting components for datetime data type, so, you need to pass the whole datetime value, something like this:

SELECT DATE('2020-11-06'),
YEAR('2020-11-06'),
MONTH('2020-11-06'),
DAY('2020-11-06');

+--------------------+--------------------+---------------------+-------------------+
| DATE('2020-11-06') | YEAR('2020-11-06') | MONTH('2020-11-06') | DAY('2020-11-06') |
+--------------------+--------------------+---------------------+-------------------+
| 2020-11-06         |               2020 |                  11 |                 6 |
+--------------------+--------------------+---------------------+-------------------+

To convert Month, and Day into the actual date name, you can use:

• MONTHNAME()
• DAYNAME()

For example:

SELECT DATE('2020-11-06'),
YEAR('2020-11-06'),
MONTH('2020-11-06'),
DAY('2020-11-06'),
MONTHNAME('2020-11-06'),
DAYNAME('2020-11-06');

+--------------------+--------------------+---------------------+-------------------+-------------------------+-----------------------+
| DATE('2020-11-06') | YEAR('2020-11-06') | MONTH('2020-11-06') | DAY('2020-11-06') | MONTHNAME('2020-11-06') | DAYNAME('2020-11-06') |
+--------------------+--------------------+---------------------+-------------------+-------------------------+-----------------------+
| 2020-11-06         |               2020 |                  11 |                 6 | November                | Friday                |
+--------------------+--------------------+---------------------+-------------------+-------------------------+-----------------------+

Here is an example of how you can use it in an actual table:

SELECT 'Asa - Jailer' AS Song,
CONCAT(DAYNAME('2020-11-06'), ', ', MONTHNAME('2020-11-06'), SPACE(1),
    DAY('2020-11-06'), ', ', YEAR('2020-11-06')) AS 'Last Played';

+--------------+--------------------------+
| Song         | Last Played              |
+--------------+--------------------------+
| Asa - Jailer | Friday, November 6, 2020 |
+--------------+--------------------------+

You would replace the strings with an actual column name, for the datetime value, you can use something like last_played, depending on the name of your column.

Let's include a time column:

SELECT 'Asa - Jailer' AS Song,
CONCAT(DAYNAME('2020-11-06'), ', ', MONTHNAME('2020-11-06'), SPACE(1),
    DAY('2020-11-06'), ', ', YEAR('2020-11-06')) AS 'Last Played',

CONCAT(HOUR('2020-11-06 15:39:10'), ': ', MINUTE('2020-11-06 15:39:10'),
    IF(HOUR('2020-11-06 15:39:10') < 12, ' a.m', ' p.m')) AS 'Last Viewed';

+--------------+--------------------------+-------------+
| Song         | Last Played              | Last Viewed |
+--------------+--------------------------+-------------+
| Asa - Jailer | Friday, November 6, 2020 | 15: 39 p.m  |
+--------------+--------------------------+-------------+

HOUR(), MINUTE(), and SECOND() may be used with DATETIME, TIME and TIMESTAMP data type.

As you can see, we are using the IF function to test if the hour is lesser than 12, if it is, we append a.m, if otherwise, we concatenate p.m

Formatting Dates and Time

Manipulating date and time might be frustrating, but it is quite interesting if you know how to make use of the in-built function to manage date & time, and an example of that is date and time formatting functions.

If you don't like the default formatting, mariadb provides an inbuilt function that may be used to return temporal data in different formats.

An example of that is the DATE_FORMAT(), and TIME_FORMAT(). You can use these to format date and time values taken from a column, a string, or another function.

Here is an example:

SELECT DATE('2020-11-06 15:39:10'),
DATE_FORMAT('2020-11-06 15:39:10', '%W, %M %e, %Y') AS 'DATE FORMAT',
TIME_FORMAT('2020-11-06 15:39:10', '%I:%i %p') AS 'TIME FORMAT';

+-----------------------------+--------------------------+-------------+
| DATE('2020-11-06 15:39:10') | DATE FORMAT              | TIME FORMAT |
+-----------------------------+--------------------------+-------------+
| 2020-11-06                  | Friday, November 6, 2020 | 03:39 PM    |
+-----------------------------+--------------------------+-------------+

With the DATE_FORMAT() and the TIME_FORMAT() functions, you
give the column to format as the first argument and then provide a string in quotes that contains formatting codes and text to lay out how you want the date and time formatted.

This is the best method when it comes to formatting data, in short, it handles padding and even colon for you. These are the Date and Time formatting codes if you wanna harness the power of formatting:

Date and Time Formatting Codes Table

Adding and Subtracting Dates and Time

It's not quite straightforward to do calculations with date, to make our life even easier, mariadb includes several built-in functions that may be used to change a given date or time by adding or subtracting.

The major ones are DATE_ADD(), and DATE_SUB(). It takes two arguments, the first one is the date to be modified, and the second argument is the amount of time.

The amount of time is presented with the keyword INTERVAL, followed by a count of intervals, followed by the date or time factor (e.g., INTERVAL 1 MONTH).

Suppose, your member renewed their subscription for a year, you can use the DATE_ADD to extend it, before updating the actual data, we can use the select statement to simulate what would get updated:

SELECT name_first, name_last,
DATE_ADD(membership_expiration, INTERVAL 1 YEAR) AS 'Membership Expiration'
FROM users
WHERE user_id = 5
AND membership_expiration < CURDATE( );

+------------+-----------+-----------------------+
| name_first | name_last | Membership Expiration |
+------------+-----------+-----------------------+
| Nubia      | Putin     | 2021-10-07            |
+------------+-----------+-----------------------+

We are adding a year to the current membership_expiration, but just for the user that has an id of 5, but not for those whose membership has already expired.

We are using the greater sign to compare two date values in the WHERE clause, meaning expression membership_expiration < CURDATE() will verify the date stamp with the current date, if the membership_expiration timestamp is lesser than the current date, then it means the membership as expired, and hence a year would get added, otherwise, it will return an empty result set.

To do the actual update in your table, you do:

UPDATE users
SET membership_expiration = DATE_ADD(membership_expiration, INTERVAL 1 YEAR)
WHERE user_id = 5
AND membership_expiration < CURDATE( );

Let's do a select, and you'll see the date would have been extended by a year:

MariaDB [marn_melodic]> SELECT * FROM users;
+---------+------------+-----------+-----------------------+-----------------------+
| user_id | name_first | name_last | email_address         | membership_expiration |
+---------+------------+-----------+-----------------------+-----------------------+
|       1 | Blaise     | Pascal    | blaise@melodic.com    | 2020-11-07            |
|       2 | Charles    | Dawton    | charles@melodic.com   | 2020-11-07            |
|       3 | James      | Peter     | james@melodic.com     | 2020-11-07            |
|       4 | Devsrealm  | Guy       | devsrealm@melodic.com | 2020-11-07            |
|       5 | Nubia      | Putin     | nubia@melodic.com     | 2021-10-07            |
+---------+------------+-----------+-----------------------+-----------------------+

Now, suppose, our member wanted to actually extend their membership by 6 months, and we mistakenly extended it for a year, we can use DATE_SUB() to subtract it, again, let's test it with SELECT before updating the actual data:

SELECT name_first, name_last,
DATE_SUB(membership_expiration, INTERVAL 6 MONTH) AS 'Membership Expiration'
FROM users
WHERE user_id = 5
AND membership_expiration > CURDATE( );

+------------+-----------+-----------------------+
| name_first | name_last | Membership Expiration |
+------------+-----------+-----------------------+
| Nubia      | Putin     | 2021-04-07            |
+------------+-----------+-----------------------+

You don't even need to add the expression : AND membership_expiration > CURDATE( )

But, I added it just to be sure the membership is renewed at all. Also, you can use the DATE_ADD() function to achieve the same result by changing the count of the interval to a negative number, e.g:

SELECT name_first, name_last,
DATE_ADD(membership_expiration, INTERVAL -6 MONTH) AS 'Membership Expiration'
FROM users
WHERE user_id = 5
AND membership_expiration > CURDATE( );

+------------+-----------+-----------------------+
| name_first | name_last | Membership Expiration |
+------------+-----------+-----------------------+
| Nubia      | Putin     | 2021-04-07            |
+------------+-----------+-----------------------+

As you can see, it gave us the same result, so, which ever you use is up to you.

To update the actual data, we do:

UPDATE users
SET membership_expiration = DATE_SUB(membership_expiration, INTERVAL 6 MONTH)
WHERE user_id = 5
AND membership_expiration > CURDATE( );

If the data type you used for a certain column is a timestamp, and you want to ADD maybe 2days and 3 hours for one your user, you can do:

UPDATE last_played_table
SET last_played_col = DATE_ADD(last_played_col, INTERVAL '2 3' DAY_OUR)
WHERE played_id = 789;

The argument for the interval count is a combination of two intervals, DAY_HOUR for both DAY and HOUR.

We list the counts in the same order and put them within quotes. If we want to subtract the intervals (i.e., one day and two hours earlier), we would put a negative sign within the quotes before one of the values.

Below is a table that lists the date and time interval format if you want more flexibility:

Date and Time Intervals Formats Table

Comparing Dates and Times

Let's conclude the date and time section with a function that gives you the ability to compare values containing dates and times.

There are a couple of functions that can do this, but let's look at the common ones, which are DATEDIFF() and TIMEDIFF(). Using the functions alone would give you the ability to compare two dates or times.

Suppose you want to display the remaining days until a certain user membership expires on the member's profile page or wherever you want, we can use the DATEDIFF() function like so:

SELECT CURDATE() AS "Today's Date",
DATE_FORMAT(membership_expiration, '%M %e, %Y')
    AS 'Your Membership Expires',
DATEDIFF(membership_expiration, CURDATE())
    AS 'Days Until Expiration'
FROM users
WHERE user_id = 5;

+--------------+-------------------------+-----------------------+
| Today's Date | Your Membership Expires | Days Until Expiration |
+--------------+-------------------------+-----------------------+
| 2020-11-07   | April 7, 2021           |                   151 |
+--------------+-------------------------+-----------------------+

Similar to DATEDIFF(), you can get the difference between time values using the TIMEDIFF() function.

Suppose you want to determine a time and date difference until the start of a certain event, you can do:

SELECT NOW() AS 'Current Date & Time'
    event_date, start_time,
    DATEDIFF(event_date, DATE(NOW())) AS 'Days To Event',
    TIMEDIFF(start_time, TIME(NOW())) AS 'Time To Start'
FROM music_events;

Off course, you'll want to make sure the column that holds the both date and time has the proper date and time data type.

Manipulating and using date and time can be frustrating, but chances are whatever you want to do already has a function that makes your life easier, good luck, and I hope you find this section useful.

Numeric Functions

I would conclude this guide by diving a bit deep into the major numeric functions in mariadb.

There is no way you won't need to use a numeric function, be it for aggregating data, counting a specific row, and doing some basic calculation. That said, let's start with...

Aggregate Functions

Aggregate functions will help us calculate statistical values of sorts, let's see some example:

Counting All rows in a table:

To count all rows in a table, you can utilize the count function, this is particularly useful if you have thousands of rows, and you want to know ahead how many they are, you can do it the following way:

SELECT COUNT(*) FROM cmusic;

+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+

We put an asterisk within the parentheses of the function to indicate that we want all of the rows. We could put a column name instead of an asterisk to count only rows that have data.

Using a column prevents MySQL from counting rows that have a NULL value in that column. But it will count rows that have a blank or empty value (i.e.,' ').

For example, I have a column titled description with all the values set to NULL, if I use the count function, it would return nada, see:

SELECT COUNT(description) FROM cmusic;

+--------------------+
| COUNT(description) |
+--------------------+
|                  0 |
+--------------------+

Suppose, I have the following table:

+----------+-------+
| music_id | mood  |
+----------+-------+
|        1 | Dance |
|        2 | NULL  |
|        3 | NULL  |
|        4 | Dance |
|        5 | Dance |
|        6 | Dance |
|        7 | NULL  |
+----------+-------+

If you want to count the rows where the mood column is NULL, you can do:

SELECT COUNT(*) 
FROM cmusic
WHERE mood IS NULL;

+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

and if you want to count the row where the mood column is not null, you just reverse it like so:

SELECT COUNT(*) 
FROM cmusic
WHERE mood IS NOT NULL;

+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+

To count a number of times a certain item appears in a table, we can do:

SELECT 
    cmusic.artiste AS  'Artiste Name',
    COUNT(*) AS	'Number of Time Avicii Appeared'
FROM 
    cmusic
WHERE artiste = 'Avicii';

+--------------+--------------------------------+
| Artiste Name | Number of Time Avicii Appeared |
+--------------+--------------------------------+
| Avicii       |                              2 |
+--------------+--------------------------------+

You can use GROUP by to group a certain data together, let's count the number of songs withing each genre of music:

SELECT COUNT(*) AS 'Number of Song Within Each Genre of Music'
FROM cmusic
GROUP BY genre_id;

+-------------------------------------------+
| Number of Song Within Each Genre of Music |
+-------------------------------------------+
|                                         1 |
|                                         2 |
|                                         2 |
|                                         2 |
+-------------------------------------------+

This is not really useful, but I have a genre table that is linked to the cmusic table by its genre_id, here is the genre table:

+----------+-------------------+
| genre_id | genre_name        |
+----------+-------------------+
|        1 | Indie pop         |
|        2 | Progressive House |
|        3 | Folk and Root     |
|        4 | Afrobeat          |
+----------+-------------------+

Now, we can group everything together, and find the number of times a certain genre is allocated for an artiste in our table:

SELECT 
    cmusic_genre.genre_name AS 'Genre',
    COUNT(*) AS 'Number of Appearance'
FROM 
    cmusic,
    cmusic_genre
WHERE cmusic.genre_id = cmusic_genre.genre_id
GROUP BY cmusic_genre.genre_name;

+-------------------+----------------------+
| Genre             | Number of Appearance |
+-------------------+----------------------+
| Afrobeat          |                    2 |
| Folk and Root     |                    2 |
| Indie pop         |                    1 |
| Progressive House |                    2 |
+-------------------+----------------------+

We gave the GROUP BY clause cmusic_genre.genre_name;, which is the genre_name column from the cmusic_genre table. If we didn’t use the GROUP clause, we won’t be able to count the separate rows in cmusic_genre table.

Alternatively, instead of using the WHERE clause, we can use the JOIN clause to get a similar result.

Before linking a table, you’ll want to make sure the tables have something in common for purposes of selecting, updating, or deleting data.

The relationship of the tables might be an id, and from there, you can link them together to present a meaningful result altogether. Recapping the tables, we have:

Table 1 – cmusic:

+----------+-----------------+-------------------------+----------+-------------+
| music_id | artiste         | title                   | genre_id | producer_id |
+----------+-----------------+-------------------------+----------+-------------+
|        1 | Avicii          | Silhouette              |        2 |           1 |
|        2 | Beautiful Nubia | Higher Steps            |        3 |           3 |
|        3 | Beautiful Nubia | Tables Turn             |        3 |           3 |
|        4 | Fela            | Observation Is No Crime |        4 |           4 |
|        5 | Avicii          | Levels                  |        2 |           1 |
|        6 | Fela            | Colonial Mentality      |        4 |           4 |
|        7 | Asa             | Jailer                  |        1 |           2 |
+----------+-----------------+-------------------------+----------+-------------+

Table 2 – cmusic_genre:

+----------+-------------------+
| genre_id | genre_name        |
+----------+-------------------+
|        1 | Indie pop         |
|        2 | Progressive House |
|        3 | Folk and Root     |
|        4 | Afrobeat          |
+----------+-------------------+

If you look at the two table carefully, you can see that their relationship is defined by the genre_id in the both table. We can get the joined data from both tables by running the following query:

SELECT 
    cmusic_genre.genre_name AS 'Genre',
    COUNT(*) AS 'Number of Appearance'
FROM cmusic 
    JOIN cmusic_genre
USING (genre_id)
GROUP BY cmusic.genre_id;

+-------------------+----------------------+
| Genre             | Number of Appearance |
+-------------------+----------------------+
| Indie pop         |                    1 |
| Progressive House |                    2 |
| Folk and Root     |                    2 |
| Afrobeat          |                    2 |
+-------------------+----------------------+

If you want to group the result in alphabetically order, you can group by the name instead as I have done below:

Note: The genre_name column is available in the cmusic_genre table, that is why I am using cmusic_genre.genre_name. Since we've joined the table together, we can call whatever column we want, just make sure you include the table name.

SELECT 
    cmusic_genre.genre_name AS 'Genre',
    COUNT(*) AS 'Number of Appearance'
FROM cmusic 
    JOIN cmusic_genre
USING (genre_id)
GROUP BY cmusic_genre.genre_name;

+-------------------+----------------------+
| Genre             | Number of Appearance |
+-------------------+----------------------+
| Afrobeat          |                    2 |
| Folk and Root     |                    2 |
| Indie pop         |                    1 |
| Progressive House |                    2 |
+-------------------+----------------------+

If you want to catch a genre that is not related to the genre_id or if some of the rows have genre_id of NULL, and it is not contained in the genre_name column, you can use the LEFT JOIN to get the details, something like:

SELECT 
    cmusic_genre.genre_name AS 'Genre',
    COUNT(*) AS 'Number of Appearance'
FROM cmusic 
    LEFT JOIN cmusic_genre
USING (genre_id)
GROUP BY cmusic_genre.genre_name;

You'll get an output like so:

+-------------------+----------------------+
| Genre             | Number of Appearance |
+-------------------+----------------------+
| Afrobeat          |                    2 |
| Folk and Root     |                    2 |
| Indie pop         |                    1 |
| Progressive House |                    2 |
| NULL              |                    5 |
| NULL              |                    3 |
+-------------------+----------------------+

You can learn more about Joining tables: Joining and Subquerying Data (MariaDB)

What if I tell you that you can't show the total number of what you are aggregating. To do that, we would add 'WITH ROLLUP' to the GROUP BY clause like so:

SELECT 
    cmusic_genre.genre_name AS 'Genre',
    COUNT(*) AS 'Number of Appearance'
FROM cmusic 
    LEFT JOIN cmusic_genre
USING (genre_id)
GROUP BY cmusic_genre.genre_name WITH ROLLUP;

+-------------------+----------------------+
| Genre             | Number of Appearance |
+-------------------+----------------------+
| Afrobeat          |                    2 |
| Folk and Root     |                    2 |
| Indie pop         |                    1 |
| Progressive House |                    2 |
| NULL              |                    7 |
+-------------------+----------------------+

The total is on the last line with the name NULL, well, mariadb just put that as default, to give it a label, we do:

SELECT 
    IFNULL(cmusic_genre.genre_name, 'Total:') AS 'Genre',
    COUNT(*) AS 'Number of Appearance'
FROM cmusic 
    LEFT JOIN cmusic_genre
USING (genre_id)
GROUP BY cmusic_genre.genre_name WITH ROLLUP;

+-------------------+----------------------+
| Genre             | Number of Appearance |
+-------------------+----------------------+
| Afrobeat          |                    2 |
| Folk and Root     |                    2 |
| Indie pop         |                    1 |
| Progressive House |                    2 |
| Total:            |                    7 |
+-------------------+----------------------+

Easy right. Here is how it works: We used the IFNULL() function to wrap the fields for the genre_name count. It tells mariadb that if the value for the field will be NULL, it should be replace with the value or string given, in our case it is "Total:" else it should return the name of the genre_name field.

There are more functions, and I would update this page regularly as I learn about more functions in mariadb.

For now, adios|odabo.