【SQL】自分用チートシートをつくりました

チートシート

-- =====================================
-- SQL CHEAT SHEET (基本〜中級)
-- =====================================

-- テーブル一覧
SHOW TABLES;

-- テーブル構造表示
DESCRIBE table_name;

-- =====================================
-- SELECT（検索）
-- =====================================
SELECT * FROM table_name;
SELECT col1, col2 FROM table_name;
SELECT DISTINCT col FROM table_name;
SELECT * FROM table_name WHERE col = value;
SELECT * FROM table_name WHERE col > value;
SELECT * FROM table_name WHERE col BETWEEN x AND y;
SELECT * FROM table_name WHERE col IN (x, y, z);
SELECT * FROM table_name WHERE col LIKE '%text%';
SELECT * FROM table_name WHERE col IS NULL;

-- ORDER BY
SELECT * FROM table_name ORDER BY col ASC;
SELECT * FROM table_name ORDER BY col DESC;

-- LIMIT
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 10 OFFSET 20;

-- =====================================
-- 集約関数
-- =====================================
SELECT COUNT(*) FROM table_name;
SELECT AVG(col) FROM table_name;
SELECT SUM(col) FROM table_name;
SELECT MIN(col) FROM table_name;
SELECT MAX(col) FROM table_name;

-- =====================================
-- 集約関数 + GROUP BY / HAVING
-- =====================================
-- 基本
SELECT col, COUNT(*) AS cnt
FROM table_name
GROUP BY col;

SELECT col1, col2, SUM(amount) AS total
FROM table_name
GROUP BY col1, col2;

-- 条件付き集計
SELECT col, COUNT(*) AS cnt
FROM table_name
GROUP BY col
HAVING COUNT(*) > 3;

-- ORDER BY と組み合わせ
SELECT col, SUM(amount) AS total
FROM table_name
GROUP BY col
ORDER BY total DESC;

-- =====================================
-- JOIN（結合）
-- =====================================
-- 内部結合
SELECT *
FROM A
JOIN B ON A.id = B.a_id;

-- 左外部結合
SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id;

-- 右外部結合
SELECT *
FROM A
RIGHT JOIN B ON A.id = B.a_id;

-- クロス結合
SELECT *
FROM A
CROSS JOIN B;

-- =====================================
-- INSERT（追加）
-- =====================================
INSERT INTO table_name (col1, col2) VALUES (v1, v2);

-- 複数行
INSERT INTO table_name (col1, col2) VALUES 
(v1, v2),
(v3, v4);

-- =====================================
-- UPDATE（更新）
-- =====================================
UPDATE table_name
SET col = value
WHERE id = x;

-- =====================================
-- DELETE（削除）
-- =====================================
DELETE FROM table_name WHERE id = x;

-- テーブル全削除（注意）
DELETE FROM table_name;
TRUNCATE table_name;

-- =====================================
-- テーブル作成
-- =====================================
CREATE TABLE table_name (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================
-- 列追加 / 削除 / 修正
-- =====================================
ALTER TABLE table_name ADD COLUMN age INT;
ALTER TABLE table_name DROP COLUMN age;
ALTER TABLE table_name MODIFY COLUMN age BIGINT;

-- =====================================
-- INDEX
-- =====================================
CREATE INDEX idx_col ON table_name (col);
DROP INDEX idx_col;

-- =====================================
-- サブクエリ
-- =====================================
SELECT *
FROM table_name
WHERE col IN (SELECT col FROM other_table);

-- =====================================
-- ビュー
-- =====================================
CREATE VIEW view_name AS
SELECT col1, col2 FROM table_name;

DROP VIEW view_name;

動作検証してないですけど多分動くと思います。