KATOエンジニヤリング開発日誌

「アウトプット無きエンジニアにインプットもチャンスも無い」の精神で書いています

新人エンジニア向け、仮想環境でSQLに慣れてみる 前編

f:id:masayuki_kato:20170227230710j:plain

2年前に会社の新人研修用に作成した資料を見つけたのでこちらに掲載します。

参考にした資料は下記になります。

達人に学ぶ SQL徹底指南書

達人に学ぶ SQL徹底指南書

一部古い情報があるかもしれないので試される際はお気をつけください。

PC上にLinuxの仮想開発環境を用意する

ドットインストールのローカル開発環境構築が参考になる。

ローカル開発環境の構築[Windows]編

ローカル開発環境の構築[MacOS X]編

※MacOSX編は#05の「CentOSの設定をしていこう」まで進めれば充分。Windows編は未確認だがおそらく#06「CentOSの設定をしていこう」まで進めれば大丈夫かと思う。

LinuxのCUIに慣れていなければ↓も見ておくといいかも。

UNIXコマンド入門(一般ユーザー編)

仮想開発環境にPostgreSQLを導入する

rpmを使ってPostgreSQLをインストールする。

vagrant$ sudo rpm -ivh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
vagrant$ sudo yum install postgresql93-server postgresql93-devel
vagrant$ sudo service postgresql-9.3 initdb
vagrant$ sudo service postgresql-9.3 start
vagrant$ sudo chkconfig postgresql-9.3 on
  • コマンドの行頭に「sudo」を付けることで管理者権限でコマンドを実行できる。
  • 「rpm」コマンドでPostgreSQLのパッケージを取得する。
  • 「yum」コマンドで取得したパッケージをインストールする。
  • 「service」コマンドでpostgresqlの初期化と起動を行う。
  • 「chkconfig」コマンドでpostgresqlの自動起動を行うようにする。
vagrant$ chkconfig --list | grep postgresql*
> postgresql-9.3    0:off   1:off   2:on    3:on    4:on    5:on    6:off

データベースを操作する「vagrant」ユーザーを作成する。

vagrant$ sudo su - postgres
postgres$ createuser --superuser vagrant
postgres$ exit
vagrant$ psql -U vagrant -d postgres
  • 「sudo su - postgres」で「vagrant」ユーザーから「postgres」ユーザーに変更する。
  • 「createuser」コマンドでデータベースを操作する「vagrant」ユーザーを作成する。
  • 「exit」で「postgres」ユーザーを抜けて元の「vagrant」ユーザーに戻る。
  • 「psql」コマンドでPostgreSQLに接続する。

関係データベースの簡単な説明

データベースにはいくつか種類があるが、主流は関係(リレーショナル)データベースになります。

データベースはアプリケーションのデータを保存するためのソフトで、大量のデータを蓄積しておいて、必要な情報を取り出したり加工したりします。

関係データベースは表の形でデータを管理します。

社員番号 社員氏名 職位 年齢
00241 荒巻 課長 55
00301 草薙 係長 28
00599 バトー 主任 33
00893 サイトー 主任 32
02007 アズマ null 20
02002 矢野 null 21
00302 イシカワ 係長 45
01023 ゴーダ null null
  • 表の事を「テーブル」という。
  • 社員番号等の列の事を「フィールド」や「カラム」という。
  • 社員番号00241さん等の行の事を「レコード」や「タプル」という。
  • 社員番号02007の職位のように値が無いものがある。値が無い事を「NULL」と表す。ネットとかで「NULL値」と表記しているものがあるが正確には値では無いので注意が必要。
  • SQLは普通のプログラム言語と違って2値論理(True, False)では無く、3値論理(True, False, unknown)になる。

詳細はここがわかりやすい。

データベースの中には複数のテーブルがあって、それらのテーブル同士を結合したり必要なレコードだけを取得したりすることを「関係演算」という。

null(unknown)があると関係演算を行う際に結果がnullの影響を受けてしまう。*1

関係データベースでは関係演算を行うと上記のような表形式で出力されるが厳密には表ではない。

なので上記表では社員番号の昇順で行が並び、社員番号→社員氏名→職位の順で列が並んでいるように見えるがデータベース上ではあくまで「社員番号」列と「社員氏名」列と「職位」列の集合があるだけである。

複雑なSQL文を考えるときに表で考えると苦労するので「集合」で考えるようにするのがSQL書くときのコツ。

SQL文の簡単な説明

SELECT

  • 関係演算の「射影」を行う。
  • テーブルの中から必要な列を取得する。

FROM

  • データを取得するテーブルを指定する。

WHERE

  • 比較演算子を使用して条件に一致した行だけを取得する。

GROUP BY

  • 指定した列名のグループに分ける
  • 指定した列名毎の集合に分けると考える
  • GROUP BY句を使用するとSELECT句で書く事のできる要素が下記の3点に限定される。
    • GROUP BY句で指定した集約キー
    • SUM,AVG,COUNT等の集約関数
    • 定数

例: GROUP BY 職位

元々は各タプル毎の集合だった(荒巻、草薙、バトー…)

GROUP BY することにより職位の集合になる (課長、係長、主任)

HAVING

  • 「GROUP BY」でグループ化した集合に対して抽出条件を与える。
  • 「GROUP BY」しなくてもHAVINGを使える(GROUP BY前の各タプル自体も一応集合だから)。

SQL文の実行される順番

ORDER BYは正確にはSQL文ではないのでここでは無視

SELECT→FROM→WHERE→GROUP BY→HAVINGでは無く、

FROM→WHERE→GROUP BY→HAVING→SELECTの順に実行される。

以下例題

年齢が30歳以上の役職者で2名以上同じ役職者がいる役職の「社員番号」を知りたい。

1. FROMで使用する集合を指定する。

FROM 社員
社員番号 社員氏名 職位 年齢
00241 荒巻 課長 55
00301 草薙 係長 28
00599 バトー 主任 33
00893 サイトー 主任 32
02007 アズマ null 20
02002 矢野 null 21
00302 イシカワ 係長 45
01023 ゴーダ null null

2. WHEREで必要な行を選択する。

WHERE 年齢 >= 30
社員番号 社員氏名 職位 年齢
00241 荒巻 課長 55
00599 バトー 主任 33
00893 サイトー 主任 32
00302 イシカワ 係長 45

注: 年齢がnull(unknown)だったゴーダさんも消えている(選択されていないことに注意)。

3. GROUP BYでグループ化する。

GROUP BY 職位
社員番号 社員氏名 職位 年齢
00241 荒巻 課長 55
00599 バトー 主任 33
00893 サイトー 主任 32
00302 イシカワ 係長 45

職位をキーにした集合になる。空白行は集合毎にわかれている様子を表しているだけで実際には空白行はないです。

4. HAVINGでグループに対して抽出条件を与える。

HAVING COUNT(*) >= 2
社員番号 社員氏名 職位 年齢
00599 バトー 主任 33
00893 サイトー 主任 32

各集合に対して2行以上あればTRUEの抽出条件を与える。

2人以上いるのは「主任」だけ。

5. SELECTで必要なカラム(列)を取得する

SELECT 社員番号

社員番号
00599
00893

*1:unknown >= 30 なんてものは計算の仕様がないから結果もunknownでしかない。