データベース 第 11 回 (2009 年 11 月 27 日 ) テーブル結合と集計 ( 演習 )
第 11 回のテーマ 前回より シラバスから離れ 進捗状況に合わせて全体構成を変更しています テーマ1: テーブルの結合 テーマ 2: 結合した結果からの様々な検索 テーマ3: 集計の方法
今日学ぶべきことがら Select 文のさまざまな表現 Natural join sum(*) orrder by Group by などを学ぶ ノートの左側 : 問い の部分に記載する ( 書き込むのは 後にしよう )
前回出した予習課題 教科書 P65 の 明細テーブルの内容を入力してしておいて下さい Select 文を用いての検索例として 試します
データを入力する 以下の 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', 'Display', 45, 2 ); insert into detailtb values ('16002', 'Digital Camera', 30, 1 ); 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 );
SQL 文読み込みのコマンド detailsql.sql ファイルを読み込む USBメモリを準備してください C: MySQLData にコピーする MySQLで 以下の文を実行する source C: MySQLData detailsql.sql; ファイル detailsql.sql の内容が コマンドとして実行される 注意 :load data infile データファイル名 into table テーブル名.. との違いに注意する (SQL コマンド )
演習を簡単にするためには SQL 文を書いて C: MySQLData の下に SQL 文のファイルを置いておき そのファイルを編集して実行する source ファイル名 ; で実行可能 そうすると 毎回書きなおす手間が省ける
教科書の例を入力する (3) Not null ではなく 値がない場合 Insert into CorpTB ( CorpID, CorpName ) values ( B112, Yawataya ); テーブル名の後に列名を記して それに対応するように値を列挙する CorpAddrの列には値がないので その部分を除外する
教科書の例を入力する (4) 入力を間違えた場合の修正 Insert into CorpTB values ( A012, Ohyama Syoten, Yachiyo ); Ohyama Syoten ではなく Koyama Syoten だった Update CorpTB set CorpName= Koyama Syoten where CorpID = A012 ;
教科書の例を入力する (5) 1 レコードを丸ごと削除する Delete from CorpTB where CorpID = E012 ; この where の後は key 項目でなくても記述できる また 該当するレコードがすべて削除される Where 以下を書かないと 全部のレコードが削除されてしまう!
二つのテーブルを結合する Select OrderNo, CorpName from OrderTB, CorpTB where OrderTB.CorpID = CorpTB.CorpID; 意味 注文テーブル :OrderTBの 会社 ID と 会社テーブル :CorpTB の 会社 ID とが 等しいレコードを結び付けて 注文番号と その注文をした会社名を表示する 意味 :
自然結合 OrderTB と CorpTB とでは 会社 ID を表現する列名を同じにしてある この場合には 自然結合が使える Select * from OrderTB natural join CorpTB;
明細データとの結合結果表示 項目名が同じなので 自然結合できる Select * from OrderTB natural join detailtb;
注文金額を知りたい 教科書 P98 算術演算子が使える select OrderNo, Item, Price*1000*Qty from detailtb;
注文番号ごとに集計したい P103, 104 Select OrderNo, sum(price*1000*qty ) from detailtb group by OrderNo; 表示したい項目をSelectの後に書く Group by で 注文番号ごと を記述 sum() は合計を求める集合関数 group by は 何で集合するかを表す
注文番号ごとの集計に 日付と 会社名を表示したい P103, 104 Select OrderNo, sum(price*1000*qty ) from detailtb group by OrderNo;
結果を比べてみよう Select ect OrderTB.OrderNo, e 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 and OrderTB.CorpID = CorpTB.CorpID group by OrderNo order by OrderDate; 同じことをやっているはずだが 結果が異なる natural joinだけに任せていると 予期せぬ結果が出ることがあるので whereを用いた書き方をきちんと覚えよう
さらに 注文内容が Personal Computer だけの集計を出す Select OrderTB.OrderNo, OrderNo OrderDate, CorpName, sum(price*1000*qty) from OrderTB, DetailTB, CorpTB where OrderTB.orderNo = DetailTB.OrderNo and OrderTB.CorpID = CorpTB.CorpIDCorpID group by OrderNo order by OrderDate; where 節に and で続ける and Item= Personal Computer を書き加える
今日のレポート : 来週提出 以下のそれぞれのコマンドの 実行結果をそれぞれについて記して下さい (2 点ずつ ) 各述語 句 (group by 句など ) ごとに コマンドの意味を説明して下さい ( 各項目 1 点 ) 概算 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 and OrderTB.CorpID = CorpTB.CorpID group by OrderNo order by OrderDate;
今日学んだこと Select 文に述語 句を加えると様々な集計 検索が可能となる
第 12 回の予習 本日返却した 自作データベース のテーブル設計を具体的に進め 各項目 2~3 行のレコード ( サンプルデータ ) を登録して SQLを実行できるように 考えてきておいて下さい