CREATE DATABASE

A database is an organized collection of data that can be stored, accessed, updated, and managed by a database management system.

For example, a school application may have a database named school containing tables such as:

• students
• teachers
• courses
• attendance
• fees

The basic syntax is:

CREATE DATABASE database_name;

For example:

CREATE DATABASE school;

This statement requests the creation of a database named school.

Suppose we are building a student management system. We can create a database named student_management.

CREATE DATABASE student_management;

After the database is created, tables can be created inside it.

In MySQL, IF NOT EXISTS can be used to avoid an error when the specified database already exists.

CREATE DATABASE IF NOT EXISTS school;

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

A database name should clearly describe the purpose of the database.

Examples:

• school
• library
• hospital
• ecommerce
• banking
• student_management

Using meaningful names makes database administration easier.

In a MySQL client such as MySQL Workbench or the MySQL command-line client, you can execute:

CREATE DATABASE library;

After successful execution, the database becomes available on the connected MySQL server, subject to the user's permissions.

In MySQL, the SHOW DATABASES statement can be used to list databases that the current user can see.

SHOW DATABASES;

The result displays databases available to the current MySQL account.

Creating a database does not automatically mean that it is the active database for subsequent statements.

In MySQL, use the USE statement to select a database.

USE school;

After this, subsequent table operations are performed in the selected database unless another database is explicitly specified.

You can create and then select a database as follows:

CREATE DATABASE school;

USE school;

Now the school database is selected in MySQL.

Once the database is selected, you can create tables inside it.

CREATE DATABASE school;

USE school;

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

The students table is created inside the selected school database.

A database can contain multiple related tables.

CREATE DATABASE school;

USE school;

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

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

Both tables belong to the selected school database.

A useful way to understand the relationship is:

Database
   |
   |---- Table 1
   |
   |---- Table 2
   |
   |---- Table 3
   |
   |---- Table 4

The database acts as a container for related database objects.

Creating a database generally requires appropriate privileges.

If the current database user does not have permission to create databases, the database server may return an access or permission error.

Database administrators control which users can create, modify, or remove database objects.

If you are using phpMyAdmin, you can usually create a database through its graphical interface.

You can also open the SQL interface and execute a statement such as:

CREATE DATABASE school;

The exact interface can vary depending on the phpMyAdmin version and configuration.

MySQL Workbench provides a SQL editor where you can write and execute database statements.

You can enter:

CREATE DATABASE school;

and execute the statement after connecting to a MySQL server with suitable permissions.

Good database names should be simple, meaningful, and consistent.

• Use descriptive names.
• Avoid unnecessary spaces.
• Use a consistent naming style.
• Avoid confusing abbreviations.
• Follow the naming conventions used by your project.

Examples:

school_management
library_db
ecommerce
student_management

It is generally better to avoid unnecessary special characters and spaces in database names.

For example, prefer:

student_management

instead of names containing unnecessary spaces or punctuation.

If identifiers require quoting, the quoting rules depend on the database system.

Suppose we are creating a library management application.

CREATE DATABASE library_db;

Then select it:

USE library_db;

Now we can create tables such as:

• books
• students
• members
• book_issues
• payments

An online shopping application may use a database named ecommerce.

CREATE DATABASE ecommerce;

USE ecommerce;

Tables may include:

• customers
• products
• orders
• order_items
• payments

A school management system can use a database such as school_db.

CREATE DATABASE school_db;

USE school_db;

Possible tables include:

• students
• teachers
• classes
• attendance
• fees
• exams

For scripts that may be run more than once, MySQL supports:

CREATE DATABASE IF NOT EXISTS library_db;

This is useful when you want the script to create the database only if it does not already exist.

In MySQL, the DATABASE() function can be used to see the currently selected database.

SELECT DATABASE();

If no database is selected, the result can be NULL.

The DROP DATABASE statement can remove a database.

DROP DATABASE school;

MySQL supports IF EXISTS with DROP DATABASE.

DROP DATABASE IF EXISTS test_db;

This avoids an error if the specified database does not exist.

The following example creates a database and two tables.

CREATE DATABASE school_db;

USE school_db;

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

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

This creates the school_db database and then creates the students and courses tables inside it.

Beginners may encounter errors while creating databases.

• The database name may already exist.
• The user may not have CREATE privileges.
• The SQL statement may contain a syntax error.
• The database server may not be running.
• The client may not be connected to the server.

A basic workflow for a MySQL beginner is:

Connect to MySQL
       ↓
Create Database
       ↓
Select Database
       ↓
Create Tables
       ↓
Insert Data
       ↓
Run SQL Queries

CREATE DATABASE IF NOT EXISTS school_db;

USE school_db;

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

INSERT INTO students
(id, name, age, course)
VALUES
(1, 'Rahul', 21, 'Python'),
(2, 'Priya', 22, 'SQL');

SELECT * FROM students;

This example demonstrates the basic workflow of creating a database, selecting it, creating a table, inserting records, and retrieving the records.