Managing Databases in MySQL

In MySQL, the data is organized and stored in tables, which are themselves stored in databases.

Each MySQL server can hold and manage several independent databases, which ultimately allows it to store and manage different sets of data separately.

This separation offers the possibility to grant specific permissions on a per-database basis, allowing for better control over who can access, modify, or delete data within each database.

Creating a database

To create a new database, you can use the CREATE DATABASE keywords followed by the name of the database:

CREATE DATABASE database_name;

Upon execution, MySQL will either output this message confirming the creation of the specified database:

Query OK, 1 row affected (0.01 sec)

Or this error message if it already exists:

ERROR 1007 (HY000): Can't create database 'database_name'; database exists

Note: Since database names are case-sensitive, it is recommended to keep them concise and write them using the snake case syntax, for example: streaming or e_learning.

Example

This SQL statement will create a new database named learnbackend:

mysql> CREATE DATABASE learnbackend;
Query OK, 1 row affected (0.01 sec)

Selecting a database

In order to perform operations on the objects of a database, such as retrieving rows for a table, you must first select the database using the USE keyword followed by the name of the database:

USE database_name;

Upon execution, MySQL will either output this message confirming that the specified database is in use:

Database changed

Or this error message if it doesn't exist:

ERROR 1049 (42000): Unknown database 'database_name'

Note: By default, no database will be selected when connecting to the MySQL client.

Example

This SQL statement will use the existing database named learnbackend:

mysql> USE learnbackend;
Database changed

Listing existing databases

To output the list of databases that currently exist on the MySQL server, you can use the SHOW DATABASES keywords:

SHOW DATABASES;

Upon execution, it will output a single column named Databases containing the names of the databases sorted in alphabetical order.

Example

This SQL statement will display the list of existing databases:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learnbackend       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Pre-defined MySQL databases

By default, MySQL uses 4 pre-defined databases for internal management and monitoring called information_schema, mysql, performance_schema, and sys.

Note that these databases are not meant to store user data nor be manually modified.

The information_schema database

The information_schema database contains read-only information about the objects of all databases on the MySQL server.

The mysql database

The mysql database contains information about user accounts, privileges, system configuration, and other essential data required for the server operation.

The performance_schema database

The performance_schema database contains performance data such as query execution times, wait events, server load, and other performance metrics.

The sys database

The sys database provides easier access to the data collected by the performance_schema database for easier performance tuning and troubleshooting.

Deleting a database

To delete a database and all its associated objects permanently from the MySQL server, you can use the DROP DATABASE keywords followed by the name of the database:

DROP DATABASE database_name;

Upon execution, it will output this message confirming the deletion of the specified database:

Query OK, 0 rows affected (0.01 sec)

❗ Warning: This operation is irreversible! It is recommended to create regular backups of your databases before deleting one, in case you make a mistake and need to restore them.

Example

This SQL statement will delete the learnbackend database:

mysql> DROP DATABASE learnbackend;
Query OK, 0 rows affected (0.01 sec)

Summary

Here's a summary of what you've learned in this lesson:

• The CREATE DATABASE command is used to create a new database.
• The USE command is used to switch to an existing database.
• The SHOW DATABASES command is used to list the existing databases.
• The DROP DATABASE command is used to delete a database.