MySQL Database Cheatsheet

Comprehensive MySQL cheatsheet covering databases, tables, CRUD, joins, indexes, constraints, functions, stored procedures, triggers, views, users, transactions, backup/restore, and performance optimization—with practical SQL examples throughout.

Table of Contents

1. Database Operations
2. Table Operations
3. Data Types
4. CRUD Operations
5. SELECT Queries
6. Joins
7. Indexes
8. Constraints
9. Functions & Operators
10. Stored Procedures & Functions
11. Triggers
12. Views
13. User Management
14. Transactions
15. Backup & Restore
16. Performance Optimization

---

Database Operations

Create Database

CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name;
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Show Databases

SHOW DATABASES;
SHOW DATABASES LIKE 'pattern';

Select Database

USE database_name;

Drop Database

DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

Alter Database

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

---

Table Operations

Create Table

CREATE TABLE table_name (
    column1 datatype CONSTRAINT,
    column2 datatype CONSTRAINT,
    column3 datatype CONSTRAINT,
    PRIMARY KEY (column1)
);

CREATE TABLE IF NOT EXISTS table_name (...);
CREATE TABLE new_table AS SELECT * FROM existing_table;
CREATE TABLE new_table LIKE existing_table;

Show Tables

SHOW TABLES;
SHOW TABLES FROM database_name;
SHOW FULL TABLES; -- Shows table types

Describe Table

DESCRIBE table_name;
DESC table_name;
SHOW COLUMNS FROM table_name;
SHOW CREATE TABLE table_name;

Alter Table

-- Add column
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name ADD column_name datatype AFTER existing_column;
ALTER TABLE table_name ADD column_name datatype FIRST;

-- Drop column
ALTER TABLE table_name DROP COLUMN column_name;

-- Modify column
ALTER TABLE table_name MODIFY column_name new_datatype;
ALTER TABLE table_name CHANGE old_name new_name datatype;

-- Rename table
ALTER TABLE table_name RENAME TO new_name;
RENAME TABLE old_name TO new_name;

Drop Table

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
DROP TABLE table1, table2, table3;

Truncate Table

TRUNCATE TABLE table_name;

---

Data Types

Numeric Types

TINYINT     -- Range: -128 to 127 (signed), 0 to 255 (unsigned)
SMALLINT    -- Range: -32768 to 32767
MEDIUMINT   -- Range: -8388608 to 8388607
INT         -- Range: -2147483648 to 2147483647
BIGINT      -- Range: -2^63 to 2^63-1
DECIMAL(M,D) -- Fixed-point number
FLOAT(M,D)  -- Floating-point
DOUBLE(M,D) -- Double precision
BIT(n)      -- Bit field

String Types

CHAR(n)          -- Fixed-length string (0-255)
VARCHAR(n)       -- Variable-length string (0-65535)
TINYTEXT         -- Max 255 characters
TEXT             -- Max 65,535 characters
MEDIUMTEXT       -- Max 16,777,215 characters
LONGTEXT         -- Max 4,294,967,295 characters
BINARY(n)        -- Fixed-length binary
VARBINARY(n)     -- Variable-length binary
BLOB             -- Binary Large Object (max 65,535 bytes)
ENUM('v1','v2')  -- Enumeration
SET('v1','v2')   -- Set of values

Date & Time Types

DATE        -- 'YYYY-MM-DD'
TIME        -- 'HH:MM:SS'
DATETIME    -- 'YYYY-MM-DD HH:MM:SS'
TIMESTAMP   -- 'YYYY-MM-DD HH:MM:SS' (auto-updated)
YEAR        -- YYYY

JSON Type

JSON        -- JSON document

---

CRUD Operations

INSERT

-- Single row
INSERT INTO table_name (col1, col2, col3) VALUES (val1, val2, val3);
INSERT INTO table_name VALUES (val1, val2, val3);

-- Multiple rows
INSERT INTO table_name (col1, col2) VALUES 
(val1, val2),
(val3, val4),
(val5, val6);

-- From SELECT
INSERT INTO table1 (col1, col2) SELECT col1, col2 FROM table2;

-- Insert on duplicate key update
INSERT INTO table (id, name) VALUES (1, 'John')
ON DUPLICATE KEY UPDATE name = 'John';

-- Replace
REPLACE INTO table_name (col1, col2) VALUES (val1, val2);

SELECT

-- Basic select
SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;

-- With conditions
SELECT * FROM table WHERE condition;

-- Distinct values
SELECT DISTINCT column FROM table;
SELECT DISTINCT col1, col2 FROM table;

-- Limit results
SELECT * FROM table LIMIT 10;
SELECT * FROM table LIMIT 10 OFFSET 20;
SELECT * FROM table LIMIT 20, 10;  -- offset, limit

