← Back to all cheatsheets
Database
mysqldatabasesqlrdbmsmariadb

MySQL Cheat Sheet

Connection and Basic Commands

Connect to MySQL

# Connect to local MySQL
mysql -u username -p

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

# Connect to specific database
mysql -u username -p database_name

# Connect with port
mysql -h hostname -P 3306 -u username -p

# Execute command from command line
mysql -u username -p -e "SELECT * FROM table"

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

# Exit MySQL
exit
quit
\q

Show Information

-- Show MySQL version
SELECT VERSION();
SHOW VARIABLES LIKE '%version%';

-- Show current user
SELECT USER();
SELECT CURRENT_USER();

-- Show current database
SELECT DATABASE();

-- Show server status
SHOW STATUS;

-- Show processes
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Show variables
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';

-- Show warnings/errors
SHOW WARNINGS;
SHOW ERRORS;

Database Operations

Create and Manage Databases

-- Create database
CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name;

-- Create with character set
CREATE DATABASE database_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- List all databases
SHOW DATABASES;
SHOW DATABASES LIKE 'test%';

-- Select database
USE database_name;

-- Drop database
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

-- Rename database (workaround)
-- MySQL doesn't support direct rename
CREATE DATABASE new_name;
RENAME TABLE old_db.table TO new_db.table;

-- Show database size
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema;

-- Show single database size
SELECT
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name';

Table Operations

Create Tables

-- Basic table creation
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with multiple constraints
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
);

-- Create table from another table
CREATE TABLE new_table AS SELECT * FROM old_table;

-- Create table like another (structure only)
CREATE TABLE new_table LIKE old_table;

-- Create temporary table
CREATE TEMPORARY TABLE temp_table (
    id INT,
    name VARCHAR(50)
);

Alter Tables

-- Add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN age INT AFTER email;

-- Modify column
ALTER TABLE users MODIFY COLUMN username VARCHAR(100);
ALTER TABLE users CHANGE old_name new_name VARCHAR(50);

-- Drop column
ALTER TABLE users DROP COLUMN phone;

-- Add primary key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Drop primary key
ALTER TABLE users DROP PRIMARY KEY;

-- Add foreign key
ALTER TABLE orders
    ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Drop foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_name;

-- Add index
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD UNIQUE INDEX idx_username (username);

-- Drop index
ALTER TABLE users DROP INDEX idx_email;

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

-- Change engine
ALTER TABLE users ENGINE = InnoDB;

Show Table Information

-- List all tables
SHOW TABLES;
SHOW TABLES LIKE 'user%';

-- Show table structure
DESCRIBE users;
DESC users;
SHOW COLUMNS FROM users;

-- Show create table statement
SHOW CREATE TABLE users;

-- Show table status
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'users';

-- Show indexes
SHOW INDEX FROM users;

-- Show table size
SELECT
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'database_name'
ORDER BY (data_length + index_length) DESC;

Drop Tables

-- Drop table
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

-- Drop multiple tables
DROP TABLE table1, table2, table3;

-- Truncate table (faster than DELETE)
TRUNCATE TABLE table_name;

Data Manipulation

INSERT

-- Insert single row
INSERT INTO users (username, email)
VALUES ('john', '[email protected]');

-- Insert multiple rows
INSERT INTO users (username, email) VALUES
    ('alice', '[email protected]'),
    ('bob', '[email protected]'),
    ('charlie', '[email protected]');

-- Insert from SELECT
INSERT INTO new_users (username, email)
SELECT username, email FROM users WHERE created_at > '2024-01-01';

-- Insert or update (UPSERT)
INSERT INTO users (id, username, email)
VALUES (1, 'john', '[email protected]')
ON DUPLICATE KEY UPDATE
    username = VALUES(username),
    email = VALUES(email);

-- Insert ignore (skip duplicates)
INSERT IGNORE INTO users (username, email)
VALUES ('john', '[email protected]');

SELECT

-- Basic select
SELECT * FROM users;
SELECT username, email FROM users;

-- Where clause
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- Like and wildcards
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- IN operator
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE status IN ('active', 'pending');

-- Between
SELECT * FROM orders WHERE price BETWEEN 10 AND 100;

-- Null checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Order by
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY username ASC, created_at DESC;

-- Limit and offset
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users LIMIT 20, 10;  -- offset, limit

-- Distinct
SELECT DISTINCT status FROM orders;

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT status) FROM orders;

UPDATE

-- Basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- Update multiple columns
UPDATE users
SET username = 'newname', email = '[email protected]'
WHERE id = 1;

-- Update with calculation
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- Update from another table
UPDATE users u
JOIN profiles p ON u.id = p.user_id
SET u.status = 'verified'
WHERE p.verified = 1;

-- Update all rows (be careful!)
UPDATE users SET status = 'inactive';

DELETE

-- Delete specific rows
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2020-01-01';

