MySQL online course

How to Create and Select a Database in MySQL

Learn how to create a MySQL database, verify it with SHOW DATABASES, select it with USE, and troubleshoot permissions and naming errors.

A MySQL database is a named container and namespace for related database objects, including tables, views, stored routines, and other objects. A database must exist before you can create tables inside it.

This lesson assumes that you can connect to MySQL through the command-line client or another SQL interface. If you are new to the client, review how to access MySQL and basic SQL command syntax.

MySQL database creation workflow

The normal workflow has three stages:

1. Create the database.
2. Verify that it appears in the databases available to your account.
3. Select it as the active database for the current session.

After the database is selected, you can create tables and other objects in it. Continue with creating a MySQL table when you are ready.

CREATE DATABASE syntax

The basic statement is:

CREATE DATABASE database_name;

CREATE DATABASE is the SQL statement that creates a new database. Replace database_name with an identifier chosen for your project. The semicolon terminates the statement in the MySQL client.

When the statement succeeds, the MySQL client returns a confirmation such as Query OK. The exact affected-row or warning details can vary by MySQL version and client, but successful execution means the database has been created.

Example: create a database named testdb

Enter the statement at the MySQL prompt:

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected

Here, testdb is the database name. It is only an example; use a name that describes your application or project.

Verify the database with SHOW DATABASES

Use SHOW DATABASES; to list the databases visible to the connected MySQL account:

mysql> SHOW DATABASES;

The result is a set of database names. Look for testdb in that list. System databases may also appear. A system database is maintained by MySQL for purposes such as metadata, privileges, performance information, or other server functions.

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

The databases displayed depend on the permissions of the current account. Seeing a database in one connection does not guarantee that every account can see or use it.

Select the active database with USE

Creating a database does not automatically make it the current target for later statements. Use USE to select the active database for the current session:

mysql> USE testdb;
Database changed

Database changed confirms that testdb is now the active database. An active database is the default database used when SQL refers to an object without qualifying it with a database name.

For example, after selecting testdb, a statement such as CREATE TABLE customers (...); creates the table in testdb. Without an active database, statements that require a default database can fail, and selecting a different database can send them to the wrong location.

Complete command sequence

The following sequence creates, verifies, and selects a database:

CREATE DATABASE testdb;
SHOW DATABASES;
USE testdb;

In practice, inspect the response after each command. Confirm that creation succeeded, confirm that testdb is listed, and then confirm that MySQL reports Database changed.

MySQL commands for creating and selecting a database

Safely create a database if it may already exist

If a database might already exist, use the optional IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS testdb;

This form avoids an error caused solely by an existing database with the same name. It does not replace, reset, empty, or modify the existing database. If testdb already exists, MySQL leaves it unchanged.

Use this form only when leaving an existing database untouched is the intended behavior. If you need a new, separate database, choose a different name instead.

Choosing a database name

• Choose a meaningful, consistent name such as inventory, blog_app, or testdb.
• Follow identifier conventions: use a clear pattern, avoid unnecessary punctuation, and be consistent about letter case.
• Avoid names that conflict with MySQL system databases or with reserved words.
• Remember that a database name is an identifier used in SQL statements, not a description shown only to humans.

Most conventional names do not need quoting. If a name contains special characters or conflicts with a reserved word, MySQL identifier quoting uses backticks:

CREATE DATABASE `sales-data`;
USE `sales-data`;

Backticks are for identifiers such as database names. They are not the same as single quotes, which normally delimit string values. In general, choosing a simple conventional name is preferable to relying on quoting.

Permissions and database access

Creating a database requires the appropriate MySQL privilege, meaning permission granted to an account to perform an operation. Typically, the account needs the CREATE privilege. If it does not, MySQL returns an access-denied error.

Database creation, user creation, and privilege assignment are separate tasks:

• CREATE DATABASE creates a database.
• CREATE USER creates a MySQL server account. See creating a MySQL user.
• GRANT statements assign permissions to accounts.

An account may be able to see or use only databases for which it has permissions. Therefore, SHOW DATABASES; lists databases accessible to the current account, not necessarily every database on the server.

Troubleshooting

The database name already exists

Another database already has the requested name. Choose a different name, inspect the existing database, or use CREATE DATABASE IF NOT EXISTS testdb; when you intentionally want no change if it already exists.

Access denied when creating a database

The connected account probably lacks the required CREATE privilege. Connect with an authorized account or ask a database administrator to grant the appropriate permission. Do not confuse this problem with creating a new MySQL user.

The database does not appear in SHOW DATABASES

Check the response to the creation command first. The creation may have failed, you may be connected to a different MySQL server, or the current account may not be allowed to view the database. Verify the connection details and review account privileges.

Commands target the wrong database

No database may be selected, or a different database may still be active. Run the following before creating or querying objects:

USE testdb;

You can also check the current selection with SELECT DATABASE();. A null result means that no active database is selected.

Syntax error near the database name

The identifier may contain unsupported characters, conflict with a reserved word, or be formatted incorrectly. Prefer a conventional name, or quote the identifier with backticks when appropriate.

Exam-relevant notes

• CREATE DATABASE creates the named database; it does not select it automatically.
• SHOW DATABASES lists databases visible to the connected account.
• USE database_name; selects the default database for the current session.
• IF NOT EXISTS prevents an existing-name error but does not alter an existing database.
• Tables must be created inside an existing database, either by selecting it first or by qualifying the table name with a database name.