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 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.
Basic Syntax of CREATE DATABASE
The simplest form of the statement is just two words followed by a name:
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:
[...] =
optional ·
Blue = required keywords ·
[Green] = optional clauses ·
Purple = your custom name ·
Yellow = value strings
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.
school_db,
hr_system, or
ecommerce_prod.
utf8mb4 (recommended — supports emoji and
all Unicode), utf8,
latin1.
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.
-- Safe: runs without error even if school_db already exists
CREATE DATABASE IF NOT EXISTS school_db;
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.
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.
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.
-- 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)
);
database_name.table_name.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:
CREATE DATABASE IF NOT EXISTS school_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
CREATE DATABASE school_db
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0;
CREATE DATABASE school_db
COLLATE SQL_Latin1_General_CP1_CI_AS;
-- 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.
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:
-- 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;
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.
school_db,
ecommerce_prod,
hr_v2 — readable, consistent, and
works the same on all operating systems.
_dev,
_staging, _prod
so you can instantly tell which environment a database belongs to.
`my database`) — error-prone and messy.
Avoid hyphens, dots, and special symbols entirely.
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.
databases (db_name, owner, charset, collation, size_mb)
· Try:
Which clause prevents an error when creating a database that already exists?
- 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
utf8mb4for new databases to support full Unicode and emoji. - COLLATE controls how strings are compared and sorted.
utf8mb4_unicode_ciis 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 andDROP 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.dbfile.
Rate Us on Google
Join Our Learning Community
Connect with thousands of learners, get daily tips, ask questions and stay updated with new tutorials.