Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 40 ยท Getting Started

Create, Select, Alter and Drop a Database

Create and manage MariaDB databases: CREATE DATABASE with character sets and collations, IF NOT EXISTS, USE, ALTER DATABASE, DROP DATABASE and what happens on disk.

A database in MariaDB is a named container for tables, views, stored programs and other objects. It is also a directory on disk, which explains much of its behavior. This lesson covers the four statements you use to manage databases and the one setting that matters most: the default character set and collation.

The statements below change server state, so they are read-only in this lesson. Each has a runnable cousin where the idea can be shown with SQL the browser supports.

CREATE DATABASE

SQL
CREATE DATABASE shop;
CREATE DATABASE IF NOT EXISTS shop;
CREATE OR REPLACE DATABASE shop_test;     -- MariaDB: drops an existing one first
CREATE DATABASE blog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
  • CREATE SCHEMA is a synonym of CREATE DATABASE.
  • IF NOT EXISTS turns "already exists" from an error into a warning, which makes setup scripts safe to repeat.
  • CREATE OR REPLACE is a MariaDB extension. It is convenient in test setups and dangerous anywhere else, because it silently destroys the old database.
  • Names are case sensitive on Linux (they map to directories) and case insensitive on Windows. Use lowercase with underscores everywhere, and your code works on both.
  • Names with unusual characters need backticks, like `my-db`. Avoid them.

Character set and collation

A character set defines which characters can be stored and how they are encoded. A collation defines how they are compared and sorted (case sensitive or not, accent sensitive or not). If you give no values the server defaults apply, which differ between versions and platforms. Always set them on purpose.

For almost every new project use utf8mb4, the full four-byte UTF-8 that stores emoji and every language. (The older utf8 in MariaDB is an alias for the three-byte utf8mb3, which cannot hold emoji.) Typical collations are utf8mb4_unicode_ci and utf8mb4_general_ci, both case-insensitive. Modern MariaDB also offers uca1400 collations. See character sets and collations for the details.

SQL
SHOW CHARACTER SET LIKE 'utf8%';
SHOW COLLATION WHERE Charset = 'utf8mb4' AND Collation LIKE '%unicode%';
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata;

Listing and selecting

SQL
SHOW DATABASES;
SHOW DATABASES LIKE 's%';
USE shop;
SELECT DATABASE();   -- shop

USE shop sets the default database for the session. Without it you must qualify every table name, as in SELECT * FROM shop.products. You can query across databases on the same server by qualifying names, which is a feature, not a mistake.

You can also choose the database when connecting: mariadb -u app -p shop. Applications put it in the connection string.

ALTER DATABASE

You can only change database-wide options, not rename it:

SQL
ALTER DATABASE blog
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci;

This changes the default for tables created later. It does not convert existing tables or their data. To convert an existing table, run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4. This is a frequent surprise: after changing the database default, old tables stay in the old character set.

MariaDB has no RENAME DATABASE (it was removed from MySQL for being unsafe). To rename, create a new database and move tables with RENAME TABLE old.t TO new.t, or dump and reload.

DROP DATABASE

SQL
DROP DATABASE blog;
DROP DATABASE IF EXISTS blog;

This deletes every table and all data in the database immediately and permanently, with no confirmation and no transaction. It cannot be rolled back. Habits that prevent disasters:

  • Check SELECT DATABASE(); before running it.
  • Give application users no DROP privilege on production.
  • Keep tested backups (see backup and restore).

What exists on disk

Each database is a folder under the data directory (SHOW VARIABLES LIKE 'datadir'). Inside, there is a db.opt file with the default charset and, depending on the engine, files per table. InnoDB tables use .ibd files when innodb_file_per_table is on (the default). Creating and dropping databases is therefore very fast, but you should still never delete those folders by hand while the server runs.

flowchart TD
  S["Server (datadir)"] --> D1["shop/"]
  S --> D2["blog/"]
  D1 --> T1["products.frm / .ibd"]
  D1 --> T2["orders.frm / .ibd"]
  D2 --> T3["posts.frm / .ibd"]

One folder per database; the engine stores the table files inside.

A runnable stand-in

SQLite has one database per file, so CREATE DATABASE has no direct equivalent. What you can try is the other half of the lesson, qualifying names and inspecting the catalog. Every SQLite connection has a database called main, and you can attach another one in memory:

SQL Two databases in one session ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In MariaDB the same pattern needs no ATTACH, because all databases on a server are visible:

SQL
CREATE DATABASE archive;
CREATE TABLE archive.old_orders AS
SELECT * FROM shop.orders WHERE status = 'delivered';

You can also see which databases the session knows about:

SQL Listing databases (SQLite) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Naming advice

  • Use one database per application (or per tenant in simple designs).
  • Lowercase, underscores, no spaces: shop, blog_prod.
  • Do not prefix tables with the database name; qualify with db.table when needed.
  • Keep production, staging and test in separate databases or servers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does CREATE DATABASE IF NOT EXISTS shop do when shop already exists?

    IF NOT EXISTS makes the statement safe to repeat; it only produces a warning.
  2. 2. After ALTER DATABASE blog CHARACTER SET utf8mb4, what happens to existing tables?

    Only the default for future tables changes. Convert existing tables with ALTER TABLE ... CONVERT TO CHARACTER SET.
  3. 3. Which statement is true about DROP DATABASE?

    DDL statements cause an implicit commit, and dropping a database is immediate and permanent.
  4. 4. Which character set should most new MariaDB projects use?

    utf8mb4 stores every Unicode character including emoji. The plain name utf8 means the three-byte utf8mb3.

Summary

  • CREATE DATABASE (alias CREATE SCHEMA) makes a database; add IF NOT EXISTS for repeatable scripts and always set utf8mb4.
  • USE name selects the default database; qualify names as db.table to cross databases.
  • ALTER DATABASE changes only the defaults for future tables; there is no rename.
  • DROP DATABASE is instant and irreversible.

Next: Working with tables