【DB】テーブル結合したデータ参照【SQL】

テーブルを正規化すると単純に1テーブルだけの参照ではデータ取得ができません。そのため、複数のテーブルを結合して参照する必要があります。

テーブル結合

テーブルを結合する場合「SELECT句」に「JOIN」という条件を追加して複数のテーブルを結合します。

今回結合するテーブルは以下の通りです。

※テーブル作成までの経緯については下記を参照ください。

テーブルの正規化

テーブル構造

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 WARSHIP_TYPE_ID NUMERIC(2)
4 LAUNCH_DATE DATE

テーブル名:WARSHIP_TYPE

No. カラム名 データ型 主キー
1 ID NUMERIC(2)
2 NAME VARCHAR(50)

テーブルデータ

テーブル名:WARSHIP_TYPE

ID NAME
1 戦艦
2 空母
3 駆逐艦
4 海防艦

テーブル名:WARSHIP

ID NAME WARSHIP_TYPE_ID LUNCH_DATE
1 大和 1 1940年8月8日
2 赤城 2 1925年4月22日
3 五月雨 3 1935年7月6日
4 朝霜 3 1943年7月18日

エイリアス(別名)

テーブル結合を実施する場合、SQL文を少しでも簡素化する方法として「エイリアス」を定義します。

WARSHIP:「A」
WARSHIP_TYPE:「B」

単純にアルファベットを並べてエイリアスを定義しました。

※エイリアスにテーブル名の一部を省略した名称などを使うこともあります。個人的には略称を扱わないようにしております。略称はかぶってしまう場合が多々あるためルール化が複雑になるためです。

結合するSQL文

「SELECT」~「FROM」~「JOIN」~「ON」(~)

  • SELECT:取得したい項目を指定します。
  • FROM:取得したい項目を管理するテーブルを指定します。
  • JOIN:結合して取得した項目を管理するテーブルを指定します。
  • ON:結合する条件を指定します。
  • WHERE:取得するデータを絞り込む条件を指定します。

実際に結合してみます。

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)

結合したすべてのデータが出力されます。

※SQL文に「AS~」と指定しています。これはカラム名に対してのエイリアスになります。

ID NAME TYPE_NAME LAUNCH_DATE
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

このようにカラム名とエイリアス指定した名称でデータが取得されます。この結合方法は内部結合といいます。

内部結合・外部結合について

上記で結合してデータを参照する方法を内部結合といいます。内部結合に対して外部結合という結合方法がありあます。

内部結合とは

結合するお互いのテーブルに存在する情報を出力する場合を「内部結合」するといいます。

上記例では「WARSHIP_TYPE」テーブルに「4:海防艦」が定義されているのに出力結果には存在しておりません。これは内部結合を行った結果になります。

内部結合はSQL文で明示的に記載することもできます。上記SQL文は内部結合の省略形式になります。

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
INNER JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)

「JOIN」を「INNER JOIN」に変更しました。この「INNER JOIN」句が内部結合の明示的な指定方法です。

外部結合とは

外部結合は、片側のテーブルにある情報すべてと結合できた情報を表示します。内部結合では抽出できなかった「WARSHIP_TYPE」テーブルの「4:海防艦」も出力したい場合は以下のようなSQL文なります。

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
RIGHT JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)

「INNER JOIN」を「RIGHT JOIN」に変更しました。「RIGHT JOIN」を指定したことで「WARSHIP_TYPE」テーブルのすべてを取得するという指定になります。

ID NAME TYPE_NAME LAUNCH_DATE
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日
NULL NULL 海防艦 NULL

取得できないデータはすべて「NULL」として取得されます。

また、「RIGHT」という表現がでてくると「LEFT」はできないのかなと考えます。「LEFT JOIN」も可能です。上記SQL文で「LEFT JOIN」を指定すると「WARSHIP」テーブルのすべてを取得という指定になります。指定する意味は違いますが内部結合の時と同じ内容が取得されます。

 

SELECT
  A.ID,
  A.NAME,
  B.NAME AS TYPE_NAME,
  A.LAUNCH_DATE
FROM WARSHIP A
LEFT JOIN WARSHIP_TYPE B ON (A.WARSHIP_TYPE_ID = B.ID)
ID NAME TYPE_NAME LAUNCH_DATE
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

【DB】テーブル定義について

DBに定義するテーブルについて記載します。

テーブルとは

