SQL - SQLite - 賢いテーブル設計 - 正規化の理由 - select, where句, ワイルドカード, like
Head First SQL ―頭とからだで覚えるSQLの基本、 Lynn Beighley(著)、 佐藤 直生(監訳)、 松永 多苗子(翻訳)、 オライリージャパンの 4章(賢いテーブル設計 - 正規化の理由)、p.161(自分で考えてみよう)の解答を求めてみる。
fish.sql
drop table if exists fish_info;
create table fish_info (
common text,
species text,
location text,
weight text
);
create table if not exists fish_records (
first_name text,
last_name text,
common text,
location text,
state text,
weight text,
date text
);
insert into fish_info values
('バス、オオクチバス', 'M. salmoides', 'ジョージア州モンゴメリー湖', '10.2kg'),
('ウォールアイ', 'S. vitreus', 'テネシー州オールドピッコリー湖', '11.3kg'),
('マス、カットスロートトラウト', 'O. Clarki', 'ネバダ州ピラミッド湖', '18.5'),
('スズキ、イエローパーチ', 'P. Flavescens', 'ニュージャージー州ボーデンタウン', '1.8kg'),
('ブルーギル', 'L. Macrochirus', 'アラバマ州カトナ湖', '1.9kg'),
('ガー、ロングノーズガー', 'L. Osseus', 'テキサス州トリニティ川', '22.9kg'),
('グラッピー、ホワイトクラッピー', 'P. annularis', 'ミシシッピ州エニッドタム', '2.4kg'),
('カワカマス、クサカワカマス', 'E. americanus', 'インディアナ州デュワート湖', '0.5kg'),
('ゴールドフィッシュ', 'C. auratus', 'カリフォルニア州ホッジス湖', '2.8kg'),
('サケ、マスノスケ', 'O. Tshowytscha', 'アラスカ州ケナイ川', '44.2kg');
insert into fish_records values
('ジョージ', 'ペリー', 'バス、オオクチバス', 'モンゴメリー湖', 'ジョージア州', '10,2kg', '1932/6/2'),
('マブリー', 'ハーパー', 'ウォールアイ', 'オールドヒッコリー湖', 'テネシー州', '11.3kg', '1960/8/2'),
('ジョン', 'スキマホーン', 'マス、カットスロートトラウト', 'ピラミッド湖', 'ネバタ州', '18.5', '1925/12/1'),
('C.C.', 'アボット', 'スズキ、イエローパーチ', 'ボーデンタウン', 'ニュージャージー州', '1.8kg', '1865/5/1'),
('T.S.', 'ハドソン','ブルーギル', 'ケトナ湖', 'アラバマ州', '1.8kg', '1950/4/9'),
('タウンセンド', 'ミラー', 'ガー、ロングノーズガー','トリニティ川', 'テキサス州', '22.9kg', '1954/7/30'),
('フレッド', 'プライト', 'クラッピー、ホワイトクラッピー', 'エニッドダム', 'ミシシッピ州', '2.4kg', '1957/7/31'),
('マイク', 'バーグ', 'カワカマス、クサカワカマス', 'デュワート湖', 'インディアナ州', '0.5kg', '1990/6/9'),
('フローレンティーノ', 'アベナ', 'ゴールドフィッシュ', 'ホッジス湖', 'カリフォルニア州', '2.8kg', '1996/4/17'),
('レス', 'アンダーソン', 'サケ、マスノスケ', 'ケナイ川', 'アラスカ州', '44.18kg', '1985/5/17');
コード
sample1.py
#! /usr/bin/env python3
import sqlite3
con = sqlite3.connect('fish.db')
cur = con.cursor()
with open('fish.sql') as f:
cur.executescript(f.read())
print('fish_info')
sql = '''select * from fish_info'''
cur.execute(sql)
for row in cur.fetchall():
print(row)
print('select')
sql = """
select * from fish_info
where location like 'ニュージャージー州%'
"""
cur.execute(sql)
for row in cur.fetchall():
print(row)
print('fish_records')
sql = '''select * from fish_records'''
cur.execute(sql)
for row in cur.fetchall():
print(row)
print('select')
sql = """
select * from fish_records
where state = 'ニュージャージー州'
"""
cur.execute(sql)
for row in cur.fetchall():
print(row)
cur.close()
con.close()
入出力結果(Terminal, Zsh)
% ./sample1.py
fish_info
('バス、オオクチバス', 'M. salmoides', 'ジョージア州モンゴメリー湖', '10.2kg')
('ウォールアイ', 'S. vitreus', 'テネシー州オールドピッコリー湖', '11.3kg')
('マス、カットスロートトラウト', 'O. Clarki', 'ネバダ州ピラミッド湖', '18.5')
('スズキ、イエローパーチ', 'P. Flavescens', 'ニュージャージー州ボーデンタウン', '1.8kg')
('ブルーギル', 'L. Macrochirus', 'アラバマ州カトナ湖', '1.9kg')
('ガー、ロングノーズガー', 'L. Osseus', 'テキサス州トリニティ川', '22.9kg')
('グラッピー、ホワイトクラッピー', 'P. annularis', 'ミシシッピ州エニッドタム', '2.4kg')
('カワカマス、クサカワカマス', 'E. americanus', 'インディアナ州デュワート湖', '0.5kg')
('ゴールドフィッシュ', 'C. auratus', 'カリフォルニア州ホッジス湖', '2.8kg')
('サケ、マスノスケ', 'O. Tshowytscha', 'アラスカ州ケナイ川', '44.2kg')
select
('スズキ、イエローパーチ', 'P. Flavescens', 'ニュージャージー州ボーデンタウン', '1.8kg')
fish_records
('ジョージ', 'ペリー', 'バス、オオクチバス', 'モンゴメリー湖', 'ジョージア州', '10,2kg', '1932/6/2')
('マブリー', 'ハーパー', 'ウォールアイ', 'オールドヒッコリー湖', 'テネシー州', '11.3kg', '1960/8/2')
('ジョン', 'スキマホーン', 'マス、カットスロートトラウト', 'ピラミッド湖', 'ネバタ州', '18.5', '1925/12/1')
('C.C.', 'アボット', 'スズキ、イエローパーチ', 'ボーデンタウン', 'ニュージャージー州', '1.8kg', '1865/5/1')
('T.S.', 'ハドソン', 'ブルーギル', 'ケトナ湖', 'アラバマ州', '1.8kg', '1950/4/9')
('タウンセンド', 'ミラー', 'ガー、ロングノーズガー', 'トリニティ川', 'テキサス州', '22.9kg', '1954/7/30')
('フレッド', 'プライト', 'クラッピー、ホワイトクラッピー', 'エニッドダム', 'ミシシッピ州', '2.4kg', '1957/7/31')
('マイク', 'バーグ', 'カワカマス、クサカワカマス', 'デュワート湖', 'インディアナ州', '0.5kg', '1990/6/9')
('フローレンティーノ', 'アベナ', 'ゴールドフィッシュ', 'ホッジス湖', 'カリフォルニア州', '2.8kg', '1996/4/17')
('レス', 'アンダーソン', 'サケ、マスノスケ', 'ケナイ川', 'アラスカ州', '44.18kg', '1985/5/17')
select
('C.C.', 'アボット', 'スズキ、イエローパーチ', 'ボーデンタウン', 'ニュージャージー州', '1.8kg', '1865/5/1')
%