SQL 辞典

SQL コースで覚える構文の一覧です。

SELECT表からデータを取り出すLv1 で覚える

SELECT 列, 列 FROM 表;
SELECT * FROM 表;

* はすべての列。列どうしの計算や、AS で列に別名をつけることもできます。文の最後は ; で終わります。

SELECT name, price FROM items;
SELECT price * 1.1 AS with_tax FROM items;

WHERE条件に合う行だけにするLv3 で覚える

SELECT … FROM 表 WHERE 条件;

= <> < > <= >= で比べます。文字列や日付は '…' で囲みます。AND・OR・NOT で条件を組み合わせ、かっこで優先順位を決めます。

SELECT * FROM items WHERE price >= 1000 AND category = 'book';

ORDER BY並べ替えるLv5 で覚える

… ORDER BY 列 [ASC | DESC], 列 …

ASC は小さい順（省略時）、DESC は大きい順。複数の列を書くと、前の列が同じときに次の列で並べます。

SELECT * FROM items ORDER BY price DESC, name;

DISTINCT重複した行を 1 つにするLv6 で覚える

SELECT DISTINCT 列 FROM 表;

選んだ列の組み合わせが同じ行を 1 行にまとめます。

SELECT DISTINCT category FROM items;

LIMIT / FETCH FIRST件数を絞るLv6 で覚える

… LIMIT n [OFFSET m]
… FETCH FIRST n ROWS ONLY

先頭から n 行だけ返します。ORDER BY と組み合わせて上位 n 件に。FETCH FIRST は標準 SQL の書き方です。

SELECT * FROM items ORDER BY price DESC LIMIT 3;

LIKEパターンで探すLv8 で覚える

列 LIKE 'パターン'
列 NOT LIKE 'パターン'

% は 0 文字以上、_ はちょうど 1 文字。大文字小文字を区別します（PostgreSQL の ILIKE は区別しない）。

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

BETWEEN範囲に入っているLv9 で覚える

列 BETWEEN a AND b

a 以上 b 以下（両端を含む）。日付の範囲にもよく使います。

SELECT * FROM orders WHERE ordered_on BETWEEN '2024-04-01' AND '2024-04-30';

IN候補のどれかに一致するLv9 で覚える

列 IN (値, 値, …)
列 IN (SELECT …)

OR を並べる代わりに使えます。NOT IN は候補に NULL があると何も返らない点に注意。

SELECT * FROM items WHERE category IN ('book', 'game');

IS NULL値がない（NULL）か調べるLv10 で覚える

列 IS NULL
列 IS NOT NULL

NULL は「値がない」こと。= NULL では比べられないので IS NULL を使います。

SELECT * FROM members WHERE phone IS NULL;

COALESCE / NULLIFNULL を置き換えるLv10 で覚える

COALESCE(値, 代わりの値, …)
NULLIF(a, b)

COALESCE は最初の NULL でない値を返します。NULLIF は a と b が同じなら NULL（0 での割り算よけに）。

SELECT COALESCE(phone, '未登録') FROM members;

文字列関数文字を加工するLv11 で覚える

UPPER(s) LOWER(s) LENGTH(s)
SUBSTRING(s FROM 開始 FOR 長さ) / SUBSTR(s, 開始, 長さ)
TRIM(s) REPLACE(s, 古, 新) LPAD(s, 桁, 文字)
s || t  CONCAT(s, t)

|| で文字列をつなげます。位置は 1 から数えます。

SELECT UPPER(name) || '様' FROM members;

数値関数数を丸める・計算するLv12 で覚える

ROUND(x, 桁) TRUNC(x, 桁) CEIL(x) FLOOR(x)
ABS(x) MOD(a, b) POWER(a, b)

ROUND は四捨五入、TRUNC は切り捨て、CEIL は切り上げ。整数どうしの割り算は小数が切り捨てられます。

SELECT ROUND(AVG(price), 1) FROM items;

日付関数日付を扱うLv12 で覚える

CURRENT_DATE  CURRENT_TIMESTAMP
EXTRACT(YEAR FROM 日付)
日付 - 日付（日数）  日付 + 整数

