Lesson 02 ~20 min read

Creating and Managing Databases

A database is the container your tables live in. Before you can create a single table, you need to create the database — and know how to select it, inspect it, and when necessary, remove it.

The Database as a Container

In MySQL and most relational systems, a database (also called a schema) is a named container that holds a collection of related tables. Before you can create any tables or store any data, you need a database to put them in.

Think of a database as a folder on your computer. Tables are the files inside it. You can have multiple databases on the same server — one for your school system, one for your student app, one for testing — all completely separate.

CREATE DATABASE

Creates a new, empty database. The name should be lowercase, use underscores instead of spaces, and be descriptive.

sql

-- Basic syntax
CREATE DATABASE school_db;

-- Safe version — only creates if it doesn't already exist
CREATE DATABASE IF NOT EXISTS school_db;

-- With character encoding (best practice)
CREATE DATABASE school_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

// Always use utf8mb4

The utf8mb4 character set supports all Unicode characters including emoji. The older utf8 in MySQL is actually a limited 3-byte encoding. Use utf8mb4 for any new database to avoid encoding problems down the line.

SHOW DATABASES

Lists all databases available on the current server. Useful to check what exists before creating or connecting to a database.

sql

SHOW DATABASES;

-- Typical output:
-- +--------------------+
-- | Database           |
-- +--------------------+
-- | information_schema |
-- | mysql              |
-- | performance_schema |
-- | school_db          |
-- +--------------------+

-- The first three are system databases — leave them alone
-- school_db is the one we just created

USE — Selecting a Database

Before running any table operations, you must tell the DBMS which database to work in. The USE command sets the active database for all subsequent queries in your session.

sql

-- Select the database to work in
USE school_db;

-- Now all table queries operate inside school_db
SHOW TABLES;   -- lists tables in school_db
SELECT * FROM students;  -- queries the students table in school_db

-- You can also specify the database inline without USE:
SELECT * FROM school_db.students;

DROP DATABASE

Permanently deletes a database and everything inside it — all tables, all data, all indexes. There is no undo. This is one of the most destructive commands in SQL. Use it only in development, and always double-check which database is active before running it.

sql

-- Delete a database entirely (IRREVERSIBLE)
DROP DATABASE school_db;

-- Safe version — won't error if it doesn't exist
DROP DATABASE IF EXISTS school_db;

-- Always confirm the active database before dropping:
SELECT DATABASE();  -- shows your currently selected database

CREATE DATABASE

Creates a new empty database container on the server.

SHOW DATABASES

Lists all databases on the current server connection.

USE

Sets the active database. All subsequent queries target this database.

SELECT DATABASE()

Returns the name of the currently active database — a useful sanity check.

DROP DATABASE

Permanently deletes the database and all its contents. Irreversible.

// Knowledge Check

You connect to a MySQL server and want to start working with a database called library_db that already exists. What is the correct command?