【DB】テーブル結合したデータ参照【SQL】テーブルを正規化すると単純に1テーブルだけの参照ではデータ取得ができません。そのため、複数のテーブルを結合して参照する必要があります。テーブル結合テーブルを結合する場合「SELECT句」に「JOIN」という条件を追加して複数のテーブルを結合します。今回結合するテーブルは以下の通りです。※テーブル作成までの経緯については下記を参照ください。1943年7月18日エイリアス(別名)テーブル結合を実施する場合、SQL文を少しでも簡素化する方法として「エイリアス」を定義します。単純にアルファベットを並べてエイリアスを定義しました。※エイリアスにテーブル名の一部を省略した名称などを使うこともあります。個人的には略称を扱わないようにしております。略称はかぶってしまう場合が多々あるためルール化が複雑になるためです。「SELECT」~「FROM」~「JOIN」~「ON」(~)SELECT:取得したい項目を指定します。FROM:取得したい項目を管理するテーブルを指定します。JOIN:結合して取得した項目を管理するテーブルを指定します。ON:結合する条件を指定します。WHERE:取得するデータを絞り込む条件を指定します。実際に結合してみます。B.ID)結合したすべてのデータが出力されます。※SQL文に「AS~」と指定しています。これはカラム名に対してのエイリアスになります。1943年7月18日このようにカラム名とエイリアス指定した名称でデータが取得されます。ついて上記で結合してデータを参照する方法を内部結合といいます。内部結合に対して外部結合という結合方法がありあます。内部結合とは結合するお互いのテーブルに存在する情報を出力する場合を「内部結合」するといいます。上記例では「WARSHIP_TYPE」テーブルに「4:海防艦」が定義されているのに出力結果には存在しておりません。これは内部結合を行った結果になります。内部結合はSQL文で明示的に記載することもできます。上記SQL文は内部結合の省略形式になります。JOIN」に変更しました。JOIN」句が内部結合の明示的な指定方法です。外部結合とは外部結合は、片側のテーブルにある情報すべてと結合できた情報を表示します。内部結合では抽出できなかった「WARSHIP_TYPE」テーブルの「4:海防艦」も出力したい場合は以下のようなSQL文なります。JOIN」に変更しました。JOIN」を指定したことで「WARSHIP_TYPE」テーブルのすべてを取得するという指定になります。NULL取得できないデータはすべて「NULL」として取得されます。「RIGHT」という表現がでてくると「LEFT」はできないのかなと考えます。JOIN」も可能です。JOIN」を指定すると「WARSHIP」テーブルのすべてを取得という指定になります。指定する意味は違いますが内部結合の時と同じ内容が取得されます。【DB】テーブル定義についてDBに定義するテーブルについて記載します。テーブルとはテーブルとはデータベース上でデータを種類ごとに分類し整理する箱のようなものだと考えます。テーブルを定義する場合に最低限必要な要素は以下の通りです。テーブル名:データベース上でテーブルを識別する名称です。項目(カラム):テーブル内にデータを種類ごとに分ける枠の名称です。データ型:カラムに登録するデータのプログラムから見たデータ種類です。主キー:各データを一意に識別可能な値になります。(※)※主キーは「索引:インデックス」の仲間になります。インデックスの持つ意味についてはここでは割愛いたします。データ型についてカラムに設定するデータ型によってデータの種類が決まってきます。主なデータ型については以下の通りです。PDFファイル画像データなどを登録するまた、データ型にはサイズ(文字数、数値の桁数)を明示的に定義する必要があるものもあります。上記表にあるデータ型だと「NUMERIC」、「CHAR」、「VARCHAR」などが該当します。※細かい型の説明については利用するDBの仕様に依存するためここでは割愛します。主キーについて主キーとは、データを一意に識別可能な情報です。主キーを定義する場合一つの項目から定義する場合と複数の項目から定義する場合とあります。以下のような社員情報のテーブル定義を作成します。滝本ひふみテーブル定義をする場合は以下のように考えることができます。VARCHAR(20)「社員番号」は各社員ごとでユニークになると判断できるため主キーとして定義しました。データの特色から「社員番号」を固定長、「名前」を可変長として定義しました。固定長と可変長テーブルの作り方テーブルの構成に最低限必要な要素を一つずつ定義します。上記の軍艦の情報をテーブル定義していきます。※テーブルの正規化については別途記載するため今回は割愛します。テーブル名の定義まずはテーブル名を定義します。テーブル名は情報が分かるものをアルファベットで表現します。※データベースにより日本語をそのまま使えることもありますが今回はアルファベットにしました。日本語を使わない理由カラム構成の定義カラム構成を下記のように考えていきます。◆主キーまず、主キーとなる項目を考えます。「No.」という項目がありますが実際には表を見やすくするための項目になります。この情報にはユニークになる項目がないため連番を識別番号の項目として追加します。◆カラム名カラム名もアルファベット化していきます。◆データ型カラムごとにデータ型を定義します。データ型は情報の特性を考えて定義します。※増やしていった場合1万件では不安と考えました。※海外艦でも100文字は超えないかなと考えました。※海外艦種名でも50文字は超えないかなと考えました。※年月日で表現できると考えました。テーブル作成テーブル構成は以下の通りで定義しました。※正規化についてはここでは考慮しておりません。DATEテーブルを作成は以下のSQLで行います。DATE)これでテーブルは作成されます。情報からテーブル作成をする場合、「正規化」という考え方があります。ここでは「第一正規化」について扱います。※第一正規化より先はある程度複雑な業務などを扱うプロジェクトでないといまいちイメージが出来なかったため割愛させてください。第一正規化とはテーブル定義を行ったときに同じ情報「名称など」を繰り返し扱うことがあります。このような情報をグループごとにテーブル化することを言います。正規化を行うと、データの冗長性、不整合が抑止できると考えております。同じ名称を繰り返しテーブルに追加していくと登録する文字列に間違いが生じることが予測されます。文字列に間違いが生じると本来同じ意味であった情報が違うものとして扱われてしまいます。このような状態を事前に抑止できるように正規化は行っておいた方がよいと思います。それでは、以下のテーブルを正規化してみます。※テーブル作成については別途記載しております。正規化しないでテーブル定義をした場合は以下の通りです。上記情報では、「艦種」の項目に重複した情報が存在することが分かります。この艦種だけでテーブルを作成することを考えます。元のテーブルから「艦種」項目に新しく追加したテーブル「WARSHIP_TYPE」を参照する「ID」の項目「WARSHIP_TYPE_ID」に変更します。DATEこの形が「第一正規化」を実施した形になります。実際に正規化したテーブルにデータを配置します。※「4:海防艦」は、参照するときの説明にあると便利かなと思い追記しました。1943年7月18日このような形でデータ登録を行います。この分割したテーブルをもとの情報と同じように参照する方法については下記を参照してください。【DB】命名ルールで「日本語」を使わない理由【考え方】データベースもプログラムも最近は日本語で変数名やカラム名などを定義できます。それでもアルファベットで定義することを行う個人的な理由を記載します。※あくまで個人的な考え方です。一般的な考え方に合わせているのではないでご了承ください。なんで日本語を使えるのに使わないのか読みづらくなるからプログラムコードに日本語が含まれると「コメント」なのか「プログラムコード」なのかわかりづらくなります。全角スペースが紛れてシンタックスエラーになるから全角入力を行っているとスペース入力時に全角スペースを誤って入力することがあります。半角スペースはプログラム命令の区切りとしてとらえられますが全角スペースは文字として扱われます。エディタの設定でスペースを表示していないと判別が難しくなります。入力モードを切り替えるのが煩わしいインターネットでサンプルコードを検索して、参考にしながら自分のコードに入力する場合に他の画面を見ながら入力を行うことがあります。画面を見ないで日本語を入力することは変換ミスなどがあるため非常に難しいというか無理なためです。コーディングルールで日本語禁止になっているから仕事で業務プログラムを書くことが多いためコーディングルールに合わせることが基準となってしまう癖がついてしまっているからです。※開発プロジェクトによるため、一概には言えません。まとめ以上のような理由でアルファベット推奨する考え方になっております。実際には今の環境になれてしまっていて日本語でコーディングしてみようと考えることがないのが原因でしょうか。本来、メリット・デメリットを比較してどちらが良いかというようなことを記載するのが正しいのでしょうが「にわか」で試してみてもよい部分を見つけることができませんでした。【DB】データ型についての簡単な理解DBで定義するデータ型について簡単な説明をします。一般的に扱われるデータ型は以下のような分類に分かれます。※データ型の説明に記載する型指定子は、僕自身が最低限だと考えているもののみ記載しております。記載以外の型指定子も存在しますがDBの仕様などいろいろ一概に言えないものもありますのでご了承ください。数値を扱うデータ型NUMERIC:データサイズを指定するデータ型INT:データサイズが決まっている数値型ログイン回数、金額など計算を伴う数値を扱う場合に定義します。文字、文字列を扱うデータ型VARCHAR:最大サイズを指定する文字列型(可変長)CHAR:固定サイズを指定する文字列型(固定長)名前、住所、メールアドレス、プロフィールなど文字で表現する情報を登録する場合に定義します。電話番号など数字だけど計算を行わないような場合も文字列として扱うことがあります。文字列の固定長、可変長について日付、時刻を扱うデータ型DATE:主に日付を扱うデータ型DATETIME:主に日付と時刻を扱うデータ型データを作成した日時や誕生日など日付、時刻で扱いたい情報を登録する場合に定義します。情報の中で有効・無効などの論理値で表現できる情報を登録する場合に定義します。画像ファイル、PDFファイルをデータベースに登録する場合に定義します。区別して考えることがあります。固定長とは固定長は文字数が明示的に定義されている場合を意味します。文字数が「固定長で10文字」と定義されていている場合は必ず10文字である必要があります。」このように何らかの形で文字数を揃える場合を「固定長」といいます。上表の例では「後方スペースパディング」で表現した場合になります。可変長とは可変長とは文字数の上限を明示的に定義した文字列になります。文字数が「可変長で10文字」と定義されている場合は10文字以内であればよいということになります。「123456」このように文字列をそのまま扱う形になります。【DB】物理削除と論理削除【考え方】データベースのデータ削除には物理削除と論理削除という考え方があります。今回の説明に利用するテーブル構造は以下の通りです。BOOLEAN登録されているデータは以下の通りです。※データについては、趣味全開です。TRUE物理削除とは以下のようにSQL文の「DELETE」構文を実行することを意味します。テーブルから物理的にデータが削除されます。1上記SQL文を発行した場合、以下のようになります。TRUEこのようにテーブルからデータが消えてしまう状態を「物理削除」と言っております。論理削除とは物理削除とは違い、主に以下のようなSQL文の「UPDATE」構文を実行して論理削除の状態を実現します。1上記SQL文を発行した場合、以下のようになります。TRUEこのようにデータは実際には削除されずに残っている状態になります。この状態では削除されているとは言えません。論理削除には、「削除」という状態の考え方が少し違います。データを削除するのではなくデータベースを利用しているプログラムから見て対象外とする意味があります。論理削除という状態を実現するためには、プログラムからデータ取得する条件に必ず「ENABLED」が「TRUE」であるルールが必要になります。これはシステム全体で決められたルールとして定義されていることにより実現できると言えます。削除されていないデータをすべて参照する場合のSQL文は以下の通りとなります。TRUE上記SQLを実行すると以下のデータが取得できます。TRUEこのように、データ取得SQL文に条件を付けるということで削除という状態を表現します。まとめ物理削除とは、データベースからデータ自体を削除してしまうことをいいます。論理削除とは、データベースからデータ削除自体は行わず、データベースを扱うシステムのルールにより削除されていないと判断されるデータのみ対象とすることで削除を実現する考え方です。いうのをシステム開発の現場では方式などと言います。【DB】データ操作するSQL文【SQL文】データベースのデータを操作するSQL文について説明します。説明に使用するテーブルは以下の通りです。VARCHAR登録されているデータは以下の通りです。※データについては、趣味全開です。あんこうデータを取得するSQL文データベースからデータを取得するSQL文は以下のような構成になります。※説明を簡素化したいと思いますので並び順、集約などのお話はここでは省いております。「SELECT」~「FROM」~「WHERE」~SELECT:取得したい項目を指定します。FROM:取得したい項目を管理するテーブルを指定します。WHERE:取得するデータを絞り込む条件を指定します。2このSQL文を発行することで「ID」が「2」に該当する情報のみ取得します。取得したイメージは以下の通りです。USER上記の通り、条件を指定しないでSQL文を発行します。この場合はすべてのデータが取得されるため取得したイメージは以下の通りになります。あんこうデータを登録(追加)するSQL文データ登録を行うSQL文は以下のような構成になります。※こちらも単純にデータを追加する説明といたします。INTO:データを登録するテーブルを指定します。()内にはデータを設定する項目(カラム名)を指定します。VALUES:()内に上記で指定した項目に設定する値を指定します。※項目や値を複数指定する場合は「カンマ」で区切ります。'あんこう')上記SQL文を発行することで、データが以下のように追加されます。あんこうデータを更新(変更)するSQL文データベースに登録されているデータを変更するSQL文は以下のような構成になります。「UPDATE」~「SET」~「WHERE」~UPDATE:データを更新するテーブルを指定します。SET:データを更新する項目(カラム名)と変更する値を指定します。(※)WHERE:データ更新する対象を絞り込む条件を指定します。※更新する対象項目(カラム名)と値は「イコール」を挟みます。更新する対象が複数ある場合は「カンマ」で区切ります。4上記SQL文を発行することで以下のようにデータが更新されます。カメさんデータを削除(物理削除)するSQL文データベースに登録されているデータを削除するSQL文は以下のような構文になります。FROM:データを削除するテーブルを指定します。WHERE:削除対象を絞り込む条件を指定します。※物理削除に対して論理削除という考え方がありますが今回は物理削除についてのみ説明します。4上記SQL文を発行することで以下の通り削除された状態となります。あんこう追加したデータを更新して削除したため元通りになりました。業務プログラムを開発していると「ゼロパディング」「ゼロサプレス」なんて言葉をたまに使います。ゼロパディングとはゼロパディングの「パディング」とは、文字数調整になります。7桁の前方ゼロパディングといった場合「150」を表現すると「0000150」の文字列となります。桁そろえの意味は「パディング」という言葉で表現され、「ゼロ」は桁を揃える文字に該当します。ゼロパディングといった場合「ゼロ(数字の0)」で文字数を揃えると言うことになります。亜種としては空白パディングなどもあります。これは「空白」で文字数を揃えると言うことになります。パディングの必要性は過去のシステム構成から来ていると個人的に思います。過去のシステムではデータを固定長で扱うことがあったため各データの文字数を設計により定義して、情報としては各データを連続した塊として表現していたからだと思いいます。ゼロサプレスとはゼロサプレスの「サプレス」は切り詰めに該当すると思います。上記ゼロパディングした「0000150」をゼロサプレスするという場合「150」になります。データ管理上は固定長で扱うが、システムを扱うユーザーにはわかりやすい数値で見せたいと思う場合に利用します。サプレスにも「ゼロ」だけではなく空白サプレスをする場合もあります。この場合は今のプログラミング言語(C#やJavaやPHPなど)では「trim」で実現できるかと思います。機能や役割などグループ分けしておいた方がよい場合があります。その場合ファイルを管理する間隔でプロジェクト上にフォルダーを追加する方法があります。フォルダーの追加エンティティをまとめておく「Entity」フォルダーを追加してみます。エンティティについてソリューションエクスプローラーでプロジェクト名で右クリックし、「追加」→新しいフォルダーを選択します。ソリューションエクスプローラーに「NewForlder1」が追加されるので名前を変更します。「Entity」にフォルダー名を変更しました。Space」が定義されます。Space」として適用されるためクラス名が重複することを意識する必要がなくなります。