LearnContact
Lesson 917 min read

Creating Your First Database

Learn to create, list, select, and delete a database in MySQL by building a real "school" database.

Introduction

With the theory in place, it is time to write real SQL. In this lesson you will create your very first database in MySQL, learn how to see what databases already exist, how to select which one you are working in, and how to remove one safely.

Everything from this lesson onward uses the same running example: a "school" database, which will hold the students table you build in the next lesson.

What You Will Learn
  • How to create a database with CREATE DATABASE.
  • How to list every database on your server with SHOW DATABASES.
  • How to select a database to work in with USE.
  • How to delete a database with DROP DATABASE, and why to be careful with it.
  • How to create and use a real "school" database end to end.

Creating a Database

A new, empty database is created with the CREATE DATABASE statement, followed by the name you want to give it and a semicolon.

CREATE DATABASE school;
Result
Query OK, 1 row affected (0.02 sec)

This creates an empty database named "school." It has no tables yet — you will add the students table to it in the next lesson.

Naming Tip

Database names should be short, lowercase, and descriptive, using underscores instead of spaces (like school_records, not "School Records"). Avoid spaces and special characters entirely.

Listing Databases

To see every database that currently exists on your MySQL server, use SHOW DATABASES.

SHOW DATABASES;
Result
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql               |
| performance_schema  |
| school              |
| sys                 |
+--------------------+

You will notice a few databases you did not create, like information_schema, mysql, performance_schema, and sys. These are built into MySQL itself and store internal system information — you should generally leave them alone.

Selecting a Database

A single MySQL server can host many databases at once, so before you create tables or run queries, you need to tell MySQL which database you want to work in. This is done with the USE statement.

USE school;
Result
Database changed

After running USE school;, every table you create and every query you run will apply to the "school" database, until you switch to a different one with another USE statement.

Easy to Forget

Forgetting to run USE before creating tables is one of the most common beginner mistakes — you might accidentally create a table in the wrong database. Always confirm which database you are using before proceeding.

Deleting a Database

To permanently delete a database and everything inside it — every table, every row of data — use DROP DATABASE.

DROP DATABASE school;
Result
Query OK, 0 rows affected (0.01 sec)
Extremely Destructive

DROP DATABASE deletes the entire database immediately and permanently — there is no undo, no recycle bin, and no confirmation prompt in a script. Running this against the wrong database can destroy real data in seconds. Never run it without being absolutely certain of the database name, and never run it on a production system without a verified backup.

Because this lesson's worked example needs the "school" database to still exist for the next lesson, do not actually run DROP DATABASE school; yet — only run it when you genuinely intend to delete a database.

Full Worked Example

Here is the complete, safe sequence to create and start using the "school" database, which every following lesson will build on.

setup_school_db.sql
-- 1. Create the database
CREATE DATABASE school;

-- 2. Confirm it exists
SHOW DATABASES;

-- 3. Select it so upcoming statements apply here
USE school;
Result
Query OK, 1 row affected (0.02 sec)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql               |
| performance_schema  |
| school              |
| sys                 |
+--------------------+

Database changed

Common Mistakes

Avoid These Mistakes
  • Forgetting the semicolon at the end of a statement — MySQL will simply wait for more input.
  • Running DROP DATABASE without double-checking the database name first.
  • Creating tables before running USE, accidentally placing them in the wrong database.
  • Trying to CREATE DATABASE with a name that already exists, which causes an error unless you use IF NOT EXISTS.

Best Practices

  • Always run SHOW DATABASES; after creating one, to confirm it was actually created.
  • Get in the habit of running USE database_name; immediately after selecting a database in any script.
  • Use CREATE DATABASE IF NOT EXISTS school; in scripts you might re-run, to avoid errors on a database that already exists.
  • Treat DROP DATABASE as a last resort, and always keep backups of anything you cannot afford to lose (covered later in Backup & Restore).

Frequently Asked Questions

What happens if I create a database with a name that already exists?

MySQL returns an error, such as "Can't create database 'school'; database exists." Use CREATE DATABASE IF NOT EXISTS school; to avoid the error if the database may already be present.

Can I rename a database after creating it?

MySQL has no direct RENAME DATABASE statement. The common workaround is creating a new database with the desired name, moving the tables over, and dropping the old one.

Do I need to run USE every time I open MySQL Workbench?

Yes, each new connection or session starts without a database selected, so you need to run USE database_name; again (or double-click the schema in Workbench's sidebar) before running queries against it.

Is there a way to undo DROP DATABASE?

No, not through MySQL itself. The only way to recover a dropped database is restoring it from a backup taken beforehand — which is exactly why the Backup & Restore lesson later in this course matters.

Key Takeaways

  • CREATE DATABASE database_name; creates a new, empty database.
  • SHOW DATABASES; lists every database currently on the server.
  • USE database_name; tells MySQL which database your upcoming statements should apply to.
  • DROP DATABASE database_name; permanently and irreversibly deletes a database and everything inside it.
  • The "school" database created in this lesson will hold the students table you build next.

Summary

Creating, listing, selecting, and dropping databases are the very first practical SQL skills you need, and they form the foundation everything else in this course sits on top of.

In this lesson, you created the "school" database, learned to list and select databases, and saw why DROP DATABASE demands extreme caution. Next, you will learn about MySQL's data types before creating your first table inside "school."

Lesson 9 Completed
  • You created a real database named "school."
  • You can list, select, and delete databases confidently.
  • You understand why DROP DATABASE must be used carefully.
  • You are ready to learn about MySQL data types.
Next Lesson →

Data Types