SQL入門 STEP14｜SQLの基礎を使ってデータを操作しよう

karin@taishi@felmia

ここまで覚えたSQLを使ってみよう

STEP1から始めたSQL入門も、今回が最後のSTEPです。

最初は、

SELECT 1;

という、とても短いSQLから始めました。

そこからテーブルを作り、商品を登録し、条件を指定して検索したり、データを更新したりしてきました。

今では、2つのテーブルをJOINしてデータを取得することもできます。

ここまで来ると、SQLの書き方を一つずつ覚えるだけではなく、

「このデータが欲しいなら、どのSQLを使えばいいだろう？」

と考えられるようになることが大切です。

今回は、これまで使ってきた商品管理のデータを使って、いくつかの課題に挑戦します。

ルミィ

いよいよSQL入門の総合演習！
今まで覚えたSQLを使って、商品データを操作してみよう♪

今回使うテーブルを確認しよう

まず、現在のデータを確認しておきます。

productsテーブルには商品情報が入っています。

SELECT *
FROM products
ORDER BY product_id ASC;

STEP13まで記事どおりに進めていれば、次のような状態です。

product_id | product_name    | price  | category_id
-----------+-----------------+--------+------------
1          | ノートPC         | 120000 | 1
2          | マウス           | 3000   | 2
3          | キーボード        | 8000   | 2
4          | モニター          | 30000  | 2
5          | USBメモリ        | 2000   | 3
6          | Webカメラ        | 7000   | 2
7          | ヘッドセット      | 7000   | 2
8          | HDMIケーブル 2m   | 1800   | 3
9          | タブレット        | 50000  | NULL

カテゴリーはcategoriesテーブルで管理しています。

SELECT *
FROM categories
ORDER BY category_id ASC;

category_id | category_name
------------+---------------
1           | PC
2           | 周辺機器
3           | アクセサリ

この2つのテーブルを使って進めます。

まずは商品の一覧を作ってみよう

最初の課題です。

商品管理画面に、

商品ID
商品名
価格
カテゴリー名

を表示するとします。

カテゴリーが決まっていない商品も、一覧から消えてはいけません。

どんなSQLを書けばよいでしょうか。

少し考えてから、続きを見てみてください。

考えるポイント

商品とカテゴリーは別のテーブルにあります。

さらに、カテゴリー未設定の商品も表示する必要があります。

STEP12とSTEP13のどちらのJOINを使えばよいか考えてみましょう。

今回はLEFT JOINを使います。

SELECT
    p.product_id,
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
ORDER BY p.product_id ASC;

カテゴリーが設定されている商品にはカテゴリー名が表示されます。

商品ID9のタブレットも一覧に残り、カテゴリー名はNULLになります。

ここでINNER JOINを使うと、カテゴリーが設定されていないタブレットは結果から外れます。

「何を表示したいのかによってJOINを選ぶ」

というSTEP13の内容が、そのまま使えましたね。

価格を指定して商品を検索してみよう

次は検索です。

次の条件の商品を取得してみます。

5000円以上、30000円以下の商品を価格の安い順に表示する

ここでは、

条件を指定する
並び替える

という2つの操作が必要です。

どのSQLを使うか思い出してみてください。

SELECT
    product_id,
    product_name,
    price
FROM products
WHERE price >= 5000
  AND price <= 30000
ORDER BY price ASC;

条件を指定しているのがWHERE。

2つの条件を両方満たすためにAND。

最後にORDER BYで価格の安い順に並べています。

SQLが長くなっても、一つひとつを見るとすでに使ったことのあるものばかりです。

ルミィ

WHEREとORDER BYを組み合わせれば、検索結果を見やすい順番にできるね♪

新しい商品を登録してみよう

ここからは、少し自分で考える部分を増やします。

次の商品を登録してください。

商品ID：10
商品名：プリンター
価格：18000
カテゴリーID：2

使うSQLは何だったでしょうか。

データを新しく登録するときに使うのはINSERTでした。

まず自分で書いてみてから、答えを確認してみましょう。

解答例

