公開日：2020-08-09 11:14:55 • 最終更新：2023-12-28 00:23:21
このサイトの記事ではアフィリエイトプログラムを利用してアフィリエイト広告リンクを掲載しています。

非エンジニア職の方のためのデータベースとSQL入門（学習ツール付き）

IT技術 教養としてのテクノロジー入門

この記事について

いうまでもないことですが、データ利活用がますます重要になってきています。ほとんどの業務の場でデータベースで溜めたデータをうまく分析、抽出して活用することが求められています。実際の抽出作業をエンジニアに依頼してもらうだけでなく、エンジニアでない人も自らデータ抽出をする機会が増えたり、逆に毎回エンジニアに依頼するがゆえに不便に感じている人もいるのではないのでしょうか。

この記事では、SQLというデータベースのデータ処理言語を用いてデータ管理をすることについて初心者向けに解説しています。

なお、実際にSQLを使う機会は、Microsoft Accessであったり、会社で用意されたデータ抽出ツールなどであると思います。今回はこちらの学習ツールを私のほうで作りましたのでこちらをつかって勉強できるようにしております。

SQLとは

SQLとはStructured Query Languageの頭文字３つをとってつけたデータベース操作用のプログラミング言語の一種です。データベースにSQLで書かれたプログラムを入力することで、データベースが記載されたデータを出力したり入力したりします。なおこれらの命令のことをクエリといいます。


# データを検索出力する場合。
SELECT * FROM customer WHERE name = "佐藤太郎";

#データを入力する場合
INSERT INTO fruits (title, price) VALUES ("リンゴ", "200");

データベースにも様々な種類がありますが、SQLはRDB（リレーショナルデータベース）という以下の画像ような表構造のデータを取り扱うことを得意としています。一般にこれらの表はテーブルといいます。それ以外のDBでも、SQLの派生を用いて操作することが多く、DB操作の基本中の基本であるといえます。

　

それでは早速こちらの学習ツールを使ってSQLとデータベース体験をしてみましょう。

さっそく体験してみる

今回利用する学習ツールについて

今回使用する学習ツールには家電ショッピングサイトのデータを想定した以下のテーブルとデータが入っています。

productテーブル

　

productテーブルは、家電ショッピングサイトで販売している商品のデータが入ったテーブルです。それぞれの列は以下のデータが入っています。

  • id:商品IDです。商品ごとの識別子です。
  • title:商品名です。
  • price:商品価格です。
  • description:商品の説明です。
  • maker:商品の製造メーカーです。

userテーブル

　

userテーブルは、家電ショッピングサイトに会員登録しているユーザーが入ったテーブルです。

  • id:ユーザーの識別子です。
  • account:ユーザーがログインする際に用いるアカウントです。
  • name:ユーザーの氏名です。

product_orderテーブル

　

product_orderテーブルは、家電ショッピングサイトでの注文データを登録してあります。

  • id:注文ごとの識別子です。
  • user_id:注文したユーザーのidです。userテーブルのidと紐づきます。
  • product_id:商品IDです。productテーブルのidと紐づきます。
  • number:購入個数です。
  • order_datetime:注文日時です。

なお、学習ツールの「テーブルの構成を確認する」ボタンを押すとテーブルの詳細な作りが確認できます。※具体的な記載内容の見方は後述します。

データを取得してみる

それでは早速データの取得をしてみましょう。まず、以下のSQLを学習ツールのフォームに入力して、SQL実行を押してみましょう。


SELECT * FROM user;

以下の画面が出れば成功です。

　

データ出力SQLの構文

データ出力SQLの構文は以下となっています。


SELECT 出力したい列(全部取得したい場合は*) FROM テーブル名;

こちらを意識して以下のSQLを実行してみましょう。


SELECT account, name FROM user;

account列とname列だけが出力されます。

　

SELECTとFROMの間に*を入力するとすべての列が出力されましたが、SELECT と FROMの間に列名を入れると指定された列名だけが出力されます。

列を指定すると。今回は列数がそんなに多くないものの実務では、たくさんの列が表示されるケースもあるため列を指定するのが一般的です。注意点として複数の列を指定するときは,区切りにしてください。しないとエラーが出ますので注意しましょう。

FROMと;の間にはテーブル名を指定します。userとなっていたのをproductに変えてみましょう。productテーブルが表示されます。


SELECT * FROM product;
　

検索してみる

