【楽しく学ぶSQL入門】第5章：単一行関数とデータ型

システム開発の現場では、データベースから取り出した生のデータをそのまま使うだけでなく、システム側で扱いやすい形に加工して出力するケースが多々あります。SQL文で利用できる関数には、1行ずつ処理を行う単一行関数と、複数の行をまとめる集計関数の2種類が存在します。今回は、実務での登場頻度が極めて高い単一行関数の使い方を見ていきましょう。合わせて、「データ型」の基本についても整理しておきます。

データ型

テーブルの列には、それぞれどのような種類の実体を格納するかという「型」があらかじめ決まっています。これがデータ型と呼ばれる概念。

利用可能なデータ型はDBMSによって異なります。ただし、ベースとなる数値型、文字列型、日付型の3つはどのシステムでも必ず用意されているのが特徴。実際の名称や細かな挙動は製品ごとに違いがあるため、注意が必要です。

※文字列の「固定長」と「可変長」の違い

〇CHAR型は固定長の文字列データを扱うデータ型。たとえば「CHAR(10)」と指定された列では、内部であらかじめ10バイト分の記録領域が固定で確保されます。格納する文字列が10バイトに満たない場合、自動的に右側へ空白が詰められ、ぴったり10バイトに調整されて保存される仕組み。そのため、郵便番号や社員番号のように、入れるデータの桁数があらかじめ固定されている項目に向いています。

〇一方、VARCHAR型は可変長の文字列データを扱うデータ型。たとえば「VARCHAR(10)」と設定した列に3バイトや7バイトの文字が入力された場合、その文字数に合わせる形で領域が柔軟に変動するのが特徴。無駄なスペースが発生しないため、名前や住所など、桁数が変動する項目に最適です。

算術式

数値が格納されているデータに対しては、SELECT句の中で算術演算子を用いた四則演算を行うことができます。おなじみの算数と同じ計算方法をイメージしてください。

計算が評価される優先順位は、［乗算 * ］➔［除算 / ］➔［加算 + ］➔［減算 – ］の順番。一般的な数式と同じく、計算順序をコントロールしたい場合はカッコ（ ）で囲むことで優先順位を最も高く引き上げることが可能です。

① 例）収支テーブルから0円以上の出費と、その金額に100円を加算した値を取得する。

SELECT expenditure, expenditure + 100
FROM income_and_expenditure
WHERE expenditure > 0 ;

なお、SQLを用いると、テーブル de データを使わずに単純な計算式だけを実行することも可能。電卓のような使い方をイメージすると分かりやすいでしょう。

② 例）SQLを使って数式の計算結果を出力する。

SELECT (1+4)*2-6/3 FROM dual ;

ここで登場した「DUAL表」は、こうしたちょっとした検証や計算を行うテスト環境のために用意された仮想のテーブル。ダミーテーブルと呼ばれることもあり、覚えておくと便利です。

また計算式に空データ（NULL）が含まれていると、どのような計算であっても結果は必ずNULLになってしまう点には注意してください。

③ 例）NULLを含んだ除算を行う。

SELECT 5/NULL FROM dual ;

関数

SQLには、複雑なデータ加工や判定を自動化するための「関数」と呼ばれる命令セットが数多く用意されています。効率的な開発には欠かせません。

あらゆるシステムにおける共通のメカニズムとして、すべての関数は、「呼び出し時に指定した情報（引数）に対して、定められた処理を行い、結果（戻り値）に変換する」という挙動を取るのが基本。ただし、関数の種類や仕様はDBMSごとのバラつきが極めて大きい部分でもあります。以下で挙げる関数も、システムによっては動作しない場合があるため、あくまで基本モデルとして参考にしてください。

文字列に関する関数

・CHAR_LENGTH( str ) ：対象文字列（str）の「文字数」を返す。
・LENGTH( str ) ：対象文字列（str）の「バイト数」を返す。
・TRIM( str ) ：文字列の左右両端に存在する空白を除去した値を返す。
・LTRIM( str ) ：文字列の左側（前方）の空白を除去した値を返す。
・RTRIM( str ) ：文字列の右側（後方）の空白を除去した値を返す。
・REPLACE( str, x, y ) ：文字列（str）の中にある文字「x」を「y」へ置き換える。
・SUBSTRING( str, m, n ) ：文字列（str）の「m文字目」から「n文字分」をピンポイントで切り抜く。
・CONCAT(str1, str2[, …]) ：バラバラの文字列を1つに連結して出力する。

④ 例）都道府県テーブルから都道府県名と、その文字数をカウントして取得する。

SELECT name, CHAR_LENGTH(name)
FROM pref ;

参考サイト：MySQL 公式サイト（String Functions）

数値に関する関数

・ROUND( m, n ) ：数値（m）を任意の指定桁（n）で四捨五入する関数。
・TRUNCATE( m, n ) ：数値（m）を任意の指定桁（n）で切り捨て処理を行う。
・POWER( m, n ) ：数値（m）を「n乗」した累乗の計算結果を出力する。

⑤ 例）収支テーブルの出費を百の位で四捨五入した値を取得する。

SELECT expenditure, ROUND(expenditure, -3)
FROM income_and_expenditure
WHERE expenditure > 0;

桁数の指定（n）のルールは以下の通り。
「1」：小数第2位を対象／「0」：小数第1位を対象／「-1」：1の位を対象／「-3」：百の位を対象（今回の例）

参考サイト：MySQL 公式サイト（Mathematical Functions）

日付に関する関数

・CURRENT_DATE() ：本日の日付（YYYY-MM-DD）を返す。
・CURRENT_TIME() ：現時点の時刻（HH:MM:SS）を出力する。
・CURRENT_TIMESTAMP() ：現在の日時（YYYY-MM-DD HH:MM:SS）をタイムスタンプ形式で取得する。

構造の見方：YYYYは西暦4桁、MMは月2桁、DDは日2桁。時刻のHHは時間2桁、MMは分2桁、SSは秒2桁を意味する。

⑥ 例）現在のシステム日付を取得する。

SELECT CURRENT_DATE() FROM dual;

参考サイト：MySQL 公式サイト（Date and Time Functions）

変換に関する関数

・CAST( a AS 変換する型 ) ：対象データ（a）を、指定した別データ型へ変換する関数。
・COALESCE( a, b, c[, …] ) ：並べた引数の中から、**「最初に登場するNULLではない有効な値」**を選び出して返す。

⑦ 例）社員テーブルから、もし電話番号（tel）が空（NULL）だった場合に代替文字を出力する。

SELECT name1, name2, COALESCE(tel, '登録されていません') AS tel
FROM employee ;

参考サイト：MySQL 公式サイト（Comparison Operators）