11. SQL の IN,副問い合わせ

470 Views

January 12, 22

スライド概要

リレーショナルデータベースの基本(スライド資料とプログラム例)
https://www.kkaneko.jp/de/ds/index.html


ds-1. データベースとは,データベースシステムとは,情報とデータ
ds-2. SQL,SQL のデータ型,テーブル定義,問い合わせ(クエリ)
ds-3. ER 図,関連,異状, テーブル分解
ds-4. 問い合わせ(クエリ)
ds-5. 集計・集約
ds-6. 並べ替え(ソート)
ds-7. 結合
ds-8. データベースの異状,分解と結合
ds-9. 主キー,参照整合性制約,従属
ds-10. 中間まとめ,データベースの応用,データベースの種類
ds-11. SQL のIN,副問い合わせ
ds-12. データベースの AND,OR,NOT
ds-13. データベース操作,トランザクション,リカバリ,ロック,同時実行制御
ds-14. 従属,正規形,正規化
ds-15. さまざまなデータベース

YouTube 再生リスト「リレーショナルデータベースの基本」
https://youtube.com/playlist?list=PLwoDcGBEg9WGKPP6dExr8DcUf9nV2kYGD

金子邦彦研究室ホームページ:
https://www.kkaneko.jp/index.html

profile-image

金子邦彦(かねこくにひこ) 福山大学・工学部・教授 ホームページ: https://www.kkaneko.jp/index.html 金子邦彦 YouTube チャンネル: https://youtube.com/user/kunihikokaneko

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

関連スライド

各ページのテキスト
1.

11. SQL の IN,副問い合わせ URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞:この資料では「いらすとや」のイラストを使用しています 1

2.

複数の値との比較(IN),副問い合わせ • 複数の値との比較(IN) SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); 一階または二階 • IN は,問い合わせの結果を別の問い合わせで使う(副問い 合わせ)ときでも便利 SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 2

3.

アウトライン 番号 項目 復習 11-1 11-2 11-3 11-4 11-5 SQL の IN Access を用いた IN の演習 SQL の副問い合わせ Accessを用いた副問い合わせの演習 Paiza.IO による演習 3

4.

リレーショナルデータベースシステム データベースシステムの一種 コンピュータ 記憶 装置 リレーショナル リレーショナル データベース データベース 管理システム たくさんのテーブルが格納される あわせて リレーショナルデータベースシステム 4

5.

SQL • SQL は、リレーショナルデータベースシステムの さまざまな機能を使える言語 問い合わせ(クエリ)、 テーブル定義、 その他の操作 5

6.

SQL による問い合わせ(クエリ)の例 ① SELECT * FROM 商品; ② SELECT 名前, 単価 FROM 商品; ③ SELECT 名前, 単価 FROM 商品 WHERE 単価 > 80; ④ SELECT 受講者, COUNT(*) FROM 成績 GROUP BY 受講者; ⑤ SELECT * FROM 米国成人調査データ ORDER BY 年齢; ⑥ SELECT * FROM T, S; ⑦ SELECT * FROM T, S WHERE a = b; 6

7.

Access で SQLビューを開く. ①「作成」タブで、 「クエリデザイン」 をクリック このような 表示が出た ときは 「閉じる」を クリック ②「デザイン」タブで、 「表示」を展開し「SQL ビュー」を選ぶ 7

8.

11-1. SQL の IN 8

9.

SQL での条件 • 比較 (= など) SELECT * FROM 授業 WHERE 教室名 = '一階'; • パターンマッチ (LIKE) SELECT * FROM 授業 WHERE 教室名 LIKE '%階'; Access では % でなく * • 複数の値との比較(IN) SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); • 条件は、複数を AND や OR でつなげることができる SELECT * FROM 授業 WHERE 教室名 = '一階' OR 教室名 = '二階'; 9

10.

SQL の IN 複数の値と比較. そのうち1つの値でも一致するものを結果とする SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); 半角丸かっこ で囲む 半角の カンマ 半角丸かっこ で囲む 10

11.

11-2. Access を用いた IN の演習 11

12.

演習用のデータベースファイル • 演習用の Access データベースファイル セレッソの利用者は,セレッソからもダウンロード可能 ファイル名: db11.accdb • 「コンテンツの有効化」のメッセージが出たときは、確認 のうえ、次にすすむ • つぎのような表示が出たときは、確認のうえ、「はい」 12

13.

