CREATE DATABASE in MySQL

CREATE DATABASE is a MySQL statement used to create a new database.

CREATE DATABASE school;

This creates a database named school.

The basic syntax is:

CREATE DATABASE database_name;

Replace database_name with the name you want to use.

For a school management system, you could create:

CREATE DATABASE school;

The database can later contain tables such as students, teachers, courses, and fees.

For a library management system, you could create:

CREATE DATABASE library;

The database could contain books, students, issues, and returns tables.

The database name identifies the database on the MySQL server.

Examples:

school
library
company
hospital
shop

Use meaningful names that describe the purpose of the database.

A semicolon normally terminates a SQL statement.

CREATE DATABASE school;

The semicolon indicates the end of the statement in the MySQL command-line client and many SQL tools.

A MySQL server can contain multiple databases.

CREATE DATABASE school;
CREATE DATABASE library;
CREATE DATABASE company;

Each statement creates a separate database.

After creating a database, use SHOW DATABASES to see databases accessible to the current user.

SHOW DATABASES;

Use IF NOT EXISTS when you want MySQL to avoid an error if the database already exists.

CREATE DATABASE IF NOT EXISTS school;

If school already exists, MySQL will not create another database with the same name.

This option is useful in installation scripts and application setup scripts that may be executed more than once.

CREATE DATABASE IF NOT EXISTS student_management;

It helps make the script safer when the database may already exist.

If you run a normal CREATE DATABASE statement for a database that already exists, MySQL can report an error.

CREATE DATABASE school;

Using IF NOT EXISTS can avoid this specific situation.

Creating a database does not automatically make it the current database.

Use the USE statement:

CREATE DATABASE school;

USE school;

After using a database, you can check the current database with:

SELECT DATABASE();

For example, after USE school;, the result should identify school as the current database.

After creating and selecting a database, you can create tables inside it.

CREATE DATABASE school;

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

A student management application could use:

CREATE DATABASE student_management;

After selecting it, tables such as students, courses, attendance, and fees can be created.

A company application could use:

CREATE DATABASE company_db;

It could contain employees, departments, projects, and salary tables.

A hospital management application could use:

CREATE DATABASE hospital_db;

Tables could later be created for patients, doctors, appointments, and billing.

An online shopping application could use:

CREATE DATABASE ecommerce_db;

Possible tables include products, customers, orders, and payments.

1. Connect to MySQL
2. Create the database
3. Show databases
4. Select the database
5. Create tables
6. Insert data
7. Query the data

You can execute the CREATE DATABASE statement in MySQL Workbench's SQL Editor.

CREATE DATABASE school;

After execution, refresh the Schemas panel to see the database.

The same SQL statement can be executed from the MySQL command-line client.

mysql> CREATE DATABASE school;

SQL works independently of whether you use Workbench or the command line to send it to the MySQL server.

A MySQL user must have appropriate privileges to create databases.

If the user does not have the required permission, MySQL may reject the CREATE DATABASE operation.

Use names that clearly describe the application or project.

Good examples include:

school_db
library_db
student_management
inventory_db

Clear naming makes projects easier to understand and maintain.

Avoid unnecessarily confusing database names.

For example, instead of:

db1
database123
testabc

prefer names that describe the purpose of the database.

A practical database setup statement can be:

CREATE DATABASE IF NOT EXISTS school_db;

This is commonly useful when preparing an application database through an installation or setup script.

CREATE DATABASE IF NOT EXISTS school;

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

Here, the database is created first and then two tables are created inside it.

You can verify that the database exists with:

SHOW DATABASES;

Then select it:

USE school;

Finally, check its tables:

SHOW TABLES;

Common issues while creating a database include:

• Database already exists
• Insufficient privileges
• Invalid database name
• SQL syntax error
• MySQL Server is not running
• Connection problem

CREATE DATABASE IF NOT EXISTS student_management;

SHOW DATABASES;

USE student_management;

SELECT DATABASE();

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    mobile VARCHAR(15)
);

SHOW TABLES;

This example creates a database, checks available databases, selects the new database, verifies it, creates a table, and displays the tables.

Suppose you are developing a library management system. First create the database:

CREATE DATABASE IF NOT EXISTS library_db;

USE library_db;

CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(150),
    author VARCHAR(100)
);

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    mobile VARCHAR(15)
);

SHOW TABLES;

The library_db database now provides a container for the tables required by the library application.