Upgrade to Pro — share decks privately, control downloads, hide ads and more …

データベースと応用システム:SQL(2)問い合わせ

 データベースと応用システム:SQL(2)問い合わせ

More Decks by 自然言語処理研究室

Other Decks in Education

Transcript

  1. (c)長岡技術科学大学 電気系 2 全抽出 SELECT * FROM 社員 【社員】 社員

    番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31
  2. (c)長岡技術科学大学 電気系 3 射影(特定列のみ抽出) SELECT 所属, 氏名 FROM 社員 【社員】

    所属 氏名 C部 佐藤 A部 小林 B部 田中 B部 池田 B部 鈴木 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31
  3. (c)長岡技術科学大学 電気系 4 選択(特定行のみ抽出) SELECT * FROM 社員 WHERE 所属

    = 'B部' 【社員】 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31 社員 番号 所属 氏名 年齢 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31 C言語などと違って == ではない
  4. (c)長岡技術科学大学 電気系 5 重複の削除 SELECT DISTINCT 所属 FROM 社員 【社員】

    所属 C部 A部 B部 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31
  5. (c)長岡技術科学大学 電気系 6 グループで集計 SELECT 所属, COUNT(*) FROM 社員 GROUP

    BY 所属 【社員】 所属 COUNT(*) C部 1 A部 1 B部 3 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31
  6. (c)長岡技術科学大学 電気系 7 集合関数 • AVG:平均 • MAX:最大 • MIN:最小

    • SUM:合計 • COUNT:行数 • それぞれ、DISTINCT と併用すると重複を除外した上 で計算することができる。
  7. (c)長岡技術科学大学 電気系 8 集合関数の使用例 SELECT SUM (点数) FROM 受験結果   点数を総和

    SELECT SUM DISTINCT (点数) FROM 受験結果   点数を(重複を除外して)総和 SELECT MAX (点数) FROM 受験結果   点数の最大値を求める SELECT COUNT DISTINCT (*) FROM 受験結果   (重複を除外して)NULL以外の行数を数える
  8. (c)長岡技術科学大学 電気系 9 集計後に検索 SELECT 所属, COUNT(*) FROM 社員 GROUP

    BY 所属 HAVING COUNT(*) >= 3 【社員】 所属 COUNT(*) B部 3 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31
  9. (c)長岡技術科学大学 電気系 10 ソート SELECT * FROM 社員 ORDER BY 年齢

    DESC 【社員】 社員 番号 所属 氏名 年齢 C1 C部 佐藤 40 B1 B部 田中 38 A1 A部 小林 35 B3 B部 鈴木 31 B2 B部 池田 23 年齢を降順でソート 社員 番号 所属 氏名 年齢 A1 A部 小林 35 B1 B部 田中 38 B2 B部 池田 23 B3 B部 鈴木 31 C1 C部 佐藤 40
  10. (c)長岡技術科学大学 電気系 11 ソート(続き) SELECT * FROM 社員 ORDER BY

    年齢 ASC   昇順でソート SELECT * FROM 社員 ORDER BY 年齢   ASCは省略可能 SELECT * FROM 社員 ORDER BY 年齢, 社員番号   年齢が同じ場合、社員番号でさらにソートする SELECT 受注ID, SUM(数量) FROM 社員 ORDER BY 2 ASC   数字を指定するとSELECT中の項目番号を指す。   この場合は ORDER BY SUM(数量) ASC と同じ意味
  11. (c)長岡技術科学大学 電気系 12 集合演算子 SELECT 会社ID FROM 受注伝票  UNION SELECT

    会社ID FROM 得意先リスト • UNIONは和集合を重複行なしで出力する。重複させたま ま出力するには UNION ALL とする。 • 差集合は EXCEPT • 積集合は INTERSECT
  12. (c)長岡技術科学大学 電気系 13 SQLの演算子 • 関係演算子 – >, <, >=,

    <= – = (C言語の==), <> (C言語の!=)の6種類 • 論理演算子 – AND, OR, NOT の3種類
  13. (c)長岡技術科学大学 電気系 14 BETWEEN WHERE 受注日 BETWEEN '20010401' AND '20030331'

    • 両端の数字を含む。 • 上記のように書いても同じ   WHERE   受注日 >= '20010401' AND   受注日 <= '20030331'
  14. (c)長岡技術科学大学 電気系 15 IN WHERE 受注日 IN ('20010802', '20010803') •

    ()内に指定した値だけを対象にする。  
  15. (c)長岡技術科学大学 電気系 16 LIKE WHERE 氏名 LIKE '山%'   氏名が山~(任意長)である WHERE

    氏名 LIKE '山_'   氏名が山~(1文字)である WHERE 氏名 LIKE '%山'   氏名が~(任意長)山である
  16. (c)長岡技術科学大学 電気系 18 NOT WHERE 年齢 NOT BETWEEN '30' AND

    '39'   年齢が30以上39以下でない WHERE 年齢 NOT IN ('30', '35')   年齢が30または35でない WHERE 氏名 NOT LIKE '山%'   氏名が山~(任意長)でない WHERE 氏名 NOT LIKE '山_'   氏名が山~(1文字)でない WHERE 所属 IS NOT NULL   所属がNULLでない
  17. (c)長岡技術科学大学 電気系 20 内部結合 SELECT 受注.受注ID, 受注.日付, 得意先.会社名  FROM 受注,

    得意先  WHERE 受注.会社ID = 得意先.会社ID 受注ID, 日付, 会社名が単一の表にしかない属性名の場合 は、下記のように表名を省略可能 SELECT 受注ID, 日付, 会社名  FROM 受注, 得意先  WHERE 受注.会社ID = 得意先.会社ID
  18. (c)長岡技術科学大学 電気系 21 内部結合(続き) 別名を使って下記のように書くこともできる。 SELECT 受注ID, 日付, 会社名  FROM

    受注 X, 得意先 Y  WHERE X.会社ID = Y.会社ID 別の書き方もある。 SELECT 受注ID, 日付, 会社名  FROM 受注 INNER JOIN 得意先  ON 受注.会社ID = 得意先.会社ID INNER JOINは省略して JOINと書いてもよい 列名が同じ場合は USING 会社ID と書いてもよい
  19. (c)長岡技術科学大学 電気系 22 自己結合 同一の表を別の表のように扱って内部結合する。 SELECT X.氏名, Y.氏名 AS 上司氏名

     FROM 名簿 X, 名簿 Y  WHERE X.上司ID = Y.会員ID 属性名の別名 会員ID 氏名 住所 上司ID 氏名 上司氏名 【名簿】 上記の出力
  20. (c)長岡技術科学大学 電気系 23 外部結合 記法1 SELECT 受注ID, 日付, 会社名  FROM

    受注 LEFT JOIN 得意先  ON 受注.会社ID = 得意先.会社ID 記法2 SELECT 受注ID, 日付, 会社名  FROM 受注 LEFT JOIN 得意先  USING 会社ID LEFT JOIN:左外部結合 RIGHT JOIN:右外部結合 FULL JOIN:全外部結合
  21. (c)長岡技術科学大学 電気系 24 副問い合わせ(1)関係演算子 SELECT 社員番号, 所属, 氏名, 年齢 FROM

    社員 WHERE 年齢 >=  ( SELECT AVG(年齢) FROM 社員 ) 【社員】から平均年齢以上を抽出
  22. (c)長岡技術科学大学 電気系 25 副問い合わせ(2)IN演算子 SELECT 社員番号, 所属, 氏名 FROM 社員

    WHERE 社員番号 IN  ( SELECT 社員番号 FROM 社員年齢   WHERE 年齢 >= 30 ) 30歳以上の社員を抽出 社員 番号 所属 氏名 C1 C部 佐藤 A1 A部 小林 B1 B部 田中 B2 B部 池田 B3 B部 鈴木 社員 番号 年齢 C1 40 A1 35 B1 38 B2 23 B3 31 【社員】 【社員年齢】
  23. (c)長岡技術科学大学 電気系 26 副問い合わせ(3)EXISTS演算子 SELECT 社員番号, 所属, 氏名 FROM 社員

    WHERE EXISTS  ( SELECT * FROM 社員年齢   WHERE 社員番号 = 社員.社員番号   AND 年齢 >= 30 ) 30歳以上の社員を抽出 (前ページと同じ結果) 社員 番号 所属 氏名 C1 C部 佐藤 A1 A部 小林 B1 B部 田中 B2 B部 池田 B3 B部 鈴木 社員 番号 年齢 C1 40 A1 35 B1 38 B2 23 B3 31 【社員】 【社員年齢】 【社員】の社員番号
  24. (c)長岡技術科学大学 電気系 27 IN と EXISTS は何が違う? • 両者は同じ結果を得ることができる。 •

    しかし、一般にはEXISTSを使ったほうが速い (理由) • INを使った場合は、主問い合わせと副問い合わせ が組み合わせ的に検索される。 – 両者の処理件数が各1万件なら1億回の検索が必要 • EXISTSを使った場合は、もし結合キーの副問い合 わせ部分がインデックス化されていれば、組み合 わせ的にならない – 上記例では1万回 + 1万回の検索でよい