UPDATE

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
UPDATE table1, table2 SET table1.col = table2.col WHERE table1.id = table2.id;

DELETE

DELETE FROM table_name WHERE condition;
DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.id WHERE condition;
DELETE FROM table_name;  -- Delete all rows

---

SELECT Queries

WHERE Clause Operators

= , != , <>        -- Equal, Not equal
> , < , >= , <=    -- Greater/Less than
BETWEEN a AND b    -- Range
LIKE 'pattern'     -- Pattern matching (% _ )
IN (val1, val2)    -- Multiple values
IS NULL, IS NOT NULL
AND, OR, NOT       -- Logical operators
REGEXP 'pattern'   -- Regular expression

ORDER BY

ORDER BY column1 ASC, column2 DESC;
ORDER BY FIELD(column, 'value1', 'value2');
ORDER BY RAND();  -- Random order

GROUP BY & HAVING

SELECT column, COUNT(*) FROM table 
GROUP BY column HAVING COUNT(*) > 1;

Subqueries

-- Scalar subquery
SELECT * FROM table WHERE col = (SELECT MAX(col) FROM table);

-- Row subquery
SELECT * FROM table WHERE (col1, col2) = (SELECT col1, col2 FROM ...);

-- Correlated subquery
SELECT * FROM t1 WHERE col1 IN (SELECT col1 FROM t2 WHERE t2.id = t1.id);

-- EXISTS
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);

UNION

SELECT col1, col2 FROM table1
UNION [ALL]
SELECT col1, col2 FROM table2;

---

Joins

INNER JOIN

SELECT * FROM table1 
INNER JOIN table2 ON table1.id = table2.id;

-- Using USING when column names match
SELECT * FROM table1 
INNER JOIN table2 USING (id);

LEFT/RIGHT JOIN

SELECT * FROM table1 
LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 
RIGHT JOIN table2 ON table1.id = table2.id;

CROSS JOIN

SELECT * FROM table1 CROSS JOIN table2;
SELECT * FROM table1, table2;  -- Implicit cross join

SELF JOIN

SELECT a.name, b.name FROM employees a
INNER JOIN employees b ON a.manager_id = b.id;

Multiple Joins

SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.id = table3.id
INNER JOIN table4 ON table3.id = table4.id;

---

Indexes

Create Index

CREATE INDEX index_name ON table_name (column1, column2);
CREATE UNIQUE INDEX index_name ON table_name (column);
CREATE FULLTEXT INDEX index_name ON table_name (column);
CREATE SPATIAL INDEX index_name ON table_name (column);

Add/Drop Index on Existing Table

ALTER TABLE table_name ADD INDEX index_name (column);
ALTER TABLE table_name ADD UNIQUE index_name (column);
ALTER TABLE table_name DROP INDEX index_name;

Show Indexes

SHOW INDEX FROM table_name;
SHOW INDEX FROM table_name FROM database_name;

Drop Index

DROP INDEX index_name ON table_name;

Index Types

BTREE      -- Default for most engines
HASH       -- Memory/heap tables only
FULLTEXT   -- Full-text search
SPATIAL    -- Spatial data types

---

Constraints

PRIMARY KEY

-- On table creation
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    ...
);

-- Composite primary key
CREATE TABLE table_name (
    col1 INT,
    col2 INT,
    PRIMARY KEY (col1, col2)
);

-- Add later
ALTER TABLE table_name ADD PRIMARY KEY (column);

FOREIGN KEY

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- Add later
ALTER TABLE orders 
ADD CONSTRAINT fk_user 
FOREIGN KEY (user_id) REFERENCES users(id);

UNIQUE

CREATE TABLE table_name (
    email VARCHAR(100) UNIQUE
);

ALTER TABLE table_name ADD UNIQUE (column);
ALTER TABLE table_name ADD CONSTRAINT uc_email UNIQUE (email);

CHECK

CREATE TABLE table_name (
    age INT CHECK (age >= 18)
);

ALTER TABLE table_name ADD CONSTRAINT chk_age CHECK (age >= 18);

NOT NULL & DEFAULT

CREATE TABLE table_name (
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active'
);

---

Functions & Operators

String Functions

CONCAT(str1, str2, ...)
CONCAT_WS(separator, str1, str2, ...)
LENGTH(str) / CHAR_LENGTH(str)
UPPER(str) / LOWER(str)
TRIM(str) / LTRIM(str) / RTRIM(str)
SUBSTRING(str, start, length)
REPLACE(str, from_str, to_str)
REVERSE(str)
INSTR(str, substr)
LPAD(str, length, padstr) / RPAD(str, length, padstr)
LEFT(str, length) / RIGHT(str, length)

Numeric Functions

ABS(x), CEIL(x), FLOOR(x), ROUND(x,d)
MOD(x,y), POW(x,y), SQRT(x)
RAND(), GREATEST(a,b,c), LEAST(a,b,c)

