Creating Databases
Learn how to create, select, and delete databases using SQL commands.
Introduction to Creating Databases
Before we can store data in tables, we need to create a database to hold those tables. Think of a database as a container or folder that organizes related tables together.
For example, an e-commerce application might have a database called "ecommerce" containing tables for products, customers, orders, and reviews.
CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new database. The syntax is simple:
CREATE DATABASE company;This creates a new database named "company". Database names should be:
- Descriptive of their purpose
- Written in lowercase (convention)
- Use underscores instead of spaces (
my_databasenotmy database) - Avoid special characters
Note: In some SQL dialects like PostgreSQL and SQL Server, you might see CREATE SCHEMA used instead of or alongside CREATE DATABASE. They serve similar purposes but have subtle differences depending on the database system.
Checking Existing Databases
Before creating a database, you might want to see what databases already exist. Different SQL systems have different commands for this:
-- MySQL/MariaDB
SHOW DATABASES;-- PostgreSQL
SELECT datname FROM pg_database;CREATE DATABASE IF NOT EXISTS
To avoid errors when a database already exists, you can use the IF NOT EXISTS clause:
CREATE DATABASE IF NOT EXISTS company;If the database "company" already exists, this command will succeed without throwing an error. This is especially useful in scripts that might run multiple times.
Best Practice: Always use IF NOT EXISTS in scripts and automated processes to make them idempotent (safe to run multiple times).
Selecting a Database
After creating a database, you need to "select" or "use" it before you can create tables or query data within it.
-- MySQL/MariaDB
USE company;-- PostgreSQL (connect when starting psql)
c companyOnce you've selected a database, all subsequent SQL commands will operate on tables within that database.
DROP DATABASE Statement
The DROP DATABASE statement permanently deletes a database and all its tables and data. Use with extreme caution!
DROP DATABASE company;Warning: DROP DATABASE is irreversible! All data in the database will be permanently deleted. Always make backups before dropping a database.
For safety, you can use IF EXISTS to avoid errors if the database doesn't exist:
DROP DATABASE IF EXISTS old_test_db;Database Naming Conventions
Following good naming conventions makes your databases easier to understand and maintain:
Good Names:
ecommerce- Clear and concisecustomer_portal- Descriptive, uses underscoresanalytics_2024- Includes version/year when neededinventory_system- Describes purpose
Poor Names:
db1- Not descriptiveMy Database- Contains spaces (will cause errors or require quotes)tHiS-iS-mY-dB- Inconsistent casing, uses hyphenstemp- Too vague
Rules of thumb:
- Use lowercase letters
- Use underscores for spaces
- Be descriptive but concise
- Avoid special characters
- Don't start with numbers
Pro Tip: Establish naming conventions early in your project and document them. Consistency across your databases makes maintenance much easier!
Complete Example - Database Lifecycle
Here's a complete workflow showing database creation, usage, and deletion:
-- 1. Create a new database
CREATE DATABASE IF NOT EXISTS company;
-- 2. Select the database
USE company;
-- 3. Check which database is currently selected (MySQL)
SELECT DATABASE();-- Later, if needed: Drop the database
-- (Commented out for safety!)
-- DROP DATABASE IF EXISTS company;Now that you know how to create and manage databases, we're ready to create tables within them!