Home / SQL / CREATE DATABASE

CREATE DATABASE

Overview

The CREATE DATABASE statement is used to create a new database. A database is a container for storing tables, views, indexes, and other database objects.

Creating a database is typically the first step in setting up a new database system for your application.

Basic Syntax

CREATE DATABASE Syntax

CREATE DATABASE database_name;

Examples

Example 1: Simple Database Creation

Create a new database

CREATE DATABASE company_db;

Example 2: Database with Character Set

Specify character set (MySQL)

CREATE DATABASE company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Example 3: Check if Database Exists

Avoid errors if database exists

-- MySQL/SQL Server
CREATE DATABASE IF NOT EXISTS company_db;

-- PostgreSQL
CREATE DATABASE company_db;

Database Naming Conventions

Best practices for naming databases:

Use descriptive names: Reflect the purpose of the database
Lowercase letters: Avoid case sensitivity issues
Use underscores: Separate words with underscores
Avoid reserved words: Don't use SQL keywords
Keep it short: But still meaningful

Good vs Bad Names

✅ Good:
company_db
ecommerce_store
student_management

❌ Bad:
Database        (reserved word)
my-db          (hyphens may cause issues)
123database    (starts with number)

Selecting a Database

After creating a database, you need to select (use) it before creating tables:

USE Statement

-- Create database
CREATE DATABASE company_db;

-- Select/use the database
USE company_db;

-- Now you can create tables in this database
CREATE TABLE employees (...);

Database-Specific Syntax

Different database systems have varying syntax for CREATE DATABASE:

MySQL

MySQL CREATE DATABASE

CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

PostgreSQL

PostgreSQL CREATE DATABASE

CREATE DATABASE company_db
WITH ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';

SQL Server

SQL Server CREATE DATABASE

CREATE DATABASE company_db
ON (NAME = 'company_db', FILENAME = 'C:\Data\company_db.mdf')
LOG ON (NAME = 'company_db_log', FILENAME = 'C:\Data\company_db_log.ldf');

Viewing Databases

To see all databases in your system:

List all databases

-- MySQL
SHOW DATABASES;

-- PostgreSQL
\l

-- SQL Server
SELECT name FROM sys.databases;

Permissions Required

Creating a database typically requires administrative privileges:

MySQL: Requires CREATE privilege
PostgreSQL: Requires CREATEDB privilege
SQL Server: Requires CREATE DATABASE permission

Important Considerations

Backup first: Database creation is irreversible without backup
Choose character set: Important for internationalization
Set collation: Determines sorting and comparison rules
Plan structure: Design database before creating it
Check disk space: Ensure sufficient storage

Common Errors

Database already exists: Use IF NOT EXISTS or DROP first
Insufficient privileges: Need admin rights
Invalid name: Contains special characters or reserved words
Disk full: Not enough space on server

Best Practices

Use meaningful names: Reflect the database purpose
Set appropriate character set: UTF8 or UTF8MB4 for international support
Document purpose: Note why the database was created
Plan ahead: Design schema before creation
Version control: Track database creation scripts

Next Steps

After creating a database, learn about creating tables with CREATE TABLE, or learn about removing databases with DROP DATABASE.

← Case Drop Db →