すぐに忘れる脳みそのためのメモ

ラベル SQL の投稿を表示しています。すべての投稿を表示

2011年8月5日金曜日

Excel, LibreOffice, SQL におけるワイルドカード (*) の意味

1. Excel でワイルドカード

Excel で文字データを入力した。入力中、不明なデータについては、半角で

?

とした。後になって、不明なデータを検索しようと `?’ を検索。しかし、上手くヒットしない。ただし、全角の

？

で検索すると、半角の ? がヒットした。

なぜなら、Excel では、半角の `?’ が任意の一文字を表すため。また、全角の `？’ は、文字通りクエッションマークとして認識され、デフォルトでは半角と全角を区別しないことにより、半角の `?’ も検索対象となるため。

「*」と「?」を検索する：Excel即効テクニックによると、

Excelでデータを検索するときに文字列の一部が分からなくても、ワイルドカードと呼ばれる特別な記号「*」と「?」を使って検索できるのはご存知の通り。「*」は任意の文字列を表し「?」は任意の1文字を表す。…

「*」と「?」を検索文字として指定するときは「~*」「~?」のように「~」を付けて入力すればよい

正規表現 – Wikipedia の書き方とは違うので気をつける必要がある。

2. LibreOffice で正規表現による検索

これに対して、LibreOffice では、正規表現で検索できる。

Writer/Using Wildcards in Text Searches/ja - LibreOffice Help によると、

編集 → 検索と置換を選択します。

ダイアログを展開するには、詳細オプションをクリックします。

正規表現チェックボックスを選択します。

素直に書けるので、検索するときに間違えることはない。

任意の 1 文字を表すワイルドカードは、ピリオド記号 (.) です。

直前の文字の任意回数の繰り返し (ゼロ回を含む) を示すワイルドカードは、アスタリスク記号です。

LibreOffice の方がシンプルで好きだなぁ。

3. SQL との比較