EXTRACT で年・月・日を取り出します。PostgreSQL では date_trunc・to_char・interval も使えます。

SELECT EXTRACT(MONTH FROM ordered_on) FROM orders;

CAST型を変えるLv12 で覚える

CAST(値 AS 型)
値::型（PostgreSQL）

文字列を数や日付に、数を文字列に変えます。

SELECT CAST('2024-04-01' AS DATE);

CASE場合分けするLv13 で覚える

CASE WHEN 条件 THEN 値 … ELSE 値 END
CASE 列 WHEN 値 THEN 値 … END

上から順に調べて最初に合った THEN の値になります。ELSE がなければ NULL。集計の中にも書けます。

SELECT name, CASE WHEN price >= 1000 THEN '高' ELSE '安' END FROM items;

COUNT / SUM / AVG / MIN / MAX集計するLv15 で覚える

COUNT(*) COUNT(列) COUNT(DISTINCT 列)
SUM(列) AVG(列) MIN(列) MAX(列)

COUNT(*) は行数、COUNT(列) は NULL でない値の数。集計関数は NULL を無視します。

SELECT COUNT(*), AVG(price) FROM items;

GROUP BYグループごとに集計するLv16 で覚える

SELECT 列, 集計 FROM 表 GROUP BY 列;

同じ値の行をまとめて集計します。SELECT には GROUP BY の列か集計関数だけを書けます。

SELECT category, COUNT(*) FROM items GROUP BY category;

HAVING集計した結果で絞るLv17 で覚える

… GROUP BY 列 HAVING 集計の条件

WHERE は集計の前に行を絞り、HAVING は集計の後にグループを絞ります。

SELECT category, COUNT(*) FROM items GROUP BY category HAVING COUNT(*) >= 3;

JOIN表をつなぐ（内部結合）Lv21 で覚える

FROM 表 a JOIN 表 b ON a.列 = b.列

ON の条件に合う組み合わせだけを残します。INNER JOIN と同じ。同じ名前の列は 別名.列 で区別します。

SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;

LEFT / RIGHT / FULL JOIN相手がいなくても行を残すLv23 で覚える

FROM a LEFT JOIN b ON …
FROM a RIGHT JOIN b ON …
FROM a FULL OUTER JOIN b ON …

LEFT JOIN は左の表の行をすべて残し、相手がいない列は NULL。WHERE b.id IS NULL で「相手のいない行」を探せます。

SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

CROSS JOINすべての組み合わせLv24 で覚える

FROM a CROSS JOIN b

a の行数 × b の行数の組み合わせを作ります。同じ表を 2 回書けば自己結合（社員と上司）もできます。

SELECT s.name, d.day FROM shops s CROSS JOIN days d;

UNION / INTERSECT / EXCEPT結果を重ねるLv25 で覚える

SELECT … UNION [ALL] SELECT …
SELECT … INTERSECT SELECT …
SELECT … EXCEPT SELECT …（Oracle は MINUS）

列の数と型をそろえます。UNION は重複を除き、UNION ALL は残します。

SELECT name FROM staff UNION SELECT name FROM members;

副問い合わせ問い合わせの中に問い合わせLv27 で覚える

WHERE 列 > (SELECT …)
WHERE 列 IN (SELECT …)
FROM (SELECT …) 別名

かっこの中の SELECT の結果を使います。1 つの値・値の並び・表として使えます。

SELECT * FROM items WHERE price > (SELECT AVG(price) FROM items);

EXISTS対応する行があるかLv28 で覚える

WHERE EXISTS (SELECT 1 FROM … WHERE 外側との条件)

外側の行ごとに副問い合わせを調べます（相関副問い合わせ）。NOT EXISTS は「ない」もの。

SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

WITH（共通テーブル式）問い合わせに名前をつけるLv29 で覚える

WITH 名前 AS (SELECT …) SELECT … FROM 名前;
WITH RECURSIVE …（再帰）

長い問い合わせを段階に分けて書けます。複数書くときは , で区切ります。

WITH big AS (SELECT * FROM orders WHERE total >= 10000) SELECT COUNT(*) FROM big;

