Database Management Basics

Introduction

A database is a named schema container inside MySQL—like a folder for tables. This chapter creates, selects, lists, and drops databases with correct charset settings, and explains system databases you should not delete casually.

Prerequisites

SHOW and USE

List databases:

sql
SHOW DATABASES;

Typical output includes:

DatabasePurpose
mysqlSystem grants and metadata
information_schemaRead-only metadata views
performance_schemaServer performance stats
sysHelpful diagnostic views
learn_dbYour practice DB (if created)

Select active database:

sql
USE learn_db;

Confirm:

sql
SELECT DATABASE();

Code explanation:

  • USE does not create a database—it only switches context
  • Unqualified CREATE TABLE goes into current database

CREATE DATABASE

With explicit charset (recommended):

sql
CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Code explanation:

  • CHARACTER SET utf8mb4 stores emoji and all Unicode
  • COLLATE utf8mb4_unicode_ci defines sort/compare rules (case-insensitive for many Latin letters)

Idempotent create:

sql
CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

DROP DATABASE

Permanently removes database and all tables:

sql
DROP DATABASE shop;

Safer:

sql
DROP DATABASE IF EXISTS shop;

Warning

No Undo for DROP DATABASE

Back up before drop—backup chapter later. Never drop mysql or sys.

Rename Pattern

MySQL has no single RENAME DATABASE—use dump/import or rename tables into new database—advanced. Prefer planning names upfront.

Charset and Collation Deep Dive

SettingLevelAnalogy
Server defaultmy.cnfFactory default
DatabaseCREATE DATABASEProject standard
TableCREATE TABLEOverride per table
ColumnColumn definitionRare fine control

Stick to utf8mb4 / utf8mb4_unicode_ci unless team specifies otherwise.

Wrong charset symptom: ??? or broken emoji in stored text—fix before large data load.

Inspect Database Metadata

sql
SHOW CREATE DATABASE learn_db\G
sql
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'learn_db';

Practice Exercise

sql
CREATE DATABASE blog_dev
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
 
USE blog_dev;
 
-- Confirm empty—no user tables yet
SHOW TABLES;

Keep blog_dev for upcoming table chapters.

FAQ

How many databases?

As needed—one per app environment (shop_dev, shop_test) is common.

Schema vs database in MySQL?

In MySQL, schema and database are synonyms: CREATE SCHEMA x = CREATE DATABASE x.

Access denied creating DB?

User lacks CREATE privilege—use root or GRANT (users chapter).

Docker MYSQL_DATABASE?

Creates one DB at init—add more with CREATE DATABASE inside client.

Case sensitivity of names?

Database/table names case depends on lower_case_table_names—use lowercase on all platforms to avoid surprises.

Default database on login?

mysql -u user -p blog_dev or USE in ~/.my.cnf.