Databases & tables: CREATE, ALTER, DROP

Create databases with utf8mb4, define tables, change them with ALTER TABLE and remove them safely.

Every MySQL server holds many databases (MySQL also calls them schemas), and each database holds tables. In this lesson you'll create both, inspect them, change their structure with ALTER TABLE, and remove them safely. These statements make up SQL's Data Definition Language (DDL).

Creating a database#

SQL

CREATE DATABASE IF NOT EXISTS school
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;
USE school;

IF NOT EXISTS stops the statement from failing if the database is already there, which is handy in scripts you run more than once.
Character set utf8mb4 is real UTF-8, so Hindi, Chinese and emoji 🐘 all fit. It is the default in MySQL 8, but saying it explicitly documents your intent.
Collation decides how text is compared and sorted. utf8mb4_0900_ai_ci is accent-insensitive (ai) and case-insensitive (ci), so 'resume' = 'Résumé' is true. Use utf8mb4_0900_as_cs or utf8mb4_bin when case must matter.

See what exists and how it was defined:

SQL

SHOW DATABASES LIKE 'sch%';
SHOW CREATE DATABASE school;

Output

+-----------------+
| Database (sch%) |
+-----------------+
| school          |
+-----------------+
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+

(The /*!80016 ... */ part is a versioned comment: only MySQL 8.0.16 or newer runs what's inside.)

Database names map to folders on disk, so keep them lowercase, with letters, digits and underscores only.

Creating a table#

A table definition lists each column with a name, a data type and optional constraints:

SQL

CREATE TABLE students (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    birth_date DATE,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

ENGINE = InnoDB picks the storage engine. InnoDB is the default and the one you want: it supports transactions, row-level locking and foreign keys. (The old MyISAM engine supports none of those.)
A column without NOT NULL, like birth_date, allows NULL, meaning "unknown".
You'll meet every type in the next lesson and every constraint in Constraints.

Inspect the table in two ways:

SQL

DESCRIBE students;

Output

+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int unsigned | NO   | PRI | NULL              | auto_increment    |
| first_name | varchar(50)  | NO   |     | NULL              |                   |
| last_name  | varchar(50)  | NO   |     | NULL              |                   |
| email      | varchar(255) | NO   | UNI | NULL              |                   |
| birth_date | date         | YES  |     | NULL              |                   |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+

SHOW CREATE TABLE students\G prints the exact CREATE TABLE statement MySQL would use to rebuild the table, including indexes, engine and character set. It's great for copying a structure or checking what really got created.

Copying tables#

SQL

-- Same structure (columns, indexes), no rows:
CREATE TABLE students_archive LIKE students;

-- Structure and data from a query (indexes and AUTO_INCREMENT are NOT copied):
CREATE TABLE student_emails AS
    SELECT id, email FROM students;

SHOW TABLES;

Output

+------------------+
| Tables_in_school |
+------------------+
| student_emails   |
| students         |
| students_archive |
+------------------+

Changing a table with ALTER TABLE#

Requirements change. ALTER TABLE changes a table's structure without losing the data in it.

Add columns. New columns go at the end unless you say FIRST or AFTER col:

SQL

ALTER TABLE students
    ADD COLUMN phone VARCHAR(20) AFTER email,
    ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

Change a type or constraint with MODIFY. You must restate the whole definition, and anything you leave out (like NOT NULL or a default) is dropped:

SQL

ALTER TABLE students MODIFY phone VARCHAR(30) NOT NULL DEFAULT '';

Rename a column. RENAME COLUMN (MySQL 8.0+) changes only the name. CHANGE renames and redefines in one go:

SQL

ALTER TABLE students RENAME COLUMN phone TO phone_number;
ALTER TABLE students CHANGE birth_date date_of_birth DATE NULL;

Change only a default, which is instant because only metadata changes:

SQL

ALTER TABLE students ALTER COLUMN is_active SET DEFAULT FALSE;

Drop a column. Its data is gone for good:

SQL

ALTER TABLE students DROP COLUMN phone_number;

Rename a table:

SQL

RENAME TABLE student_emails TO email_list;
-- or: ALTER TABLE student_emails RENAME TO email_list;

Check the result:

SQL

DESCRIBE students;

Output

+---------------+--------------+------+-----+-------------------+-------------------+
| Field         | Type         | Null | Key | Default           | Extra             |
+---------------+--------------+------+-----+-------------------+-------------------+
| id            | int unsigned | NO   | PRI | NULL              | auto_increment    |
| first_name    | varchar(50)  | NO   |     | NULL              |                   |
| last_name     | varchar(50)  | NO   |     | NULL              |                   |
| email         | varchar(255) | NO   | UNI | NULL              |                   |
| date_of_birth | date         | YES  |     | NULL              |                   |
| created_at    | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| is_active     | tinyint(1)   | NO   |     | 0                 |                   |
+---------------+--------------+------+-----+-------------------+-------------------+

Notice that BOOLEAN is stored as tinyint(1), with TRUE = 1 and FALSE = 0. MySQL has no separate boolean type.

On big tables, ALTER TABLE can take a long time. MySQL 8 does many changes online (ALGORITHM=INPLACE) or even instantly (ALGORITHM=INSTANT, for example adding a column or changing a default). You can ask for a specific algorithm, and MySQL will give an error rather than silently copying the whole table: ALTER TABLE students ADD COLUMN notes TEXT, ALGORITHM=INSTANT;

Temporary tables#

A TEMPORARY table exists only in your current connection and vanishes when you disconnect. It's perfect for intermediate results in a report:

SQL

CREATE TEMPORARY TABLE tmp_totals (student_id INT, total INT);

Other connections can't see it, and it can even share a name with a real table (hiding it for your session).

Emptying and removing tables#

SQL

INSERT INTO students (first_name, last_name, email)
VALUES ('Ada', 'Lovelace', 'ada@example.com');

TRUNCATE TABLE students;      -- delete ALL rows, reset AUTO_INCREMENT, keep the table
DROP TABLE IF EXISTS email_list, students_archive;   -- remove tables entirely
SHOW TABLES;

Output

+------------------+
| Tables_in_school |
+------------------+
| students         |
+------------------+

Statement	Rows	Structure	AUTO_INCREMENT
`DELETE FROM t`	removed (can use `WHERE`)	kept	continues
`TRUNCATE TABLE t`	all removed, fast	kept	reset
`DROP TABLE t`	removed	removed	–

DDL statements such as DROP and TRUNCATE commit automatically and can't be rolled back. Before dropping anything important, take a backup (see Backup & restore).

To remove a whole database and every table in it:

SQL

DROP DATABASE IF EXISTS school;

Common mistakes#

Using reserved words as names. order, group and key are SQL keywords. CREATE TABLE order (...) fails, so use orders instead, or quote the name with backticks (`order`).
Forgetting part of the definition in MODIFY/CHANGE. MODIFY name VARCHAR(80) silently removes an existing NOT NULL. Copy the column from SHOW CREATE TABLE and edit it.
Running DROP on the wrong server. Check SELECT DATABASE(), @@hostname; first.

What's next#

Choosing the right data type for each column saves space, prevents bugs and makes queries faster. That's the next lesson.

Creating a database#

Creating a table#

Copying tables#

Changing a table with ALTER TABLE#

Temporary tables#

Emptying and removing tables#

Common mistakes#

What's next#

Quick quiz