MySQL Cheat Sheet

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message TEXT,
  level VARCHAR(20) DEFAULT 'info'
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

ALTER TABLE users MODIFY COLUMN name VARCHAR(500) NOT NULL;

ALTER TABLE users CHANGE name full_name VARCHAR(255) NOT NULL;

DROP TABLE IF EXISTS temp_data;

CREATE INDEX idx_users_email ON users(email);

CREATE VIEW active_users AS
SELECT id, name, email FROM users
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);

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

INSERT INTO users (name, email)
VALUES ('Dave Wilson', 'dave@example.com');

SELECT * FROM users WHERE name = 'Dave Wilson';

INSERT INTO products (name, category, price, stock) VALUES
  ('Headphones', 'Electronics', 149.99, 100),
  ('Webcam', 'Electronics', 89.99, 60);

SELECT * FROM products WHERE name IN ('Headphones', 'Webcam');

UPDATE orders SET status = 'shipped'
WHERE id = 3;

SELECT * FROM orders WHERE id = 3;

DELETE FROM orders WHERE status = 'pending';

SELECT * FROM orders;

REPLACE INTO users (id, name, email)
VALUES (1, 'Alice Updated', 'alice.new@example.com');

SELECT * FROM users WHERE id = 1;

INSERT INTO users (id, name, email)
VALUES (2, 'Bob Updated', 'bob.new@example.com')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

SELECT * FROM users WHERE id = 2;

-- TRUNCATE is faster than DELETE for removing all rows
-- TRUNCATE TABLE logs;

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

SELECT name, email FROM users;

SELECT * FROM products;

SELECT * FROM orders WHERE price > 50 AND status = 'completed';

SELECT * FROM products ORDER BY price DESC;

SELECT * FROM products ORDER BY price DESC LIMIT 3 OFFSET 1;

SELECT status, COUNT(*) as count, SUM(price) as total
FROM orders GROUP BY status;

SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

SELECT DISTINCT category FROM products;

SELECT users.name, orders.product, orders.price
FROM orders
INNER JOIN users ON orders.user_id = users.id;

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

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE price > 100);

WITH high_value_orders AS (
  SELECT user_id, SUM(price) as total
  FROM orders
  GROUP BY user_id
  HAVING total > 100
)
SELECT users.name, high_value_orders.total
FROM high_value_orders
JOIN users ON users.id = high_value_orders.user_id;

SELECT name, 'user' as type FROM users
UNION ALL
SELECT name, 'product' as type FROM products;

SELECT name, price,
  ROW_NUMBER() OVER (ORDER BY price DESC) as row_rank,
  SUM(price) OVER () as total
FROM products;

SELECT COUNT(*) as total_orders FROM orders;

SELECT
  SUM(price) as total_revenue,
  AVG(price) as avg_order_value
FROM orders;

SELECT MIN(price) as cheapest, MAX(price) as most_expensive
FROM products;

SELECT name, COALESCE(email, 'no email') as email
FROM users;

SELECT name, NULLIF(stock, 0) as stock_or_null
FROM products;

SELECT product, price,
  CASE
    WHEN price > 500 THEN 'expensive'
    WHEN price > 100 THEN 'moderate'
    ELSE 'affordable'
  END as price_tier
FROM orders;

SELECT name, stock,
  IF(stock > 0, 'In Stock', 'Out of Stock') as availability
FROM products;

SELECT
  UPPER(name) as uppercase,
  LENGTH(name) as name_length,
  SUBSTRING_INDEX(email, '@', 1) as username
FROM users;

SELECT
  NOW() as current_time,
  CURDATE() as today,
  DATE_ADD(CURDATE(), INTERVAL 7 DAY) as next_week,
  YEAR(NOW()) as current_year;

SELECT JSON_EXTRACT('{"name": "Alice", "age": 30}', '$.name') as name;

SELECT user_id,
  GROUP_CONCAT(product SEPARATOR ', ') as products
FROM orders
GROUP BY user_id;

SELECT FIND_IN_SET('b', 'a,b,c,d') as position;

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  department_id INT
);

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

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE,
  username VARCHAR(100) UNIQUE
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

CREATE TABLE logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message TEXT,
  level VARCHAR(20) DEFAULT 'info',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),
  stock INT CHECK (stock >= 0)
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending'
);

START TRANSACTION;

INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- Changes are not visible to other connections yet

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
COMMIT;

SELECT * FROM users WHERE name = 'Test';

START TRANSACTION;
DELETE FROM users;
ROLLBACK;

-- Users table is unchanged
SELECT * FROM users;

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('A', 'a@test.com');
SAVEPOINT my_savepoint;
INSERT INTO users (name, email) VALUES ('B', 'b@test.com');
ROLLBACK TO SAVEPOINT my_savepoint;
COMMIT;

-- Only user 'A' was inserted
SELECT * FROM users WHERE email LIKE '%test.com';

-- Lock for reading (others can read but not write)
-- LOCK TABLES users READ;
-- SELECT * FROM users;
-- UNLOCK TABLES;

-- Lock specific rows
-- START TRANSACTION;
-- SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- UPDATE orders SET status = 'processing' WHERE id = 1;
-- COMMIT;

SHOW TABLES;

DESCRIBE users;

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- Reclaim space after many DELETEs
-- OPTIMIZE TABLE orders;

SELECT * FROM users WHERE email LIKE '%@example.com';

SELECT * FROM users WHERE email REGEXP '^[a-z]+@example\.com$';

-- Create fulltext index
-- ALTER TABLE products ADD FULLTEXT(name, category);

-- Natural language search
-- SELECT * FROM products
-- WHERE MATCH(name) AGAINST('laptop');

SET @total = 0;
SELECT @total := @total + price as running_total
FROM orders
ORDER BY id;

INSERT INTO users (name, email) VALUES ('New User', 'new@example.com');
SELECT LAST_INSERT_ID() as new_user_id;