ちなみに、SQL では、任意の一文字を表すのに `_’ を用いる。

SQLの基礎「SELECT」文を覚えようによると、

（4） LIKE条件
文字の検索条件を指定します。ここで、％と＿（アンダースコア）は特殊な意味が割り当てられており、％は「任意の文字数の任意の文字」、＿は「1文字の任意の文字」を表します。

Access は `?’, `_’ が任意の一文字を表す。

あいまいな条件抽出 - LIKE演算子： SQL入門講座によると、

アスタリスク (*)
パーセント(%)
0文字以上の任意の文字列を表す。

疑問符（?）
アンダスコア(_)
任意の一文字を表す。

普段から使ってないと、混乱する。 (+_+)

2010年11月12日金曜日

Tutorial D の拡張と要約 - 値の計算と集約

「Tutorial D でリレーショナル代数」のつづき

拡張

既存の属性を元に計算した結果を関係に追加する。 SQL では SELECT 句においてカラムに演算子を適用することに相当。Date によると、

「オリジナルで定義されたリレーショナル代数の範囲を超えてしまう」

(データベース実践講義, p101より)

ために「拡張」と呼ばれる。

例えば、「人」に対して、

年齢の 2 倍
男性であるかどうか？

となる属性を関係に追加したい場合は、

extend persons 
  add(age * 2 as DoubleAge, gender = 1 as isMale)

(対象のデータベースはこちら)

拡張の後に制限

上記の書き方を見ると SQL の方が簡単に思えるが、Tutorial D の拡張により返されるのは「関係」であるため、返される値に対してスムーズに制限を適用できる。

先ほどの結果に対して、

年齢を 2 倍したら 50 以上で、かつ、男性である人

を抽出したい場合、

extend persons
  add(age * 2 as DoubleAge, gender = 1 as isMale)
  where DoubleAge >= 50 and isMale

と書くことができる。

要約

「人」の要素数を知りたい場合は、

summarize persons 
  add(count() as pCount)

最高齢の人を求めるには、対象となる属性 age を max に与える。

summarize persons 
  add(max(age) as pMax)

定義されている要約は summary を参照。

特定の基準で要約 - by

SQL で言う GROUP BY に相当するもの。

例えば、性別ごとの人数を得るには、

summarize persons 
  by {gender}
    add(count() as pCount)

per を使っても上記と同じ結果が得られる。

summarize persons
  per(persons {gender})
    add(count() as pCount)

ただし、これだと書くのが面倒なので、上記のように by が定義されているのかな。

他の関係変数と組み合わせる - per

per は別の関係変数と組み合わせたときに有用。

例えば、「割当て」を「人」の id ごとにカウントしたい場合に by を使うと、

summarize assignments 
  by {p_id}
    add(count() as pCount)

当然ながら、「割当て」がされてない人は表示されない。

これに対し、「人」を基準にして「割当て」を要約すると、

summarize assignments 
  per((persons rename(id as p_id)) {p_id})
    add(count() as pCount)

「割当て」がされてない人も表示される。

by と per の定義については、summarize の per_or_by を参照。

「Tutorial D で外部キーの定義 - 整合性制約で記述」につづく

2010年11月11日木曜日

Tutorial D でリレーショナル代数

「Tutorial D で関係変数の定義と代入」のつづき

「データベース実践講義」における “5.2 オリジナル演算子” (pp.92-99) の練習。

「SQL の相関サブクエリ (2)」で利用したデータベースと同じものを使う。

Tutorial D での定義は以下通り。

gist: 671916 – GitHub

削除するときはこちらより。

制限

特定の条件に一致するタプルを含む関係を返す。例えば、男性を取得するには、

persons where gender = 1

111-11-2010CropperCapture[1]

SQL の WHERE 句に相当し、同じキーワード where を使うので覚えやすい。

射影

特定の属性を含む関係を返す。人の名前と年齢を取得するには、

persons { name, age }

211-11-2010CropperCapture[2]

SQL の SELECT 句に相当する。こちらは位置も表現も異なる。

SQL との違いは、以下を評価するとハッキリとする。

persons { gender }

リレーショナル代数で「関係」が返されるわけだから、重複はない。 SQL では SELECT 句で DISTINCT を指定することに相当。

結合

「人」と「割当て」を結合する。 SQL の自然結合に相当。

persons rename(id as p_id) join assignments

ただし、結合には同じ属性名が使われるので、結合前に属性名を変更するため rename 演算子を利用する。

ここでは assignments で「人」を示すための属性として p_id を利用しているので、上記では persons の属性 id を p_id に変更している。

次に「人」「割当て」「グループ」の 3 つを結合し、「人の名前、グループ名、日付」を表示する。

(persons rename(id as p_id)
  join assignments
  join groups rename(id as g_id, name as g_name))
    {name, g_name, date}

複数の属性名を一気に変更する場合は、rename 演算子において prefix を利用する。

(persons rename(prefix "" as "p_") 
  join assignments
  join groups rename(prefix "" as "g_"))
    {p_name, g_name, date}

結合は、join { A, B, C … } のように書くことができる。

join { persons rename(prefix "" as "p_")
     , assignments
     , groups rename(prefix "" as "g_")}
  {p_name, g_name, date}

交わり

「人」の属性 id と「割当て」の属性 p_id の交わりを求める。

(persons rename(id as p_id)) {p_id} 
  intersect assignments {p_id}

交わりは、属性が全て同じ関係の結合と見なせるので、join を使っても書ける。

(persons rename(id as p_id)) {p_id} 
  join assignments {p_id}

デカルト積

「人」の名前と「グループ」名のすべての組み合わせを求めたい。

デカルト積は、共通属性がない関係の結合と見なせるので、結合したい関係の属性名が重ならないように rename 演算子を用いる。このとき、prefix を利用すると便利。

(persons rename(prefix "" as "p_")
  join groups rename(prefix "" as "g_"))
    {p_name, g_name}

SQL では FROM 句にテーブル名を並べることに相当。

半結合

「グループ」に「割当て」られている「人」を抽出したい。

persons semijoin assignments

データベース実践講義 (p94) によると、

(半結合についてこれまでに聞いたことがないかもしれないが、実がとても重要な演算子である)。定義は次の通り。 r と s の半結合は、 r と s の結合であり、結果は r の属性に射影される。

semijoin の代わりに matching と書くこともできる。こちらの方が言葉としては理解しやすいかな。

SQL では exists 述語を利用して記述できる。

和

「人」と「人」の和。

 (persons where id = 1) union 
  relation {tuple {id 100, name "Gonzou", gender 1, age 100}}

直和

共通のタプルがあった場合に、エラーが出力される和も用意されている。

(persons where id <= 3)
  d_union (persons where id >= 3)

この場合、「人」の id が 3 の人が共通してるのでエラーとなる。

差

特定の「人」を取り除きたい場合。

persons { name } minus
  relation { tuple { name "Hanko" }
           , tuple { name "Jirou" }
           , tuple { name "Sadayo" }}

半差

先ほどの「半結合」とは対照的な「半差」。 SQL では not exists 述語に相当する演算子。

persons rename(id as p_id) 
  semiminus assignments

差は、属性が全て共通している関係の半差と見なせる。

persons { name } semiminus
 relation { tuple { name "Hanko"}
          , tuple { name "Jirou"}
          , tuple {name "Sadayo" }}

その他

「商」はどうやって書いたらいいんだろう。。 (+_+)

「Tutorial D の拡張と要約 - 値の計算と集約」へつづく

2010年11月10日水曜日

Tutorial D で関係変数の定義と代入 - Rel を利用して

SQL に対する批判

C.J.Date の「データベース実践講義」には、SQL に対する批判が随所に書かれている。

例えば、1 章の「概要」だけ見ても、初っ端から次のようにある。

リレーショナルモデルに関する知識が SQL の知識にのみ基づくものであるならば、リレーショナルモデルを十分に理解しているとは言えない。 (p1)

既存の実装に対しても手厳しい。

SQL をリレーショナルに使用することはもちろん可能だが(...)、既存の実装は完璧というにはほど遠いため、そのように使用するとパフォーマンスが大幅に低下することがある。その場合には、「完全にリレーショナル」ではない方法に頼らざるを得ないだろう (同上, p4)

一貫して主張されているのが null の否定。

実体整合性のルールには、実は問題がある。それは、筆者が「null」の概念を完全に否定しているからである。すなわち、null はリレーショナルモデルに存在する意味がないというのが、筆者のこだわりなのである。(同上, p7)

また、リレーショナルモデルにおける「関係」が SQL で正しく扱われていないことも指摘。

SQL では行の重複が許されていること。

... 関係に重複するタプルが含まれることはあり得ない。なぜなら、本体はタプルの集合であり、数学における集合に重複する要素が含まれることはないためである。ところで、これは SQL には当てはまらない。知ってのとおり、SQL のテーブルには重複する行が含まれていてもかまわない。このため、一般的には、SQL のテーブルは関係ではない。 (同上, pp.14-15)

これは重複を許すと、RDB はリレーショナル代数に基いているのにも関わらず、「関係」に対する演算が「関係」でなくなるため、代数ではなくなってしまうということかな。

列に順序があること。

... 関係の属性についても、左から右への順序付けがあるわけではない。なぜなら、見出しも数学的な集合だからである。... SQL のテーブルの列は、左から右へ順序付けされている。(このこともまた、SQL のテーブルが一般的に関係でない理由の一つである)。 (同上, p15)

テーブルとビューについては、扱う「関係」という点から見れば、本質的には変わらないことが述べられている。

... こうした状況は SQL 規格 (...) からもうかがわれるほどで、一般的には (...) 「テーブルとビュー」という表現が使われている。当然ながら、そうした表現が使われたのでは、誰もがテーブルとビューが別のものであると勘違いし、「テーブル」が物理的なもので「ビュー」が実体のないものであると思い込んでしまうだろう。... ビューは「通常の関係」なので、通常の関係で実行できる処理は(少なくともリレーショナルモデルでは)ビューでも同じように実行できる。(同上, p17)

リレーショナルモデルで扱うのは「関係」という値と、それに対する演算であって、実装の問題は別腹と考えるのはシンプルですっきりしている。

Tutorial D

… 本書では可能な限り SQL を使って例を示すが、このように何らかの理由でそれが不可能である場合には、 Tutorial D というきわめて直感的な (そして真のリレーショナル) 言語を使用する。 (同上, p19)

ということで、真のリレーショナルと言われる Tutorial D を試してみる。

第3のマニフェスト – Wikipedia によると、

第3のマニフェストは、デイトとダーウェンが考案したデータベース言語 Tutorial D を使って、関係モデルを説明している。 Tutorial D は、データベース言語仕様 D の実装の一つである。 D は、関係データベースのデータベース言語が満たすべき要件の集合である。

http://dbappbuilder.sourceforge.net/Rel.php

Rel via kwout

実装として挙げられている中で、お手軽に試せるのが Java で実装された Rel 。

SourceForge.net より、ダウンロードしてインスール。

スタート > すべてのプログラム > Rel > DBrowser を起動。

sys.Catalog

を入力して Evaluate すると、データベースに定義されている変数の一覧が表示される。

Rel に慣れるために

以下ざっと目を通す。

簡単な計算を評価すると、

1 + 2

`3’ と結果が表示される。

タプル、関係の評価

タプルを評価すると、

tuple {name "Tarou",  age 10 }

結果は、

「関係」を評価すると、

relation {
  tuple {name "Tarou",  age 10 },
  tuple {name "Hanako", age 20 },
  tuple {name "Jirou",  age 30 }}

結果は、

Character, Integer と型を指定しなくても、属性の型が表示された。

式と文