-- Delete with limit
DELETE FROM logs ORDER BY created_at ASC LIMIT 1000;

-- Delete all rows (use TRUNCATE instead for better performance)
DELETE FROM temp_table;

Joins

Types of Joins

-- Inner join
SELECT u.username, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Right join
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;

-- Self join
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Multiple joins
SELECT u.username, o.order_id, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

Aggregate Functions

Common Aggregates

-- Count
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;

-- Sum
SELECT SUM(price) FROM orders;
SELECT SUM(quantity * price) AS total FROM order_items;

-- Average
SELECT AVG(price) FROM products;
SELECT AVG(age) FROM users WHERE status = 'active';

-- Min/Max
SELECT MIN(price), MAX(price) FROM products;
SELECT MIN(created_at), MAX(created_at) FROM orders;

-- Group by
SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT user_id, SUM(price) FROM orders GROUP BY user_id;

-- Having (filter after grouping)
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING order_count > 5;

-- Group by with multiple columns
SELECT DATE(created_at) AS date, status, COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at), status
ORDER BY date DESC;

User Management

Create and Manage Users

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

-- Grant privileges
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';
GRANT SELECT ON database_name.table_name TO 'username'@'localhost';

-- Grant all databases
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost';

-- Grant with admin option
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- Show grants
SHOW GRANTS FOR 'username'@'localhost';
SHOW GRANTS FOR CURRENT_USER;

-- Revoke privileges
REVOKE INSERT, UPDATE ON database_name.* FROM 'username'@'localhost';
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

-- Change password
ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');

-- Drop user
DROP USER 'username'@'localhost';

-- List users
SELECT user, host FROM mysql.user;

-- Flush privileges (apply changes)
FLUSH PRIVILEGES;

Backup and Restore

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 compression
mysqldump -u username -p database_name | gzip > backup.sql.gz

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

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

# Backup with routines and triggers
mysqldump -u username -p --routines --triggers database_name > backup.sql

# Backup remote database
mysqldump -h remote_host -u username -p database_name > backup.sql

Restore

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

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

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

# Create database and restore
mysql -u username -p -e "CREATE DATABASE database_name"
mysql -u username -p database_name < backup.sql

Indexes

Create and Manage Indexes

-- Create index
CREATE INDEX idx_username ON users(username);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create composite index
CREATE INDEX idx_name_email ON users(username, email);

-- Create full-text index
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- Show indexes
SHOW INDEX FROM users;

-- Drop index
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;

-- Analyze table (update index statistics)
ANALYZE TABLE users;

Views

Create and Manage Views

-- Create view
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';

-- Create or replace view
CREATE OR REPLACE VIEW user_orders AS
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Show views
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- Show create view
SHOW CREATE VIEW active_users;

-- Drop view
DROP VIEW active_users;
DROP VIEW IF EXISTS active_users;

-- Query view
SELECT * FROM active_users;

Stored Procedures and Functions

Stored Procedures

-- Create procedure
DELIMITER $$
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
    SELECT * FROM orders WHERE user_id = userId;
END$$
DELIMITER ;

-- Call procedure
CALL GetUserOrders(1);

-- Procedure with OUT parameter
DELIMITER $$
CREATE PROCEDURE GetUserCount(OUT userCount INT)
BEGIN
    SELECT COUNT(*) INTO userCount FROM users;
END$$
DELIMITER ;

-- Call with OUT parameter
CALL GetUserCount(@count);
SELECT @count;

-- Show procedures
SHOW PROCEDURE STATUS WHERE Db = 'database_name';

-- Drop procedure
DROP PROCEDURE IF EXISTS GetUserOrders;

Functions

