Blank?=False

「呉下の阿蒙にあらず」をモットーにしたITエンジニアの日々

SQL ゼロから始めるデータベース操作 第6章 述語, CASE式

f:id:stonebeach-dakar:20160424222031p:plain

今回は引き続きSQLのお勉強、述語を勉強していきます。

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

述語とは?

真理値を返す関数
(真理値とは、TRUE,FALSEを指します。)

たとえば、◯◯が条件▲▲にマッチするならTRUE,しなければFALSE…という戻り値になります。
ポピュラーな例だと = ,>, <, <>が述語と言われます。

各述語の機能

LIKE

正規表現によるパターンマッチング検索を行います。
正規表現はそれだけで1つの本になるほど奥が深いものですが、
ここでは% ,_の2つのワイルドカードのみを使用します。

まず、2つのワイルドカードの仕様は以下のようになっています。

ワイルドカード 機能
% 0文字以上の任意の文字列
_ (アンダーバー) 任意の文字1つ


%はどのような文字列(0個以上)にもマッチし、_はどのような文字でも1文字だけマッチします。
_を2つ続ければ、任意2文字となります。
%_は使えますが意味はありません。%A_とすれば、A以前はどのような文字列でもOKで、Aの後は1文字のみ、という条件になります。
例えば、'ab12AB' Ac にはマッチしますが、 Abc1234Aにはマッチしません。

今回は、こんなサンプルテーブルを用意します。

mysql> select * from SampleLIKE;
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| abdddc |
| ddabc  |
| dddabc |
+--------+
6 rows in set (0.00 sec)


実際に使ってみます。

mysql> SELECT * FROM SampleLIKE
    -> WHERE strcol LIKE '%dd';
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
+--------+
2 rows in set (0.00 sec)

この場合、最後がddならその前は何でもいい、という条件になります。
これを後方一致といいます。

逆に、

mysql> SELECT * FROM SampleLIKE
    -> WHERE strcol LIKE 'dd%';
+--------+
| strcol |
+--------+
| ddabc  |
| dddabc |
+--------+
2 rows in set (0.01 sec)

とした場合、先頭がddならその後は何でもいい、という条件になります。
このような条件を前方一致といいます。

ところで、Google検索は検索文字列が真ん中にあってもヒットしますよね。
f:id:stonebeach-dakar:20160509175153p:plain
こういう検索方法を、中間一致といいます。

試しにやってみると、

mysql> SELECT * FROM SampleLike
    -> WHERE strcol LIKE '%dd%';
+--------+
| strcol |
+--------+
| abcdd  |
| abcddd |
| abddc  |
| abdddc |
| ddabc  |
| dddabc |
+--------+
6 rows in set (0.00 sec)

先頭がdd,真ん中がdd,最後がddとなるすべてのデータが出てきます。
最も検索結果が多くなりやすい検索方法なので、様々な検索は中間一致がデフォルトになっている事が多いです。
ただし、あえて先頭が一致するもの、最後が一致するものを検索したい、というシチュエーションもあるので
3つの検索方法を選択できるようになっているシステムもあります。

おまけですが、正規表現を使うことで非常に柔軟な検索機能を作ることができます。
たとえば、インターネットのURLの文字列だけを取り出す、等様々なことができるので、
ご興味あればググるとよし!
(自分が正規表現好きなだけ)

BETWEEN

範囲検索を行います。個人的に一番わかり易いSQLの句じゃないかとおもったり。
たとえば、何年から何年までのデータを表示、といったシチュエーションで使われます。
データベースは件数が多くなりやすいので、BETWEENでここ数ヶ月分だけ表示、という風によく使います。

mysql> select * from Sample;
+------+---------------+---------+------------+------------+
| id   | Name          | VALUE   | Date       | Category   |
+------+---------------+---------+------------+------------+
| 0001 | Car           | 2000000 | 2009-11-25 | Vehicle    |
| 0002 | Pencil        |     200 | 2009-11-27 | Stationery |
| 0003 | Cabinet       |   12500 | 2009-11-27 | Furniture  |
| 0004 | Eraser        |     299 | 2009-12-11 | Stationery |
| 0005 | Moto          |  195000 | 2009-12-21 | Vehicle    |
| 0006 | Chair         |    8500 | 2010-05-12 | Furniture  |
| 0007 | Ballpoint pen |     230 | 2010-06-08 | Stationery |
| 0008 | Crayon        |     210 | 2010-06-15 | Stationery |
| 0009 | Garage        | 5282000 | 2010-09-01 | NULL       |
| 0010 | MacBookPro    |       0 | 2011-02-03 | NULL       |
+------+---------------+---------+------------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM Sample
    -> WHERE Date BETWEEN '2009-11-01' AND '2009-11-30';