INSERT INTO products (
    product_id,
    product_name,
    price,
    category_id
)
VALUES (
    10,
    'プリンター',
    18000,
    2
);

実行できたら確認します。

SELECT *
FROM products
WHERE product_id = 10;

プリンターが表示されれば登録できています。

登録した商品を更新してみよう

続いて、プリンターの価格が変更されたとします。

プリンター
18000円 → 20000円

登録済みのデータを変更するSQLを思い出してみましょう。

ここではすぐにUPDATEを書かず、STEP7で覚えた手順を使います。

まずは対象を確認します。

SELECT *
FROM products
WHERE product_id = 10;

商品ID10のプリンターだけが表示されていることを確認します。

問題なければ更新します。

解答例

UPDATE products
SET price = 20000
WHERE product_id = 10;

そして、もう一度確認します。

SELECT *
FROM products
WHERE product_id = 10;

価格が20000になっていれば成功です。

UPDATEやDELETEでは、確認する習慣を忘れずに

SQLの書き方を覚えていても、WHEREを間違えれば別のデータを変更してしまいます。

SELECTで対象を確認
↓
UPDATE・DELETEを実行
↓
SELECTでもう一度確認

総合演習でも、この流れは変わりません。

カテゴリーごとの商品数を調べてみよう

今度は集計です。

次の情報を取得してください。

カテゴリーごとの商品数

ここでは、

「件数を数えるには何を使った？」

「カテゴリーごとに分けるには何を使った？」

と考えてみましょう。

STEP9とSTEP10の内容です。

解答例

SELECT
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
ORDER BY category_id ASC;

COUNT(*)で件数を数え、GROUP BY category_idでカテゴリーごとに分けています。

ただ、この結果には少し気になるところがあります。

表示されるのはカテゴリー名ではなく、カテゴリーIDです。

せっかくcategoriesテーブルがあるので、もう少し分かりやすくしてみましょう。

カテゴリー名と一緒に集計してみよう

今度は、

カテゴリー名ごとの商品数

を表示してみます。

ここでは、

JOIN
COUNT
GROUP BY

を組み合わせます。

少し難しくなりました。

一気にSQLを書こうとせず、必要なことを整理してみましょう。

商品はproductsにあります。

カテゴリー名はcategoriesにあります。

商品数はCOUNTで数えます。

カテゴリーごとにまとめるのでGROUP BYが必要です。

今回はカテゴリーが設定されていない商品も確認したいので、LEFT JOINを使ってみます。

解答例

SELECT
    c.category_name,
    COUNT(*) AS product_count
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
GROUP BY c.category_name
ORDER BY c.category_name ASC;

これでカテゴリー名ごとの商品数を取得できます。

カテゴリー未設定のタブレットは、category_nameがNULLのグループとして集計されます。

ここまで来るとSQLも長くなりましたね。

ですが、

LEFT JOIN → 商品とカテゴリーをつなぐ
COUNT     → 商品数を数える
GROUP BY  → カテゴリーごとに分ける
ORDER BY  → 結果を並び替える

と分ければ、それぞれはすでに学んだ内容です。

カテゴリーごとの平均価格を調べてみよう

もう一つ集計してみます。

今度は、

カテゴリー名ごとの平均価格

を求めてください。

先ほどの商品数を求めるSQLを少し変えれば作れます。

どこを変えればよいでしょうか。

商品数は、

COUNT(*)

でした。

平均価格なら、STEP9で使ったAVGですね。

解答例

SELECT
    c.category_name,
    AVG(p.price) AS average_price
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
GROUP BY c.category_name
ORDER BY c.category_name ASC;

SQLを全部書き直して覚える必要はありません。

先ほどのSQLから、

COUNT(*) AS product_count
        ↓
AVG(p.price) AS average_price

に変えただけです。

こうやって、すでに動いているSQLを少しずつ変えながら目的の結果に近づける方法もあります。

カテゴリー未設定の商品を探してみよう

商品を管理していると、

「カテゴリーを設定し忘れている商品はないか」

を確認したくなることもあります。

STEP13ではIS NULLを使いました。

では、カテゴリー未設定の商品だけを取得してみてください。