テーブルとはデータベース上でデータを種類ごとに分類し整理する箱のようなものだと考えます。テーブルを定義する場合に最低限必要な要素は以下の通りです。

  • テーブル名:データベース上でテーブルを識別する名称です。
  • 項目(カラム):テーブル内にデータを種類ごとに分ける枠の名称です。
  • データ型:カラムに登録するデータのプログラムから見たデータ種類です。
  • 主キー:各データを一意に識別可能な値になります。(※)

※主キーは「索引:インデックス」の仲間になります。インデックスの持つ意味についてはここでは割愛いたします。

データ型について

カラムに設定するデータ型によってデータの種類が決まってきます。主なデータ型については以下の通りです。

No. 種類 データ型 主な用途
1 数値 NUMERIC 金額、年齢など
2 文字、
文字列
CHAR、
VARCHAR
など
 名前、住所、メールアドレスなど
3 日付、
時刻
DATE、DATETIME、TIMESTAMP
など
データ登録日時、誕生日など
4 論理値 BOOLEAN、
BIT、
など
フラグ管理など。
5 バイナリ  BLOB
など
 PDFファイル画像データなどを登録する

また、データ型にはサイズ(文字数、数値の桁数)を明示的に定義する必要があるものもあります。上記表にあるデータ型だと「NUMERIC」、「CHAR」、「VARCHAR」などが該当します。

※細かい型の説明については利用するDBの仕様に依存するためここでは割愛します。

主キーについて

主キーとは、データを一意に識別可能な情報です。主キーを定義する場合一つの項目から定義する場合と複数の項目から定義する場合とあります。

例えば以下のような社員情報のテーブル定義を作成します。

No. 社員番号 名前
1 SN0001 葉月しずく
2 SN0002 遠山りん
3 SN0003 八神コウ
4 SN0004 滝本ひふみ

テーブル定義をする場合は以下のように考えることができます。

No. 項目 データ型(サイズ) 主キー
1 社員番号 CHAR(6)
2 名前 VARCHAR(20)

「社員番号」は各社員ごとでユニークになると判断できるため主キーとして定義しました。データの特色から「社員番号」を固定長、「名前」を可変長として定義しました。

固定長と可変長

テーブルの作り方

テーブルの構成に最低限必要な要素を一つずつ定義します。

No. 艦名 艦種 進水日
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

上記の軍艦の情報をテーブル定義していきます。

※テーブルの正規化については別途記載するため今回は割愛します。

 テーブル名の定義

まずはテーブル名を定義します。テーブル名は情報が分かるものをアルファベットで表現します。

テーブル名:「WARSHIP

※データベースにより日本語をそのまま使えることもありますが今回はアルファベットにしました。

日本語を使わない理由

カラム構成の定義

カラム構成を下記のように考えていきます。

◆主キー

まず、主キーとなる項目を考えます。「No.」という項目がありますが実際には表を見やすくするための項目になります。この情報にはユニークになる項目がないため連番を識別番号の項目として追加します。

主キー項目:「ID

◆カラム名

カラム名もアルファベット化していきます。

艦名:「NAME
艦種:「TYPE
進水日:「LAUNCH_DATE

◆データ型

カラムごとにデータ型を定義します。データ型は情報の特性を考えて定義します。

ID:「NUMERIC(5)
※増やしていった場合1万件では不安と考えました。
艦名:「VARCHAR(100)
※海外艦でも100文字は超えないかなと考えました。
艦種:「VARCHAR(50)
※海外艦種名でも50文字は超えないかなと考えました。
進水日:「DATE
※年月日で表現できると考えました。

テーブル作成

テーブル構成は以下の通りで定義しました。

※正規化についてはここでは考慮しておりません。

正規化とは

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 TYPE VARCHAR(50)
4 LAUNCH_DATE DATE

テーブルを作成は以下のSQLで行います。

CREATE TABLE WARSHIP (
  ID NUMERIC(5) PRIMARY KEY,
  NAME VARCHAR(100),
  TYPE VARCHAR(50),
  LAUNCH_DATE DATE)

これでテーブルは作成されます。

【DB】テーブル正規化の考え方【考え方】

情報からテーブル作成をする場合、「正規化」という考え方があります。ここでは「第一正規化」について扱います。

※第一正規化より先はある程度複雑な業務などを扱うプロジェクトでないといまいちイメージが出来なかったため割愛させてください。

