Tutorials SQL CREATE DATABASE
Ch 6 / 69
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above
Chapter 6 Beginner 5 min read DDL

SQL CREATE DATABASE

Before you can store any data, you need a container for it — a database. The CREATE DATABASE statement is the very first DDL command every SQL developer learns. In this chapter you'll master the exact syntax, the safety guard IF NOT EXISTS, character sets, collations, and how to switch between databases using USE.

What you'll learn
What a database is in the context of SQL
Full syntax of the CREATE DATABASE statement
How to use IF NOT EXISTS to avoid errors
Setting character sets and collations
Switching databases with the USE statement
Listing and dropping databases
Syntax differences across MySQL, PostgreSQL, SQL Server

What is a Database in SQL?

In SQL, a database is a named container that holds all your related tables, views, stored procedures, indexes, and other database objects. Think of it as a folder on your file system: just like a folder groups related files, a database groups all the tables that belong to a single application.

A single database server (MySQL, PostgreSQL, SQL Server) can host many databases simultaneously. For example, a hosting server might run one database for a school application, another for an e-commerce site, and another for a blog — all completely isolated from each other.

One SQL Server — Multiple Databases
MySQL Server (localhost:3306)
school_db  — students, courses, grades tables
ecommerce_db  — products, orders, customers tables
blog_db  ← just created with CREATE DATABASE
information_schema  — system metadata (built-in)

Basic Syntax of CREATE DATABASE

The simplest form of the statement is just two words followed by a name:

SQL — Minimal CREATE DATABASE
CREATE DATABASE school_db;

That single statement creates an empty database named school_db on the connected server. Let's break down every element of the full syntax:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name ] [COLLATE collation_name ];
Square brackets [...] = optional  ·  Blue = required keywords  ·  [Green] = optional clauses  ·  Purple = your custom name  ·  Yellow = value strings
CREATE DATABASE
The DDL command that instructs the database server to allocate a new, empty database on disk and register it in the system catalog.
IF NOT EXISTS
An optional safety guard. Without it, running CREATE DATABASE school_db twice throws an error. With it, the server silently skips creation if the database already exists — perfect for scripts that run repeatedly.
database_name
The identifier for your new database. Use lowercase, underscores instead of spaces, and meaningful names like school_db, hr_system, or ecommerce_prod.
CHARACTER SET charset_name
Sets the default character encoding for all tables in this database. Common values: utf8mb4 (recommended — supports emoji and all Unicode), utf8, latin1.
COLLATE collation_name
Defines the sorting and comparison rules for string data. utf8mb4_unicode_ci means case-insensitive Unicode sorting (most used). _bin collations are case-sensitive.

Using IF NOT EXISTS — The Safety Clause

Running CREATE DATABASE on an already-existing database causes an error that halts your entire script. The IF NOT EXISTS clause prevents this, making your setup scripts idempotent — safe to run multiple times without side effects.

SQL — CREATE DATABASE with IF NOT EXISTS
-- Safe: runs without error even if school_db already exists
CREATE DATABASE IF NOT EXISTS school_db;
Best Practice
Always use IF NOT EXISTS in any database setup or migration script. This keeps your scripts re-runnable — critical for CI/CD pipelines, team onboarding scripts, and deployment automation.
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

Character Sets & Collations

A character set (charset) determines which characters can be stored. A collation controls how characters are compared and sorted. Setting these correctly at the database level means every table inherits them by default — saving you from setting them on every single table.

SQL — CREATE DATABASE with charset and collation (MySQL)
CREATE DATABASE IF NOT EXISTS school_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
Character Set Description Use When
utf8mb4 Full Unicode including emoji (4-byte) ✅ Recommended for all new databases
utf8 3-byte Unicode (no emoji support) Legacy systems; avoid for new projects
latin1 Western European characters only Very old systems; not recommended
Collation Case Sensitive? Accent Sensitive? Notes
utf8mb4_unicode_ci No No Most widely used; _ci = case-insensitive
utf8mb4_bin Yes Yes Binary comparison; _bin = exact match
utf8mb4_general_ci No No Faster but less accurate than unicode_ci

Switching Databases — The USE Statement

Creating a database doesn't automatically make it active. After creating it, you must tell SQL which database to work with using the USE statement. Every subsequent SQL command will operate inside that database until you switch again.

SQL — Full workflow: Create, Use, then Create Table
-- Step 1: Create the database
CREATE DATABASE IF NOT EXISTS school_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Step 2: Switch to the new database
USE school_db;

