How to create and delete databases and tables in MySQL

Introduction

MySQL maintains the data it manages within tables and databases. Tables store data within predefined schemas of columns and data types. Databases are a structure that can hold multiple tables, indexes, procedures, and help define a scope for privileges, customizations, and more.

In this guide, we'll show you how to create tables and databases in MySQL. We'll show you how to manage some features of both structures and then we'll cover how to remove them to clean up if you no longer need them.

Prerequisites

To follow along with this guide, you will need to log in to a MySQL server with a user with administrative privileges using the mysql command line client.

If you don't want to log in with the root MySQL administrative account, your user will need at least the CREATE privilege to perform the actions described in this guide.

You can check your user's available privileges by typing:

SHOW GRANTS\G

How do you create a new database?

The first thing we'll demonstrate how to do is create a new database. In MySQL, databases contain tables, indexes, procedures, and other resources that are typically related to a single project. Each of those resources must be created within a database, so learning how to create a new database is a good first step.

The basic syntax used to create a new database looks like this:

CREATE DATABASE <db_name>;

Replace <db_name> with the name you wish to give your database before executing the command. This command will create a database with the given name and set the current user as the new database's owner.

The database will use the system-wide defaults for the character set, collation, and encryption properties:

• CHARACTER SET: sets the character set for the new database. This is a localization option that affects what characters are considered uppercase, lowercase, and digits.
• COLLATE: sets the collation, or sort order for the database. This is a localization option that determines how items are organized when they are ordered.
• ENCRYPTION: sets encryption for for the new database. This is a boolean option that can be either 'Y' for yes, or 'N' for no.

You can find the character sets available for your installation by typing:

SHOW CHARACTER SET;

Likewise, you can find the available collations by typing:

SHOW COLLATION;

If you want to specify a non-default value for one of these parameters, you can do so by adding them after the main creation statement like this:

CREATE DATABASE <db_name>
  CHARACTER SET = 'utf8mb4'
  COLLATE = 'utf8mb4_0900_ai_ci'
  ENCRYPTION = 'Y';

To follow along with the examples in this guide, create a database called school using your instance's default locale settings and the UTF8 character set:

CREATE DATABASE school CHARACTER SET = 'utf8mb4';

This will create your new database using the specifications you provided.

List existing databases

To list the databases that are available on your server, type:

SHOW DATABASES;

This will list each of the databases currently defined within the environment:

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

You can verify that the settings you provided were applied to the new school database using the SHOW CREATE DATABASE command:

SHOW CREATE DATABASE school;

The output will display the command and options used to create the database, padding out the options with whatever default values applied:

+----------+----------------------------------------------------------------------------------------------------------------------------------+
  Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
  school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

How to change to a different database in MySQL

Once your database is created, you can either work with it in a few different ways.

The first option is to specify the database as an argument in every command you use. To save time if you are running multiple commands related to the database's objects though, you can also "switch to" or "use" the database to set your new database as the default context for any database-related commands that you'll be running.

To change to a different database, type:

USE <db_name>;

In our case, we can switch to the school database we created by typing:

USE school;

Create tables within databases

Once you have created a database, you can define tables and other objects inside to store and manage you data. In MySQL, tables consist of a table name, the column definitions (these in turn include names, data types, and constraints among others), and table options.

CREATE TABLE <table_name> (
    <column_name> <data_type> [<column_constraint>],
    [<table_constraint>,]
);

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

CREATE TABLE supplies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  description VARCHAR(255),
  manufacturer VARCHAR(255),
  color VARCHAR(255),
  inventory INT CHECK (inventory >= 0)
);

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
+------------------+
1 row in set (0.01 sec)

SHOW CREATE TABLE supplies\G

*************************** 1. row ***************************
       Table: supplies
Create Table: CREATE TABLE `supplies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `color` varchar(255) DEFAULT NULL,
  `inventory` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `supplies_chk_1` CHECK ((`inventory` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec

CREATE TABLE teachers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  subject VARCHAR(255),
  grade_level INT
);

How to view tables in MySQL

In MySQL you can list tables in a few different ways depending on what information you are looking for.

If you'd like to see what tables are available within your database, type:

SHOW TABLES;

+------------------+
  Tables_in_school |
+------------------+
  supplies         |
  teachers         |
+------------------+
2 rows in set (0.00 sec)

You can also check that the schema for the table matches your specifications:

DESCRIBE teachers;

+--------------+--------------+------+-----+---------+----------------+
  Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
  id           | int          | NO   | PRI | NULL    | auto_increment |
  name         | varchar(255) | YES  |     | NULL    |                |
  description  | varchar(255) | YES  |     | NULL    |                |
  manufacturer | varchar(255) | YES  |     | NULL    |                |
  color        | varchar(255) | YES  |     | NULL    |                |
  inventory    | int          | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

The teachers table seems to match our definition.

How to change tables with the ALTER TABLE command

If you need to change the schema of an existing table in MySQL, you can use the ALTER TABLE command. The ALTER TABLE command is very similar to the CREATE TABLE command, but operates on an existing table.

ALTER TABLE <table_name> <change_command> <change_parameters>;

ALTER TABLE some_table ADD missing_column text;

ALTER TABLE some_table DROP COLUMN useless_column;

ALTER TABLE resident MODIFY COLUMN id INT;

Drop tables

If you want to delete a table, you can use the DROP TABLE SQL statement. This will delete the table as well as any data stored within it.

The basic syntax looks like this:

DROP TABLE <table_name>;

This will delete the table if it exists and throw an error if the table name does not exist.

If you wish to delete the table if it exists and do nothing if it does not exist, you can include the IF EXISTS qualifier within the statement:

DROP TABLE IF EXISTS <table_name>;

Tables that have dependencies on other tables or objects cannot be deleted by default while those dependencies exist.

Delete the supplies table we created earlier by typing:

DROP TABLE supplies;

We will keep the teachers database to demonstrate that the statement to delete databases also removes all child objects like tables.

Drop databases

The DROP DATABASE statement tells MySQL to delete the specified database. The basic syntax looks like this:

DROP DATABASE <database_name>;

Replace the <database_name> placeholder with the name of the database you wish to remove. This will delete the database if it is found. If the database cannot be found, an error will occur:

DROP DATABASE some_database;

ERROR 1008 (HY000): Can't drop database 'some_database'; database doesn't exist

If you wish to delete the database if it exists and otherwise do nothing, include the optional IF EXISTS option:

DROP DATABASE IF EXISTS some_database;

Query OK, 0 rows affected, 1 warning (0.00 sec)

This will remove the database or do nothing if it cannot be found.

To remove the school database that we used in this guide, list the existing databases on your system:

SHOW DATABASES;

+--------------------+
  Database           |
+--------------------+
  information_schema |
  mysql              |
  performance_schema |
  school             |
  sys                |
  testing            |
+--------------------+
6 rows in set (0.00 sec)

Delete the school database with the following command:

DROP DATABASE school;

This will remove the school database along with the teachers table defined within.

Conclusion

This article covered the basics of how to create and delete databases and tables with MySQL. These are some of the most basic commands required to set up a database system and being defining the structure of your data.

As mentioned earlier, the SQL statements covered in this MySQL tutorial, particularly the CREATE TABLE statement, have many additional parameters can be used to change the system's behavior. You can find out more about these by checking out the official MySQL documentation.

FAQ

ALTER TABLE <table_name> <change_command> <change_parameters>;

ALTER TABLE some_table ADD missing_column text;

DROP TABLE IF EXISTS <table_name>;

DROP INDEX <index_name> ON <table_name>;

CREATE TEMPORARY TABLE table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);