第一正規化とは

テーブル定義を行ったときに同じ情報「名称など」を繰り返し扱うことがあります。このような情報をグループごとにテーブル化することを言います。

正規化を行うと、データの冗長性、不整合が抑止できると考えております。

例えば同じ名称を繰り返しテーブルに追加していくと登録する文字列に間違いが生じることが予測されます。文字列に間違いが生じると本来同じ意味であった情報が違うものとして扱われてしまいます。このような状態を事前に抑止できるように正規化は行っておいた方がよいと思います。

それでは、以下のテーブルを正規化してみます。

※テーブル作成については別途記載しております。

テーブル作成方法

No. 艦名 艦種 進水日
1 大和 戦艦 1940年8月8日
2 赤城 空母 1925年4月22日
3 五月雨 駆逐艦 1935年7月6日
4 朝霜 駆逐艦 1943年7月18日

正規化しないでテーブル定義をした場合は以下の通りです。

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 TYPE VARCHAR(50)
4 LAUNCH_DATE DATE

グループを抜き出してテーブル化

上記情報では、「艦種」の項目に重複した情報が存在することが分かります。この艦種だけでテーブルを作成することを考えます。

テーブル名:WARSHIP_TYPE

No. カラム名 データ型 主キー
1 ID NUMERIC(2)
2 NAME VARCHAR(50)

元のテーブル構成を変更

元のテーブルから「艦種」項目に新しく追加したテーブル「WARSHIP_TYPE」を参照する「ID」の項目「WARSHIP_TYPE_ID」に変更します。

テーブル名:WARSHIP

No. カラム名 データ型 主キー
1 ID NUMERIC(5)
2 NAME VARCHAR(100)
3 WARSHIP_TYPE_ID NUMERIC(2)
4 LAUNCH_DATE DATE

この形が「第一正規化」を実施した形になります。

正規化後のテーブルにデータを登録

実際に正規化したテーブルにデータを配置します。

テーブル名:WARSHIP_TYPE

ID NAME
1 戦艦
2 空母
3 駆逐艦
4 海防艦

※「4:海防艦」は、参照するときの説明にあると便利かなと思い追記しました。

テーブル名:WARSHIP

ID NAME WARSHIP_TYPE_ID LUNCH_DATE
1 大和 1 1940年8月8日
2 赤城 2 1925年4月22日
3 五月雨 3 1935年7月6日
4 朝霜 3 1943年7月18日

このような形でデータ登録を行います。この分割したテーブルをもとの情報と同じように参照する方法については下記を参照してください。

テーブルを結合して参照する方法

【DB】命名ルールで「日本語」を使わない理由【考え方】

データベースもプログラムも最近は日本語で変数名やカラム名などを定義できます。それでもアルファベットで定義することを行う個人的な理由を記載します。

※あくまで個人的な考え方です。一般的な考え方に合わせているのではないでご了承ください。

なんで日本語を使えるのに使わないのか

読みづらくなるから

プログラムコードに日本語が含まれると「コメント」なのか「プログラムコード」なのかわかりづらくなります。

全角スペースが紛れてシンタックスエラーになるから

全角入力を行っているとスペース入力時に全角スペースを誤って入力することがあります。半角スペースはプログラム命令の区切りとしてとらえられますが全角スペースは文字として扱われます。エディタの設定でスペースを表示していないと判別が難しくなります。

入力モードを切り替えるのが煩わしい

インターネットでサンプルコードを検索して、参考にしながら自分のコードに入力する場合に他の画面を見ながら入力を行うことがあります。画面を見ないで日本語を入力することは変換ミスなどがあるため非常に難しいというか無理なためです。

コーディングルールで日本語禁止になっているから

仕事で業務プログラムを書くことが多いためコーディングルールに合わせることが基準となってしまう癖がついてしまっているからです。

※開発プロジェクトによるため、一概には言えません。

まとめ

以上のような理由でアルファベット推奨する考え方になっております。実際には今の環境になれてしまっていて日本語でコーディングしてみようと考えることがないのが原因でしょうか。

本来、メリット・デメリットを比較してどちらが良いかというようなことを記載するのが正しいのでしょうが「にわか」で試してみてもよい部分を見つけることができませんでした。

【DB】データ型についての簡単な理解

DBで定義するデータ型について簡単な説明をします。

データ型の種類

一般的に扱われるデータ型は以下のような分類に分かれます。