-- Step 3: Now create tables inside it
CREATE TABLE students (
    student_id  INT          PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE,
    city        VARCHAR(60)
);
1
CREATE DATABASE — Allocate the container
The server creates the database directory on disk and registers it in the system catalog. The database exists but contains no tables yet.
2
USE — Select the active database
Sets your session's active database context. All subsequent DDL and DML commands target this database unless you prefix the table name with database_name.table_name.
3
CREATE TABLE — Build structure inside it
Now you define the tables that will hold your data. Each table inherits the character set and collation from the database unless overridden at the table level.

CREATE DATABASE Across SQL Flavours

While the core syntax is similar, each major database system has its own nuances. Here's how to create a database in the four most widely used systems:

MySQL / MariaDB
CREATE DATABASE IF NOT EXISTS school_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;
PostgreSQL
CREATE DATABASE school_db
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE   'en_US.UTF-8'
    TEMPLATE   template0;
Microsoft SQL Server (T-SQL)
CREATE DATABASE school_db
COLLATE SQL_Latin1_General_CP1_CI_AS;
SQLite — no CREATE DATABASE command
-- SQLite creates a new database automatically when you open a file.
-- There is no CREATE DATABASE command.
-- In the CLI: sqlite3 school_db.db
-- In Python:  conn = sqlite3.connect('school_db.db')
Database System IF NOT EXISTS Charset Clause USE Equivalent
MySQL / MariaDB ✅ Supported CHARACTER SET … COLLATE … USE db_name;
PostgreSQL ❌ Not supported* ENCODING 'UTF8' \c db_name (psql) or reconnect
SQL Server ❌ Use IF EXISTS check COLLATE USE db_name;
SQLite N/A Always UTF-8 Open the .db file

*PostgreSQL 9.3+ supports CREATE DATABASE IF NOT EXISTS via a workaround using DO blocks or procedural SQL.

Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

Listing & Deleting Databases

Once you've created databases you'll often need to list existing ones or remove databases that are no longer needed. Here are the key commands:

SQL — List, Inspect and Drop Databases
-- List all databases on the server (MySQL)
SHOW DATABASES;

-- Show the CREATE statement for an existing database
SHOW CREATE DATABASE school_db;

-- Check which database is currently selected
SELECT DATABASE();

-- Drop (permanently delete) a database — USE WITH CAUTION!
DROP DATABASE IF EXISTS school_db;
Warning — DROP DATABASE is Permanent
DROP DATABASE immediately and permanently deletes the database, all its tables, and all their data. There is no undo — always verify you are connected to the correct server and database before executing this command. In production, only DBAs with explicit permission should run this.

Database Naming Rules & Conventions

Choosing a good database name matters. On Linux, MySQL database names are case-sensitive (because they map to directory names), while on Windows they are not. Following a consistent convention avoids cross-platform bugs.

Use lowercase + underscores
school_db, ecommerce_prod, hr_v2 — readable, consistent, and works the same on all operating systems.
Include the environment
Suffix or prefix with _dev, _staging, _prod so you can instantly tell which environment a database belongs to.
Avoid spaces and special characters
Names with spaces require backtick quoting (`my database`) — error-prone and messy. Avoid hyphens, dots, and special symbols entirely.
Don't use reserved words
Names like database, table, or select are reserved SQL keywords. Using them as identifiers requires quoting and causes confusion.

Try It Yourself

This in-browser runner simulates a SQL environment with a pre-loaded databases table showing what a SHOW DATABASES result might look like. Explore the sample queries or write your own SELECT statements.

SQL Query Runner In-Browser
Available tables: databases (db_name, owner, charset, collation, size_mb)  ·  Try:
Results will appear here after you run a query
Quick Check — Chapter 6

Which clause prevents an error when creating a database that already exists?

Chapter Summary
  • A database is a named container that holds all your tables, views, procedures, and other SQL objects. A single server can host many databases simultaneously.
  • CREATE DATABASE database_name; is the minimal syntax — it creates an empty database on the connected server.
  • Use IF NOT EXISTS in all scripts to make them idempotent — safe to run multiple times without throwing an error: CREATE DATABASE IF NOT EXISTS school_db;
  • CHARACTER SET controls which characters can be stored. Always use utf8mb4 for new databases to support full Unicode and emoji.
  • COLLATE controls how strings are compared and sorted. utf8mb4_unicode_ci is the recommended default (case-insensitive).
  • After creating a database, switch to it with USE database_name; before creating tables or inserting data.
  • Use SHOW DATABASES; to list all databases and DROP DATABASE IF EXISTS db_name; to permanently delete one.
  • SQLite has no CREATE DATABASE — a new database is automatically created when you open or create a .db file.
Advertisement
📢 Ad space · 728 × 90 · Replace slot ID above

Rate Us on Google

4.8 / 5
Enjoying this tutorial?
Your 5-star review helps other learners discover our free tutorials and encourages us to keep creating quality content.
80+ reviews
Write a Google Review
Join WhatsApp Channel