過去の関連投稿
「SQLの基礎について学ぶ＃０１」
「SQLの基礎について学ぶ＃０２」
「SQLの基礎について学ぶ＃０３」
「SQLの基礎について学ぶ＃０４」
「SQLの基礎について学ぶ＃０５」
1. 式と演算子
次のテーブルを用いて説明していきます。
accountテーブル
| price（金額） |
|---|
| 100 |
| 200 |
| 600 |
| 500 |
復習ですが、SELECT文のすぐ後に選択列リストを指定します。選択列リストには結果表に、どのような列を出力するかを指定することができます。また前回までは、テーブルの中の列を指定していましたが、選択列リストには、ほかに固定値や計算式を指定することも可能です。 |
①選択列リストへの様々な指定
SELECT price, /* 列名での指定 */
price * 2, /* 計算式での指定 */
'金額' /* 固定値での指定 */
FROM account
①結果表
| price | price * 2 | '金額' |
|---|---|---|
| 100 | 200 | 金額 |
| 200 | 400 | 金額 |
| 600 | 1200 | 金額 |
| 500 | 1000 | 金額 |
②計算式に別名を付ける
SELECT price, /* 列名での指定 */
price * 2 AS doubled_price /* ASの後に別名を指定*/
FROM account
②結果表
| price | doubled_price |
|---|---|
| 100 | 200 |
| 200 | 400 |
| 600 | 1200 |
| 500 | 1000 |
③データの代わりに計算式を利用する
/* INSERT文での計算式の利用 */
INSERT INTO account(price)
VALUES (300 + 500) /* (300 + 500)は800と同値*/
/* UPDATE文での計算式の利用 */
UPDATE account
SET price = price + 100
INSERT文の結果
| price（金額） |
|---|
| 100 |
| 200 |
| 600 |
| 500 |
| 800 |
▶ 一番下の行に(300 + 500)の計算結果(800)が挿入された。 |
UPDATE文の結果
| price（金額） |
|---|
| 200 |
| 300 |
| 700 |
| 600 |
▶ 各行の金額が+100された。 |
2. CASE演算子
CASE演算子は列の値や条件式によって、その結果に応じた値に変換することができる演算子です。
CASE 評価する列や式 WHEN 値1 THEN 値1のときに返す値
(WHEN 値2 THEN 値2のときに返す値)
(ELSE デフォルト値)
END
では、具体的にSELECT文で用法を次のテーブルを使用して、見ていきます。
listテーブル
| name | price |
|---|---|
| にんじん | 200 |
| 鶏肉 | 400 |
| 牛肉 | 1200 |
| レタス | 300 |
| カレー粉 | 400 |
CASE演算子
SELECT name, price,
CASE name WHEN 'にんじん' THEN '野菜'
WHEN '鶏肉' THEN '肉類'
WHEN '牛肉' THEN '肉類'
WHEN 'レタス' THEN '野菜'
ELSE 'その他'
END AS category /* 列名を指定 */
FROM account WHERE price > 0
結果表
| name | price | category |
|---|---|---|
| にんじん | 200 | 野菜 |
| 鶏肉 | 400 | 肉類 |
| 牛肉 | 1200 | 肉類 |
| レタス | 300 | 野菜 |
| カレー粉 | 400 | その他 |
▶ またWHEN 'にんじん' < 300 THNE '普段より安い'のようにWHENのあとに値ではなく、条件式を入れることも可能です。
3. さまざまな関数
関数とは、より高度な計算を行うためにデータベースに用意された命令の総称です。
すべての関数は「呼び出し時に指定した情報である引数に対して、定められた処理を行い、結果である戻り値に変換」します。
notebookテーブル
| memo |
|---|
| Hello |
| plan |
| friends |
関数の呼び出し(LENGTH関数)
SELECT memo, LENGTH(memo) AS memo_length
FROM notebook
/* LENGTHによってmemoの値が引数として渡され、戻り値として文字数に変換される*/
結果
| memo | memo_length |
|---|---|
| Hello | 5 |
| plan | 4 |
| friends | 7 |
※ 関数はDBMSによって大きく異なるため注意が必要
関数については使用するDBMS製品によって構文が大きく異るため、製品マニュアルを確認する必要がある。
①文字の長さを得る関数 LENGTH関数/LEN関数
LENGTH(文字列を表す列) => 文字列の長さを表す数値
LEN(文字列を表す列) => 文字列の長さを表す数値
SELECT memo, LENGTH(memo) AS memo_length FROM notebook
WHERE LENGTH(memo) <= 10 /* memoの文字数が１０文字以下の行だけ取得する*/
②空白を除去する関数 TRIM関数 / LTRIM関数 / RTRIM関数
TRIM(文字列を表す列) => 左右から空白を除去した文字列
LTRIM(文字列を表す列) => 左側の空白を除去した文字列
RTRIM(文字列を表す列) => 右側の空白を除去した文字列
SELECT memo, TRIM(memo) AS memo_trim /* 左右から空白を除去した文字列 */
FROM notebook
③指定文字を置換する関数 REPLACE関数
REPLACE(置換対象の文字, 置換前の部分文字列, 置換後の部分文字列) => 置換後の文字列
notebookテーブル
| memo |
|---|
| Hekko |
UPDATE notebook
SET memo = REPLACE( memo, 'k', 'l' ) /* Hekko => Hello に置換 */
④一部を抽出する関数 SUBSTRING関数 / SUBSTR関数
SUBSTRING(文字列を表す列, 抽出開始位置, 抽出する文字数) => 抽出した部分文字列
SUBSTR(文字列を表す列, 抽出開始位置, 抽出する文字数) => 抽出した部分文字列
UPDATE notebook
WHERE SUBSTRING( memo, 1, 3 ) /* memoの１〜３文字目を抽出する */
4. 数値に関連する関数
①指定した桁で四捨五入する関数 ROUND関数
ROUND(数値を表す列, 有効にする桁数) => 四捨五入した値
accountテーブル
| price |
|---|
| 180 |
| 210 |
| 650 |
/* 十の位を四捨五入して、百単位の金額を取得する */
SELECT price, ROUND(price, -2) AS price_round
FROM account
結果
| price | price_round |
|---|---|
| 180 | 200 |
| 210 | 200 |
| 650 | 700 |
②指定した桁を切り捨てる関数 TRUNC関数
TRUNC(数値を表す列, 有効にする桁数) => 切り捨てた値
TRUNC関数はROUND関数と異なり、四捨五入を行わず、指定した桁数を切り捨てる。
③べき乗を計算する関数 POWER関数
POWER(数値を表す列, 何乗するか指定する数値) => 指定した乗数で生じた数値
例：POWER(price, 2) = price * price
例：POWER(price, 3) = price * price * price
例：POWER(price, 4) = price * price * price price * price
5. 日付に関連する関数
現在の日付・時刻を取得する
CURRENT_DATE関数 / CURRENT_TIME関数
CURRENT_DATE => YYYY-MM-DD(現在の日付)
CURRENT_TIME => HH:MM:SS(現在の時刻)
/* 日付を自動的に取得して登録 */
INSERT INTO account
VALUES (CURRENT_DATE, 'お弁当代', 500)
▶ CURRENT_DATE関数/CURRENT_TIME関数以外にも使用するDBMS製品によっては様々な日付に関連する関数がある。
5. 変換に関連する関数
データ型を変換する CAST関数
CAST(変換する値 AS 変換するデータ型) => 変換後の値
例： CAST(price AS VARCHAR(10)) + '円'
▶ 元々、priceはinteger型だが、CAST関数で文字列に変換することで'円'(文字列)と連結することができるようになる。
まとめ
今回はこれで終わります。ここで、説明している関数は、ほんの一部分になるので、引き続き学習を進めていきたいと思います。