Creating Databases and Tables

Creating a Database

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE shop;

For fresh installations always specify utf8mb4 as the character set so you can store emoji and any world language safely.

Creating a Table

CREATE TABLE customers (
  id        BIGINT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  email     VARCHAR(150) NOT NULL UNIQUE,
  city      VARCHAR(80),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explaining the Columns

• id BIGINT AUTO_INCREMENT PRIMARY KEY: every row gets a unique number automatically.
• VARCHAR(100): variable length text up to 100 characters.
• NOT NULL: the column may not be left empty.
• UNIQUE: no two rows may share the same value.
• DEFAULT CURRENT_TIMESTAMP: fills in the creation time automatically.

Looking at Table Structure

DESCRIBE customers;
SHOW CREATE TABLE customers;\G

Altering a Table

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers MODIFY COLUMN city VARCHAR(100);
ALTER TABLE customers DROP COLUMN phone;

Dropping Objects

DROP TABLE customers;
DROP DATABASE shop;   -- careful, everything inside is gone

Key Points

• Use CREATE DATABASE then USE to select it.
• Declare types, nullability and defaults on every column.
• AUTO_INCREMENT provides surrogate primary keys.
• ALTER TABLE changes an existing table.
• Test on a scratch database before dropping real data.