※データ型の説明に記載する型指定子は、僕自身が最低限だと考えているもののみ記載しております。記載以外の型指定子も存在しますがDBの仕様などいろいろ一概に言えないものもありますのでご了承ください。

数値を扱うデータ型

  • NUMERIC:データサイズを指定するデータ型
  • INT:データサイズが決まっている数値型

ログイン回数、金額など計算を伴う数値を扱う場合に定義します。

文字、文字列を扱うデータ型

  • VARCHAR:最大サイズを指定する文字列型(可変長)
  • CHAR:固定サイズを指定する文字列型(固定長)

名前、住所、メールアドレス、プロフィールなど文字で表現する情報を登録する場合に定義します。また、電話番号など数字だけど計算を行わないような場合も文字列として扱うことがあります。

文字列の固定長、可変長について

日付、時刻を扱うデータ型

  • DATE:主に日付を扱うデータ型
  • DATETIME:主に日付と時刻を扱うデータ型

データを作成した日時や誕生日など日付、時刻で扱いたい情報を登録する場合に定義します。

論理値を扱うデータ型

  • BOOLEAN:「TRUE」、「FALSE」のみを扱うデータ型
  • BIT:「0」、「1」のみを扱うデータ型

情報の中で有効・無効などの論理値で表現できる情報を登録する場合に定義します。

バイナリデータを扱うデータ型

  • BLOB:バイナリデータを格納できるデータ型

画像ファイル、PDFファイルをデータベースに登録する場合に定義します。

【全般】文字列の固定長、可変長について

プログラムで文字列を表現する中で「固定長」、「可変長」と区別して考えることがあります。

固定長とは

固定長は文字数が明示的に定義されている場合を意味します。

例えば文字数が「固定長で10文字」と定義されていている場合は必ず10文字である必要があります。

No. 文字列 固定長で表現した場合
1 1 「1         」
2 1234567890 「1234567890」
3 123 「123       」
4 123456 「123456    」

このように何らかの形で文字数を揃える場合を「固定長」といいます。

上表の例では「後方スペースパディング」で表現した場合になります。

パディングの考え方

可変長とは

可変長とは文字数の上限を明示的に定義した文字列になります。

例えば文字数が「可変長で10文字」と定義されている場合は10文字以内であればよいということになります。

No. 文字列 可変長で表現した場合
1 1 「1」
2 1234567890 「1234567890」
3 123 「123」
4 123456 「123456」

このように文字列をそのまま扱う形になります。

【DB】物理削除と論理削除【考え方】

データベースのデータ削除には物理削除と論理削除という考え方があります。

今回の説明に利用するテーブル構造は以下の通りです。

テーブル名:USER

No. カラム名 データ型 主キー
1 ID NUMERIC
2 NAME VARCHAR
3 TEAM VARCHAR
4 ENABLED BOOLEAN

登録されているデータは以下の通りです。

※データについては、趣味全開です。

ID NAME TEAM ENABLED
1 角谷杏 カメさん TRUE
2 西住みほ あんこう TRUE
3 武部沙織 あんこう TRUE

物理削除とは

以下のようにSQL文の「DELETE」構文を実行することを意味します。テーブルから物理的にデータが削除されます。

DELETE FROM USER
WHERE ID = 1

上記SQL文を発行した場合、以下のようになります。

ID NAME TEAM ENABLED
2 西住みほ あんこう TRUE
3 武部沙織 あんこう TRUE

このようにテーブルからデータが消えてしまう状態を「物理削除」と言っております。

論理削除とは

物理削除とは違い、主に以下のようなSQL文の「UPDATE」構文を実行して論理削除の状態を実現します。

UPDATE USER
SET ENABLED = FALSE
WHERE ID = 1

上記SQL文を発行した場合、以下のようになります。

ID NAME TEAM ENABLED
1 角谷杏 カメさん TRUE
2 西住みほ あんこう FALSE
3 武部沙織 あんこう TRUE

このようにデータは実際には削除されずに残っている状態になります。この状態では削除されているとは言えません。

論理削除には、「削除」という状態の考え方が少し違います。データを削除するのではなくデータベースを利用しているプログラムから見て対象外とする意味があります。

論理削除という状態を実現するためには、プログラムからデータ取得する条件に必ず「ENABLED」が「TRUE」であるルールが必要になります。これはシステム全体で決められたルールとして定義されていることにより実現できると言えます。

