Microsoft PowerPoint pptx

(1)

データベース

デ

タ

デ

タ

第11回（2009年11月27日）テーブル結合と集計（演習）テブル結合と集計（演習）

(2)

第

11 11回

回のテーマ

のテーマ

第

11 11回

回のテーマ

のテーマ

y 前回よりシラバスから離れ進捗状況に y 前回より、シラバスから離れ、進捗状況に合わせて全体構成を変更しています。 y テーマ１：テーブルの結合テ２結合した結果からの様々な検索 y テーマ２：結合した結果からの様々な検索 y テーマ３：集計の方法

(3)

今日学ぶべきことがら

y Select文のさまざまな表現 y Select文のさまざまな表現 ◦ Natural join ◦ sum(*) ◦ orrder byy ◦ Group by ◦ などを学ぶ ◦ などを学ぶノートの左側：「問い」の部分に記載する（書き込むのは、後にしよう。）

(4)

前回出した予習課題

y 教科書P65の明細テブルの内容を y 教科書P65の、明細テーブルの内容を入力してしておいて下さい。 y Select文を用いての検索例として、試します。します。

(5)

データを入力する

データを入力する。

以下のSQL文を実行する以下のSQL文を実行する。

create table detailTB ( OrderNo char(5) not null, Item varchar(40) not null, Price int unsigned not null, Qty smallint unsigned not null, primary key (OrderNo, Item) ); insert into detailTB values ('16001', 'Personal Computer', 100, 2 );

insert into detailTB values ('16001', 'MO Drive', 50, 1 ); insert into detailTB values ('16001' 'Table Tap' 2 4 ); insert into detailTB values ( 16001 , Table Tap , 2, 4 ); insert into detailTB values ('16001', 'Display', 45, 2 );

insert into detailTB values ('16002', 'Digital Camera', 30, 1 ); d lTB l ('16002' 'CF M ' 10 2 ) insert into detailTB values ('16002', 'CF Memory', 10, 2 ); insert into detailTB values ('16003', 'Filter', 6, 2 );

insert into detailTB values ('16003', 'Personal Computer', 90, 3 ); insert into detailTB values ('16004', 'Carrier', 5, 1 );

insert into detailTB values ('16004', 'Battery', 9, 1 ); insert into detailTB values ('16004', 'Display', 40, 3 ); insert into detailTB values ( 16004 , Display , 40, 3 );

(6)

SQL

SQL文読み込みのコマンド

文読み込みのコマンド

SQL

SQL文読み込みのコマンド

文読み込みのコマンド

y detailSQL sqlファイルを読み込む y detailSQL.sqlファイルを読み込む。 ◦ USBメモリを準備してください。 C ¥M SQLD ¥ y C:¥MySQLData¥ ◦ にコピーする。 y MySQLで、以下の文を実行する。 source C:¥MySQLData¥detailSQL.sql;y q ◦ ファイル「detailSQL.sql」の内容が、コマンドとして実行される。

◦ 注意：_{load data infile ‘データファイル名’ into table} ‘テーブル名’ …..

(7)

演習を簡単にするためには

y SQL文を書いて C:¥MySQLData¥の下 y SQL文を書いて、C:¥MySQLData¥の下に、SQL文のファイルを置いておき、そのイを編集し実行するそのファイルを編集して実行する。 source ファイル名; で実行可能 sou ce ファイル名; で実行可能 y そうすると、毎回書きなおす手間が省けるける。

(8)

教科書の例を入力する

(3)

教科書の例を入力する

(3)

y Not null ではなく値がない場合 y Not null ではなく、値がない場合

Insert into CorpTB ( CorpID, CorpName ) values ( ‘B112’, ‘Yawataya’ ); テブル名の後に列名を記してそれテーブル名の後に列名を記して、それに対応するように値を列挙する。 CorpAddrの列には値がないので、その部分を除外する。部分を除外する。

(9)

教科書の例を入力する

(4)

教科書の例を入力する

(4)

y 入力を間違えた場合の修正 y 入力を間違えた場合の修正

Insert into CorpTB values (

‘A012’ ‘Ohyama Syoten’ ‘Yachiyo’ );A012 , Ohyama Syoten , Yachiyo );

‘Ohyama Syoten’ではなく’Koyama Syoten’

だた

だった・・ Update CorpTBp p

set CorpName=‘Koyama Syoten’ h C ID ‘A012’

(10)

教科書の例を入力する

(5)

教科書の例を入力する

(5)

y １レコドを丸ごと削除する y １レコードを丸ごと削除する。

Delete from CorpTB

where CorpID = ‘E012’;

y この where の後は key項目でなく y この、where の後は、key項目でなくても記述できる。 y また、該当するレコードがすべて削除される。される。 y Where以下を書かないと、全部のレコドが削除されてしまう！コードが削除されてしまう！

