In this guide, we would look at various ways you can select data in mariadb. If you are new to mariadb, you can read the following guide, doing this would help you understand the select statement even more:
- Introduction To MariaDB
- [MariaDB] Guide To Creating Databases and Tables
- Altering MariaDB Tables
- Indexes In MariaDB
- Guide To Understanding INSERT Statement (MariaDB)
The simplest way to retrieve data from a MariaDB database is by using the SQL statement, SELECT.
SELECT Syntax
SELECT is used to retrieve rows selected from one or more tables, and has the following syntax:
SELECT COLUMN FROM table_name;The column indicates the column or number of columns (separated by comma) that you want to retrieve. There must be at least one column to retrieve from.
The table_name indicates the table or tables from which to retrieve
rows.
SELECT All Data From a Table
Enter the following SQL statement in mariadb to get a list of all of the columns and rows in a table:
SELECT * FROM cmusic;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| music_id | status | mood | artiste | title | genre_id | producer_id | description |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
| 1 | 1 | Dance | Avicii | Silhouette | 2 | 1 | NULL |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps | 3 | 3 | NULL |
| 3 | 1 | Dance | Beautiful Nubia | Tables Turn | 3 | 3 | NULL |
| 4 | 1 | Dance | Fela | Observation Is No Crime | 4 | 4 | NULL |
| 5 | 1 | Dance | Avicii | Levels | 2 | 1 | NULL |
| 6 | 1 | Dance | Fela | Colonial Mentality | 4 | 4 | NULL |
| 7 | 1 | Dance | Asa | Jailer | 1 | 2 | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+-------------+
SELECT Only Certain Columns From a Table
To select only certain columns, you use the SELECT statement with the list of columns you want to retrieve:
SELECT music_id, artiste, title, genre_id, producer_id FROM 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 |
+----------+-----------------+-------------------------+----------+-------------+
Limit The Numer of SELECT
To limit the number of select to maybe 4 rows, you use the LIMIT keyword followed by the number of the row you want to limit:
SELECT artiste, title FROM cmusic LIMIT 4;
+-----------------+-------------------------+
| artiste | title |
+-----------------+-------------------------+
| Avicii | Silhouette |
| Beautiful Nubia | Higher Steps |
| Beautiful Nubia | Tables Turn |
| Fela | Observation Is No Crime |
+-----------------+-------------------------+
To limit the starting and ending rows, you do:
SELECT music_id, artiste, title FROM cmusic LIMIT 2, 5;
+----------+-----------------+-------------------------+
| music_id | artiste | title |
+----------+-----------------+-------------------------+
| 3 | Beautiful Nubia | Tables Turn |
| 4 | Fela | Observation Is No Crime |
| 5 | Avicii | Levels |
| 6 | Fela | Colonial Mentality |
| 7 | Asa | Jailer |
+----------+-----------------+-------------------------+
LIMIT 2, 5 is telling mariadb to negate the first 3 rows (counting starts from zero), and the 5 would show the next 5 rows after the first 3 has been negated.
SELECT By a Condition
Suppose you want to select only songs of a certain artiste, you can do (I am selecting only songs of Beautiful Nubia):
SELECT music_id, artiste, title FROM cmusic WHERE artiste = "Beautiful Nubia";
+----------+-----------------+--------------+
| music_id | artiste | title |
+----------+-----------------+--------------+
| 2 | Beautiful Nubia | Higher Steps |
| 3 | Beautiful Nubia | Tables Turn |
+----------+-----------------+--------------+
This would select only "Beautiful Nubia"
Ordering Results of SELECT
If you want to put the results of a column in alphabetical order, you can use ORDER BY like so:
SELECT artiste, title FROM cmusic ORDER BY artiste; LIMIT 5;
+-----------------+--------------+
| artiste | title |
+-----------------+--------------+
| Asa | Jailer |
| Avicii | Silhouette |
| Avicii | Levels |
| Beautiful Nubia | Higher Steps |
| Beautiful Nubia | Tables Turn |
+-----------------+--------------+
This would order the artiste column, and limit it to the first 5 rows. By default, the ORDER BY clause uses ascending order, which means from A to Z for an alphabetic column.
If you want to display data in descending order, add the DESC option, as in ORDER BY DESC. There’s also a contrasting ASC option, but you would likely need that as ascending order is the default.
SELECT List of Data From a Table
Another handy thing you can do with the SELECT statement is selecting a list of data's from a specific column, here is how it works:
SELECT music_id, artiste, title
FROM cmusic
WHERE artiste IN('Avicii', 'Asa', 'Fela');
+----------+---------+-------------------------+
| music_id | artiste | title |
+----------+---------+-------------------------+
| 1 | Avicii | Silhouette |
| 4 | Fela | Observation Is No Crime |
| 5 | Avicii | Levels |
| 6 | Fela | Colonial Mentality |
| 7 | Asa | Jailer |
+----------+---------+-------------------------+
This is useful when finding specific data, if, for example, I only want "Avicii". I'll do:
SELECT music_id, artiste, title
FROM cmusic
WHERE artiste IN('Avicii');
+----------+---------+------------+
| music_id | artiste | title |
+----------+---------+------------+
| 1 | Avicii | Silhouette |
| 5 | Avicii | Levels |
+----------+---------+------------+
Using it together with LIMIT:
SELECT music_id, artiste, title
FROM cmusic
WHERE artiste IN('Avicii')
LIMIT 1;
+----------+---------+------------+
| music_id | artiste | title |
+----------+---------+------------+
| 1 | Avicii | Silhouette |
+----------+---------+------------+
Combine Table With SELECT
Suppose we have three tables that relates with each other:
- cmusic (serves as the main table)
- cmusic_genre (Table that holds name of genre and genre description)
- cmusic_producers (Table that holds the name of the producers)
These are what I have in all the tables...for...
cmusic_genre
SELECT genre_id, genre_name FROM cmusic_genre;
+----------+-------------------+
| genre_id | genre_name |
+----------+-------------------+
| 1 | Indie pop |
| 2 | Progressive House |
| 3 | Folk and Root |
| 4 | Afrobeat |
+----------+-------------------+
I have more columns in the cmusic_genre, to save the display, this is all we need.
For...
cmusic_producers
SELECT producer_id, producer_name FROM cmusic_producers;
+-------------+---------------+
| producer_id | producer_name |
+-------------+---------------+
| 1 | Avicii |
| 2 | Cobhams |
| 3 | Eni Obanke |
| 4 | Felas' Band |
+-------------+---------------+
For the main table
cmusic table
SELECT music_id, artiste, title, genre_id, producer_id FROM 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 |
+----------+-----------------+-------------------------+----------+-------------+
Looking at the table, you'll see they have something in common, cmusic_genre and cmusic_producers table have a reference id that can be linked with the cmusic table since that has their id's.
Let's connect the tables together and retrieve data from them, we can do it like so:
SELECT
cmusic.title AS 'Song Title',
cmusic.artiste AS 'Artiste Name',
cmusic_genre.genre_name AS 'Music Genre',
cmusic_producers.producer_name AS 'Producers'
FROM
cmusic,
cmusic_genre,
cmusic_producers
WHERE cmusic.genre_id = cmusic_genre.genre_id
AND
cmusic.producer_id = cmusic_producers.producer_id
LIMIT 5;
+--------------+-----------------+-------------------+------------+
| Song Title | Artiste Name | Music Genre | Producers |
+--------------+-----------------+-------------------+------------+
| Silhouette | Avicii | Progressive House | Avicii |
| Levels | Avicii | Progressive House | Avicii |
| Jailer | Asa | Indie pop | Cobhams |
| Higher Steps | Beautiful Nubia | Folk and Root | Eni Obanke |
| Tables Turn | Beautiful Nubia | Folk and Root | Eni Obanke |
+--------------+-----------------+-------------------+------------+
The SQL statement might be confusing, but here is how it works:
In the SELECT statement, we are connecting together three tables, we are not doing anything special, I simply state:
SELECT cmusic.title and give it a new heading of 'Song Title'. The first two selection is for the main cmusic table, which is why you have cmusic.table and cmusic.artiste, the 2 next selection is for the cmusic_genre table and cmusic_producers table.
Just specifying the SELECT statement without the FROM clause doesn't make much sense, by using the FROM, we are telling mariadb where the Table we want to SELECT from are located.
The WHERE clause tell mariadb to join the cmusic table to the cmusic_genre table where the genre_id from both tables are equal or matches. Using AND, we then give another condition in the WHERE clause, this tells mariadb to join the cmusic table to the cmusic_genre table where the producer_id from both tables are equal or matches.
Lastly, we use the LIMIT 5 to limit the row to 5
SELECT Similar Data in a Table (Plus Regular Expression)
To select a similar data in a table, we can use the LIKE operator like so:
SELECT music_id, artiste, title, genre_id, producer_id FROM cmusic WHERE artiste LIKE 'Beautiful%';
+----------+-----------------+--------------+----------+-------------+
| music_id | artiste | title | genre_id | producer_id |
+----------+-----------------+--------------+----------+-------------+
| 2 | Beautiful Nubia | Higher Steps | 3 | 3 |
| 3 | Beautiful Nubia | Tables Turn | 3 | 3 |
+----------+-----------------+--------------+----------+-------------+
MariaDB selected rows in which the artitse column starts with "Beautiful", and ends with anything (wildcard %).
To selected multiple values using LIKE operator you can combine it with an OR operator like so:
SELECT music_id, artiste, title, genre_id, producer_id FROM cmusic WHERE artiste LIKE 'Beautiful%' OR artiste LIKE 'Avi%';
+----------+-----------------+--------------+----------+-------------+
| 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 |
| 5 | Avicii | Levels | 2 | 1 |
+----------+-----------------+--------------+----------+-------------+
A faster of doing this is using regular expression, here is an alternative:
SELECT music_id, artiste, title, genre_id, producer_id FROM cmusic WHERE artiste REGEXP 'Beautiful*|Avi*';
+----------+-----------------+--------------+----------+-------------+
| 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 |
| 5 | Avicii | Levels | 2 | 1 |
+----------+-----------------+--------------+----------+-------------+
We are using regular expression here, and instead of using % for the wildcard, we use an asterisk. The vertical bar (i.e., |) between the two expressions signifies that either value is acceptable, it means OR.
To do the opposite, I mean if you don't want to return Anything that starts with Beautiful or Avi, you can do:
SELECT music_id, artiste, title, genre_id, producer_id FROM cmusic WHERE artiste NOT REGEXP 'Beautiful*|Avi*';
+----------+---------+-------------------------+----------+-------------+
| music_id | artiste | title | genre_id | producer_id |
+----------+---------+-------------------------+----------+-------------+
| 4 | Fela | Observation Is No Crime | 4 | 4 |
| 6 | Fela | Colonial Mentality | 4 | 4 |
| 7 | Asa | Jailer | 1 | 2 |
+----------+---------+-------------------------+----------+-------------+
Using NOT REGEXP eliminated any that starts with Beautiful* or Avi*
Counting and Grouping Results
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 |
+--------------------+
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 |
+--------------+--------------------------------+
We can also 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.