sqlANYcreatedatabaseutf8mb4
sql
CREATE DATABASE IF NOT EXISTS app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;Using `utf8mb4` is the standard choice for full Unicode support.
MySQL DDL and Schema Design Cheatsheet
create, alter, rename, and drop databases and tables in MySQL, with practical patterns for primary keys, unique keys, and foreign keys.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
create and alter tables
Core DDL for databases, tables, columns, constraints, and defaults.
sqlANYcreate-tableusersprimary-key
sql
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB;A common baseline table definition for application data.
sqlANYalter-tableadd-column
sql
ALTER TABLE users ADD COLUMN phone VARCHAR(50) NULL AFTER email;`AFTER` controls physical column position in MySQL table metadata.
sqlANYalter-tablemodify-column
sql
ALTER TABLE users MODIFY COLUMN phone VARCHAR(100) NULL;Use `MODIFY COLUMN` when you are changing the definition but not renaming the column.
sqlANYalter-tablerename-column
sql
ALTER TABLE users CHANGE COLUMN full_name display_name VARCHAR(255) NOT NULL;In MySQL, `CHANGE COLUMN` renames a column and requires the new full definition.
sqlANYalter-tabledrop-column
sql
ALTER TABLE users DROP COLUMN phone;Be careful in production because this is destructive.
sqlANYrenametable
sql
RENAME TABLE users TO app_users;Useful during refactors or when aligning schema names with application conventions.
sqlANYdrop-tabledestructive
sql
DROP TABLE IF EXISTS old_logs;Always double-check the target table before dropping in shared environments.
constraints and keys
Primary keys, unique keys, foreign keys, and check-style patterns.
sqlANYuniqueconstraintindex
sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);Unique constraints are commonly used for logins, slugs, and external identifiers.
sqlANYforeign-keyorderscascade
sql
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_orders_user_id (user_id),
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE RESTRICT
) ENGINE=InnoDB;The explicit index on the foreign key column helps join and lookup performance.
sqlANYforeign-keydrop
sql
ALTER TABLE orders DROP FOREIGN KEY fk_orders_user;You may also need to drop the supporting index separately if you no longer need it.
sqlANYcomposite-keyjunction-table
sql
CREATE TABLE user_roles (
user_id BIGINT UNSIGNED NOT NULL,
role_code VARCHAR(50) NOT NULL,
granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_code)
) ENGINE=InnoDB;Composite keys are common in mapping or junction tables.
sqlANYdrop-databasedestructive
sql
DROP DATABASE IF EXISTS sandbox_db;This is intentionally destructive and should be reserved for controlled environments.
More in MySQL
MySQL Procedures, Triggers, and Events Cheatsheet
stored procedures, stored functions, delimiters, call, triggers, and scheduled events in MySQL.
MySQL Functions, JSON, and Date Cheatsheet
now, date_add, date_sub, date_format, timestampdiff, json_extract, json_set, json_object, concat, coalesce, and case in MySQL.
MySQL Backup, Restore, Import, and Export Cheatsheet
mysqldump, mysql restore, load data infile, select into outfile, mysqlimport, and MySQL Shell dump/load workflows.
MySQL Users, Grants, and Privileges Cheatsheet
create users, alter passwords, grant privileges, show grants, revoke access, and manage MySQL accounts safely.
MySQL Transactions and Locking Cheatsheet
start transaction, commit, rollback, savepoints, select for update, isolation levels, and InnoDB lock troubleshooting.
MySQL Indexes, EXPLAIN, and Performance Cheatsheet
index design, show index, explain, explain analyze, analyze table, and practical performance tuning workflows in MySQL.