3．SQLによる結合

【概要】

リレーショナルデータベースは複数のテーブルから構成される．関連するデータが複数のテーブルに分散して格納されているため，複数のテーブルにまたがる情報を得るにはテーブルの結合が必要となる．本教材では，複数のテーブルを結合して1つのテーブルを生成する操作について説明し，SQLite3データベースとPythonを用いた結合操作の演習を行う．

【目次】

【サイト内の関連ページ】

第１章　外部キー

テーブル R(…, Ai, …) と S(…, Bj, …) について，属性 Bj がテーブル S の主キーであるとする．次の条件が成り立つとき，属性 Ai をテーブル R の外部キー（foreign key）という．

R の任意の行における Ai の値は，S の Bj のいずれかの値と等しいか，または空値（NULL）である．

結合演算では，外部キーとそれが参照する主キーを結合条件として使用する．

第２章　結合演算

R(A1, A2, …, An) と S(B1, B2, …, Bm) をテーブルとする．R の属性 Ai と S の属性 Bj に対する結合演算「R[Ai θ Bj]S」は次のように定義される（θ は比較演算子）．

R[Ai θ Bj]S は，R と S の直積集合（R × S，すなわち R の各行と S の各行のすべての組み合わせ）の要素のうち，R.Ai θ S.Bj を満たすものの集合である．

結合演算「R[Ai θ Bj]S」は，SQLで次のように記述する．

SELECT * FROM R, S WHERE R.Ai θ S.Bj

第３章　結合問い合わせ

結合問い合わせとは，SQL問い合わせの WHERE <式> に結合演算を含む問い合わせである．

第４章　SQLite3のデータベースファイル

本教材では，リレーショナルデータベース管理システム SQLite3 を使用する．SQLite3 では，リレーショナルデータベースが1つのファイルに格納される．そのため，データベースファイルのコピーや配布が容易である．

第５章　UML図

UML（Unified Modeling Language）図は，テーブル名，各テーブルの属性名，各属性のデータ型，主キーなどを図示したものである．本教材で使用するデータベース（SQLite版）の構造を以下に示す．

凡例 — PK: 主キー，FK: 外部キー，UNIQUE: 一意制約

employee
emp_no : INTEGERPK
birth_date : DATE
first_name : TEXT
last_name : TEXT
gender : TEXT ('M' または 'F')
hire_date : DATE
department
dept_no : TEXTPK
dept_name : TEXTUNIQUE
dept_emp
emp_no : INTEGERPK, FK → employee
dept_no : TEXTPK, FK → department
from_date : DATE
to_date : DATE
dept_manager
emp_no : INTEGERPK, FK → employee
dept_no : TEXTPK, FK → department
from_date : DATE
to_date : DATE
title
emp_no : INTEGERPK, FK → employee
title : TEXTPK
from_date : DATEPK
to_date : DATE
salary
emp_no : INTEGERPK, FK → employee
amount : INTEGER
from_date : DATEPK
to_date : DATE

テーブル間の関連: employee ←1:N→ dept_emp ←N:1→ department，employee ←1:N→ dept_manager ←N:1→ department，employee ←1:N→ title，employee ←1:N→ salary

出典: MySQL Employees Sample Database
https://dev.mysql.com/doc/employee/en/sakila-structure.html より引用，SQLite版（bytebase/employee-sample-database）に合わせて表記．

第６章　演習準備

本演習では Python とテキストエディタを使用する．Python のコードはテキストエディタで作成し，ファイルとして保存してから実行する．Python の起動方法やプログラムの作成・実行方法が分からない場合は，先に「Windows環境でのPython：基本とプログラムの作成・実行」を参照すること．

掲載されているPythonコードは，Windows 上の Python および Google Colab のいずれの環境でも動作する．Windows で実行する場合は，下記の「Python 3.12 のインストール」を展開し，手順に従いインストールすること．Google Colab で実行する場合は，sqlite3，urllib.request はいずれも Python の標準ライブラリであり，あらかじめ利用可能であるため追加インストールは不要である．

Python 3.12 のインストール

Pythonのインストールを行い、Pythonのプログラムを実行する環境を整える。扱う環境は、Windows搭載パソコンである。金子研究室では、Python 3.12.10を推奨する。

[Windows での Python 3.12 のインストール手順を見るには、ここをクリック]

Windows での Python 3.12 のインストール

以下のいずれかの方法でPython 3.12をインストールする。Pythonがインストール済みの場合、この手順は不要である。

方法 1：winget によるインストール

【インストールコマンドの実行方法】

管理者権限でコマンドプロンプトを起動する（手順：Windowsキーまたはスタートメニュー → cmd と入力 → 右クリック → 「管理者として実行」）。そして、コマンド全体をコマンドプロンプトにコピー＆ペーストする。

--scope machine を指定することで、システム全体（全ユーザー向け）にインストールされる。このオプションの実行には管理者権限が必要である。インストール完了後、コマンドプロンプトを再起動するとPATHが反映される。

