Pre-requisites
For this example I am running:
- A computer running Windows 11.
- MariaDB Server (v11.4.5) running in a Docker on Unraid.
- HeidiSQL v12.11.0.7065.
- Unraid 6.2.10
This tutorial is about creating a simple MariaDB database and learning how to create tables in said database. I cover how to use HeidiSQL both graphical user interface driven and using pure SQL.
BONUS Material As a bonus I am also proving pre-vetted SQL statements to create a "Test" database with tables and dummy data for you to use in my tutorial on creating a Rest API using NodeJS coming soon. All the SQL scripts here.
MariaDB Server running on Unraid
This tutorial assumes you have already installed MariaDB Server on Unraid.
It is important to note that when you connect to a MariaDB database you are actually connecting to MariaDB Server and MariaDB server is handling your connection to the MariaDB database. This means that MariaDB Server has to be running to connect to a MariaDB database.
Ports: As with connecting to devices on a network and networking in general port numbers are critically important. Knowing specific port numbers will make your life a lot easier in the long run.
This leads us to your MariaDB Server installation on Unraid. You have to know the port that you have set MariaDB Server to run on as you will use this information later on when to connect to your MariaDB database.
In Unraid check the port number that you have set the MariaDB Docker container to use. In this example you can see that MariaDB Server is using port: 3666.
You can also check the MariaDB Docker Container set up to check the port number (see image below):
HeidiSQL: Connect to MariaDB Server
From here on you will be connecting to MariaDB Server and your specific MariaDB database using HeidiSQL.
To be clear this tutorial isn't really about learning SQL. It's more conceptual about the relationship between MariaDB Server, MariaDB databases and that you can run queries against your MariaDB database using HeidiSQL or other querying client, eg, MySQL Workbench.
Create a MariaDB Database
In this tutorial you are connecting to MariaDB using HeidiSQL. This tutorial assumes you have HeidiSQL installed. If you do not have HeidiSQL installed, head over to HeidiSQL and install it.
Step 1: Log into HeidiSQL
Start HeidiSQL and you will be prompted to use one of your existing session connections or you can create a New session (see image below). Bottom left corner for the 'New' button.:
In this example, the user already has 5 previous sessions set up.
If you are a first time HeidiSQL user and using MariaDB in a Docker container on Unraid (as per this tutorial), then the key information for you is to know the IP Address of your Unraid server. This information is required to connect to your MariaDB Server.
HeidiSQL Sessions
Connection information: So to setup a HeidiSQL session you require the following data:
- IP Address of your Unraid server
- Password to your MariaDB server
- Port to connect to MariaDB server
- Database name (optional)
Step 2: Click on "New" button.
The following will appear:
Step 3: Input a "name" for the session where it says "Unnamed *" and then input
- Hostname / IP: IP Address of your Unraid server
- Password: Password to your MariaDB server
- Port: Port to connect to MariaDB server
- Databases: Names of existing databases separated by semicolon or leave blank.
then click "Open" command button.
Step 4: Create a database
Right click on the "Session" name then...click on "Create new"...click on Database (see image below):
Enter a name for the database. In this example "testdb" and click 'OK' command button.
Right click on the "Session" name...click "Refresh" and you will see the "testdb" database you just created. Congratulations, you've just created your first MariaDB database!
Of course a database isn't much use without tables so read on.
Database Tables
This tutorial is only an elementary introduction to MariaDB. If time permits a future tutorial will get into the nitty gritty of MariaDB tables and database structure. For now you'll just get some basics.
However, to understand and create database tables you do require some preliminary knowledge of data types in MariaDB.
Numeric Data Types
| Numeric Data Type | Description |
|---|---|
| TINYINT | Very small integer. Signed range: -128 to 127; Unsigned: 0 to 255. |
| SMALLINT | Small integer. Signed range: -32,768 to 32,767; Unsigned: 0 to 65,535. |
| MEDIUMINT | Medium-sized integer. Signed: -8,388,608 to 8,388,607; Unsigned: 0 to 16,777,215. |
| INT / INTEGER | Standard integer. Signed: -2,147,483,648 to 2,147,483,647; Unsigned: 0 to 4,294,967,295. |
| BIGINT | Large integer. Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. |
| DECIMAL (M,D) / NUMERIC | Exact fixed-point number. M = total digits, D = digits after decimal point. |
| FLOAT (M,D) | Approximate single-precision floating-point number. M and D control display. |
| DOUBLE / DOUBLE PRECISION / REAL | Approximate double-precision floating-point number. |
| BIT(M) | Stores binary values (bit-field). M is the number of bits (1–64). |
| BOOL / BOOLEAN | Alias for TINYINT(1). Value of 0 is false, non-zero is true. |
String Data Types
| String Data Type | Description |
|---|---|
| CHAR(M) | Fixed-length string. Always uses M characters, padded with spaces if needed. Max 255 characters. |
| VARCHAR(M) | Variable-length string. Stores up to M characters (max 65,535 bytes depending on row size and encoding). |
| TINYTEXT | Very small text string. Maximum length of 255 characters. |
| TEXT | Text column with a maximum length of 65,535 characters (64 KB). |
| MEDIUMTEXT | Medium-sized text. Stores up to 16,777,215 characters (16 MB). |
| LONGTEXT | Large text data. Can store up to 4,294,967,295 characters (4 GB). |
| BINARY(M) | Fixed-length binary data. Stores exactly M bytes. |
| VARBINARY(M) | Variable-length binary data. Stores up to M bytes. |
| TINYBLOB | Very small binary large object. Max size: 255 bytes. |
| BLOB | Binary large object up to 65,535 bytes (64 KB). |
| MEDIUMBLOB | Binary large object up to 16,777,215 bytes (16 MB). |
| LONGBLOB | Binary large object up to 4,294,967,295 bytes (4 GB). |
| ENUM('val1', 'val2', ...) | String object with a predefined set of values. Stores one value from the list. |
| SET('val1', 'val2', ...) | String object that can store zero or more values from a predefined list. |
Date and Time Data Types
| Date & Time Data Type | Description |
|---|---|
| DATE | Stores date values in the format YYYY-MM-DD. Range: 1000-01-01 to 9999-12-31. |
| DATETIME[(fsp)] | Stores date and time in the format YYYY-MM-DD HH:MM:SS. Optional fractional seconds (fsp: 0–6). |
| TIMESTAMP[(fsp)] | Stores date and time similar to DATETIME, but converts between session time zone and UTC. Supports fractional seconds. |
| TIME[(fsp)] | Stores time values or durations in the format HH:MM:SS. Range: -838:59:59 to 838:59:59. Supports fractional seconds. |
| YEAR | Stores a 4-digit year in the format YYYY. Range: 1901 to 2155. |
Creating Tables
You can create tables either through:
I'll show you both methods
Graphical User Interface to Create MariaDB Database and Tables
Create Table: Graphical user interface
This method is probably the easiest if you are new to SQL or an infrequent user of HeidiSQL and MariaDB. Basically, to use the SQL statements well it's best to know the MariaDB data types in great detail and infrequent users just won't build up the detailed knowledge that using the graphical user interface lets you get around.
Step 1: Right click on the database name...Create new...Table (see image below):
Then enter the:
- database "name"; and
- optionally a "Comment" about the database.
Add table columns (fields): Then click the "+Add" button to add a column to the table.
In this tutorial we will make a table to collect all the albums I have in my personal music collection. As such we will make a table called 'Album' and record associated data in a number of columns such as album name, artist, year the album was released and the record label.
For each column you define a data type and also the length. The length is particularly important as it has impacts on the size of data you can collect and the speed of your queries. While not so important for small databases the chosen data type can have significant impact on large databases as well as ongoing maintenance and hardware considerations.
Entering Data Type: The HeidiSQL user interface is pretty easy to use. When you add a new column you just click on the "Datatype" text box and a dropdown will appear for you to enter the data type. Easy and intuitive.
Modifying the table columns: The HeidiSQL user interface makes modifying the table structure quite easy and you can easily move the columns up, down, remove a column, add a column and create a new index by right clicking in the # column and the following drop down will appear.
Allowing NULLs: Probably one of the most important table design considerations is whether you allow a column to have a NULL value. HeidiSQL makes setting a column to accept or not accept NULL values easy. Just click on the "Allow NULL" checkbox to allow NULLs or keep unchecked if it must contain a value.
DEFAULT Values: Another important table design consideration is whether you set default values. HeidiSQL makes setting a default value easy. Click in the "Default" textbox and a dropdown will appear.
Primary Key: Primary keys and creating unique identifiers is a critical aspect of table design. As a practical tutorial I'll leave it up to you to do some research on primary keys in your own time. What I will show you is how to create a primary key and also an auto-incrementing column, that is, an identifier that goes up in value each time you add a record without you having to code anything.
Add a Primary Key: Again the HeidiSQL gui makes this a simple task. Follow these steps:
- Right click anywhere in the columns definition
- Click on "Create new index"
- Click on "PRIMARY"
Auto-incrementing Column: Even creating an auto-incrementing column is very easy in HeidiSQL. Follow these steps:
- Click on the 'Default' text box
- Click on the 'AUTO-INCREMENT' button
- Click on the 'OK' command button
Simple - it's done. HeidiSQL does all the hard work for you and only permits you to create an auto-incrementing column on INTEGER data types (compliant with MariaDB Server rules).
Congratulations! You've covered the basics of creating a MariaDB database and tables using the HeidiSQL graphical user interface. Now we'll briefly go over using SQL statements to do something similar.
Database Creation Using SQL Statements
We will literally only touch the surface here as if you are serious about using SQL statements to create MariaDB databases and tables you really have to be a frequent and ongoing user of MariaDB and HeidiSQL to make it worth your while. Knowing and learning all the syntax and settings would require a significant amount of learning time that isn't realistic for infrequent users.
Create Database: SQL Statements
To create a MariaDB database the syntax is very simple. Simply the command "CREATE DATABASE" followed by the Database Name enclosed in speech marks.
Create Table: SQL Statements
To create a table using SQL statements is a lot more complex and you really need an intimate knowledge of MariaDB data types, syntax and MariaDB Server database rules. We've included code how to create the 'Albums' table and you can see it is a lot more complex than using the graphical user interface.
CREATE TABLE `Albums` (
`AlbumID` INT(11) NOT NULL AUTO_INCREMENT,
`AlbumName` TEXT NOT NULL COLLATE 'utf8mb4_general_ci',
`ArtistName` VARCHAR(256) NOT NULL COLLATE 'utf8mb4_general_ci',
`YearReleased` INT(11) NOT NULL,
`RecordLabel` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`AlbumID` ) USING BTREE
)
AUTO_INCREMENT = 1
COMMENT='Albums in my music collection'
COLLATE='utf8mb4_general_ci'
ENGINE=INNODB
;
We aren't going to delve any deeper in using SQL statements and will leave it to you to do your own independent learning if it is a subject you are extremely interested in.
Please support this channel: Have I saved you minutes, hours or even days of scouring the internet to find an actual working solution.
It takes me time and effort to both find a working solution and then write everything up. Please consider buying me a coffee so I can keep producing useful content, especially if I've made your life easier. Cheers!
BONUS Content: SQL Statements for Dummy MariaDB Database and Tutorial on Rest APIs
If you are interested in Rest API development, particularly using NodeJS I am supplying SQL statements to setup a MariaDB database, tables and dummy data to use. Check out my REST API tutorial here (coming soon).
Create the table: customers
CREATE TABLE `customers` (
`customerid` INT(11) NOT NULL AUTO_INCREMENT,
`firstname` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`lastname` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`address1` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`address2` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`state` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`email` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`mobile` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
`gender` CHAR(255) NOT NULL DEFAULT '0' COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`customerid`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
INSERT dummy data into the table: customers
INSERT INTO customers
(firstname, lastname, address1, address2, state, email, mobile, gender )
VALUES
('Michael', 'Jordan', '10 Newhaven Road', 'Williamstown', 'VIC', 'michaeljordan@gmail.com', '02 6271 5111','M'),
('Isiah', 'Isiah', '24 Amberly Avenue', 'Detroit', 'Michigan', 'Isiahthomas@gmail.com', '02 6271 5111','M'),
('Charles', 'Barkly', '10 Fifth Avenue', 'Philadelphia', 'Pennsylvania', 'charlesbarkly@gmail.com', '02 6271 5111','M'),
('Taylor', 'Swift', '25 Stockton Road', 'Reading', 'Pennsylvania', 'taylorswift@hotmail.com', '02 6271 5111','F'),
('Dua', 'Lipa', '35 Tottenham Court Road', 'South Kensington', 'London', 'dualipa@hotmail.com', '02 6271 5111','F'),
('Gracie', 'Abrams', '3 Hound Road', 'Los Angeles', 'California', 'gracieabrams@hotmail.com', '02 6271 5111','F'),
('Tom', 'Cruise', '133 Top Gun Street', 'Los Angeles', 'California', 'tomcruise@hotmail.com', '02 6271 5111','M'),
('Jonn', 'Wick', '133 Constantine Road', 'Detroit', 'Michigan', 'jonnwick@hotmail.com', '02 6271 5111','M')
Create the table: products
CREATE TABLE `products` (
`productid` INT(20) NOT NULL AUTO_INCREMENT,
`productname` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
`category` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
`brand` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`productid`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
INSERT dummy data into the table: products
INSERT INTO products
(productname, category, brand )
VALUES
('Jordan Retro 95', 'Shoes', 'Nike'),
('Isiah Thomas Piston Retro 89', 'Shoes', 'Adidas'),
('Shaq Attack 91', 'Cap', 'Reebok'),
('Scotty Pippin', 'Cap', 'Fila'),
('Michael Jordan Jr', 'Cap', 'ASICS'),
('Charles Barkly', 'Tshirt', 'ASICS'),
('John Stockton', 'Tshirt', 'Reebok'),
('Clyde Drexler', 'Tshirt', 'Nike'),
('Joe Dumars', 'Shoes', 'Fila')
0 Comments