-- Create function
DELIMITER $$
CREATE FUNCTION GetUserName(userId INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE userName VARCHAR(50);
    SELECT username INTO userName FROM users WHERE id = userId;
    RETURN userName;
END$$
DELIMITER ;

-- Use function
SELECT GetUserName(1);

-- Show functions
SHOW FUNCTION STATUS WHERE Db = 'database_name';

-- Drop function
DROP FUNCTION IF EXISTS GetUserName;

Triggers

Create Triggers

-- Before insert trigger
DELIMITER $$
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END$$
DELIMITER ;

-- After update trigger
DELIMITER $$
CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_history (order_id, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, NOW());
END$$
DELIMITER ;

-- Show triggers
SHOW TRIGGERS;
SHOW TRIGGERS FROM database_name;

-- Drop trigger
DROP TRIGGER IF EXISTS before_user_insert;

Transactions

Transaction Control

-- Start transaction
START TRANSACTION;
BEGIN;

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

-- Savepoint
START TRANSACTION;
INSERT INTO users (username) VALUES ('test1');
SAVEPOINT sp1;
INSERT INTO users (username) VALUES ('test2');
ROLLBACK TO SAVEPOINT sp1;  -- Rollback to savepoint
COMMIT;

-- Transaction isolation levels
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Show isolation level
SELECT @@transaction_isolation;

Performance

Query Optimization

-- Explain query execution plan
EXPLAIN SELECT * FROM users WHERE username = 'john';
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id IN (1,2,3);

-- Analyze query
ANALYZE TABLE users;

-- Optimize table
OPTIMIZE TABLE users;

-- Check table
CHECK TABLE users;

-- Repair table
REPAIR TABLE users;

-- Show slow queries
SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Log queries taking > 2 seconds

Configuration

-- Show configuration variables
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb%';

-- Set variables
SET GLOBAL max_connections = 200;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

-- Show status
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Uptime';

-- Show engine status
SHOW ENGINE INNODB STATUS;

Common MySQL Commands

Database Maintenance

-- Check database integrity
mysqlcheck -u username -p --all-databases

-- Optimize all tables
mysqlcheck -u username -p --optimize --all-databases

-- Repair tables
mysqlcheck -u username -p --repair --all-databases

-- Analyze tables
mysqlcheck -u username -p --analyze --all-databases

Import/Export

# Export to CSV
mysql -u username -p -e "SELECT * FROM table" database_name > output.csv

# Import CSV
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

# Export with mysqldump and import
mysqldump -u username -p database_name | mysql -h remote_host -u username -p remote_db

Best Practices

Security

-- Use prepared statements (in application code)
-- Avoid: SELECT * FROM users WHERE id = $id
-- Use: SELECT * FROM users WHERE id = ?

-- Principle of least privilege
GRANT SELECT, INSERT ON database.table TO 'user'@'localhost';

-- Use strong passwords
ALTER USER 'user'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

-- Limit remote access
CREATE USER 'user'@'192.168.1.%' IDENTIFIED BY 'password';

-- Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- Disable remote root login
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1');

Performance Tips

  1. Use indexes on columns used in WHERE, JOIN, ORDER BY
  2. **Avoid SELECT *** - Select only needed columns
  3. Use LIMIT for large result sets
  4. Use EXPLAIN to analyze query performance
  5. Optimize table regularly - OPTIMIZE TABLE
  6. Use appropriate data types - INT vs BIGINT, VARCHAR vs TEXT
  7. Normalize database but denormalize when necessary
  8. Use connection pooling in applications
  9. Enable query cache (if appropriate)
  10. Monitor slow queries and optimize them

Data Types

-- Numeric types
TINYINT     -- 1 byte  (-128 to 127)
SMALLINT    -- 2 bytes (-32768 to 32767)
MEDIUMINT   -- 3 bytes
INT         -- 4 bytes (-2B to 2B)
BIGINT      -- 8 bytes

-- Decimal
DECIMAL(10,2)  -- Fixed precision (price: 99999999.99)
FLOAT          -- Approximate
DOUBLE         -- Approximate

-- String types
CHAR(10)       -- Fixed length
VARCHAR(255)   -- Variable length (max 65,535)
TEXT           -- Long text (max 65,535)
MEDIUMTEXT     -- Very long text (max 16MB)
LONGTEXT       -- Extremely long text (max 4GB)

-- Date and time
DATE           -- YYYY-MM-DD
TIME           -- HH:MM:SS
DATETIME       -- YYYY-MM-DD HH:MM:SS
TIMESTAMP      -- Unix timestamp
YEAR           -- YYYY

-- Binary
BLOB           -- Binary large object
MEDIUMBLOB
LONGBLOB

-- Other
ENUM('value1', 'value2')  -- Enumeration
SET('value1', 'value2')   -- Set
JSON                      -- JSON data (MySQL 5.7+)

Troubleshooting

Common Issues

-- Too many connections
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 200;

-- Lock wait timeout
SHOW PROCESSLIST;
KILL <process_id>;

-- Binary log space issues
SHOW BINARY LOGS;
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

-- Check for locked tables
SHOW OPEN TABLES WHERE In_use > 0;

-- Check InnoDB status
SHOW ENGINE INNODB STATUS;

-- Reset root password (emergency)
-- Stop MySQL and start with --skip-grant-tables
-- Then: ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Quick Reference

-- Connection
mysql -u root -p

-- Database
CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name;

-- Table
CREATE TABLE table_name (id INT PRIMARY KEY);
ALTER TABLE table_name ADD COLUMN col_name VARCHAR(50);
DROP TABLE table_name;

-- Data
INSERT INTO table VALUES (1, 'value');
SELECT * FROM table WHERE condition;
UPDATE table SET col = 'value' WHERE condition;
DELETE FROM table WHERE condition;

-- User
CREATE USER 'user'@'host' IDENTIFIED BY 'password';
GRANT ALL ON db.* TO 'user'@'host';
FLUSH PRIVILEGES;

-- Backup
mysqldump -u user -p db_name > backup.sql
mysql -u user -p db_name < backup.sql