練習データを確認する
SQL入門で作ったproductsを使います。詳細ページだけを読む場合は、先に基礎ページのCREATE TABLEとINSERTを実行してください。
全商品を確認SQL
SELECT id, name, price, category, stock
FROM products
ORDER BY id;実行結果OUTPUT
id name price category stock
-- ---------- ----- ---------- -----
1 SQL入門書 2800 book 5
2 ノート 300 stationery 12
3 ボールペン 150 stationery 0
4 マグカップ 900 3SQLiteの通常表示ではNULLが空欄に見えることがあります。空文字が保存されているとは限りません。
条件を小さく組み立てる
検索条件は、対象を言葉で整理してから書きます。次は「分類が文房具」「または分類が未設定」のうち、「在庫がある」商品です。
ANDとORを組み合わせるSQL
SELECT name, category, stock
FROM products
WHERE (category = 'stationery' OR category IS NULL)
AND stock > 0
ORDER BY id;実行結果OUTPUT
name category stock
---------- ---------- -----
ノート stationery 12
マグカップ 3部分一致を使う
LIKEでは、%が0文字以上の任意の文字、_が任意の1文字を表します。
名前に「入門」を含む商品SQL
SELECT name FROM products WHERE name LIKE '%入門%';実行結果OUTPUT
name
---------
SQL入門書利用者の入力を連結しない アプリで検索語を受け取る場合もSQL文字列へ直接つなげず、利用する言語のプレースホルダー機能へ値を渡します。
CASE式で値を分類する
CASE式は条件に応じた値を返します。上から順に調べ、最初に一致したTHENの値を使います。
在庫状態を表示SQL
SELECT name, stock,
CASE
WHEN stock = 0 THEN '在庫切れ'
WHEN stock <= 3 THEN '残りわずか'
ELSE '在庫あり'
END AS stock_status
FROM products
ORDER BY id;実行結果OUTPUT
name stock stock_status
---------- ----- ------------
SQL入門書 5 在庫あり
ノート 12 在庫あり
ボールペン 0 在庫切れ
マグカップ 3 残りわずかstock = 0を先に書かないと、stock <= 3にも一致して「残りわずか」になります。条件が重なるときは順番を確認します。
集計関数とNULL
COUNT(*)は行数を数えますが、COUNT(category)はcategoryがNULLの行を数えません。
数え方の違いSQL
SELECT COUNT(*) AS all_rows,
COUNT(category) AS category_rows,
SUM(stock) AS total_stock,
ROUND(AVG(price), 1) AS average_price
FROM products;実行結果OUTPUT
all_rows category_rows total_stock average_price
-------- ------------- ----------- -------------
4 3 20 1037.50件のとき
COUNTは0を返しますが、SUMやAVGはNULLを返すことがあります。必要ならCOALESCE(SUM(stock), 0)とします。GROUP BYでまとまりを作る
GROUP BY categoryは、同じ分類の行を一つのグループとして集計します。集計対象でない列を無関係にSELECTへ加えると、意味が曖昧になります。
分類別に集計SQL
SELECT COALESCE(category, '未分類') AS category_name,
COUNT(*) AS count,
SUM(stock) AS total_stock
FROM products
GROUP BY category
ORDER BY count DESC, category_name;実行結果OUTPUT
category_name count total_stock
------------- ----- -----------
stationery 2 12
book 1 5
未分類 1 3WHEREとHAVINGを使い分ける
WHEREはグループを作る前の行を絞り、HAVINGは集計したあとのグループを絞ります。次は在庫がある商品のみを集計し、その合計在庫が4個以上の分類だけを返します。
集計前と集計後の条件SQL
SELECT category, SUM(stock) AS total_stock
FROM products
WHERE stock > 0
AND category IS NOT NULL
GROUP BY category
HAVING SUM(stock) >= 4
ORDER BY category;実行結果OUTPUT
category total_stock
---------- -----------
book 5
stationery 12ミニ課題：価格帯別の商品数
CASEで500円未満を「低価格」、500円以上1000円未満を「中価格」、1000円以上を「高価格」に分け、価格帯ごとの商品数を表示してください。
解答例SQL
SELECT CASE
WHEN price < 500 THEN '低価格'
WHEN price < 1000 THEN '中価格'
ELSE '高価格'
END AS price_range,
COUNT(*) AS product_count
FROM products
GROUP BY price_range
ORDER BY MIN(price);実行結果OUTPUT
price_range product_count
----------- -------------
低価格 2
中価格 1
高価格 1