Creating & Listing Databases0%

Creating & Listing Databases

Beginner12 min read•Updated: 2026-09-12

Creating & Listing Databases: MySQL DDL Administration

In MySQL, a Database (synonymous with a Schema) is the highest-level logical container that houses your tables, views, stored procedures, and triggers. Before creating any tables or storing customer records, you must master the fundamental Data Definition Language (DDL) commands for creating and inspecting databases.

1. Listing Existing Databases (`SHOW DATABASES`)

To view all databases currently present on your MySQL server instance:

1SHOW DATABASES;

Understanding System Databases in MySQL

When you execute SHOW DATABASES; on a fresh MySQL instance, you will notice several default system databases:

information_schema: A virtual metadata database providing read-only tables describing table structures, column types, and server privileges.
mysql: The internal administrative core storing user accounts, password hashes, and global access permissions.
performance_schema: An engine that monitors low-level server performance metrics, mutex latencies, and thread execution timings.
sys: A set of user-friendly administrative views built on top of performance_schema for DBAs.

Important Warning

[!CAUTION] Never manually delete or directly modify tables in the mysql or information_schema system databases!

2. Creating Databases (`CREATE DATABASE`)

The basic syntax to create a new database is:

1CREATE DATABASE database_name;

Production-Grade Database Creation

In production environments, you should always include character set specifications and safety checks:

1-- Production Best Practice:
2CREATE DATABASE IF NOT EXISTS ecommerce_platform
3    CHARACTER SET utf8mb4
4    COLLATE utf8mb4_unicode_ci;

Key Syntax Elements:

1
IF NOT EXISTS: Prevents an error from halting your script if the database already exists.
2
CHARACTER SET utf8mb4: Guarantees full 4-byte UTF-8 encoding (mandatory for supporting all international scripts, Hindi/Devanagari, mathematical symbols, and modern emojis).
3
COLLATE utf8mb4_unicode_ci: Defines the alphabetical sorting and comparison rules (case-insensitive Unicode standard).

3. Database Naming Conventions

When naming databases, adhere strictly to industry standards:

Use Snake Case: Always use lowercase letters separated by underscores (my_company_db rather than MyCompanyDb).
Operating System Case Sensitivity: On Linux, MySQL database names correspond directly to directory names on the filesystem, making them case-sensitive! On Windows, they are case-insensitive. Using strictly lowercase prevents cross-platform migration headaches.
Avoid Reserved Keywords: Never name a database using SQL reserved words like select, table, or database. If necessary, wrap names in backticks (`` database ``).

4. Querying Database Metadata from `information_schema`

Instead of just running SHOW DATABASES;, you can query the database list with standard SQL filters:

1-- Query all user databases excluding system schemas:
2SELECT schema_name, default_character_set_name, default_collation_name
3FROM information_schema.schemata
4WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

5. Best Practices & Common Pitfalls

Avoid the Default Character Set (latin1): Older MySQL tutorials default to latin1 or utf8 (which in MySQL was limited to 3-byte characters). Always explicitly specify utf8mb4.
Idempotent Migration Scripts: Always guard database creation statements with IF NOT EXISTS in CI/CD migration scripts.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL statement lists all databases available on the current MySQL server?

Question 2

Why is utf8mb4 preferred over older utf8 (utf8mb3) encoding in MySQL?

Question 3

Which system database contains read-only metadata views detailing tables, columns, and constraints across all databases?

Question 4

Why should database names always be written in lowercase characters with underscores (snake_case)?

Question 5

What is the function of the COLLATE clause when creating a database?

Next Lesson

Selecting & Dropping Databases

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Writing & Running Your First SQL Script	Selecting & Dropping Databases

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Selecting & Dropping Databases

Creating & Listing Databases

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Creating & Listing Databases: MySQL DDL Administration

1. Listing Existing Databases (`SHOW DATABASES`)

To view all databases currently present on your MySQL server instance:

1SHOW DATABASES;

Understanding System Databases in MySQL

When you execute SHOW DATABASES; on a fresh MySQL instance, you will notice several default system databases:

