Menu
Share on

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.

What is the syntax of MySQL's CREATE TABLE command?

The basic syntax to create tables using MySQL's CREATE TABLE command looks like this:

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

We can break down above command template into the following components:

  • CREATE TABLE <table_name>: The basic creation statement. The <table_name> placeholder should be replaced with the name of the table you wish to use.
  • <column_name> <data_type>: Defines a basic column within the table. The <column_name> placeholder should be replaced with the name you wish to use for your column. The <data_type> specifies the MySQL data type of the column. Data stored within the table must conform to the column structure and column data types to be accepted.
  • <column_constraint>: Column constraints are optional restraints add additional requirements for data. For example, you can require that entries be not null, unique, or positive integers.
  • <table_constraint>: Table constraints are similar to column constraints but involve the interaction of multiple columns. For instance, you could have a table constraint that checks that a DATE_OF_BIRTH is before DATE_OF_DEATH in a table.

How do you create tables only if they don't already exist?

MySQL's default behavior is to raise an error if you try to create a table that already exists. However, the optional IF NOT EXISTS clause can be added to the creation statement to override this behavior.

You can use the IF NOT EXISTS clause by inserting it in the command after the CREATE TABLE phrase but before the table name:

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

This will modify the behavior of the command as follows: If the specified table name already exists, MySQL will raise a warning instead of an error. The rest of the command behavior remains the same.

Creating tables within MySQL databases

The command template given above is enough to create basic tables. To further our example, we'll create two tables in our school database. One table will be called supplies and the other will be called teachers:

Entity relationship diagrams for supplies and teachers tables

In the supplies table, we'll include the following fields:

  • id: A unique ID for each type of school supply.
  • name: The name of a specific school item.
  • description: A short description of the item.
  • manufacturer: The name of the item manufacturer.
  • color: The color of the item.
  • inventory: The number of items we have for a certain type of school supply. This should never be less than 0.

We can create the supplies table with the above qualities using the following SQL:

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)
);

This will create the supplies table within the school database. The PRIMARY KEY column constraint is a special constraint used to indicate columns that can uniquely identify records within the table. As such, the constraint specifies that the column cannot be null and must be unique. MySQL creates indexes for primary key columns to increase querying speed.

Verify that the new table is present by typing:

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

You can verify that the schema reflects your definition by typing:

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

The columns, data types, and constraints we specified are present in the output, though the ordering and display might be different.

Next, create a teachers table. In this table, the following columns should be present:

  • id: A unique employee identification number.
  • first_name: The teacher's first name.
  • last_name: The teacher's last name.
  • subject: The subject that the teacher is hired to teach.
  • grade_level: The grade level of students that the teach is hired to teach.

Create the teachers table with the above schema with the following SQL:

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 create tables with primary keys and foreign keys

You can find information about creating tables with primary and foreign keys in some of our other MySQL guides. Primary keys and foreign keys are both types of database constraint within MySQL.

A primary key is a special column or column that is guaranteed to be unique across rows within the same table. All primary keys can be used to uniquely identify a specific row. Primary keys not only ensure that each row has a unique value for the primary key columns, they also ensure that no rows contain NULL values for that column. Often, the primary key in MySQL uses the following format to specify an automatically assigned incrementing primary key: id INT AUTO_INCREMENT PRIMARY KEY.

Foreign keys are a way to ensure that a column or columns in one table match the values contained within another table. This helps ensure referential integrity between tables.

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 syntax

The basic syntax for modifying tables in MySQL looks like this:

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

The <change_command> indicates the exact type of change you would like to make, whether it involves setting different options on the table, adding or removing columns, or changing types or constraints. The <change_parameters> part of the command contains any additional information that MySQL needs to complete the change.

Adding columns to tables

You can add a column to a MySQL table with the ADD change command. The change parameters will include the column name, type, and options, just as you would specify them in the CREATE TABLE command.

For example, to add a column called missing_column of the text type to a table called some_table, you would type:

ALTER TABLE some_table ADD missing_column text;

Removing columns from tables

If, instead, you'd like to remove an existing column, you can use the DROP COLUMN command instead. You need to specify the name of the column you wish to drop as a change parameter:

ALTER TABLE some_table DROP COLUMN useless_column;

Changing the data type of a column

To change the data type of a column, you can use ALTER COLUMN change command with the MODIFY COLUMN column command. The parameters in this context include the column name and its new type:

ALTER TABLE resident MODIFY COLUMN id INT;

Other table changes

Many other types of changes can be achieved with the ALTER TABLE command. For more information about the options available, check out the official MySQL documentation for ALTER TABLE.

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.