ウィンドウ関数行を残したまま順位・累計Lv30 で覚える

関数() OVER (PARTITION BY 列 ORDER BY 列)
ROW_NUMBER() RANK() DENSE_RANK()
SUM(列) OVER (…)  LAG(列) LEAD(列)

GROUP BY と違い行はまとまりません。PARTITION BY でグループ、ORDER BY で順番を決めます。

SELECT name, RANK() OVER (ORDER BY score DESC) FROM results;

INSERT行を加えるLv33 で覚える

INSERT INTO 表 (列, …) VALUES (値, …), (値, …);
INSERT INTO 表 SELECT …;

列を書かないときはすべての列を表の順番で。書かなかった列は DEFAULT か NULL になります。

INSERT INTO items (name, price) VALUES ('ペン', 120);

UPDATE値を書き換えるLv34 で覚える

UPDATE 表 SET 列 = 値, … WHERE 条件;

WHERE を忘れるとすべての行が変わります。SET の右側には計算や副問い合わせも書けます。

UPDATE items SET price = price * 1.1 WHERE category = 'book';

DELETE行を消すLv34 で覚える

DELETE FROM 表 WHERE 条件;

WHERE を忘れるとすべての行が消えます。先に SELECT で対象を確かめるのが安全です。

DELETE FROM orders WHERE status = 'cancel';

CREATE TABLE表を作るLv35 で覚える

CREATE TABLE 表 (列 型 [制約], …);

型は INTEGER・VARCHAR(n)・NUMERIC(p,s)・DATE・BOOLEAN など。CREATE TABLE … AS SELECT で結果から作ることもできます。

CREATE TABLE items (id INTEGER PRIMARY KEY, name VARCHAR(50) NOT NULL);

制約入れてよい値を決めるLv35 で覚える

PRIMARY KEY  NOT NULL  UNIQUE  DEFAULT 値
CHECK (条件)  REFERENCES 表(列)  FOREIGN KEY (列) REFERENCES …

PRIMARY KEY は重複も NULL も不可。外部キーは相手の表にある値だけを許します。違反するとエラーになります。

CREATE TABLE orders (id INTEGER PRIMARY KEY, item_id INTEGER REFERENCES items(id), qty INTEGER CHECK (qty > 0));

ALTER TABLE表の形を変えるLv36 で覚える

ALTER TABLE 表 ADD COLUMN 列 型;
ALTER TABLE 表 DROP COLUMN 列;
ALTER TABLE 表 RENAME COLUMN 古 TO 新;
ALTER TABLE 表 ADD CONSTRAINT 名前 …;

列の追加・削除・名前の変更・制約の追加ができます。

ALTER TABLE items ADD COLUMN stock INTEGER DEFAULT 0;

DROP表などを消すLv36 で覚える

DROP TABLE [IF EXISTS] 表;
DROP VIEW / DROP INDEX …

表そのものを消します（データも消える）。

DROP TABLE IF EXISTS tmp_items;

TRUNCATE表を空にするLv36 で覚える

TRUNCATE TABLE 表;

全行を一度に消します（表は残る）。

TRUNCATE TABLE logs;

トランザクションまとめて確定・取り消しLv37 で覚える

BEGIN; … COMMIT;
BEGIN; … ROLLBACK;
SAVEPOINT 名前; ROLLBACK TO 名前;

BEGIN から COMMIT までの変更はまとめて確定し、ROLLBACK で全部取り消します。Oracle は BEGIN なしで、COMMIT するまで確定しません。

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

CREATE INDEX検索を速くするLv38 で覚える

CREATE [UNIQUE] INDEX 名前 ON 表 (列, …);
DROP INDEX 名前;

よく WHERE や JOIN に使う列に作ると検索が速くなります。UNIQUE INDEX は重複を許しません。

CREATE INDEX idx_orders_customer ON orders (customer_id);

CREATE VIEW問い合わせに名前をつけて表のように使うLv38 で覚える

CREATE [OR REPLACE] VIEW 名前 AS SELECT …;

ビューはデータを持たず、使うたびに中の SELECT が実行されます。

CREATE VIEW book_items AS SELECT * FROM items WHERE category = 'book';