(11)

二つのテーブルを結合する

二つのテーブルを結合する。

Select OrderNo CorpName Select OrderNo, CorpName

from OrderTB, CorpTB

where OrderTB.CorpID = CorpTB.CorpID;

y 意味：意味 ◦ 注文テーブル：_{OrderTBの「会社ID」と、} 会社テブル：C TBの「会社ID」とが ◦ 会社テーブル：CorpTBの「会社ID」とが ◦ 等しいレコードを結び付けて、 ◦ 注文番号と、その注文をした会社名を表示する。

(12)

自然結合

y OrderTBとCorpTBとでは会社IDを表 y OrderTBとCorpTBとでは、会社IDを表現する列名を同じにしてある。 y この場合には、自然結合が使える。

Select * from OrderTB Select from OrderTB

(13)

明細データとの結合結果表示

項目名が同じなので自然結合できる項目名が同じなので、自然結合できる。 Select * from OrderTB

(14)

注文金額を知りたい

y 教科書P98 y 教科書P98 ◦ 算術演算子が使える

select OrderNo, Item, Price*1000*Qty from detailTB;

(15)

注文番号ごとに集計したい

y P103 104 y P103, 104

Select OrderNo, sum(Price*1000*Qty ) from detailTB group by OrderNo; group by OrderNo; 表示したい項目をSelectの後に書く。 Group by で、「注文番号ごと」を記述 sum()は合計を求める集合関数 sum()は合計を求める集合関数 group by は、何で集合するかを表す。

(16)

注文

注文番号ごとの集計に、日付と

番号ごとの集計に、日付と

会社名を表示したい。

y P103 104 y P103, 104

Select OrderNo, sum(Price*1000*Qty ) from detailTB

group by OrderNo; group by OrderNo;

(17)

結果を比べてみよう

Select OrderTB.OrderNo, OrderDate, CorpName, sum(Price*1000*Qty) Se ect O e .O e o, O e ate, Co p a e, su ( ce 000 Qty)

from OrderTB natural join CorpTB, DetailTB group by OrderNo

order by OrderDate;

Select OrderTB.OrderNo, OrderDate, CorpName, sum(Price*1000*Qty) from OrderTB DetailTB CorpTB

from OrderTB, DetailTB, CorpTB

where OrderTB.orderNo = DetailTB.OrderNo and OrderTB.CorpID = CorpTB.CorpID group by OrderNo order by OrderDate; 同じことをやっているはずだが結果が異なる同じことをやっているはずだが、結果が異なる ⇒ natural joinだけに任せていると、予期せぬ結果が出ることがあるので、whereを用いた書き方をきちんと覚えよう。

(18)

さらに、注文内容が「

Personal

Computer

Computer」だけの集計を出す

」だけの集計を出す

Select OrderTB OrderNo OrderDate CorpName Select OrderTB.OrderNo, OrderDate, CorpName,

sum(Price*1000*Qty)

from OrderTB, DetailTB, CorpTB from OrderTB, DetailTB, CorpTB

where OrderTB.orderNo = DetailTB.OrderNo and OrderTB CorpID = CorpTB CorpID and OrderTB.CorpID CorpTB.CorpID group by OrderNo

order by OrderDate; order by OrderDate;

y where節に、andで続ける

and Item=‘Personal Computer’ and Item= Personal Computer を書き加える。

(19)

【今日のレポート：来週提出】

以下のそれぞれのコマンドの、実行結果をそれぞれについて記して下さい。（２点ずつ）各述語、句（_{group by 句など）ごとに、コマンドの意味を説明} して下さい。（各項目１点）概算_12点満点説明する項目の数が多いほど点数は高くなります説明する項目の数が多いほど、点数は高くなります。

Select OrderTB.OrderNo, OrderDate, CorpName, sum(Price*1000*Qty) from OrderTB natural join CorpTB, DetailTB

group by OrderNo order by OrderDate;

Select OrderTB.OrderNo, OrderDate, CorpName, sum(Price*1000*Qty) from OrderTB, DetailTB, CorpTB

where OrderTB orderNo = DetailTB OrderNo where OrderTB.orderNo DetailTB.OrderNo

and OrderTB.CorpID = CorpTB.CorpID group by OrderNo

d b O d D order by OrderDate;

(20)

今日学んだこと

y Select文に述語句を加えると様々な y Select文に述語、句を加えると様々な集計・検索が可能となる。

(21)

第

12 12回

回の予習

の予習

第

12 12回

回の予習

の予習

本日返却した「自作デタベス」 y 本日返却した、「自作データベース」のテーブル設計を具体的に進め、各項２３行ド（プデ目２～３行のレコード（サンプルデータ）を登録して、SQLを実行できるように、考えてきておいて下さい。