SQLの書き方
よく使う文法・タグ・操作を、短い例で。43項目から探せます。
43 / 43項目
SQLの例で使う表について
PostgreSQLを基準にしています。booksはid・title・price・author_id、authorsはid・nameを持つ表の例です。例は個別に確認するためのものです。追加・更新・削除は学習用のデータで試してください。
テーブルを作る
情報を保存する、列と行の入れ物を用意します。
CREATE TABLE books (
title TEXT,
price INTEGER
);データを追加
テーブルに、実際の情報を入れます。
INSERT INTO books (title, price)
VALUES ('入門書', 1000);データを取得
必要な列を選んで、一覧を取り出します。
SELECT title FROM books;条件で絞り込む
条件に合う行だけを選びます。
SELECT title FROM books
WHERE price <= 1000;並べ替えと件数
価格順に並べたり、先頭の数件だけ取得したりします。
SELECT title FROM books
ORDER BY price ASC
LIMIT 3;集計する
件数や合計を、データベースで計算します。
SELECT COUNT(*) FROM books;データを更新
条件に合う行の値を書き換えます。
UPDATE books
SET price = 900
WHERE title = '入門書';コメント
SQLにメモを残す。
-- 本の一覧
SELECT * FROM books;全列・別名
全列を選び、表示名を付ける。
SELECT * FROM books;
SELECT title AS book_name FROM books;重複を除く
同じ値を一つにまとめる。
SELECT DISTINCT title FROM books;比較演算
等しい、異なる、より大きいを調べる。
SELECT * FROM books WHERE price = 1000;
SELECT * FROM books WHERE price <> 1000;
SELECT * FROM books WHERE price > 1000;AND・OR・NOT
条件を組み合わせる。
SELECT * FROM books
WHERE price >= 500 AND price <= 1500;IN
候補のいずれかと一致する行。
SELECT * FROM books
WHERE price IN (500, 1000, 1500);BETWEEN
両端を含む範囲で絞り込む。
SELECT * FROM books
WHERE price BETWEEN 500 AND 1500;LIKE
文字列のパターンで探す。
SELECT * FROM books WHERE title LIKE '%入門%';%は0文字以上、_は任意の1文字です。
IS NULL・IS NOT NULL
値がない、またはある行を探す。
SELECT * FROM books WHERE price IS NULL;
SELECT * FROM books WHERE price IS NOT NULL;NULLの比較に= NULLは使いません。
複数条件で並べ替え
同じ価格なら題名順にする。
SELECT * FROM books
ORDER BY price ASC, title ASC;OFFSET
指定件数を飛ばして取得する。
SELECT * FROM books
ORDER BY title, price
LIMIT 10 OFFSET 10;安定したページ分けには主キーなど一意になる並べ替えも必要です。
CASE
条件に応じて表示値を変える。
SELECT title,
CASE WHEN price <= 1000 THEN '安い' ELSE '通常' END AS label
FROM books;COALESCE
NULLの代わりの値を使う。
SELECT title, COALESCE(price, 0) AS price FROM books;SUM・AVG・MIN・MAX
合計、平均、最小、最大。
SELECT SUM(price), AVG(price), MIN(price), MAX(price)
FROM books;COUNTの違い
行数か、NULL以外の値の数を数える。
SELECT COUNT(*), COUNT(price) FROM books;GROUP BY
同じ値ごとに集計する。
SELECT price, COUNT(*)
FROM books
GROUP BY price;HAVING
集計した結果を条件で絞る。
SELECT price, COUNT(*)
FROM books
GROUP BY price
HAVING COUNT(*) >= 2;INNER JOIN
一致する行同士を結合する。
SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id;booksにauthor_id、authorsにidとnameがある例です。
LEFT JOIN
左の表の行をすべて残して結合する。
SELECT b.title, a.name
FROM books AS b
LEFT JOIN authors AS a ON b.author_id = a.id;一致する著者がない行ではa.nameはNULLです。
サブクエリ
別の問い合わせの結果を条件に使う。
SELECT * FROM books
WHERE price > (SELECT AVG(price) FROM books);EXISTS
関連する行が存在するか調べる。
SELECT * FROM authors AS a
WHERE EXISTS (
SELECT 1 FROM books AS b WHERE b.author_id = a.id
);UNION・UNION ALL
複数の取得結果を縦に合わせる。
SELECT title FROM books WHERE price <= 1000
UNION
SELECT title FROM books WHERE title LIKE '%入門%';UNIONは重複を除き、UNION ALLは残します。列数と型を揃えます。
WITH
途中の結果に名前を付ける。
WITH cheap AS (
SELECT * FROM books WHERE price <= 1000
)
SELECT title FROM cheap;ウィンドウ関数
行を残したまま順位などを計算する。
SELECT title, price,
RANK() OVER (ORDER BY price DESC) AS price_rank
FROM books;文字列関数
文字数を数え、大文字に変える。
SELECT title, LENGTH(title), UPPER(title) FROM books;CAST
データの型を変える。
SELECT CAST('100' AS INTEGER);日付
現在の日付を取得する。
SELECT CURRENT_DATE;複数行の追加
一度に複数の行を入れる。
INSERT INTO books (title, price)
VALUES ('入門書', 1000), ('練習帳', 500);行を削除する
条件に合う行だけ削除する。
DELETE FROM books WHERE title = '練習帳';WHEREを省くと全行が対象になります。
制約
重複、空欄、不正な値を防ぐ。
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
age INTEGER CHECK (age >= 0)
);外部キー
別の表に存在する値だけを許す。
CREATE TABLE reviews (
book_id INTEGER REFERENCES books(id),
comment TEXT
);books側に主キーまたは一意制約のあるid列が必要です。
列を追加する
表に新しい項目を足す。
ALTER TABLE books ADD COLUMN memo TEXT;表を削除する
表そのものを削除する。
DROP TABLE practice_books;表とデータが削除されます。学習用の表で試してください。
トランザクション
複数の変更をまとめて確定する。
BEGIN;
UPDATE books SET price = 900 WHERE price = 1000;
COMMIT;確定せず取り消す場合はCOMMITの代わりにROLLBACKを使います。
インデックス
検索に使う索引を作る。
CREATE INDEX books_title_index ON books(title);ビュー
問い合わせに名前を付けて再利用する。
CREATE VIEW cheap_books AS
SELECT title, price FROM books WHERE price <= 1000;
SELECT * FROM cheap_books;