MySQL Cheat Sheet

Installation & Setup

# Ubuntu/Debian
sudo apt update && sudo apt install mysql-server

# CentOS/RHEL
sudo yum install mysql-server

# macOS
brew install mysql

# Windows: Download from mysql.com

# Start service
sudo systemctl start mysql # Linux
brew services start mysql # macOS

# Secure installation
sudo mysql_secure_installation

Note: After installation, run security script to set root password and secure your instance.

Connection & Basic Commands

Command	Description
mysql -u [user] -p	Connect locally
mysql -h [host] -u [user] -p	Connect to remote server
SHOW DATABASES;	List all databases
USE [database];	Select database
SHOW TABLES;	List tables in current database
DESC [table];	Describe table structure
SOURCE file.sql;	Execute SQL file
EXIT or \q	Quit MySQL shell

Database Operations

CREATE DATABASE db_name;

DROP DATABASE db_name;

ALTER DATABASE db_name CHARACTER SET utf8mb4;

SHOW CREATE DATABASE db_name;

Warning: DROP DATABASE permanently deletes all data. Use with caution!

Table Operations

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 18),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users ADD COLUMN phone VARCHAR(15);

ALTER TABLE users DROP COLUMN age;

ALTER TABLE users MODIFY COLUMN name VARCHAR(100);

DROP TABLE users;

CRUD Operations

-- INSERT
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

-- SELECT
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 25;
SELECT DISTINCT name FROM users;

-- UPDATE
UPDATE users SET email = 'new@email.com' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 3;

Important: Always use WHERE clause with UPDATE and DELETE to avoid modifying all rows!

Query Clauses

Clause	Example	Purpose
WHERE	WHERE age >= 21 AND country = 'US'	Filter records
ORDER BY	ORDER BY created_at DESC	Sort results
LIMIT	LIMIT 10 OFFSET 20	Pagination
GROUP BY	GROUP BY department	Group rows
HAVING	HAVING COUNT(*) > 5	Filter groups
LIKE	WHERE name LIKE 'J%'	Pattern matching
IN	WHERE id IN (1,5,7)	Multiple value check

Joins

-- INNER JOIN
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

-- LEFT JOIN
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- SELF JOIN
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

User Management

-- Create User
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

-- Grant Privileges
GRANT SELECT, INSERT ON db.* TO 'user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- Revoke Privileges
REVOKE DELETE ON db.* FROM 'user'@'%';

-- Show Privileges
SHOW GRANTS FOR 'user'@'localhost';

Security Tip: Always grant minimum required privileges to users

Aggregation

SELECT
  COUNT(*) AS total_users,
  AVG(age) AS average_age,
  MAX(salary) AS max_salary,
  MIN(created_at) AS first_signup,
  SUM(revenue) AS total_revenue
FROM users;

Indexes & Optimization

-- Create Index
CREATE INDEX idx_email ON users(email);

-- Show Indexes
SHOW INDEXES FROM users;

-- Explain Query
EXPLAIN SELECT * FROM users WHERE age > 30;

Tip: Indexes speed up SELECT queries but slow down INSERT/UPDATE

Backup & Restore

# Export single database
mysqldump -u root -p db_name > backup.sql

# Import database
mysql -u root -p db_name < backup.sql

# Backup all databases
mysqldump --all-databases > full_backup.sql

Important: Regularly backup your databases to prevent data loss

Transactions

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK to undo

Note: Use transactions for operations that need to be atomic