次はデータの検索をしてみましょう。データの検索はテーブル名の後ろにWHERE句をつけます。以下のSQLを実行してみましょう。


SELECT account,name FROM user WHERE account = "taro123";
　

このSQLはaccount = "taro123"つまりtaro123のアカウントのユーザーを探すというSQLです。

account = "taro123"は=を使った一致検索ですが、ほかにもLIKEを使った部分検索もできます。


SELECT account,name FROM user WHERE name LIKE "%山田%";
　

LIKE "%{検索文字}%"という構文です。これは山田という名前の付くユーザーを探すというクエリになります。

続いてINという検索もしてみましょう。INは指定されたものが含まれるものを探すという検索になります。以下のクエリはIDが1,3,5のものを出力するというクエリです。なお文字列は''で囲う必要がありますが、数値は囲う必要はありません。


SELECT account,name FROM user WHERE id IN (1, 3, 5);
　

ここまでは直接値を探すという検索でしたが、数値の計算もできます。以下はidが3より上のユーザーを探すというクエリになります。


SELECT account,name FROM user WHERE id > 3;
　

このほかにも検索は様々パターンがあり、より複雑な条件をしていすることができます。以下検索条件クエリの例をご紹介します。



# 4/14 - 4/16 の間
SELECT id, number, order_datetime FROM product_order WHERE order_datetime BETWEEN "2020-04-14" AND "2020-04-16";

# id 2 以上かつ id 4以下
SELECT account,name FROM user WHERE id >= 2 AND id <= 4;

# id 2 以上かつ id 4以下　または　名前が山田太郎 
SELECT account,name FROM user WHERE (id >= 2 AND id <= 4) OR name = "山田太郎";

表示列に別名をつける

ここまでテーブルに定義された列名をそのまま列名として出力していました。しかし出力したいケースによっては例えば列名を日本語表記にしたいなど、列名表示を変更したいときがあります。そういうときにはASを使います。


SELECT account AS アカウント, name AS 氏名 FROM user;
　

なお画像のほうはasとして小文字を使っていますが、小文字でも問題ありません。SQLは大文字と小文字の区別がなく例えばselect * from user;としても正しく実行されます。

表示順番を変更する

これまでid順に表示されていました。しかしながら特定の列順に表示したいケースがあります。その場合は、ORDER BY という構文を使います。


SELECT title, price, maker FROM product ORDER BY price DESC;
　

上記はprice列にある価格を高い順に表示するというクエリです。なおDESCという表記が高い順に表示する指定で、逆にASCにすると低い順になります。


SELECT title, price, maker FROM product ORDER BY price ASC;

関数を使った集計

次は関数という機能を使ってデータを集計してみましょう。まずMax()という関数を使って最も高額な商品だけを出してみます。


SELECT title, Max(price), maker FROM product;
　

関数には様々なものがあります。例えば以下のようなcount()という関数を使うと現在productテーブルには何個の商品が出品されているか、データの個数を数えてくれます。以下のクエリは数え上げる列の代表としてidを指定しています。


SELECT count(id) AS 現在の出品数 FROM product;
　

データのグルーピング

もう少し関数を使って応用的なことをしてみましょう。「Group By 列名」という構文を使うことで、列名ごとにグルーピングします。下のSQLはmaker名ごとに出品数を数え上げるクエリです。


SELECT maker, count(id) AS 現在の出品数 FROM product GROUP BY maker order by 現在の出品数;
　

テーブルの結合

続いてテーブルの結合をやってみます。product_orderテーブルは以下のように、user_id,product_idが置いてあるのみで、実際にuserテーブルやproductテーブルを再度参照しないと、実際にどのユーザーがどの商品を購入したのかわかりません。

　

ここで便利なのがテーブルの結合という機能です。テーブルの結合は「join テーブル名 on 紐づけるid」または、「FROM テーブル1,テーブル2 WHERE 紐づけるid」という構文で結合できます。なお複数のテーブルを扱うときは「テーブル名.列名」としないと、列名重複がおきてしまいます。重複しない場合は、列名単体でよいですがidのような重複するものは「テーブル名.列名」という表記をするようにしましょう。

結合をするとSQL記載量が途端に増えます。先ほどご紹介したASや改行をつかって、書きやすくしたり、見やすくしたりするようにしましょう。


# JOIN句を使う例
SELECT title, maker, price,number, order_datetime
FROM product AS p
JOIN product_order AS po ON p.id = po.product_id;

