Log in Sign up

Databases

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE shop;
DROP DATABASE shop;          -- deletes everything in it, no undo

Always use utf8mb4. The old utf8 (an alias of utf8mb3) cannot store emoji and some other characters. MySQL 8 uses utf8mb4 by default; older servers and some MariaDB versions do not.

Creating a table

CREATE TABLE customers (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email       VARCHAR(255) NOT NULL,
  name        VARCHAR(100) NOT NULL,
  is_active   BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  INT UNSIGNED NOT NULL,
  total        DECIMAL(10,2) NOT NULL,
  status       ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new',
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_customer (customer_id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Keys and constraints

ElementPurpose
PRIMARY KEYunique ID of each row; every table should have one
AUTO_INCREMENTMySQL assigns the next number
UNIQUEno two rows may share the value
NOT NULLvalue is required
DEFAULTvalue used when none is given
FOREIGN KEYvalue must exist in another table (InnoDB only)
CHECK (total >= 0)rule for values (enforced since MySQL 8.0.16 and MariaDB 10.2)

ON DELETE options for foreign keys: RESTRICT (block), CASCADE (delete child rows too), SET NULL.

Data types

Numbers

TypeRange / use
TINYINT−128…127 (0…255 unsigned)
INTabout ±2.1 billion (0…4.29 billion unsigned)
BIGINTvery large IDs and counters
DECIMAL(10,2)exact numbers – use for money
FLOAT, DOUBLEapproximate numbers – measurements, not money
BOOLEANalias for TINYINT(1): 0 = false, 1 = true

Text

TypeUse
CHAR(n)fixed length, e.g. country codes
VARCHAR(n)variable text up to n characters – names, emails
TEXT, MEDIUMTEXT, LONGTEXTlong text (64 KB / 16 MB / 4 GB)
ENUM('a','b')one value from a fixed list
JSONJSON documents, validated (in MariaDB an alias of LONGTEXT with a check)
BLOBbinary data – usually better stored as files

Date and time

TypeUse
DATE2026-11-15
TIME14:30:00
DATETIMEdate and time, no time zone conversion
TIMESTAMPstored as UTC and converted to the session time zone; in MySQL limited to the year 2038

For most applications DATETIME with values in UTC is the simplest choice.

Inspecting tables

SHOW TABLES;
DESCRIBE orders;
SHOW CREATE TABLE orders\G
SHOW TABLE STATUS LIKE 'orders'\G      -- engine, row estimate, size

Changing tables

ALTER TABLE customers ADD COLUMN phone VARCHAR(30) NULL AFTER name;
ALTER TABLE customers MODIFY COLUMN name VARCHAR(150) NOT NULL;
ALTER TABLE customers RENAME COLUMN phone TO phone_number;   -- MySQL 8 / MariaDB 10.5+
ALTER TABLE customers DROP COLUMN phone_number;
ALTER TABLE customers ADD INDEX idx_name (name);
RENAME TABLE customers TO clients;

On large tables ALTER TABLE can take a long time; test on a copy first and take a backup.

Removing data and tables

TRUNCATE TABLE logs;   -- empties the table, resets AUTO_INCREMENT
DROP TABLE logs;       -- removes the table completely
DROP TABLE IF EXISTS logs;

Neither can be undone with ROLLBACK.

Next: MySQL Basics: INSERT, SELECT, UPDATE and DELETE

Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.