In our last guide, we dive deep into creating databases and tables. In this guide, we would discuss altering and manipulating the data, well, you can get the full structure of your table in a go, so, you will need to occasionally change the structure or other aspects of your table.
I create a couple of tables in the last guide, that is what I would be using, so, you might want to take a sneak peek in the guide I linked above, this way, we can be on the same page.
ALTER TABLE Syntax (MariaDB)
The syntax for altering a table is as follows:
ALTER TABLE table_name changes;Replace table_namewith the name of the table you want to change followed by the changes you want to make.
Adding Columns To An Existing Table (MariaDB)
Now, I'll add a couple of columns to the cmusic table (producer and writer), but before we do that, it is a good practice to make a copy of the table and alter the copy instead of the original. When we’re finished we’ll use the table we altered to replace the original table.
Enter the following to make a copy of an existing table:
CREATE TABLE test.cmusic_new LIKE cmusic;The test is our test database, let us avoid messing with a production database, if you don't have the test database, it would return: ERROR 1049 (42000): Unknown database 'test'
Fix that using:
CREATE DATABASE test;
CREATE TABLE test.cmusic_new LIKE cmusic;
To check the new copy, use:
DESCRIBE test.cmusic_new;
MariaDB [marn_melodic]> DESCRIBE cmusic_new;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| artiste | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| genre_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
This would show you the structure of the new table, and it won't even copy the data to the new table, to copy the data, you do:
INSERT INTO test.cmusic_new
SELECT * FROM marn_melodic.cmusic;This would copy all the cmusic table from the marn_melodic database into the cmusic_new table in the test database.
To avoid appending the database name for every query we make to the test database, switch to the test database using:
USE test;Let’s now alter the new table and add a column named producer and writer. To add the column, enter the following SQL statement in your mariadb client:
ALTER TABLE cmusic_new
ADD COLUMN producer VARCHAR(100),
ADD COLUMN writer VARCHAR(150);This will add two column (producer and writer), if you omit the ADD COLUMNstatement, it won't add the column, if you are finding it add to understand the syntax, look at it this way:
Hey MariaDB, could you please ALTER TABLE
cmusic_newand ADD COLUMNproducerwith a data type of VARCHAR and a maximum length of 100, also ADD COLUMNwriterwith a data type of VARCHAR and a maximum length of 150.
I find it easier to remember stuff like this if I can translate it to the way I speak.
Now, let's look at the structure of the cmusic_new table to see how it looks now:
DESCRIBE cmusic_new;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| artiste | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| genre_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
| producer | varchar(100) | YES | | NULL | |
| writer | varchar(150) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
Drop a Column (MariaDB)
As you can see, we update the columns with producer and writer, if you want to DROP a column, you can do:
ALTER TABLE cmusic_new
DROP COLUMN writer;This would drop the writer column plus all the data associated with that column, this won't hurt us since we haven't added any data in that column.
Drop Multiple Columns (MariaDB)
To drop multiple columns you can do:
ALTER TABLE simpleapp.sa_seo_tool
DROP COLUMN col_name1,
DROP COLUMN col_name2,
DROP COLUMN col_name3;The word COLUMN is optional and can be omitted, except for RENAME COLUMN (to distinguish a column-renaming operation from the RENAME table-renaming operation).
Add a Column After Another Column (MariaDB)
Let's re-add the writer column, but this time, we would put it AFTER the description column, you can do this using:
ALTER TABLE cmusic_new
ADD COLUMN writer VARCHAR(150) AFTER description;Confirm the changes by running:
DESCRIBE cmusic_new;+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| artiste | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| genre_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
| writer | varchar(150) | YES | | NULL | |
| producer | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
To add a column to the first position, you would use the keyword FIRST instead of AFTER, and the FIRST keyword takes no column name.
Add a Column With a Default Value (MariaDB)
To make this a bit more interesting, let's add a column status, this would indicate whether a music is available or not, we do this using:
ALTER TABLE cmusic_new
ADD COLUMN status BIT DEFAULT b'1' AFTER music_id;We’re using a BIT data type, and this stores one bit. It takes a value of either set or unset; 1 or 0. We specified a default value for this column with the DEFAULT keyword followed by the default value, and you should also put b in front of the value in quote.
One issue we would encounter when trying to print data in the column is that the bit value won't show, and this is because bit values are non printable characters, but you'll think the dev at mariadb should handle this somehow, well, that is not the case.
As this might cause issue down the line, we would remove it and use a BOOLEAN data type instead, I only show you the bit data type in case you come across it. When designing a table in MariaDB, you should consider the available data types so that you can select the most optimal ones for storing data, for the status column, the boolean data type isn't bad.
Note: In MariaDB, zero (0) means false and non-zero means true. The BOOLEAN and BOOL are the same as TINYINT(1), so, you can use them interchangeably.
Let's drop the status column:
ALTER TABLE cmusic_new
DROP COLUMN status;Recreate the status column with a boolean data type and a default value of 1 (which mean TRUE):
ALTER TABLE cmusic_new
ADD COLUMN status BOOLEAN DEFAULT 1 AFTER music_id;This would add a new column (status) with a default value of 1, meaning all the songs are available, to show the data in the table, you can do:
SELECT * FROM cmusic_new;
+----------+--------+-----------------+-------------------------+----------+-------------+--------+----------+
| music_id | status | artiste | title | genre_id | description | writer | producer |
+----------+--------+-----------------+-------------------------+----------+-------------+--------+----------+
| 1 | 1 | Avicii | Silhouette | NULL | NULL | NULL | NULL |
| 2 | 1 | Beautiful Nubia | Higher Steps | NULL | NULL | NULL | NULL |
| 3 | 1 | Beautiful Nubia | Tables Turn | NULL | NULL | NULL | NULL |
| 4 | 1 | Fela | Observation Is No Crime | NULL | NULL | NULL | NULL |
| 5 | 1 | Avicii | Levels | NULL | NULL | NULL | NULL |
| 6 | 1 | Fela | Colonial Mentality | NULL | NULL | NULL | NULL |
| 7 | 1 | Asa | Jailer | NULL | NULL | NULL | NULL |
+----------+--------+-----------------+-------------------------+----------+-------------+--------+----------+
Change The Value of a Column
Just for learning purpose, let's change the value of the status column for some of songs. To do this, we use the UPDATE statement:
UPDATE cmusic_new SET status = 0
WHERE music_id IN(1,3,4,6);This will unset the value for the rows in which the music_id column has one of the values listed within the parentheses, this is a good reason for having an id, if we hadn't created the id in the last guide, we would have to be updating the song by their names.
Check which values has been updated using the SELECT statement:
SELECT * FROM cmusic_new;
+----------+--------+-----------------+-------------------------+----------+-------------+--------+----------+
| music_id | status | artiste | title | genre_id | description | writer | producer |
+----------+--------+-----------------+-------------------------+----------+-------------+--------+----------+
| 1 | 0 | Avicii | Silhouette | NULL | NULL | NULL | NULL |
| 2 | 1 | Beautiful Nubia | Higher Steps | NULL | NULL | NULL | NULL |
| 3 | 0 | Beautiful Nubia | Tables Turn | NULL | NULL | NULL | NULL |
| 4 | 0 | Fela | Observation Is No Crime | NULL | NULL | NULL | NULL |
| 5 | 1 | Avicii | Levels | NULL | NULL | NULL | NULL |
| 6 | 0 | Fela | Colonial Mentality | NULL | NULL | NULL | NULL |
| 7 | 1 | Asa | Jailer | NULL | NULL | NULL | NULL |
+----------+--------+-----------------+-------------------------+----------+-------------+--------+----------+
Using SELECT Statement Based on a Certain Condition (MariaDB)
Beautiful, we can make this even interesting by retrieving data using the SELECT statements based on whether a song is available or not, remember that zero (0) means false and 1 means TRUE. To check if a song is available, we do:
SELECT * FROM cmusic_new WHERE status = 1;
+----------+--------+-----------------+--------------+----------+-------------+--------+----------+
| music_id | status | artiste | title | genre_id | description | writer | producer |
+----------+--------+-----------------+--------------+----------+-------------+--------+----------+
| 2 | 1 | Beautiful Nubia | Higher Steps | NULL | NULL | NULL | NULL |
| 5 | 1 | Avicii | Levels | NULL | NULL | NULL | NULL |
| 7 | 1 | Asa | Jailer | NULL | NULL | NULL | NULL |
+----------+--------+-----------------+--------------+----------+-------------+--------+----------+
Or we can retrieve a selected column using:
SELECT music_id, artiste, title FROM cmusic_new WHERE status = 1;
+----------+-----------------+--------------+
| music_id | artiste | title |
+----------+-----------------+--------------+
| 2 | Beautiful Nubia | Higher Steps |
| 5 | Avicii | Levels |
| 7 | Asa | Jailer |
+----------+-----------------+--------------+
Cool isn't it. To retrieve songs that are not available, you can do:
SELECT music_id, artiste, title FROM cmusic_new WHERE NOT status = 1;
+----------+-----------------+-------------------------+
| music_id | artiste | title |
+----------+-----------------+-------------------------+
| 1 | Avicii | Silhouette |
| 3 | Beautiful Nubia | Tables Turn |
| 4 | Fela | Observation Is No Crime |
| 6 | Fela | Colonial Mentality |
+----------+-----------------+-------------------------+
Instead of adding WHERE status = 1you can do:
WHERE status
and instead of doing WHERE NOT status = 1you can do:
WHERE NOT status
Just an heads up, you can do whatever you want.
Understanding and The Usage of ENUM Data Type
Let's make things a bit fancier by creating a mood column, we would make this a bit different by using ENUM (enumeration) data type:
ALTER TABLE cmusic_new
ADD COLUMN mood ENUM('Dance', 'Sad', 'Energetic')
AFTER status;ENUM is a string object that can have only one value, chosen from the list of values 'value1', 'value2', ...,
When using Enumeration, you have the following advantages:
- Compact data storage. MySQL ENUM uses numeric indexes (1, 2, 3, …) to represents string values.
- The specified strings as input values are mapped or encoded as numbers.
- Readable queries and output.
The ENUM column or the mood column we have just created will accept only three values Dance, Sad and Energetic. MariaDB would map enumeration item to a numeric index (like an array). Dance, Sad, and Energetic are mapped to 1, 2, and 3 respectively.
Note that we added it after the status column.
Think of this as a way of sorting our music collection by their mood.
Let's take a look at our table structure:
DESCRIBE cmusic_new;
+-------------+---------------------------------+------+-----+---------+----------------+
| 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 | |
| description | text | YES | | NULL | |
| writer | varchar(150) | YES | | NULL | |
| producer | varchar(100) | YES | | NULL | |
+-------------+---------------------------------+------+-----+---------+----------------+
As you can see in the third column, the mood plus the enum type has been added.
Let's insert data into the mood(enum) column:
UPDATE cmusic_new
SET mood = 1;Now, check the data in the table using:
SELECT music_id, status, mood, artiste, title FROM cmusic_new;
+----------+--------+-------+-----------------+-------------------------+
| music_id | status | mood | artiste | title |
+----------+--------+-------+-----------------+-------------------------+
| 1 | 0 | Dance | Avicii | Silhouette |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps |
| 3 | 0 | Dance | Beautiful Nubia | Tables Turn |
| 4 | 0 | Dance | Fela | Observation Is No Crime |
| 5 | 1 | Dance | Avicii | Levels |
| 6 | 0 | Dance | Fela | Colonial Mentality |
| 7 | 1 | Dance | Asa | Jailer |
+----------+--------+-------+-----------------+-------------------------+
You get the idea now right? ;) To set different a different enum item for different songs, we do:
UPDATE cmusic_new SET mood = 2 WHERE music_id IN(7,3);
+----------+--------+-------+-----------------+-------------------------+
| music_id | status | mood | artiste | title |
+----------+--------+-------+-----------------+-------------------------+
| 1 | 0 | Dance | Avicii | Silhouette |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps |
| 3 | 0 | Sad | Beautiful Nubia | Tables Turn |
| 4 | 0 | Dance | Fela | Observation Is No Crime |
| 5 | 1 | Dance | Avicii | Levels |
| 6 | 0 | Dance | Fela | Colonial Mentality |
| 7 | 1 | Sad | Asa | Jailer |
+----------+--------+-------+-----------------+-------------------------+
As you can see, it isn't that difficult after all, this is just a way of structuring the data in an efficient manner.
Rename a Column In MariaDB
Before we proceed, you can change a column name the following way:
ALTER TABLE cmusic_new
CHANGE COLUMN old_column_name new_column_name VARCHAR(255);We use the CHANG COLUMN statement plus the name of the old column with the new column name you want to change it to. After the column names, you must give the data type. Even if you
were using the CHANGE COLUMN statement to change only the name of the column, you must give the data type again.
Note: You must supply the same column name twice even if you are not changing the column name or else it won't work.
In addition to just adding and renaming columns, you can use the ALTER TABLE statement to set the value of table variables, as well as the default value of columns (we've done this before). Also, you can use it to rename tables, and even change indexes in a table.
Setting a Column’s Default Value For an Existing Column (MariaDB)
You may have noticed that the results of the DESCRIBE statement shown in earlier examples have a heading called Default. We added a default value of 1 when we added the status column, and we changed it a couple of times down the line.
The default value can be useful when the user does not enter a value for a column. For example, we can alter the value of an existing column by using the ALTER TABLE statement to specify a default value other than null.
Note that it won’t change the values of existing rows, which is self-explanatory as we are only changing the default value of newly added rows if they don't already have a value.
To change the default value without actually changing the value of the existing row, you do:
ALTER TABLE cmusic_new
ALTER status SET DEFAULT 0;Now, if you enter the DESCRIBE statement, you'll see the default value would have been set to a default of zero (0):
MariaDB [test]> DESCRIBE cmusic_new;
+-------------+---------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| status | tinyint(1) | YES | | 0 | |
| mood | enum('Dance','Sad','Energetic') | YES | | NULL | |
| artiste | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| genre_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
| writer | varchar(150) | YES | | NULL | |
| producer | varchar(100) | YES | | NULL | |
+-------------+---------------------------------+------+-----+---------+----------------+
Again, this won't change the value of the existing row, to reset it a default to null:
ALTER TABLE cmusic_new
ALTER status DROP DEFAULT;This would alter the column settings so there is no default value:
SHOW COLUMNS FROM cmusic_new LIKE 'status';
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| status | tinyint(1) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
This new SQL statement would only return the value of one column field instead of returning the whole column, in this case, we are interested in the status column.
Go ahead and change it back to a default of 1.
Another way of setting the default is:
ALTER TABLE cmusic_new CHANGE COLUMN status status BOOLEAN DEFAULT 1;
Notice that the column name status is specified twice. Although the column name isn't being changed, it still must be respecified. This is preferable if you want to change the column name as well as the default.
Setting the Value of AUTO_INCREMENT
Many of the main tables in a database will have a primary key that uses the AUTO_INCREMENT option, an example is our music_id. The AUTO_INCREMENT variable is created in the table called tables in the information_schema database.
This table is part of the default table created for us by mariadb, One of the columns of that table is called auto_increment, you'll find the value of the next row to be created in a table. This is set to 1 by default unless you set it when creating the table.
To get the value from the information_schema database, from the tables table, you run:
SELECT auto_increment FROM information_schema.tables WHERE table_name = 'cmusic';
+----------------+
| auto_increment |
+----------------+
| 8 |
+----------------+
As you can see this has a value of 8, which means the next row we add to the table will have 8 in the column.
If you would like to change the value of AUTO_INCREMENT for a particular table, you can do so with the ALTER TABLE statement.
Let’s set the value of AUTO_INCREMENT for our actual cmusic
table to 100, just to see how to change it this way.
Switch the database back to marn_melodic, and run the following:
ALTER TABLE cmusic
AUTO_INCREMENT = 100;This will cause the music_id to be set to 100 for the next row of data that we enter into the cmusic table. Change it back to 8 as this isn't necessary but it is good to know you can manipulate it using the ALTER TABLE syntax:
ALTER TABLE cmusic
AUTO_INCREMENT = 8;Copying a Certain Column Into a New Table
Sometimes, it is feasible to copy just the columns you are working on from a large table instead of making a copy of the whole table, let's say you only want to work with the genre_id and the genre description, we can copy the data from the two columns in the cmusic data table into a new table entirely e.g genre_details.
We don't have a genre_description column in the actual table, so, let's create that:
ALTER TABLE cmusic ADD COLUMN genre_description VARCHAR(255) AFTER genre_id;
Let's check the columns we have in the cmusic table:
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| music_id | int(11) | NO | PRI | NULL | auto_increment |
| artiste | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| genre_id | int(11) | YES | | NULL | |
| genre_description | varchar(255) | YES | | NULL | |
| description | text | YES | | NULL | |
+-------------------+--------------+------+-----+---------+----------------+
Now, let's copy only the genre_id and the genre_description into a new table called genre_info:
CREATE TABLE genre_info
SELECT genre_id, genre_description
FROM cmusic;This creates the genre_info table with two columns, based on the same columns in the cmusic table. It also copies the data from the two columns in the birds table into the genre_info table.
Note that this table won't have an AUTO_INCREMENT nor a Primary Key, this is fine as we would manually set the value of the genre_id for each row we enter, there are better ways we can do it so as not to deviate away from the genre_id in the cmusic table, we would see this later.
For now, let's remove the genre_description from the actual table, but we would leave the genre_id so as to create a relationship with the genre_info table:
ALTER TABLE cmusic
DROP COLUMN genre_description;Renaming Table (MariaDB)
To rename a table, you can follow the below syntax:
RENAME TABLE old_table_name TO new_table_name;
The above SQL statement would rename the old_table_name to new_table_name, the syntax is very easy to understand. Before you run that SQL statement, make sure the new_table_name doesn't already exist in the database.
If it does, it won’t overwrite that table. Instead, you’ll get an error message and the table won’t be renamed.
The RENAME TABLE statement can also be used to move a table to another database. This can be useful when you have a table that you’ve created in one database, as we did in the test database, and now want to relocate it to a different database or the actual database.
To see this in action, let's prefix all the table in the marn_melodic database with cmusic, here is the table we have so far in the marn_melodic database:
SHOW TABLES IN marn_melodic;
+------------------------+
| Tables_in_marn_melodic |
+------------------------+
| cmusic |
| genre_info |
| users |
+------------------------+
We would leave the first one as is, and rename the genre_info to cmusic_genre_info and the users to cmusic_users, this is to keep everything consistent, the following RENAME statement would do the changes:
RENAME TABLE genre_info To cmusic_genre_info,
users To cmusic_users;We are renaming two tables, and now, if we check at the structure, we would we have this:
SHOW TABLES IN marn_melodic;
+------------------------+
| Tables_in_marn_melodic |
+------------------------+
| cmusic |
| cmusic_genre_info |
| cmusic_users |
+------------------------+
I think we are on the same page.
If you recall we created a copy of the cmusic table into the test database, let's rename the cmusic table in the marn_melodic database to cmusic_old and then rename and relocate the cmusic_new table from the test database to cmusic in the marn_melodic database:
RENAME TABLE marn_melodic.cmusic TO marn_melodic.cmusic_old,
test.cmusic_new TO marn_melodic.cmusic;Run the SHOW TABLES statement to see the tables in the marn_melodic database:
SHOW TABLES IN marn_melodic LIKE 'cmusic%';
We are requesting table starting with the word cmusic by using the LIKE clause with the wildcard %, here is the output:
+------------------------+
| Tables_in_marn_melodic |
+------------------------+
| cmusic |
| cmusic_genre_info |
| cmusic_old |
| cmusic_users |
+------------------------+
The table from the test database has been renamed and moved to the marn_melodic database, and we also remove the actual cmusic table in the marn_melodic database to cmusic_old.
Before deleting the cmusic_old table, let's see the data in the cmusic we just copied over, just to sure everything is in place:
SELECT * FROM cmusic;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
| music_id | status | mood | artiste | title | genre_id | description | writer | producer |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
| 1 | 0 | Dance | Avicii | Silhouette | NULL | NULL | NULL | NULL |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps | NULL | NULL | NULL | NULL |
| 3 | 0 | Sad | Beautiful Nubia | Tables Turn | NULL | NULL | NULL | NULL |
| 4 | 0 | Dance | Fela | Observation Is No Crime | NULL | NULL | NULL | NULL |
| 5 | 1 | Dance | Avicii | Levels | NULL | NULL | NULL | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | NULL | NULL | NULL | NULL |
| 7 | 1 | Sad | Asa | Jailer | NULL | NULL | NULL | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
Alright, cool. Let's drop the cmusic_old table:
DROP TABLE cmusic_old;If you check the tables in the marn_melodic database, you'll see it has been dropped/deleted.
Limiting Retrieve Result With The Select Statement
To limit the result of data in a table you do:
SELECT * FROM cmusic LIMIT 5;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
| music_id | status | mood | artiste | title | genre_id | description | writer | producer |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
| 1 | 0 | Dance | Avicii | Silhouette | NULL | NULL | NULL | NULL |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps | NULL | NULL | NULL | NULL |
| 3 | 0 | Sad | Beautiful Nubia | Tables Turn | NULL | NULL | NULL | NULL |
| 4 | 0 | Dance | Fela | Observation Is No Crime | NULL | NULL | NULL | NULL |
| 5 | 1 | Dance | Avicii | Levels | NULL | NULL | NULL | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
This limits the rows of data to the first five rows, feel free to play with the Limit statement further.
Reordering a Table
The syntax for re-ordering a table in mariadb is as follows:
ALTER TABLE table_name
ORDER BY column;We won't be able to re-order the table because we have the index column in place, we can fix this by using the SELECT statement:
SELECT * FROM cmusic ORDER BY artiste;
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
| music_id | status | mood | artiste | title | genre_id | description | writer | producer |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
| 7 | 1 | Sad | Asa | Jailer | NULL | NULL | NULL | NULL |
| 1 | 0 | Dance | Avicii | Silhouette | NULL | NULL | NULL | NULL |
| 5 | 1 | Dance | Avicii | Levels | NULL | NULL | NULL | NULL |
| 2 | 1 | Dance | Beautiful Nubia | Higher Steps | NULL | NULL | NULL | NULL |
| 3 | 0 | Sad | Beautiful Nubia | Tables Turn | NULL | NULL | NULL | NULL |
| 4 | 0 | Dance | Fela | Observation Is No Crime | NULL | NULL | NULL | NULL |
| 6 | 0 | Dance | Fela | Colonial Mentality | NULL | NULL | NULL | NULL |
+----------+--------+-------+-----------------+-------------------------+----------+-------------+--------+----------+
This would rearrange the artiste column, and as you can see the music_id follows suit. Note that this re-ordering is only for viewing and it won't change the actual structure.
This would conclude out guide on altering table in mariadb. In future guide, we would discuss about indexes, plus the ability to alter an index.