Cat Paw Software

この文書は読取専用です。文書のソースを閲覧することは可能ですが、変更はできません。もし変更したい場合は管理者に連絡してください。
====== SQL ======
SQLめも

===== WHERE ( ... ) IS NOT FALSE  =====
MySQL 5で動作確認。Postgresもいけるらしい

以下のようなSQLをプリペアドステートメントで使いたい、でも場合によっては検索条件は全部使わない。
<code>
SELECT * FROM TBL_A WHERE (hoge = ? AND fuga = ? AND piyo = ? ) 
</code>
プログラム中で文字列継ぎ接ぎしてSQLを組み立てることをせずに、使わない条件を無視させることができる。
<code>
SELECT * FROM TBL_A WHERE (hoge = ? AND fuga = ? AND piyo = ? ) IS NOT FALSE
</code>
WHERE句を()で囲みIS NOT FALSEを付ける。検索に使わない条件には、NULLをセットする。

piyoを検索に使わないときは、piyoの比較対象にNULLをセットする。すると、piyo = NULL は unknown と評価される。

piyo以外の条件がtrueとなった場合
   (true AND unknown) IS NOT FALSE
となり、(true AND unknown) = unknown であるから、unknown IS NOT FALSE となり全体はtrueとなる。

piyo以外の条件がfalseとなった場合
   (false AND unknown) IS NOT FALSE
となり、(false AND unknown) = false であるから、false IS NOT FALSE となり全体はfalseとなる。

===== unknown =====
プログラミング言語において一般的に真偽値はtrue、falseの２つの値を取る。SQLの場合true、false、unknownという3つの値がある。unknownはある値とNULLを比較演算した場合に発生する。例えば NULL = 1 や NULL > 1 は unknown である（MySQL等ではunknownはNULLで代用されているようだ）。

true false unknownから成る論理系を３値論理という。

unknownとの論理演算
^and     ^true   ^unknown^false  ^
^true    |true   |unknown|false  |
^unknown |unknown|unknown|false  |
^false   |false  |false  |false  |

^or      ^true   ^unknown^false  ^
^true    |true   |true   |true   |
^unknown |true   |unknown|unknown|
^false   |true   |unknown|false  |

^x       ^NOT x  ^
|true    |false  |
|unknown |unknown|
|false   |true   |

unknownとのand演算とor演算の結果を覚えるのは面倒くさいが、unkwownを仮にtrueとfalseにそれぞれ置き換えた場合にどうなるかを考えると理解しやすい。

参考：[[http://mickindex.sakura.ne.jp/database/db_3vl.html|3値論理 —— 神のいない論理]]

===== 和集合・積集合・差集合 =====

===== ANYとALL =====

===== EXISTS (相関サブクエリ) の基本 =====
1対多でリレーションでは、ある1側のレコードに対して関連する多の側のレコードが0件であるケースがありえる。関連する多テーブルのレコードが0件であったり、少なくとも1件存在する１テーブルのデータを取得するのにEXISTS句が役立つ。

1側（modelsテーブル）
^id^value^
|1|X|
|2|Y|
|3|Z|

多側（gradesテーブル）
^id^model_id^value^
|1|1|R|
|2|1|S|
|3|2|S|

grades（多）にSがある models（１）のレコードを取る
<code sql>
SELECT * FROM models WHERE EXISTS (SELECT 1 FROM grades WHERE models.id = grades.model_id AND grades.value = 'S');
</code>

grades（多）にRが無い models（１）のレコードを取る
<code sql>
SELECT * FROM models WHERE NOT EXISTS (SELECT 1 FROM grades WHERE models.id = grades.model_id AND grades.value = 'R');
</code>


===== 自己相関サブクエリによる重複レコード削除 =====
自己相関サブクエリを利用すると重複レコードが削除できる。

^id^user_id^value^ctime^
|1|1|hoge|2008-01-01|
|2|3|fuga|2008-01-17|
|3|3|moge|2008-02-02|
|4|2|piyo|2008-01-11|
|5|1|puni|2008-02-01|

例えばvalueとuser_idについて重複しているレコードを削除するＳＱＬは以下のようになる。ただしユニークなidカラムがあること。
<code sql>
DELETE FROM hoge_table t1 
WHERE EXISTS (
    SELECT 1 
    FROM hoge_table t2 
    WHERE t1.user_id = t2.user_id 
    AND t1.value = t2.value 
    AND t1.id < t2.id
); 
</code>
ただしMySQL 5.0はinsert update deleteで操作対象と同一のテーブルを参照するサブクエリを使えないため、上記のSQLはエラーとなる。テンポラリテーブルを使って頑張ろう。

また、このSQLではuser_idとvalueに複合インデックスが無いとレコード数×レコード数のマッチングを行うため、とても時間がかかるので注意

===== 各ユーザが最後に追加したレコードをまとめて取得 =====
各ユーザが最後に追加したレコードをまとめて取得する。ctimeをレコードを追加した時刻を格納したカラムとする
<code sql>
SELECT t1.id, t1.user_id, t1.value, t1.ctime 
FROM hoge_table t1
INNER JOIN (
    SELECT user_id, MAX(ctime) FROM hoge_table GROUP BY user_id
) AS t2 
ON t1.ctime = t2.ctime 
AND t1.user_id = t2.user_id;
</code>
インラインビューでテーブルを自己結合させるのがポイント。一度のSQLで各ユーザの最後のステータスを取得するといったことができる。（例：各部門が最後に発売した商品一覧を取得する）

===== having基本 =====
^num^chr^
|1|a|
|1|b|
|1|c|
|2|b|
|2|c|
|3|a|
|3|b|
|4|c|
|4|d|
上記のテーブルでnumの値1,2,3に対して、その**どれか**に存在するchrを取得する場合（和集合）は
<code sql>
SELECT chr FROM t WHERE num in (1,2,3) group by (chr);
</code>
上記のテーブルでnumの値が1,2,3それぞれの場合で、**すべて**に存在するchrを取得する場合（積集合）は
<code sql>
SELECT chr FROM t WHERE num in (1,2,3) group by (chr) having count(*) = 3;
</code>
1,2,3という3つの要素に対してすべてに存在すると言う事は、レコードが3つあるということ。