クリエーション・ビュー こんにちは、児山です。
本日はデータ分析に興味があってSQLの勉強を始めた方向けの記事です。
SQLを学び始めたばかりの時、自分で書いたSQLを実行するとどんな結果が返ってくるのか、予想通りのデータが得られるか、サクッと検証したいことありますよね。
そんな時にブラウザ上で使えるプレイグラウンドサービスを使うと手軽に検証ができて便利です。
今回はデータベースプレイグラウンドサービスを使って手を動かしつつ、基本的なSQL構文を学べるように記事をまとめました。
皆様がSQLの理解を深める際のお役に立てば幸いです。
前提 ~DB-fiddleについて~
今回はDB-fiddleというプレイグラウンドサービスを使います。
簡単に画面の見方を説明します。
今回主に使うのはこの3つのエリアです。
-
Schema SQL
- テーブルの論理的な定義を設定するSQLを書くエリアです。テーブルの名前や各列の名前と型を定義したり、データの追加・更新・削除といった操作はここに記載します。
-
Query SQL
- データベースからデータを取得するための問い合わせをするSQL分を書くエリアです。
-
result
- SQLの実行結果が表示されるエリアです。
やることの流れ
- 事前準備
- テーブル作成
- 基本的なSQL構文を使ったデータ取得
事前準備
一般的にはテーブルを作成する前にデータベースの環境構築が必要になりますが、今回はDB-Fiddleを使うことでその手間をスキップします。
利用するDBの選択だけしておきましょう。今回はMySQL v8.0を選択します。
- DBは画面左上のところから選択できます。
テーブル作成
まずはこのような2つのテーブルを用意しましょう。
- purchase：購買履歴テーブル
- date:日付
- product:購入した本のジャンル
- price:金額
- cs_id:購入した顧客id
| date | product | price | cs_id |
|---|---|---|---|
| 2024/4/1 | 雑誌 | 100 | c0001 |
| 2024/4/1 | 文庫本 | 200 | c0001 |
| 2024/4/2 | 雑誌 | 100 | c0002 |
| 2024/4/3 | マンガ | 300 | c0002 |
| 2024/4/3 | 文庫本 | 200 | c0003 |
- customer：顧客テーブル
- id：顧客id
- name：顧客の名前
- age：年齢
- sex：性別
| id | name | age | sex |
|---|---|---|---|
| c0001 | Bob | 26 | male |
| c0002 | Jany | 32 | female |
| c0003 | Anna | 24 | female |
CREATE文
- テーブルを作成するにはCREATEを使います。
- CREATE TABLEの後に作成するテーブルの名称を指定します。
- 括弧の中にはテーブルの列名・データ型・制約を定義しています。
以下のSQLをエディタに貼り付けて実行することで、2つのテーブルを作ってみましょう
-- purchaseテーブルの作成
CREATE TABLE purchase (
date DATE NOT NULL,
product VARCHAR(255) NOT NULL,
price INT NOT NULL,
cs_id VARCHAR(10) NOT NULL
);
-- customerテーブルの作成
CREATE TABLE customer (
id VARCHAR(10) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
sex VARCHAR(10) NOT NULL
);DB-fiddleを開いたら、Schema SQLのエリアに以下のように貼り付けて、実行（画面上部のRunボタンを押す、もしくはctrl+Enter / command+Enterキーを押下）すればOKです
もしも実行時にincorrect string valueのエラーが出る場合はDBのバージョンを最新のものへ変更してみてください。（MySQLの場合、2024/05/10現在だとv8.0が最新です。）
さて、この時点ではまだテーブルの中にデータが格納されていません。
次の構文を使ってテーブルにデータを格納していきましょう。
INSERT文
- どのテーブルにどんなデータを格納するかを指定するにはINSERTを使います
- INSERT INTO の後に登録先のテーブル名を指定します。
- テーブル名の後の括弧にはテーブルの列名を並べます。
- VALUESの後には登録したいデータをカンマ区切りで並べます。
- この時、列名と値は順番を合わせる必要があります。
先程作成した3つのテーブルに値を格納しましょう。
今度は以下のSQLをコピーします。
-- purchaseテーブルにデータを挿入
INSERT INTO purchase (date, product, price, cs_id) VALUES
('2024-04-01', '雑誌', 100, 'c0001'),
('2024-04-01', '文庫本', 200, 'c0001'),
('2024-04-02', '雑誌', 100, 'c0002'),
('2024-04-03', 'マンガ', 300, 'c0002'),
('2024-04-03', '文庫本', 200, 'c0003');
-- customerテーブルにデータを挿入
INSERT INTO customer (id, name, age, sex)
VALUES
('c0001', 'Bob', 26, 'male'),
('c0002', 'Jany', 32, 'female'),
('c0003', 'Anna', 24, 'female');先程のCREATE文の末尾に追加する形で、以下のように貼り付けて実行してみてください。
実行完了したら、値が格納されたことを確認するために
Query SQLの欄に以下のSQLを貼り実行してみましょう。
SELECT * FROM purchase;
SELECT * FROM customer;値が追加されていれば、このように画面下部のresultに結果が表示されます。
これでテーブルの準備ができました。
基本的なSQL構文を使ったデータ取得
テーブルが準備できたら、今度は溜め込んだデータの中から欲しいデータを取ってみます。
上手くデータを取ってくるコツは、「今存在するテーブルのデータを使ってどんな表を作りたいのか」をイメージすることです。
SELECT句
- SQLでデータを取得するにはSELECTを使います。
- SELECTの後に取得したい列名を指定します。
- FROMの後に取得元のテーブルを指定します。
例えば、顧客テーブルから顧客名簿を作りたいとします。
SELECT文を使って取得してみましょう。
顧客テーブルはこんな構造でしたね。
| id | name | age | sex |
|---|---|---|---|
| c0001 | Bob | 26 | male |
| c0002 | Jany | 32 | female |
| c0003 | Anna | 24 | female |
顧客テーブルのname列を取得するクエリを書けば、名簿を作ることができそうです。
というわけで、以下のクエリをQuery SQLに貼り付けて実行し、結果を見比べてみましょう。
SELECT name FROM customer;もしもテーブル内にある全てのカラムを取得したい場合はアスタリスク(*)を指定します。
先程テーブルの中身を確認する時に使いましたね。
SELECT * FROM purchase;
SELECT * FROM customer;WHERE句
- WHERE句は、指定した条件に一致する行のみを抽出するために使います。
- 比較演算子（=、<、>、<=、>=、<>）や論理演算子(AND,OR,NOT)を使用して、取得したい条件を指定します。
顧客名簿を作るにあたってさらに条件を加えてみます。
今回は女性の顧客のみに絞り込んでみましょう。
顧客テーブルを見るとsexカラムがあるので、これがfemaleであるレコードに絞り込めばうまく名簿を作れそうですね。
| id | name | age | sex |
|---|---|---|---|
| c0001 | Bob | 26 | male |
| c0002 | Jany | 32 | female |
| c0003 | Anna | 24 | female |
実行した後にこんな感じの表が出来上がれば良さそうです。
| name |
|---|
| Jany |
| Anna |
では、WHERE句で条件指定してみましょう。Query SQLに以下のクエリを貼り付けて実行してみます。
SELECT name FROM customer WHERE sex = 'female';予想通りの表が表示されたでしょうか？
WHERE句では複数の条件をつけることもできます。
さらに30歳未満に絞るとどうなるでしょうか？
SELECT name FROM customer WHERE sex = 'female' AND age < 30 ;以下の2つのクエリの結果はどう変わるでしょうか?
SELECT name FROM customer WHERE sex = ('female' AND age < 30) OR age > 25;
SELECT name FROM customer WHERE sex = 'female' AND (age < 30 OR age > 25);いろいろ条件を変えて試してみてください。
GROUP BY句
GROUP BY句は、指定した列を基準にデータをグループ化し、それぞれのグループに対して集計処理を行うために使います。
GROUP BY句には、グループ化したい列名を指定します。
今度は購買履歴から本のジャンルごとの売上を取得してみましょう。
購買履歴テーブルのproductカラムの項目ごとにpriceカラムの合計を計算すれば売上が分かりそうですね。
| date | product | price | cs_id |
|---|---|---|---|
| 2024/4/1 | 雑誌 | 100 | c0001 |
| 2024/4/1 | 文庫本 | 200 | c0001 |
| 2024/4/2 | 雑誌 | 100 | c0002 |
| 2024/4/3 | マンガ | 300 | c0002 |
| 2024/4/3 | 文庫本 | 200 | c0003 |
実行後にできる表はこんな感じになると予想できます。
| product | price |
|---|---|
| 雑誌 | 200 |
| 文庫本 | 400 |
| マンガ | 200 |
GROUP BY句の後にproductを指定してみます。Query SQLに以下のクエリを貼り付けて実行してみましょう。
SELECT product, SUM(price) AS total_sales FROM purchase GROUP BY product;resultを見て予想通りの表が出力されているか確認してみてください。
本のジャンルではなく、日付ごとに売上を出したい場合はどうすれば良いでしょうか？
GROUP BY の後をproductでからdateに変えて実行してみましょう。
SELECT date, SUM(price) AS total_sales FROM purchase GROUP BY date;ちなみに、SUM関数はカラム内の数値を合計する関数、AS は別名をつけるための構文です。SUM関数のように、ある項目に対して合計したり、平均値を求めたり、あるいはデータ件数を取得したり等集計を行う関数を集計関数と呼びます。
- COUNT(カラム名)：データの個数を返す
- SUM(カラム名)：データの合計値を返す
- AVG(カラム名)：データの平均値を返す
- MAX(カラム名)：データの最大値を返す
- MIN(カラム名)：データの最小値を返す
SUMを別の集計関数に変えたときにどんな値が返ってくるか試してみましょう。
SELECT product, COUNT(price) AS count_sales FROM purchase GROUP BY product;
SELECT product, AVG(price) AS avg_sales FROM purchase GROUP BY product;
SELECT product, MAX(price) AS max_sales FROM purchase GROUP BY product;
SELECT product, MIN(price) AS min_sales FROM purchase GROUP BY product;ORDER BY句
- ORDER BY句は、取得したデータを並び替えるために使用されます。
- ORDER BY句は、SELECT文の最後に指定することができます。
- 昇順に並び替える場合はASCを、降順に並び替える場合はDESCを使用します。（ASCは省略可能です）
次は顧客管理テーブルから年齢が若い順にデータを取得してみましょう。
| id | name | age | sex |
|---|---|---|---|
| c0001 | Bob | 26 | male |
| c0002 | Jany | 32 | female |
| c0003 | Anna | 24 | female |
年齢が若い順ということはageカラムを昇順に並び替えると良さそうです。Query SQLに以下のクエリを貼り付けて実行してみましょう。
SELECT * FROM employee ORDER BY age ASC;ちなみにORDER BY句はデフォルトで昇順に並び替えるのでASCは省略可能です。
SELECT * FROM employee ORDER BY age;JOIN句
- JOIN句は複数のテーブルを結合するために使用されます。
- 結合するテーブルはFROMの後に指定します。
- JOINの種類にはINNER JOIN、LEFT JOIN、RIGHT JOINなどがあります。
最後に複数のテーブルからデータを取得してみます。
購入履歴テーブルと顧客テーブルから購入日、購入した本のジャンル、購入者名が記載されたリストを作成してみましょう。
まずは購入日、購入した本のジャンル、購入者名がどこのテーブルに格納されているかを確認します。
購入日、購入した本のジャンルは購入履歴テーブルのdate,productが使えそうですね。
| date | product | price | cs_id |
|---|---|---|---|
| 2024/4/1 | 雑誌 | 100 | c0001 |
| 2024/4/1 | 文庫本 | 200 | c0001 |
| 2024/4/2 | 雑誌 | 100 | c0002 |
| 2024/4/3 | マンガ | 300 | c0002 |
| 2024/4/3 | 文庫本 | 200 | c0003 |
購入者名は顧客テーブルのnameが使えそうです。
| id | name | age | sex |
|---|---|---|---|
| c0001 | Bob | 26 | male |
| c0002 | Jany | 32 | female |
| c0003 | Anna | 24 | female |
そして、2つのテーブルにはそれぞれ、cs_idとidというカラムに顧客idが入っています。
この2つのカラムを元に2つの表を紐づけてみましょう。
作りたいリストは以下のようになります。
| date | product | name |
|---|---|---|
| 2024/4/1 | 雑誌 | Bob |
| 2024/4/1 | 文庫本 | Bob |
| 2024/4/2 | 雑誌 | Jany |
| 2024/4/3 | マンガ | Jany |
| 2024/4/3 | 文庫本 | Anna |
Query SQLに以下のクエリを貼り付けて実行し予想通りの結果が返ってくるかを確認してみましょう。
SELECT date,product,name FROM purchase LEFT JOIN customer ON cs_id = id;ここではLEFT JOINを使っており、これは左側（つまり購入履歴テーブル）をベースに結合するように指示しています。
LEFT JOIN、RIGHT JOIN、INNER JOINを理解するために購入履歴テーブルと顧客テーブルへ少しデータを追加してみましょう。Schema SQLの末尾に以下を追加して実行してみてください。
-- purchaseテーブルにデータを挿入
INSERT INTO purchase (date, product, price, cs_id) VALUES
('2024-04-04', '参考書', 150, 'c0004')
-- customerテーブルにデータを挿入
INSERT INTO customer (id, name, age, sex)
VALUES
('c0005', 'Wein', 35, 'male')Query SQLの欄に以下のクエリを貼って実行し、購入履歴テーブルと顧客テーブルの中身を確認してみましょう。
SELECT * FROM purchase;
SELECT * FROM customer;resultに以下のような表が出力されていればOKです。
| date | product | price | cs_id |
|---|---|---|---|
| 2024/4/1 | 雑誌 | 100 | c0001 |
| 2024/4/1 | 文庫本 | 200 | c0001 |
| 2024/4/2 | 雑誌 | 100 | c0002 |
| 2024/4/3 | マンガ | 300 | c0002 |
| 2024/4/3 | 文庫本 | 200 | c0003 |
| 2024/4/4 | 参考書 | 150 | c0004 |
| id | name | age | sex |
|---|---|---|---|
| c0001 | Bob | 26 | male |
| c0002 | Jany | 32 | female |
| c0003 | Anna | 24 | female |
| c0005 | Wein | 35 | male |
cs_id、idのカラムに注目すると
c0004は購入履歴テーブルのみ、c0005は顧客テーブルにしか存在しないことが分かります。
この状態で購入日、購入した本のジャンル、購入者名のリストを取得してみます。
今度はQuery SQLに以下のクエリを貼り付けて実行してみましょう。
SELECT date,product,name FROM purchase LEFT JOIN customer ON cs_id = id;
SELECT date,product,name FROM purchase RIGHT JOIN customer ON cs_id = id;
SELECT date,product,name FROM purchase INNER JOIN customer ON cs_id = id;画面下部のresultエリアにQuery #1からQuery #3まで実行結果のテーブルが表示されるかと思います。
Query #1
- LEFT JOINを使った場合の実行結果です。購入履歴テーブルをベースに結合しています。
- 購入履歴テーブルに存在するc0004のレコードはSQLの実行結果に含まれますが、c0005のレコードは含まれません。
- 顧客テーブルにc0004の名前のデータが存在しないため、c0004のnameはnullになります。
Query #2
- RIGHT JOINを使った場合の実行結果です。顧客テーブルをベースに結合しています。
- Query #1とは逆に、顧客テーブルに存在するc0005のレコードはSQLの実行結果に含まれますが、c0004のレコードは含まれません。
- 購入履歴テーブルにc0005のdate,productのデータが存在しないため、c0005のdate,productはnullになります。
Query #3
- INNER JOINを使った場合の実行結果です。左右どちらかのテーブルにしか存在しないレコードは結合対象に含みません。
- そのため、c0004とc0005のレコードはどちらも実行結果に含まれません。
LEFT JOIN、RIGHT JOIN、INNER JOINの挙動のイメージはついたでしょうか？
最後まで読んでいただきありがとうございました。
今回はDB-fiddleを使っていますが他にも様々な無料で使えるプレイグラウンドサービスがあります。
ぜひ自分で手を動かしながらSQLの基礎知識の定着を図ってみてください。
