چیت‌شیت MySQL

1CREATE TABLE users (2  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,3  email VARCHAR(255) NOT NULL,4  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,5  PRIMARY KEY (id),6  UNIQUE KEY users_email_unique (email)7);

1CREATE TABLE orders (2  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,3  user_id BIGINT UNSIGNED NOT NULL,4  total DECIMAL(10, 2) NOT NULL,5  PRIMARY KEY (id),6  CONSTRAINT orders_user_fk7    FOREIGN KEY (user_id) REFERENCES users (id)8);

1ALTER TABLE users2  ADD COLUMN display_name VARCHAR(120) NULL,3  ADD INDEX users_created_at_idx (created_at);

1SELECT id, email, created_at2FROM users3WHERE created_at >= ?4ORDER BY created_at DESC, id DESC5LIMIT 20;

1SELECT id, status, total2FROM orders3WHERE status IN ('paid', 'shipped')4  AND total BETWEEN 100.00 AND 500.005  AND cancelled_at IS NULL;

1SELECT id, title2FROM posts3WHERE id < ?4ORDER BY id DESC5LIMIT 25;

1INSERT INTO users (email, display_name)2VALUES (?, ?);

1UPDATE users2SET display_name = ?3WHERE id = ?;

1DELETE FROM sessions2WHERE expires_at < CURRENT_TIMESTAMP;

1INSERT INTO settings (setting_key, setting_value)2VALUES ('theme', 'dark') AS incoming3ON DUPLICATE KEY UPDATE4  setting_value = incoming.setting_value;

1SELECT users.email, orders.total2FROM users3JOIN orders ON orders.user_id = users.id4WHERE orders.status = 'paid';

1SELECT user_id, COUNT(*) AS order_count,2       SUM(total) AS revenue3FROM orders4GROUP BY user_id5HAVING COUNT(*) >= 2;

1SELECT2  COALESCE(status, 'unknown') AS status_label,3  SUM(CASE WHEN total >= 100 THEN 1 ELSE 0 END) AS large_orders4FROM orders5GROUP BY COALESCE(status, 'unknown');

1EXPLAIN2SELECT id, email3FROM users4WHERE email = 'person@example.test';

1START TRANSACTION;23UPDATE accounts SET balance = balance - 25 WHERE id = 1;4UPDATE accounts SET balance = balance + 25 WHERE id = 2;56COMMIT;

1SHOW PROCESSLIST;2SHOW STATUS LIKE 'Threads_connected';