REM Python 3.12 をシステム領域にインストール
winget install --id Python.Python.3.12 -e --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 Include_test=0 Include_pip=1 Include_launcher=1 InstallLauncherAllUsers=1 TargetDir=\"C:\Program Files\Python312\""

REM Python と Scripts を PATH 先頭に追加
powershell -NoProfile -Command "$p='C:\Program Files\Python312'; $s=\"$p\Scripts\"; $c=[Environment]::GetEnvironmentVariable('Path','Machine'); if((Test-Path $p) -and (';'+$c+';' -notlike \"*;$p;*\") -and (';'+$c+';' -notlike \"*;$s;*\")){[Environment]::SetEnvironmentVariable('Path',\"$p;$s;$c\",'Machine')}"

方法 2：インストーラーによるインストール

  1. Python公式サイト（https://www.python.org/downloads/）にアクセスし、「Download Python 3.x.x」ボタンからWindows用インストーラーをダウンロードする。
  2. ダウンロードしたインストーラーを実行する。
  3. 初期画面の下部に表示される「Add python.exe to PATH」にチェックを入れてから「Customize installation」を選択する。このチェックを入れ忘れると、コマンドプロンプトから python コマンドを実行できない。
  4. 「Install Python 3.xx for all users」にチェックを入れ、「Install」をクリックする。

インストールの確認

コマンドプロンプトで以下を実行する。

python --version

バージョン番号（例：Python 3.12.x）が表示されればインストール成功である。「'python' は、内部コマンドまたは外部コマンドとして認識されていません。」と表示される場合は、インストールが正常に完了していない。

第７章　使用するデータベースについて

Employees Sample データベース（SQLite版）

このデータベースは，MySQL Employees Sample Database を SQLite 形式に変換したものであり，GitHub 上の bytebase/employee-sample-database リポジトリで公開されている．出典と著作権表示は次のとおりである．

データの出典: MySQL Employees Sample Database（原著者: Fusheng Wang, Carlo Zaniolo．リレーショナルスキーマ: Giuseppe Maxia．データ変換: Patrick Crews）

This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.

SQLite adaptation: bytebase/employee-sample-database (MIT License)

第８章　演習1．Employees Sample データベース（SQLite版）のダウンロード

手順

  1. 作業ディレクトリ（例：C:\work）を作成し，コマンドプロンプトで以下のように移動する．Google Colab を使用する場合はこの手順は不要である． 
    cd C:\work
  2. 以下のコードを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．Google Colab を使用する場合はセルにコピーする． 
    import urllib.request

url = "https://raw.githubusercontent.com/bytebase/employee-sample-database/main/sqlite/dataset_small/employee.db"
urllib.request.urlretrieve(url, "employee.db")
  3. 作業ディレクトリに employee.db が作成されていることを確認する．

ヒント

考察ポイント

第９章　演習2．データベースの内容確認

手順

  1. 以下のコードを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）．employee.db と同じディレクトリで実行する． 
    import sqlite3

conn = sqlite3.connect('employee.db')

result = conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
for row in result:
    print(row)
print()

for table_name in ['department', 'dept_manager', 'dept_emp', 'employee', 'title', 'salary']:
    print(table_name)
    result = conn.execute(f"PRAGMA table_info({table_name})").fetchall()
    for row in result:
        print(row)
    print()

conn.close()
  2. テーブル名の一覧と，各テーブルの列名・データ型・主キー情報が表示されることを確認する．

ヒント

考察ポイント

第10章　演習3．単一テーブルへの問い合わせ

手順

  1. 以下のコードを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）． 
    import sqlite3

conn = sqlite3.connect('employee.db')

print('--- department ---')
result = conn.execute("SELECT * FROM department").fetchall()
for row in result:
    print(row)

print('--- dept_manager ---')
result = conn.execute("SELECT * FROM dept_manager").fetchall()
for row in result:
    print(row)

print('--- salary (amount > 120000) ---')
result = conn.execute("SELECT * FROM salary WHERE amount > 120000").fetchall()
for row in result:
    print(row)

conn.close()
  2. Windows で実行する場合は，コマンドプロンプトで以下を実行する． 
    python a.py

ヒント

考察ポイント

第11章　演習4．結合問い合わせ

手順

  1. 以下のコードを実行する（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）． 
    import sqlite3

conn = sqlite3.connect('employee.db')

print('--- salary JOIN employee (amount > 120000) ---')
result = conn.execute("""SELECT *
    FROM salary, employee
    WHERE salary.emp_no = employee.emp_no
    AND amount > 120000""").fetchall()
for row in result:
    print(row)

print('--- salary JOIN title (amount > 120000) ---')
result = conn.execute("""SELECT *
    FROM salary, title
    WHERE salary.emp_no = title.emp_no
    AND amount > 120000""").fetchall()
for row in result:
    print(row)

conn.close()
  2. Windows で実行する場合は，コマンドプロンプトで以下を実行する． 
    python a.py

ヒント

考察ポイント