information_schema: A virtual metadata database providing read-only tables describing table structures, column types, and server privileges.
mysql: The internal administrative core storing user accounts, password hashes, and global access permissions.
performance_schema: An engine that monitors low-level server performance metrics, mutex latencies, and thread execution timings.
sys: A set of user-friendly administrative views built on top of performance_schema for DBAs.

Important Warning

[!CAUTION] Never manually delete or directly modify tables in the mysql or information_schema system databases!

2. Creating Databases (`CREATE DATABASE`)

The basic syntax to create a new database is:

1CREATE DATABASE database_name;

Production-Grade Database Creation

In production environments, you should always include character set specifications and safety checks:

1-- Production Best Practice:
2CREATE DATABASE IF NOT EXISTS ecommerce_platform
3    CHARACTER SET utf8mb4
4    COLLATE utf8mb4_unicode_ci;

Key Syntax Elements:

1
IF NOT EXISTS: Prevents an error from halting your script if the database already exists.
2
CHARACTER SET utf8mb4: Guarantees full 4-byte UTF-8 encoding (mandatory for supporting all international scripts, Hindi/Devanagari, mathematical symbols, and modern emojis).
3
COLLATE utf8mb4_unicode_ci: Defines the alphabetical sorting and comparison rules (case-insensitive Unicode standard).

3. Database Naming Conventions

When naming databases, adhere strictly to industry standards:

Use Snake Case: Always use lowercase letters separated by underscores (my_company_db rather than MyCompanyDb).
Operating System Case Sensitivity: On Linux, MySQL database names correspond directly to directory names on the filesystem, making them case-sensitive! On Windows, they are case-insensitive. Using strictly lowercase prevents cross-platform migration headaches.
Avoid Reserved Keywords: Never name a database using SQL reserved words like select, table, or database. If necessary, wrap names in backticks (`` database ``).

4. Querying Database Metadata from `information_schema`

Instead of just running SHOW DATABASES;, you can query the database list with standard SQL filters:

1-- Query all user databases excluding system schemas:
2SELECT schema_name, default_character_set_name, default_collation_name
3FROM information_schema.schemata
4WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

5. Best Practices & Common Pitfalls

Avoid the Default Character Set (latin1): Older MySQL tutorials default to latin1 or utf8 (which in MySQL was limited to 3-byte characters). Always explicitly specify utf8mb4.
Idempotent Migration Scripts: Always guard database creation statements with IF NOT EXISTS in CI/CD migration scripts.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL statement lists all databases available on the current MySQL server?

Question 2

Why is utf8mb4 preferred over older utf8 (utf8mb3) encoding in MySQL?

Question 3

Which system database contains read-only metadata views detailing tables, columns, and constraints across all databases?

Question 4

Why should database names always be written in lowercase characters with underscores (snake_case)?

Question 5

What is the function of the COLLATE clause when creating a database?

Next Lesson

Selecting & Dropping Databases

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Writing & Running Your First SQL Script	Selecting & Dropping Databases

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Creating & Listing Databases: MySQL DDL Administration

1. Listing Existing Databases (SHOW DATABASES)

Understanding System Databases in MySQL

2. Creating Databases (CREATE DATABASE)

Production-Grade Database Creation

Key Syntax Elements:

3. Database Naming Conventions

4. Querying Database Metadata from information_schema

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Selecting & Dropping Databases

Related Lessons

Practice Quiz

Loading Page Content...

Creating & Listing Databases: MySQL DDL Administration

1. Listing Existing Databases (SHOW DATABASES)

Understanding System Databases in MySQL

2. Creating Databases (CREATE DATABASE)

Production-Grade Database Creation

Key Syntax Elements:

3. Database Naming Conventions

4. Querying Database Metadata from information_schema

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Selecting & Dropping Databases

Related Lessons

Practice Quiz

1. Listing Existing Databases (`SHOW DATABASES`)

2. Creating Databases (`CREATE DATABASE`)

4. Querying Database Metadata from `information_schema`

1. Listing Existing Databases (`SHOW DATABASES`)

2. Creating Databases (`CREATE DATABASE`)

4. Querying Database Metadata from `information_schema`