mount -t cifs "//IPアドレス/Data" /mnt/マウントするディレクトリ -o password=パスワード,sec=ntlm,uid=1000,gid=1000,vers=1.0
MySQL講座#19 テーブルの足し算と引き算
集合演算とは
本ページで学習するのは、「集合演算」という名前で呼ばれている操作である。「集合」というと数学の世界では「物の集まり」を表すが、データベースの世界では「レコードの集合」を表す。「レコードの集合」とは具体的に言えば、もちろんテーブルはそうであるし、ビューやクエリの実行結果もそうである。
これまでも、テーブルからレコードを選択したり、データを登録したりする方法を学習してきたが、集合演算とは、レコード同士を足したり引いたりする、いわばレコードの「四則演算」である。集合演算を行うことで、片方のテーブルだけにあるレコードを集めた結果や、共通するレコードを集めた結果、片方のテーブルだけにあるレコードを集めた結果などを得ることができる。そして、このような集合演算を行うための演算子を「集合演算子」と呼ぶ。
テーブルの足し算－UNION
最初に紹介する集合演算子は、レコードの足し算を行うUNION（和）である。
実際に使い方を見る前に、サンプルのテーブルを1つ用意する。次のような、今まで使ってきたchojin（超人）テーブルと同じレイアウトで、テーブル名だけが異なる「team」というテーブルを作る。
teamテーブルを作成する。
CREATE TABLE team (id CHAR(4) NOT NULL, name VARCHAR(100) NOT NULL, kana VARCHAR(100) NOT NULL, category VARCHAR(100) NOT NULL, attribute VARCHAR(100) NOT NULL, height INTEGER NOT NULL, weight INTEGER NOT NULL, power INTEGER NOT NULL, origin VARCHAR(100) NOT NULL, created DATE NOT NULL, PRIMARY KEY (id));
teamテーブルには、以下のレコードを登録する。
START TRANSACTION;
INSERT INTO team VALUES ('0001','キン肉マン','きんにくまん','正義超人','キン肉マンチーム','185','90','95','キン肉星',NOW());
INSERT INTO team VALUES ('0002','テリーマン','てりーまん','正義超人','キン肉マンチーム','190','95','95','アメリカ合衆国',NOW());
INSERT INTO team VALUES ('0003','ロビンマスク','ろびんますく','正義超人','キン肉マンチーム','217','155','96','イギリス',NOW());
INSERT INTO team VALUES ('0004','ウォーズマン','うぉーずまん','正義超人','キン肉マンチーム','210','150','100','ロシア',NOW());
INSERT INTO team VALUES ('0005','ラーメンマン','らーめんまん','正義超人','キン肉マンチーム','209','130','97','中華人民共和国',NOW());
INSERT INTO team VALUES ('0006','ブロッケンＪｒ','ぶろっけんじゅにあ','正義超人','ソルジャーチーム','195','90','90','西ドイツ',NOW());
INSERT INTO team VALUES ('0014','バッファローマン','ばっふぁろーまん','悪魔超人','ソルジャーチーム','250','220','1000','スペイン',NOW());
INSERT INTO team VALUES ('0018','ザ・ニンジャ','ざ・にんじゃ','悪魔超人','ソルジャーチーム','190','115','360','日本',NOW());
INSERT INTO team VALUES ('0019','アシュラマン','あしゅらまん','悪魔超人','ソルジャーチーム','203','200','1000','魔界',NOW());
INSERT INTO team VALUES ('0045','ジェロニモ','じぇろにも','正義超人','ソルジャーチーム','180','80','83','アメリカ合衆国',NOW());
INSERT INTO team VALUES ('0046','ミートくん','ミートくん','正義超人','ソルジャーチーム','100','25','50','キン肉星',NOW());
INSERT INTO team VALUES ('0047','キン肉アタル','きんにくあたる','正義超人','ソルジャーチーム','197','102','108','キン肉星',NOW());
COMMIT;
それでは準備ができたところで、さっそくこの2つのテーブルを「chojinテーブル＋teamテーブル」というように足し算をしてみる。
SELECT id, name FROM chojin UNION SELECT id, name FROM team;
結果
+------+--------------------------------+ | id | name | +------+--------------------------------+ | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | （中略） | 0014 | バッファローマン | （中略） | 0018 | ザ・ニンジャ | | 0019 | アシュラマン | （中略） | 0045 | ジェロニモ | | 0046 | ミートくん | | 0047 | キン肉アタル | +------+--------------------------------+
結果は、2つのテーブルに含まれていたレコードが、すべて網羅されるという形になる。この演算のイメージは、集合論の「和集合」である。
超人ID「0001」～「0006」の6つのレコードはどちらのテーブルにも存在していたので、重複して結果に出てくるように思うかもしれないが、UNIONに限らず集合演算子は、通常は重複行が排除される。
集合演算の注意事項
この重複行を結果に出すことも可能だが、その前に、集合演算子を使うときの一般的な注意事項を学んでおく。これはUNIONに限らず、この後で学習するすべての演算子に当てはまる注意事項である。
注意事項①－演算対象となるレコードの列数は同じであること
例えば、次のように片方の列数が2列なのに、片方が3列という足し算を行うことはできない。
SELECT id, name FROM chojin UNION SELECT id, name, power FROM team;
結果
ERROR 1222 (21000): The used SELECT statements have a different number of columns
注意事項②－足し算の対象となるレコードの列のデータ型が一致していること
左から数えて同じ位置にある列は、同じデータ型である必要がある。例えば、次のSQL文は、列数は同じでも、2列目のデータ型が数値型と日付型で不一致のため、エラーになる。
SELECT id, power FROM chojin UNION SELECT id, created FROM team;
※MySQLでは、型が異なっていても、暗黙のうちに型変換を行う。
注意事項③－SELECT文はどんなものを指定しても良い。ただし、ORDER BY句は最後に1つだけ
UNIONで足せるSELECT文は、どんなものでも構わない。これまでに学んだWHERE、GROUP BY、HAVINGといった句も使うことができる。ただし、ORDER BY句だけは全体として1つ最後につけられるだけである。
SELECT id, name FROM chojin WHERE category = '正義超人' UNION SELECT id, name FROM team WHERE category = '正義超人' ORDER BY id;
結果
+------+--------------------------+ | id | name | +------+--------------------------+ | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | | 0007 | ウルフマン | | 0043 | スペシャルマン | | 0044 | カナディアンマン | | 0045 | ジェロニモ | | 0046 | ミートくん | | 0047 | キン肉アタル | +------+--------------------------+
重複行を残す集合演算－ALLオプション
さて、それではUNIONの結果から重複行を排除しない構文を紹介する。UNIONの後ろに「ALL」というキーワードを追加するだけである。このALLオプションは、UNION以外の集合演算子でも同様に使用できる。
SELECT id, name FROM chojin WHERE category = '正義超人' UNION ALL SELECT id, name FROM team WHERE category = '正義超人';
結果
+------+--------------------------+ | id | name | +------+--------------------------+ | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | | 0007 | ウルフマン | | 0043 | スペシャルマン | | 0044 | カナディアンマン | | 0001 | キン肉マン | | 0002 | テリーマン | | 0003 | ロビンマスク | | 0004 | ウォーズマン | | 0005 | ラーメンマン | | 0006 | ブロッケンＪｒ | | 0045 | ジェロニモ | | 0046 | ミートくん | | 0047 | キン肉アタル | +------+--------------------------+
MySQL講座#18 CASE式
CASE式とは
本ページで学ぶCASE式は、「式」という語がついている通り、「1 + 1」や「120 / 4」のような式と同じく一種の演算を行う機能である。その意味で、CASE式は関数の一種でもある。SQLの機能の中で一、二を争う重要な機能のため、ここでしっかり身につける必要がある。
CASE式は、CASE（場合）という名前が示すように、「場合分け」を記述するときに使う。この場合分けのことを、一般的に「条件分岐」とも呼ぶ。
CASE文の構文
CASE式の構文には、「単純CASE式」と「検索CASE式」の2種類がある。ただし、検索CASE式は単純CASE式の機能をすべて含むので、本ページでは検索CASE式だけを取り上げる。単純CASE式を知りたい方は、本ページ末のコラムを参照していただきたい。
ではさっそく、検索CASE式の構文を見てみる。
CASE WHEN ＜評価式＞ THEN ＜式＞
WHEN ＜評価式＞ THEN ＜式＞
WHEN ＜評価式＞ THEN ＜式＞
・・・
ELSE ＜式＞
END
WHEN句の＜評価式＞とは、「列 = 値」のように、戻り値が真理値（TRUE／FALSE／UNKNOWN）になるような式のことである。=、!=やLIKE、BETWEENといった述語を使って作る式だと考えてもらえれば良い。
CASE式の動作は、最初のWHEN句の＜評価式＞が評価されることから始まる。「評価」とは、その式の真理値が何か調べることである。その結果、もし真（TRUE）になれば、WHEN句で指定された式が戻されて、CASE式全体が終わる。もし真にならなければ、次のWHEN句の評価に移る。もしこの作業を最後のWHEN句まで繰り返してなお真にならなかった場合は、「ELSE」で指定された式が戻されて終了となる。
なお、CASE式は、名前に「式」とついていることからも分かる通り、これ全体が1つの式を構成している。そして式は、最終的に1つの値に定まるものであるから、CASE式は、SQL文の実行時には、全体が1つの値に変換される。分岐の多いCASE式を使うと何十行にわたって書くことも珍しくないが、その巨大なCASE式全体が、最後には「1」や「'渡辺さん'」のような単純な値になってしまうのである。
CASE式の使い方
では、CASE式を具体的に使ってみる。例えば、いまchojin（超人）テーブルには、正義超人、悪魔超人、完璧超人という3種類の超人分類が格納されている。これを次のような表示に変えて結果を得る方法を考える。
A:正義超人
B:悪魔超人
C:完璧超人
テーブルにあるレコードには、「A:」や「B:」という文字はついていないので、SQLの中でこれをくっつけてやる必要がある。そうすると、文字列結合の関数「CONCAT」を使うのだな、ということが分かるはずである。
残る問題は「A:」「B:」「C:」を正しいレコードに結びつけてやることである。これをCASE式で実現する。
SELECT name,
CASE WHEN category = '正義超人'
THEN CONCAT('A:', category)
WHEN category = '悪魔超人'
THEN CONCAT('B:', category)
WHEN category = '完璧超人'
THEN CONCAT('C:', category)
ELSE NULL
END AS abc_category
FROM chojin;
結果
+--------------------------------+----------------+ | name | abc_category | +--------------------------------+----------------+ | キン肉マン | A:正義超人 | | テリーマン | A:正義超人 | | ロビンマスク | A:正義超人 | | ウォーズマン | A:正義超人 | | ラーメンマン | A:正義超人 | | ブロッケンＪｒ | A:正義超人 | | ウルフマン | A:正義超人 | | ステカセキング | B:悪魔超人 | | ブラックホール | B:悪魔超人 | | ミスター・カーメン | B:悪魔超人 | 以下省略
CASE式の6行が、これで1つの列（abc_category）に相当する。超人分類（category）の名前に応じて、3つの分岐をWHEN句によって作っている。最後に「ELSE NULL」としているが、これは「それ以外の場合はNULLを返す」という意味。ELSE句では、WHEN句に指定した条件以外のレコードをどのように扱うかを記述する。NULL以外にも通常の値や式を書くことが可能。ただし現在は、テーブルに含まれる超人分類が3種類だけなので、実質的にELSE句は無くでも同じである。
ELSE句は、省略して書かないことも可能であるが、その場合は自動的に「ELSE NULL」とみなされることになってしまう。しかし、後から読む人が読み落とすことのないよう、明示的にELSE句を書くようにした方が良い。
なお、CASE式の最後の「END」は省略不可能なので、絶対に書き落とさないよう注意が必要である。
CASE式が書ける場所
このCASE式の便利なところは、まさに「式である」という点である。これが何を意味するかというと、式を書ける場所ならどこにでも書ける、ということなのである。それはつまり、「1 + 1」が書ける場所ならどこでも、という意味である。たとえば、CASE式の便利な使い方として、次のようにSELECT文の結果を行列変換する方法が知られている。
+-----------------+-----------------+-------------------+ | sum_power_seigi | sum_power_akuma | sum_power_kanpeki | +-----------------+-----------------+-------------------+ | 653 | 5830 | 71899 | +-----------------+-----------------+-------------------+
これは超人分類（category）ごとに超人強度を合計した結果であるが、普通に超人分類の列をGROUP BY句で集約キーとして使っても、結果は「行」として出力されてしまい、列として並べることはできない。
SELECT category, SUM(power) AS sum_power FROM chojin GROUP BY category;
結果
+--------------+-----------+ | category | sum_power | +--------------+-----------+ | 完璧超人 | 71899 | | 悪魔超人 | 5830 | | 正義超人 | 653 | +--------------+-----------+
「列」として結果を得るには、以下のようにSUM関数の中でCASE式を使うことで、列を3つ本当に作ってしまえば良い。
SELECT SUM(CASE WHEN category = '正義超人' THEN power ELSE 0 END) AS sum_power_seigi, SUM(CASE WHEN category = '悪魔超人' THEN power ELSE 0 END) AS sum_power_akuma, SUM(CASE WHEN category = '完璧超人' THEN power ELSE 0 END) AS sum_power_kanpeki FROM chojin;
このCASE式のやっていることは、超人分類（category）が「正義超人」なり「悪魔超人」なりの特定の値と合致した場合には、その超人の超人強度を出力し、そうでない場合はゼロを出力する、ということである。その結果を合計することで、ある特定の超人分類の超人強度の合計値を算出できるようになるわけである。
単純CASE式
CASE式には2種類ある。1つ目が上記で学んだ「検索CASE式」、そして2つ目がそれを簡略化した「単純CASE式」である。
単純CASE式は、検索CASE式に比べると記述が簡単なのであるが、記述できる条件が限定的であるという欠点を持っている。そのため、基本的には検索CASE式を使ってもらえればよいのだが、ここで構文について簡単に学習しておく。
単純CASE式の構文は以下のようになる。
CASE ＜式＞
WHEN ＜式＞ THEN ＜式＞
WHEN ＜式＞ THEN ＜式＞
WHEN ＜式＞ THEN ＜式＞
・
・
・
ELSE ＜式＞
END
最初のWHEN句から評価を始めて、真になるWHEN句が見つかるまで次々にWHEN句を見ていく動作は、検索CASE式と同じである、また、最後まで真になるWHEN句がなかった場合に、ELSE句で指定された式を返す点も変わらない、違いは、最初の「CASE ＜式＞」で、評価対象になる式を決めてしまう点である。
具体的に、検索CASE式と単純CASE式で同じ意味のSQL文を書いてみる。
検索CASE式で書いた場合
SELECT name,
CASE WHEN category = '正義超人'
THEN CONCAT('A:', category)
WHEN category = '悪魔超人'
THEN CONCAT('B:', category)
WHEN category = '完璧超人'
THEN CONCAT('C:', categ\n\nory)
ELSE NULL
END AS abc_category
FROM chojin;
単純CASE式で書いた場合
SELECT name,
CASE category
WHEN '正義超人' THEN CONCAT('A:', category)
WHEN '悪魔超人' THEN CONCAT('B:', category)
WHEN '完璧超人' THEN CONCAT('C:', category)
ELSE NULL
END AS abc_category
FROM chojin;
単純CASE式では、「CASE category」のように、評価したい式（ここでは列そのものであるが）を記述した後は、WHEN句でもう一度「category」を記述する必要がない。その手軽さが利点ではあるが、逆にWHEN句ごとに違う列に対して条件を指定したい場合などは、単純CASE式で記述することはできない。
MySQL講座#17 述語
本ページで学習するのは、SQLで抽出条件を記述するときに不可欠な「述語」と呼ばれる道具である。実は、これまでも、この述語の仲間を扱っている。例えば、=、<、>、<>などの比較演算子は、正確には比較述語の一種である。
述語とは、関数の一種である、ただし、特別な条件を満たす関数となる。その条件とは、「戻り値が真理値になること」である、普通の関数は、戻り値が数値だったり文字列だったり日付だったりいろいろですが、述語の戻り値はすべて真理値（TRUE／FALSE／UNKNOWN）である。ここが述語と関数の大きな違いである。
具体的には、次の述語を扱っていく。
- LIKE
- BETWEEN
- IS NULL、IS NOT NULL
- IN
- EXISTS
LIKE述語－文字列の部分一致検索
これまでは、文字列を条件に検索するケースでは「=」を使ってきた。この「=」は、文字列が完全に一致する場合しか真（TRUE）にならない。一方、LIKE述語はもう少し曖昧で、文字列の部分一致検索を行うときに使用する。
部分一致には、大きく分けて、前方一致、中間一致、後方一致の3種類がある。
まず、検索用のテーブルを作成する。
CREATE TABLE SampleLike (strcol VARCHAR(6) NOT NULL, PRIMARY KEY (strcol));
START TRANSACTION;
INSERT INTO SampleLike (strcol) VALUES('abcddd');
INSERT INTO SampleLike (strcol) VALUES('dddabc');
INSERT INTO SampleLike (strcol) VALUES('abdddc');
INSERT INTO SampleLike (strcol) VALUES('abcdd');
INSERT INTO SampleLike (strcol) VALUES('ddabc');
INSERT INTO SampleLike (strcol) VALUES('abddc');
COMMIT;
SELECT * FROM SampleLike;
結果
+--------+ | strcol | +--------+ | abcdd | | abcddd | | abddc | | abdddc | | ddabc | | dddabc | +--------+
このテーブルから文字列「ddd」を含むレコードを選択するとする。このとき、前方一致、中間一致、後方一致では、それぞれ次のような結果の違いが生じる。
前方一致：「dddabc」が選択される
前方一致とは、検索条件となる文字列が、検索対象の文字列の最初に位置しているレコードだけが選択される検索の仕方である。
中間一致「abcddd」「abdddc」「dddabc」が選択される
中間一致は、検索条件となる文字列が検索対象の文字列の「どこか」に含まれていればレコードが選択される検索の仕方である。最初でも、最後でも、真ん中でも構わない。
後方一致：「abcddd」が選択される
後方一致は、前方一致の反対。つまり検索条件となる文字列が、文字列の最後尾にあるレコードだけが選択対象となる検索の仕方である。
このように、文字列の中に含まれる規則に基づいて検索することを「パターンマッチング」と呼ぶ。
前方一致を行う
SELECT * FROM SampleLike WHERE strcol LIKE 'ddd%';
結果
+--------+ | strcol | +--------+ | dddabc | +--------+
「%」は「0文字以上の任意の文字列」を意味する特殊な記号で、この場合だと「dddで始まるすべての文字列」を意味している。
中間一致検索
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd%';
結果
+--------+ | strcol | +--------+ | abcddd | | abdddc | | dddabc | +--------+
後方一致検索
SELECT * FROM SampleLike WHERE strcol LIKE '%ddd';
結果
+--------+ | strcol | +--------+ | abcddd | +--------+
なお、「%」の代わりに「_」を使うこともできるが、これは%と違い、「任意の1文字」を意味する。
strcolが「abc+任意の2文字」で構成されるレコードを選択するには、以下のようになる。
SELECT * FROM SampleLike WHERE strcol LIKE 'abc__';
結果
+--------+ | strcol | +--------+ | abcdd | +--------+
「abc」で始まる文字列としては、「abcddd」もそうである、しかし、こちらは「ddd」が3文字なため、「__」という2文字分を指定する条件に合致しない。そのため結果にも含まれない、ということになる。したがって反対に、以下のように書けば、今度は「abcddd」だけを選択することになる。
SELECT * FROM SampleLike WHERE strcol LIKE 'abc___';
結果
+--------+ | strcol | +--------+ | abcddd | +--------+
BETWEEN述語－範囲検索
BETWEENは範囲検索を行う。この述語が他の述語や関数と異なる点は、引数を3つ使うことである。例えば、chojinテーブルから身長（height）が200から210までの超人を選択する場合、以下のようになる。
SELECT name, height FROM chojin WHERE height BETWEEN 200 AND 210;
結果
+-----------------------------+--------+ | name | height | +-----------------------------+--------+ | ウォーズマン | 210 | | ラーメンマン | 209 | | ブラックホール | 201 | | ミスター・カーメン | 200 | | アトランティス | 200 | | スニゲーター | 210 | | アシュラマン | 203 | | マーリンマン | 202 | | グリムリパー | 206 | | ネメシス | 205 | | アビスマン | 202 | | ジャスティスマン | 209 | | サイコマン | 206 | +-----------------------------+--------+
BETWEENの特徴は、200と210という両端の値も含むということ。もし両端を結果に含みたくない場合、<と
を使って書く必要がある。
SELECT name, height FROM chojin WHERE height > 200 AND height < 210;
結果
+--------------------------+--------+ | name | height | +--------------------------+--------+ | ラーメンマン | 209 | | ブラックホール | 201 | | アシュラマン | 203 | | マーリンマン | 202 | | グリムリパー | 206 | | ネメシス | 205 | | アビスマン | 202 | | ジャスティスマン | 209 | | サイコマン | 206 | +--------------------------+--------+
IS NULL、IS NOT NULL－NULLか非NULLかの判定
ある列がNULLの行を選択するためには、「=」を使うことはできない。特別なIS NULLという述語を使う必要がある。
超人属性（attribute）がNULLの超人を検索
SELECT name, attribute FROM chojin WHERE attribute IS NULL;
結果
+--------------------------+-----------+ | name | attribute | +--------------------------+-----------+ | スペシャルマン | NULL | | カナディアンマン | NULL | +--------------------------+-----------+
これとは反対にNULL以外の行を選択したければIS NOT NULLを使う。
超人属性（attribute）がNULL以外の超人を検索
SELECT name, attribute FROM chojin WHERE attribute IS NOT NULL;
結果
+--------------------------------+-----------------------+ | name | attribute | +--------------------------------+-----------------------+ | キン肉マン | アイドル超人 | | テリーマン | アイドル超人 | | ロビンマスク | アイドル超人 | | ウォーズマン | アイドル超人 | | ラーメンマン | 残虐超人 | 以下省略
IN述語－ORの便利な省略形
今度は、超人強度が1000、2000、3000の超人を選択することを考えてみる。
SELECT name, power FROM chojin WHERE power = 1000 OR power = 2000 OR power = 3000;
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | バッファローマン | 1000 | | アシュラマン | 1000 | | クラッシュマン | 3000 | | グリムリパー | 1000 | | シングマン | 3000 | | カラスマン | 2000 | | サイコマン | 1000 | +--------------------------+-------+
これはこれで正解だが、値が増えると読みにくくなる。このようなとき、IN述語を使った形式で書き換えるとすっきりまとめられる。
SELECT name, power FROM chojin WHERE power IN (1000, 2000, 3000);
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | バッファローマン | 1000 | | アシュラマン | 1000 | | クラッシュマン | 3000 | | グリムリパー | 1000 | | シングマン | 3000 | | カラスマン | 2000 | | サイコマン | 1000 | +--------------------------+-------+
反対に、「超人強度が1000、2000、3000以外」の超人を選択したいなら、否定形NOT INを使う。
SELECT name, power FROM chojin WHERE power NOT IN (1000, 2000, 3000);
結果
+--------------------------------+-------+ | name | power | +--------------------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ウォーズマン | 100 | | ラーメンマン | 97 | | ブロッケンＪｒ | 90 | | ウルフマン | 80 | | ステカセキング | 250 | 以下省略
IN述語の引数にサブクエリを使用する
INとサブクエリ
IN述語には、他の述語にはない使い方がある。それは引数にサブクエリを指定するという使い方である。サブクエリは、SQL内部で生成されたテーブルのことであるから、「INはテーブルを引数に指定できる」という言い方をしても良い。
具体的な使い方を見るために、ここで1つ、新しいテーブルを追加する。どの超人が、どのイベントに参加したか、EVENTテーブルを作る。
CREATE TABLE event (event_id CHAR(4) NOT NULL, event_mei VARCHAR(200) NOT NULL, chojin_id CHAR(4) NOT NULL, PRIMARY KEY (event_id, chojin_id));
このCREATE TABLE文で特徴的なところは、主キーを2列指定しているところである。この理由は、テーブルに含まれるある1行を、重複なく特定するためには、イベントID（event_id）や超人ID（chojin_id）という1列だけでは不十分で、イベントと超人の組み合わせが必要になるからである。
では、eventテーブルにデータを挿入するINSERT文を作る。
START TRANSACTION;
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0001');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0002');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0003');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000A','第20回超人オリンピック編','0005');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0001');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0004');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0005');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0006');
INSERT INTO event (event_id, event_mei, chojin_id) VALUES ('000B','超人オリンピック ザ・ビッグファイト編','0007');
COMMIT;
実際にテーブルを表示させてみる。
SELECT * FROM event;
結果
+----------+---------------------------------------------------------+-----------+ | event_id | event_mei | chojin_id | +----------+---------------------------------------------------------+-----------+ | 000A | 第20回超人オリンピック編 | 0001 | | 000A | 第20回超人オリンピック編 | 0002 | | 000A | 第20回超人オリンピック編 | 0003 | | 000A | 第20回超人オリンピック編 | 0005 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0001 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0004 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0005 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0006 | | 000B | 超人オリンピック ザ・ビッグファイト編 | 0007 | +----------+---------------------------------------------------------+-----------+
では、「第20回超人オリンピック編（000A）に参加した超人（chojin_id）の超人強度（power）」を求める。
この答えを出すには2つのステップを踏む必要がある。
- eventテーブルから、event_idが000Aが持っている超人ID（chojin_id）を選択する
- chojinテーブルから、1.で選択した超人（chojin_id）のみ超人強度（power）を選択する
SQLでも同様である。まず、1.のステップは次のように書ける。
SELECT chojin_id FROM event WHERE event_id = '000A';
結果
+-----------+ | chojin_id | +-----------+ | 0001 | | 0002 | | 0003 | | 0005 | +-----------+
あとは、このSELECT文そのものを2.の条件として使えば良い。
SELECT name, power FROM chojin WHERE id IN (SELECT chojin_id FROM event WHERE event_id = '000A');
結果
+--------------------+-------+ | name | power | +--------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ラーメンマン | 97 | +--------------------+-------+
では、「超人オリンピック ザ・ビッグファイト編（000B）に参加しなかった正義超人」を検索する。
SELECT name, power FROM chojin WHERE category ='正義超人' AND id NOT IN (SELECT chojin_id FROM event WHERE event_id = '000B');
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | テリーマン | 95 | | ロビンマスク | 96 | | スペシャルマン | 0 | | カナディアンマン | 0 | +--------------------------+-------+
EXISTS述語
本ページの最後で学習するのは、EXISTSという述語である。これを最後に持ってきたことには、3つの理由がある。
- EXISTSはこれまで学んだ述語とは使い方が異なる
- 構文を直感的に理解することが難しい
- 実はEXISTSを使わなくてもIN（およびNOT IN）によって、ほぼ代用できる
1.と2.の理由は、ある意味でセットになっているが、EXISTSは慣れないうちは使い方の難しい述語である。特に否定形のNOT EXISTSを使うSQL文は、熟練したDBエンジニアでも意味を即座に把握できないこともしばしばである。また、結局の所3.の理由で述べたように、INで代用できてしまうケースが多いため、「覚えたけどあまり利用しない」という人が多い述語である。
ただし、EXISTS述語は、使いこなせるようになると非常に大きな力を発揮する。そのゆえ、いずれSQLの中級入門を果たすときはマスターしていただきたい道具であるため、本ページでは基本的な使い方に絞って紹介する。
EXISTS述語の使い方
EXISTS述語の役割を一言でいうと、「“ある条件に合致するレコードの存在有無”を調べること」である。そういうレコードが存在すれば真（TRUE）、存在しなければ偽（FALSE）を返す。EXISTS（存在する）という述語の主語は、「レコード」である。
例として、「INとサブクエリ」で求めた「第20回超人オリンピック編（000A）に参加した超人（chojin_id）の超人強度（power）」を、EXISTSを使って求めてみる。
SELECT name, power FROM chojin AS C WHERE EXISTS ( SELECT * FROM event AS E WHERE E.event_id = '000A' AND E.chojin_id = C.id );
結果
+--------------------+-------+ | name | power | +--------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ラーメンマン | 97 | +--------------------+-------+
EXISTSの引数
これまで学んだ述語は、だいたい「列LIKE文字列」や「列BETWEEN 値1 AND 値2」のように、2つ以上の引数を指定した。しかし、EXISTSの左側には何もない。これは妙な形であるが、その理由は、EXISTSが引数を1つしか取らない述語だからである。EXISTSは、右に引数を1つだけ書く。そしてその引数は、常にサブクエリである。この場合、
( SELECT * FROM event AS E WHERE E.event_id = '000A' AND E.chojin_id = C.id )
というサブクエリが唯一の引数である。正確には、「E.chojin_id = C.id」という条件でchojinテーブルとeventテーブルを結合しているため、相関サブクエリが引数である。EXISTSは、常に相関サブクエリを引数にとる。
サブクエリの中の「SELECT *」
先ほどのサブクエリの中で「SELECT *」としている点に違和感を感じるかもしれないが、EXISTSはレコードの存在有無しか見ないため、どんな列が返されるかを一切気にしない。EXISTSは、サブクエ内のWHERE句で指定されている条件「イベントID（event_id）が'000A'で、超人ID（chojin_id）が超人（chojin）テーブルとイベント（event）テーブルとで一致する」レコードが存在するかどうかだけ調べて、そのレコードが存在した場合にのみ、真（TRUE）を返す。
したがって、以下のように書き方をしても、結果は変わらない。
SELECT name, power FROM chojin AS C WHERE EXISTS (SELECT 1 FROM event AS E WHERE E.event_id = '000A' AND E.chojin_id = C.id);
結果
+--------------------+-------+ | name | power | +--------------------+-------+ | キン肉マン | 95 | | テリーマン | 95 | | ロビンマスク | 96 | | ラーメンマン | 97 | +--------------------+-------+
EXISTSのサブクエリで「SELECT *」と書くのは、SQLの一種の習慣だと思っていただきたい。
NOT INをNOT EXISTSで書き換える
INをNOT EXISTSで書き換えられるように、NOT IN をNOT EXISTSで書き換えることも可能。「超人オリンピック ザ・ビッグファイト編（000B）に参加した超人（chojin_id）以外の正義超人の超人強度（power）」を求めるSELECT文を、NOT EXISTSを使って書いてみる。
SELECT name, power FROM chojin AS C WHERE category = '正義超人' AND NOT EXISTS (SELECT * FROM event AS E WHERE E.event_id = '000B' AND E.chojin_id = C.id);
結果
+--------------------------+-------+ | name | power | +--------------------------+-------+ | テリーマン | 95 | | ロビンマスク | 96 | | スペシャルマン | 0 | | カナディアンマン | 0 | +--------------------------+-------+
NOT EXISTSは、EXISTSとは逆に、サブクエリ内部で指定した条件のレコードが「存在しない」場合に真（TRUE）を返す。
さて、INとEXISTSのSELECT文を見比べると、INのほうが分かりやすいと感じる人も多いのではないだろうか。最初は無理にEXISTSを使う必要はない。EXISTSには、INにない便利さがあるし、厳密にこの2つは同値ではないので、最終的にはどちらの述語もマスターしてほしいが、そうしたことは中級編に内容になってくる。
MySQL講座#16 いろいろな関数
関数の種類
これまでは、主にSQLの文法や構文といった、「守るべきルール」を中心に学習してきた。本ページでは、これまでと観点を変えて、SQLが持っている便利な道具を紹介する。その中心となるのが「関数」である。
関数とは、「ある値を“入力”すると、それに対応した値を“出力”する」機能である。このときの入力を「引数」と呼び、出力を「戻り値」と呼ぶ。
関数は大きく分けて、
- 算術関数
- 文字列関数
- 日付関数
- 変換関数
- 集約関数
がある。
集約関数は基本的に、COUNT、SUM、AVG、MAX、MINの5つだったが、そのほかの種類の関数の総数は200を超える。数が多いと言っても、頻繁に使う関数はせいぜい30～50くらいであるし、わからなければ関数リファレンスを引けば良い。
算術関数
いちばん基本的な算術関数については、もう既に学習済みである。
- +（足し算）
- -（引き算）
- *（掛け算）
- /（割り算）
まずは算術関数の学習のため、以下のようなサンプルのテーブル（SampleMath）を用意する。
データ型「NUMERIC」は、多くのDBMSが持っているデータ型で、「NUMERIC」という形式で数値の大きさを指定する。
CREATE TABLE SampleMath (m NUMERIC (10,3), n INTEGER, p INTEGER);
START TRANSACTION; INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL); INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL); INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3); INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL); INSERT INTO SampleMath(m, n, p) VALUES (5.555, 2, NULL); INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL); INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL); COMMIT;
SELECT * FROM SampleMath;
結果
+----------+------+------+ | m | n | p | +----------+------+------+ | 500.000 | 0 | NULL | | -180.000 | 0 | NULL | | NULL | NULL | NULL | | NULL | 7 | 3 | | NULL | 5 | 2 | | NULL | 4 | NULL | | 8.000 | NULL | 3 | | 2.270 | 1 | NULL | | 5.555 | 2 | NULL | | NULL | 1 | NULL | | 8.760 | NULL | NULL | +----------+------+------+
ABS－絶対値
ABSは絶対値を求める関数である。絶対値とは、数値の符号を考えない、ゼロからの距離の置きさを表す関数である。
SELECT m, ABS(m) AS abs_col FROM SampleMath;
結果
+----------+---------+ | m | abs_col | +----------+---------+ | 500.000 | 500.000 | | -180.000 | 180.000 | | NULL | NULL | | NULL | NULL | | NULL | NULL | | NULL | NULL | | 8.000 | 8.000 | | 2.270 | 2.270 | | 5.555 | 5.555 | | NULL | NULL | | 8.760 | 8.760 | +----------+---------+
右側のabs_col列は、ABS関数で求めたmの絶対値である。-180の絶対値が、符号がとれて180になっている点に注目していただきたい。
この結果を見てすぐに気づくと思われるが、ABS関数の引数がNULLの場合、結果もNULLである。これはABS関数だけではなく、ほぼすべての関数がNULLに対してはNULLを返す決まりになっている。
MOD関数
MODは割り算の余り（剰余）を求める関数で、moduloの略である。たとえば「7 / 3」の余りは1なので、「MOD(7, 3) = 1」になる。小数の計算が入ると「余り」という概念がなくなってしまうので、MOD関数が使えるのは、必然的に整数の列だけになる。
割り算（n÷p）の余りを求める
SELECT n, p, MOD(n, p) AS mod_col FROM SampleMath;
結果
+------+------+---------+ | n | p | mod_col | +------+------+---------+ | 0 | NULL | NULL | | 0 | NULL | NULL | | NULL | NULL | NULL | | 7 | 3 | 1 | | 5 | 2 | 1 | | 4 | NULL | NULL | | NULL | 3 | NULL | | 1 | NULL | NULL | | 2 | NULL | NULL | | 1 | NULL | NULL | | NULL | NULL | NULL | +------+------+---------+
ROUND－四捨五入
四捨五入はROUNDという関数で行う。四捨五入のことを「丸める」ともいうが、英語でもround（丸い）という単語を使用する。丸めの桁数に1を指定すると、小数点第二位で、2を指定すると第三位で四捨五入する。
m列の数値をn列の丸め桁数で四捨五入する
SELECT m, n, ROUND(m, n) AS ROUND_COL FROM SampleMath;
結果
+----------+------+-----------+ | m | n | ROUND_COL | +----------+------+-----------+ | 500.000 | 0 | 500.000 | | -180.000 | 0 | -180.000 | | NULL | NULL | NULL | | NULL | 7 | NULL | | NULL | 5 | NULL | | NULL | 4 | NULL | | 8.000 | NULL | NULL | | 2.270 | 1 | 2.300 | | 5.555 | 2 | 5.560 | | NULL | 1 | NULL | | 8.760 | NULL | NULL | +----------+------+-----------+
文字列関数
これまで、関数ということで主に数値に対して使う算術演算子を中心に見てきた。しかし、SQLが持っている関数のうち、実は算術関数はごく一部でしかない。もちろん、算術関数は頻繁に使われる関数ではあるが、それと同じくらいよく使うのが文字列関数である。
SQLにも、置換、切り出し、短縮などよく使う文字列操作の機能がたくさん用意されている。
文字列関数を学習するために、もう1つサンプルのテーブル（SampleStr）を作ってみる。
CREATE TABLE SampleStr (str1 VARCHAR(40), str2 VARCHAR(40), str3 VARCHAR(40));
START TRANSACTION;
INSERT INTO SampleStr (str1, str2, str3) VALUES ('あいう', 'えお', NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田', '太郎', 'です');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL, 'あああ', NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎', 'abc', 'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ミックマック', 'ッ', 'っ');
COMMIT;
SELECT * FROM SampleStr;
結果
+--------------------+-----------+--------+ | str1 | str2 | str3 | +--------------------+-----------+--------+ | あいう | えお | NULL | | abc | def | NULL | | 山田 | 太郎 | です | | aaa | NULL | NULL | | NULL | あああ | NULL | | @!#$% | NULL | NULL | | ABC | NULL | NULL | | aBC | NULL | NULL | | abc太郎 | abc | ABC | | abcdefabc | abc | ABC | | ミックマック | ッ | っ | +--------------------+-----------+--------+
CONCAT－連結
実務では、「あいう + えお = あいうえお」のように、文字列を連結したいと思うことが頻繁にある。SQLでこれを実現するには、「CONCAT」という関数を使用する。
str1 + str2 と2つの文字をつなげる
SELECT str1, str2, CONCAT(str1, str2) AS str_concat FROM SampleStr;
結果
+--------------------+-----------+-----------------------+ | str1 | str2 | str_concat | +--------------------+-----------+-----------------------+ | あいう | えお | あいうえお | | abc | def | abcdef | | 山田 | 太郎 | 山田太郎 | | aaa | NULL | NULL | | NULL | あああ | NULL | | @!#$% | NULL | NULL | | ABC | NULL | NULL | | aBC | NULL | NULL | | abc太郎 | abc | abc太郎abc | | abcdefabc | abc | abcdefabcabc | | ミックマック | ッ | ミックマックッ | +--------------------+-----------+-----------------------+
LENGTH－文字長
文字列が何文字なのかを調べるための関数は、LENGTH（長さ）である。
str1の文字列の長さを調べてみる。
SELECT str1, LENGTH(str1) AS len_str FROM SampleStr;
結果
+--------------------+---------+ | str1 | len_str | +--------------------+---------+ | あいう | 9 | | abc | 3 | | 山田 | 6 | | aaa | 3 | | NULL | NULL | | @!#$% | 5 | | ABC | 3 | | aBC | 3 | | abc太郎 | 9 | | abcdefabc | 9 | | ミックマック | 18 | +--------------------+---------+
LOWER－小文字化
LOWERはアルファベットの場合だけに関係する関数で、引数の文字列をすねて小文字に変換する。したがって、アルファベット意外に適用しても変化しない。また、最初から小文字の文字にも影響しない。
SELECT str1, LOWER(str1) AS low_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
結果
+--------+---------+ | str1 | low_str | +--------+---------+ | abc | abc | | 山田 | 山田 | | ABC | abc | | aBC | abc | +--------+---------+
小文字があれば大文字もある。大文字化の関数はUPPERである。
RELPACE－文字列の置換
REPLACEは、文字列中にある一部分の文字列を、別の文字に置き換えるときに使う。
SELECT str1, str2, str3, REPLACE(str1, str2, str3) AS rep_str FROM SampleStr;
str1：対象文字列
str2：置換前の文字列
str3：置換後の文字列
rep_str：置換結果
結果
+--------------------+-----------+--------+--------------------+ | str1 | str2 | str3 | rep_str | +--------------------+-----------+--------+--------------------+ | あいう | えお | NULL | NULL | | abc | def | NULL | NULL | | 山田 | 太郎 | です | 山田 | | aaa | NULL | NULL | NULL | | NULL | あああ | NULL | NULL | | @!#$% | NULL | NULL | NULL | | ABC | NULL | NULL | NULL | | aBC | NULL | NULL | NULL | | abc太郎 | abc | ABC | ABC太郎 | | abcdefabc | abc | ABC | ABCdefABC | | ミックマック | ッ | っ | ミっクマっク | +--------------------+-----------+--------+--------------------+
SUBSTRING－文字列の切り出し
SUBSTRINGは、文字列中にある一部分の文字列を切り出す場合に使用する。切り出し位置は、「左から何文字目」という数え方をする。
文字列の左から3番目と4番目の文字を抜き出す
SELECT str1, SUBSTRING(str1 FROM 3 FOR 4) AS sub_str FROM SampleStr;
結果
+--------------------+--------------+ | str1 | sub_str | +--------------------+--------------+ | あいう | う | | abc | c | | 山田 | | | aaa | a | | NULL | NULL | | @!#$% | #$% | | ABC | C | | aBC | C | | abc太郎 | c太郎 | | abcdefabc | cdef | | ミックマック | クマック | +--------------------+--------------+
UPPER－大文字化
UPPERは、アルファベットだけに関係する関数で、引数の文字列をすべて大文字に変換する。したがって、アルファベット意外に適用しても変化しない。また、最初から大文字の文字にも影響しない。
SELECT str1, UPPER(str1) AS up_str
FROM SampleStr
WHERE str1 IN ('ABC', 'aBC', 'abc', '山田');
結果
+--------+--------+ | str1 | up_str | +--------+--------+ | abc | ABC | | 山田 | 山田 | | ABC | ABC | | aBC | ABC | +--------+--------+
これとは反対に、小文字化する関数はLOWERである。
日付関数
CURRENT_DATE－現在の日付
CURRENT_DATEは、SQLを実行した日、つまりこの関数が実行された日を戻り値として返す。引数がないため、カッコ（）は不要。
CURRENT_DATEは、実行する日時によって戻り値が変化する。2020年2月11日に実行すれば「2020-02-11」が得られるし、2020年2月12日に実行すれば「2020-02-12」が得られる。
SELECT CURRENT_DATE;
結果
+--------------+ | CURRENT_DATE | +--------------+ | 2020-02-11 | +--------------+
CURRENT_TIME－現在の時間
CURRENT_TIMEは、SQLを実行した時間、つまりこの関数が実行された時間を取得する。これも引数がないためカッコ（）が不要である。
SELECT CURRENT_TIME;
結果
+--------------+ | CURRENT_TIME | +--------------+ | 19:47:04 | +--------------+
CURRENT_TIMESTAMP－現在の日時
CURRENT_TIMESTAMPは、CURRENT_DATE + CURRENT_TIMEの機能を持つ関数である。この関数を使うと現在の日付も日時も一緒に取得できるし、この結果から日時や時間だけを切り出すことも可能。
SELECT CURRENT_TIMESTAMP;
結果
+---------------------+ | CURRENT_TIMESTAMP | +---------------------+ | 2020-02-11 19:49:21 | +---------------------+
EXTRACT－日付要素の切り出し
EXTRACTは、日付のデータからその一部分、たとえば「年」や「月」、または「時間」や「秒」だけを切り出す場合に使用する。戻り値は日付型ではなく、数値型になる。
日付要素を切り出してみる。
SELECT CURRENT_TIMESTAMP, EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month, EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day, EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS hour, EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS minute, EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
結果
+---------------------+------+-------+------+------+--------+--------+ | CURRENT_TIMESTAMP | year | month | day | hour | minute | second | +---------------------+------+-------+------+------+--------+--------+ | 2020-02-11 19:54:01 | 2020 | 2 | 11 | 19 | 54 | 1 | +---------------------+------+-------+------+------+--------+--------+
変換関数
最後に紹介するカテゴリは、変換関数というちょっと特殊な働きをする関数の一群である。特殊と言っても、構文はこれまで見てきた関数と似ているし、数も少ないのですぐに覚えることが可能。
「変換」という言葉は意味の広いものであるが、SQLにおいては大きく2つ意味がある。1つが、データの型の変換、略して「型変換」や、英語で「キャスト」と呼ぶもの。そしてもう1つが、値の変換である。
CAST－型変換
型変換はCASTという関数で行う。
型変換はなぜ必要かというと、データ型に合わないデータをテーブルに登録したり、あるいは演算したりするときには、型の不一致であるがゆえのエラーが生じたり、暗黙の型変換を生じさせて処理速度を低下させるといった不都合が起きるためである。そういう場合には、事前に適切な型へ変換してあげる必要がある。
文字型から数値型への変換は以下のようになる。
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
結果
+---------+ | int_col | +---------+ | 1 | +---------+
文字型から日付型への変換は以下のようになる。
SELECT CAST('2020-02-11' AS DATE) AS date_col;
結果
+------------+ | date_col | +------------+ | 2020-02-11 | +------------+
この結果を見ると分かるように、文字型から整数型へ変更すると、「000」のような前ゼロが表示上消えるので、型変換されているという実感がわく。しかし、文字型から日付型へ変更するような場合、ユーザから見てデータの見た目に何か変化があるわけではないので、型変換されているイメージを持ちにくい。このことからもわかるように、型変換は、ユーザが使いやすいように用意された機能というより、DBMSにとって内部処理をやりやすくするために作られた機能である。
COALESCE－NULLを値へ変換
COALESCE（コアレス）は、SQL独特の関数である。可変個の引数をとり、左から順に引数を見て、最初にNULLでない値を返す。可変個なので、必要ならいくつでも引数を増やすことができる。
しかし、変わった関数でありながら、実は非常に頻繁に使われる。どのような場合に使うかというと、SQL文の中で、NULLを何か別の値に変えて扱いたい場合である。NULLが演算や関数の中にまぎれこむと、結果が全部NULLになってしまう。これを避けるときに重宝するのがCOALESCEである。
SELECT COALESCE(NULL, 1) AS col_1, COALESCE(NULL, 'test', NULL) AS col_2, COALESCE(NULL, NULL, '2020-02-11') AS col_3;
結果
+-------+-------+------------+ | col_1 | col_2 | col_3 | +-------+-------+------------+ | 1 | test | 2020-02-11 | +-------+-------+------------+
SampleStrテーブルの列を使ったサンプル。
SELECT COALESCE(str2, 'NULLLです') FROM SampleStr;
結果
+-------------------------------+ | COALESCE(str2, 'NULLLです') | +-------------------------------+ | えお | | def | | 太郎 | | NULLLです | | あああ | | NULLLです | | NULLLです | | NULLLです | | abc | | abc | | ッ | +-------------------------------+
このように、NULLを含む列であっても、COALESCEで別の値に変換してからほかの関数や演算の入力とすることで、結果がNULLでなくなるのである。
なお、多くのDBMSがこのCOALESCEの簡略版の独自関数を用意している。しかし、これらは実装依存であるため、どんなDBMSでも使えるCOALESCEを使用することを推奨する。
MySQL講座#15 相関サブクエリ
普通のサブクエリと相関サブクエリの違い
「超人強度（powrer）が、全体の平均の超人強度よりも高い超人」を選び出すには、サブクエリを使えば実現できる。今度は少しこの条件を変えて「超人分類（category）ごとに平均超人強度より高い超人」を、categoryのグループから選び出すことを考えてみる。
超人分類ごとに平均超人強度を比較する
グループごとに「小分け」にしたうえで、そのグループ内の平均超人強度と各超人の超人強度を比較したい。
超人分類別に、平均超人強度を求めること自体、難しくはない。
SELECT AVG(power) FROM chojin GROUP BY category;
結果
+------------+ | AVG(power) | +------------+ | 3268.1364 | | 448.4615 | | 72.5556 | +------------+
しかし、スカラ・サブクエリのやり方にならって、このSELECT文をそのままサブクエリとしてWHERE句に書いてしまうと、エラーになってうまくいかない。
SELECT id, name, power FROM chojin WHERE power > (SELECT AVG(power) FROM chojin GROUP BY category);
結果
ERROR 1242 (21000): Subquery returns more than 1 row
エラーになる理由は、このサブクエリが3行を返してしまい、スカラ・サブクエリにならないからである。WHERE句でサブクエリを使用する場合は、必ず結果は1行である必要がある。
しかし、超人分類というグループ単位で超人強度と平均超人強度を比較する以上、これ以外書きようはない気がするが、どのようにすればよいか。
相関サブクエリを使った解決方法
ここで登場するのが、相関サブクエリです。
先ほどのSELECT文に1行追加するだけで、求められる結果を得られる正しいSELECT文に変身させることができる。
SELECT id, name, power FROM chojin AS C1 WHERE power > (SELECT AVG(power) FROM chojin AS C2 WHERE C1.category = C2.category GROUP BY category);
結果
+------+--------------------------------+-------+ | id | name | power | +------+--------------------------------+-------+ | 0001 | キン肉マン | 95 | | 0002 | テリーマン | 95 | | 0003 | ロビンマスク | 96 | | 0004 | ウォーズマン | 100 | | 0005 | ラーメンマン | 97 | | 0006 | ブロッケンＪｒ | 90 | | 0007 | ウルフマン | 80 | | 0014 | バッファローマン | 1000 | | 0016 | プラネットマン | 500 | | 0017 | ジャンクマン | 600 | | 0019 | アシュラマン | 1000 | | 0020 | サンシャイン | 700 | | 0021 | マックス・ラジアル | 4800 | | 0022 | ターボメン | 4000 | | 0026 | ピークア・ブー | 4200 | | 0027 | ストロング・ザ・武道 | 9999 | | 0029 | ジャック・チー | 3500 | | 0031 | ネメシス | 6800 | | 0032 | ポーラマン | 7200 | | 0039 | ガンマン | 3800 | +------+--------------------------------+-------+
これで正義超人、悪魔超人、完璧超人の3つの分類について、各グループの平均超人強度より高い超人を選択することができた。
ポイントは、サブクエリ内に追加したWHERE句の条件である。この意味を日本語で表現するならば、「各超人の超人強度と平均超人強度の比較を、同じ超人分類の中で行う」となる。
C1、C2というテーブルの別名は、今回、比較対象となるテーブル同士が同じchojinテーブルだったので、区別するために必要なものである。相関サブクエリの場合、こういったテーブルの別名を列名の前に「＜テーブル名＞.＜列名＞」の形式で記述する必要がある。
このように、相関サブクエリは、テーブル全体ではなく、テーブルの一部のレコード集合に限定した比較をしたい場合に使用する。したがって、相関サブクエリを使うとき、俗に「縛る」とか「制限する」という言い方をする。今回の例で言えば、「超人分類に縛って」平均超人強度との比較を行っている。
相関サブクエリも、結局は集合のカットをしている
相関サブクエリも、GROUP BY句と同じく、集合の「カット」という機能を持っていることが分かる。
それぞれの超人分類の中で平均超人強度が計算され、それが超人テーブルの各レコードと比較されるため、相関サブクエリは、レコードに対して実質的に1行しか返していない、とみなされる。これが相関サブクエリがエラーにならないカラクリである。
超人分類が変わると、比較する平均超人強度も変わる。このようにして各超人の超人強度と平均超人強度が比較される訳である。相関サブクエリは内部動作が見えにくいため、初心者にとっては理解しにくい機能として有名であるが、内部動作を見える化して追ってみると、意外に簡単であることが分かる。
結合条件は必ずサブクエリの中に書く
ここで、SQLの初心者が相関サブクエリを使うときに、よくやってしまう間違いを1つ紹介する。それは「縛る」ための結合条件をサブクエリの内側ではなく、外側に書いてしまうというものである。具体的には、以下。
SELECT id, name, power FROM chojin AS C1 WHERE C1.category = C2.category WHERE power > (SELECT AVG(power) FROM chojin AS C2 GROUP BY category);
結果
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE power > (SELECT AVG(power) FROM chojin AS C2 ' at line 4
これは、サブクエリの中にあった条件を、外側に移動させただけで、その他は何の変更も加えていない。ところが、このSELECT文はエラーになって正しく実行できない。こういう書き方が許されてもおかしくなさそうなのに、SQLのルールで禁止されている。
ではいったいそれはどのようなルールかというと、相関名のスコープである。相関名というのは、C1やC2など、テーブルの別名としてつけた名前である。そして、スコープとは、生存範囲である。つまり、相関名には、それが通用する範囲に制限がある、ということなのである。
具体的には、サブクエリ内部でつけられた相関名は、そのサブクエリ内でしか使用できない、ということである。別の言い方をすると、「内から外は見えるが、外から内は見えない」ということである。
このように、相関名には、それが有効な範囲が存在する、ということを忘れないでいただきたい。SQLは、以前も説明したように、内側のサブクエリから外側へ向かって実行される。そうすると、サブクエリが実行され終わったときには、実行結果だけが残って、抽出元となったテーブルC2は消えてなくなるのである。そのため、サブクエリの外側が実行されるタイミングでは、もうC2は存在しなくなっていて、「そんな名前のテーブルはありません」というエラーが返されることになるのである。
では、今度は超人分類別に平均身長を求めてみる。
SELECT category, AVG(height) FROM chojin GROUP BY category;
結果
+--------------+-------------+ | category | AVG(height) | +--------------+-------------+ | 完璧超人 | 230.6364 | | 悪魔超人 | 215.4615 | | 正義超人 | 199.4286 | +--------------+-------------+
では相関サブクエリを使って、各超人分類の平均身長より高い身長の超人を選択する。
SELECT category, name, height FROM chojin AS C1 WHERE height > (SELECT AVG(height) FROM chojin AS C2 WHERE C1.category = C2.category GROUP BY category);
結果
+--------------+--------------------------------+--------+ | category | name | height | +--------------+--------------------------------+--------+ | 正義超人 | ロビンマスク | 217 | | 正義超人 | ウォーズマン | 210 | | 正義超人 | ラーメンマン | 209 | | 悪魔超人 | ザ・魔雲天 | 285 | | 悪魔超人 | バッファローマン | 250 | | 悪魔超人 | プラネットマン | 254 | | 悪魔超人 | サンシャイン | 300 | | 完璧超人 | マックス・ラジアル | 256 | | 完璧超人 | クラッシュマン | 283 | | 完璧超人 | ストロング・ザ・武道 | 290 | | 完璧超人 | ポーラマン | 278 | | 完璧超人 | ミラージュマン | 263 | | 完璧超人 | ガンマン | 302 | | 完璧超人 | シングマン | 298 | | 完璧超人 | カラスマン | 260 | +--------------+--------------------------------+--------+
MySQL講座#14 サブクエリ
サブクエリとビュー
これから本ページで学ぶ「サブクエリ」は、ビューを基本とした技術である。サブクエリの特徴を一言で表すと、「使い捨てのビュー」である。
ビューは、データそのものを保存するのではなく、データを取り出すSELECT文だけを保存するという方法で、ユーザの利便性を高める道具であった。それに対してサブクエリとは、ビュー定義のSELECT文をそのままFROM句に持ち込んでしまったものである。
では、もう一度CategorySumビューのビュー定義と、ビューに対するSELECT文を見てみる。
CREATE VIEW CategorySum (category, cnt_chojin) AS SELECT category, COUNT(*) FROM chojin GROUP BY category;
ビューが作成されていることの確認。
SELECT category, cnt_chojin FROM CategorySum;
結果
+--------------+------------+ | category | cnt_chojin | +--------------+------------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+------------+
サブクエリでは以下のようになる。
SELECT category, cnt_chojin FROM (SELECT category, COUNT(*) AS cnt_chojin FROM chojin GROUP BY category) AS CategorySum;
得られる結果はどちらも同じである。
+--------------+------------+ | category | cnt_chojin | +--------------+------------+ | 完璧超人 | 22 | | 悪魔超人 | 13 | | 正義超人 | 9 | +--------------+------------+
見ての通り、ビュー定義のSELECT文をそのままFROM句の中に入れてしまったのがサブクエリである。「AS CategorySum」というのがこのサブクエリにつけられた名前であるが、これは使い捨ての名前なので、ビューのように記憶装置に保存されることはなく、SELECT文の実行終了後には消えてなくなる。サブクエリとは「下位の（sub）」の「問い合わせ（query）」という意味。
実際、このSELECT文は、入れ子構造になっていて、まずFROM句の中のSELECT文が実行され、その後に外側のSELECT文が実行される、という順番になる。
1.まずはFROM句の中のSELECT文（サブクエリ）が実行される
SELECT category, COUNT(*) AS cnt_chojin FROM chojin GROUP BY category;
2.の結果に対して、外側のSELECT文が実行される
SELECT category, cnt_chojin FROM CategorySum;
SELECT id, name, power FROM chojin WHERE power &> AVG(power);
結果
ERROR 1111 (HY000): Invalid use of group function
集約関数をWHERE句に書くことはできないという制限のため、このSELECT文は誤りとなる。
さて、ではいったい、どうすれば上記のような条件を満たすSELECT文を書くことができるのだろうか。
ここで、スカラ・サブクエリが力を発揮する。まず、chojinテーブルに含まれている超人の平均の超人強度（power）を求めるには、以下のSELECT文で可能である。
SELECT AVG(power) FROM chojin;
結果
+------------+ | AVG(power) | +------------+ | 1781.4091 | +------------+
ここで、SELECT文の検索結果がスカラ値であることは、一目瞭然である。したがって、その結果をそのまま、先程失敗したクエリの右辺に使うことが可能なのである。
SELECT id, name, power FROM chojin WHERE power &> (SELECT AVG(power) FROM chojin);
結果
+------+--------------------------------+-------+ | id | name | power | +------+--------------------------------+-------+ | 0021 | マックス・ラジアル | 4800 | | 0022 | ターボメン | 4000 | | 0023 | クラッシュマン | 3000 | | 0024 | ダルメシマン | 2500 | | 0025 | マーリンマン | 2200 | | 0026 | ピークア・ブー | 4200 | | 0027 | ストロング・ザ・武道 | 9999 | | 0028 | マーベラス | 1800 | | 0029 | ジャック・チー | 3500 | | 0031 | ネメシス | 6800 | | 0032 | ポーラマン | 7200 | | 0035 | ミラージュマン | 2200 | | 0036 | アビスマン | 2600 | | 0037 | ペインマン | 1800 | | 0039 | ガンマン | 3800 | | 0040 | シングマン | 3000 | | 0041 | カラスマン | 2000 | +------+--------------------------------+-------+
スカラ・サブクエリを書ける場所
スカラ・サブクエリを書ける場所は、何もWHERE句だけに限らない。基本的に、スカラ値が書けるところはどこにでも書くことができる。ということは、定数や列名を書くことのできる場所すべてとなり、SELECT句でもGROUP BY句でもHAVING句でもORDER BY句でも、ほとんどあらゆる場所に書くことが可能である。
例えば、SELECT句で先程の平均値を求めるスカラ・サブクエリを使えば、以下のようになる。
SELECT id, name, power, (SELECT AVG(power) FROM chojin) AS avg_power FROM chojin;
結果
+------+--------------------------------+-------+-----------+ | id | name | power | avg_power | +------+--------------------------------+-------+-----------+ | 0001 | キン肉マン | 95 | 1781.4091 | | 0002 | テリーマン | 95 | 1781.4091 | | 0003 | ロビンマスク | 96 | 1781.4091 | | 0004 | ウォーズマン | 100 | 1781.4091 | | 0005 | ラーメンマン | 97 | 1781.4091 | | 0006 | ブロッケンＪｒ | 90 | 1781.4091 | | 0007 | ウルフマン | 80 | 1781.4091 | | 0008 | ステカセキング | 250 | 1781.4091 | | 0009 | ブラックホール | 200 | 1781.4091 | | 0010 | ミスター・カーメン | 200 | 1781.4091 | | 0011 | ザ・魔雲天 | 50 | 1781.4091 | | 0012 | アトランティス | 250 | 1781.4091 | 以下省略
これは、超人一覧表の中に超人全体の平均超人強度も含めた結果、ということである。こういう帳票なども、ときとして求められることがある。
また、HAVING句に書くのなら、以下のようなSELECT文が考えられる。
SELECT category, AVG(power) FROM chojin GROUP BY category HAVING AVG(power) & > (SELECT AVG(power) FROM chojin);
結果
+--------------+------------+ | category | AVG(power) | +--------------+------------+ | 完璧超人 | 3268.1364 | +--------------+------------+
このクエリの意味は、「categoryごとに計算した平均超人強度が、超人全体の超人強度よりも高いcategoryを選択する」となる。
スカラ・サブクエリを使うときの注意点
最後に、スカラ・サブクエリを使うときに最も注意しなければならないのはことを取り上げる。それは「絶対にサブクエリが複数行を返さないようにする」ということである。というのも、サブクエリが複数行を返す時点ですでにそれはスカラ・サブクエリではなく、ただのサブクエリになってしまう。すると=、<>といったスカラ値を入力する演算子も利用できないし、SELECT句などに書くこともできなくなる。
例えば、次のサブクエリはエラーとなる。
SELECT id, name, power (SELECT AVG(power) FROM chojin GROUP BY category) AS avg_power FROM chojin;
結果
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT AVG(power) FROM chojin GROUP BY category) AS avg_power FR' at line 2
エラーの理由は簡単で、このサブクエリは、次のような複数行を返すからである。
+------------+ | AVG(power) | +------------+ | 3268.1364 | | 448.4615 | | 72.5556 | +------------+
SELECT句の1行の中に3行を押し込むことは不可能な話である。そのため、上記SELECT文は「サブクエリが複数行を返すため実行できない」という理由のエラーが返されることになる。