+------+---------+---------+------------+------------+
| id   | Name    | VALUE   | Date       | Category   |
+------+---------+---------+------------+------------+
| 0001 | Car     | 2000000 | 2009-11-25 | Vehicle    |
| 0002 | Pencil  |     200 | 2009-11-27 | Stationery |
| 0003 | Cabinet |   12500 | 2009-11-27 | Furniture  |
+------+---------+---------+------------+------------+
3 rows in set (0.01 sec)


BETWEENを使う時の注意点として、指定した値を含むということです。
たとえば、上の例の場合、2009-11-01から2009-11-30まで、となります。
つまり、=>,=<と同じということです。
指定した値を含めたくない場合は、WHERE Date > 2009-11-01 AND Date < 2009-11-30とするか、
WHERE Date BETWEEN '2009-11-02' AND '2009-11-29'とするかですね。
個人的には前者のほうが読みやすいと思います。

IS NULL, IS NOT NULL

IS NULL はNULLであればTRUE, IS NOT NULLはNULLでなければTRUEを返す述語です。
NULLなレコードを抽出して一律UPDATE、等で使うことが有ります。

mysql> SELECT * FROM Sample;
+------+---------------+---------+------------+------------+
| id   | Name          | VALUE   | Date       | Category   |
+------+---------------+---------+------------+------------+
| 0001 | Car           | 2000000 | 2009-11-25 | Vehicle    |
| 0002 | Pencil        |     200 | 2009-11-27 | Stationery |
| 0003 | Cabinet       |   12500 | 2009-11-27 | Furniture  |
| 0004 | Eraser        |     299 | 2009-12-11 | Stationery |
| 0005 | Moto          |  195000 | 2009-12-21 | Vehicle    |
| 0006 | Chair         |    8500 | 2010-05-12 | Furniture  |
| 0007 | Ballpoint pen |     230 | 2010-06-08 | Stationery |
| 0008 | Crayon        |     210 | 2010-06-15 | Stationery |
| 0009 | Garage        | 5282000 | 2010-09-01 | NULL       |
| 0010 | MacBookPro    |       0 | 2011-02-03 | NULL       |
+------+---------------+---------+------------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM Sample
    -> WHERE Category IS NULL;
+------+------------+---------+------------+----------+
| id   | Name       | VALUE   | Date       | Category |
+------+------------+---------+------------+----------+
| 0009 | Garage     | 5282000 | 2010-09-01 | NULL     |
| 0010 | MacBookPro |       0 | 2011-02-03 | NULL     |
+------+------------+---------+------------+----------+
2 rows in set (0.00 sec)


IN, NOT IN

ORの省略系で、1つの句複数の条件を設定できます。
実際に使ってみた例が下です。

mysql> SELECT * FROM Sample;
+------+---------------+---------+------------+------------+
| id   | Name          | VALUE   | Date       | Category   |
+------+---------------+---------+------------+------------+
| 0001 | Car           | 2000000 | 2009-11-25 | Vehicle    |
| 0002 | Pencil        |     200 | 2009-11-27 | Stationery |
| 0003 | Cabinet       |   12500 | 2009-11-27 | Furniture  |
| 0004 | Eraser        |     299 | 2009-12-11 | Stationery |
| 0005 | Moto          |  195000 | 2009-12-21 | Vehicle    |
| 0006 | Chair         |    8500 | 2010-05-12 | Furniture  |
| 0007 | Ballpoint pen |     230 | 2010-06-08 | Stationery |
| 0008 | Crayon        |     210 | 2010-06-15 | Stationery |
| 0009 | Garage        | 5282000 | 2010-09-01 | NULL       |
| 0010 | MacBookPro    |       0 | 2011-02-03 | NULL       |
+------+---------------+---------+------------+------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM Sample
    -> WHERE Category IN ( 'Vehicle', 'Furniture');