Date/Time Functions

NOW(), CURDATE(), CURTIME()
DATE_ADD(date, INTERVAL value unit)
DATE_SUB(date, INTERVAL value unit)
DATEDIFF(date1, date2)
TIMEDIFF(time1, time2)
DATE_FORMAT(date, format)
EXTRACT(unit FROM date)
YEAR(date), MONTH(date), DAY(date)
HOUR(time), MINUTE(time), SECOND(time)
UNIX_TIMESTAMP(), FROM_UNIXTIME()

Aggregate Functions

COUNT(*) / COUNT(column) / COUNT(DISTINCT column)
SUM(column) / AVG(column)
MAX(column) / MIN(column)
GROUP_CONCAT(column [ORDER BY ...] [SEPARATOR '...'])

Conditional Functions

IF(condition, value_if_true, value_if_false)
IFNULL(expr1, expr2)
NULLIF(expr1, expr2)
COALESCE(value1, value2, ...)
CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result
END

Window Functions (MySQL 8.0+)

ROW_NUMBER() OVER (PARTITION BY column ORDER BY column)
RANK() OVER (ORDER BY column)
DENSE_RANK() OVER (ORDER BY column)
NTILE(n) OVER (ORDER BY column)
LAG(column, offset) OVER (ORDER BY column)
LEAD(column, offset) OVER (ORDER BY column)
FIRST_VALUE(column) OVER (ORDER BY column)
LAST_VALUE(column) OVER (ORDER BY column)
SUM/AVG/COUNT(column) OVER (PARTITION BY column)

---

Stored Procedures & Functions

Stored Procedure

-- Create procedure
DELIMITER //
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(100))
BEGIN
    -- SQL statements
    SELECT * FROM table WHERE id = param1;
    SET param2 = 'result';
END //
DELIMITER ;

-- Call procedure
CALL procedure_name(1, @output);
SELECT @output;

-- Drop procedure
DROP PROCEDURE IF EXISTS procedure_name;

-- Show procedures
SHOW PROCEDURE STATUS;
SHOW CREATE PROCEDURE procedure_name;

Functions

DELIMITER //
CREATE FUNCTION function_name(param INT) 
RETURNS INT
DETERMINISTIC  -- or NOT DETERMINISTIC
READS SQL DATA -- or NO SQL, MODIFIES SQL DATA
BEGIN
    DECLARE result INT;
    -- Function logic
    RETURN result;
END //
DELIMITER ;

-- Use function
SELECT function_name(5);

-- Drop function
DROP FUNCTION IF EXISTS function_name;

---

Triggers

Create Trigger

DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- OLD and NEW references
    -- INSERT: NEW only
    -- DELETE: OLD only
    -- UPDATE: OLD and NEW
    INSERT INTO audit_log (action, old_value, new_value)
    VALUES ('UPDATE', OLD.value, NEW.value);
END //
DELIMITER ;

Manage Triggers

SHOW TRIGGERS;
SHOW CREATE TRIGGER trigger_name;
DROP TRIGGER IF EXISTS trigger_name;

---

Views

Create View

CREATE VIEW view_name AS
SELECT col1, col2
FROM table
WHERE condition;

CREATE OR REPLACE VIEW view_name AS SELECT ...;

Manage Views

SHOW FULL TABLES WHERE table_type = 'VIEW';
SHOW CREATE VIEW view_name;
DROP VIEW IF EXISTS view_name;
ALTER VIEW view_name AS SELECT ...;

Updatable Views

-- Must meet conditions: no aggregates, DISTINCT, GROUP BY, 
-- UNION, subqueries in SELECT, etc.
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Then you can UPDATE, DELETE, INSERT through the view
UPDATE active_users SET email = 'new@email.com' WHERE id = 1;

---

User Management

Create User

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
CREATE USER 'username'@'%' IDENTIFIED BY 'password';  -- any host
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant Privileges

-- Specific privileges
GRANT SELECT, INSERT, UPDATE ON database.table TO 'user'@'host';
GRANT ALL PRIVILEGES ON database.* TO 'user'@'host';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'host' WITH GRANT OPTION;

-- Execute stored procedures
GRANT EXECUTE ON PROCEDURE database.proc_name TO 'user'@'host';

-- Show grants
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER();

Revoke Privileges

REVOKE privilege ON database.table FROM 'user'@'host';
REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'host';

Managing Users

-- Change password
ALTER USER 'user'@'host' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'user'@'host' = 'new_password';

-- Drop user
DROP USER 'user'@'host';

-- Rename user
RENAME USER 'old_name'@'host' TO 'new_name'@'host';

-- List users
SELECT User, Host FROM mysql.user;

Roles (MySQL 8.0+)