解答例

SELECT
    p.product_id,
    p.product_name,
    p.price
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
WHERE c.category_id IS NULL;

現在は、タブレットが表示されます。

product_id | product_name | price
-----------+--------------+------
9          | タブレット     | 50000

では、このタブレットをPCカテゴリーに設定してみましょう。

タブレットにカテゴリーを設定しよう

categoriesを確認すると、

category_id | category_name
------------+--------------
1           | PC

となっています。

タブレットの商品IDは9です。

まず対象を確認します。

SELECT *
FROM products
WHERE product_id = 9;

問題なければcategory_idを更新します。

UPDATE products
SET category_id = 1
WHERE product_id = 9;

もう一度確認しましょう。

SELECT *
FROM products
WHERE product_id = 9;

category_idが1になっていれば更新できています。

これでタブレットもPCカテゴリーに属する商品になりました。

JOINして確認してみよう

数字だけを見るのではなく、カテゴリー名まで確認してみます。

SELECT
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
INNER JOIN categories AS c
    ON p.category_id = c.category_id
WHERE p.product_id = 9;

結果は、

product_name | price | category_name
-------------+-------+--------------
タブレット     | 50000 | PC

となります。

UPDATEしたのはcategory_idだけですが、JOINすることで「PC」というカテゴリー名まで取得できます。

主キーと外部キーを使ってテーブルを分けた意味も、少し見えてきたのではないでしょうか。

最後の課題

最後は、ヒントを少なくします。

次の条件を満たす商品一覧を取得してください。

最後の課題

次の条件で商品を検索します。

商品名を表示する
価格を表示する
カテゴリー名を表示する
5000円以上の商品だけを対象にする
価格が高い順に並べる

カテゴリーが設定されている商品のみを対象とします。

まずは、自分でSQLを書いてみてください。

使うものはすべて、ここまでに登場しています。

必要なのは、

SELECT

INNER JOIN

ON

WHERE

ORDER BY

です。

最後の課題の解答例

書けたら、こちらと比べてみましょう。

SELECT
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
INNER JOIN categories AS c
    ON p.category_id = c.category_id
WHERE p.price >= 5000
ORDER BY p.price DESC;

まったく同じ書き方でなくても、条件どおりの結果を取得できていれば大丈夫です。

SQLでは、目的の結果にたどり着く書き方が一つとは限りません。

大切なのは、

「欲しい結果は何か」

を整理して、それに必要な処理を一つずつ組み立てることです。

ルミィ

自分でSQLを組み合わせて書けたら、最初のころからかなり進んでるよ♪

SQLが書けないときは日本語で整理してみよう

ここまで進むと、SQLが長くなることもあります。

そんなとき、最初から完成形を書こうとすると混乱しやすくなります。

例えば最後の課題なら、まず日本語で整理します。

何が欲しい？
→ 商品名、価格、カテゴリー名

どのテーブルが必要？
→ products、categories

どうやってつなぐ？
→ category_id

条件は？
→ 5000円以上

並び順は？
→ 価格の高い順

ここまで整理できれば、使うSQLも見えてきます。

プログラミングでも同じですが、分からないときにいきなりコードを書き始める必要はありません。

「何をしたいのか」を小さく分ける。

SQLでも、この考え方はかなり役に立ちます。

SQL入門で学んだことを振り返ろう

STEP1から、かなり多くのSQLを使ってきました。

ここで全部を暗記できている必要はありません。

何ができるのかを知っていれば、必要になったときに調べながら書けます。

内容	主に使ったSQL
テーブルを作る	`CREATE TABLE`
データを取得する	`SELECT`
条件を指定する	`WHERE`
並び替える	`ORDER BY`
データを追加する	`INSERT`
データを更新する	`UPDATE`
データを削除する	`DELETE`
件数を数える	`COUNT`
合計・平均を求める	`SUM`・`AVG`
最大・最小を求める	`MAX`・`MIN`
グループごとに集計する	`GROUP BY`
テーブルを結合する	`INNER JOIN`・`LEFT JOIN`

さらに、