削除されていないデータをすべて参照する場合のSQL文は以下の通りとなります。

SELECT
  ID,
  NAME,
  TEAM,
  ENABLED
FROM
  USER
WHERE ENABLED = TRUE

上記SQLを実行すると以下のデータが取得できます。

ID NAME TEAM ENABLED
1 角谷杏 カメさん TRUE
3 武部沙織 あんこう TRUE

このように、データ取得SQL文に条件を付けるということで削除という状態を表現します。

まとめ

物理削除とは、データベースからデータ自体を削除してしまうことをいいます。

論理削除とは、データベースからデータ削除自体は行わず、データベースを扱うシステムのルールにより削除されていないと判断されるデータのみ対象とすることで削除を実現する考え方です。こういうのをシステム開発の現場では方式などと言います。

【DB】データ操作するSQL文【SQL文】

データベースのデータを操作するSQL文について説明します。

説明に使用するテーブルは以下の通りです。

テーブル名:USER

No. カラム名 データ型 主キー
1 ID NUMERIC
2 NAME VARCHAR
3 TEAM VARCHAR

登録されているデータは以下の通りです。

※データについては、趣味全開です。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう

データを取得するSQL文

データベースからデータを取得するSQL文は以下のような構成になります。

※説明を簡素化したいと思いますので並び順、集約などのお話はここでは省いております。

「SELECT」~「FROM」~「WHERE」~

  • SELECT:取得したい項目を指定します。
  • FROM:取得したい項目を管理するテーブルを指定します。
  • WHERE:取得するデータを絞り込む条件を指定します。

一意のデータ参照する場合

SELECT
  ID,
  NAME,
  TEAM
FROM
  USER
WHERE
  ID = 2

このSQL文を発行することで「ID」が「2」に該当する情報のみ取得します。取得したイメージは以下の通りです。

ID NAME TEAM
2 西住みほ あんこう

全部取得する場合

SELECT
  ID,
  NAME,
  TEAM
FROM
  USER

上記の通り、条件を指定しないでSQL文を発行します。この場合はすべてのデータが取得されるため取得したイメージは以下の通りになります。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう

データを登録(追加)するSQL文

データ登録を行うSQL文は以下のような構成になります。

※こちらも単純にデータを追加する説明といたします。

「INSERT INTO」~(~)「VALUES」(~)

  • INSERT INTO:データを登録するテーブルを指定します。また、()内にはデータを設定する項目(カラム名)を指定します。
  • VALUES:()内に上記で指定した項目に設定する値を指定します。

※項目や値を複数指定する場合は「カンマ」で区切ります。

一つのデータを登録する場合

INSERT INTO USER (
  ID,
  NAME,
  TEAM)
VALUES (
  4,
  '五十鈴華',
  'あんこう')

上記SQL文を発行することで、データが以下のように追加されます。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう
4 五十鈴華 あんこう

データを更新(変更)するSQL文

データベースに登録されているデータを変更するSQL文は以下のような構成になります。

「UPDATE」~「SET」~「WHERE」~

  • UPDATE:データを更新するテーブルを指定します。
  • SET:データを更新する項目(カラム名)と変更する値を指定します。(※)
  • WHERE:データ更新する対象を絞り込む条件を指定します。

※更新する対象項目(カラム名)と値は「イコール」を挟みます。また、更新する対象が複数ある場合は「カンマ」で区切ります。

一つのデータを更新する場合

UPDATE USER
SET
  NAME = '河嶋桃',
  TEAM = 'カメさん'
WHERE
  ID = 4

上記SQL文を発行することで以下のようにデータが更新されます。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう
4 河嶋桃 カメさん

データを削除(物理削除)するSQL文

データベースに登録されているデータを削除するSQL文は以下のような構文になります。

「DELETE FROM」~「WHERE」

  • DELETE FROM:データを削除するテーブルを指定します。
  • WHERE:削除対象を絞り込む条件を指定します。

※物理削除に対して論理削除という考え方がありますが今回は物理削除についてのみ説明します。

論理削除とは

一つのデータを削除する場合

DELETE FROM USER
WHERE
  ID = 4

上記SQL文を発行することで以下の通り削除された状態となります。

ID NAME TEAM
1 角谷杏 カメさん
2 西住みほ あんこう
3 武部沙織 あんこう

追加したデータを更新して削除したため元通りになりました。