Creating a database is one of the first things you do when setting up a new project or installing database software.
Whether you’re using Oracle, SQL Server, MySQL, or PostgreSQL (or any other database), you’ll need to create a database at some point.
A database is a collection of tables and other objects. Many database vendors allow you to have multiple databases as part of the overall database engine.
Let’s take a look at the create database process.
SQL Create Database
Here’s a quick reference on how to do it in each database:
|MySQL||CREATE DATABASE database_name;|
|SQL Server||CREATE DATABASE database_name;|
|PostgreSQL||CREATE DATABASE database_name;|
|Oracle||N/A – create a schema and assign privileges|
You can find more details below.
How to Create a Database in SQL
The easiest way to create a new database is to use the CREATE DATABASE command:
CREATE DATABASE database_name;
Add in the name of your database, run the command, and the new database is created.
This statement will work on MySQL, SQL Server, and PostgreSQL.
For example, to create a new database called employee, run this command:
CREATE DATABASE employee;
A new database called employee is now created. You can then create tables and other objects inside this database.
For more details on creating a database in MySQL, check out this guide: How to Create a Database in MySQL.
What about Oracle?
Oracle Create Database
The concept of a database exists differently in Oracle.
Each Oracle installation has a single database, and within that database, there can be multiple schemas. Each schema is tied to a user, and the schemas can have tables and database objects.
So, to create a set of related tables and objects, you would create a schema instead of a database.
To do this, the steps are:
- Create a new user:
CREATE USER newuser IDENTIFIED BY newpassword;
- Grant permissions to the new user
GRANT CREATE SESSION TO newuser; GRANT CREATE TABLE TO newuser; GRANT CREATE VIEW TO newuser;
You may want to grant other permissions as needed, such as creating sequences or synonyms.
- Create tables
Follow the same steps as normal to create tables.
Once you create a database, you can see if it has been created by looking at the list of databases on the system.
The way to do that depends on the database you’re running.
To see a list of the databases in MySQL, run this command:
It shows a list of all databases:
To see a list of the databases in SQL Server, run this command:
SELECT name FROM master.sys.databases;
This will show a list of all databases.
If you’re using a command line, you can use either the \l or \list command to list databases.
If you’re using an IDE such as pgAdmin or DBeaver, you can run this SQL command:
SELECT datname FROM pg_database;
You’ll see a list of all databases:
Because individual databases work differently on Oracle, there is no real “show databases” query on Oracle. You can show a list of users by querying dba_users or all_users, but it’s not really the same thing.
Check if Database Is Created
Is there a way to create a new database if it doesn’t already exist?
You can sometimes do this with procedures or tables.
It works on some database engines as well.
To check if a database exists before creating it in MySQL:
CREATE DATABASE IF NOT EXISTS database_name;
Adding the IF NOT EXISTS is a simple way to do this.
To check if a database exists before creating it in SQL Server:
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'database_name') BEGIN CREATE DATABASE database_name; END; GO
This will check if the database exists before creating it.
To check if a database exists before creating it in PostgreSQL:
SELECT 'CREATE DATABASE database_name' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'database_name')\gexec
According to the manual, the gexec command sends the query buffer to the server. So, it will execute the create database command if it does not exist.
Because databases work differently in Oracle, this command isn’t relevant.
So, that’s how you can create a new database in different database engines.
2 thoughts on “SQL Create Database: A How-To Guide”
Hi Ben, for a newbee like me this is another simple but great explanation. Thank you so much!