+------+---------+---------+------------+-----------+
| id   | Name    | VALUE   | Date       | Category  |
+------+---------+---------+------------+-----------+
| 0001 | Car     | 2000000 | 2009-11-25 | Vehicle   |
| 0003 | Cabinet |   12500 | 2009-11-27 | Furniture |
| 0005 | Moto    |  195000 | 2009-12-21 | Vehicle   |
| 0006 | Chair   |    8500 | 2010-05-12 | Furniture |
+------+---------+---------+------------+-----------+
4 rows in set (0.01 sec)

指定したVehicleFurnitureのデータが表示されます。


もし、コレをOR句でやろうとすると、

mysql> SELECT * FROM Sample
    -> WHERE Category = 'Vehicle'
    -> OR Category = 'Furniture';
+------+---------+---------+------------+-----------+
| id   | Name    | VALUE   | Date       | Category  |
+------+---------+---------+------------+-----------+
| 0001 | Car     | 2000000 | 2009-11-25 | Vehicle   |
| 0003 | Cabinet |   12500 | 2009-11-27 | Furniture |
| 0005 | Moto    |  195000 | 2009-12-21 | Vehicle   |
| 0006 | Chair   |    8500 | 2010-05-12 | Furniture |
+------+---------+---------+------------+-----------+

1つ条件が増えるたびにORを追加していかないといけなくなりますね。

INの否定形であるNOT INは、逆に指定したもの以外という条件になります。

mysql> SELECT * FROM Sample
    -> WHERE Category NOT IN ( 'Vehicle', 'Furniture');
+------+---------------+-------+------------+------------+
| id   | Name          | VALUE | Date       | Category   |
+------+---------------+-------+------------+------------+
| 0002 | Pencil        |   200 | 2009-11-27 | Stationery |
| 0004 | Eraser        |   299 | 2009-12-11 | Stationery |
| 0007 | Ballpoint pen |   230 | 2010-06-08 | Stationery |
| 0008 | Crayon        |   210 | 2010-06-15 | Stationery |
+------+---------------+-------+------------+------------+
4 rows in set (0.00 sec)

さっきとは逆に、VehicleFurniture以外のデータが表示されます。


INの真価はサブクエリを使うときに出てきます。
複数の条件ということは、スカラサブクエリではないサブクエリの結果を条件に含める事ができるようになるわけですね。
試しにやってみました。(あんまり意味ない使い方だけどサンプルってことで・・・)

mysql> SELECT * FROM Sample
    -> WHERE Name IN ( SELECT Name
    ->                 FROM Sample
    ->                 WHERE VALUE > 10000 );
+------+---------+---------+------------+-----------+
| id   | Name    | VALUE   | Date       | Category  |
+------+---------+---------+------------+-----------+
| 0001 | Car     | 2000000 | 2009-11-25 | Vehicle   |
| 0003 | Cabinet |   12500 | 2009-11-27 | Furniture |
| 0005 | Moto    |  195000 | 2009-12-21 | Vehicle   |
| 0009 | Garage  | 5282000 | 2010-09-01 | NULL      |
+------+---------+---------+------------+-----------+
4 rows in set (0.01 sec)


EXISTS

レコードの存在チェックを行う述語。
存在すればTRUE,存在しなければFALSEを返す。
引数は必ず相関サブクエリになる。(相関ではなくても使えるけど、必要となるケースはたいてい相関サブクエリな感じ。)

本書では、

否定形のNOT EXISTS関数を使うSQL文は熟練のDBエンジニアでも即座に把握できないこともしばしばある
使いこなすと非常に大きな力を発揮する

とあり、このEXISTSがSQL中級者への登竜門のような扱いになっています。
自分もあまり理解しきれていないですが、プログラミングで言うIsFileExistなどのイメージで合っていると思います。

どう使うかというと、◯◯というものが▲▲テーブルにあれば表示というシチュエーションで使います。

実際に、例を出してみます。
いつものSampleテーブル、そしてカテゴリの一覧があるCategoriesテーブルです。

