SQL — Create DB

Basic Syntax

CREATE DATABASE database_name;

Create If Not Exists

Prevents errors if the database already exists:

CREATE DATABASE IF NOT EXISTS my_store;

Selecting a Database

After creating it, tell MySQL which database to use:

USE my_store;

Full Example

CREATE DATABASE IF NOT EXISTS shop;
USE shop;

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2)
);

Character Set and Collation

CREATE DATABASE my_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

• CHARACTER SET — defines which characters can be stored (use utf8mb4 for full Unicode support)
• COLLATE — defines how characters are sorted and compared

Show All Databases

SHOW DATABASES;

Drop a Database

DROP DATABASE my_store;

-- Safe version
DROP DATABASE IF EXISTS my_store;

PostgreSQL Syntax

PostgreSQL uses CREATE DATABASE but with slightly different options:

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

Naming Rules

• Must start with a letter or underscore
• Can contain letters, numbers, and underscores
• Cannot contain spaces or special characters
• Maximum 64 characters
• Case sensitivity depends on the OS

Best Practices

• Use descriptive, lowercase names with underscores (user_management not UM)
• Create a separate database per environment (dev, staging, production)
• Always use utf8mb4 for character encoding
• Use IF NOT EXISTS in scripts for idempotency

Practice

1. Create a database called school
2. Create a database with utf8mb4 encoding
3. Show all databases and verify yours exists
4. Drop the database safely with IF EXISTS