So far, we've covered a couple of topics on mariadb, if you want to follow the progress, here are the guides:
- Introduction To MariaDB
- [MariaDB] Guide To Creating Databases and Tables
- Altering MariaDB Tables
- Indexes In MariaDB
After creating a table, the next step is inserting data into the table, we use the INSERT statement.
INSERT Syntax
The INSERT statement adds rows of data into a table. It can add a single row or multiple rows at a time. It has the following syntax:
INSERT INTO table_name tbl_name [(column_name,...)]
VALUES (value, ...), (...), (...), ...,The INSERT keyword is followed by the name of the table and an optional list of columns in parentheses. To insert a value, you use the keyword VALUES and a pair of parentheses containing a list of values for each column.
Note: The square brackets in a mariadb syntax indicate that the bracketed items are optional.
INSERT Data Without Specifying Columns
The following examples add data to a table called cmusic assuming the table columns are title, artiste, and year:
INSERT INTO cmusic
VALUES('Silhouette', 'Avicii', '2013');As you can see, we aren't specifying the columns in the table, this is because it maps it in the order assumed column order. There are three columns, so, we have to specify three values, which will go into the columns in the order that the columns were defined when you created the cmusic table.
For example, "Silhouette" will be inserted into the first column of the table (title), "Avicii" will be inserted into the second column of the table (artiste), and 2013 will go into the year column,
INSERT Data With Default Without Specifying Columns
For columns that have a default value set, you can rely on the server to use that value and omit the column from your INSERT statement.
Another way you can make use of the default value is by entering DEFAULT or NULL, e.g:
INSERT INTO cmusic
VALUES('Silhouette', 'Avicii', DEFAULT);MariaDB will use the default value for the third column. For a column defined with AUTO_INCREMENT, the server will put the next number in the sequence for that column.
INSERT Data By Specifying Columns
You can also explicitly specify the column you wish the data to go into:
INSERT INTO cmusic
(title, artiste, year)
VALUES('Silhouette', 'Avicii', '2013');Note that the list of values must match the order of the list of columns, you can list the columns in any order you like, just make sure it matches the order of the list of columns.
If you have any default column, it would take of that for you by inserting the default value you specified at the time of table creation.
INSERT Multiple Data
If you have many rows of data to insert into the same table, you can supply them by putting them in parenthesis with a comma, for example:
INSERT INTO cmusic
(title, artiste)
VALUES
('Silhouette', 'Avicii'),
('Higher Steps', 'Beautiful Nubia'),
('Tables Turn', 'Beautiful Nubia'),
('Observation Is No Crime', 'Fela'),
('Levels', 'Avicii'),
('Colonial Mentality', 'Fela'),
('Jailer', 'Asa');The above SQL statement enters two rows of data into the cmusic table.
Simple Example of Using INSERT (MariaDB)
Let’s create a simple table that would hold user information, we would name the table cmusic_users.
CREATE TABLE cmusic_users
(user_id INT AUTO_INCREMENT PRIMARY KEY,
name_first VARCHAR(40),
name_last VARCHAR(40),
email_address VARCHAR(150));
The following adds 5 people to the cmusic_users table:
INSERT INTO cmusic_users
(name_first, name_last, email_address)
VALUES
('Blaise', 'Pascal', 'blaise@melodic.com'),
('Charles', 'Dawton', 'charles@melodic.com'),
('Jams', 'Petr', 'james@melodic.com'),
('Devsrealm', 'Guy', 'devsrealm@melodic.com'),
('Nubia', 'Putin', 'nubia@melodic.com');
To check the data we just INSERTED, do the following:
SELECT * FROM cmusic_users;
+---------+------------+-----------+-----------------------+
| user_id | name_first | name_last | email_address |
+---------+------------+-----------+-----------------------+
| 1 | Blaise | Pascal | blaise@melodic.com |
| 2 | Charles | Dawton | charles@melodic.com |
| 3 | Jams | Petr | james@melodic.com |
| 4 | Devsrealm | Guy | devsrealm@melodic.com |
| 5 | Nubia | Putin | nubia@melodic.com |
+---------+------------+-----------+-----------------------+
If you look at user_id 3, you’ll see I made a mistake with the name_first and the name_last, it should be James not Jams, and Peter not Petr. I did this intentionally, mistakes are bound to happen, so, if you find yourself in a similar scenario, how do you fix this?
The row we need to fix is row 3, let’s delete it:
DELETE FROM cmusic_users
WHERE name_first = 'Jams'
AND name_last = 'Petr';
This would delete the row, but isn’t this too long? You can leverage the user_id for quick deletion:
DELETE FROM cmusic_users WHERE user_id = 3;
This is better and much faster.
Let’s correct row 3 entry:
INSERT INTO cmusic_users (user_id, name_first, name_last) VALUES(3, 'James', 'Peter');
If I didn’t specify I want the data in row 3, it would dump it in row 6, this is because we have the user_id column set as AUTO_INCREMENT.
Confirm the changes:
SELECT * FROM cmusic_users;
+---------+------------+-----------+-----------------------+
| user_id | name_first | name_last | email_address |
+---------+------------+-----------+-----------------------+
| 1 | Blaise | Pascal | blaise@melodic.com |
| 2 | Charles | Dawton | charles@melodic.com |
| 3 | James | Peter | NULL |
| 4 | Devsrealm | Guy | devsrealm@melodic.com |
| 5 | Nubia | Putin | nubia@melodic.com |
+---------+------------+-----------+-----------------------+
All good now.
Here is a fancy tweak, if you want to order a particular column alphabetically, you can do:
SELECT user_id, email_address FROM cmusic_users ORDER BY email_address;
+---------+-----------------------+
| user_id | email_address |
+---------+-----------------------+
| 3 | NULL |
| 1 | blaise@melodic.com |
| 2 | charles@melodic.com |
| 4 | devsrealm@melodic.com |
| 5 | nubia@melodic.com |
+---------+-----------------------+
Why did it return NULL first? Well, I made a mistake when we deleted row 3, we didn’t insert the email address of the user when we were re-inserting the data. Again, I intentionally did this, you learn quickly if you make mistakes and correct your mistake. To fix this you don’t need to delete the entire column, we just need to insert the email address to that particular column, you can do that the following way:
UPDATE cmusic_users SET email_address = 'james@melodic.com' WHERE user_id = 3;
Now, run the select statement, and you’ll see everything is in place:
SELECT * FROM cmusic_users;
+---------+------------+-----------+-----------------------+
| user_id | name_first | name_last | email_address |
+---------+------------+-----------+-----------------------+
| 1 | Blaise | Pascal | blaise@melodic.com |
| 2 | Charles | Dawton | charles@melodic.com |
| 3 | James | Peter | james@melodic.com |
| 4 | Devsrealm | Guy | devsrealm@melodic.com |
| 5 | Nubia | Putin | nubia@melodic.com |
+---------+------------+-----------+-----------------------+
Practical Example of Using INSERT (MariaDB)
Our practical example would involve building a database of a few classical music. Instead of creating multiple columns, we would create multiple tables which would help us build a better structure, we would then reference the table if there is a need to, you'll see this in action in a moment.
The following would be the table we would be creating:
- cmusic - The main table
- cmusic_genre - The genre table
- cmusic_producers - The producers table
These would be the structure of the main cmusic table:
- music_id - This would be the unique index of the song
- status - Would store a boolean value, 1 if the song is available, and zero(0) if the song is not available
- mood - The moods of the song using an ENUM data type
- artiste
- title
- genre_id - This would be used to join with the cmusic_genre table
- producer_id - This would be used to join with the cmusic_producers table
We would start with creating the cmusic_genre table then we move to the cmusic_producers table then lastly to the main cmusic table.
Creating the cmusic_genre table
Let's create a table that would hold the actual genre, if we like we can create in such a way that it will hold more information about the genre of music, so, we would tie it into the genre_id column in the cmusic table, this would make things a bit more clearer.
CREATE TABLE cmusic_genre (
genre_id INT AUTO_INCREMENT PRIMARY KEY,
genre_name VARCHAR (150),
genre_description VARCHAR(255) );I am creating THREE columns, the first one would be indexed and will be referenced by the cmusic table.
To see the structure of the cmusic_genre table, you run:
DESCRIBE cmusic_genre;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| genre_id | int(11) | NO | PRI | NULL | auto_increment |
| genre_name | varchar(150) | YES | | NULL | |
| genre_description | varchar(255) | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
This table has only three columns: an identification number (genre_id) that will be used by the cmusic table to join to this table, a column for the music genre, and a column for the genre description.
Before entering data into the genre table, you can change the AUTO_INCREMENT variable to whatever integer you want, the default is 1, and we would stick to 1.
If you want to change it, you can do it the following way:
ALTER TABLE cmusic_genre
AUTO_INCREMENT = 100;This will set the first genre_id for the first data you enter in the cmusic_genre table to 100, change it back to 1 as that is what I'll be using, but it doesn't really matter which you use.
The next SQL statement INSERT a couple of genres and a description:
INSERT INTO cmusic_genre (genre_name, genre_description)
VALUES
('Indie pop', "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."),
('Progressive House', "Progressive house is a subgenre of house music. It focuses mostly on harmonies"),
('Folk and Root', "Built on rich folkloric traditions and native wisdom."),
('Afrobeat', "A music genre which involves the combination of elements of West African musical styles such as fuji music, Yoruba, and highlife.");We INSERTED two of the three-column, you don't need to specify the genre_id, mariadb would sort that out for us.
Make sure you always cross-check the order at which you are entering the data otherwise, you might be entering data into the wrong column. To check the data we just INSERTED, do the following:
SELECT * FROM cmusic_genre \G
*************************** 1. row ***************************
genre_id: 1
genre_name: Indie pop
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.
*************************** 2. row ***************************
genre_id: 2
genre_name: Progressive House
genre_description: Progressive house is a subgenre of house music. It focuses mostly on harmonies
*************************** 3. row ***************************
genre_id: 3
genre_name: Folk and Root
genre_description: Built on rich folkloric traditions and native wisdom.
*************************** 4. row ***************************
genre_id: 4
genre_name: Afrobeat
genre_description: A music genre which involves the combination of elements of West African musical styles such as fuji music, Yoruba, and highlife.
The reason why I am using \G instead of a semicolon is that the data won't display properly if you have a wide data set.
Creating the cmusic_producers table
Let's create another table that would hold information on music producers that created the songs in the cmusic table:
CREATE TABLE cmusic_producers
(producer_id INT AUTO_INCREMENT PRIMARY KEY,
producer_name VARCHAR(40),
producer_description VARCHAR(250));The following adds a list of producers to the cmusic_producers table:
INSERT INTO cmusic_producers
(producer_name)
VALUES
('Avicii'),
('Cobhams'),
('Eni Obanke'),
("Felas' Band");To check the data we just INSERTED, do the following:
SELECT * FROM cmusic_producers;
+-------------+---------------+----------------------+
| producer_id | producer_name | producer_description |
+-------------+---------------+----------------------+
| 1 | Avicii | NULL |
| 2 | Cobhams | NULL |
| 3 | Eni Obanke | NULL |
| 4 | Felas' Band | NULL |
+-------------+---------------+----------------------+
I think it is time to create the main cmusic table, let's create the table structure:
CREATE TABLE cmusic (
music_id INT AUTO_INCREMENT PRIMARY KEY,
status tinyint(1) DEFAULT '1',
mood enum('Dance','Sad','Energetic'),
artiste varchar(255),
title varchar(255),
genre_id INT,
producers_id INT,
description text,
writer varchar(150),
producer varchar(100)
);Let's look at the columns in the cmusic table, You go can do that using the SHOW COLUMNS statement:
SHOW COLUMNS FROM cmusic;
+-------------+---------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| status | tinyint(1) | YES | | 1 | |
| mood | enum('Dance','Sad','Energetic') | YES | | NULL | |
| artiste | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| genre_id | int(11) | YES | | NULL | |
| user_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
| writer | varchar(150) | YES | | NULL | |
| producer | varchar(100) | YES | | NULL | |
+-------------+---------------------------------+------+-----+---------+----------------+
Alternatively, you can return the result using the DESCRIBE statement. The difference is that the SHOW COLUMNS gives us more flexibility on how the columns are returned, you can retrieve a list of columns based on different pattern. Suppose you just want a list of reference columns, the columns we labeled with the ending, _id. You could enter:
SHOW COLUMNS FROM cmusic LIKE '%id';
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| genre_id | int(11) | YES | | NULL | |
| user_id | int(11) | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
The percentage sign (%) represent a wildcard.
Let's add data to the cmusic table:
INSERT INTO cmusic (title, artiste)
VALUES
('Silhouette', 'Avicii'),
('Higher Steps', 'Beautiful Nubia'),
('Tables Turn', 'Beautiful Nubia'),
('Observation Is No Crime', 'Fela'),
('Levels', 'Avicii'),
('Colonial Mentality', 'Fela'),
('Jailer', 'Asa');I guess you know we can enter more data into other columns, you can do that later if you like, what I want to show you is how you can connect the other tables with the main table (cmusic), and retrieve data from them.
Before we can do that, let's see the data in the cmusic_genre table:
SELECT * FROM cmusic_genre \G
*************************** 1. row ***************************
genre_id: 1
genre_name: Indie pop
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.
*************************** 2. row ***************************
genre_id: 2
genre_name: Progressive House
genre_description: Progressive house is a subgenre of house music. It focuses mostly on harmonies
*************************** 3. row ***************************
genre_id: 3
genre_name: Folk and Root
genre_description: Built on rich folkloric traditions and native wisdom.
*************************** 4. row ***************************
genre_id: 4
genre_name: Afrobeat
genre_description: A music genre which involves the combination of elements of West African musical styles such as fuji music, Yoruba, and highlife.
As you can see we have 4 genre of music with their distinct identification number, for better correlation with the cmusic table, we need to map the song to the genre, meaning, all "Beautiful Nubia" songs would have genre_id 3 in the cmusic table, all "Avicii" songs would have genre_id 2 in the cmusic table, etc.
For Beautiful Nubia, we do:
UPDATE cmusic
SET genre_id = 3
WHERE artiste = 'Beautiful Nubia';This would set all "Beautiful Nubia" genre_id to 3, let's confirm:
SELECT music_id, artiste, title, genre_id FROM cmusic;
+----------+-----------------+-------------------------+----------+
| music_id | artiste | title | genre_id |
+----------+-----------------+-------------------------+----------+
| 1 | Avicii | Silhouette | NULL |
| 2 | Beautiful Nubia | Higher Steps | 3 |
| 3 | Beautiful Nubia | Tables Turn | 3 |
| 4 | Fela | Observation Is No Crime | NULL |
| 5 | Avicii | Levels | NULL |
| 6 | Fela | Colonial Mentality | NULL |
| 7 | Asa | Jailer | NULL |
+----------+-----------------+-------------------------+----------+
If we have tons of data, it would be tedious to update everything manually, so, we can use a case statement to speed things up, to Update Fela, Asa, and Avicii to their respective genre_id, we can use a case statement like so:
UPDATE cmusic
SET genre_id= CASE
WHEN artiste = 'Fela' THEN '4'
WHEN artiste = 'Avicii' THEN '2'
WHEN artiste = 'Asa' THEN '1'
else genre_id
END;Haha, you might feel overwhelmed, but trust me, it isn't that hard to understand. What we are doing above is using a CASE statement to match multiple rows at the same time. We are simply saying:
SET genre_id = CASE, this means we want to use a CASE control structure, and we then followed it with whatever we want to do.
In this case, we said, When artiste is equals to 'FELA' could you please set genre_id to 4, when artiste is equals to Avicii, set the genre_id to 2, when artiste is Asa, set the genre_id to 1, lastly, we use else genre_id, using the else statement would leave the other genre_id field as is, if you fail to include the else statement, the rest of the genre_id that isn't include in the CASE construct would be updated to NULL.
We can use the same idea to update the producer_id, but let's see the cmusic_producers table to know what and what should be updated:
SELECT * FROM cmusic_producers;
+-------------+---------------+----------------------+
| producer_id | producer_name | producer_description |
+-------------+---------------+----------------------+
| 1 | Avicii | NULL |
| 2 | Cobhams | NULL |
| 3 | Eni Obanke | NULL |
| 4 | Felas' Band | NULL |
+-------------+---------------+----------------------+
Using the UPDATE statement together with a case control structure to update the producer_id in the cmusic table:
UPDATE cmusic
SET producer_id= CASE
WHEN artiste = 'Fela' THEN '4'
WHEN artiste = 'Avicii' THEN '1'
WHEN artiste = 'Asa' THEN '2'
WHEN artiste = 'Beautiful Nubia' THEN '3'
else producer_id
END;We are simply saying:
When artiste is equals to 'FELA' set producer_id to 4, when artiste is equals to Avicii, set the producer_id to 1, when artiste is Asa, set the producer_id to 2, when artiste is Beautiful Nubia, set the producer_id to 3, lastly, we use else producer_id, using the else statement would leave the other producer_id field as is, if you fail to include the else statement, the rest of the producer_id that isn't include in the CASE construct would be updated to NULL.
Let's confirm the changes:
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 |
+----------+-----------------+-------------------------+----------+-------------+
We've entered a couple of data in different tables, and the smaller tables; cmusic_genre and cmusic_producers 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;Output
+-------------------------+-----------------+-------------------+-------------+
| 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 |
| Observation Is No Crime | Fela | Afrobeat | Felas' Band |
| Colonial Mentality | Fela | Afrobeat | Felas' Band |
+-------------------------+-----------------+-------------------+-------------+
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.
If this is complicated, read it twice or more times, and you would get the gist, hope this helps.
Inserting Data from Another Table
I'll round up this guide by showing you a way you can insert data from another table. Suppose you have an old table that contains lots of columns, and you need a way to import a single column of data from the old table into a separate table, you can achieve the proposed scenario by first creating a new column for the old data you want to import in the new table.
E.g let's assume it's an email_address, so, create a new column for that in the new table:
ALTER TABLE new_table ADD COLUMN email_address VARCHAR(255);
Now, to copy the data from the old table into this column, you do:
INSERT IGNORE INTO new_table
(email_address)
SELECT email_address
FROM old_table;