MariaDB Tutorial / Create a Database with CREATE DATABASE, USE, and utf8mb4

CREATE DATABASE creates a database, while USE selects the default database for your current connection. Specify utf8mb4 and a suitable collation explicitly for this practice database, then inspect the stored settings; IF NOT EXISTS does not update an existing database’s defaults.

Check the learning account and database scope

Connect as course_user using the command-line connection guide. The installation procedure grants privileges on shopcourse.*. That lets the account create and use this database without permission to create arbitrary databases elsewhere.

Keep the name shopcourse for this course. If you change it, the grants and later SQL examples must change consistently. Do not use system databases such as mysql for practice tables. Confirm the authenticated account before creating anything:

SELECT CURRENT_USER() AS privilege_account;
SHOW GRANTS;

The expected account is course_user@127.0.0.1. A different result can indicate that you connected using another identity or host rule.

Create shopcourse and inspect its settings

Save the complete example as a UTF-8 file named lesson05-create-database.sql, or execute the statements in order inside the client:

CREATE DATABASE IF NOT EXISTS shopcourse
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SHOW WARNINGS;
USE shopcourse;
SELECT DATABASE() AS current_database;
SHOW CREATE DATABASE shopcourse;

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'shopcourse';

If using a file, run SOURCE with its actual full local path as explained in the client guide. On a fresh database, SELECT DATABASE() returns shopcourse. The schema-information query should report:

SHOW WARNINGS belongs immediately after the statement whose messages you want to inspect. SHOW CREATE DATABASE shows the server’s stored definition; its formatting can differ across versions. This file creates the database container, not the products table. The CREATE DATABASE reference and SHOW CREATE DATABASE reference describe these statements.

What IF NOT EXISTS does and does not do

IF NOT EXISTS avoids a database-already-exists error by leaving the existing database in place and reporting a note. It does not clear its data or rewrite its character set and collation. Consequently, rerunning the example against an existing shopcourse may show settings different from the fresh-database result above.

Inspect the existing database before deciding what to change. Do not use DROP DATABASE as a routine way to make an example’s output match. If valuable tables exist, preserve them and investigate their definitions. A successful CREATE statement with IF NOT EXISTS is not proof that the database now has the requested defaults.

Distinguish character sets, collations, and connections

A character set defines how text is represented. A collation defines how strings are compared and ordered. utf8mb4 supports four-byte UTF-8 characters; the shorter name utf8 has historically been an alias with version-dependent configuration, so use the explicit name in these examples.

We use utf8mb4_unicode_ci as a commonly available example rather than claiming it is every release’s newest or default collation. The official character-set and collation guide explains inheritance and configuration. To see one specific comparison difference, run:

SELECT
  _utf8mb4'A' COLLATE utf8mb4_unicode_ci =
  _utf8mb4'a' COLLATE utf8mb4_unicode_ci AS case_insensitive,
  _utf8mb4'A' COLLATE utf8mb4_bin =
  _utf8mb4'a' COLLATE utf8mb4_bin AS binary_comparison;

Here 1 means the comparison is true and 0 means false: the case-insensitive collation treats these two ASCII letters as equal, while the binary collation distinguishes them. This small test does not describe every language’s sorting behavior. Choose a collation according to the comparisons your application needs.

Select the database for the current connection

USE shopcourse;
SELECT DATABASE() AS current_database;
SHOW TABLES;

USE selects a default database for this connection only. It does not permanently change the default for every client or future login. On a new database, SHOW TABLES returns an empty set because no tables have been created yet; this is a valid result.

You can also refer to a table with a qualified name such as shopcourse.products once it exists. An unqualified name such as products depends on the selected database. The product-table and query exercise creates that table and makes this distinction practical.

Troubleshoot permissions, database selection, and text settings

• CREATE permission denied: check CURRENT_USER() and SHOW GRANTS for the intended database scope.
• Unknown database: verify the name and that you are connected to the intended server and port.
• No database selected: run USE shopcourse before using an unqualified table name.
• Unexpected collation: inspect an existing database; IF NOT EXISTS does not revise its defaults.
• Incorrect text: check the source file, connection settings, and actual table or column definitions.

Changing a database’s default character set does not convert text columns in existing tables. Before modifying definitions, save their original definitions and assess the required table or column conversion separately. There is no need to delete a database merely to diagnose a connection or selection problem. Verify the current settings first and change only the setting responsible for the issue.