Creating Databases, Tables, and Authorized Users
As you know, a database can be defined in simple terms as an organized collection of information. Particularly, MariaDB is a relational database management system (RDBMS) and uses the Structure Query Language to perform operations on databases. Additionally, keep in mind that MariaDB uses the terms database and schema interchangeably.
To store persistent information in a database, we will use tables that store rows of data. Often, two or more tables will be related to each other in some way. That is part of the organization that characterizes the use of relational databases.
Creating a New Database
To create a new database named
BooksDB, enter the MariaDB prompt with the following command (you’ll be prompted to enter the password for the root MariaDB user):
Once the database has been created, we need to create at least two tables on it. But first let’s explore the concept of data types.
Introducing MariaDB data Types
As we explained earlier, tables are database objects where we will keep persistent information. Each table consists of two or more fields (also known as columns) of a given data type (the type of information) that such field can store.
The most common data types in MariaDB are the following (you can consult the complete list in the official MariaDB online documentation):
Numeric:
- BOOLEAN considers 0 as false and any other values as true.
- TINYINT, if used with SIGNED, covers the range from -128 to 127, whereas the UNSIGNED range is 0 to 255.
- SMALLINT, if used with SIGNED, covers the range from -32768 to 32767. The UNSIGNED range is 0 to 65535.
- INT, if used with UNSIGNED, covers the range from 0 to 4294967295, and -2147483648 to 2147483647 otherwise.
Note: In TINYINT, SMALLINT, and INT, the default SIGNED is assumed.
DOUBLE(M, D), where M is the total number of digits and D is the number of digits after the decimal point, represents a double-precision floating-point number. If UNSIGNED is specified, negative values are not be allowed.
String:
- VARCHAR(M) represents a string of variable length where M is the maximum allowed column length in bytes (65,535 in theory). In most cases, the number of bytes is identical to the number of characters, except for some characters that can take up as much as 3 bytes. For example, the Spanish letter ñ represents one character but takes up 2 bytes.
- TEXT(M) represents a column with a maximum length of 65,535 characters. However, as it happens with VARCHAR(M), the actual maximum length is reduced if multi-byte characters are stored. If M is specified, the column is created as the smallest type that can store such number of characters.
- MEDIUMTEXT(M) and LONGTEXT(M) are similar to TEXT(M), only that the maximum allowed lengths are 16,777,215 and 4,294,967,295 characters, respectively.
Date and Time:
- DATE represents the date in YYYY-MM-DD format.
- TIME represents the time in HH:MM:SS.sss format (hour, minutes, seconds, and milliseconds).
- DATETIME is the combination of DATE and TIME in YYYY-MM-DD HH:MM:SS format.
- TIMESTAMP is used to define the moment a row was added or updated.
After having reviewed these data types, you will be in a better position to determine which data type you need to assign to a given column in a table.
For example, a person’s name can easily fit into a VARCHAR(50), whereas a blog post will need a TEXT type (choose M as per your specific needs).
Creating Tables with Primary and Foreign Keys
Before we dive into creating tables, there are two fundamental concepts about relational databases that we need to review: primary and foreign keys.
A primary key contains a value that uniquely identifies each row, or record, in the table. On the other hand, a foreign key is used to create a link between the data in two tables, and to control the data that can be stored in the table where the foreign key is located. Both primary and foreign keys are generally INTs.
To illustrate, let’s use the
BookstoreDB and create two tables named AuthorsTBL and BooksTBL as follows. The NOT NULL constraint indicates that the associated field requires a value other than NULL.
Also, AUTO_INCREMENT is used to increase by one the value of INT primary key columns when a new record is inserted into the table.
Create MySQL Tables with Primary and Foreign Key
Now we can go ahead and start inserting records into
AuthorsTBL and BooksTBL.Selecting, Inserting, Updating, and Deleting Rows
We will first populate the
AuthorsTBL table. Why? Because we need to have values for AuthorID before inserting records into the BooksTBL.
Execute the following query from your MariaDB prompt:
After that, we will select all records from AuthorsTBL. Remember we will need the AuthorID for each record to create the INSERT query for BooksTBL.
If you want to retrieve one record at a time, you can use a WHERE clause to indicate a condition that a row must meet to be returned. For example,
Alternatively, you can select all the records simultaneously:
Select and Query Record in MySQL
Select and Query a String in MySQL Database
Now let’s create the INSERT query for BooksTBL, using the corresponding AuthorID to match the author of each book. A value of 1 in BookIsAvailable indicates the book is in stock, 0 otherwise:
Insert Query in MySQL Table
At this point we will do a SELECT to see the records in BooksTBL. Then let’s UPDATE the price of “The Alchemist” by Paulo Coelho and SELECT that specific record again.
Note how the BookLastUpdated field now shows a different value. As we explained earlier, a TIMESTAMP field shows the value when the record was inserted or last modified.
Insert Query and update Table in MySQL Database
Insert and Update Database Table Record
Although we won’t do it here, you can also delete a record if not used anymore. For example, suppose we want to delete “The Alchemist” from BooksTBL.
To do so, we will use the DELETE statement as follows:
As in the case of UPDATE, it is a good idea to do a SELECT first in order to view the record(s) that may potentially be impacted by the DELETE.
Also, don’t forget to add the WHERE clause and a condition (BookID=6) to select the specific record to be removed. Otherwise, you run the risk of deleting all the rows in the table!
If you desire to concatenate two (or more) fields, you can use the CONCAT statement. For example, let’s say we want to return a result set that consist of one field with the book name and author in the form of “The Alchemist (Paulo Coelho)” and another column with the price.
This will require a JOIN between AuthorsTBL and BooksTBL on the common field shared by both tables (AuthorID):
As we can see, CONCAT allows us to join multiple string expressions separated by commas. You’ll also noticed that we chose the alias Description to represent the result set of the concatenation.
The output of the above query is shown in the below image:
Query Multiple Fields in MySQL Table
Query Multiple Fields in Database Table
Create User to Access the BookstoreDB Database
Using root to perform all DML operations in a database is a bad idea. To avoid this, we can create a new MariaDB user account (we’ll name it bookstoreuser) and assign all necessary permissions for BookstoreDB:
Create New Database User with Privileges
Having a dedicated, separate user for each database will prevent damages to the entire database should a single account become compromised.
Extra MySQL Tips
To clear up the MariaDB prompt, type the following command and press Enter:
To inspect the configuration of a given table, do:
For example,
List Columns in Database Table
List Columns in Database Table
A quick inspection reveals that the BookIsAvailable field admits NULL values. Since we don’t want to allow that, we’ll ALTER the table as follows:
(Feel free to show the columns again – the highlighted YES in the above image should now be a NO).
Finally, to view all the databases on your server, do:
List All MySQL Databases
The following image shows the result of the above command after accessing the MariaDB prompt as the bookstoreuser (note how this account can’t “see” any databases other than BookstoreDB and information_schema (available for all users):
List All MySQL Databases
Summary
In this article we have explained how to run DML operations and how to create a database, tables, and dedicated users on a MariaDB database. Additionally, we shared a few tips that may make your life as a system / database administrator easier.