# WHERE句を使う例
SELECT p.title, p.maker, p.price, po.number, po.order_datetime
FROM product AS p, product_order AS po
WHERE p.id = po.product_id;
　

テーブルを作ってみる

ここまでデータを出力する操作を扱ってきました。最後に入力操作を簡単に解説します。今回は、テーブルの作成、データの追加を扱います。

まずテーブルの作成です。テーブルの作成は以下の構文を使います。ここでは家電ショッピングサイトの従業員を登録するテーブルを作ると想定してみましょう。テーブル作成はCREATE　TABLE という構文を使ってテーブルを作成します。


 CREATE TABLE employee (
        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        account varchar(100) NOT NULL,
        name varchar(100) NOT NULL,
        position varchar(100) NOT NULL
      );

見たこともない記載が多いと思いますが、以下の構文となっています。


 CREATE TABLE テーブル名 (
        列名 列の型 オプション,
        列名 列の型 オプション
      );

列の型とは、列の値の種類の指定です。INTEGERとは数値のことで、varchar(100)は100文字以下の文字を指します。このほかに日付の場合はDATETIMEなどがあります。

オプションは、その列につける補助機能です。例えばidは「NOT NULL」「PRIMARY KEY」「AUTOINCREMENT」という３つのオプションをしています。「NOT NULL」はこの列が空の値を持ってはいけないという意味です。「PRIMARY KEY」はこの列が主キーを表し重複した値を許さないことを意味します。「AUTOINCREMENT」はデータ挿入時に、値を指定しなかった場合、自動的に値が採番されていれられるオプションです。次のデータ挿入で具体的な効果を説明します。

それでは、上記のemployeeテーブルの作成クエリを実行してみましょう。フォームに入力後SQL実行で作成されます。何も出力されないのは出力クエリではなく入力クエリであるためです。また「Error: table employee already exists」エラーが出てしまった人は、すでにテーブルが作成されており、重複してしまった可能性があります。リセットボタンを押せば初期に戻るのでリセットボタンをおして再度試してみましょう。

CREATE TABLEのクエリを実行したら「テーブル構成を確認する」ボタンを押してみましょう。employeeテーブルが作成されているのが確認できるかと思います。なお、ここまでくればテーブル構成の右欄の構成SQLの意味が分かると思います。productテーブルなどほかのテーブルがどういう構成なのか確認してみましょう

　

テーブルにデータを入れてみる

最後にデータの登録をやってみましょう。データの登録はINSERT文を使って行います。構文は以下のようになっています。


 INSERT INTO テーブル名 (入力する列名,入力する列名...) VALUES (入力する値, 入力する値...);

それでは早速employeeテーブルに以下の値を入れてみましょう。以下のクエリを実行してみましょう。実行後SELECT文で検索するとデータが登録されていることがわかります。


INSERT INTO employee (id, account, name, position) VALUES (1, "jiro123", "山本次郎", "カスタマーサポート");
　

続いて先ほど設定したオプションの効果を体験してみましょう。まず、idを入力せずにデータを挿入してみます。こちらのSQLを実行してみましょう。


INSERT INTO employee (account, name, position) VALUES ("kaneda1", "金田大樹", "システムエンジニア");

実行後、SELECTしてみると、自動的にidに番号が振られていることがわかります。これがAUTOINCREMENTのオプションの効果です。

　

今度は氏名も指定しないでINSERTしてみましょう。


INSERT INTO employee (account, position) VALUES ("sato51", "システムエンジニア");

「Error: NOT NULL constraint failed: employee.name」というエラーが出たと思います。これがNOT NULLオプションの効果で、値の空欄を許さないようにしています。

練習問題

以上がSQLの基本的な使い方でした。SQLは何度も打ち込んでいって覚えていくものなので、10題ほど練習問題を提示します。いくつかの問題は今回解説していない構文や関数も必要ですが、ヒントをもとに調べたり、後述の解答例を見てこんな書き方もあるんだと勉強してみてください。

LEVEL1:userテーブルから御坂修という名前のユーザーのaccountのみ出力してください。

　

LEVEL2:userテーブルからsの付くアカウントのユーザーのaccount,nameを出力してください。

　

LEVEL3:productテーブルの最安値を出力してください。
※ヒント；最小値はMin()関数でだせます。

　

