4. 問い合わせ（クエリ）

4. 問い合わせ（クエリ） URL: https://www.kkaneko.jp/de/ds/index.html 金子邦彦 謝辞：この資料では「いらすとや」のイラストを使用しています 1

• https://www.kkaneko.jp/de/ds/index.html

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

問い合わせ（クエリ）の仕組み 問い合わせ（クエリ） の結果は，テーブル形式の データ 問い合わせ （クエリ） のコマンド リレーショナル データベースシステム データの種類ごとに分かれ た、たくさんのテーブル 3

SQL による問い合わせの例 ① SELECT * FROM 商品; ② SELECT 名前, 単価 FROM 商品; ③ SELECT 名前, 単価 FROM 商品 WHERE 単価 > 80; 4

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

アウトライン 番号 項目 説明時間の 目安 4-1 リレーショナルデータベースシステム の機能紹介 5分 4-2 問い合わせ（クエリ） 20分 4-3 Access を用いた問い合わせ（クエリ）の 演習① 14分 4-4 Access を用いた問い合わせ（クエリ）の 演習② 11分 6

リレーショナルデータベースでのさまざまな データ分析 年収５万ドル以上の人の職業 教育の列（属性） にはどういう値があるか 7

今日の内容 • リレーショナルデータベースの豊富な機能を知る • 問い合わせ（クエリ）の全体像を知る 射影、選択、結合、集計・集約、並べ替え（ソート）、副問い 合わせの体験 （より詳しくは、別の回で） • 実データを用いた演習を各自で 8

4-1. リレーショナルデータベー スシステムの機能紹介 9

なぜリレーショナルデータベースシステムを使う のでしょうか？ 他のデータベースシステムと比べて • 機能が豊富、便利 • 問い合わせ（クエリ）は SQL で簡単にできる • データベース設計もしやすく、学びやすい • 普及度はナンバーワン。無料のデータベースシス テムも多数ある。Web との連携なども簡単。 10

リレーショナルデータベースシステムの機能 テーブル定義 問い合わせ（クエ リ） データ操作 トランザクション 機能 テーブル定義 データ型 SQL のキーワード CREATE TABLE CHAR, TEXT, INTEGER, REAL, DATETIME, BIT, NULL オートナンバー AUTOINCREMENT 主キー PRIMARY KEY 参照整合性制約 FOREIGN KEY, REFERENCES 射影、選択、結合 SELECT FROM WHERE 重複行除去（分解でも） DISTINCT 比較，範囲指定，パター =, <, >, <>, !=, <=, >=, BETWEEN, ンマッチ，AND/OR LIKE, AND, OR, IS NULL, IS NOT NULL 集計・集約 GROUP BY, MAX, MIN, COUNT, AVG, SUM 並べ替え（ソート） ORDER BY 副問い合わせ IN 挿入、削除、更新 INSERT INTO, DELETE FROM WHERE, UPDATE SET WHERE 開始、コミット、ロール BEGIN TRANSACTION, COMMIT, バック ROLLBACK 11

4-2. 問い合わせ（クエリ） 12

問い合わせ（クエリ） • リレーショナルデータベースシステムの問い合わ せ（クエリ）は、さまざまな機能の組み合わせ • 問い合わせ（クエリ）の結果は、テーブル形式の データ 13

射影 元のテーブル テーブル名: P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 PRICE 20 30 1000 100 誰が、何を、いくらで買ったか 射影 SELECT CUST FROM P; 射影 SELECT PRODUCT, CUST FROM P; 14

選択 元のテーブル テーブル名: P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 PRICE 20 30 1000 100 誰が、何を、いくらで買ったか 選択 SELECT * FROM P WHERE PRICE > 50; 選択と射影の組み合わせ SELECT PRODUCT FROM P WHERE PRICE > 50; 15

結合とは 結合とは、２つのテーブルを結合条件で、 １つにまとめること テーブル テーブル 新しい テーブル 結合 結合条件について、より詳しくは別の回で説明 16

結合 元のテーブル テーブル名: P CUST PRODUCT PRICE 100 P100 20 101 P100 30 101 X200 1000 102 P300 100 誰が、何を、いくらで買ったか ID テーブル名: C NAME BBB AAA CCC 100 101 102 MEMBER 2020/10/02 2020/10/10 2020/10/15 名簿（番号と氏名と入会日） 結合と射影の組み合わせの例 SELECT NAME, PRODUCT FROM P, C WHERE P.CUST = C.ID; 17

