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

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

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

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

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2010/06/29
メディア: 大型本
購入: 39人クリック: 484回
この商品を含むブログ (16件) を見る

述語とは？
各述語の機能
CASE式

述語とは？

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

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

各述語の機能

LIKE

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

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

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

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

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

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

おまけですが、正規表現を使うことで非常に柔軟な検索機能を作ることができます。
たとえば、インターネットの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の省略系で、１つの句複数の条件を設定できます。
実際に使ってみた例が下です。

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)

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

もし、コレを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)

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

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)

この２つのテーブルを使い、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)

作者: ミック
出版社/メーカー: 翔泳社
発売日: 2008/02/07
メディア: 単行本（ソフトカバー）
購入: 54人クリック: 1,004回
この商品を含むブログ (78件) を見る

CASE式

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

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

文法

単純CASE式

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

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

検索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)

こうなります。

Blank?=False

ゆるゆる仕事したいフリーランスエンジニアの記事

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

述語とは？

各述語の機能

LIKE

BETWEEN

IS NULL, IS NOT NULL

IN, NOT IN

EXISTS

CASE式

文法

単純CASE式

検索CASE式

使用例

単純CASE式

検索CASE式

おまけ