LEVEL4:productテーブルの各makerの商品の平均価格を出力してください。
※ヒント：平均はAVG()関数でだせます。

　

LEVEL5:userテーブルとproduct_orderテーブルを使って全ユーザーの購入履歴を出力してください。

　

LEVEL6:userテーブルとproduct_orderテーブル,productテーブルを使って各ユーザーの購入履歴を出力してください。なお、購入履歴がないユーザーは下記のように購入履歴を空欄のまま表示してください。
※ヒント：外部結合という結合を使います。調べてみてください。また、テーブルは3つ以上の結合も可能です。

　

LEVEL7:userテーブルとproduct_orderテーブル,productテーブルを使って各ユーザーの購入履歴を出力してください。購入履歴がないユーザーは表示しなくてOKです。ただし、productテーブルのpriceとproduct_orderテーブルのnumberを掛け合わせて合計金額を出力してください。
※ヒント；列名に演算子を使うと、列同士の計算ができます。

　

LEVEL8:各メーカーの合計売上を算出してください。
※ヒント：LEVEL7でやってみた列同士の計算に関数、ASを組み合わせます。

　

LEVEL9:サブクエリをつかって　以下の表示のように山田太郎の購入合計金額を出してください。
※ヒント：LEVEL10のための準備です。サブクエリは自分で調べてみましょう。

　

LEVEL9:サブクエリをつかって　以下の表示のように山田太郎と佐藤花子の購入合計金額を出してください。
※ヒント：サブクエリ同士の結合です。WHERE句を使わない結合の応用でできます。

　

練習問題解答例

回答は以下のボタンをクリックすると表示されます。


#LEVEL1
select account from user where name = "御坂修";

#LEVEL2
select account , name from user where account like "%s%";

#LEVEL3
SELECT MIN(price) AS 最安価格 FROM product;

#LEVEL4
SELECT maker, AVG(price) as price_average FROM product GROUP BY maker;

#LEVEL5
select po.id, u.name, po.order_datetime from user as u JOIN product_order as po on po.user_id = u.id ;
  
#LEVEL6
SELECT u.name, p.title, p.price, po.number, po.order_datetime
FROM user AS u
LEFT JOIN product_order as po ON u.id = po.user_id
LEFT JOIN  product as p ON po.product_id = p.id;

#LEVEL7
SELECT u.name, p.title, p.price, po.number, p.price * po.number, po.order_datetime 
FROM user AS u
JOIN product_order AS po on po.user_id = u.id 
JOIN product AS p on po.product_id = p.id;

#LEVEL8
SELECT p.title, SUM(p.price * po.number) AS total
FROM product AS p
JOIN product_order AS po ON p.id =po.product_id
GROUP BY p.title;

#LEVEL9
SELECT taro.total as 山田太郎の購入金額合計
FROM
(
  SELECT SUM(p.price * po.number) as total
  FROM user AS u
  JOIN product_order AS po ON u.id = po.user_id
  JOIN product as p ON p.id = po.product_id WHERE u.name = "山田太郎"
) as taro;


#level10
SELECT taro.total as 山田太郎の購入金額合計, sato.total as 佐藤花子の購入金額合計 
FROM
(
  SELECT SUM(p.price * po.number) as total
  FROM user AS u 
  JOIN product_order AS po ON u.id = po.user_id 
  JOIN product as p ON p.id = po.product_id
  WHERE u.name = "山田太郎"
) as taro,
(
  SELECT SUM(p.price * po.number) as total
  FROM user AS u JOIN product_order AS po ON u.id = po.user_id
  JOIN product as p ON p.id = po.product_id
  WHERE u.name = "佐藤花子"
) as sato

なおこちらの解答はあくまで解答例です。ほかにも記載方法はあります。各LEVELの画像で示したような結果が出力されてば正解です。

おわりに

駆け足でしたが以上が非エンジニア向けのSQL入門でした。データ出力中心のため、まだ、データの削除、データん更新などは取り扱っておらず、またデータ出力もUNIONなどまだ取り扱っていないものがあります。もしさらに勉強したい方がいましたら、本を一冊買ってみたり、よりエンジニア向けのSQLサイトで学んでみることをおすすめします。この記事のSQLを習得した方でしたら問題なく覚えられると思います。

この記事を通じて少しでもSQLに対する理解が深まったり、業務の効率化につながれば幸いです。

Photo by Javier Garcia Chavez on Unsplash

みやうデジタルラボ - にほんブログ村