重複行除去 元のテーブル テーブル名: P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 PRICE 20 30 1000 100 誰が、何を、いくらで買ったか 重複行除去しない SELECT CUST FROM P; 重複行除去する SELECT DISTINCT CUST FROM P; 18

並べ替え（ソート） 元のテーブル テーブル名: P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 PRICE 20 30 1000 100 誰が、何を、いくらで買ったか 並べ替え（ソート） SELECT * FROM P ORDER BY PRICE; PRICE で並べ替え 並べ替え（ソート）の詳細は、別の回で説明 19

集計・集約 元のテーブル テーブル名: P CUST 100 101 101 102 PRODUCT P100 P100 X200 P300 PRICE 20 30 1000 100 誰が、何を、いくらで買ったか 集計・集約 SELECT CUST, COUNT(*) FROM P GROUP BY CUST; 誰が、何個買ったか 集計・集約の詳細は、別の回で説明 20

副問い合わせ 元のテーブル テーブル名: P CUST PRODUCT PRICE 100 P100 20 101 P100 30 101 X200 1000 102 P300 100 誰が、何を、いくらで買ったか ID テーブル名: C 100 101 102 NAME MEMBER BBB 2020/10/02 AAA 2020/10/10 CCC 2020/10/15 名簿（番号と氏名と入会日） 副問い合わせ SELECT NAME FROM C WHERE ID IN (SELECT CUST FROM P WHERE PRODUCT='P100'); P100 を買ったのは誰？ 詳細は別の回で説明 21

4-3. Access を用いた問い合わ せ（クエリ）の演習① 22

Access のデータベースファイル データベースは ファイルに格納されている 他のファイルは関係ない このファイル ↑ある Access データベースのファイル 23

演習用のデータベースファイル • 演習用の Access データベースファイル セレッソの利用者は，セレッソからもダウンロード 可能 ファイル名: db4-3.accdb URL: https://www.kkaneko.jp/cc/ds/index.html 「コンテンツの有効化」のメッセージが出たときは、 確認のうえ、次にすすむ • つぎのような表示が出たときは、確認のうえ、 「はい」 24

• https://www.kkaneko.jp/cc/ds/index.html

テーブル名: P 誰が、何を、いくらで買ったか テーブル名: C 名簿（番号と氏名と入会日） 25

射影、選択 射影 SELECT CUST FROM P; 射影 SELECT PRODUCT, CUST FROM P; 選択 SELECT * FROM P WHERE PRICE > 50; 26

射影、選択 選択と射影の組み合わせ SELECT PRODUCT FROM P WHERE PRICE > 50; 結合 結合と射影の組み合わせ SELECT NAME, PRODUCT FROM P, C WHERE P.CUST = C.ID; 27

重複行除去 重複行除去する SELECT DISTINCT CUST FROM P; 並べ替え（ソート） 並べ替え（ソート） SELECT * FROM P ORDER BY PRICE; PRICE で並べ替え 28

集計・集約 集計・集約 SELECT CUST, COUNT(*) FROM P GROUP BY CUST; 副問い合わせ 副問い合わせ SELECT NAME FROM C WHERE ID IN (SELECT CUST FROM P WHERE PRODUCT='P100'); 誰が、何個買ったか P100 を買ったのは誰？ 29

4-4. Access を用いた問い合わ せ（クエリ）の演習② 30

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

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

米国成人調査データ 33

SELECT DISTINCT 教育 FROM 米国成人調査データ; 重複行除去． 教育の列（属性）の値をみる 34

SELECT 年齢, 職業 FROM 米国成人調査データ WHERE 年収５万ドル以上か = '>50K'; 年収５万ドル以上の人の年齢と職業 35

SELECT DISTINCT 職業 FROM 米国成人調査データ WHERE 年収５万ドル以上か = '>50K'; 年収５万ドル以上の人の職業、重複行除去 36

SELECT * FROM 米国成人調査データ WHERE 年齢 > 80 ORDER BY 年齢; 80歳よりも上の人は？ そして、並べ替え（ソート） 37