PRIMARY KEY

FOREIGN KEY

を使ったテーブル同士の関係についても学びました。

これだけ使えれば、SQLの基礎としてかなり多くのデータ操作ができるようになっています。

覚えていないSQLがあっても大丈夫

ここまで読んで、

「UPDATEの書き方をもう忘れている」

「GROUP BYはまだ何も見ずには書けない」

というものがあっても問題ありません。

一度学んだからといって、すぐに全部覚えられるものではありません。

特にSQLは、実際に何度も書いているうちに、

「この場合はWHEREだったな」

「集計だからGROUP BYが必要そうだな」

と少しずつ判断できるようになります。

最初のうちは、前のSTEPを見ながら書いても大丈夫です。

ただし、SQLをコピーして終わりにするのではなく、

「このWHEREは何のためにある？」

「このONでは何と何をつないでいる？」

と、自分で説明できるか確認してみてください。

説明できれば、単に書き方を覚えるよりもずっと理解が残りやすくなります。

よくあるつまずき

SQLが長くなると分からなくなる

長いSQLを一度に読もうとしなくて大丈夫です。

例えば、

SELECT     → 何を取得する？
FROM       → どのテーブルを使う？
JOIN / ON  → 何と何をつなぐ？
WHERE      → どんな条件？
GROUP BY   → 何ごとにまとめる？
ORDER BY   → どんな順番？

と分けて見てみましょう。

UPDATEやDELETEをいきなり実行する

UPDATEとDELETEは、データそのものを変更します。

まずSELECTで対象を確認する習慣を付けておくと、意図しないデータを変更するミスを減らせます。

エラーが出ると全部間違っていると思ってしまう

SQLが長くなっても、間違っているのは一部分だけということがあります。

カラム名のスペル。

,の付け忘れ。

WHEREとGROUP BYの順番。

JOINするカラムの指定。

エラーメッセージを見ながら、一つずつ確認していきましょう。

STEP14のまとめ

STEP14のポイント

SQLは目的に合わせて複数の構文を組み合わせて使う
INSERT・SELECT・UPDATE・DELETEで基本的なデータ操作ができる
WHEREやORDER BYを組み合わせて必要なデータを探せる
集計関数とGROUP BYでデータをまとめて調べられる
JOINを使うと複数のテーブルからデータを取得できる
UPDATEやDELETEの前にはSELECTで対象を確認する
SQLが長くなったら、何をしたいのか一つずつ分けて考える

STEP1では、PostgreSQLをインストールしてSELECT 1;を実行するところから始めました。

そこから自分でテーブルを作り、商品を登録し、検索し、更新し、削除し、集計し、最後には2つのテーブルをJOINしてデータを取得しました。

SQLを学び始めたばかりの段階で大切なのは、難しい構文をたくさん暗記することではありません。

例えば、

「商品を探したいからSELECT」

「条件があるからWHERE」

「2つのテーブルから情報が欲しいからJOIN」

というように、やりたいことから必要なSQLを考えられることです。

書き方を忘れたら確認すれば大丈夫です。

まずは、今回までに作ったSQLを少し変えてみたり、条件を自分で変えて結果を確認したりしてみてください。

その繰り返しが、SQLに慣れる一番の近道です。

ルミィ

SQL入門、全STEPクリア！
最初のSELECTからここまでおつかれさま♪
今度は自分で条件を変えながら、いろいろ試してみてね！

SQL入門を終えたら

ここまでで、Felmia StudioのSQL入門 STEP1～14は完了です。

この先、SQLにはまだ、

サブクエリ
CASE
EXISTS
UNION
WITH（CTE）
ウィンドウ関数

など、さまざまな機能があります。

ただ、ここで急いで全部覚える必要はありません。

まずは今回までのSQLを使って、必要なデータを自分で取得・変更できることを優先しましょう。

基礎が身についてから先へ進んだほうが、応用的なSQLも理解しやすくなります。

SQL入門は、これでひと区切りです。

SQL入門 STEP1～14、おつかれさまでした！

→ SQL入門｜初心者が基礎から学べるSQL学習ガイド