MySQL Cheat Sheet

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

RENAME TABLE orders TO customer_orders;

CREATE TABLE orders_backup LIKE orders;
INSERT INTO orders_backup SELECT * FROM orders;

ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'new';

ALTER TABLE orders RENAME COLUMN total TO amount;  -- 8.0+
ALTER TABLE orders CHANGE total amount DECIMAL(10,2) NOT NULL;  -- 5.7

ALTER TABLE orders DROP COLUMN status;

ALTER TABLE orders MODIFY COLUMN total DECIMAL(12,2) NOT NULL;

TRUNCATE TABLE orders;

DROP TABLE IF EXISTS orders_backup;

DESCRIBE orders;
SHOW FULL COLUMNS FROM orders;

SHOW CREATE TABLE orders\G

INSERT INTO orders (customer_id, total)
VALUES (1, 49.90), (2, 12.00);

INSERT INTO orders_archive (id, total)
SELECT id, total FROM orders WHERE created_at < NOW() - INTERVAL 1 YEAR;

SELECT LAST_INSERT_ID();

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

UPDATE orders o
JOIN order_totals t ON t.order_id = o.id
SET o.total = t.amount;

DELETE FROM orders WHERE created_at < NOW() - INTERVAL 2 YEAR;

INSERT INTO orders (id, total) VALUES (1, 49.90) AS new
ON DUPLICATE KEY UPDATE total = new.total;  -- 8.0.19+

INSERT IGNORE INTO orders (id, total) VALUES (1, 49.90);

SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

DELETE a FROM customers a
JOIN customers b ON a.email = b.email AND a.id > b.id;

SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET 100;

CREATE OR REPLACE VIEW recent_orders AS
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 30 DAY;

SELECT CASE WHEN total > 100 THEN 'large' ELSE 'small' END AS bucket
FROM orders;

SELECT CURDATE(), NOW(), UTC_TIMESTAMP();

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') FROM orders;

SELECT DATEDIFF(delivered_at, created_at) AS days,
       TIMESTAMPDIFF(HOUR, created_at, delivered_at) AS hours
FROM orders;

SELECT DATE_ADD(created_at, INTERVAL 7 DAY), DATE_SUB(NOW(), INTERVAL 1 MONTH);

SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*)
FROM orders GROUP BY month ORDER BY month;

SELECT CONCAT(first_name, ' ', last_name) AS name,
       CONCAT_WS(', ', city, country)     AS location
FROM customers;

SELECT customer_id, GROUP_CONCAT(sku ORDER BY sku SEPARATOR ', ')
FROM order_items GROUP BY customer_id;

SELECT COALESCE(nickname, first_name, 'there') FROM customers;

SELECT created_at, SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

CREATE INDEX orders_created_at_idx ON orders (created_at);

CREATE UNIQUE INDEX customers_email_idx ON customers (email);

SHOW INDEX FROM orders;

DROP INDEX orders_created_at_idx ON orders;

ALTER TABLE orders ADD PRIMARY KEY (id);

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

ALTER TABLE orders DROP FOREIGN KEY orders_customer_fk;

EXPLAIN SELECT * FROM orders WHERE status = 'new';

SHOW DATABASES;

CREATE DATABASE analytics
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE analytics;
SHOW TABLES;

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY mb DESC;

SHOW FULL PROCESSLIST;

KILL QUERY 12345;      -- stop the statement
KILL CONNECTION 12345; -- drop the session

GRANT SELECT ON analytics.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

SELECT VERSION(), DATABASE(), CURRENT_USER();