Mariadb Create Table – How to Guide (Step by Step)
Mariadb Create Table – How to Guide (Step by Step). In this post we will introduce what MariaDB is and its main feature and how to create a table. MariaDB provides a create table statement to the user where we are able to create a table with the assigned name of the table. There are various ways to create a table and where we want to store the table will depend on the user. Let’s introduce MariaDB first!
What is MariaDB
MariaDB is a free, open source and one of the most popular database management system around the world. Its high performance, ease of use and data security makes it a popular database solution.
MariaDB server was primarily designed to ensure that the MySQL codebase was free for everyone. It is a fork of the MySQL database management system and offers data processing capabilities for both small and enterprise tasks.
MariaDB has a pluggable storage engine capabilities that allows developers to use a single database in a more flexible manner. You can concentrate on creating individual tables to suit a specific business need. Databases are containers for objects with a user defined structure. One type of object is a table.
MariaDB Table
MariaDB table is used to organize data in the form of rows and columns. It is similar to worksheets in the spreadsheet for storage and showing records in the structure format.
MariaDB Database consists of tables which are a containers with a logical structure in a row and column format. Each row represents a record while each column represents a field or property of that record.
Follow this post how in MariaDB to Create Table – How to Guide.
Basic syntax for Table in MariaDB
MariaDB allows us to create a table into the database by using the CREATE TABLE command. To create a table in MariaDB, you will require three things:
- Name of the table
- Names of fields
- Definitions for each field
The basic syntax for creating a table in MariaDB is shown below:
CREATE TABLE [IF NOT EXISTS] table_name(
column_definition1,
column_definition2,
........,
table_constraints
);
A brief explanation of each syntax is shown below:
- table_name: It is the name of the table. The table name should be unique.
- column_definition: Define the name of the column with data types for each column. The columns are separated by the comma operator.
- table_constraints: Define the PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK, etc.
- IF NOT EXISTS: If there is a same table in the database, the query will not be executed.
Create a Table in MariaDB
To create a table in MariaDB, first you will need to open a terminal interface and log in to the MariaDB shell. You can do it using the following command:
mysql -u root -p
You will be asked to provide a MariaDB root password. Once you are connected to the MariaDB, you will get the following shell:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 44
Server version: 10.5.12-MariaDB-0+deb11u1 Debian 11
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Next, create a database named books using the CREATE DATABASE statement:
MariaDB [(none)]> CREATE DATABASE books;
Please verify your database using the following command:
MariaDB [(none)]> SHOW DATABASES;
You should see your created database in the following output:
+--------------------+
| Database |
+--------------------+
| books |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
Next, switch the database to books and create a table named books using the CREATE TABLE statement:
MariaDB [(none)]> USE books;
MariaDB [books]> CREATE TABLE books(title VARCHAR(50) NOT NULL,author VARCHAR(30) NOT NULL,published_year INT NOT NULL,PRIMARY KEY(title));
Print the table structure using the following command:
MariaDB [books]> DESCRIBE books;
You will get the following output:
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| title | varchar(50) | NO | PRI | NULL | |
| author | varchar(30) | NO | | NULL | |
| published_year | int(11) | NO | | NULL | |
+----------------+-------------+------+-----+---------+-------+
Following that is to verify the created table using the following command:
MariaDB [books]> SHOW tables;
You should see your table in the following output:
+-----------------+
| Tables_in_books |
+-----------------+
| books |
+-----------------+
After creating a table, we will insert the following data into a table:
You can use the INSERT statement to insert the data in the first row of the table:
MariaDB [books]> INSERT INTO books VALUE ("Ulysses", "James Joyse", 1923);
Next, repeat the same command to insert the remaining row of table:
MariaDB [books]> INSERT INTO books VALUE ("Catch-22", "Joseph Heller", 1990);
MariaDB [books]> INSERT INTO books VALUE ("Robinson Crusoe", "Daniel Defoe", 1719);
MariaDB [books]> INSERT INTO books VALUE ("Tom Jones", "Henry Fielding", 1749);
MariaDB [books]> INSERT INTO books VALUE ("Emma", "Jane Austen", 1816);
Now, verify all inserted data using the following statement:
MariaDB [books]> SELECT * FROM books;
You should see the following output:
+-----------------+----------------+----------------+
| title | author | published_year |
+-----------------+----------------+----------------+
| Catch-22 | Joseph Heller | 1990 |
| Emma | Jane Austen | 1816 |
| Robinson Crusoe | Daniel Defoe | 1719 |
| Tom Jones | Henry Fielding | 1749 |
| Ulysses | James Joyse | 1923 |
+-----------------+----------------+----------------+
Create a Table Using a Script
You can also create a table in MariaDB using the script. To do so, first, create a file named books.sql using the following command:
nano books.sql
Add the following statements:
CREATE DATABASE books1;
USE books1;
CREATE TABLE books1(title VARCHAR(50) NOT NULL,author VARCHAR(30) NOT NULL,published_year INT NOT NULL,PRIMARY KEY(title));
INSERT INTO books1 VALUE ("Ulysses", "James Joyse", 1923);
INSERT INTO books1 VALUE ("Catch-22", "Joseph Heller", 1990);
INSERT INTO books1 VALUE ("Robinson Crusoe", "Daniel Defoe", 1719);
INSERT INTO books1 VALUE ("Tom Jones", "Henry Fielding", 1749);
INSERT INTO books1 VALUE ("Emma", "Jane Austen", 1816);
Save and close the file when you are finished. Then, run the books.sql file into the MariaDB using the following command:
mysql -u root -p < books.sql
Provide your MariaDB root password to execute the script.
Next, log in to MariaDB using the following command:
mysql -u root -p
Once you are logged in, switch the database to books1 and verify your books1 table using the following command:
MariaDB [(none)]> USE books1;
MariaDB [(none)]> SELECT * FROM books1;
You will get the following output:
+-----------------+----------------+----------------+
| title | author | published_year |
+-----------------+----------------+----------------+
| Catch-22 | Joseph Heller | 1990 |
| Emma | Jane Austen | 1816 |
| Robinson Crusoe | Daniel Defoe | 1719 |
| Tom Jones | Henry Fielding | 1749 |
| Ulysses | James Joyse | 1923 |
+-----------------+----------------+----------------+
Querying Data From a MariaDB Table
MariaDB provides several options for querying data from a table using the SELECT statement.
For example, to display the data from the first column named title use the following statement:
MariaDB [(none)]> USE books
MariaDB [books]> SELECT title FROM books;
You will get only the first column in the following output:
+-----------------+
| title |
+-----------------+
| Catch-22 |
| Emma |
| Robinson Crusoe |
| Tom Jones |
| Ulysses |
+-----------------+
MariaDB view
You can also create a view in MariaDB to filter and display data based on the defined parameters.
For example, create a view named minimum_published_year to display all books title published after the year 1800:
MariaDB [books]> CREATE VIEW minimum_published_year AS SELECT title FROM books WHERE published_year > 1800;
Next, display the view using the SELECT statement:
MariaDB [books]> SELECT * FROM minimum_published_year;
You will get the following output:
+----------+
| title |
+----------+
| Catch-22 |
| Emma |
| Ulysses |
+----------+
If you want to rename the table from books to novels, run the ALTER statement:
MariaDB [books]> ALTER TABLE books RENAME TO novels;
If you want to delete a column with title author from a table, run the following command:
MariaDB [books]> ALTER TABLE novels DROP COLUMN author;
Now, verify the table data using the following command:
MariaDB [books]> SELECT * FROM novels;
You should see that the author column is deleted:
+-----------------+----------------+
| title | published_year |
+-----------------+----------------+
| Catch-22 | 1990 |
| Emma | 1816 |
| Robinson Crusoe | 1719 |
| Tom Jones | 1749 |
| Ulysses | 1923 |
+-----------------+----------------+
If you want to delete a table, use the DROP TABLE statement:
MariaDB [books]> DROP TABLE novels;
Backup and Restore a Table in MariaDB
MariaDB provides a mysqldump command line utility to easily backup and restores a database or table.
For example, to backup a table named books and compress it in Gzip format use the following command:
mysqldump -u root -p books books | gzip > books.sql.gz
To restore a backup from the Gzip file, run the following command:
gunzip < books.sql.gz | mysql -u root -p books
Mariadb Create Table – How to Guide (Step by Step) Conclusion
In the above guide, we explained how to create a table in MariaDB. We also explained how to insert and filter the data by executing queries. I hope you have now enough knowledge to create, delete and manage a table in MariaDB.