mysql> SELECT * FROM Sample;
+------+---------------+---------+------------+------------+
| id   | Name          | VALUE   | Date       | Category   |
+------+---------------+---------+------------+------------+
| 0001 | Car           | 2000000 | 2009-11-25 | Vehicle    |
| 0002 | Pencil        |     200 | 2009-11-27 | Stationery |
| 0003 | Cabinet       |   12500 | 2009-11-27 | Furniture  |
| 0004 | Eraser        |     299 | 2009-12-11 | Stationery |
| 0005 | Moto          |  195000 | 2009-12-21 | Vehicle    |
| 0006 | Chair         |    8500 | 2010-05-12 | Furniture  |
| 0007 | Ballpoint pen |     230 | 2010-06-08 | Stationery |
| 0008 | Crayon        |     210 | 2010-06-15 | Stationery |
| 0009 | Garage        | 5282000 | 2010-09-01 | NULL       |
| 0010 | MacBookPro    |       0 | 2011-02-03 | NULL       |
+------+---------------+---------+------------+------------+
10 rows in set (0.00 sec)

mysql> Select * FROM Categories;
+----------+
| Name     |
+----------+
| Computer |
| Strage   |
| Vehicle  |
+----------+
3 rows in set (0.00 sec)


この2つのテーブルを使い、Categoriesテーブルにあるカテゴリのデータを表示する、というお題でやってみます。

mysql> SELECT *
    -> FROM Sample AS S1
    -> WHERE EXISTS( SELECT *
    ->               FROM Categories
    ->               WHERE S1.Category = Name );
+------+------+---------+------------+----------+
| id   | Name | VALUE   | Date       | Category |
+------+------+---------+------------+----------+
| 0001 | Car  | 2000000 | 2009-11-25 | Vehicle  |
| 0005 | Moto |  195000 | 2009-12-21 | Vehicle  |
+------+------+---------+------------+----------+
2 rows in set (0.00 sec)

と、このようにSampleテーブルのデータのカテゴリでCategoriesテーブルに登録されているのは
Vehicleだけですので、Vehicleのデータだけ表示される、という結果になりました。

たとえば、CategoryStationeryの場合、Categoriesテーブルに存在しないのでFALSEとなり、
抽出条件に含まれなくなるわけですね。

否定形のNOT EXISTSを使った場合、

mysql> SELECT *
    -> FROM Sample AS S1
    -> WHERE NOT EXISTS( SELECT *
    ->                   FROM Categories
    ->                   WHERE S1.Category = NAME );
+------+---------------+---------+------------+------------+
| id   | Name          | VALUE   | Date       | Category   |
+------+---------------+---------+------------+------------+
| 0002 | Pencil        |     200 | 2009-11-27 | Stationery |
| 0003 | Cabinet       |   12500 | 2009-11-27 | Furniture  |
| 0004 | Eraser        |     299 | 2009-12-11 | Stationery |
| 0006 | Chair         |    8500 | 2010-05-12 | Furniture  |
| 0007 | Ballpoint pen |     230 | 2010-06-08 | Stationery |
| 0008 | Crayon        |     210 | 2010-06-15 | Stationery |
| 0009 | Garage        | 5282000 | 2010-09-01 | NULL       |
| 0010 | MacBookPro    |       0 | 2011-02-03 | NULL       |
+------+---------------+---------+------------+------------+
8 rows in set (0.00 sec)

Categoriesテーブルに存在しないカテゴリのデータが表示されます。
本書ではこういったあたりを解説されていますが、より詳細を知りたいひとは
達人に学ぶSQL徹底指南書(同じ作者の本)を呼んでみてください、とのことでした。

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)

達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)


CASE式

プログラミングをやる人ならSwitch Select Case などの条件分岐のSQL版、といえばわかるかな。
(ちなみにMySQLIFによる条件分岐も対応している。が、わりと読みにくいので非推奨。)

CASE式には2種類あり、単純CASE式検索CASE式があり、文法が少し異なる。
単純CASE式プログラミング言語switchの文法に近い感じで、
検索CASE式IFの文法に近い、というイメージ。


文法

単純CASE式
  CASE <式>
       WHEN <式>     THEN <式>
       WHEN <式>     THEN <式>
       ELSE <式>
  END