CREATE ROLE 'app_developer', 'app_read', 'app_write';
GRANT SELECT ON app.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app.* TO 'app_write';
GRANT ALL ON app.* TO 'app_developer';

GRANT 'app_developer' TO 'user1'@'localhost';
SET DEFAULT ROLE 'app_developer' TO 'user1'@'localhost';

---

Transactions

Basic Transaction

START TRANSACTION;
-- or BEGIN;
-- or BEGIN WORK;

-- SQL statements
INSERT INTO accounts VALUES (1, 'Alice', 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;
-- or ROLLBACK;

Savepoints

START TRANSACTION;
INSERT INTO table1 VALUES (1);
SAVEPOINT savepoint1;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;

Transaction Isolation Levels

-- View current isolation level
SELECT @@transaction_isolation;
SHOW VARIABLES LIKE 'transaction_isolation';

-- Set isolation level
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- Default
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Global setting
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Locking

-- Table locks
LOCK TABLES table1 READ, table2 WRITE;
UNLOCK TABLES;

-- Row-level locks (InnoDB)
SELECT * FROM table WHERE id = 1 FOR UPDATE;
SELECT * FROM table WHERE id = 1 FOR SHARE;  -- MySQL 8.0+
SELECT * FROM table WHERE id = 1 LOCK IN SHARE MODE;  -- Older versions

---

Backup & Restore

Using mysqldump

# Backup single database
mysqldump -u username -p database_name > backup.sql

# Backup all databases
mysqldump -u username -p --all-databases > all_backup.sql

# Backup specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Backup with options
mysqldump -u username -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  database_name > backup.sql

# Backup structure only (no data)
mysqldump -u username -p --no-data database_name > structure.sql

# Backup data only
mysqldump -u username -p --no-create-info database_name > data.sql

Restore Database

# Restore from dump
mysql -u username -p database_name < backup.sql

# Restore all databases
mysql -u username -p < all_backup.sql

# Restore compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

Using mysqlpump (MySQL 5.7+)

# Parallel backup
mysqlpump -u username -p \
  --default-parallelism=4 \
  database_name > backup.sql

Export/Import CSV

-- Export to CSV
SELECT * INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

-- Import from CSV
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

---

Performance Optimization

EXPLAIN Queries

EXPLAIN SELECT * FROM table WHERE condition;
EXPLAIN FORMAT=JSON SELECT * FROM table WHERE condition;
EXPLAIN ANALYZE SELECT * FROM table WHERE condition;  -- MySQL 8.0.18+

Analyze & Optimize

-- Analyze table (update key distribution)
ANALYZE TABLE table_name;

-- Optimize table (reclaim unused space)
OPTIMIZE TABLE table_name;

-- Check table for errors
CHECK TABLE table_name;

-- Repair table
REPAIR TABLE table_name;

Query Cache (removed in MySQL 8.0)

-- Older versions only
SHOW VARIABLES LIKE 'query_cache%';
SET GLOBAL query_cache_size = 16777216;
RESET QUERY CACHE;

Performance Schema

-- Check if enabled
SHOW VARIABLES LIKE 'performance_schema';

-- Query performance
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- Find full table scans
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0 OR SUM_NO_GOOD_INDEX_USED > 0;

Server Variables for Performance

-- View all variables
SHOW VARIABLES;
SHOW VARIABLES LIKE '%buffer%';

-- Important performance variables
SHOW STATUS LIKE 'Innodb_buffer_pool%';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'slow_query_log';

-- Set variables
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB
SET GLOBAL max_connections = 500;

Slow Query Log

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Log queries taking > 2 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- Check slow query log status
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Connection Pooling Tips

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
KILL connection_id;
SHOW STATUS LIKE 'Threads_%';
SHOW STATUS LIKE 'Connections';

Common Optimization Practices

-- Use indexes on WHERE, JOIN, ORDER BY columns
-- Avoid SELECT *
-- Use appropriate data types
-- Use LIMIT for large result sets
-- Denormalize when necessary
-- Use EXPLAIN to analyze queries
-- Partition large tables
-- Regular maintenance (ANALYZE, OPTIMIZE)

Monitor Table Status

SHOW TABLE STATUS FROM database_name;
SHOW TABLE STATUS LIKE 'table_name';
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name';

---

Common CLI Commands

# Connect to MySQL
mysql -u username -p
mysql -u username -p -h hostname database_name

# Execute SQL from file
mysql -u username -p database_name < file.sql

# Execute single command
mysql -u username -p -e "SELECT * FROM table" database_name

# Check MySQL version
mysql --version

# Show MySQL status
mysqladmin -u root -p status
mysqladmin -u root -p extended-status

# Secure installation
mysql_secure_installation

# Check system variables without logging in
mysqladmin -u root -p variables

---