Creating a Database

A database is the main container that holds all tables and other objects. Before creating tables, we must first create or select the database to work with.

CREATE DATABASE

The CREATE DATABASE statement is used to create a new database. Here is the basic syntax:

CREATE DATABASE name_database;

Example of creating a database for an academic system:

CREATE DATABASE academic;

Listing All Databases

Use the SHOW DATABASES command to see the list of all databases on the MySQL server:

SHOW DATABASES;

The resulting output will display the system's built-in databases and the database we just created.

Selecting a Database

After a database is created, we must select it before we can work inside it. Use the USE command:

USE academic;

After running this command, all subsequent queries will be executed on the academic database until we select another database.

IF NOT EXISTS

When running automated scripts or redeploying, a database with the same name may already exist. To avoid an error, use the IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS academic;

If the academic database already exists, MySQL will ignore this command without displaying an error. This is very useful in production environments.

Dropping a Database

The DROP DATABASE statement is used to permanently delete a database along with all of its contents:

DROP DATABASE academic;

Warning: This action cannot be undone. All tables and data in the database will be lost forever.

IF EXISTS

Similar to IF NOT EXISTS, the IF EXISTS clause prevents an error when trying to drop a database that does not exist:

DROP DATABASE IF EXISTS academic;

If the database is not found, MySQL will ignore this command without an error.

A Practical Combination

In development, we often want to rebuild a database from scratch. Here is a commonly used combination:

-- Drop the old database if it exists
DROP DATABASE IF EXISTS academic;

-- Create the new database
CREATE DATABASE academic;

-- Use the database
USE academic;

This pattern ensures we always start with a clean state.

Database Naming Rules

MySQL has certain rules for naming databases:

• Names may use letters, digits, and underscores (_)
• Names must not start with a digit
• Names must not contain spaces or special characters
• Maximum length is 64 characters
• Names must be unique within a single MySQL server

-- Valid names
CREATE DATABASE my_database;
CREATE DATABASE db_academic_2024;

-- INVALID names
CREATE DATABASE my-database;      -- contains a hyphen
CREATE DATABASE 2024_database;    -- starts with a digit
CREATE DATABASE my database;      -- contains a space

Summary

Creating and managing databases is the first step in data management with SQL. The CREATE DATABASE, USE, and DROP DATABASE commands form the foundation of database administration. Always use IF NOT EXISTS and IF EXISTS to prevent unwanted errors.

In the next chapter, we will learn how to create tables inside the database we have created.