Blank?=False

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

SQL ゼロから始めるデータベース操作 第6章 関数

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

各関数について勉強していきます。
DBMSによって仕様が異なる(いわゆる方言)事が多いため、軽く触るくらいにします。
実際に開発するときは各DBMSのリファレンスを確認するのがベストだと思います。
本記事ではMySQLを使用しています。

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

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

サンプルテーブル

今回は本書と同様このサンプルテーブルを使います。
やっぱ日本語あるとずれちゃうね

mysql> SELECT * FROM SampleMath;
+----------+------+------+
| m        | n    | p    |
+----------+------+------+
|  500.000 |    0 | NULL |
| -180.000 |    0 | NULL |
|     NULL |    7 |    3 |
|     NULL | NULL | NULL |
|     NULL |    7 |    3 |
|     NULL |    5 |    2 |
|     NULL |    4 | NULL |
|    8.000 | NULL |    3 |
|    2.270 |    1 | NULL |
|    5.556 |    2 | NULL |
|     NULL |    1 | NULL |
|    8.760 | NULL | NULL |
+----------+------+------+
12 rows in set (0.00 sec)

mysql> 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    |
| あいう             | えお      | NULL   |
| abcdefabc          | abc       | ABC    |
| ミックマック       | ッ        | っ     |
+--------------------+-----------+--------+
12 rows in set (0.00 sec)

関数とは?

様々な計算を行うもの。必ず「何かを入力して何かを出力する」という機能。
プログラミングの関数や、静的メソッドと同じ。

たとえば、ABSという関数は絶対値を返す関数です。
つまり、正の値でも負の値でも符号が正の値で結果を表示することができるわけです。
(厳密に言えば絶対値は0からの距離ですがプログラミングにおいては符号を外すという意味で使われる事が多いような。)

実際に、サンプルテーブルでは2つ目の行のmが負の値になっています。
これをABS関数にかけると、

mysql> SELECT ABS(m)
    -> FROM SampleMath
    -> WHERE 0 > m;
+---------+
| ABS(m)  |
+---------+
| 180.000 |
+---------+
1 row in set (0.00 sec)

上のように正の値になります。

標準SQL規約で標準的な関数の定義がされているため、大抵のDBMSで基本的な関数は使用可能。
ただしDBMSによっては標準SQLとは違う仕様になっていることがあるため、
なんかアレ?と思ったら各DBMSのリファレンスを見るのがベスト。

mononofu.hatenablog.com


その他の関数

その他、DBMSには様々な関数が準備されているので、何か使いたい関数があれば、
MySQLリファレンスを参照したり、ググってみてリファレンス紹介HPを呼んでみるのが一番。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12 関数と演算子 MySQL関数リファレンス


ここでは、標準SQL関数でよく使いそうなものを出していきます。

ROUND

四捨五入を行う関数。大抵のプログラミング言語と同じ。

mysql> 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 |    7 |      NULL |
|     NULL | NULL |      NULL |
|     NULL |    7 |      NULL |
|     NULL |    5 |      NULL |
|     NULL |    4 |      NULL |
|    8.000 | NULL |      NULL |
|    2.270 |    1 |     2.300 |
|    5.556 |    2 |     5.560 |
|     NULL |    1 |      NULL |
|    8.760 | NULL |      NULL |
+----------+------+-----------+
12 rows in set (0.00 sec)


ちなみに切り下げの時はTRUNCATE関数を使う。

mysql> SELECT m,n, TRUNCATE(m, 2) AS TRUNCATE_col from SampleMath;
+----------+------+--------------+
| m        | n    | TRUNCATE_col |
+----------+------+--------------+
|  500.000 |    0 |       500.00 |
| -180.000 |    0 |      -180.00 |
|     NULL |    7 |         NULL |
|     NULL | NULL |         NULL |
|     NULL |    7 |         NULL |
|     NULL |    5 |         NULL |
|     NULL |    4 |         NULL |
|    8.000 | NULL |         8.00 |
|    2.270 |    1 |         2.27 |
|    5.556 |    2 |         5.55 |
|     NULL |    1 |         NULL |
|    8.760 | NULL |         8.76 |
+----------+------+--------------+
12 rows in set (0.00 sec)

CONCAT

文字列を連結する関数。MySQLCONCAT関数、他のDBMS||+を使う。

mysql> SELECT str1, str2, str3, CONCAT(str1,str2,str3) AS STR_Concat
    -> FROM SampleStr
    -> WHERE str1='山田';
+--------+--------+--------+--------------------+
| str1   | str2   | str3   | STR_Concat         |
+--------+--------+--------+--------------------+
| 山田   | 太郎   | です   | 山田太郎です       |
+--------+--------+--------+--------------------+

UPPER

アルファベットの小文字をすべて大文字にする関数。
アルファベットの文字列比較では大文字・小文字で違う文字として判定されることもあるため、
大文字に揃えてから比較する事が多く、そのためによく使います。

mysql> SELECT str1, UPPER(str1) AS UPPER FROM SampleStr;
+--------------------+--------------------+
| str1               | UPPER              |
+--------------------+--------------------+
| あいう             | あいう             |
| abc                | ABC                |
| 山田               | 山田               |
| aaa                | AAA                |
| NULL               | NULL               |
| @!#$%              | @!#$%              |
| ABC                | ABC                |
| aBC                | ABC                |
| abc太郎            | ABC太郎            |
| あいう             | あいう             |
| abcdefabc          | ABCDEFABC          |
| ミックマック       | ミックマック       |
+--------------------+--------------------+
12 rows in set (0.01 sec)

LENGTH

標準SQLでは文字数を出力する関数として定義されていますが、
MySQLのLENGTH関数は文字のバイト数を出力します。
※他の多くのDBMSだとちゃんと文字数を出力する関数です。MySQLだけ別?
実際に、LENGTH関数を使ってみると以下のようにバイト数が表示されます。

mysql> SELECT str1, LENGTH(str1) FROM SampleStr;
+--------------------+--------------+
| str1               | LENGTH(str1) |
+--------------------+--------------+
| あいう             |            9 |
| abc                |            3 |
| 山田               |            6 |
| aaa                |            3 |
| NULL               |         NULL |
| @!#$%              |            5 |
| ABC                |            3 |
| aBC                |            3 |
| abc太郎            |            9 |
| あいう             |            9 |
| abcdefabc          |            9 |
| ミックマック       |           18 |
+--------------------+--------------+
12 rows in set (0.00 sec)


もしMySQLで文字数を取得したい場合、CHAR_LENGTH関数を使えばOKです。

mysql> SELECT str1, CHAR_LENGTH(str1) FROM SampleStr;
+--------------------+-------------------+
| str1               | CHAR_LENGTH(str1) |
+--------------------+-------------------+
| あいう             |                 3 |
| abc                |                 3 |
| 山田               |                 2 |
| aaa                |                 3 |
| NULL               |              NULL |
| @!#$%              |                 5 |
| ABC                |                 3 |
| aBC                |                 3 |
| abc太郎            |                 5 |
| あいう             |                 3 |
| abcdefabc          |                 9 |
| ミックマック       |                 6 |
+--------------------+-------------------+
12 rows in set (0.01 sec)

CURRENT_TIMESTAMP

現在の日時・時間を表示する関数です。

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2016-05-08 13:11:19 |
+---------------------+
1 row in set (0.01 sec)