「情報処理試験まとめ/応用情報技術者試験の勉強法まとめ/応用情報技術者試験向けデータベース入門」の編集履歴（バックアップ）一覧はこちら
「情報処理試験まとめ/応用情報技術者試験の勉強法まとめ/応用情報技術者試験向けデータベース入門」(2016/03/09 (水) 20:31:55) の最新版変更点
追加された行は緑色になります。
削除された行は赤色になります。
&topicpath()&aname(top)
#divclass(pageTitle){応用情報技術者試験向けデータベース入門}
***初めに
****初めに
基本情報技術者試験、応用情報技術者試験の受験者向けのデータベース学習の自習書です
実際にデータベースを作ったり、SQL文を入力することで仕組みを勉強することを目的としています
主に環境作りと簡単な説明をすることを目的としていますので、しっかりとした解説は参考書等をご覧ください
****環境について
*****必要なソフト
-Microsoft SQL Server 2012 Express With Tools
*****ダウンロード、インストール方法
-Microsoft SQL Server 2012 Express With Toolsをダウンロード
--http://www.microsoft.com/ja-jp/download/details.aspx?id=29062
--64bit版のWindowsを利用している人は、JPN\x64\SQLEXPRWT_x64_JPN.exeをダウンロード
--32bit版のWindowsを利用している人は、JPN\x86\SQLEXPRWT_x86_JPN.exeをダウンロード
-インストール方法
--ダウンロードしたexeをダブルクリックする
--管理者による実行の確認画面が出るのでokボタンをクリックする
--新規スタンドアロンインストールを選択
--ライセンスに同意して次へ
--「更新プログラムを含める」をチェックされているのを確認して次へ
--セットアップウィンドウが立ち上がる
--機能の選択はそのまま次へ
--インスタンスの構成はそのまま次へ
--サーバの構成はそのまま次へ
--データベースエンジンの構成はそのまま次へ
--エラーレポートはそのまま次へ
--インストールの終了
***イントロダクション ～あるラーメン屋さんのお話～
****初めに
主人公はラーメン屋さんの店長です。
これまで店長は、一日の売り上げや、商品ごとの販売数、麺類、ご飯ものなど種類別の販売数などを電卓で計算していました。しかしとても時間がかかるためデータベースを活用することで、もっと簡単に集計できるようにしたいと思い始めました。
そこで店長はデータベースの仕組みや使い方について勉強を始めようと思い立ちました。
****入力するデータについて
普段、実際には以下のようなレシートを発行しています。店長はこのデータを利用して実際にデータベースを作れないかを考えました。
|&image(http://cdn47.atwikiimg.com/matowiki/?cmd=upload&act=open&pageid=101&file=r01.jpg)|&image(r02.jpg)|&image(r03.jpg)|
|&image(r04.jpg)|&image(r05.jpg)||
普通によくみるレシートです。記入されている項目を上から順番に説明します。
-レシート番号。ユニークな番号（重複しない数字）で、レシートを発行するたびに数字は増えていきます
-日付。レシートを発行した日付が表示されています
-担当者名。レジを入力した従業員の担当者番号と名前が表示されています
-料理。注文した料理の名前、注文数、合計価格（単品価格×注文数）が表示されています
-支払い合計。すべての合計金額です
***テーブルの作り方入門 ～データベースの正規化のお話～
****適当に作る
店長は、まず適当に作ってみることにしました
■ 適当に作成したテーブル
|レシートNo|日付|担当No|担当者名|商品名|注文数|価格|商品名|注文数|価格|商品名|注文数|価格|
|1|2013/05/20|1|鈴木|醤油ラーメン|1|600|餃子|1|350|ライス|1|250|
|2|2013/05/20|2|佐藤|味噌ラーメン|1|600|餃子|1|350||||
|3|2013/05/21|1|鈴木|醤油ラーメン|2|600|ライス|2|250||||
|4|2013/05/21|1|鈴木|醤油ラーメン|1|600|チャーハン|1|400||||
|5|2013/05/21|2|佐藤|野菜ラーメン|1|600|餃子|1|350||||
それが、このテーブルです。価格は単価（1品あたりの価格）を入力することにしました。注文数があるので、単価×注文数で商品ごとの合計金額は計算すれば表示できるのではないかと考えたのです。
そして、レシート全体の合計金額も入力しないようにしようと考えました。単価×注文数の金額をすべて足していけばレシート全体の合計金額が計算できると考えたのです。
****第一正規形
しかし、このテーブルだと問題がでてきてしまいます。
-問題1 このテーブルでは商品を最高3種類までしか入力できません。4種類以上の注文を想定して横に長くする必要がでてきてしまいます
-問題2 仮に一枚のレシートで10種類まで入力できるよう表を横に長くすると、今度は1種類しか頼まないお客さんがいた場合、入力されていないスペースが無駄になってしまいます
-問題3 現実的にあり得ないかもしれないけど、10種類以上頼むお客さんがいたら入力できなくなってしまいます
問題2と問題3は相反する問題で解決ができませんね。
そこで店長は考えました。同じレシートNoの重複を許して縦に長い表を作れば、商品を何種類も入力できるから便利なのでは？
そこで作成したのが以下のテーブルです。
■ 商品を何個でも登録できるようにしたテーブル（第一正規形）
|レシートNo|日付|担当者No|担当者名|商品名|注文数|価格|
|1|2013/05/20|1|鈴木|醤油ラーメン|1|600|
|1|2013/05/20|1|鈴木|餃子|1|350|
|1|2013/05/20|1|鈴木|ライス|1|250|
|2|2013/05/20|2|佐藤|味噌ラーメン|1|600|
|2|2013/05/20|2|佐藤|餃子|1|350|
|3|2013/05/21|1|鈴木|醤油ラーメン|2|600|
|3|2013/05/21|1|鈴木|ライス|2|250|
|4|2013/05/21|1|鈴木|醤油ラーメン|1|600|
|4|2013/05/21|1|鈴木|チャーハン|1|400|
|5|2013/05/21|2|佐藤|野菜ラーメン|1|600|
|5|2013/05/21|2|佐藤|餃子|1|350|
このようにすれば、1品だったら1行ですし、何種類もの商品を頼まれても縦に長くなるだけなので、何種類でも登録することができます。
これが第一正規形の姿です。
#divclass(h6){第一正規形ではなぜいけないのか}
上記のような第一正規形がなぜいけないのかというと、基本的には保存する情報が重複して登録されているからです。また、あらかじめ担当者名をマスタ登録できないなどの問題もありますね。
上記の第一正規形の「候補キー」はどの属性でしょうか？候補キーというのは、ある「行」を特定するために必要な属性、またはその組み合わせということになります。上記の例ではレシートNoだけでは行を特定することはできません。従ってもう一つの属性が必要です。というわけで行をみると特定にできそうなのは「商品名」ですね。このテーブルの候補キーは｛レシートNo、商品名｝ということになるわけです。
また、これらの情報のうち、日付、担当者名、担当者Noは重複して保存されています。レシートに対して日付と担当者が同じなのは、普通の飲食店なら当たり前のことですね。ですから、わざわざ日付、担当者などは重複して保存する必要はありません。しかし実際には、日付と担当者が重複で登録されているからなんとかしたいわけですね。
第一正規形である理由として「すべての属性が単一値で、候補キーＡ、Ｂの一部であるＢに非キー属性のＣが部分関数従属するため」という説明があります。これを今回に当てはめて説明すると「すべての属性が単一値で、候補キーレシートNo、商品名の一部であるレシートNoに非キー属性である日付（または担当者No、担当者名）が部分関数従属するから」第一正規形であるわけです。
候補キーの一部であるレシートNoがわかれば、日付、担当者は特定できてしまいますので、第一正規形だというわけでずね。
****第二正規形
ここまではうまくできました。しかし、いろいろ考えるとそれでもいくつかの問題がでてきてしまいました。
-問題1 それでもレシートNo、日付、担当者No、担当者名が重複しているので余分なスペースが必要になってします
-問題2 日付や担当者名の修正をしたとき、関連するすべての行を修正しなくてはならくなってしまいます
関連するすべてのテーブルを修正しなければならないので非常に面倒ですね。そこで店長はテーブルを二つに分離すれば面倒がなくなるのではないかと考えました。
そして作成したのが以下のテーブルです。
■ レシートテーブル レシートのデータと商品のデータを分離（第二正規形）
|レシートNo|日付|担当者No|担当者名|
|1|2013/05/20|1|鈴木|
|2|2013/05/20|2|佐藤|
|3|2013/05/21|1|鈴木|
|4|2013/05/21|1|鈴木|
|5|2013/05/21|2|佐藤|
■ レシート詳細テーブル レシートのデータと商品のデータを分離（第二正規形）
|レシートNo|商品名|注文数|価格|
|1|醤油ラーメン|1|600|
|1|餃子|1|350|
|1|ライス|1|250|
|2|味噌ラーメン|1|600|
|2|餃子|1|350|
|3|醤油ラーメン|2|600|
|3|ライス|2|250|
|4|醤油ラーメン|1|600|
|4|チャーハン|1|400|
|5|野菜ラーメン|1|600|
|5|餃子|1|350|
こうして、レシートテーブルからレシートNo1のものを表示するとき、レシート詳細テーブルから該当する料理を持ってくれば済みます。そして重複データは最低限だけで、すっきりさせることができました。データのレシートの日付や担当者の修正も1行で済むため最低限で済みますね。
#divclass(h6){第二正規形ではなぜいけないのか}
第二正規形になると一見よさそうにみえますが、それでも問題が残っています。レシートテーブルの担当者名と担当者Noの関係です。レシートテーブルを縦に眺めてみます。そうすると、担当者Noが1で、担当者名が鈴木さんという情報が複数でてきます。これはやはりデータとして冗長になってしまっています。担当者Noが1なら鈴木さんなのは当たり前ですので、それぞれ保存すると容量の無駄になりますし、例えば氏名が変わったときに、過去の担当者名をすべて変更するというのは効率が悪いですね。また名前を入力ミスすると鈴木さんが須々木になったりして統一がとれなくなってしまいます。
レシートテーブルでの候補キーは「レシートNo」です。第二正規形にしたことで候補キーが複合キーではなく単一の属性のキーになっているのがわかります。そして今回は担当者Noと担当者名の関係が問題になっています。担当者Noがわかれば担当者名は一意に求められますので、そこが問題です。そして、担当者Noは候補キーではありません。
第二正規形であることの理由として「すべての属性が単一値で、候補キーからの部分関数従属がなく、推移的関数従属性Ａ→Ｂ→Ｃがあるため」というものがあります。これを今回のレシートテーブルに当てはめてみますと「すべての属性が単一値で、レシートNoからの部分関数従属がなく、推移的関数従属制レシートNo→担当者No→担当者名があるため」第二正規形となっていることがわかります。Ａ→Ｂ→ＣというのはレシートNoから担当者Noが求められ、さらに担当者Noから担当者名が求められるのはおかしいということですね。レシートNoから担当者Noまでが求められるのはいいですが、そこからさらに担当者名まで求められてしまうのは情報が重複されて保存されている証拠という具合です。
****第三正規形
ここまではうまくいきました。しかし、これまでのやり方では、ご飯物、麺類といった種別ごとの集計ができないことに気がつきました。また入力も面倒だし、例えば、商品ごとの売上げ数などを調査するのに、少しでも商品名を間違って入力したりして、例えば、味噌ラーメンを"みそラーメン"と間違って入力してしまうと検索対象外になってしまい問題です。
そこで店長は、さらにテーブルを作り商品は商品ごとにまとめて登録するし、種別や分類も入力できるようにして、番号を入力するようにすればいいのではないかと考えました。
そして作ったのが以下のテーブルです。
■ レシートテーブル 担当者を別テーブルに分離（第三正規形）
|レシートNo|日付|担当者No|
|1|2013/05/20|1|
|2|2013/05/20|2|
|3|2013/05/21|1|
|4|2013/05/21|1|
|5|2013/05/21|2|
■ レシート詳細テーブル 商品を別テーブルに分離（第三正規形）
|レシートNo|商品No|注文数|
|1|1|1|
|1|6|1|
|1|4|1|
|2|2|1|
|2|6|1|
|3|1|2|
|3|4|2|
|4|1|1|
|4|5|1|
|5|3|1|
|5|6|1|
■ 担当者テーブル 担当者名を別テーブルに分離（第三正規形）
|担当者No|担当者名|
|1|鈴木|
|2|佐藤|
■ 商品テーブル 商品名、分類、価格を別テーブルに分離（第三正規形）
|商品No|商品名|分類|価格|
|1|醤油ラーメン|麺|600|
|2|味噌ラーメン|麺|600|
|3|野菜ラーメン|麺|600|
|4|ライス|飯|250|
|5|チャーハン|飯|400|
|6|餃子|おかず|350|
店長は担当者についても別のテーブルにしてみました。こうすることによって担当者名を手入力しなくても数字で済むし、担当者ごとの集計などもできると考えたからです。
これで概ね目標に沿ったテーブルを作ることができました。
では、これを元に実際にテーブルを作ってみることにします。
※これはあくまで簡単に説明するためのモデルで確実にこの方法が正しいというわけではありません
#divclass(h6){第三正規形に対する考え方}
まず第三正規形までしか考えていないからといって、ボイスコッド正規形や、第四、第五正規形になっていないというわけではありません。中には第一正規形にしたら自動的に第三正規形を満たしていたということも多いはずです。実務上、普通につくるとよほどへんなデータ構造でない限り第三正規形まで実現すると第五正規形を満たしていることがほとんどです。逆に違和感を感じると第三正規形以上に正規化できることがほとんどなので、業務上ではそれほど気にするようなことはありません。
第三正規形でのポイントは「マスタ」の概念を理解すると、より理解が深まると思います。例えば、商品名や担当者名をいちいちその場で入力するのは時間がかかりますし、入力にミスをする可能性があります。それだと問題ですよね。なので、商品に関する情報は商品テーブルとしてあらかじめマスタ化しておけば、商品Noを入力するだけで済むわけです。
スマホや携帯で電話（アドレス）帳を使うケースがありますよね。あれがマスタ化です。例えば友達などであれば、友達の名前で登録して、そこにメアドや電話番号を登録します。電話をかけるときは名前から検索して電話をかけることができます。データベースの世界でも同様です。いちいち電話番号を入力するのは面倒ですし、そもそも電話番号をわすれてしまいますし、押し間違えによる間違い電話もなくなるわけです。
今回の場合は商品テーブルと担当者テーブルを作っておき、スマホの電話帳のように事前に登録しておけば選択肢の中から簡単に選択して、押し間違えやご入力もなくなるということです。この「マスタ化」を頭に思い浮かべることができれば、事前に担当者と担当者名、商品名と商品価格などは「マスタ化」したほうが便利だと想像できるわけです。これを意識すると最初から第三正規形されたテーブル構造を作ることができるようになるわけです。
***データベースの操作入門 ～SQ文（クエリ）のお話～
****準備
テーブルを作る前に、データベースに新しいデータベースを登録する必要があります。以下の通りに作成します。
-プログラムからMicrosoft SQL Server Management Studio（MSSMS）を起動しログインします
-ツリーからデータベースを右クリックし新しいデータベースをクリックします
#center{&image(mssms01.jpg)}
-データベース名にRamenDBと入力しOKボタンをクリックします
-するとツリーのデータベースフォルダにRamenDBが作成されます
#center{&image(mssms02.jpg)}
これで新しいデータベースの完成です
ついでにクエリの入力の仕方も勉強します
-RamenDBフォルダをクリックして、ツールバーの新しいクエリをクリックします。すると右側にクエリを入力するエディタが表示されます
-オブジェクトエクスプローラの上部にあるコンボボックスにRamenDBと表示されていることを確かめてください。ここで表示されているデータベースを操作することになります。なんどクエリを実行して、どうみても正しいはずなのに動作しないなんてときは、違うデータベースに更新かけていることがあったりしますのでクエリを記述するときは必ず確認してください。
#center{&image(mssms03.jpg)}
-試しにクエリを入力して実行してみます。画像のようにクエリを入力し、ツールバーの「!実行」をクリックします。ツリーを右クリックして「最新の情報に更新」をクリックするとRamenDB-テーブルに「dbo.担当者」が作成されているのがわかります。
#center{&image(mssms04.jpg)}
-dbo.担当者を右クリックしてデザインを実行すると、今度は画面右側に作成されたテーブルの内容が表示されているのがわかります。実はこれはテーブルを作るクエリでした。
#center{&image(mssms05.jpg)}
****テーブルの作成
さて、テーブルの作りましょう。すでにクエリの入力の仕方と実行の仕方を学びましたので、あとは各テーブルを作成するクエリを作成するだけです。
まだ最初なので、テーブルを作成するクエリを全部書いておきますね。担当者のテーブルはすでに出来ていると思いますので外しておきます。
>Create Table レシート
>(
>レシートNo int,
>日付 datetime,
>担当者No int
>)
>Create Table レシート詳細
>(
>レシートNo int,
>商品No int,
>注文数 int
>)
>Create Table 商品
>(
>商品No int,
>商品名 nvarchar(50),
>分類 nvarchar(50),
>価格 int
>)
各クエリの説明はこんな感じです。
例えば商品テープルの場合、「商品」でテーブルを作りなさい。列は「商品No」を整数型で、「商品名」を文字列型で、「分類」を文字列型で、「価格」を整数型で作ってね。という感じです。クエリは理屈じゃないので覚えるしかありません。ちなみにここで使われているintは整数型、nvarchar(50)はユニコードの文字列で50文字まで、日付のdatetimeです。他にもいろいろあるのですが、それらは参考書などに掲載されているので調べてみてください。
****テーブルの削除
次はテーブルの削除です。テーブル名「テスト」で適当にテーブルを作って削除してみます。
削除はこれだけです。簡単です。削除したり追加したら、ツリーを更新するのを忘れないようにしましょう。
>drop table テスト
****データの入力
次はテーブルへのデータの入力です。第三正規形の項目で作成したようにデータを入力していきます。
例えば、担当者テーブルへデータを挿入するクエリは以下の通りです。担当者Noは数値なのでそのまま、担当者名は文字列(nvarchar)なので'（シングルクォーテーション）でくくる必要があります。
>insert into 担当者
>(担当者No,担当者名)
>values
>(1,'鈴木')
レシートテーブルへ入力するクエリはこんな感じです。
>insert into レシート
>(レシートNo,日付,担当者No)
>values
>&s(){(1,2013/05/20,1)}
>(1,'2013/05/20',1)
&s(){日付型（datetime）は'でくくる必要はありません。}(2015/11/06 日付型はシングルクォーテーションで囲む必要がありました。お詫びして訂正いたします。)
それでは、各データをインストールするクエリを作成してデータをそれぞれ入力してみてください。クエリをコピペしてデータや列名だけを変更するのではなく、すべて消去してソラで手書きできるようにしてくださいね。
入力し終わったら入力されたデータを確認します。テーブル名を選択して右クリックし、上位200行の編集を実行してみてください。以下のように表示されていれば完璧です。
クエリの意味はみたままですが、「担当者」テーブルに、担当者Noを1、担当者名を鈴木で1行のデータを入力するという意味になります。
#center{&image(mssms06.jpg)}
****データの更新
次はデータの更新です。すでに入力された内容を訂正する場合に利用します。実際のクエリはこのようになります。
>update 商品
>set 商品名='ご飯'
>where 商品No=4
クエリは、「商品」テーブルの、商品Noが「4」の行の「商品名」のフィールドを「ご飯」に変更するという意味になります。where以下の条件で指定した行の特定の項目を更新するというイメージになります。項目はカンマ区切りで複数項目の更新も可能です。where文の条件で複数行が対象となる場合は、複数行を同時に更新することになります。このあたりも参考書をみながら実際に確認してみてください。
ここでポイントなのはwhere文ですね。where文で行を特定するところがポイントです。例えばこんなクエリを実行すると面白い更新をすることができます。実際に変化を調べてみてください。
>update 商品 set
>分類='ご飯物'
>where 分類='飯'
****データの削除
では次はデータの削除です。行そのものが不必要になったときに削除します。
>delete from 商品
>where 商品No=6
クエリの意味はそのままです。「商品」テーブルの商品Noが6の行を削除するという意味です。餃子のデータは必要なので削除したら、またinsert文で商品No6のデータを追加しておきましょう。
****データの抽出
*****SELECT文
さて、次はいよいよデータの抽出です。まずは単純なデータの表示です。
>select * from 商品
単純に表示させるのはこれだけです。「商品」テーブルの全ての列のデータを表示するクエリです。※はすべての列のデータを表示するという意味になります。特定の列のデータのみ表示したい場合は、などのように列名を記述します。実際にどのように表示されるか実験してみましょう。
>select 商品名 from 商品
*****WHERE句
次は条件を指定したデータの抽出です。すでにupdateやdeleteなどでwhere文がでてきていますが、それと同じです。whereで指定した条件に合致する行が表示されます。最も簡単な例はこんな感じです。
>select * from 商品 where 価格=600
クエリの意味は、「商品」テーブルの価格が600円の行を抽出して表示するです。条件は=だけでなく不等号や複数条件の指定も利用できます。例えば、こんなクエリも実行可能です。実際に入力して結果を調べてみてください。
>select * from 商品 where 価格>=400
>select * from 商品 where 分類='ご飯物' and 価格>300
>select * from 商品 where 分類='ご飯物' or 価格>500
*****ORDER BY句
次は並び替えの条件を指定したデータの抽出です。例えば価格の高い準に並べたデータを抽出します
>select * from 商品 order by 価格 desc
クエリの意味は行を「価格」の値をdesc（降順、大きい物順）で並びなさいという意味。価格の安い順はこんな感じです。
>select * from 商品 order by 価格
*****INNER JOIN句
さて、いよいよここからが本番です。都合で商品、担当者テーブルを別テーブルとして設計したため、レシートやレシート詳細に担当者名や商品名が表示されず、番号のみが表示されている状態でした。いよいよ、複数のテーブルを結合して同じ表のように表示させます。
まずはレシート詳細と商品を結合してみましょう。
>select * from レシート詳細 inner join 商品
>on レシート詳細.商品No = 商品.商品No
いろんな書き方がありますがオーソドックスな方法で。これはレシート詳細と商品を内部結合しなさいというクエリです。条件はレシート詳細テーブルの商品Noと商品テーブルの商品Noが同じものを結合するということですね。実際に実行して結果を調べてみてください。
また同じように、レシートテーブルと担当者テーブル、レシートテーブルとレシート詳細テーブルを内部結合させるクエリを自分で書いてみてください。
ちなみに、すべてのテーブルを結合させるとこんな感じになります。これで正規化する前に作ったエクセルのような表ができあがりました。
>select * from レシート
>inner join 担当者
>on レシート.担当者No = 担当者.担当者No
>inner join レシート詳細
>on レシート.レシートNo = レシート詳細.レシートNo
>inner join 商品
>on レシート詳細.商品No = 商品.商品No
#center{&image(mssms07.jpg)}
*****COUNT関数
さてここから面倒臭くなってきます。次はCOUNT関数です。これは行数を計算するクエリです。
例えば、担当者別にレジ打ちをしたレシート数を計算するクエリを作ってみます。例えば、鈴木さんが何枚、佐藤さんが何枚のレシートを発行したか？ということですね。まずは内部結合して一覧を表示してみます。
>select * from レシート inner join 担当者 on レシート.担当者No = 担当者.担当者No
まずは、これを担当者別にまとめてみます。鈴木さんを鈴木さん、佐藤さんを佐藤さんの行だけまとめて表示します。
>select 担当者.担当者名 from レシート inner join 担当者 on レシート.担当者No = 担当者.担当者No
>group by 担当者.担当者名
これをグループ化といいます。グループ化ではgroup byで記述した列のデータでひとまとめにして表示します。ここでselect句に*（アスタリスク）は使用できません。アスタリスクにすると異なるデータの入る列があってグループ化できないのでエラーになってしまいます。ここは担当者名でグループ化するのでselectでは担当者テーブルの担当者名だけを表示させるよう記述します。実行すると佐藤さんと鈴木さんでグループされたのがわかると思います。そして、それぞれの数をカウントするようにしてみましょう。
>select count(担当者.担当者名), 担当者.担当者名 from レシート inner join 担当者 on レシート.担当者No = 担当者.担当者No
>group by 担当者.担当者名
担当者テーブルの担当者名をでまとめられた行数をcount関数で集計します。ついでに担当者名も表示させておきます。これで佐藤さんが2枚、鈴木さんが3枚のレシートを発行したのがわかります。
*****SUM関数
次は各レシートごとの合計金額を計算してみましょう。まずは商品詳細テーブルと商品テーブルの結合です。ここまでは簡単ですね。
>select * from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
先ほどは担当者名の回数を数えるために担当者でグループ化しました。今度はレシートごとの合計金額を集計したいのでレシートNoでグループ化します。
>select レシートNo from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
グループ化では異なるデータのものは表示できないのでselectの指定をすることを忘れないようにしましょう。
いよいよ金額の合計です。先ほどは担当者名をcount関数で計算して回数を集計しました。今度は価格を足して行けばいいので価格をsum関数で合計値を集計すればいいはずです。
>select レシートNo,sum(価格) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
これで合計っぽいものができましたが、実際にはこれは間違いです。レシート詳細テーブルをよくみると注文数という項目がありますよね？つまり、商品価格×注文数を合計しなければなりません。なので実際の正解は以下のクエリになります。
>select レシートNo,sum(価格*注文数) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
このクエリはレシートNoでグループ化するので、そのグループ化した行のデータで価格×注文数の合計を計算しなさいということを表しています。
*****MAX関数、MIN関数
次は、それぞれのレシートの中で最も価格の安い商品と、最も価格の高い商品を抽出してみます。ここはcountなどと同じですので、軽く流していきます。
>select レシートNo,max(価格) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
>select レシートNo,min(価格) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
#divclass(h5){実践テスト}
実際に下記のような表示をするクエリを作ってみましょう。指定されていないものはorder byは必要ありません。従ってorder byが指定されていない場合は表示順がおかしな場合があります。
#divclass(h6){日付別の売上げ集計}
#center{&image(mssms08.jpg)}
#co(){
select 日付,sum(価格*注文数) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 日付
}
#divclass(h6){商品ごとの販売数合計}
#center{&image(mssms09.jpg)}
#co(){
select 商品名,sum(注文数) as 販売数 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 商品名
}
#divclass(h6){日付別商品別の売上げ集計}
#center{&image(mssms10.jpg)}
#co(){
select 日付,商品名,sum(注文数*価格) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 商品名,日付
}
#divclass(h6){分類別の販売数、売上げ集計}
#center{&image(mssms11.jpg)}
#co(){
select 分類,sum(注文数*価格) as 金額,sum(注文数) as 注文数 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 分類
}
#divclass(h6){ご飯物を頼んだレシートのご飯物だけの金額の合計}
#center{&image(mssms12.jpg)}
#co(){
select レシート.レシートNo as レシートNo, sum(注文数) as 注文数,sum(価格*注文数) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
where 分類='ご飯物'
group by レシート.レシートNo
}
#divclass(h6){1000円以上注文したレシートのみの抽出}
#center{&image(mssms13.jpg)}
※ヒント ここで取り上げていないhaving句を使用しています。気になったら勉強してみてくださいな。
#co(){
select レシート.レシートNo as レシートNo, sum(価格*注文数) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by レシート.レシートNo
having sum(価格*注文数) > 1000}
#divclass(h5){後書き}
#divclass(h6){おわりに}
これでひとまずおしまいです。簡単なクエリのみ取り上げましたが、ほとんどはここに記述されている内容で解答できるのではないかと思います。他に出そうなものとしては、like、distinct、exists、union、right join、having、副問い合わせあたりでしょうか？
ここまでくれば、同様にテストすることができますので、ネットの情報や参考書をみながらテストしてみるといいと思います。
#divclass(h6){SQLお勧め参考書}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4774138355" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4798118818" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4774144916" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4822296210" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
**コメントを残したい
#divclass(i10)
{
- datetimeで数時間悩んだ……記述ミスなのかわざとなのか -- t (2015-11-03 21:29:00)
- ご迷惑をおかけして申し訳ありません。どこがどのようにおかしかったのでしょうか？訂正させていただきたく思います。よろしくお願い申し上げます。 -- 管理人 (2015-11-04 19:46:12)
- (1,2013/05/20,1) -- 名無しさん (2015-11-05 23:33:32)
- データの入力で(1,2013/05/20,1)と記載されていますがシングルクォーテーションでくくらないと正しく設定されませんでした。--tさんも同じ指摘ですかね。 -- 名無しさん (2015-11-05 23:36:54)
- ご指摘ありがとうございました。すべてクエリは実際の動作を確認したものを掲載していたのですが、漏れがあったようです。シングルクォーテーションが必要だった旨訂正させていただきました。ご指摘ありがとうございました。 -- 管理人 (2015-11-06 18:07:59)
- 管理人さん訂正ありがとうございました。名無しさんもありがとうございました。入門者なのでとても助かってます -- t (2015-11-08 20:25:08)
#comment()
}
#right(){&link_anchor(top){▲}&link_anchor(bottom){▼}}
#javascript(){{
<script>
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','//www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-50556855-2', 'auto');
ga('send', 'pageview');
</script>
}}
&topicpath()&aname(top)
#divclass(pageTitle){応用情報技術者試験向けデータベース入門}
***初めに
****初めに
基本情報技術者試験、応用情報技術者試験の受験者向けのデータベース学習の自習書です
実際にデータベースを作ったり、SQL文を入力することで仕組みを勉強することを目的としています
主に環境作りと簡単な説明をすることを目的としていますので、しっかりとした解説は参考書等をご覧ください
****環境について
*****必要なソフト
-Microsoft SQL Server 2012 Express With Tools
*****ダウンロード、インストール方法
-Microsoft SQL Server 2012 Express With Toolsをダウンロード
--http://www.microsoft.com/ja-jp/download/details.aspx?id=29062
--64bit版のWindowsを利用している人は、JPN\x64\SQLEXPRWT_x64_JPN.exeをダウンロード
--32bit版のWindowsを利用している人は、JPN\x86\SQLEXPRWT_x86_JPN.exeをダウンロード
-インストール方法
--ダウンロードしたexeをダブルクリックする
--管理者による実行の確認画面が出るのでokボタンをクリックする
--新規スタンドアロンインストールを選択
--ライセンスに同意して次へ
--「更新プログラムを含める」をチェックされているのを確認して次へ
--セットアップウィンドウが立ち上がる
--機能の選択はそのまま次へ
--インスタンスの構成はそのまま次へ
--サーバの構成はそのまま次へ
--データベースエンジンの構成はそのまま次へ
--エラーレポートはそのまま次へ
--インストールの終了
***イントロダクション ～あるラーメン屋さんのお話～
****初めに
主人公はラーメン屋さんの店長です。
これまで店長は、一日の売り上げや、商品ごとの販売数、麺類、ご飯ものなど種類別の販売数などを電卓で計算していました。しかしとても時間がかかるためデータベースを活用することで、もっと簡単に集計できるようにしたいと思い始めました。
そこで店長はデータベースの仕組みや使い方について勉強を始めようと思い立ちました。
****入力するデータについて
普段、実際には以下のようなレシートを発行しています。店長はこのデータを利用して実際にデータベースを作れないかを考えました。
|&image(http://cdn47.atwikiimg.com/matowiki/?cmd=upload&act=open&pageid=101&file=r01.jpg)|&image(r02.jpg)|&image(r03.jpg)|
|&image(r04.jpg)|&image(r05.jpg)||
普通によくみるレシートです。記入されている項目を上から順番に説明します。
-レシート番号。ユニークな番号（重複しない数字）で、レシートを発行するたびに数字は増えていきます
-日付。レシートを発行した日付が表示されています
-担当者名。レジを入力した従業員の担当者番号と名前が表示されています
-料理。注文した料理の名前、注文数、合計価格（単品価格×注文数）が表示されています
-支払い合計。すべての合計金額です
***テーブルの作り方入門 ～データベースの正規化のお話～
****適当に作る
店長は、まず適当に作ってみることにしました
■ 適当に作成したテーブル
|レシートNo|日付|担当No|担当者名|商品名|注文数|価格|商品名|注文数|価格|商品名|注文数|価格|
|1|2013/05/20|1|鈴木|醤油ラーメン|1|600|餃子|1|350|ライス|1|250|
|2|2013/05/20|2|佐藤|味噌ラーメン|1|600|餃子|1|350||||
|3|2013/05/21|1|鈴木|醤油ラーメン|2|600|ライス|2|250||||
|4|2013/05/21|1|鈴木|醤油ラーメン|1|600|チャーハン|1|400||||
|5|2013/05/21|2|佐藤|野菜ラーメン|1|600|餃子|1|350||||
それが、このテーブルです。価格は単価（1品あたりの価格）を入力することにしました。注文数があるので、単価×注文数で商品ごとの合計金額は計算すれば表示できるのではないかと考えたのです。
そして、レシート全体の合計金額も入力しないようにしようと考えました。単価×注文数の金額をすべて足していけばレシート全体の合計金額が計算できると考えたのです。
****第一正規形
しかし、このテーブルだと問題がでてきてしまいます。
-問題1 このテーブルでは商品を最高3種類までしか入力できません。4種類以上の注文を想定して横に長くする必要がでてきてしまいます
-問題2 仮に一枚のレシートで10種類まで入力できるよう表を横に長くすると、今度は1種類しか頼まないお客さんがいた場合、入力されていないスペースが無駄になってしまいます
-問題3 現実的にあり得ないかもしれないけど、10種類以上頼むお客さんがいたら入力できなくなってしまいます
問題2と問題3は相反する問題で解決ができませんね。
そこで店長は考えました。同じレシートNoの重複を許して縦に長い表を作れば、商品を何種類も入力できるから便利なのでは？
そこで作成したのが以下のテーブルです。
■ 商品を何個でも登録できるようにしたテーブル（第一正規形）
|レシートNo|日付|担当者No|担当者名|商品名|注文数|価格|
|1|2013/05/20|1|鈴木|醤油ラーメン|1|600|
|1|2013/05/20|1|鈴木|餃子|1|350|
|1|2013/05/20|1|鈴木|ライス|1|250|
|2|2013/05/20|2|佐藤|味噌ラーメン|1|600|
|2|2013/05/20|2|佐藤|餃子|1|350|
|3|2013/05/21|1|鈴木|醤油ラーメン|2|600|
|3|2013/05/21|1|鈴木|ライス|2|250|
|4|2013/05/21|1|鈴木|醤油ラーメン|1|600|
|4|2013/05/21|1|鈴木|チャーハン|1|400|
|5|2013/05/21|2|佐藤|野菜ラーメン|1|600|
|5|2013/05/21|2|佐藤|餃子|1|350|
このようにすれば、1品だったら1行ですし、何種類もの商品を頼まれても縦に長くなるだけなので、何種類でも登録することができます。
これが第一正規形の姿です。
#divclass(h6){第一正規形ではなぜいけないのか}
上記のような第一正規形がなぜいけないのかというと、基本的には保存する情報が重複して登録されているからです。また、あらかじめ担当者名をマスタ登録できないなどの問題もありますね。
上記の第一正規形の「候補キー」はどの属性でしょうか？候補キーというのは、ある「行」を特定するために必要な属性、またはその組み合わせということになります。上記の例ではレシートNoだけでは行を特定することはできません。従ってもう一つの属性が必要です。というわけで行をみると特定にできそうなのは「商品名」ですね。このテーブルの候補キーは｛レシートNo、商品名｝ということになるわけです。
また、これらの情報のうち、日付、担当者名、担当者Noは重複して保存されています。レシートに対して日付と担当者が同じなのは、普通の飲食店なら当たり前のことですね。ですから、わざわざ日付、担当者などは重複して保存する必要はありません。しかし実際には、日付と担当者が重複で登録されているからなんとかしたいわけですね。
第一正規形である理由として「すべての属性が単一値で、候補キーＡ、Ｂの一部であるＢに非キー属性のＣが部分関数従属するため」という説明があります。これを今回に当てはめて説明すると「すべての属性が単一値で、候補キーレシートNo、商品名の一部であるレシートNoに非キー属性である日付（または担当者No、担当者名）が部分関数従属するから」第一正規形であるわけです。
候補キーの一部であるレシートNoがわかれば、日付、担当者は特定できてしまいますので、第一正規形だというわけでずね。
****第二正規形
ここまではうまくできました。しかし、いろいろ考えるとそれでもいくつかの問題がでてきてしまいました。
-問題1 それでもレシートNo、日付、担当者No、担当者名が重複しているので余分なスペースが必要になってします
-問題2 日付や担当者名の修正をしたとき、関連するすべての行を修正しなくてはならくなってしまいます
関連するすべてのテーブルを修正しなければならないので非常に面倒ですね。そこで店長はテーブルを二つに分離すれば面倒がなくなるのではないかと考えました。
そして作成したのが以下のテーブルです。
■ レシートテーブル レシートのデータと商品のデータを分離（第二正規形）
|レシートNo|日付|担当者No|担当者名|
|1|2013/05/20|1|鈴木|
|2|2013/05/20|2|佐藤|
|3|2013/05/21|1|鈴木|
|4|2013/05/21|1|鈴木|
|5|2013/05/21|2|佐藤|
■ レシート詳細テーブル レシートのデータと商品のデータを分離（第二正規形）
|レシートNo|商品名|注文数|価格|
|1|醤油ラーメン|1|600|
|1|餃子|1|350|
|1|ライス|1|250|
|2|味噌ラーメン|1|600|
|2|餃子|1|350|
|3|醤油ラーメン|2|600|
|3|ライス|2|250|
|4|醤油ラーメン|1|600|
|4|チャーハン|1|400|
|5|野菜ラーメン|1|600|
|5|餃子|1|350|
こうして、レシートテーブルからレシートNo1のものを表示するとき、レシート詳細テーブルから該当する料理を持ってくれば済みます。そして重複データは最低限だけで、すっきりさせることができました。データのレシートの日付や担当者の修正も1行で済むため最低限で済みますね。
#divclass(h6){第二正規形ではなぜいけないのか}
第二正規形になると一見よさそうにみえますが、それでも問題が残っています。レシートテーブルの担当者名と担当者Noの関係です。レシートテーブルを縦に眺めてみます。そうすると、担当者Noが1で、担当者名が鈴木さんという情報が複数でてきます。これはやはりデータとして冗長になってしまっています。担当者Noが1なら鈴木さんなのは当たり前ですので、それぞれ保存すると容量の無駄になりますし、例えば氏名が変わったときに、過去の担当者名をすべて変更するというのは効率が悪いですね。また名前を入力ミスすると鈴木さんが須々木になったりして統一がとれなくなってしまいます。
レシートテーブルでの候補キーは「レシートNo」です。第二正規形にしたことで候補キーが複合キーではなく単一の属性のキーになっているのがわかります。そして今回は担当者Noと担当者名の関係が問題になっています。担当者Noがわかれば担当者名は一意に求められますので、そこが問題です。そして、担当者Noは候補キーではありません。
第二正規形であることの理由として「すべての属性が単一値で、候補キーからの部分関数従属がなく、推移的関数従属性Ａ→Ｂ→Ｃがあるため」というものがあります。これを今回のレシートテーブルに当てはめてみますと「すべての属性が単一値で、レシートNoからの部分関数従属がなく、推移的関数従属制レシートNo→担当者No→担当者名があるため」第二正規形となっていることがわかります。Ａ→Ｂ→ＣというのはレシートNoから担当者Noが求められ、さらに担当者Noから担当者名が求められるのはおかしいということですね。レシートNoから担当者Noまでが求められるのはいいですが、そこからさらに担当者名まで求められてしまうのは情報が重複されて保存されている証拠という具合です。
****第三正規形
ここまではうまくいきました。しかし、これまでのやり方では、ご飯物、麺類といった種別ごとの集計ができないことに気がつきました。また入力も面倒だし、例えば、商品ごとの売上げ数などを調査するのに、少しでも商品名を間違って入力したりして、例えば、味噌ラーメンを"みそラーメン"と間違って入力してしまうと検索対象外になってしまい問題です。
そこで店長は、さらにテーブルを作り商品は商品ごとにまとめて登録するし、種別や分類も入力できるようにして、番号を入力するようにすればいいのではないかと考えました。
そして作ったのが以下のテーブルです。
■ レシートテーブル 担当者を別テーブルに分離（第三正規形）
|レシートNo|日付|担当者No|
|1|2013/05/20|1|
|2|2013/05/20|2|
|3|2013/05/21|1|
|4|2013/05/21|1|
|5|2013/05/21|2|
■ レシート詳細テーブル 商品を別テーブルに分離（第三正規形）
|レシートNo|商品No|注文数|
|1|1|1|
|1|6|1|
|1|4|1|
|2|2|1|
|2|6|1|
|3|1|2|
|3|4|2|
|4|1|1|
|4|5|1|
|5|3|1|
|5|6|1|
■ 担当者テーブル 担当者名を別テーブルに分離（第三正規形）
|担当者No|担当者名|
|1|鈴木|
|2|佐藤|
■ 商品テーブル 商品名、分類、価格を別テーブルに分離（第三正規形）
|商品No|商品名|分類|価格|
|1|醤油ラーメン|麺|600|
|2|味噌ラーメン|麺|600|
|3|野菜ラーメン|麺|600|
|4|ライス|飯|250|
|5|チャーハン|飯|400|
|6|餃子|おかず|350|
店長は担当者についても別のテーブルにしてみました。こうすることによって担当者名を手入力しなくても数字で済むし、担当者ごとの集計などもできると考えたからです。
これで概ね目標に沿ったテーブルを作ることができました。
では、これを元に実際にテーブルを作ってみることにします。
※これはあくまで簡単に説明するためのモデルで確実にこの方法が正しいというわけではありません
#divclass(h6){第三正規形に対する考え方}
まず第三正規形までしか考えていないからといって、ボイスコッド正規形や、第四、第五正規形になっていないというわけではありません。中には第一正規形にしたら自動的に第三正規形を満たしていたということも多いはずです。実務上、普通につくるとよほどへんなデータ構造でない限り第三正規形まで実現すると第五正規形を満たしていることがほとんどです。逆に違和感を感じると第三正規形以上に正規化できることがほとんどなので、業務上ではそれほど気にするようなことはありません。
第三正規形でのポイントは「マスタ」の概念を理解すると、より理解が深まると思います。例えば、商品名や担当者名をいちいちその場で入力するのは時間がかかりますし、入力にミスをする可能性があります。それだと問題ですよね。なので、商品に関する情報は商品テーブルとしてあらかじめマスタ化しておけば、商品Noを入力するだけで済むわけです。
スマホや携帯で電話（アドレス）帳を使うケースがありますよね。あれがマスタ化です。例えば友達などであれば、友達の名前で登録して、そこにメアドや電話番号を登録します。電話をかけるときは名前から検索して電話をかけることができます。データベースの世界でも同様です。いちいち電話番号を入力するのは面倒ですし、そもそも電話番号をわすれてしまいますし、押し間違えによる間違い電話もなくなるわけです。
今回の場合は商品テーブルと担当者テーブルを作っておき、スマホの電話帳のように事前に登録しておけば選択肢の中から簡単に選択して、押し間違えやご入力もなくなるということです。この「マスタ化」を頭に思い浮かべることができれば、事前に担当者と担当者名、商品名と商品価格などは「マスタ化」したほうが便利だと想像できるわけです。これを意識すると最初から第三正規形されたテーブル構造を作ることができるようになるわけです。
***データベースの操作入門 ～SQ文（クエリ）のお話～
****準備
テーブルを作る前に、データベースに新しいデータベースを登録する必要があります。以下の通りに作成します。
-プログラムからMicrosoft SQL Server Management Studio（MSSMS）を起動しログインします
-ツリーからデータベースを右クリックし新しいデータベースをクリックします
#center{&image(mssms01.jpg)}
-データベース名にRamenDBと入力しOKボタンをクリックします
-するとツリーのデータベースフォルダにRamenDBが作成されます
#center{&image(mssms02.jpg)}
これで新しいデータベースの完成です
ついでにクエリの入力の仕方も勉強します
-RamenDBフォルダをクリックして、ツールバーの新しいクエリをクリックします。すると右側にクエリを入力するエディタが表示されます
-オブジェクトエクスプローラの上部にあるコンボボックスにRamenDBと表示されていることを確かめてください。ここで表示されているデータベースを操作することになります。なんどクエリを実行して、どうみても正しいはずなのに動作しないなんてときは、違うデータベースに更新かけていることがあったりしますのでクエリを記述するときは必ず確認してください。
#center{&image(mssms03.jpg)}
-試しにクエリを入力して実行してみます。画像のようにクエリを入力し、ツールバーの「!実行」をクリックします。ツリーを右クリックして「最新の情報に更新」をクリックするとRamenDB-テーブルに「dbo.担当者」が作成されているのがわかります。
#center{&image(mssms04.jpg)}
-dbo.担当者を右クリックしてデザインを実行すると、今度は画面右側に作成されたテーブルの内容が表示されているのがわかります。実はこれはテーブルを作るクエリでした。
#center{&image(mssms05.jpg)}
****テーブルの作成
さて、テーブルの作りましょう。すでにクエリの入力の仕方と実行の仕方を学びましたので、あとは各テーブルを作成するクエリを作成するだけです。
まだ最初なので、テーブルを作成するクエリを全部書いておきますね。担当者のテーブルはすでに出来ていると思いますので外しておきます。
>Create Table レシート
>(
>レシートNo int,
>日付 datetime,
>担当者No int
>)
>Create Table レシート詳細
>(
>レシートNo int,
>商品No int,
>注文数 int
>)
>Create Table 商品
>(
>商品No int,
>商品名 nvarchar(50),
>分類 nvarchar(50),
>価格 int
>)
各クエリの説明はこんな感じです。
例えば商品テープルの場合、「商品」でテーブルを作りなさい。列は「商品No」を整数型で、「商品名」を文字列型で、「分類」を文字列型で、「価格」を整数型で作ってね。という感じです。クエリは理屈じゃないので覚えるしかありません。ちなみにここで使われているintは整数型、nvarchar(50)はユニコードの文字列で50文字まで、日付のdatetimeです。他にもいろいろあるのですが、それらは参考書などに掲載されているので調べてみてください。
****テーブルの削除
次はテーブルの削除です。テーブル名「テスト」で適当にテーブルを作って削除してみます。
削除はこれだけです。簡単です。削除したり追加したら、ツリーを更新するのを忘れないようにしましょう。
>drop table テスト
****データの入力
次はテーブルへのデータの入力です。第三正規形の項目で作成したようにデータを入力していきます。
例えば、担当者テーブルへデータを挿入するクエリは以下の通りです。担当者Noは数値なのでそのまま、担当者名は文字列(nvarchar)なので'（シングルクォーテーション）でくくる必要があります。
>insert into 担当者
>(担当者No,担当者名)
>values
>(1,'鈴木')
レシートテーブルへ入力するクエリはこんな感じです。
>insert into レシート
>(レシートNo,日付,担当者No)
>values
>&s(){(1,2013/05/20,1)}
>(1,'2013/05/20',1)
&s(){日付型（datetime）は'でくくる必要はありません。}(2015/11/06 日付型はシングルクォーテーションで囲む必要がありました。お詫びして訂正いたします。)
それでは、各データをインストールするクエリを作成してデータをそれぞれ入力してみてください。クエリをコピペしてデータや列名だけを変更するのではなく、すべて消去してソラで手書きできるようにしてくださいね。
入力し終わったら入力されたデータを確認します。テーブル名を選択して右クリックし、上位200行の編集を実行してみてください。以下のように表示されていれば完璧です。
クエリの意味はみたままですが、「担当者」テーブルに、担当者Noを1、担当者名を鈴木で1行のデータを入力するという意味になります。
#center{&image(mssms06.jpg)}
****データの更新
次はデータの更新です。すでに入力された内容を訂正する場合に利用します。実際のクエリはこのようになります。
>update 商品
>set 商品名='ご飯'
>where 商品No=4
クエリは、「商品」テーブルの、商品Noが「4」の行の「商品名」のフィールドを「ご飯」に変更するという意味になります。where以下の条件で指定した行の特定の項目を更新するというイメージになります。項目はカンマ区切りで複数項目の更新も可能です。where文の条件で複数行が対象となる場合は、複数行を同時に更新することになります。このあたりも参考書をみながら実際に確認してみてください。
ここでポイントなのはwhere文ですね。where文で行を特定するところがポイントです。例えばこんなクエリを実行すると面白い更新をすることができます。実際に変化を調べてみてください。
>update 商品 set
>分類='ご飯物'
>where 分類='飯'
****データの削除
では次はデータの削除です。行そのものが不必要になったときに削除します。
>delete from 商品
>where 商品No=6
クエリの意味はそのままです。「商品」テーブルの商品Noが6の行を削除するという意味です。餃子のデータは必要なので削除したら、またinsert文で商品No6のデータを追加しておきましょう。
****データの抽出
*****SELECT文
さて、次はいよいよデータの抽出です。まずは単純なデータの表示です。
>select * from 商品
単純に表示させるのはこれだけです。「商品」テーブルの全ての列のデータを表示するクエリです。※はすべての列のデータを表示するという意味になります。特定の列のデータのみ表示したい場合は、などのように列名を記述します。実際にどのように表示されるか実験してみましょう。
>select 商品名 from 商品
*****WHERE句
次は条件を指定したデータの抽出です。すでにupdateやdeleteなどでwhere文がでてきていますが、それと同じです。whereで指定した条件に合致する行が表示されます。最も簡単な例はこんな感じです。
>select * from 商品 where 価格=600
クエリの意味は、「商品」テーブルの価格が600円の行を抽出して表示するです。条件は=だけでなく不等号や複数条件の指定も利用できます。例えば、こんなクエリも実行可能です。実際に入力して結果を調べてみてください。
>select * from 商品 where 価格>=400
>select * from 商品 where 分類='ご飯物' and 価格>300
>select * from 商品 where 分類='ご飯物' or 価格>500
*****ORDER BY句
次は並び替えの条件を指定したデータの抽出です。例えば価格の高い準に並べたデータを抽出します
>select * from 商品 order by 価格 desc
クエリの意味は行を「価格」の値をdesc（降順、大きい物順）で並びなさいという意味。価格の安い順はこんな感じです。
>select * from 商品 order by 価格
*****INNER JOIN句
さて、いよいよここからが本番です。都合で商品、担当者テーブルを別テーブルとして設計したため、レシートやレシート詳細に担当者名や商品名が表示されず、番号のみが表示されている状態でした。いよいよ、複数のテーブルを結合して同じ表のように表示させます。
まずはレシート詳細と商品を結合してみましょう。
>select * from レシート詳細 inner join 商品
>on レシート詳細.商品No = 商品.商品No
いろんな書き方がありますがオーソドックスな方法で。これはレシート詳細と商品を内部結合しなさいというクエリです。条件はレシート詳細テーブルの商品Noと商品テーブルの商品Noが同じものを結合するということですね。実際に実行して結果を調べてみてください。
また同じように、レシートテーブルと担当者テーブル、レシートテーブルとレシート詳細テーブルを内部結合させるクエリを自分で書いてみてください。
ちなみに、すべてのテーブルを結合させるとこんな感じになります。これで正規化する前に作ったエクセルのような表ができあがりました。
>select * from レシート
>inner join 担当者
>on レシート.担当者No = 担当者.担当者No
>inner join レシート詳細
>on レシート.レシートNo = レシート詳細.レシートNo
>inner join 商品
>on レシート詳細.商品No = 商品.商品No
#center{&image(mssms07.jpg)}
*****COUNT関数
さてここから面倒臭くなってきます。次はCOUNT関数です。これは行数を計算するクエリです。
例えば、担当者別にレジ打ちをしたレシート数を計算するクエリを作ってみます。例えば、鈴木さんが何枚、佐藤さんが何枚のレシートを発行したか？ということですね。まずは内部結合して一覧を表示してみます。
>select * from レシート inner join 担当者 on レシート.担当者No = 担当者.担当者No
まずは、これを担当者別にまとめてみます。鈴木さんを鈴木さん、佐藤さんを佐藤さんの行だけまとめて表示します。
>select 担当者.担当者名 from レシート inner join 担当者 on レシート.担当者No = 担当者.担当者No
>group by 担当者.担当者名
これをグループ化といいます。グループ化ではgroup byで記述した列のデータでひとまとめにして表示します。ここでselect句に*（アスタリスク）は使用できません。アスタリスクにすると異なるデータの入る列があってグループ化できないのでエラーになってしまいます。ここは担当者名でグループ化するのでselectでは担当者テーブルの担当者名だけを表示させるよう記述します。実行すると佐藤さんと鈴木さんでグループされたのがわかると思います。そして、それぞれの数をカウントするようにしてみましょう。
>select count(担当者.担当者名), 担当者.担当者名 from レシート inner join 担当者 on レシート.担当者No = 担当者.担当者No
>group by 担当者.担当者名
担当者テーブルの担当者名をでまとめられた行数をcount関数で集計します。ついでに担当者名も表示させておきます。これで佐藤さんが2枚、鈴木さんが3枚のレシートを発行したのがわかります。
*****SUM関数
次は各レシートごとの合計金額を計算してみましょう。まずは商品詳細テーブルと商品テーブルの結合です。ここまでは簡単ですね。
>select * from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
先ほどは担当者名の回数を数えるために担当者でグループ化しました。今度はレシートごとの合計金額を集計したいのでレシートNoでグループ化します。
>select レシートNo from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
グループ化では異なるデータのものは表示できないのでselectの指定をすることを忘れないようにしましょう。
いよいよ金額の合計です。先ほどは担当者名をcount関数で計算して回数を集計しました。今度は価格を足して行けばいいので価格をsum関数で合計値を集計すればいいはずです。
>select レシートNo,sum(価格) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
これで合計っぽいものができましたが、実際にはこれは間違いです。レシート詳細テーブルをよくみると注文数という項目がありますよね？つまり、商品価格×注文数を合計しなければなりません。なので実際の正解は以下のクエリになります。
>select レシートNo,sum(価格*注文数) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
このクエリはレシートNoでグループ化するので、そのグループ化した行のデータで価格×注文数の合計を計算しなさいということを表しています。
*****MAX関数、MIN関数
次は、それぞれのレシートの中で最も価格の安い商品と、最も価格の高い商品を抽出してみます。ここはcountなどと同じですので、軽く流していきます。
>select レシートNo,max(価格) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
>select レシートNo,min(価格) from レシート詳細 inner join 商品 on レシート詳細.商品No = 商品.商品No
>group by レシートNo
#divclass(h5){実践テスト}
実際に下記のような表示をするクエリを作ってみましょう。指定されていないものはorder byは必要ありません。従ってorder byが指定されていない場合は表示順がおかしな場合があります。
#divclass(h6){日付別の売上げ集計}
#center{&image(mssms08.jpg)}
#co(){
select 日付,sum(価格*注文数) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 日付
}
#divclass(h6){商品ごとの販売数合計}
#center{&image(mssms09.jpg)}
#co(){
select 商品名,sum(注文数) as 販売数 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 商品名
}
#divclass(h6){日付別商品別の売上げ集計}
#center{&image(mssms10.jpg)}
#co(){
select 日付,商品名,sum(注文数*価格) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 商品名,日付
}
#divclass(h6){分類別の販売数、売上げ集計}
#center{&image(mssms11.jpg)}
#co(){
select 分類,sum(注文数*価格) as 金額,sum(注文数) as 注文数 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by 分類
}
#divclass(h6){ご飯物を頼んだレシートのご飯物だけの金額の合計}
#center{&image(mssms12.jpg)}
#co(){
select レシート.レシートNo as レシートNo, sum(注文数) as 注文数,sum(価格*注文数) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
where 分類='ご飯物'
group by レシート.レシートNo
}
#divclass(h6){1000円以上注文したレシートのみの抽出}
#center{&image(mssms13.jpg)}
※ヒント ここで取り上げていないhaving句を使用しています。気になったら勉強してみてくださいな。
#co(){
select レシート.レシートNo as レシートNo, sum(価格*注文数) as 金額 from レシート
inner join レシート詳細 on レシート.レシートNo = レシート詳細.レシートNo
inner join 商品 on レシート詳細.商品No = 商品.商品No
group by レシート.レシートNo
having sum(価格*注文数) > 1000}
#divclass(h5){後書き}
#divclass(h6){おわりに}
これでひとまずおしまいです。簡単なクエリのみ取り上げましたが、ほとんどはここに記述されている内容で解答できるのではないかと思います。他に出そうなものとしては、like、distinct、exists、union、right join、having、副問い合わせあたりでしょうか？
ここまでくれば、同様にテストすることができますので、ネットの情報や参考書をみながらテストしてみるといいと思います。
#divclass(h6){SQLお勧め参考書}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4774138355" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4798118818" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4774144916" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
#html2(){
<iframe src="http://rcm-jp.amazon.co.jp/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=matowiki-22&o=9&p=8&l=as4&m=amazon&f=ifr&ref=ss_til&asins=4822296210" style="width:120px;height:240px;" scrolling="no" marginwidth="0" marginheight="0" frameborder="0"></iframe>}
**コメントを残したい
#divclass(i10)
{
- datetimeで数時間悩んだ……記述ミスなのかわざとなのか -- t (2015-11-03 21:29:00)
- ご迷惑をおかけして申し訳ありません。どこがどのようにおかしかったのでしょうか？訂正させていただきたく思います。よろしくお願い申し上げます。 -- 管理人 (2015-11-04 19:46:12)
- (1,2013/05/20,1) -- 名無しさん (2015-11-05 23:33:32)
- データの入力で(1,2013/05/20,1)と記載されていますがシングルクォーテーションでくくらないと正しく設定されませんでした。--tさんも同じ指摘ですかね。 -- 名無しさん (2015-11-05 23:36:54)
- ご指摘ありがとうございました。すべてクエリは実際の動作を確認したものを掲載していたのですが、漏れがあったようです。シングルクォーテーションが必要だった旨訂正させていただきました。ご指摘ありがとうございました。 -- 管理人 (2015-11-06 18:07:59)
- 管理人さん訂正ありがとうございました。名無しさんもありがとうございました。入門者なのでとても助かってます -- t (2015-11-08 20:25:08)
#comment()
}
#right(){&link_anchor(top){▲}&link_anchor(bottom){▼}}
表示オプション
横に並べて表示：
変化行の前後のみ表示：
