← Back to Chapters

MySQL Database

?️ MySQL Database

Learn how to create, select, and delete databases in MySQL using DDL commands.

DDL – Data Definition Language

⚡ Quick Overview

  • CREATE DATABASE – creates a new, empty database container.
  • USE – switches the current working database.
  • DROP DATABASE – permanently deletes a database and everything inside it.
  • Database-level commands in MySQL are part of DDL because they define the structure, not the data.

? Key Concepts

  • Database: A logical container that holds tables, views, procedures, and data.
  • Current database: The database selected via USE; all unqualified queries run here.
  • DDL: Commands that define or modify schema objects (databases, tables, etc.).
  • Irreversible operations: DROP DATABASE removes data permanently (unless backed up).

? Syntax – Database-Level DDL

These are the basic MySQL commands for creating, selecting, and deleting a database:

? View Code Example 
-- Basic database commands in MySQL
-- Create a new database
CREATE DATABASE database_name;
-- Delete an existing database
DROP DATABASE database_name;
-- Select a database for subsequent queries
USE database_name;

You can also avoid errors when the database already exists by using: CREATE DATABASE IF NOT EXISTS database_name;

? Full Example – Creating & Using a Database

This example shows how to create a database, switch to it, create a table, and finally drop the database.

? View Code Example 
-- Create and use EmployeeDB database
CREATE DATABASE EmployeeDB;

-- Switch to the new database
USE EmployeeDB;

-- Create a table inside EmployeeDB
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Salary DECIMAL(10,2)
);

-- Drop the database (this deletes all data inside it)
DROP DATABASE EmployeeDB;

? Useful Helper Commands

  • Check which database you are currently using:
? View Code Example 
-- Shows the name of the currently selected database
SELECT DATABASE();
  • List all databases available on the server:
? View Code Example 
-- Shows all databases on the current MySQL server
SHOW DATABASES;

? Explanation

  • CREATE DATABASE: Creates a new, empty database container. Think of it like creating a folder that will hold tables, views, stored procedures, and data for a specific application.
  • USE: Switches the current working database to the one you specify. All subsequent queries such as CREATE TABLE, INSERT, or SELECT will run inside this database unless you explicitly reference another database.
  • DROP DATABASE: Deletes the database and all its objects (tables, views, data, etc.) permanently. This command should be used with extreme caution because once dropped, the data cannot be recovered unless you have a backup.
  • Multiple databases: You can create different databases for different projects, such as school_db, inventory_db, or shop_db. Each database keeps its own tables and data independent.
  • Part of DDL: Database management is part of DDL (Data Definition Language) because it defines the schema and structure rather than manipulating the actual data rows.

? Sample Output – SHOW DATABASES;

After running SHOW DATABASES;, you might see something like:

information_schema
mysql
performance_schema
sys
EmployeeDB

This confirms that EmployeeDB was created successfully and is available on the server.

✅ Tips & Best Practices

  • Always back up your database before running DROP DATABASE.
  • Use the USE statement to select the correct database before performing any queries.
  • Prefer CREATE DATABASE IF NOT EXISTS db_name; to avoid errors if the database already exists.
  • Avoid using reserved keywords (like order or select) as database names. If you must, wrap them in backticks, e.g., `order`.
  • Use descriptive names like hr_db, inventory_db, etc., to keep projects organized.

? Try It Yourself

  • Create a new database called SchoolDB, and inside it create a table Students with columns: StudentID, Name, Class, and DOB.
  • Run SHOW DATABASES; and verify that SchoolDB appears in the list.
  • Use SELECT DATABASE(); to confirm which database is currently selected.
  • (Optional – on a test server) Practice DROP DATABASE SchoolDB; and observe that the database is removed from SHOW DATABASES;.