上記の評価では、最後に `;’ を付けないことに注意。

Rel / Tutorial D Grammar を見ると、式は、

evaluate ::= ( compound_statement_body ";" )? expression ( <EOT> | <EOF> )

これに対して、文は末尾に `;’ を付ける。

statement := statement_body ";"

関係変数の定義と代入

変数の定義

SQL で最初にテーブルを作成するときの要領で、「関係」を値に持つ関係変数 (relvar) を定義してみる。

例えば、以下の表にある「人」型の関係変数を定義。

var persons base
  relation { id     integer
           , name   character
           , gender integer
           , age    integer}
    key {id};

変数 persons は relation で指定した型となる。ここでは組み込みの型のみを使って定義した。

末尾に `;’ を付けるのを忘れずに。

値を変数に代入

次に、関係変数を定義したので、変数に値である「関係」を代入する。

persons :=
  relation{
    tuple { id 1, name "Tarou",   gender 1, age 10 },
    tuple { id 2, name "Hanako",  gender 2, age 20 },
    tuple { id 3, name "Jirou",   gender 1, age 30 },
    tuple { id 4, name "Saburou", gender 1, age 40 },
    tuple { id 5, name "Akemi",   gender 2, age 8  },
    tuple { id 6, name "Sadayo",  gender 2, age 70 },
    tuple { id 7, name "Hiroko",  gender 2, age 15 }};

先ほどの relation が型を返すのに対して、この relation は値を生成する。

関係変数の内容を表示させたい場合は、

persons

と入力して Evaluate する。 `;’ を末尾に付けない。

変数の削除

作成した関係変数を削除したい場合は、

drop var persons;

「Tutorial D でリレーショナル代数」へつづく

SQL の相関サブクエリ – filter 関数から考える

2010年11月6日土曜日

SQL の相関サブクエリ (5) – forall (∀) の exists (∃) への読み替え

「SQL の相関サブクエリ (4) - SELECT 句で使う」のつづき

今回も前回と同じデータベースを使う。構造は以下の通り。

ここから、

すべての 「グループ」に「割当て」られたことがある「人」

を抽出したい。

データベースの内容をオブジェクト図で示すと、一見して目的の人がわかる。

Tarou だけが 3 つのグループすべてに「割当て」られた。 Jirou, Saburou は割当てがされてるものの 1 グループに対してのみ。

では、これを SQL で書くにはどうしたらいいのか？

これまでと同じように、特定の条件に一致する「人」を抽出したいので、