演習で使うデータベース 米国成人調査データ (1994年、米国における統計調査データのうち 32561 人分) ※ このデータを使います (演習では、特定の職業、学歴、性別、母国を差別的に見ないようにしてください) データの出典:Lichman, M. (). UCI Machine Learning Repository [http://archive.ics.uci.edu/ml]. Irvine, CA: University of California, School of Information and Computer Science (米国) 13

14.

米国成人調査データ 「成績」はあとで使う 14

15.

SQL ビュー Access で、SQL ビューを開くときは、「表示」→ 「SQLビュー」と操作する 表示の下に「SQLビュー」が ないときは、 「作成」,「クエリデザイン」 と操作 15

16.

問い合わせ(クエリ)での、2つのビュー 実行 SQL ビュー 問い合わせ(クエリ)の 作成、編集 表示 + SQL ビュー データシートビュー 問い合わせ(クエリ)の 結果 マウス操作でビューを切り替え 16

17.

SELECT * FROM 米国成人調査データ WHERE 教育 IN ('10th', '11th'); ・空白文字は半角 ・「;」は最後にだけ 選択 10th, 11th だけが選ばれている 17

18.

SELECT * FROM 米国成人調査データ WHERE 母国 IN ('インド', 'メキシコ'); ・空白文字は半角 選択 ・「;」は最後にだけ インド,メキシコだけが選ばれている 18

19.

11-3. SQL の副問い合わせ 19

20.

単純な問い合わせ 成績テーブル 学生番号 101 の科目名を得る SQL SELECT 科目名 FROM 成績 WHERE 学生番号 = 101; 科目名 データベース 数学 結果は1つのテーブル, テーブルの本体は「データベース」と「数学」 20

21.

副問い合わせ 問い合わせの結果を、別の問い合わせで使う 成績テーブル 学生番号 101 の科目名を得る SQL SELECT 科目名 FROM 成績 WHERE 学生番号 = 101; 科目名 データベース 数学 「データベース」と「数学」 学生番号 101 の科目名のものを(1つでもよいので)受講している 学生番号を全て得る SQL SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 学生番号 101 102 103 「101」と「102」と「103」 21

22.

副問い合わせの書き方例 次の書き方が便利 SELECT ... FROM ... WEHRE ... IN (SELECT ... FROM ... WHERE ...); 学生番号 101 の科目名のものを(1つでもよいので)受講している 学生番号を全て得る SQL SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 22

23.

11-4. Access を用いた 副問い合わせの 演習 23

24.

副問い合わせ 副問い合わせでは、 問い合わせの結果を、別の問い合わせで使う 単純な問い問い合わせ 全ての中の最高点 SELECT MAX (得点) FROM 成績; 副問い合わせ 最高点を取ったのはだれか SELECT 学生番号 FROM 成績 WHERE 得点 IN (SELECT MAX (得点) FROM 成績); 24

25.

成績テーブル 学生番号 101 101 102 102 103 201 202 科目名 データベース 数学 データベース プログラミング データベース 歴史 地理 演習では, 11-2. Access を用いた IN の演習 で用いた db11.accdb を使用 得点 90 80 85 100 95 90 90 25

26.

SELECT 科目名 FROM 成績 WHERE 学生番号 = 101; 単純な問い合わせ 学生番号 101 の科目名 26

27.

SELECT MAX (得点) FROM 成績; 単純な問い合わせ 全ての中の最高点 27

28.

SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN ('データベース', '数学'); 単純な問い合わせ データベースや数学が科目名にある学生番号 28

29.

SELECT 学生番号 FROM 成績 WHERE 得点 IN (SELECT MAX (得点) FROM 成績); 副問い合わせ 最高点をとった学生 29

30.

SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 副問い合わせ 学生番号 101 の科目名のものを(1つでもよいの で)受講している学生番号 30

31.

この授業では「書き方2」をおススメします • 書き方1 (副問い合わせの答えが必ず1つの場合) SELECT ・・・ FROM ・・・ WHERE ・・・ = ( SELECT ・・・ FROM ・・・ WHERE ・・・); • 書き方2 (副問い合わせの答えが2つ以上になる可能性がある場合) SELECT ・・・ FROM ・・・ WHERE ・・・ IN ( SELECT ・・・ FROM ・・・ WHERE ・・・); 31

32.

11-5. Paiza.IO による演習 32

33.

Paiza.IO の使い方 ① ウェブブラウザを起動する ② 次の URL を開く https://paiza.io/ ③ もし,表示が英語になっていたら,日本語に切り 替える

34.

④ 「コード作成を試してみる」をクリック ⑤ 「MySQL」を選ぶ(左上のボタンをクリックするとメニューが出る)

35.

プログラムの 編集画面 プログラムを 書き換えること ができる 実行ボタン 35

36.

編集画面を確認する。 すでに、SQL が入っているが、使わないので消す。

37.

• 次の SQL を入れ,実行結果を確認 使用している Web ブラウザの設定等によっては,結果が表示されない場合 がある.そのときは,Webブラウザを別のものに変えて実行 37

38.

• 次の SQL を入れる。次のページに続く 38

39.

• 次の SQL を入れる。次のページに続く 39

40.

• 実行結果を確認 使用している Web ブラウザの設定等によっては,結果が表示されない場合 がある.そのときは,Webブラウザを別のものに変えて実行 40

41.

複数の値との比較(IN),副問い合わせ • 複数の値との比較(IN) SELECT * FROM 授業 WHERE 教室名 IN ('一階', '二階'); 一階または二階 • IN は,問い合わせの結果を別の問い合わせで使う(副問い 合わせ)ときでも便利 SELECT DISTINCT 学生番号 FROM 成績 WHERE 科目名 IN (SELECT 科目名 FROM 成績 WHERE 学生番号 = 101); 41