When we install MariaDB, we're installing a database server, not a specific database, and a single MariaDB database server can have several databases inside it. Here's an analogy that can help us understand this arrangement: a database can be thought of as a large filing cabinet. The filing cabinet contains a number of drawers and inside each drawer are files with information. In this analogy, the filing cabinet is a database, the drawers are tables within the database, and the files are rows of data within the tables. So what is MariaDB? It's the room the filing cabinet is located in, and it's a large room so we can put many filing cabinets inside it.
When MariaDB is installed, the installer creates a system database that MariaDB uses to keep track of users, databases, and other housekeeping information. The installer also creates a test database for experimentation and learning, and a couple of read-only, semi-virtual databases where MariaDB stores performance and other statistics. We don't want to use the system database as this could mess up the entire server if we made a mistake. We can't put data into the statistics databases, called information_schema and performance_schema, because they are semi-virtual and read only. We can use the test database, but we probably don't want to use it for anything permanent. So one of our first tasks, when we start using MariaDB, is to create at least one database for us to use.
Tip
Another word for a database is schema. In some database servers, a schema and a database are not quite the same thing, but in MariaDB they are. So when we see information_schema, this means the information database. We can even use SCHEMA instead of DATABASE when we are using the command-line client. For example: SHOW SCHEMAS instead of SHOW DATABASES. In this book, we'll stick to the name databases.
Generally, databases are created for specific things or specific applications. For example, we could have an accounting database for the finance department, a human resources database for the HR department, and a parts database for the warehouse.
Creating and dropping (deleting) databases are two things that we will do less often than just about anything else when working with MariaDB. There just isn't much call for it in day-to-day work. We generally create a database and then use it as long as it is needed (which could be for years or decades) and then we delete (drop) it. Thankfully, the commands for creating and dropping a database are very simple, so they're easy to remember.
As mentioned previously, creating a database is not something we will do often. To create a database in MariaDB, we use the CREATE DATABASE command. The basic syntax is as follows:
CREATE DATABASE <databasename>;
If the database already exists when we try to create it, we will receive an error. We can suppress the error with IF NOT EXISTS.
The following are some examples:
CREATE DATABASE my_database; CREATE DATABASE IF NOT EXISTS my_database;
The preceding two commands are equivalent if the database does not exist. If the database does exist, the first command will exit with an error and the second command will do nothing.
As mentioned before, it isn't often that we need to remove or delete a database, but when we do, we use the
DROP DATABASE command. Out of the database commands, this one is by far the easiest, but it is potentially the most dangerous. The basic syntax is as follows:
DROP DATABASE <databasename>;
If the named database doesn't exist when we try to drop it, we will receive an error. We can suppress the error with IF EXISTS.
The following are a couple of examples that drop the database that we just created:
DROP DATABASE my_database; DROP DATABASE IF EXISTS my_database;
The preceding two commands are equivalent if the database my_database exists. If the database does not exist, the first command will exit with an error and the second command will do nothing.
As mentioned previously, the DROP DATABASE command can be very dangerous. Why is this, you might ask? This is because if you have the appropriate permission to drop a database, MariaDB trusts you and will delete the database and everything in it when you tell it to, no questions asked. So when setting up users, it is important to give only trusted users, who actually need it, the ability to use the DROP DATABASE command. More on setting up users and giving them permissions is given in Chapter 4, Administering MariaDB.
Note
Warning: When dropping a database, user privileges for the database are not removed. We need to revoke them manually, or drop the user entirely; otherwise, if or when the database is recreated, the user will still have the privileges. See Chapter 4, Administering MariaDB, for information on managing users and their privileges.