MySQL Cheatsheet

mysql -u root -p
mysql -u user -p -h hostname dbname
mysql -u user -p -e "SELECT 1"

# Basic commands
SHOW DATABASES;
USE mydb;
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users;

-- Create table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_name (name)
);

-- Alter table
ALTER TABLE users ADD phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY name VARCHAR(200);
ALTER TABLE users RENAME TO accounts;

-- Drop
DROP TABLE IF EXISTS users;
TRUNCATE TABLE users;

-- Insert
INSERT INTO users (name, email) VALUES ('Alice', 'a@b.com');
INSERT INTO users (name, email) VALUES
  ('Bob', 'b@b.com'),
  ('Carol', 'c@b.com');

-- Update
UPDATE users SET age = 26 WHERE name = 'Alice';
UPDATE users SET age = age + 1;

-- Delete
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;

-- Upsert
INSERT INTO users (id, name) VALUES (1, 'Alice')
  ON DUPLICATE KEY UPDATE name = 'Alice';

SELECT * FROM users;
SELECT name, email FROM users WHERE age > 18;
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT DISTINCT status FROM users;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE age IN (25, 30, 35);
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;

-- Aggregation
SELECT COUNT(*) FROM users;
SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT status, AVG(age) FROM users GROUP BY status HAVING COUNT(*) > 5;

-- Subquery
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

-- INNER JOIN
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

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

-- RIGHT JOIN / FULL OUTER JOIN (emulated)
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- Self join
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- String
CONCAT('Hello', ' ', 'World')
UPPER(name)  LOWER(name)
SUBSTRING(name, 1, 3)
LENGTH(name)  TRIM(name)
REPLACE(name, 'old', 'new')

-- Date
NOW()  CURDATE()  CURTIME()
DATE_FORMAT(created_at, '%Y-%m-%d')
DATEDIFF(NOW(), created_at)
DATE_ADD(NOW(), INTERVAL 7 DAY)

-- Aggregate
COUNT  SUM  AVG  MIN  MAX
GROUP_CONCAT(name SEPARATOR ', ')

-- Conditional
IF(age > 18, 'adult', 'minor')
CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END
COALESCE(name, 'Unknown')
IFNULL(name, 'N/A')

-- Users
CREATE USER 'app'@'%' IDENTIFIED BY 'password';
GRANT ALL ON mydb.* TO 'app'@'%';
FLUSH PRIVILEGES;

-- Backup & Restore
mysqldump -u root -p mydb > backup.sql
mysql -u root -p mydb < backup.sql

-- Performance
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
SHOW PROCESSLIST;
SHOW STATUS;