まんまプログラミング言語SWITCHSELECT CASEですね。
対象となる式が1つ、それに対して色々な条件での分岐です。

検索CASE式
  CASE WHEN <条件式>   THEN <式>
       WHEN <条件式>   THEN <式>
       ELSE  <式>
  END 

こっちはSWITCHよりもIF ~ ELSE IF~という感じです。
WHEN~ごとに別々の条件を設定できます。

使用例

実際に、[SampleテーブルでCategoryがVehicleなら乗り物, Furnitureなら家具, Stationeryなら文房具,それ以外は未設定と表示する列を作る]というお題でやってみます。

単純CASE式
mysql> SELECT NAME,
    ->        Category,
    ->        CASE Category
    ->          WHEN 'Vehicle' THEN '乗り物'
    ->          WHEN 'Furniture' THEN '家具'     
    ->          WHEN 'Stationery' THEN '文房具'             
   ->          ELSE '未設定'  
->        END AS CatJPN                                            
-> FROM Sample;                                       
+---------------+------------+-----------+
| NAME          | Category   | CatJPN    |
+---------------+------------+-----------+
| Car           | Vehicle    | 乗り物    |
| Pencil        | Stationery | 文房具    |
| Cabinet       | Furniture  | 家具      |
| Eraser        | Stationery | 文房具    |
| Moto          | Vehicle    | 乗り物    |
| Chair         | Furniture  | 家具      |
| Ballpoint pen | Stationery | 文房具    |
| Crayon        | Stationery | 文房具    |
| Garage        | NULL       | 未設定    |
| MacBookPro    | NULL       | 未設定    |
+---------------+------------+-----------+
10 rows in set (0.00 sec)
検索CASE式
mysql> SELECT NAME,
    ->        Category,
    ->        CASE WHEN Category='Vehicle' THEN '乗り物' 
 ->             WHEN Category='Stationery' THEN '文房具' 
 ->             WHEN Category='Furniture' THEN '家具' 
 ->             ELSE '未設定'`
 ->           END AS CatJPN
-> FROM Sample;
+---------------+------------+-----------+
| NAME          | Category   | CatJPN    |
+---------------+------------+-----------+
| Car           | Vehicle    | 乗り物    |
| Pencil        | Stationery | 文房具    |
| Cabinet       | Furniture  | 家具      |
| Eraser        | Stationery | 文房具    |
| Moto          | Vehicle    | 乗り物    |
| Chair         | Furniture  | 家具      |
| Ballpoint pen | Stationery | 文房具    |
| Crayon        | Stationery | 文房具    |
| Garage        | NULL       | 未設定    |
| MacBookPro    | NULL       | 未設定    |
+---------------+------------+-----------+
10 rows in set (0.00 sec)


おまけ

本書では

CASE式は特にSELECT分の結果を柔軟に組み替えるときに、大きな威力を発揮します。

と紹介されています。。

例として本書には行列を入れ替えるものが紹介されていました。
それは、以下の様な文です。

mysql> SELECT COUNT( CASE WHEN Category='Vehicle' THEN Category ELSE NULL END ) AS Count_Vehicle,
    ->        COUNT( CASE WHEN Category='Furniture' THEN Category ELSE NULL END ) AS Count_Furniture,
    ->        COUNT( CASE WHEN Category='Stationery' THEN Category ELSE NULL END ) AS Count_Stationery
    -> FROM Sample;
+---------------+-----------------+------------------+
| Count_Vehicle | Count_Furniture | Count_Stationery |
+---------------+-----------------+------------------+
|             2 |               2 |                4 |
+---------------+-----------------+------------------+
1 row in set, 8 warnings (0.00 sec)

この例は、カテゴリごとの個数をそれぞれの列に出力するサンプルです。
もし、行で表現すると、

mysql> SELECT Category,COUNT(Category) AS Category_Count
    -> FROM Sample
    -> GROUP BY Category;
+------------+----------------+
| Category   | Category_Count |
+------------+----------------+
| NULL       |              0 |
| Furniture  |              2 |
| Stationery |              4 |
| Vehicle    |              2 |
+------------+----------------+
4 rows in set (0.00 sec)

こうなります。