select *
from persons
where exists (select *
              from ...

と書き始めてみたものの、

「すべての x は p である」

という形式をどのように表現するのだろう？

exists 述語は、サブクエリで返される結果が一つでもあれば True となる。

「すべての…」

を表現してるのではなく、

「 p である x が少なくとも一つ存在する」

ということを表すために用いる述語なので、このように使用するのは不適切。

全称命題と存在命題

全称命題

http://ja.wikipedia.org/wiki/%E5%85%A8%E7%A7%B0%E8%A8%98%E5%8F%B7

全称記号
- Wikipedia
via kwout

ところで、「すべての x は p である」という表現は、「論理記号」によると、

「任意のx に対し
(＝すべてのx について・どのようなxをとっても)
P(x)である」
（ただし、P(x)はxに関するある性質・条件を表す）
は、
論理記号「 ( ∀x ) ( P(x) ) 」で表される。 …

全称命題を表す論理記号「∀」を
全称記号、全称量化子、普遍量化universal quantifierなどと呼ぶ。

存在命題

http://ja.wikipedia.org/wiki/%E5%AD%98%E5%9C%A8%E8%A8%98%E5%8F%B7

存在記号 >
- Wikipedia
via kwout

これに対して、SQL の EXISTS 述語に対応している「論理記号」は、

「 P (x)を満たすxが（少なくとは一つは）存在する」
（ただし、P(x)はxに関するある性質・条件を表す）
は、
論理記号「 ( ∃x ) ( P(x) ) 」で表される。 …

存在命題を表す論理記号「∃」を
存在記号、存在量化子existential quantifierなどと呼ぶ。

「SQL の相関サブクエリ (2) – EXISTS 述語」で見たように、

「グループ」に「割当て」られた「人」

を抽出したい場合、以下のように記述した。

select * 
from persons as p
where exists (select *
              from assignments as a
              where p.id = a.p_id)

求めるものを言い換えると、

「グループ」に「割当て」られたことがある「人」

「グループ」に「割当て」られたことが少なくとも一度ある 「人」

繰り返すが、EXISTS 述語は一行でも結果を返せば True となる。

日常的な言葉の表現における言い換え

「すべての x は p である」という命題は別の表現で表すことができる。

全称命題 - Wikipedia によると、

全称命題は、存在命題の否定と論理的に等値である。それゆえ、「全ての牛は空を飛ぶ」という命題を主張することは、「少なくとも一頭は空を飛べない牛がいる」という命題を否定することと等値である。

例えば、日常的な言葉の使い方で考えるなら、

「すべてのプログラマは面倒なことが嫌い」

の意味は、

「面倒なことが嫌いでないプログラマはいない」

と言うのと同じ。

もう少し言い換え、上記の引用と同じ表現にするなら、

『少なくとも一人は面倒なことが嫌いでないプログラマがいる』を否定すること

に等しい。

これを記号を用いて表すなら、全称記号 – Wikipedia によると、

「∀xPx」は存在記号と否定記号とを用いて、「￢∃x￢Px」と表現することもできる。「￢∃x￢Px」は「P でないような x は存在しない」という意味だから、これはすなわち「すべての x は Pである」ということである。

「すべての…」を SQL の EXISTS 述語で書く

プログラマのためのSQL 第2版 (pp.193-194) には、上記のような「すべての…」という表現を変換して SQL で記述する方法が述べられている。

「すべての人間は死ぬ」が、「死なない人間はいない」を包含するというのは誰もが賛成するでしょう …

… 「すべてのセールスマンは嘘つきである」という述語を、 SQL の EXISTS 述語を使って書きたいかもしれません。これについては、今議論していた変換ルールを使います。
NOT EXISTS (SELECT *
            FROM Personnel AS P1     
            WHERE P1.job = 'Salesman'    
              AND P1.name NOT IN (SELECT L1.name      
                                  FROM Liars AS L1));
これを平易にいえば、「嘘つきでないセールスマンはいない」ということです。

この SQL を順に読んで行くと、

冒頭の `NOT EXISTS’ は、以降で指定するものが「存在しない」ということを意味し、
何が存在しないかと言えば、とある「人」なんだけれど、
その人の仕事は「セールスマン」であり、
かつ、名前が、「嘘つき」の名前には含まれてない人である

という流れで書かれている。

まとめると、

「すべてのセールスマンは嘘つきである」を
「嘘つきでないセールスマンはいない」に言い換え、
SQL の定義で言えば、
- いないですよ
- とある「人」で
- セールスマンであり
- かつ、嘘つきでない人

により表現する。

forall (∀) と exist (∃) の変換

C.J.Date の「データベース実践講義」 (pp.204-205) では、forall (∀) と exist (∃) による表現を相互に変換できることが述べられている。

以下の文は、

EXISTS x ( p ( x ) )

論理的には以下の文に等しい (この場合、述語 p は x に加えてほかのパラメータを含んでいてもよい。

NOT ( FORALL x ( NOT ( p ( x ) ) ) )

つまり、「p である x が少なくとも一つ存在する」は、「『すべての x は p ではない』ということはない」に等しい。

同様に、以下の文は

FORALL x ( p ( x ) )

論理的には以下の文に等しい (この場合も、述語 p は x に加えてほかのパラメータを含んでいてもよい。)

NOT ( EXISTS x ( NOT ( p ( x ) ) ) )

これはプログラマのためのSQL 第2版で書かれていたこと同じ。

繰り返しになるが、「すべての x は p である」は、「『ある x は p ではない』ということはない」に等しい。

この説明の上で、C.J.Date はEXIST しかサポートしていない SQL に対して、次のように述べている。

… EXISTS で表わすほうが「自然な」問題と、FORALL で表わすほうが「自然な」問題があるからだ。たとえば、SQL は EXISTS をサポートするが、FORALL をサポートしない。結果として、SQL で表現しようとすると非常にやっかいなクエリが存在する。

(同上より)

この後に示されている例を参考にして、最初の問題、

すべての 「グループ」に「割当て」られたことがある「人」

を SQL で表現する。

ただし、C.J.Date が以下のように強調していることを予め心得ておくこと。

単一否定でも十分に問題なのに(多くのユーザは理解するのに苦労する)、この SQL クエリのような二重否定はもってのほかである。

( 「データベース実践講義」、p205 より)

二重否定の SQL

さて、Date 曰く「もってのほか」な SQL を書いてみる。

日本語で言い換える

抽出したいのは、

すべての 「グループ」に「割当て」られたことがある「人」

これを言い換えるには、FORALL x ( P(x) ) を NOT ( EXISTS x ( NOT P(x) ) にすることを考えればよかった。

ここで P に相当するのが、

「グループ」に「割当て」られたことがある

だから、全体では、

『「グループ」に「割当て」られたことが少なくとも一度もない人』の否定

つまり、

『「グループ」に「割当て」られたことが少なくとも一度もない』ことがない「人」

… と書いたところで、何だかよくわからない日本語。 (@_@; これあってるのかな？

そのままで考える

もとい。日本語で考えず、そのまま考えた方が良さげ。

FORALL x ( グループに割当てられたことがある (x) )

この意味は、「すべてのグループに割当てられたことがある x」。

単純に変換のための式に当てはめると、

NOT ( EXISTS x ( NOT ( グループに割当てられたことがある (x) ) )

NOT ( EXISTS x ( グループに割当てられたことがない (x) ) )

対象は「人」なので、 SQL 風に書くなら、

人 WHERE FORALL x ( グループに割当てられたことがある (x) )

変換した場合、

人 WHERE NOT ( EXISTS x ( NOT ( グループに割当てられたことがある (x) ) )

人 WHERE NOT ( EXISTS x ( グループに割当てられたことがない (x) ) )

SQL で書く

次に SQL で書き直す。いきなり書くのは難しいので徐々に、上記を見ながら、

select *
from persons as p
where not exists(グループに割当てられたことがない)

次に、「グループに割当てられたことがない」の部分を書いて完成。

select *
from persons as p
where not exists (select *
                  from groups as g
                  where not exists (select *
                                    from assignments as a
                                    where a.g_id = g.id and 
                                          a.p_id = p.id))

動作をイメージする

上記 SQL の動作を確かめるため、各々の人ごとに固定してサブクエリを考える。

Tarou が割当てられていないグループを取得するには、

select *
from groups as g
where not exists (select *
                  from assignments as a
                  where a.g_id = g.id and
                        a.p_id = 1)

Tarou に割当てられていないグループはないので、抽出されるものはない。

Hanako の場合は、

select *
from groups as g
where not exists (select *
                  from assignments as a
                  where a.g_id = g.id and
                        a.p_id = 2)

Take, Ume グループが抽出される。

Saburou はどのグループにも割当てられていないので、

select *
from groups as g
where not exists (select *
                  from assignments as a
                  where a.g_id = g.id and
                        a.p_id = 4)

全てのグループが抽出される。

よって、割当てられていないグループがない Tarou だけが以下の SQL によって抽出される。

select *
from persons as p
where not exists(select *
		    from groups as g
		    where not exists (select *
                                   from assignments as a
                                   where a.g_id = g.id and
                                         a.p_id = p.id))

… とは言ったものの、パッと理解できないなぁ。。 (+_+)

FORALL 述語があれば…

もし、SQL に forall 述語があるなら、以下のように書けるのかな？

select *
from persons as p
where forall groups as g 
      exists (select *
              from assignments as a
              where a.g_id = g.id and
                    a.p_id = p.id))

ちなみに Tutorial D には exists だけでなく forall もあるので、以下のように書けるらしい。 (未確認)

persons where forall groups exists assignments 
(assginments.g_id = groups.id and
    assignments.p_id = persons.id)

Haskell で書く

Haskell で類似したものを書いてみる。わかりずらい二重否定を使うなら、

[p | p <- persons
   , null [g | g <- groups
             , null [a | a <- assignments
                       , a_g_id a == g_id g
                       , a_p_id a == p_id p]]]

( cf. gist: 645292 – GitHub )

「すべてのグループに割当てられたことがある人」を素直に書くなら、

[p | p <- persons
   , all (\g -> exists [a | a <- assignments
                          , a_g_id a == g_id g
                          , a_p_id a == p_id p]) 
         groups]

( cf. gist: 645292 – GitHub )

2010年11月2日火曜日

SQL の相関サブクエリ (4) - SELECT 句で使う

「SQL の相関サブクエリ (3) - 量化された比較述語 ALL, ANY」のつづき

これまでは WHERE 句において相関サブクエリを利用する例を見た。今回は SELECT 句で使う。

前回と同じデータベースを対象にして

例えば、

性別ごとに年齢の順位

を求めたい。

SELECT 句で相関サブクエリ

… とは言ったものの、いきなり書こうと思っても頭が混乱するのでゆっくりと。

まずは、「人」の属性に「全体の人数」を加えた結果を取得する。

select *, 
       (select count(*) 
        from persons) as num
from persons as p1

結果は、

+----+---------+--------+-----+------+
| id | name    | gender | age | num  |
+----+---------+--------+-----+------+
|  1 | Tarou   |      1 |  10 |    7 |
|  2 | Hanako  |      2 |  20 |    7 |
|  3 | Jirou   |      1 |  30 |    7 |
|  4 | Saburou |      1 |  40 |    7 |
|  5 | Akemi   |      2 |   8 |    7 |
|  6 | Sadayo  |      2 |  70 |    7 |
|  7 | Hiroko  |      2 |  15 |    7 |
+----+---------+--------+-----+------+
7 rows in set (0.00 sec)

次に、性別ごとに人数のトータルを求めるため、相関サブクエリを使う。

方法は、

各々の「人」ごとにその性別を同じくする集合を求め
その要素数を得る。

頭に思い浮かべたのは下図。

select *, 
       (select count(*) 
        from persons as p2
        where p2.gender = p1.gender) as num
from persons as p1

結果は、

+----+---------+--------+-----+------+
| id | name    | gender | age | num  |
+----+---------+--------+-----+------+
|  1 | Tarou   |      1 |  10 |    3 |
|  2 | Hanako  |      2 |  20 |    4 |
|  3 | Jirou   |      1 |  30 |    3 |
|  4 | Saburou |      1 |  40 |    3 |
|  5 | Akemi   |      2 |   8 |    4 |
|  6 | Sadayo  |      2 |  70 |    4 |
|  7 | Hiroko  |      2 |  15 |    4 |
+----+---------+--------+-----+------+
7 rows in set (0.00 sec)

最後に、順位を求めるために各々の「人」要素において、

自分の年齢よりも小さい人の人数をカウントして 1 を足す

ついでなので性別ごとに昇順に表示。

select *, 
       (select count(*) + 1
        from persons as p2
        where p2.gender = p1.gender and
              p2.age    < p1.age) as rank
from persons as p1
order by gender, age

結果は、

+----+---------+--------+-----+------+
| id | name    | gender | age | rank |
+----+---------+--------+-----+------+
|  1 | Tarou   |      1 |  10 |    1 |
|  3 | Jirou   |      1 |  30 |    2 |
|  4 | Saburou |      1 |  40 |    3 |
|  5 | Akemi   |      2 |   8 |    1 |
|  7 | Hiroko  |      2 |  15 |    2 |
|  2 | Hanako  |      2 |  20 |    3 |
|  6 | Sadayo  |      2 |  70 |    4 |
+----+---------+--------+-----+------+
7 rows in set (0.00 sec)

Haskell で書く

Haskell で類似したコード書いてみる。

順に考えるなら、最初は全体の人数を結果に追加。

[(p1, length [p2 | p2 <- persons]) | p1 <- persons]

次に、性別ごとに人数のトータルを追加。

[(p1, length [p2 | p2 <- persons
                 , gender p2 == gender p1]) | p1 <- persons]

最後に順位を求める。

[(p1, 1 + length [p2 | p2 <- persons      
                     , gender p2 == gender p1
                     , age p2 < age p1]) | p1 <- persons]

( cf. gist: 645292 – GitHub )

「SQL の相関サブクエリ (5) – forall (∀) の exists (∃) への読み替え」へつづく

SQL の相関サブクエリ (3) - 量化された比較述語 ALL, ANY

「SQL の相関サブクエリ (2) – EXISTS 述語」のつづき

量化

SQL をはじめて触ったとき、exists 述語と並んで直感的に理解しずらかったのが ALL や ANY を伴った比較。

WHERE 句において単体の値を比較するのは問題ないけれど、「すべての XXXX と比べてどうか？」とか、「少なくとも 1 つの XXXX と比べてどうなのか？」となるとイメージしずらくなる。

量化 – Wikipedia によると、

全ての人間の言語は、完全な数体系がない場合でも、量化を利用している（Wiese 2004）。例えば、日本語での例は次の通りである。

「全ての方針に目を通す必要がある」

「川を渡っている人のうち何人かが白い腕章をしている」

「私が話した人々のほとんどが、誰に投票するか決めていなかった」

「待合室の誰もが小沢氏に対する少なくとも1つの不満を持っていた」

「クラスの誰かが、私の出した全ての問題に答えられるはずだ」

「多くの人々は賢明である」

これらを量化を使わずに、複数の文の論理和や論理積で表す単純な方法は存在しない。例えば、「Aの方針に目を通す必要がある」かつ「Bの方針に目を通す必要がある」……などと続くことになる。

日常会話の中でこれらの言葉を使うのに抵抗はない。しかし、SQL で書こうとすると難しく感じることがしばしば。特に相関サブクエリを利用する場合に。

ALL, ANY の仕様

ところで、SQL-92 における「述語」は "BNF Grammar for ISO/IEC 9075:1992 - Database Language SQL (SQL-92)" によると以下の 9 つが列挙されている。

<predicate>    ::=
<comparison predicate>
     | <between predicate>
     | <in predicate>
     | <like predicate>
     | <null predicate>
     | <quantified comparison predicate>
     | <exists predicate>
     | <match predicate>
     | <overlaps predicate>

上記の「量化された比較述語」、

<quantified comparison predicate> ::= <row value constuctor> <comp op> <quantifier> <table subquery>

の一部として定義されているのが「量化子」 ALL, SOME または ANY のキーワード。

<quantifier>    ::=   <all> | <some>

<all>    ::=   ALL

<some>    ::=   SOME | ANY

値を比較するための演算子 <comp op> とサブクエリ <table subquery> と共に用いられるのがわかる。

サブクエリの結果が固定されている場合

前回と同じデータベースを対象にして ALL, ANY の使用例を考える。

ALL 述語

例えば、

すべての男性よりも年齢の高い女性

を抽出したいとする。

すべての男性の年齢を取得するには、

select age
from persons
where gender = 1

上記で抽出された「すべての年齢」と比較するために使う述語が ALL 。各々の「人」の年齢と「すべての男性の年齢」を比較する。

select *
from persons as p
where p.gender = 2 and
      p.age > all (select age
                   from persons
                   where gender = 1)

このサブクエリは、外側のクエリの値を参照していないので相関サブクエリではない。

上記の SQL を実行すると結果は、

+----+--------+--------+-----+
| id | name   | gender | age |
+----+--------+--------+-----+
|  6 | Sadayo |      2 |  70 |
+----+--------+--------+-----+
1 row in set (0.03 sec)

ただし、この場合 ALL 述語を使うよりも、素直に最大値と比較した方が効率は良いと思うけれど。

ANY 述語

次に、ALL と対照的な ANY 述語。

例として、

いずれかの男性よりも年齢の高い女性

を求める。

select *
from persons as p
where p.gender = 2 and
      p.age > any (select age
                   from persons
                   where gender = 1)

結果は、

+----+--------+--------+-----+
| id | name   | gender | age |
+----+--------+--------+-----+
|  2 | Hanako |      2 |  20 |
|  6 | Sadayo |      2 |  70 |
|  7 | Hiroko |      2 |  15 |
+----+--------+--------+-----+
3 rows in set (0.00 sec)

ALL, ANY いずれもサブクエリの値が固定されているので、割とイメージしやすい。

Haskell で all, any 述語を利用して書く

上記の SQL に類似したコードを Haskell で書くなら、

[p | p <- persons
   , gender p == 2
   , all (\p' -> age p > age p') 
         [p | p <- persons
            , gender p == 1]]

( cf. gist: 645292 - GitHub )

[p | p <- persons
   , gender p == 2
   , any (\p' -> age p > age p') 
         [p | p <- persons
            , gender p == 1]]

( cf. gist: 645292 – GitHub )

相関サブクエリにより要素ごとに結果が異なる場合

次は、要素によってサブクエリの結果が異なる場合を考える。

例えば、

同性の中で最高齢の「人」

を求めたいとする。

もし、ALL 述語を使うなら、

抽出したいのは、とある「人」なんだけれど、
その「人」はすべての同性の年齢以上である

というように意識して書けばよい。

select *
from persons as p1
where p1.age >= all (select age
                     from persons as p2
                     where p2.gender = p1.gender)

ここで注目するのは、外側のクエリにおける persons テーブルを指し示す名前 p1 と、サブクエリで persons テーブルを指し示す名前 p2 。同じテーブルに対して別名を付与している。

この意味は下図のように、p1 で指し示す「人」の要素に対して、p2 で指し示す同じく「人」の要素を組み合わせて評価するということ。

今回の場合は「同性」が条件なので、組み合わせの抽出条件として gender を用いている。

結果は、

+----+---------+--------+-----+
| id | name    | gender | age |
+----+---------+--------+-----+
|  4 | Saburou |      1 |  40 |
|  6 | Sadayo  |      2 |  70 |
+----+---------+--------+-----+
2 rows in set (0.09 sec)

Haskell で書くと…

毎度、Haskell で類似したコードを書くなら、

[p1 | p1 <- persons
    , all (\p -> age p1 >= age p)
          [p2 | p2 <- persons
              , gender p2 == gender p1]]

( cf. gist: 645292 – GitHub )

集約関数 max を使う場合

素直に最高齢を求めてから考えるなら、

select gender, max(age)
from persons as p
group by gender

により、性別ごとの最高年齢が得られるのを確認し、

+--------+----------+
| gender | max(age) |
+--------+----------+
|      1 |       40 |
|      2 |       70 |
+--------+----------+
2 rows in set (0.09 sec)

この結果と各々の「人」を比較して、gender と max(age) が一致する人を抽出すればいいので、

select *
from persons as p1
where exists (select *               
              from (select gender, max(age) as mage
                    from persons as p
                    group by gender) as m
              where m.gender = p1.gender and
                    m.mage   = p1.age)

しかし、全体としてはわかりずらいし、相関が上手く使えておらず冗長な感じ。 (+_+)

そこで、先ほど ALL で求めたときの真似をして次のように変更。

select *
from persons as p1
where age = (select max(p2.age)
             from persons as p2
             where p2.gender = p1.gender)

これでシンプルになった。

各々の要素に述語を適用するとき、比較対象をその要素の属性を用いて限定するということを意識すればよい。

Haskell で…

先ほどとほとんど変わらないけれど、Haskell で類似したコードを書くなら、

[p1 | p1 <- persons
    , age p1 == maximum [age p2 | p2 <- persons
                                , gender p2 == gender p1]]

( cf. gist: 645292 – GitHub )

「SQL の相関サブクエリ (4) - SELECT 句で使う」につづく

参考サイト

2010年10月31日日曜日

SQL の相関サブクエリ (2) – EXISTS 述語

「SQL の相関サブクエリ – filter 関数から考える」のつづき

前回、IN 述語と共に使われた相関サブクエリを、関数のネストとの比較を通して動作のイメージを考えた。今回はIN 述語以外の述語で相関サブクエリを使ってみる。

EXISTS 述語

まずは代表的な EXISTS 述語。

プログラマのためのSQL 第2版の第15章 EXISTS 述語 (p189) によると、

EXISTS 述語の意味はごく自然です。これは、空でない集合の検査です。もし、そのサブクエリーが何らかの行を返せば、結果は TRUE です。そして、さもなければ FALSE になります。 UNKNOWN が結果として返ることはありません。…

(太字は引用者による)

対象のデータベース

前回と同じ以下のテーブルで試す。

データの内容は少し変更した。

オブジェクト図で示すと、

サブクエリが必ず行を返す場合

初めに確認しておくことは、サブクエリがダミーのテーブルを参照し、必ずクエリが行を返す場合。

select *
from persons 
where exists (select 0 as hoge from dual)

サブクエリの結果が必ず存在するので、各要素で exists 述語が True となり、全ての「人」が抽出される。

相関サブクエリを利用する場合

次に、

「グループ」に「割当て」られた「人」

を EXISTS 述語を使って抽出してみる。

オブジェクト図で見た場合、「人」から「割当て」へのリンクが存在する人を選び出す。

SQL を書くときに意識することは、

抽出したいのは、とある「人」なんだけれど、
その人は、「グループ」に「割当て」られている。

という順序。

前回と同じように WHERE 句は集合の要素に対する述語であることを思い出しながら書く。

select * 
from persons as p
where exists (select *
              from assignments as a
              where p.id = a.p_id)

結果は、

+----+--------+--------+-----+
| id | name   | gender | age |
+----+--------+--------+-----+
|  1 | Tarou  |      1 |  10 |
|  2 | Hanako |      2 |  20 |
|  3 | Jirou  |      1 |  30 |
+----+--------+--------+-----+
3 rows in set (0.00 sec)

上記のサブクエリにおける SELECT 句で `*’ が書かれているのは、

SQL-89 の規則では、サブクエリーは 1 つのカラムか、 * を使った SELECT 句であるとなっています。もし、 SELECT * オプションが使われると、データベースエンジンは、 1 つのカラムを選択し、(理論上は) それを使います。

(プログラマのためのSQL 第2版の第15章 EXISTS 述語, p189 より)

相関サブクエリを利用しない場合

ちなみに相関サブクエリを使わない場合は、

先に「割当て」に注目し、
「割当て」に結合する「人」が
重複しないように抽出

すればいいので、

select distinct p.*
from assignments as a 
     join persons as p on a.p_id = p.id

Haskell で類似したコードを考える

上記に類似したコードを Haskell で考える。

予め SQL の exists 述語に相当する exists 関数を定義しておき、

exists = not . null

これを利用して次のように書く。

filter (\p -> exists
        (filter (\a -> a_p_id a == p_id p)
                assignments))
       persons

( cf. gist: 645292 - GitHub )

リスト内包表記なら、

[p | p <- persons
   , exists [a | a <- assignments
               , a_p_id a == p_id p]]

( cf. gist: 645292 - GitHub )

「SQL の相関サブクエリ (3) - 量化された比較述語 ALL, ANY」につづく

2010年10月21日木曜日

SQL の相関サブクエリ – filter 関数から考える

SQL 嫌い

SQL は昔から嫌い。そのため、C.J.Date の「データベース実践講義」でバッサリと斬り捨てている様はあまりにも心地良く、SQL 批判を全部箇条書きに抜き出そうと考えたけれど、あまりの量に断念した。

複雑で長い SQL は読みにくいし、保守しずらい。パフォーマンスに問題がない限り SQL はできるだけシンプルに保ち、やっかいなことは直感的な理解が可能なアプリケーション層で対応したい。

しかし、自分が苦手だったのは、集合的で宣言的なものの考え方であり、C.J.Date が SQL に対して批判しているタプルの重複や NULL の問題とは無関係だったことに気がついたのは後のこと。

相関サブクエリとは

特に理解しずらかったのが「相関サブクエリ」。「相関」と言えば「相関係数」をすぐに連想してしまうので余計に「何のこっちゃ？」と感じた。

相関サブクエリとは、Correlated subquery – Wikipedia によると、

It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query.

つまり、どんなクエリかと言えば、

WHERE 句の中で、外側のクエリの値を利用するサブクエリ。
外側のクエリの各行ごとにサブクエリが評価される。

この動作を、クエリの対象として表をイメージし、各々の行ごとに順にサブクエリが実行される様をループとして頭の中に描くと、それは手続き的な見方であり、宣言的な理解からかけ離れてしまう。

以前これを誤解し、「何だかんだ言っても、実行の順序を考えなくてはいけないのか…」と思っていた。しかし、概念的に言えば、各行のサブクエリが実行される順序に意味はなく、集合の要素として同時に計算が行われる。いや、そもそも同時であるかないか考えること自体お角違い。実際の具体的な計算の過程は実装が考えることであって、リレーショナル代数を利用する側が関心を持つことではない。

データベースのサンプル

例えば、「人」を「グループ」に「割当て」るモデルを仮定する。

ERD では下図の通り。 ( MySQL Workbench を利用。 )

各々のテーブルの値を以下の表に示す。

( cf. ODBC 経由でデータベースにデータを挿入 )

表だと見にくいのでオブジェクト図を示しておく。

相関サブクエリの例

上記のデータベースに対して、以下の問合せをしたい。

2010 年に「グループ」に「割当て」られた「人」は？

相関サブクエリを使わない場合

ここで最初に頭の中に思い浮かんだのは、

「割当て」の日付を見て、2010 年のタプルを抽出
上記 1 のタプルに「人」を結合。
ただし、同じ「人」が重複しないように気をつける。

という方法。

具体的なクエリは、

select distinct p.*
from (select *
      from assignments
      where year(date) = 2010) as a
     inner join persons as p on p.id = a.p_id

実行すると結果は、

+----+-------+--------+-----+
| id | name  | gender | age |
+----+-------+--------+-----+
|  1 | Tarou |      1 |  10 |
|  3 | Jirou |      1 |  30 |
+----+-------+--------+-----+
2 rows in set (0.04 sec)

このクエリは、 where 句の中で外側のクエリの値を利用していないので、相関サブクエリではない。

相関サブクエリを使う場合

相関サブクエリを利用してクエリを書くなら、

select *
from persons as p
where 2010 in (select year(a.date)   
               from assignments as a   
               where p.id = a.p_id)

この書き方は、

抽出したいのは、とある「人」なんだけれど、
その人は、 2010 年に「グループ」に「割当て」られている。

ということを意識している。

先に「割当て」に目を付けるのではなく、各々の「人」を中心に考えているところが先ほどと違う点。しかし、当初この書き方がしっくり来なかった。というか where 句の部分をどのようにイメージすればいいのかわからなかった。

WHERE 句は集合の要素に対して条件を指定している

ところで、

性別が男性である「人」

を抽出する SQL はシンプルでわかりやすい。

select * 
from persons
where gender = 1

where 句は「男性である」という条件を素直に表現していると感じる。

where gender = 1

これに対し、先ほどの where 句はシンプルとは言い難い。

where 2010 in (select year(a.date)
               from assignments as a
               where p.id = a.p_id)

特に相関サブクエリにおける where 句の p, a をどのように考えればいいのだろう？

Haskell で類似したコードを書くと …

まずは、

性別が男性である「人」

を抽出する SQL と類似したコードを Haskell で考えてみる。

data Person = P { p_id :: Int
                , name :: String
                , gender :: Int
                , age :: Int
                } deriving Show

persons = [ P 1 "Tarou" 1 10
          , P 2 "Hanako" 2 20
          , P 3 "Jirou" 1 30
          , P 4 "Saburou" 1 40]

main = print $ filter (\p -> gender p == 1) persons

特定の「人」を抽出するための filter 関数の第1引数の述語が、 SQL の where 句の「条件」に相当する。言うまでもなく、この述語の引数 p は各々の「人」を表わし、それぞれの人ごとに検査が行われる。

比較すると、SQL ではこの p に相当するものが関数の引数の如く明示されていない。

where gender = 1

これにより、where 句が要素を抽出するための述語であることをつい忘れてしまう。

相関サブクエリに類似したものを Haskell で …

次に、

2010年に「グループ」に「割当て」られた「人」は？

の問い合わせを実装することを考える。

全体の形としては、 persons の中から条件に合った人を抽出すればいいので、

filter (\p -> ... ) persons

各々の「人」については、SQL の相関サブクエリで書いたコードよろしく、値 2010 がある集合の要素に含まれるかどうかを調べれば良さげなので、

filter (\p -> 2010 `elem` ... ) persons

ここで上記 ... の中に注目。各々の「人」に対して「グループ」への「割当て」を調べたいので、その中身は以下のようになるはず。

filter (\a -> ... ) assignments

「割当て」の抽出条件は、外側の filter で指定されている「人」になるので、外側の変数 p を参照する。

filter (\a -> p_id p == a_p_id a) assignments

後は値 2010 と比較できるように要素を変換。

全体を示すと以下の通り。

filter (\p -> 2010 `elem` 
               map (\a -> case toGregorian (date a) of 
                            (y,_,_) -> y)
                   (filter (\a -> p_id p == a_p_id a) 
                           assignments))   
       persons

( cf. gist: 637741 - GitHub )

上記をいきなり見たらわかりやすいとは言い難いけれど、少なくとも変数 p と a が指し示しているものが何であるのかはっきりとする。SQL の where 句は集合の要素に対する述語。要素を参照する変数が明示的に述べられていないが、上記のコードと比較すると相関サブクエリでなぜあのように書くのかイメージしやすくなった。

リスト内包表記

ついでなので、上記をリスト内包表記で書くなら、

main = print [p | p <- persons 
                ,  2010 `elem` [case toGregorian (date a) of 
                                  (y,_,_) -> y | a <- assignments
                                               , p_id p == a_p_id a]]

( cf. gist: 637741 - GitHub )

モナド

余談ながら、モナドの bind で書くにはどうすればいいのかな？

直積を作って、後で重複を取り除くという方法で考えるなら、

main = print $ nub $ 
       persons     >>= \p ->
       assignments >>= \a ->
       guard (p_id p == a_p_id a && 
              case toGregorian (date a) of 
                (y,_,_) -> y == 2010) >>
       return p

( cf. gist: 637741 – GitHub )

上記を do 式で書き直すと、

main = print $ nub $ do 
         p <- persons
         a <- assignments
         guard (p_id p == a_p_id a && 
                case toGregorian (date a) of 
                  (y,_,_) -> y == 2010)
         return p

( cf. gist: 637741 – GitHub )

「SQL の相関サブクエリ (2) – EXISTS 述語」につづく。

2010年10月19日火曜日

MySQL のテーブル定義とデータを SQL 文で出力 - MySQL Workbench と A5:SQL Mk-2 を使って

MySQL で構築したデータベースのテーブル定義とデータを SQL 文として出力したい。

MySQL Workbench で CREATE TABLE 文を生成

MySQL Workbench の Home より、 Data Modeling > Create EER Model From Existing Database で ERD を生成。
メニューより、File > Export > Forward Engineer SQL CREATE Script... により生成。

または、 SQL Development の SQL Editor を開き、左ペインの Object Browser に表示されているデータベースの Tables を右クリック > Send to SQL Editor > Create Statement 。

INSERT 文の生成

A5:SQL Mk-2

A5:SQL Mk-2 でテーブルを開く。
メニューより、テーブル > insert文エクスポート 。

MySQL Workbench

MySQL Workbench の Home より、SQL Development 。対象のテーブルのデータを表示させる。 (テーブルを右クリック > Edit Table Data)
メニューより、Query > Export Results ...

表示されたダイアログの File Format フィールドで `SQL INSERT statements' を選択。

しかし、テーブル名がだめだった。 (@_@; なんでだろう？

ODBC 経由データベースにデータを挿入

MySQL Workbench で外部キーの設定

1. テーブル定義

MySQL Workbench で外部キーの設定をしたい。

例えば、「人」を「グループ」に「割当て」るモデルで考える。

CropperCapture[3]

「人」 (persons) 「グループ」 (groups) に対応したテーブルが定義済みだとする。これに対して、「割当て」 (assignments) に対応したテーブルを作成し、外部キーを設定したい。

2. SQL Development で外部キーを設定する場合

MySQL Workbench の Home において SQL Development を選択し、connection でデータベースに接続。

SQL Editor が開いたら、左ペインの Object Browser に表示されている

データベースの Tables で右クリック > Create Table 。

MySQL には基盤となるデータベースエンジンが複数あり、この中で外部キーをサポートしているのが InnoDB 。

InnoDB - Wikipedia によると、

InnoDB(イノデービー)はMySQLのためのデータベースエンジンであり、MySQL ABが配布している全てのバイナリに標準搭載されている。MySQLと使用できる他のデータベースエンジンに対する改良点として、PostgreSQLに似たACID互換のトランザクションに対応していることがある。また、外部キーもサポートしている。(これを宣言的参照整合性という)

よって、「Table タブ」における Engine の選択で InnoDB を選択。

「Columns タブ」でカラムの設定をしたら、「Foreign Keys タブ」で外部キーの設定は行わなわず、Apply ボタンを押して一旦テーブルを作成。

テーブルを作成した後、テーブルを変更する。

右クリック > Alter Table

「Foreign Keys タブ」で外部キーを設定する。

Foreign Key Name は適当に付ける。
Referenced Table は スキーマ名.テーブル名 で指定。
Column で外部キーを参照するためのカラムを指定。
参照先のテーブルのカラムを設定。

CropperCapture[1]

3. Data Modeling を使う場合

既にテーブルを定義している場合、MySQL Workbench の Home より

Data Modeling > Create EER Model From Existing Database

により、ERD を生成できる。

テーブルのカラム定義が済んでいる場合は、Diagram の左下アイコン

Place a Relashinship Existing Columns

を選択し、参照するカラムを選択してから、参照先のカラムを選択する。これにより、外部キーを設定できる。

データベースに変更を反映するには、

メニューより Database > Synchronize Model ...

を選択。

4. リレーションシップの点線と実線

外部キーを設定するときの線の種類には、点線と実線がある。

点線が Non-Identifying Relationship で、実線が Identifying Relationship 。

この違いは、(7.7.2.1 Adding Foreign Key Relationships Using an EER Diagram によると、

An identifying relationship is one where the child table cannot be uniquely identified without its parent.

Identifying Relationship の方は、参照する側 (子) が存在するには、参照される側 (親) が存在する必要がある場合に使うようだ。

テーブルの定義としては、点線の方は、カラムに外部キーの制約付けられる。実線は、参照する側のカラムが主キーの一部となることが加わる。

cf. What's the difference between identifying and non-identifying relationships? - Stack Overflow

登録: 投稿(Atom)

2011年8月5日金曜日

1. Excel でワイルドカード

2. LibreOffice で正規表現による検索

3. SQL との比較

2010年11月12日金曜日

拡張

拡張の後に制限

要約

特定の基準で要約 - by

他の関係変数と組み合わせる - per

2010年11月11日木曜日

制限

射影

結合

交わり

デカルト積

半結合

和

直和

差

半差

その他

2010年11月10日水曜日

SQL に対する批判

Tutorial D

Rel に慣れるために

タプル、関係の評価

式 と 文

関係変数の定義と代入

変数の定義

値を変数に代入

変数の削除

関連記事

2010年11月6日土曜日

全称命題と存在命題

全称命題

存在命題

日常的な言葉の表現における言い換え

「すべての…」 を SQL の EXISTS 述語で書く

forall (∀) と exist (∃) の変換

二重否定の SQL

日本語で言い換える

そのままで考える

SQL で書く

動作をイメージする

FORALL 述語があれば…

Haskell で書く

2010年11月2日火曜日

SELECT 句で相関サブクエリ

Haskell で書く

量化

ALL, ANY の仕様

サブクエリの結果が固定されている場合

ALL 述語

ANY 述語

Haskell で all, any 述語を利用して書く

相関サブクエリにより要素ごとに結果が異なる場合

Haskell で書くと…

集約関数 max を使う場合

Haskell で…

参考サイト

2010年10月31日日曜日

EXISTS 述語

対象のデータベース

サブクエリが必ず行を返す場合

相関サブクエリを利用する場合

相関サブクエリを利用しない場合

Haskell で類似したコードを考える

2010年10月21日木曜日

SQL 嫌い

相関サブクエリとは

データベースのサンプル

相関サブクエリの例

相関サブクエリを使わない場合

相関サブクエリを使う場合

WHERE 句は集合の要素に対して条件を指定している

Haskell で類似したコードを書くと …

相関サブクエリに類似したものを Haskell で …

リスト内包表記

モナド

式と文

「すべての…」を SQL の EXISTS 述語で書く