SQLを使う - 応用(3)

リレーション

  • 複数のテーブルに情報をわけて管理し、必要に応じて組み合わせて使うことでデータの取り扱いがしやすくなります
  • データベースについて
生産者用のテーブルを作る




データの挿入

INSERT INTO makers SET name=’山田’, address='東京都港区', tel='000-111-2222';
INSERT INTO makers SET name=’斉藤’, address='北海道小樽市', tel='111-222-3333';
INSERT INTO makers SET name=’川上’, address='神奈川県横浜市', tel='222-333-4444';


テーブルを結びつけるフィールドを作る
  • テーブル「my_items」の、idフィールドの後に「maker_id」フィールドを「INT型」で作成します



  • 値を入れ以下のように設定しておきます

複数のテーブルを横断して検索する
  • 「りんご」のID「2」を検索して、生産者のIDを取得します
SELECT maker_id FROM my_items WHERE id=2;


  • この結果から「maker_id」が「2」であることがわかります
  • 生産者ID「2」を検索します
SELECT * FROM makers WHERE id=2;


  • 生産者は「斉藤」さんであることがわかります
リレーションを使う
  • 2段階に検索したのでは、効率が悪いので「Relation(関連性)」という機能を使い、複数のテーブルをその関連性からつないで1つのテーブルのように扱います
SELECT * FROM makers, my_items WHERE my_items . id=2 AND makers . id=my_items . maker_id;



生産者を一覧表示

SELECT * FROM makers , my_items WHERE makers . id=my_items . maker_id;


WHEREでリレーションを張る
  • 複数のテーブルを指定した場合は、必ずキーとなるフィールドを使って結びつけなければなりません
AND makers . id=my_items . maker_id;

これは、「makerテーブルのidとmy_itemsテーブルのmaker_id」が一致したデータを検索するという意味。

  • テーブル名に続いて「.(ドット)」でつないでフィールド名を記述します
  • 条件にもテーブル名を付加する必要があります

GROUP BY - 複雑な集計

購入履歴を管理するテーブルを作る


  • 以下のようなデータを挿入しておきます

テーブル「my_items」とリレーションを張る
SELECT my_items . item_name , carts . count FROM my_items , carts WHERE my_items . id=carts . item_id;


  • 「item_id」フィールドに入っている数字は、テーブル「my_items」の「id」と一致しています
  • これによりどの商品がいくつ売れたがわかります
商品の購入数を算出する
SELECT SUM(count) FROM carts;


GROUP BY - 商品ごとの購入数を算出する
SELECT item_id , SUM(count) FROM carts GROUP BY item_id;


商品名を結果に表示する
SELECT i . item_name , SUM( c . count ) FROM my_items i , carts c WHERE i . id=c . item_id GROUP BY c . item_id;