Databases
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE shop;
DROP DATABASE shop; -- deletes everything in it, no undoAlways 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
| Element | Purpose |
|---|---|
PRIMARY KEY | unique ID of each row; every table should have one |
AUTO_INCREMENT | MySQL assigns the next number |
UNIQUE | no two rows may share the value |
NOT NULL | value is required |
DEFAULT | value used when none is given |
FOREIGN KEY | value 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
| Type | Range / use |
|---|---|
TINYINT | −128…127 (0…255 unsigned) |
INT | about ±2.1 billion (0…4.29 billion unsigned) |
BIGINT | very large IDs and counters |
DECIMAL(10,2) | exact numbers – use for money |
FLOAT, DOUBLE | approximate numbers – measurements, not money |
BOOLEAN | alias for TINYINT(1): 0 = false, 1 = true |
Text
| Type | Use |
|---|---|
CHAR(n) | fixed length, e.g. country codes |
VARCHAR(n) | variable text up to n characters – names, emails |
TEXT, MEDIUMTEXT, LONGTEXT | long text (64 KB / 16 MB / 4 GB) |
ENUM('a','b') | one value from a fixed list |
JSON | JSON documents, validated (in MariaDB an alias of LONGTEXT with a check) |
BLOB | binary data – usually better stored as files |
Date and time
| Type | Use |
|---|---|
DATE | 2026-11-15 |
TIME | 14:30:00 |
DATETIME | date and time, no time zone conversion |
TIMESTAMP | stored 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, sizeChanging 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.
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.