CodeCapsule

SQL CREATE DATABASE

by

Here is a clear and practical guide to the SQL CREATE DATABASE statement, with syntax for MySQL, PostgreSQL, SQL Server, and Oracle.

✅ What CREATE DATABASE Does

CREATE DATABASE creates a new database on the server.
Inside it, you can then create tables, views, stored procedures, etc.

🔹 ANSI SQL Basic Syntax

CREATE DATABASE database_name;

Most SQL engines support this form with small variations.

🟦 MySQL — CREATE DATABASE

CREATE DATABASE mydb;

Create with charset and collation:

CREATE DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Check if database exists:

CREATE DATABASE IF NOT EXISTS mydb;

🟪 PostgreSQL — CREATE DATABASE

CREATE DATABASE mydb;

Create with owner and encoding:

CREATE DATABASE mydb
WITH OWNER = admin_user
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

List databases:

\l

🟧 SQL Server — CREATE DATABASE

CREATE DATABASE mydb;

Create with file configuration:

CREATE DATABASE mydb
ON (
NAME = mydb_data,
FILENAME = 'C:\SQLData\mydb.mdf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
LOG ON (
NAME = mydb_log,
FILENAME = 'C:\SQLData\mydb_log.ldf'
);

🟨 Oracle — CREATE DATABASE

(Usually done via DBCA or admin tools, but SQL is possible.)

CREATE DATABASE mydb
USER SYS IDENTIFIED BY strongpassword
USER SYSTEM IDENTIFIED BY strongpassword
LOGFILE GROUP 1 ('/u01/oradata/mydb/redo1.log') SIZE 100M,
GROUP 2 ('/u01/oradata/mydb/redo2.log') SIZE 100M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16;

⚠ Oracle database creation is complex and typically done by DBAs.

🔥 Selecting / Switching Databases

MySQL:

USE mydb;

PostgreSQL:

\c mydb

SQL Server:

USE mydb;

🧠 Best Practices

✔ Use meaningful names (e.g., hr_db, inventory, analytics)
✔ Choose consistent character sets (UTF8/UTF8MB4 recommended)
✔ Ensure permissions for the database owner
✔ Follow naming conventions (no spaces, lowercase recommended)
✔ Avoid using reserved keywords as database names

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *